Shrinking a large sqlite DB

[Christopher Marriott]

We have a sqlite DB that has grown to over 800 MB. Consequentially accessing the data via the API has become problematic as the connections regularly time out.

We used to store PDFs in a "cache" - this was the main culprit in terms of database size.  We have since dropped this table. Our sole, remaining data table is only a couple of MBs when downloaded as a CSV - leading me to assume that the other 798 MB of sqlite DB space is waiting to be reclaimed.

Looking at the sqlite documentation there is a command called VACUUM which reclaims space, but it seems that we are "unauthorised" to use this command.

Has anyone any alternative methods for reclaiming space?

[Zarino Zappia]

Aha! That explains the API timeouts you were getting in the other thread.

I'm currently creating a backup, then I'll Vacuum the original file. Burbage (our database server) is heavily loaded right now, so it's taking a while.

ScraperWiki Classic doesn't let end-users perform funky actions like Vacuum and Pragma on databases. FWIW, that's one of the things we changed in ScraperWiki Beta, where you can do whatever you like: http://beta.scraperwiki.com 

Zarino Zappia

VP of Product, ScraperWiki

@zarino

--
You received this message because you are subscribed to the Google Groups "ScraperWiki" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scraperwiki...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Christopher Marriott]

Excellent! Thanks Zarino.

If you could VACUUM both "ofsted_inspection_schools" and "ofsted_inspection_ccs" scrapers (both of which had PDF cache tables that have now been dropped) then that would be great.

If ever I need to look into the beta site it's now, it seems!

[Zarino Zappia]

I vacuumed ofsted_inspection_schools this afternoon. It went down from 800MB to 1.4MB! Don't know whether it's helped though?

We've had other reports of slow API queries, and our database server's pretty busy right now, so that's probably not helping either.

[Christopher Marriott]

Marvellous. Thanks Zarino.

It looks like it has helped, our API request was successful first time asking.

[Zarino Zappia]

Cool, I'm currently vacuuming the ofsted_inspection_ccs scraper too. It was 1.2GB, so I have a feeling this'll take a while.

Z

[Zarino Zappia]

Oh, spoke too soon. All done. From 1.2GB down to 6.8MB :-)

Z

[Christopher Marriott]

Again, great news! Thanks for your help Zarino.