MySQL Forums
Forum List  »  Performance

Performance Schema estimates never change
Posted by: Anthony Stump
Date: March 30, 2017 10:15AM

Performance Schema never updates sizes or rows in databases in MySQL, even after numerous reboots. I realize that it's supposed to only be an estimate, but it's way off.... i.e. this example:

mysql> SELECT table_schema 'Database', SUM((data_length + index_length)/1024/1024) AS 'DBSizeMB', GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS 'DBRows' FROM information_schema.tables GROUP BY table_schema;
+--------------------+----------------+---------+
| Database | DBSizeMB | DBRows |
+--------------------+----------------+---------+
| Core | 31.31250000 | 45575 |
| information_schema | 0.15625000 | NULL |
| jspServ | 0.04687500 | 856 |
| lahman2016 | 65.21875000 | NULL |
| mysql | 5.41411686 | 4173 |
| net_snmp | 98.56250000 | 247860 |
| performance_schema | 0.00000000 | NULL |
| sys | 0.01562500 | NULL |
| WebCal | 0.62500000 | NULL |
| WxObs | 12871.64062500 | 2554745 |
+--------------------+----------------+---------+
10 rows in set (0.06 sec)


This shows teh WxObs database is 12.9 GB, with net_snmp being 98.6 MBs.

Compare that with the du -h command, where it shows WxObs should be almost twice that size at 22 GBs.

astump@astump-Desktop:~$ sudo du -h /var/lib/mysql
332K /var/lib/mysql/jspServ
1.1M /var/lib/mysql/performance_schema
163M /var/lib/mysql/lahman2016
81M /var/lib/mysql/Core
21G /var/lib/mysql/WxObs
4.0K /var/lib/mysql/xpt
676K /var/lib/mysql/sys
3.4M /var/lib/mysql/WebCal
22M /var/lib/mysql/mysql
185M /var/lib/mysql/net_snmp
22G /var/lib/mysql

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance Schema estimates never change
195
March 30, 2017 10:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.