Wp-calypso: Huge sqlite write-ahead-log (Safari) rendering computer unusable

[dllh]

I do not have a reliable repro for this, but I do have a customer in #1400406-zen who has a 200+GB sqlite file associated with wordpress.com that has essentially destroyed their ability to use their computer.

For a while now, they've had a sluggish calypso and memory issues, plus intermittent post edit failures. We've not been able to repro or point to a specific cause for these generic issues, but this week, the customer wrote back to us in a panic. Their system has become unusable, their time machine backup is hosed, and Safari bogs down and becomes unusable when they start it back up. I could imagine that a huge file like this might impact browser and memory performance as they work on their site.

They've reinstalled OS apparently, and the file keeps coming back. It's a little hard to get information that feels super consistent or reliable from the customer, but I do have this, which is at least incontrovertible as evidence that something is broken:

The big file appears to be a sqlite write-ahead log. I gather we're using localForage or something similar that in Safari is using sqlite for storage. And I gather that sometimes things can prevent that log from being purged. In theory, the log is cleaned up later by the system if there's an issue, but in this customer's case, it seems to keep coming back. They report that yesterday, it suddenly decreased in size but then started building right back up. They also report that they've done a system reinstall and the file came back, and that in fact it has destroyed their time machine backups.

My hope is that maybe there's something simple we're failing to do to clean up writes somewhere, and that we can close some connection somewhere or something and solve this.

Things we know:

• The site is an Atomic site.
• The customer has had a never-ending stream of issues ever since the site moved to Atomic back in March, and this is just the latest in a long and dizzying history of issues.
• They're on a MacBook Pro 2015 running High Sierra and Safari.
• The sqlite files in question are in /Users/<username>/Library/Safari/Databases/___IndexedDB/
• Their site has thousands of posts and dozens of users and many many edits daily and a complicated scheduling/publication routine.
• My write-ahead-log for wordpress.com when logged into the site was around 648MB but then jumped down to near 0 when I logged out (I had spent time over the past few months clicking around on the site in Safari, and I suspect this was the cumulative logging from that activity). Then when I logged back in and started clicking around to view posts, etc., it climbed fairly quickly, by a MB or so each time I clicked into a post in the editor, for example. After clicking around for just a couple of minutes to list comments and load a few posts in the editor, my log is back up to 11MB. I could well imagine that somebody working as diligently as the site owner does could very rapidly generate a large log file.
• The sqlite database itself doesn't grow a whole lot -- it's the write-ahead-log that grows. Their actual sqlite database is about 48MB and mine is more like 600k.

empathy #reallife #anxiety #stresscase #painpoint #user-report

[mirrory]

Hello,
I found this, seems it could be relevant.
https://github.com/localForage/localForage/issues/811

[dllh]

Also possibly relevant: #, #. These mention iOS specifically, but they do seem to describe the same issue and to be webkit-specific.

Any chance we could get a dev to spend 5 or 10 minutes with this? We've got a customer in a very bad state, and I'd like to be able to give them an update re the likelihood that this is something we can fix.

[tofumatt]

Hey there, I'm luckily both an Automattician and the creator of localForage. Unluckily this seems to be a vendor bug that's been around for some time that we haven't really been able to figure out from our end. I think if you run localforage._dbInfo.db.close() you might be able to reduce disk space, but it's just a report from some users.

It might be worth suggesting to this user that they run localforage._dbInfo.db.close() in their browser console on wordpress.com, but aside from that (and deleting the WAL file with Safari closed and then emptying their trash, which it sounds like they've already tried), I'm not sure what to suggest as a fix.

[mohshraim]

you have two option. (work around)
1- on load page before init your db instance wait 1-2 seconds to enable the GC to clear your wal file.
2- add button for your client to clear this
-> button should close all indexedDB instances -all of them-
-> after close indexedDB instances wait according to wal file size from 2-10 seconds (maybe more time because your wal file is so big) then reload your application page. or re init your indexedDB instance...
during the delay GC of sql-light should clear your wal file.

for me i use option 2; tested and work perfect.

[dllh]

@tofumatt thank you (thanks to you too, @mohshraim). When I try calling localforage._dbInfo.db.close(), I get a reference error for localforage. Any tips for getting past that? Also, when you say it's a vendor issue, just to make sure I'm clear, do you mean it's an issue with sqlite or with Safari?

[tofumatt]

Hmmm, it could be that localforage isn't attached to the global window object on WordPress.com because it's loaded with webpack.

Indeed, I can see it's not in scope when I open it… you'd need to load a script to run this code 😢

So running:

var head = document.getElementsByTagName('head').item(0);
var script = document.createElement('script');
script.setAttribute('src', 'https://cdn.jsdelivr.net/gh/localforage/localforage/dist/localforage.min.js');
head.appendChild(script);

script.onload = function() {
  localforage._dbInfo.db.close();
}

should work, but it's a lot to ask a regular user to try out 🤷‍♂️

And I think the issue is with Safari, but I honestly don't know. I haven't seen the issue in Chrome or outside of localForage, but maybe it's just because we don't tend to use SQLite outside Safari.

[mohshraim]

Just Note;
This problem also exist on iOS (Chrome, Safari and any application using IndexedDB)

[dllh]

Hmm, still getting a null error when I try this. I have localforage now, but when I inspect it, the dbInfo member is null.

[tofumatt]

Oh, maybe you could try:

var head = document.getElementsByTagName('head').item(0);
var script = document.createElement('script');
script.setAttribute('src', 'https://cdn.jsdelivr.net/gh/localforage/localforage/dist/localforage.min.js');
head.appendChild(script);

script.onload = function() {
  var config = {
    name: 'calypso',
    storeName: 'calypso_store',
    version: 1.0,
    description: 'Calypso Browser Storage',
    driver: [ localforage.INDEXEDDB, localforage.WEBSQL, localforage.LOCALSTORAGE ],
  };
  localforage.config(config);
  localforage.ready().then(function() {
    localforage._dbInfo.db.close();
  });
}

Maybe that'll work? It could be that localForage hasn't loaded the database yet.

[tofumatt]

Just an FYI that WebKit has closed the upstream issue as fixed with a patch 👍

https://bugs.webkit.org/show_bug.cgi?id=191294#c13

[stale[bot]]

This issue has been marked as stale and will be closed in seven days. This happened because:

• It has been inactive in the past 9 months.
• It isn't a project or a milestone, and hasn’t been labeled `[Pri] Blocker`, `[Pri] High`, `[Status] Keep Open`, or `OSS Citizen`.

You can keep the issue open by adding a comment. If you do, please provide additional context and explain why you’d like it to remain open. You can also close the issue yourself — if you do, please add a brief explanation.

[blowery]

We're sort of seeing this again on Safari 13.0.4. The wal file grows until the page reloads and sometimes survives page reload.

Closing the db after each transaction does not appear to help, but we may need to wait longer....