[sqlite] How to determine when to VACUUM?
I've been reading the documentation. I've been googling and thinking. Still, I can't figure out the best way to determine when to run the VACUUM-command. Note that I do NOT want to enable "auto vacuum". I do remember reading something about calculating empty space, used pages etc etc. Still, no perfect answer. Q: How do I programmatically (through sqlite-APIs?) determine if it's time to VACUUM a database? In general, what is the best method here? Thanks! /Chris _ Håll skräpposten borta med nya Hotmail. Klicka här! http://explore.live.com/windows-live-hotmail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote: > Q: How do I programmatically (through sqlite-APIs?) determine if it's time to > VACUUM a database? It's never time to VACUUM a database. The VACUUM command is useful only if you have want to recover unused space from the database file. So if your database file once took up 5 Meg, and you deleted a lot of data from it and it now takes up only 2 Meg, you could recover 3 Megabytes of disk space. But how useful is that 3 Megabytes of space to you ? Are you going to use it for something really valuable ? And how long will it be before you get 3 Megabytes more data which will fill it up again ? If you're trying to get the database in shape to make copies, e.g. to burn it on a DVD or send it to customers, or put it on a device with limited space, then there might be some reason to use VACUUM. If not, then it's just a waste of resources. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
What you are saying makes sense. Thanks for your advice! However, I do believe there are times when vacuuming would be beneficial. For instance, if a database for software X is detected to have 90% unused space for a couple of weeks, then why bloat the harddrive? (I don't know how to do that though :P) In my opinion, the user should always have the option to vacuum the database. My goal is to let software X have some logic to give the user a hint when this action would be appropriate. > From: slav...@bigfraud.org > Date: Tue, 6 Jul 2010 22:52:11 +0100 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How to determine when to VACUUM? > > > On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote: > > > Q: How do I programmatically (through sqlite-APIs?) determine if it's time > > to VACUUM a database? > > It's never time to VACUUM a database. The VACUUM command is useful only if > you have want to recover unused space from the database file. So if your > database file once took up 5 Meg, and you deleted a lot of data from it and > it now takes up only 2 Meg, you could recover 3 Megabytes of disk space. But > how useful is that 3 Megabytes of space to you ? Are you going to use it for > something really valuable ? And how long will it be before you get 3 > Megabytes more data which will fill it up again ? > > If you're trying to get the database in shape to make copies, e.g. to burn it > on a DVD or send it to customers, or put it on a device with limited space, > then there might be some reason to use VACUUM. If not, then it's just a waste > of resources. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Messenger i mobilen på 5 sekunder! http://new.windowslivemobile.msn.com/se-SE/Default.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
On Tue, Jul 6, 2010 at 5:58 PM, Kristoffer Danielsson wrote: > > However, I do believe there are times when vacuuming would be beneficial. For > instance, if a database for software X is detected to have 90% unused space > for a couple of weeks, then why bloat the harddrive? (I don't know how to do > that though :P) > > In my opinion, the user should always have the option to vacuum the database. > My goal is to let software X have some logic to give the user a hint when > this action would be appropriate. > It can also be beneficial for performance reasons, although you'd obviously have to do testing to see whether the very-CPU-intensive VACUUM operation is worth the gain in subsequent performance. There are some cases where it clearly is. As for your original question, I would think that you'd want to use some ratio of 'PRAGMA freelist_count;' to 'PRAGMA page_count;' to make your determination: http://sqlite.org/pragma.html Once the number of unused pages gets large enough compared to the total database size, it might be an appropriate time to VACUUM. -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
On 7/6/2010 2:58 PM, Kristoffer Danielsson wrote: > What you are saying makes sense. Thanks for your advice! > > > > However, I do believe there are times when vacuuming would be beneficial. For > instance, if a database for software X is detected to have 90% unused space > for a couple of weeks, then why bloat the harddrive? > If there is lots of free space on the drive, why work at giving it a little bit more? In any event, knowing the database and hard disk usage patterns will lead to much, much, much better criteria for vacuuming than any general rule of thumb. Optimizing plentiful resources is non-optimal. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
On Tue, Jul 06, 2010 at 11:45:18PM +0200, Kristoffer Danielsson scratched on the wall: > > I've been reading the documentation. I've been googling and thinking. > > Still, I can't figure out the best way to determine when to run > the VACUUM-command. Note that I do NOT want to enable "auto vacuum". > > I do remember reading something about calculating empty space, > used pages etc etc. Still, no perfect answer. > > Q: How do I programmatically (through sqlite-APIs?) determine if it's > time to VACUUM a database? In general, what is the best method here? It depends on your needs and goals. VACUUM does two things. First it recovers space. This usually is not significant unless you've just deleted a huge number of rows you are unlikely to replace with other data. Over time, most databases say roughly the same size, or they continually grow. It is also easy to get into trouble if space is so tight you need to VACUUM, because the VACUUM process can easily require free space that is 2x the database size. In other words, you can't VACUUM a 10GB DB file with only 10GB of free space, and you'll usually need something much closer to 20GB (in essence, you need free space to hold a copy of the old and a copy of the new database in addition to the original database file). The other reason to VACUUM is to defragment the database. This happens at the database page level and the file level. VACUUM will "repack" database pages to get better record storage, and it will also rearrange the file so that all the pages that belong to a database object (table, index, etc.) are in the same place. This can greatly improve scan speeds, as the I/O is better. This is more important for somewhat large databases that have a high flux. Overall, I tend to manually VACUUM files when I delete something very large, or when roughly 40% of the contents have changed. I've never done it in code. There are many applications that use utility databases (prefs, configs, and even document files) that just never VACUUM. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
> It's never time to VACUUM a database. This is an interesting statement. In our application, all tables get heavily fragmented with time (99% or more). I was considering VACUUM as a means to defragment, and, presumably, improve search performance. Was I misguided, and search performance does not degrade significantly with the increased fragmentation of the DB? (I guess it well might not on an SSD disk, but on a conventional rotational disk, pager could read several pages ahead with one seek - but does it?) It would be great to see performance comparison, if anyone has ever did it. -- Igor On Wed, Jul 7, 2010 at 1:52 AM, Simon Slavin wrote: > > On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote: > >> Q: How do I programmatically (through sqlite-APIs?) determine if it's time >> to VACUUM a database? > > It's never time to VACUUM a database. The VACUUM command is useful only if > you have want to recover unused space from the database file. So if your > database file once took up 5 Meg, and you deleted a lot of data from it and > it now takes up only 2 Meg, you could recover 3 Megabytes of disk space. But > how useful is that 3 Megabytes of space to you ? Are you going to use it for > something really valuable ? And how long will it be before you get 3 > Megabytes more data which will fill it up again ? > > If you're trying to get the database in shape to make copies, e.g. to burn it > on a DVD or send it to customers, or put it on a device with limited space, > then there might be some reason to use VACUUM. If not, then it's just a > waste of resources. > > Simon. > ___ > 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
Re: [sqlite] How to determine when to VACUUM?
My two cents, to complement other answers: leave it to the user. In case of a client-side GUI app, let the user run some maintenance action, like an OS has "defragment disk" action, or Outlook has "compact folders" action. In case of a server-side app, make a script or admin command to do that. Hope this helps! -- Igor On Wed, Jul 7, 2010 at 1:45 AM, Kristoffer Danielsson wrote: > > I've been reading the documentation. I've been googling and thinking. > > > > Still, I can't figure out the best way to determine when to run the > VACUUM-command. Note that I do NOT want to enable "auto vacuum". > > I do remember reading something about calculating empty space, used pages etc > etc. Still, no perfect answer. > > > > Q: How do I programmatically (through sqlite-APIs?) determine if it's time to > VACUUM a database? In general, what is the best method here? > > > > Thanks! > > /Chris > > _ > Håll skräpposten borta med nya Hotmail. Klicka här! > http://explore.live.com/windows-live-hotmail > ___ > 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
Re: [sqlite] How to determine when to VACUUM?
On 7 Jul 2010, at 10:03am, Igor Sereda wrote: >> It's never time to VACUUM a database. > > This is an interesting statement. In our application, all tables get > heavily fragmented with time (99% or more). I was considering VACUUM > as a means to defragment, and, presumably, improve search performance. > Was I misguided, and search performance does not degrade significantly > with the increased fragmentation of the DB? > > (I guess it well might not on an SSD disk, but on a conventional > rotational disk, pager could read several pages ahead with one seek - > but does it?) You are asking the correct questions, but only you can provide the answers. It depends on things like what proportion of pages are taken up with indexes, how 'clumpy' your data is, etc.. And fragmentation has large effects on disks in some formats (e.g. NTFS) but almost none on disks in other formats (e.g. ext3). I don't think it's possible to get any sort of 'standard answer' to that question which would help the 'standard SQLite user' who is using a 'standard schema'. > It would be great to see performance comparison, if anyone has ever did it. This mailing list tends to gather many examples of premature optimization. The sort of computing we do contains so many different operations and subsystems that it's easy to pick one you know some techie detail about (for example, it's faster to read from the same sector twice) and work out how to optimize that in an ingenious way. However, it turns out that that whole operation is only a very minor part of the entire load on the computers, and that the entire project runs fast and small enough that no optimization is really needed anyway. You waste more time programming and debugging the optimization than you save by it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
> (I guess it well might not on an SSD disk, but on a conventional > rotational disk, pager could read several pages ahead with one seek - > but does it?) You mean that pager should read several pages at once even if it doesn't need them right now but it estimates that it will need them in near future? Pager never does that. It reads pages one-by-one only at the time it first needs them. So probably optimization is not that much as you think it is. Of course I'm not taking into account OS-level caching, though I don't know if it has any impact here. Pavel On Wed, Jul 7, 2010 at 5:03 AM, Igor Sereda wrote: >> It's never time to VACUUM a database. > > This is an interesting statement. In our application, all tables get > heavily fragmented with time (99% or more). I was considering VACUUM > as a means to defragment, and, presumably, improve search performance. > Was I misguided, and search performance does not degrade significantly > with the increased fragmentation of the DB? > > (I guess it well might not on an SSD disk, but on a conventional > rotational disk, pager could read several pages ahead with one seek - > but does it?) > > It would be great to see performance comparison, if anyone has ever did it. > > -- Igor > > > > On Wed, Jul 7, 2010 at 1:52 AM, Simon Slavin wrote: >> >> On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote: >> >>> Q: How do I programmatically (through sqlite-APIs?) determine if it's time >>> to VACUUM a database? >> >> It's never time to VACUUM a database. The VACUUM command is useful only if >> you have want to recover unused space from the database file. So if your >> database file once took up 5 Meg, and you deleted a lot of data from it and >> it now takes up only 2 Meg, you could recover 3 Megabytes of disk space. >> But how useful is that 3 Megabytes of space to you ? Are you going to use >> it for something really valuable ? And how long will it be before you get 3 >> Megabytes more data which will fill it up again ? >> >> If you're trying to get the database in shape to make copies, e.g. to burn >> it on a DVD or send it to customers, or put it on a device with limited >> space, then there might be some reason to use VACUUM. If not, then it's >> just a waste of resources. >> >> Simon. >> ___ >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
On Wed, Jul 07, 2010 at 01:03:26PM +0400, Igor Sereda scratched on the wall: > > It's never time to VACUUM a database. > > This is an interesting statement. In our application, all tables get > heavily fragmented with time (99% or more). I was considering VACUUM > as a means to defragment, and, presumably, improve search performance. > Was I misguided, and search performance does not degrade significantly > with the increased fragmentation of the DB? No, you're not misguided, but the full picture is deeper. The VACUUM will repack the pages, putting more user-data in a smaller footprint. This improves I/O performance since you pull in more usable data with a given number of reads (at least, until things are mixed up again). This will typically only show up when doing large scans of tables that have been really thrashed, however (rows added and deleted and added and deleted over and over and over). VACUUM also puts all the pages of a given database object in the same spot in the file. That will help with reads, but only if the low level block assignments on the disk are continuous. Just because the blocks are next to each other in a file doesn't mean they're next to each other on the disk. In theory, the VACUUM also provides the filesystem an opportunity to defragment the file itself, but it is less clear how much this actually happens. VACUUM rebuilds the database into a temp file and then copies it back, but the copy is done as a low level page-by-page overwrite-- it does not actually delete the old file and then just copy or rename the rebuilt one. This makes the whole operation transaction safe (complete with roll back ability right up to the final moment of success), but it means the original database file stays more or less intact on the filesystem, and is simply over-written one section at a time. Some filesystems will take the chance to merge the file blocks, some will not. If you really want the best performance, your best bet is to copy the file after it has been VACUUMed. Or defragment the filesystem. Or use an auto-defragmenting filesystem. > (I guess it well might not on an SSD disk, but on a conventional > rotational disk, pager could read several pages ahead with one seek - > but does it?) No, the pager does not. Among other things, my feeling is that the locality of pages is not very strong, unless the database was just VACUUMed. Memory for the page cache also tends to be very guarded. On a modern desktop system, it is likely the filesystem and/or the disk controller will do some level of read-ahead, however. Any benefit will likely require contiguous physical layout. Overall, I agree with most others... if you're just starting application development, this should be way way down on your list of concerns. It is likely this concern can even drop off the list, unless you know your files are significant in size, the database lifespan is significant, and the database will be subjected to an extremely high amount of flux and row thrashing. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
On 7 Jul 2010, at 4:14pm, Jay A. Kreibich wrote: >> (I guess it well might not on an SSD disk, but on a conventional >> rotational disk, pager could read several pages ahead with one seek - >> but does it?) > > No, the pager does not. Among other things, my feeling is that the > locality of pages is not very strong, unless the database was just > VACUUMed. Actually the SSD possibility makes it worse, not better. A simplified explanation follows. SSD units use separate sets of circuits for each memory chip. Suppose for the sake of argument that an 80Gig SSD drive contains 10 8Gig memory chips, circuitry around each one, and master circuits to handle the hard drive interface. And suppose you want to write two blocks to this drive and you do it in two different 'fwrite' commands. The first command happens to be for a block which is stored in chip number 3 of 10. The drive accepts the write command, routes the data to chip 3, then tells the OS that it's ready for another command. Suppose that the second command also refers to data stored in chip 3. Well the circuitry around chip 3 is still busy handling the first command. The drive has to wait for that circuitry to become free before it can send the second command to it. On the other hand, suppose that the second write concerns data held in chip 5 instead. Well that can be executed immediately: chip 5 isn't busy. "Well," you ask, "if that's how it works, why don't SSD drives stripe the data so that contiguous blocks are spread over the different chips ?" Well, if a single command is for data that's spread over different blocks, it's faster to send one instruction to one memory chip and handle one long chunk of data then to have to talk to several different chips and assemble the data into one chunk before returning it. So it's swings and roundabouts. So for SSD drives, or any system where a 'drive' is made up of separate storage items, locality can make things worse, not better. Add to that the fact that fragmentation is only really a problem for Windows these days. Windows filesystem drivers rely heavily on read-ahead logic for speed, and both NTFS and WIndows drivers were designed with that in mind. Having been designed with this in mind they operate pretty well and give pretty fast results. On the other hand, Macs and other forms of Unix and Linux systems are hell on fragmentation because all forms of Unix run background daemons which are constantly reading and writing to disk log files and journals irrespective of what your 'main' application is doing. Drivers and file systems designed for systems like that were built with different assumptions and don't degrade much with fragmentation. Not to mention that a few hours after you've run a defragmentation program you'll find you're fragmented again because of all the background stuff that's going on. So the urge towards defragmentation is a typical example of someone who knows a bit about how computers work -- "Fragmentation bad ! Caveman not like !" -- will spend hours on optimization that doesn't save much of anything. And fragmentation is not one unusual example, many things which naively look like they should be optimization actually aren't. So write simple clean code first. Only if you've decided it's not good enough is it worth improving it. And when you write a newer version which is meant to be better, compare the too to check that it really is. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
> >> (I guess it well might not on an SSD disk, but on a conventional > >> rotational disk, pager could read several pages ahead with one seek - > >> but does it?) > > > > No, the pager does not. Among other things, my feeling is that the > > locality of pages is not very strong, unless the database was just > > VACUUMed. > > Actually the SSD possibility makes it worse, not better. > Simon, you gave an interesting explanation, but does this rule work in general? I mean there are many models, many sizes and so on. For example SanDisk SSD used in my Asus T91MT claims it has some internal writing cache, so this controller can have its own logic working independently of the software installed. Also, allowing several chips writing at the same time might have conflict with any OS' own caching mechanism. Besides I'm not sure the caching in any OS is smart enough to take this into account. For example (I'm not sure Windows is the best) but giving the fact that XP didn't have proper partition alignment for SSD and it took some time for enthusiasts to let MS know about this makes me think the cache system in Windows is still not smart enough even for much easier SSD-related tasks :) Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
On 8 Jul 2010, at 9:30am, Max Vlasov wrote: >> Actually the SSD possibility makes it worse, not better. >> > > Simon, you gave an interesting explanation, but does this rule work in > general? I mean there are many models, many sizes and so on. Don't know. You could test it. Write a program that creates a file half the size of the drive, then writes to random parts of it timing each command. If all the write commands take about the same amount of time then it doesn't work the way I described. I just repeated the description I had read of the way SSD drives work. > For example > SanDisk SSD used in my Asus T91MT claims it has some internal writing cache, > so this controller can have its own logic working independently of the > software installed. That 'internal writing cache' is the logic I was describing. > Also, allowing several chips writing at the same time > might have conflict with any OS' own caching mechanism. The OS would not know about this problem at all. The OS doesn't know any thing about the internal organisation of the drive: its problem ends when the drive says "I have accepted this write command from you.". After that it's the drive's responsibility to make sure that the data gets written. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
>> > > > > Simon, you gave an interesting explanation, but does this rule work in > > general? I mean there are many models, many sizes and so on. > > Don't know. You could test it. Write a program that creates a file half > the size of the drive, then writes to random parts of it timing each > command. If all the write commands take about the same amount of time then > it doesn't work the way I described. I just repeated the description I had > read of the way SSD drives work. > Thanks, that what I thought ) SSD is an interesting thing to research especially in sqlite perspective, for example, for reading it's access times that makes SSD winner, so probably reading large sqlite database randomly can have some benefits being used on SSD, but I don't know of any real world measurements. Do you know any? Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
