Re: [sqlite] Inserts get slower and slower

[Don V Nielsen]

Follow-up on this issue. (Re-Post of previous which included large .jpg.  I
converted .jpg to text [see below] to make message smaller, and then
deleted previous post.)

This morning I used a JetBrains product called dotTrace to analyze my
application's resource usage. I loaded a 7 million rows into a table.  When
I simply loaded the table, it took 12 minutes.  Pretty impressive.  When I
added 'Unique' to one of the fields definitions, the load time jumped to 90
minutes.

Naturally, most all the cpu consumption was cause by the various stream and
data readers in the application.  It's easy to understand, also, that
applying unique is going to cause addition hits against the database
(ensuring the unique field value does not already exist.)  The attached
image is the dotTrace output for this run.

What I did next was employ an internal hash in my application.  I attempt
to insert the unique value into the hash and catch the exception if it
exists.  The run time is now 14 minutes for the 10 million records.



4 -- Thread #1 • 30,628 ms
  99.38 % Main • 30,437 ms • CDGAddAName.AAN.Main(Sing[])
4 99.38 % InitializePools • 30,437 ms •
CDGAddName.AddPoolController.InitializePools
4 99.38 % LoadPool • 30,437 ms + CDGAddAName.AddPool.LoadPool
4 55.32 % save_rec_to_db • 16,45 ms •
CDGddAName.TblZipRoute.save_rec_to_db(Hashtable, String)
4 55.01 % ExecuteNonQuery • 16,850 ms •
System.Data.SQLite.SQLitecommand.ExecuteNonQuery
4 54.81 % ExecuteReader • 16,786 ms •
System.Data.SQLite.SQLitecommand.ExecuteReader(CommandBehavior)
4 54.75 % NextResuIt • 16,770 ms •
System.DataSQLite.SQLiteDataReader.NextResult
  52.42 % Step • 16,056 ms •
System.Data.SQLite.SqlLite3.Step(SQLiteStatement)
   2.17 % GetStatement • 665 ms •
System.Data.SQLite.SQLitecommand.GetStatement(1nt32)
   0.05 % Reset • 16 ms • System.Data.SQLite.SQLite3.Reset(SQLtteStatement)
   0.05 % CheckClosed • 16 ms •
System.Data.SQLIte.SQLiteDataReader.CheckClosed
   0.05 % InitializeForReader • 16 ms •
System.Data.SQLite.SQLitecommand.InitializeForReader
   0.10 % Dispose • 32 ms • System.Data.Common.DbDataReader.Dispose
   0.10 % Nexesult • 32 ms • System.Data.SQLiteSQLiteDataReader.NextResult
   0.26 % getltern • 79 ms • System.Collections.Hashtable.getItem(Object)
   0.05 % Eligible • 16 ms • CDG.AddAName.Global.Eligible(Hashtable)
4 43.95 % get_parsed_record • 13,451 ms
CDGAddAName.AddPool.get_parsed_record
4 43.95% GearsedRecord • 13,461 ms •
CDG.Util.FL_FirstLogic.FL_InputFile.GearsedRecord
4 42.87% ReadRecord • 13,129 ms •
CDG.UtiI.FL_Firsltogic.FL_Inputlile.ReadRecord
  42.07 % ReadLine • 13,129 ms • System.IO.StreamReader.ReadLine
   1.08 % Parse_Record • 332 ms •
CDG.UtiI.FL_FirstLogic.Parse_FmtDef.Parse(RecordString)
   0.62 % [Native or optirnized code] 191 ms
4 Thread #2 • 30,628 ms
 100.00 % [Native or optimized code] 30,628 ms


On Thu, Feb 23, 2012 at 4:02 PM, Don V Nielsen <donvniel...@gmail.com>wrote:

> Follow-up on this issue.
>
> This morning I used a JetBrains product called dotTrace to analyze my
> application's resource usage. I loaded a 7 million rows into a table.  When
> I simply loaded the table, it took 12 minutes.  Pretty impressive.  When I
> added 'Unique' to one of the fields definitions, the load time jumped to 90
> minutes.
>
> Naturally, most all the cpu consumption was cause by the various stream
> and data readers in the application.  It's easy to understand, also, that
> applying unique is going to cause addition hits against the database
> (ensuring the unique field value does not already exist.)  The attached
> image is the dotTrace output for this run.
>
> What I did next was employ an internal hash in my application.  I attempt
> to insert the unique value into the hash and catch the exception if it
> exists.  The run time is now 14 minutes for the 10 million records.
>
>
> On Thu, Feb 9, 2012 at 8:46 AM, Black, Michael (IS) <
> michael.bla...@ngc.com> wrote:
>
>> I think you may find you're running into buffer cache limits (not sqlite
>> but OS limits).
>>
>>
>>
>> So the 1st third all fits into buffer cache.  Once it starts committing
>> to disk things slow down a LOT.
>>
>>
>>
>> Since you're not showing an real times it's hard to say you are any
>> slower than anybody else.
>>
>>
>>
>> I saw similar behavior on a project I was doing and it all boiled down to
>> disk write speed once things started going to disk.
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> Advanced Analytics Directorate
>>
>> Advanced GEOINT Solutions Operating Unit
>>
>> Northrop Grumman Information Systems
>>
>> ____________________
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on behalf of Don V Nielsen [donvniel

Re: [sqlite] Inserts get slower and slower

[Don V Nielsen]

Follow-up on this issue.

This morning I used a JetBrains product called dotTrace to analyze my
application's resource usage. I loaded a 7 million rows into a table.  When
I simply loaded the table, it took 12 minutes.  Pretty impressive.  When I
added 'Unique' to one of the fields definitions, the load time jumped to 90
minutes.

Naturally, most all the cpu consumption was cause by the various stream and
data readers in the application.  It's easy to understand, also, that
applying unique is going to cause addition hits against the database
(ensuring the unique field value does not already exist.)  The attached
image is the dotTrace output for this run.

What I did next was employ an internal hash in my application.  I attempt
to insert the unique value into the hash and catch the exception if it
exists.  The run time is now 14 minutes for the 10 million records.


On Thu, Feb 9, 2012 at 8:46 AM, Black, Michael (IS)
<michael.bla...@ngc.com>wrote:

> I think you may find you're running into buffer cache limits (not sqlite
> but OS limits).
>
>
>
> So the 1st third all fits into buffer cache.  Once it starts committing to
> disk things slow down a LOT.
>
>
>
> Since you're not showing an real times it's hard to say you are any slower
> than anybody else.
>
>
>
> I saw similar behavior on a project I was doing and it all boiled down to
> disk write speed once things started going to disk.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Don V Nielsen [donvniel...@gmail.com]
> Sent: Thursday, February 09, 2012 8:14 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Inserts get slower and slower
>
> I've noticed a similar thing happening.  The first 1/3rd loads quickly; the
> remain 2/3rds stagnates.  It appears that there is some kind of bottleneck
> happening.  I thought it was the SAN.
>
> My application begins a transaction, does all its inserts, and then
> commits.  There could be millions in the transaction.  Would it be better
> processing to commit in batches, say 250m or 500m?
>
> Now's the time for me to make these changes, as the application is being
> prep'd for production.
>
> dvn
>
> On Wed, Feb 8, 2012 at 4:29 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> >
> > On 8 Feb 2012, at 10:22pm, Oliver Peters wrote:
> >
> > > It's the Primary Key that you're using cause for every INSERT it is
> > checked if unix_time is already present in a record.
> > >
> > > So the question is if you really need unix_time as a PK
> >
> > If you're batching your INSERTs up into transactions, try doing a VACUUM
> > after each COMMIT.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserts get slower and slower

[Black, Michael (IS)]

I think you may find you're running into buffer cache limits (not sqlite but OS 
limits).



So the 1st third all fits into buffer cache.  Once it starts committing to disk 
things slow down a LOT.



Since you're not showing an real times it's hard to say you are any slower than 
anybody else.



I saw similar behavior on a project I was doing and it all boiled down to disk 
write speed once things started going to disk.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Don V Nielsen [donvniel...@gmail.com]
Sent: Thursday, February 09, 2012 8:14 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Inserts get slower and slower

I've noticed a similar thing happening.  The first 1/3rd loads quickly; the
remain 2/3rds stagnates.  It appears that there is some kind of bottleneck
happening.  I thought it was the SAN.

My application begins a transaction, does all its inserts, and then
commits.  There could be millions in the transaction.  Would it be better
processing to commit in batches, say 250m or 500m?

Now's the time for me to make these changes, as the application is being
prep'd for production.

dvn

On Wed, Feb 8, 2012 at 4:29 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 8 Feb 2012, at 10:22pm, Oliver Peters wrote:
>
> > It's the Primary Key that you're using cause for every INSERT it is
> checked if unix_time is already present in a record.
> >
> > So the question is if you really need unix_time as a PK
>
> If you're batching your INSERTs up into transactions, try doing a VACUUM
> after each COMMIT.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserts get slower and slower

[Don V Nielsen]

I've noticed a similar thing happening.  The first 1/3rd loads quickly; the
remain 2/3rds stagnates.  It appears that there is some kind of bottleneck
happening.  I thought it was the SAN.

My application begins a transaction, does all its inserts, and then
commits.  There could be millions in the transaction.  Would it be better
processing to commit in batches, say 250m or 500m?

Now's the time for me to make these changes, as the application is being
prep'd for production.

dvn

On Wed, Feb 8, 2012 at 4:29 PM, Simon Slavin  wrote:

>
> On 8 Feb 2012, at 10:22pm, Oliver Peters wrote:
>
> > It's the Primary Key that you're using cause for every INSERT it is
> checked if unix_time is already present in a record.
> >
> > So the question is if you really need unix_time as a PK
>
> If you're batching your INSERTs up into transactions, try doing a VACUUM
> after each COMMIT.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserts get slower and slower

[Steinar Midtskogen]

Thanks to all for suggestions.

> My guesses:
> - Your unix_time values are not successive. In this case your first fast
> results are due to advantages of memory caching. The following slowness is
> the result of the internal fragmentation

All unix_time values should be successive, but in the particular file
used to create the inserts I discovered some corruptions in one area,
random isolated bytes had been replaced with garbage, including bits
of the timestamps, so this was the cause.  Thanks for getting me on
the right track!

-- 
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserts get slower and slower

[Max Vlasov]

On Thu, Feb 9, 2012 at 2:08 AM, Steinar Midtskogen wrote:

>
> When I build my database from scratch using millions of inserts, one
> table causes problems.  Inserts get slower and slower.  I have about
> 830,000 inserts for that table.  It gets to 300,000 pretty fast, but
> then it gets slower and slower, and eventually it will only do a few
> inserts per second, and I then I have to kill sqlite3 as it will run
> for hours if not days.
>


My guesses:
- Your unix_time values are not successive. In this case your first fast
results are due to advantages of memory caching. The following slowness is
the result of the internal fragmentation
- You have successive unix_time, but when it goes beyond 32-bit limit your
software wraps around 32-bit value instead of going 64 bit.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserts get slower and slower

[Simon Slavin]

On 8 Feb 2012, at 10:22pm, Oliver Peters wrote:

> It's the Primary Key that you're using cause for every INSERT it is checked 
> if unix_time is already present in a record.
> 
> So the question is if you really need unix_time as a PK

If you're batching your INSERTs up into transactions, try doing a VACUUM after 
each COMMIT.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserts get slower and slower

[Oliver Peters]

Am 08.02.2012 23:08, schrieb Steinar Midtskogen:

Hello

I'm having trouble with one table in my database.

When I build my database from scratch using millions of inserts, one
table causes problems.  Inserts get slower and slower.  I have about
830,000 inserts for that table.  It gets to 300,000 pretty fast, but
then it gets slower and slower, and eventually it will only do a few
inserts per second, and I then I have to kill sqlite3 as it will run
for hours if not days.  The -echo option reveals that it gets slower
and slower.  sqlite3 runs at 100% CPU.

I create other similar tables with 830,000 inserts the same way, but
inserts into them don't slow down.

The table in question is:

CREATE TABLE Voksenlia2 (
  temp_in REAL,
  pressure REAL,
  rh_in REAL,
  temp_in_2 REAL,
  temp_in_3 REAL,
  temp_in_4 REAL,
  temp_in_5 REAL,
  temp_ground_0cm REAL,
  temp_ground_10cm REAL,
  temp_ground_20cm REAL,
  temp_ground_50cm REAL,
  radiation INTEGER,
  radiation_2 INTEGER,
  uv REAL,
  temp_uv REAL,

  unix_time INTEGER, PRIMARY KEY (unix_time)
);

The commands start this way:
begin;
insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null, 
null, null, null, null, null, null, null, null, 1072915200);
insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null, 
null, null, null, null, null, null, null, null, 1075063152);

and eventually end in a "commit".  So only one transaction.

I've tried:

* PRAGMA synchronous=OFF.
* Create the database file in /dev/shm/ (RAM disk).
* Break up the inserts into several transactions.
* Create and fill this table before everything else.
* Upgrade from version 3.4.2 to 3.7.10.

Nothing has made any difference.  Any ideas?  Anything I could try or
any ways to debug this?




It's the Primary Key that you're using cause for every INSERT it is 
checked if unix_time is already present in a record.


So the question is if you really need unix_time as a PK

greetings
oliver
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users