|
« Return to Thread: Inserts get slower and slower
Re: Inserts get slower and slower
by Black, Michael (IS)
::
Rate this Message:
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-bounces@... [sqlite-users-bounces@...] on behalf of Don V Nielsen [donvnielsen@...]
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 <slavins@...> 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@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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-bounces@... [sqlite-users-bounces@...] on behalf of Don V Nielsen [donvnielsen@...]
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 <slavins@...> 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@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
« Return to Thread: Inserts get slower and slower
| Free embeddable forum powered by Nabble | Forum Help |
