Thanks for the pointers Kees. I'll keep those in mind when I go into trying to 
optimize the database.

I was clearly way off track when I said I was perfectly aware of the issues 
concerning the database. I completely forgot about taking the spin factor into 
account as you and Jim have pointed out to me. Thanks again.

JP

-----Original Message-----
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Kees Nuyt
Sent: 16. júní 2009 15:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Database inserts gradually slowing down

On Tue, 16 Jun 2009 14:23:47 +0000, Jens Páll Hafsteinsson
<j...@lsretail.com> wrote:

> Yes, I'm perfectly aware of this and hence I
> would expect the disk to be sweating like hell
> running this test while the CPU is relatively
> relaxed (given that sqlite is disk bound in
> this case and not CPU bound).
>
> But this is not happening; neither the disk nor
> the CPU are practically doing anything, which
> is a bit strange. It's as if both the disk and
> the CPU are waiting for each other or that
> sqlite is playing 'nice' behind my back and
> giving up the CPU when it shouldn't.

Apart from seeks, the disk has to spin until the correct
start sector is under the head. Then it can write a database
page, perhaps a few database pages.

There are a few parameters you can use to optimize this:
- PRAGMA page_size
- PRAGMA [default_]cache_size
- the number of INSERTs per transaction
- The schema: INDEX PRIMARY KEY on the first column
  instead of a non-unique index
  (if the application allows it)
- load the database in order of index(es)

Especially a non-unique index with low cardinality has a lot
of overhead.

>JP
-- 
  (  Kees Nuyt
  )
c[_]
_______________________________________________
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

Reply via email to