5 ms·

An explanation of what this does? Also, I'm assuming this has to be done after every update, and since Spotify auto-updates, not so convenient...

philipkglass 10y ago
I didn't read all of the original thread but apparently Spotify is overusing sqlite VACUUM, causing a modest amount of data to turn into a huge write volume. https://sqlite.org/lang_vacuum.html https://sqlite.org/lang_vacuum.html
peeters 10y ago
I'm more familiar with Postgres, but it basically instructs the table storage provider to clean up unused disk. Think of it roughly as defragmentation. You're trading work (in the form of writes and CPU) for reclaimed space. Unless SQLite has some semantics around it, replacing it with "xxxxxx;" will likely just cause it to run a bad command...might be more correct to replace it with something like "------;" (comment characters).
- boulos 10y ago
  I was going to say "SELECT 1;" but it's two characters too many. Will all comment characters evaluate to whatever VACUUM does?
cosmie 10y ago
It likely just stops Spotify from VACUUM'ing the SQLite database it has. Spotify stores a local SQLite database file (by default on a Mac it's at ~/Library/Application Support/Spotify/PersistentCache/mercury.db ) The file is only about ~100MB (on my computer). With databases, you issue a vacuum command to defragment a database (reclaim space from deleted/updated rows, re-sort the data, etc). SQLite's VACUUM behavior basically just recreates the entire database from scratch in a temp file, then replaces the live file with that[1]. It makes the file smaller and more efficient to run queries against. Spotify must be triggering a VACUUM statement too aggressively (possibly after every change), rather than on a periodic schedule or after a certain amount of fragmentation. By breaking the VACUUM statement with this solution, you prevent the database from getting recreated so frequently. The file itself will grow slowly (since you're not reclaiming free space like a VACUUM would) and possibly slow down queries against the database, but that sounds like a far better tradeoff than excessive wear on the drive from recreating the file so often. [1]https://sqlite.org/lang_vacuum.html https://sqlite.org/lang_vacuum.html
- pasbesoin 10y ago
  Years ago, manually vacuuming the several SQLite databases that Firefox used (uses) was often cited as a way to speed up Firefox, meaning particularly an instance/profile that had been heavily used for some time. Not sure about these days, though the other week something had me in Bleachbit (IIRC) and I saw that doing so is still one of that application's selectable functions.
- ChoGGi 10y ago
  Once you apply that fix you can manually vacuum with sqlite3 mercury.db vacuum; from https://sqlite.org/download.html https://sqlite.org/download.html
- thirdsun 10y ago
  That sounds like an incredibly easy thing for Spotify to fix - how is this still an issue? On the other hand it fits the picture: Their clients/apps on multiple platforms are very underwhelming. The abandoned rdio apps back from 2014 (or when did they close?) would offer a better experiment in almost any aspect.
  - verytrivial 10y ago
    Agreed. if (time_since_last_vacuum > 3600 /* seconds */) { go_hogwild_on_io(); } else { be_nice(); }
  - 72deluxe 10y ago
    Yes, this is an incredibly easy fix. Perhaps it is me but sometimes it seems that nobody wants to write decent software anymore. I have seen software from giants like Cisco and BT that is abysmal. It is disheartening to those of us who enjoy writing decent software, particularly decent native software.
  - cosmie 10y ago
    I can't find the article right now, but I read a while ago that the Spotify client applications are modularized, such that different feature teams can push updates to their individual feature without breaking or being interdependent on other parts of the clients releasing in sync. I'm not sure what the mercury database is used for or if it's maintained by one of those "feature teams" or is part of the core lib, but that lack of central oversight (and responsibility) could be one reason why such an easy fix isn't so easy to get fixed.

philipkglass 10y ago

I didn't read all of the original thread but apparently Spotify is overusing sqlite VACUUM, causing a modest amount of data to turn into a huge write volume. https://sqlite.org/lang_vacuum.html https://sqlite.org/lang_vacuum.html

peeters 10y ago

I'm more familiar with Postgres, but it basically instructs the table storage provider to clean up unused disk. Think of it roughly as defragmentation. You're trading work (in the form of writes and CPU) for reclaimed space. Unless SQLite has some semantics around it, replacing it with "xxxxxx;" will likely just cause it to run a bad command...might be more correct to replace it with something like "------;" (comment characters).

boulos 10y ago
I was going to say "SELECT 1;" but it's two characters too many. Will all comment characters evaluate to whatever VACUUM does?

cosmie 10y ago

It likely just stops Spotify from VACUUM'ing the SQLite database it has. Spotify stores a local SQLite database file (by default on a Mac it's at ~/Library/Application Support/Spotify/PersistentCache/mercury.db ) The file is only about ~100MB (on my computer). With databases, you issue a vacuum command to defragment a database (reclaim space from deleted/updated rows, re-sort the data, etc). SQLite's VACUUM behavior basically just recreates the entire database from scratch in a temp file, then replaces the live file with that[1]. It makes the file smaller and more efficient to run queries against. Spotify must be triggering a VACUUM statement too aggressively (possibly after every change), rather than on a periodic schedule or after a certain amount of fragmentation. By breaking the VACUUM statement with this solution, you prevent the database from getting recreated so frequently. The file itself will grow slowly (since you're not reclaiming free space like a VACUUM would) and possibly slow down queries against the database, but that sounds like a far better tradeoff than excessive wear on the drive from recreating the file so often. [1]https://sqlite.org/lang_vacuum.html https://sqlite.org/lang_vacuum.html

pasbesoin 10y ago
Years ago, manually vacuuming the several SQLite databases that Firefox used (uses) was often cited as a way to speed up Firefox, meaning particularly an instance/profile that had been heavily used for some time. Not sure about these days, though the other week something had me in Bleachbit (IIRC) and I saw that doing so is still one of that application's selectable functions.
ChoGGi 10y ago
Once you apply that fix you can manually vacuum with sqlite3 mercury.db vacuum; from https://sqlite.org/download.html https://sqlite.org/download.html
thirdsun 10y ago
That sounds like an incredibly easy thing for Spotify to fix - how is this still an issue? On the other hand it fits the picture: Their clients/apps on multiple platforms are very underwhelming. The abandoned rdio apps back from 2014 (or when did they close?) would offer a better experiment in almost any aspect.
- verytrivial 10y ago
  Agreed. if (time_since_last_vacuum > 3600 /* seconds */) { go_hogwild_on_io(); } else { be_nice(); }
- 72deluxe 10y ago
  Yes, this is an incredibly easy fix. Perhaps it is me but sometimes it seems that nobody wants to write decent software anymore. I have seen software from giants like Cisco and BT that is abysmal. It is disheartening to those of us who enjoy writing decent software, particularly decent native software.
- cosmie 10y ago
  I can't find the article right now, but I read a while ago that the Spotify client applications are modularized, such that different feature teams can push updates to their individual feature without breaking or being interdependent on other parts of the clients releasing in sync. I'm not sure what the mercury database is used for or if it's maintained by one of those "feature teams" or is part of the core lib, but that lack of central oversight (and responsibility) could be one reason why such an easy fix isn't so easy to get fixed.