If you’ve ever been debugging a large Multisite installation that’s having MySQL memory issues you know how maddening it can be. Sometimes you can turn on slow logging and find the source of the issue. But sometimes it’s not just about slow queries, it’s about the shear size of the tables and the number of queries being run against them. One trick is to generate a list of all the tables on the site and sort them by size. This will help you narrow down trouble spots.
USE INFORMATION_SCHEMA; SELECT table_name,table_rows,data_length FROM TABLES where table_schema = 'DBNAME' AND table_rows > 0 ORDER BY data_length DESC LIMIT 0,50;
This will produce a list like the following (see below). You can adjust the sort order and limit based on your needs. This query isn’t all that helpful in cases of small installs. But in an installation with 500+ tables it can help you narrow down the issue quickly. WARNING: this query can take a long time to run depending on the number of databases on your server and the number of tables in the Dbs.
table_nametable_rowsdata_length wp_options12565536 wp_blogs116384 wp_sitemeta2516384 wp_site116384 wp_posts316384 wp_postmeta116384 wp_users216384 wp_comments116384 wp_usermeta3116384 wp_links7560 wp_term_relationships8168 wp_term_taxonomy284 wp_terms272