So our install of TTRSS has been slowly getting slower and slower. The DB is hammering the hell out of the disks which is the main culprit, but I decided to look further to see if some of the cleanup wasn’t working, when I popped open the error log to see if anything was showing up, when I got a lot of errors related to foreign key constraints.
I’m wondering if at this point it might be worthwile to OPML export, wipe, import on a clean/new DB. Only thing stopping me from doing that right now is there’s ~54 users, and I will hate my life moving that many people over one-by-one.
Biggest culprit was DELETE FROM ttrss_entries WHERE NOT EXISTS (SELECT ref_id FROM ttrss_user_entries WHERE ref_id = id), which ran for an hour+ before dying with: ERROR: update or delete on table "ttrss_entries" violates foreign key constraint "ttrss_enclosures_post_id_fkey" on table "ttrss_enclosures" DETAIL: Key (id)=(19496237) is still referenced from table "ttrss_enclosures".
what would be the point? unless you figure out the underlying reason for your troubles you’re going to run into the exact same problem eventually.
setting FORCE_ARTICLE_PURGE (days) to a reasonable value would likely help.
read up and configure postgres for performance - i.e. work mem, shared buffers, adjust checkpoint segments, maybe disable synchronous commit and fsync (at least do it until things stabilize). out of the box setup is usually very conservative.
fwiw i was running a ~90 user public setup years ago on a potato athlon with some shit ata disks without any troubles. it wasn’t very fast but it worked without any bottlenecks.
e: you can also temporarily disallow access to tt-rss altogether to prevent locking and clean up manually i.e. delete old stuff (or everything, lol) from ttrss_entries, etc. then force set purging to prevent the database growing too much again.
I’m fairly certain somebody converted this database from MySQL to Postgres at some point, so who knows what they stuffed up in that conversion, and I’m thinking this is mainly the reason for all the foreign key errors
If cleanup was Working As Intended, then the ttrss_entries should have a reasonable count right?
ttrss=# SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'ttrss_entries';
approximate_row_count
-----------------------
3.00408e+06
Shade over 3 million rows seems a bit high for that then.
yeah, this is a lot. maybe some of your users decided to disable purging and it grown naturally from there.
at this point i’d probably just truncate all relevant tables (entries, user_entries, enclosures, etc) for a fresh start, while enforcing purge settings.
foreign key errors could be caused by overall lag causing update processes to clash (if you’re using multiprocess updater), or it could be something else purely database-related. it’s hard to say.