> Are you using indices? Do you have an integer primary key
> on the table, but the data is not inserted in key order?
> Do you delete and insert records a lot?
Yes, we do have indices on a combination of fields in the table and we
do not guarantee insert of the data in key order. However, we do not do
(much) deletes. And, as you point out, I have seen severe database
fragmentation.
What is worse is that VACUUM didn't really help that much. It takes
forever, and it doesn't really "fix" the fragmentation either.
As suggested by another reply, I have been focusing my time on analyzing
the right combination of page size and cache size.
-Abhitesh.
-----Original Message-----
From: Michael Scharf [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 11, 2007 1:23 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] File size issue?
Hi,
> I am running into some issues that seem related to the current
database
> file size.
I think it has to do with the file system cache: if you
database is small, the entire database is held in your
systems file cache. Once the database exceeds a certain
size, real disk operations have to be done to access the
data.
SQLite is not very good in managing huge amounts of data
in the database. When you are in the "slow mode", check
your CPU usage and disk usage. I'd bet that CPU is very
low and the disk is seeking a lot....
Regular VACUUM might help. Because it puts the data into
a good order on disk.
Are you using indicees? Do you have an integer primary key
on the table, but the data is not inserted in key order?
Do you delete and insert records a lot?
Those operations can lead to heavy fragmentation of your
database.
The calculation is simple: suppose you have disk with
10ms average seek time and 30Mb/s read speed and
a database of 4Gb. It takes about 130 sec to read
the entire database. In the same time you can
do 13000 seeks (in the worst case of fragmentation).
Unfortunately the SQLite tables are not optimized
for disk access. Records are accessed in key
order which can be much more expensive than to
access the table or index in disk-order.
So, VACUUM brings the key order close to the
disk order and therefore your database is much
faster. However, if you wait to long to do the
vacuum (and your database is extremely fragmented)
vacuum might "take forever", because every record
access involves a seek operation. I'm not sure what
happens to huge indicees after VACUUM. Maybe they
are fragmented again. But I don't know.
That's at least my theory after experimenting with SQLite
quite a bit. I haven't tested 3.3.15 which seems to
have some performance improvements...
Michael
--
http://MichaelScharf.blogspot.com/
------------------------------------------------------------------------
-----
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
-----
<DIV><FONT size="1">
E-mail confidentiality.
--------------------------------
This e-mail contains confidential and / or privileged information belonging to
Spirent Communications plc, its affiliates and / or subsidiaries. If you are
not the intended recipient, you are hereby notified that any disclosure,
copying, distribution and / or the taking of any action based upon reliance on
the contents of this transmission is strictly forbidden. If you have received
this message in error please notify the sender by return e-mail and delete it
from your system. If you require assistance, please contact our IT department
at [EMAIL PROTECTED]
Spirent Communications plc,
Spirent House, Crawley Business Quarter, Fleming Way, Crawley, West Sussex,
RH10 9QL, United Kingdom.
Tel No. +44 (0) 1293 767676
Fax No. +44 (0) 1293 767677
Registered in England Number 470893
Registered at Spirent House, Crawley Business Quarter, Fleming Way, Crawley,
West Sussex, RH10 9QL, United Kingdom
Or if within the US,
Spirent Communications,
26750 Agoura Road, Calabasas, CA, 91302, USA.
Tel No. 1-818-676- 2300
</FONT></DIV>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
