I am creating a tool to assist in T175096: Identify tools hosting databases on labsdb100[13] and notify maintainers that will show information about the schemas and tables that are owned by tools and user accounts. The basic query that I am using to do this is:
SELECT SUBSTRING_INDEX(table_schema, '_', 1) as owner , SUM( data_length ) as data_bytes , SUM( index_length ) as index_bytes , SUM( table_rows ) as row_count , COUNT(1) as tables FROM information_schema.TABLES WHERE table_schema regexp '^[psu][0-9]' GROUP BY owner ORDER BY data_bytes DESC
When this query is run as my Toolforge user (u3518) it finds 72 distinct owners:
(u3518@c1.labsdb) [information_schema]> SELECT
-> SUBSTRING_INDEX(table_schema, '_', 1) as owner
-> , SUM( data_length ) as data_bytes
-> , SUM( index_length ) as index_bytes
-> , SUM( table_rows ) as row_count
-> , COUNT(1) as tables
-> FROM information_schema.TABLES
-> WHERE table_schema regexp '^[psu][0-9]'
-> GROUP BY owner
-> ORDER BY data_bytes DESC;
+--------------+-------------+-------------+-----------+--------+
| owner | data_bytes | index_bytes | row_count | tables |
+--------------+-------------+-------------+-----------+--------+
| u3532 | 38337232896 | 18483609600 | 860150285 | 46 |
| u4817 | 26288865280 | 25651920896 | 207974812 | 35 |
| u2041 | 19788595200 | 10820780032 | 442218836 | 7 |
| p50380g50816 | 18500958436 | 18174479360 | 171328358 | 30 |
| p50380g50494 | 9897607168 | 9147940864 | 73522968 | 17 |
| s51187 | 8993101684 | 1388544 | 96923325 | 384 |
| s51362 | 7441645568 | 23410786304 | 83411345 | 16 |
| p50380g50692 | 5351828488 | 2482273280 | 153929591 | 54 |
| p50380g50921 | 5262351008 | 3673131008 | 97759098 | 21 |
| u2815 | 5219706131 | 2856213504 | 154563899 | 84 |
| u12219 | 2914885632 | 243286016 | 66134870 | 18 |
| s51111 | 2341691392 | 2183348224 | 43533672 | 73 |
| s52690 | 2295183008 | 1088602112 | 62265427 | 8 |
| s53311 | 2244091904 | 196001792 | 51960598 | 8 |
| p50380g50900 | 1892417536 | 0 | 27826983 | 4 |
| s52467 | 1704787968 | 1174929408 | 11345849 | 7 |
| p50380g50491 | 1493155840 | 739557376 | 30958281 | 105 |
| u3945 | 1239744512 | 132603904 | 9480639 | 3 |
| s51172 | 1212006400 | 466026496 | 27961097 | 1 |
| u11049 | 1210769408 | 24576 | 7700146 | 3 |
| s51454 | 1101627392 | 380420096 | 18270590 | 26 |
| u3570 | 1090535424 | 1416429568 | 10124817 | 2 |
| s51334 | 582770688 | 275841024 | 13443019 | 1 |
| u3356 | 458457088 | 0 | 5155192 | 3 |
| p50380g50553 | 335986688 | 0 | 7734476 | 5 |
| u3182 | 291061760 | 779517952 | 5220218 | 1 |
| p50380g50497 | 272220160 | 95996928 | 615755 | 159 |
| u2029 | 265289728 | 8192 | 3292637 | 1 |
| u10276 | 170983424 | 8192 | 3944455 | 1 |
| u2043 | 144390548 | 281989120 | 2294366 | 2 |
| s52490 | 136347648 | 111484928 | 971295 | 5 |
| p50380g50824 | 114950144 | 70385664 | 1604745 | 1 |
| u2843 | 89841664 | 124231680 | 1555310 | 1 |
| s51154 | 85114880 | 31645696 | 1038282 | 6 |
| s51306 | 78774272 | 12648448 | 70406 | 2 |
| u2745 | 64585728 | 17350656 | 527908 | 1 |
| u2402 | 42795008 | 303104 | 196733 | 4 |
| p50380g50728 | 40646600 | 8120320 | 355663 | 21 |
| s51127 | 38398876 | 33644544 | 238865 | 14 |
| p50380g50838 | 30367288 | 20283392 | 533152 | 1 |
| s51058 | 23281664 | 8192 | 500270 | 1 |
| u4974 | 20480000 | 10821632 | 319211 | 1 |
| p50380g50551 | 18808641 | 11518976 | 82714 | 12 |
| s51698 | 16973824 | 49152 | 331992 | 5 |
| s52365 | 16711520 | 32295936 | 45388 | 49 |
| p50380g50592 | 15220736 | 0 | 65275 | 1 |
| s52107 | 9977856 | 0 | 43835 | 1 |
| u3617 | 9945088 | 204800 | 194754 | 9 |
| u2248 | 9166848 | 1040384 | 108205 | 4 |
| u3949 | 8421376 | 0 | 151124 | 2 |
| s52037 | 7405568 | 770048 | 52757 | 4 |
| s51892 | 4691430 | 5875712 | 93782 | 11 |
| s51073 | 2637824 | 0 | 22381 | 1 |
| p50380g50512 | 2507324 | 2019328 | 4709 | 49 |
| s52524 | 2465792 | 8192 | 46148 | 1 |
| u3533 | 2170880 | 2154496 | 13208 | 4 |
| s52744 | 1081344 | 0 | 874 | 1 |
| u4301 | 802816 | 0 | 2643 | 2 |
| s51329 | 540672 | 0 | 4195 | 4 |
| s51755 | 106496 | 98304 | 451 | 6 |
| s53381 | 98304 | 98304 | 429 | 6 |
| s53024 | 57344 | 8192 | 1000 | 1 |
| s53012 | 49152 | 16384 | 335 | 1 |
| p50380g40014 | 16384 | 0 | 47 | 1 |
| s51055 | 16384 | 0 | 0 | 1 |
| s53463 | 16384 | 0 | 69 | 1 |
| u2117 | 16384 | 0 | 0 | 1 |
| s51206 | 16384 | 16384 | 8 | 1 |
| s51223 | 16384 | 16384 | 24 | 1 |
| u3214 | 16384 | 0 | 0 | 1 |
| s53151 | 8192 | 8192 | 0 | 1 |
| s51417 | 8192 | 8192 | 0 | 1 |
+--------------+-------------+-------------+-----------+--------+
72 rows in set, 3 warnings (15.21 sec)When I run the same query as the tool account (s53508), it only finds 25 owners:
MariaDB [information_schema]> SELECT
-> SUBSTRING_INDEX(table_schema, '_', 1) as owner
-> , SUM( data_length ) as data_bytes
-> , SUM( index_length ) as index_bytes
-> , SUM( table_rows ) as row_count
-> , COUNT(1) as tables
-> FROM information_schema.TABLES
-> WHERE table_schema regexp '^[psu][0-9]'
-> GROUP BY owner
-> ORDER BY data_bytes DESC;
+--------------+-------------+-------------+-----------+--------+
| owner | data_bytes | index_bytes | row_count | tables |
+--------------+-------------+-------------+-----------+--------+
| u2041 | 19788578816 | 10820780032 | 442218826 | 6 |
| p50380g50692 | 5351828488 | 2482273280 | 153929591 | 54 |
| p50380g50921 | 5262351008 | 3673131008 | 97759098 | 21 |
| u12219 | 2914885632 | 243286016 | 66134870 | 18 |
| u2815 | 2650903816 | 2105239552 | 73758263 | 40 |
| s51111 | 2341691392 | 2183348224 | 43533672 | 73 |
| s53311 | 2244091904 | 196001792 | 51960598 | 8 |
| s52690 | 1317254816 | 431308800 | 41395499 | 3 |
| p50380g50491 | 1167433728 | 732028928 | 24211629 | 58 |
| s51334 | 582770688 | 275841024 | 13443019 | 1 |
| s51454 | 430841856 | 248389632 | 8796653 | 9 |
| u3182 | 291061760 | 779517952 | 5220218 | 1 |
| s52490 | 136347648 | 111484928 | 971295 | 5 |
| s51306 | 78774272 | 12648448 | 70406 | 2 |
| u2402 | 42795008 | 303104 | 196733 | 4 |
| p50380g50728 | 40646600 | 8120320 | 355663 | 21 |
| s51127 | 26271744 | 26542080 | 109292 | 3 |
| u4974 | 20480000 | 10821632 | 319211 | 1 |
| p50380g50592 | 15220736 | 0 | 65275 | 1 |
| s51892 | 1238011 | 1944576 | 27306 | 6 |
| s53024 | 57344 | 8192 | 1000 | 1 |
| s53012 | 49152 | 16384 | 335 | 1 |
| s51206 | 16384 | 16384 | 8 | 1 |
| s51223 | 16384 | 16384 | 24 | 1 |
| s53463 | 16384 | 0 | 69 | 1 |
+--------------+-------------+-------------+-----------+--------+
25 rows in set, 1 warning (1.04 sec)The difference in these collections seems to be whether or not the table_schema ends in _p. I would like this tool to be able to see all of the information_schema.TABLES rows and not just the ones that end in _p. It would be nice to have this ability on labsdb1001, labsdb1003, and labsdb1005.