Comment by cosmie | Hacker News Reader

Comment by cosmie | Hacker News Reader

Parent Full thread

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

Once you apply that fix you can manually vacuum with

  sqlite3 mercury.db vacuum;

from https://sqlite.org/download.html

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.

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.

Agreed.

  if (time_since_last_vacuum > 3600 /* seconds */) {
     go_hogwild_on_io();
  } else {
     be_nice();
  }

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.

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.

Reply on news.ycombinator.com