On 4 January 2016 at 16:59, Simon Slavin <slavins at bigfraud.org> wrote:
> The VACUUM function takes so long to run that you cannot get faster > overall. > > Depending on which operating system, file system and storage system you > use, it is possible for VACUUM to increase speed of SELECT a little, but > not much [1]. Certainly not enough to make up for the time VACUUM takes. > To find out the effect on your specific platform you must perform the test > on your specific platform. > To provide another sample point, we've observed substantial improvements in sqlite DB performance (including SELECT statements) after a VACUUM. Our setup is a bit unusual though, as we store all our DBs on a specific file server over NFS (ie. we should probably be using an actual SQL server). Some combination of network latency, optical drives in the NFS server, and a mismatch between sqlite's page size and the disks' block size cause fragmentation to play a significant role in i/o performance. Of course this also mean that VACUUM takes even longer over NFS, so it's absolutely not worth running before every query. But we've seen benefit from using VACUUM as a maintenance step while the DBs are not in use. -Rowan
