ToolsDB is a heavily loaded shared database. Combining many small users to a shared database makes some sense, but we should move some of its larger/heavier users to separate instances. Doing it via OpenStack Trove seems like the best solution.
Still to sort out: backups, replication?, how to enable self-provisioning of a new db by a Toolforge maintainer
ToolsDB databases ordered by size: https://tool-db-usage.toolforge.org/
Ideally we would migrate all users off of toolsdb onto Trove or something else. Anyone who isn't using any esoteric "features" of ToolsDB and who can migrate should do so.
I would like to Trove to have replication and/or backup solutions available before we move much user data there. The existing toolsdb has both backups AND replication so it's a step-backwards in terms of resiliency until we have Trove spruced up.
I opened T326754: Clarify Trove and Toolsdb usage within WMCS, as per the description, I think what was suggested here has largely come to pass. Thoughts?
I think this task can still be useful as a parent task for any migration we want to do from ToolsDB to Trove.
I propose the scope for this task becomes:
As of today, data from https://tool-db-usage.toolforge.org/
Total number of tools is 475.
| Size under | 10 MB | 100 MB | 500 MB | 1 GB | 5 GB | 10 GB | 25 GB | 50 GB |
| Number of Tools | 235 | 337 | 391 | 407 | 433 | 450 | 464 | 468 |
| Percentage of Tools | 49.47% | 70.95% | 82.32% | 85.68% | 91.16% | 94.74% | 97.68% | 98.53% |
As the table suggest, more than 90% of tools fall under the 5GB limit, and almost 95% fall under the 10GB Limit.
Choosing a 10GB cutoff would mean a migration of 25 tools to trove. A 25GB cutoff would mean a migration of 11 tools to trove.
Another early candidate for this should be https://tool-db-usage.toolforge.org/owner/s51412
This is an Epic that will require multiple quarters. I'm removing the milestone cloud-services-team (FY2024/2025-Q1-Q2) from this task, and adding that milestone to two subtasks of this one that I can realistically start in this quarter:
This is a snapshot of the current top DBs by size:
root@tools-db-4:/srv/labsdb/binlogs# sudo du -chs /srv/labsdb/data/* | sort -rh | head 2.0T total 281G /srv/labsdb/data/s53220__quickstatements_p 197G /srv/labsdb/data/s51434__mixnmatch_p 183G /srv/labsdb/data/s53685__editgroups 103G /srv/labsdb/data/ibdata1 85G /srv/labsdb/data/s51412__data 83G /srv/labsdb/data/s51698__yetkin 72G /srv/labsdb/data/s55078__wdrc_p 70G /srv/labsdb/data/s51114__enwp10 58G /srv/labsdb/data/s53952__freebase_p
This was a similar snapshot recorded in 2022 (T301967):
aborrero@clouddb1001:~$ sudo du -cks /srv/labsdb/data/* | sort -rn | head 2566525640 total 1151204360 /srv/labsdb/data/s51230__linkwatcher 151231476 /srv/labsdb/data/s51434__mixnmatch_p 129605716 /srv/labsdb/data/s53220__quickstatements_p 107163660 /srv/labsdb/data/ibdata1 100784876 /srv/labsdb/data/s53685__editgroups 63725868 /srv/labsdb/data/s51114__enwp10 60054664 /srv/labsdb/data/s53952__freebase_p 50662520 /srv/labsdb/data/s51499__wikiminiatlas 50048072 /srv/labsdb/data/s51698__yetkin
The total has decreased from 2.5 to 2.0 TB, because we migrated linkwatcher to Trove in T328691.
But we can see quite a big increase in some of the other DBs.
I'm confused by the fact that https://tool-db-usage.toolforge.org/ is reporting different numbers for some DBs, but the top 3 ones are the same:
| Owner | Tables | Rows | Size |
| tools.quickstatements (s53220) | 4 | 461474862 | 274.5 GB |
| tools.mix-n-match (s51434) | 139 | 626236183 | 216.7 GB |
| tools.editgroups (s53685) | 21 | 110539386 | 178.5 GB |
| tools.superyetkin (s51698) | 18 | 1033118016 | 88.2 GB |
| tools.imagehash (s55462) | 17 | 550728159 | 74.9 GB |
| tools.wdrc (s55078) | 9 | 336827869 | 70.1 GB |
| tools.persondata (s51412) | 72 | 577292138 | 64.5 GB |