Re: [sqlite] Disk activity on Linux

[Bret Patterson]

Thanks Walter and Paul I'll look into the things you pointed out and let 
you guys know how it turns out. Open Source approval at IBM is version 
specific and the approval process is quite a bit of work so I'd like to 
avoid it as long as possible.

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

Re: [sqlite] Disk activity on Linux

[Paul E]

Bret Patterson  writes:

> 
> We're seeing a lot more disk activity than expected on Linux when using 
> sqlite3. We've run this same series of test on windows and the disk IO is 
> much lower, which is the opposite of what I really expected. Below is
> my scenario and perhaps someone can point out what I can do to fix this 
> problem.

http://www.sqlite.org/pragma.html

Here are some things to try:

Have you increased your page_cache from the default of 2000? You'll want at
least 32Mb of page cache for what you've described.

Try setting temp_store to 2.  This could help depending on what your inserts
look like.

It might help to increase the page_size to 2048 or 4096.

You could try vacuuming your database to see if fragmentation is the issue.

> We are using sqlite version 3.5.7 . 

That release is 1.5 years old. It couldn't hurt to upgrade.



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

Re: [sqlite] Disk activity on Linux

[Walter Dnes]

On Thu, Nov 12, 2009 at 11:24:58AM -0600, Bret Patterson wrote
> We're seeing a lot more disk activity than expected on Linux when
> using sqlite3. We've run this same series of test on windows and
> the disk IO is much lower, which is the opposite of what I really
> expected. Below is my scenario and perhaps someone can point out
> what I can do to fix this problem.

  Since it seems to be a linux issue, what are the mount settings for
the partitions?  This may seem obvious, so please don't take this as an
insult.  Here goes...

  - "noatime" (for all file systems) will reduce disk I/O and speed
things up

  - if using ReiserFS, use the "notail" option

  - the default is that all filesystems are mounted async, but check to
make sure

  Then there are the hard drives.  Use the hdparm utility to confirm
that DMA is turned on.  

  I assume the linux machine is a server, running in text mode, rather
than a resource-heavy GUI.

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