On 2013/12/11 01:41, veeresh kumar wrote:
Thanks Igor and Simon for your inputs. I was under the impression that VACUUM
would also help performance since it does defragmentation.
Hi Veeresh,
Vacuum does de-fragment the database, it also amalgamates any transaction files and so on - so you are not wrong about that. The
idea that it speeds up the database or improves performance if you will, is not wrong, but mostly negligible.
Consider what is meant by "defragmentation" for a database - the pointer position of a "next page" holding data would be a different
numerical value and/or some transactional data may still reside in a different file altogether, that's all. The things that actually
require processing time (especially within SQLite) are not really affected by this in a meaningful way. This may well have a slight
seek-penalty for the physical hardware even though an un-fragmented DB file might still be very much fragmented on the physical
layer, but the modern drive caches are adequate in size to make the penalty disappear - and if you are using SSD, the point becomes
completely moot.
Further to this, as Simon noted, 50GB is easily handled for SQLite - are your queries taking excessive amounts of time to run (as
opposed to on PostGres/MySQL or such) or is there some other reason you believe it would improve performance? (For instance, does it
improve performance for you currently? - If so, something else might be wrong).
On the other hand, the process of Vacuuming a database file has to do a lot of data moving and consequently takes quite a while -
but unless you have an app running 24/7 (such as Web-Page back-end) this should not matter much.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
