If your hosting environment has no database monitoring, you should be able to monitor it manually once in a while or with your own shell script to prevent rapidly growing MySQL tables, because the result of large MySQL tables ( I am talking about 2 – 10 GB with millions of rows ) are often random performance issues, especially if you are using community modules with not well written MySQL queries.
|
1 2 3 4 |
SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "{{{YOUR_DATABASE}}}" ORDER BY (data_length + index_length) DESC; |
This is the output of the query.
|
1 2 3 4 5 6 7 8 9 10 |
| enterprise_logging_event | 122389 | 15939572 | 3270656 | 18.32 | | catalog_product_index_eav | 324014 | 4212182 | 18053120 | 21.23 | | log_visitor_info | 182838 | 19706548 | 3599360 | 22.23 | | catalog_product_index_eav_idx | 324014 | 4522245 | 21139456 | 24.47 | | catalog_product_entity_varchar | 255303 | 10852180 | 15244288 | 24.89 | | log_url | 228886 | 10966704 | 15636480 | 25.37 | | log_url_info | 228886 | 80258352 | 7542784 | 83.73 | | enterprise_logging_event_changes | 69184 | 160619752 | 1499136 | 154.61 | +-----------------------------------------------+------------+-------------+--------------+------------+ 586 rows in set (0.05 sec) |