Skip to content

Instantly share code, notes, and snippets.

@phil-hildebrand

phil-hildebrand/connection_info.sql

Last active April 3, 2022 19:15
Show Gist options
  • Save phil-hildebrand/e3f1fce770135a71003ace6a23990d5d to your computer and use it in GitHub Desktop.
Save phil-hildebrand/e3f1fce770135a71003ace6a23990d5d to your computer and use it in GitHub Desktop.
Download ZIP
Handy MySQL information schema / processlist scripts
Raw
connection_info.sql
-- Get Current sessions by state:
SELECT @@hostname as server, user,state,command,count(*)
FROM information_schema.processlist
GROUP BY user,state,command ;
/*
+-------------+-------------+---------------------------------------------------------------+-------------+----------+
| server | user | state | command | count(*) |
+-------------+-------------+---------------------------------------------------------------+-------------+----------+
| dalcmsdb03a | heartbeat | | Sleep | 2 |
| dalcmsdb03a | root | | Sleep | 1 |
| dalcmsdb03a | root | executing | Query | 1 |
| dalcmsdb03a | slave | Master has sent all binlog to slave; waiting for more updates | Binlog Dump | 1 |
| dalcmsdb03a | system user | Slave has read all relay log; waiting for more updates | Connect | 1 |
| dalcmsdb03a | system user | Waiting for master to send event | Connect | 1 |
+-------------+-------------+---------------------------------------------------------------+-------------+----------+
6 rows in set (0.00 sec)
*/
-- Active sessions by state:
SELECT @@hostname as server, user,state,command,time,count(*)
FROM information_schema.processlist
WHERE command not like 'sleep'
GROUP by user,state,command,time
ORDER BY time ASC ;
/*
+-------------+-------------+---------------------------------------------------------------+-------------+---------+----------+
| server | user | state | command | time | count(*) |
+-------------+-------------+---------------------------------------------------------------+-------------+---------+----------+
| dalcmsdb03a | system user | Slave has read all relay log; waiting for more updates | Connect | 0 | 1 |
| dalcmsdb03a | root | executing | Query | 0 | 1 |
| dalcmsdb03a | slave | Master has sent all binlog to slave; waiting for more updates | Binlog Dump | 7250464 | 1 |
| dalcmsdb03a | system user | Waiting for master to send event | Connect | 7250502 | 1 |
+-------------+-------------+---------------------------------------------------------------+-------------+---------+----------+
4 rows in set (0.01 sec)
*/
-- Connections by db:
SELECT @@hostname as server, SUBSTR(host,1,LOCATE(':',host) - 1) as client, user, db, count(*)
FROM information_schema.processlist
GROUP BY 1,2,3,4;
/*
+-------------+-------------------------+-------------+-----------+----------+
| server | client | user | db | count(*) |
+-------------+-------------------------+-------------+-----------+----------+
| dalcmsdb03a | | root | NULL | 1 |
| dalcmsdb03a | | root | craft_cms | 1 |
| dalcmsdb03a | | system user | NULL | 2 |
| dalcmsdb03a | dalcmsdb03a.dal.moz.com | heartbeat | heartbeat | 2 |
| dalcmsdb03a | dalcmsdb03b.dal.moz.com | slave | NULL | 1 |
+-------------+-------------------------+-------------+-----------+----------+
5 rows in set (0.00 sec)
*/
-- Connections by db with Sum:
SELECT @@hostname as server, SUBSTR(host,1,LOCATE(':',host) - 1) as client, user, db, count(*)
FROM information_schema.processlist
WHERE user like 'crawl%'
GROUP by 1,2,3,4
UNION ALL
SELECT @@hostname as server, 'all clients' as client, user, db, count(*)
FROM information_schema.processlist
WHERE user like 'crawl%'
GROUP BY 1,2,3,4
/*
+---------------------+----------------------------+------------------+----------------------+----------+
| server | client | user | db | count(*) |
+---------------------+----------------------------+------------------+----------------------+----------+
| dalmavanschemadb01c | dalmesoswork01.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork02.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork03.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork04.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork05.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork06.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork07.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork09.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork10.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork11.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork12.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork13.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork14.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork15.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork16.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | dalmesoswork17.dal.moz.com | crawl_processing | vanguard_non_sharded | 10 |
| dalmavanschemadb01c | all clients | crawl_processing | vanguard_non_sharded | 160 |
+---------------------+----------------------------+------------------+----------------------+----------+
17 rows in set (0.01 sec)
*/
-- Connections by db, command:
SELECT @@hostname, SUBSTR(host,1,LOCATE(':',host) - 1) as client, user, db, command, count(*)
FROM information_schema.processlist
GROUP by 1,2,3,4;
/*
+-------------+-------------------------+-------------+-----------+-------------+----------+
| @@hostname | client | user | db | command | count(*) |
+-------------+-------------------------+-------------+-----------+-------------+----------+
| dalcmsdb03b | | root | NULL | Query | 1 |
| dalcmsdb03b | | system user | NULL | Connect | 2 |
| dalcmsdb03b | 10.0.5.84 | craft | craft_cms | Sleep | 1 |
| dalcmsdb03b | dalcmsdb03a.dal.moz.com | slave | NULL | Binlog Dump | 1 |
| dalcmsdb03b | dalcmsdb03b.dal.moz.com | heartbeat | heartbeat | Sleep | 2 |
+-------------+-------------------------+-------------+-----------+-------------+----------+
5 rows in set (0.06 sec)
*/
Raw
table_info.sql
-- Get Table Sizes (Where Size > 0):
SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, ROUND(index_length / (1024 * 1024)) AS INDEX_SIZE_MB,
ROUND((data_length + index_length) / (1024 * 1024)) AS TOTAL_SIZE_MB
FROM information_schema.tables
WHERE ROUND((data_length + index_length) / (1024 * 1024)) > 0
ORDER BY 2 DESC;
/*
+-------------------------------------------+------------+----------------+---------------+---------------+
| TABLE_NAME | TABLE_ROWS | AVG_ROW_LENGTH | INDEX_SIZE_MB | TOTAL_SIZE_MB |
+-------------------------------------------+------------+----------------+---------------+---------------+
| account_call_usages | 89390908 | 102 | 9059 | 17778 |
| account_call_usages | 35680376 | 103 | 8039 | 11553 |
| account_call_usages_rollups | 14363054 | 96 | 1222 | 2539 |
| account_call_usages_rollups | 8708578 | 96 | 739 | 1539 |
| actions | 3825505 | 155 | 440 | 1009 |
| users | 1279062 | 194 | 296 | 533 |
| accounts | 1210140 | 41 | 28 | 75 |
| accounts | 1055960 | 41 | 21 | 62 |
| profiles | 945544 | 70 | 72 | 136 |
| thumbs | 726570 | 41 | 60 | 89 |
| coupons | 428112 | 141 | 27 | 84 |
| account_service_keys | 402410 | 103 | 41 | 81 |
| line_items | 320983 | 50 | 14 | 29 |
| orders | 319858 | 50 | 10 | 25 |
| accounts_users_history | 303007 | 43 | 19 | 32 |
| accounts_users | 301125 | 47 | 37 | 51 |
| subscriptions | 295552 | 126 | 72 | 108 |
| accounts | 292350 | 70 | 0 | 20 |
| applied_coupons | 282051 | 50 | 5 | 18 |
| comments | 260889 | 565 | 36 | 176 |
| email_unsubscriptions | 247759 | 44 | 10 | 21 |
| account_service_keys | 236509 | 99 | 25 | 48 |
| cancel_users | 235701 | 64 | 23 | 38 |
| mozpoints | 116160 | 40 | 4 | 8 |
| account_call_usages_monthly_rollups | 66500 | 102 | 9 | 15 |
| account_call_usages_monthly_rollups | 66500 | 102 | 9 | 15 |
| account_service_bans | 25344 | 62 | 2 | 3 |
| posts | 23201 | 9744 | 7 | 222 |
| account_service_bans | 8610 | 45 | 0 | 1 |
| service_category_default_call_permissions | 6577 | 47 | 0 | 1 |
| account_user_invites | 2832 | 121 | 0 | 1 |
| help_topic | 729 | 2180 | 0 | 2 |
| proc | 229 | 2815 | 0 | 1 |
+-------------------------------------------+------------+----------------+---------------+---------------+
*/
-- Get Table Sizes without Indexes (Where Size > 0):
SELECT TABLE_NAME, TABLE_ROWS, ROUND((data_length) / (1024 * 1024)) AS table_size_in_MB,
ROUND((index_length) / (1024 * 1024)) AS index_size_in_MB
FROM information_schema.tables
WHERE ROUND((data_length + index_length) / (1024 * 1024)) > 0
ORDER BY 2 DESC;
-- Get Table Sizes (For Specific Database):
SELECT TABLE_NAME, TABLE_ROWS, ROUND((data_length + index_length) / (1024 * 1024)) AS size_in_MB
FROM information_schema.tables
WHERE ROUND((data_length + index_length) / (1024 * 1024)) > 0
AND TABLE_SCHEMA LIKE '<db name>'
ORDER BY 2 DESC;
/*
+------------+------------+------------+
| TABLE_NAME | TABLE_ROWS | size_in_MB |
+------------+------------+------------+
| jobs | 504373 | 206 |
| users | 145490 | 15 |
+------------+------------+------------+
*/
-- Get Database Sizes
SELECT TABLE_SCHEMA as DB, SUM(TABLE_ROWS) as TOTAL_ROWS, AVG(AVG_ROW_LENGTH) AS AVG_ROW_BYTES,
SUM(ROUND(index_length / (1024 * 1024))) AS INDEX_SIZE_MB,
SUM(ROUND((data_length + index_length) / (1024 * 1024))) AS TOTAL_SIZE_MB
FROM information_schema.tables
WHERE ROUND((data_length + index_length) / (1024 * 1024)) > 0
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;
/*
+----------------+------------+---------------+---------------+---------------+
| DB | TOTAL_ROWS | AVG_ROW_BYTES | INDEX_SIZE_MB | TOTAL_SIZE_MB |
+----------------+------------+---------------+---------------+---------------+
| beacon_prod | 646425359 | 2463.7500 | 11268 | 187852 |
| nexus | 207969368 | 111.7273 | 324 | 21204 |
| barbosa | 98362397 | 751.2857 | 2272 | 13361 |
| cmoz_clone1 | 73819042 | 700.4000 | 1057 | 5795 |
| etl_repo | 31917822 | 208.2609 | 6105 | 13101 |
| etl_test | 29454410 | 206.6136 | 5586 | 12019 |
| keymaster | 24885607 | 73.5000 | 1265 | 3363 |
| zuora | 21388794 | 385.6500 | 4110 | 11161 |
| v3seomoz | 18065685 | 944.8421 | 114 | 2154 |
| billing_team | 16941137 | 333.2222 | 0 | 5749 |
| cmoz | 11954413 | 571.6000 | 328 | 1045 |
| analytics_prod | 6040124 | 159.3333 | 479 | 977 |
| report | 4783858 | 483.9474 | 1343 | 3151 |
| eventhorizon | 3445470 | 419.2000 | 0 | 917 |
| longboard | 3069635 | 164.7143 | 258 | 753 |
| bi_test | 2091358 | 193.4286 | 296 | 662 |
| mondo | 1849975 | 115.3333 | 6 | 255 |
| wonk | 1121595 | 151.5000 | 17 | 179 |
| bi_alerts_test | 1090381 | 149.0000 | 0 | 156 |
| phil_test | 590975 | 569.7500 | 0 | 79 |
| listerine | 412851 | 593.3333 | 16 | 103 |
| pdfit | 175224 | 248.0000 | 0 | 42 |
| bi_alerts | 31182 | 259.0000 | 0 | 7 |
| mysql | 19218 | 3957.6667 | 0 | 16 |
| slow_query_log | 7868 | 1614.0000 | 0 | 13 |
+----------------+------------+---------------+---------------+---------------+
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment