#1413 ipsilon DB clean-up
Closed: Fixed with Explanation a month ago by arrfab. Opened a month ago by arrfab.

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

a month ago

Metadata Update from @arrfab:
- Issue assigned to arrfab

a month ago

@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)

a month ago

Log in to comment on this ticket.