We're hitting a strange bug in ipsilon : it keeps track of all transactions but never cleans thing up Transaction table :
mysql> select count(*) from transactions; +-----------+ | count(*) | +-----------+ | 102395214 | +-----------+
As there is also no index on it, that single query takes a very long time.
Metadata Update from @arrfab: - Issue tagged with: authentication, centos-ci-infra, centos-common-infra, centos-stream, high-gain, high-trouble, investigation
Metadata Update from @arrfab: - Issue assigned to arrfab
@ngompa : any idea about that issue in ipsilon ? it seems (based on number of open issues upstream) that there is no real maintainer on it but don't see any pointer/doc about a task that would clean things up in the DB itself
Actually tempted to just write a wrapper script that would analyze the DB and query for expired records and just delete from that table (not tested yet)
Analyzed in mysql and tried this to clean things up and also reclaim storage back. I'll modify our role to include this but ideally that would have been documented (and/or fixed ?) at ipsilon side. Here is what will be implemented :
delete from transactions where uuid in ( select uuid from ( select uuid from transactions where name='expiration_time' and value < UNIX_TIMESTAMP() ) x ) ; optimize table transactions ;
Implemented by commit and rolled out.
Gain :
select count(*) from transactions ; +----------+ | count(*) | +----------+ | 31089 | +----------+ 1 row in set (0.08 sec)
So clearly better and then after having optimized table (before ; now ) :
46G May 22 11:37 /var/lib/mysql/ipsilon/transactions.ibd 16M May 22 12:13 /var/lib/mysql/ipsilon/transactions.ibd
Metadata Update from @arrfab: - Issue close_status updated to: Fixed with Explanation - Issue status updated to: Closed (was: Open)
Log in to comment on this ticket.