On 4 May 2015, at 9:01pm, Mayank Kumar (mayankum) <mayankum at cisco.com> wrote:
> I am thinking about measuring the performance of sqlite3 write transactions > after lot of delete transactions have been performance but vacuum has not > been performed versus when vacuum is performed. Wanted to get some ideas here > on what people think theoretically should happen. I know it reclaims free > pages, but would this give a dramatic increase in write/update transactions ? Under some circumstances VACUUM can slow things down instead of speed them up. One of the more 'expensive' operations for disk is to extend an existing file to take up more space. The file system has to locate unused sectors, add them to the end of the file, and remove them from the free list. It is far more efficient to let SQLite locate unused pages which are already allocated to its file. So VACUUM freeing up 100 sectors followed by lots of INSERTS occupying 100 new sectors wastes time. The usual strategy is that you might want to use VACUUM before lots of SELECTs but not before lots of operations which change the file. However, because of the numerous different subsystems involved (storage hardware, driver, bandwidth, CPU used, OS, other things being done at the same time) when VACUUM saves or wastes time for you depends on precise details of your setup. Generally speaking you shouldn't need to worry about VACUUM unless disk space is important to you or you have a need to have your program run as fast as possible for some specific operation. Numerous SQLite databases are in use for years without ever once having been VACUUMed. Simon.
