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_name table_rows data_length wp_options 125 65536 wp_blogs 1 16384 wp_sitemeta 25 16384 wp_site 1 16384 wp_posts 3 16384 wp_postmeta 1 16384 wp_users 2 16384 wp_comments 1 16384 wp_usermeta 31 16384 wp_links 7 560 wp_term_relationships 8 168 wp_term_taxonomy 2 84 wp_terms 2 72