On Tue, Apr 08, 2025 at 02:41:44PM -0700, Kevin Fenzi via infrastructure wrote: > Instead it seems to be the bounce processor. > This processor wakes up every few minutes and does a query for any > bounces in the bounceevent table that are processed = 'false'. > If it finds any, it processes them. > > However, that table is now 50GB and contains 152167015 rows > (all of them pretty much processed = 'True'). > > From the logs (which logs slow queries), an example: > > 2025-04-08 21:32:40.510 GMT [7073] LOG: duration: 267423.928 ms plan: > Query Text: SELECT bounceevent.id AS bounceevent_id, > bounceevent.list_id AS bounceevent_ > list_id, bounceevent.email AS bounceevent_email, bounceevent.timestamp AS > bounceevent_timestamp, > bounceevent.message_id AS bounceevent_message_id, bounceevent.context AS > bounceevent_context, b > ounceevent.processed AS bounceevent_processed > FROM bounceevent > WHERE bounceevent.processed = false > Gather (cost=1000.00..7441540.83 rows=1 width=137) > Workers Planned: 2 > -> Parallel Seq Scan on bounceevent (cost=0.00..7440540.73 rows=1 > width=137) > Filter: (NOT processed) > > Yes, thats 267seconds to process that query, all the time hammering I/O > because the table is too large to cache well. > > > * Save a copy of the latest database dump that should have that table > backed up. > * 'truncate bounceevent' to wipe it > > Thoughts? +1s? counter proposals?
I suggest creating a hash index on bounceevent.processed, as the process seem only interested in True values. Next step I'd say DELETE FROM bounceevent bounceevent.processed = true; before dropping whole table as last resort. -- Tomasz Torcz 72->| 80->| to...@pipebreaker.pl 72->| 80->| -- _______________________________________________ infrastructure mailing list -- infrastructure@lists.fedoraproject.org To unsubscribe send an email to infrastructure-le...@lists.fedoraproject.org Fedora Code of Conduct: https://docs.fedoraproject.org/en-US/project/code-of-conduct/ List Guidelines: https://fedoraproject.org/wiki/Mailing_list_guidelines List Archives: https://lists.fedoraproject.org/archives/list/infrastructure@lists.fedoraproject.org Do not reply to spam, report it: https://pagure.io/fedora-infrastructure/new_issue
