Re: Query slows down over time

[Sam Shah]

I did get a chance after a long time to run some statistics on the database.

Here are the results:

mysql> SELECT ENGINE,
-> ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
-> ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
-> ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
-> COUNT(*) "Num Tables"
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE table_schema not in ("information_schema", "performance_schema")
-> GROUP BY ENGINE;
+--------+---------+----------+----------+------------+
| ENGINE | Data MB | Index MB | Total MB | Num Tables |
+--------+---------+----------+----------+------------+
| InnoDB | 79122.8 | 695.7 | 79818.6 | 14 |
| MyISAM | 800.8 | 33.2 | 834.0 | 30 |
+--------+---------+----------+----------+------------+
2 rows in set (2.09 sec)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data'; -- IBPDataPages 9593
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages 9600
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS 16384

Does this help?