I recently had to reload a db due to user error and using mysqltuner it reported
Before dump:
InnoDB buffer pool / data size: 50.0G/45.2G
After restore:
InnoDB buffer pool / data size: 50.0G/25.7G
The historical mysqldump sizes of db show expected increase in size compared to the current (we do full backups twice a day).
Any idea what might have caused something like this? The original mysqltuner output was from the previous 8.0.26 patch release and the newest is from current (as of today).
I started panicking because I thought we were missing data but it’s all there.
1 Like
Hi @Matthew_Lenz , thanks for posting to the Percona forums!
Since you mentioned mysqldump it sounds like you did a restoration from a logical backup (vs PXB or MariaBackup binary). The advantage of a logical restore is that the tables on disk will be built in Primary key order and are now in the most compact version (no fragmentation). over time with a dataset experiencing regular UPDATE and DELETE activity (particularly bulk DELETEs) you’ll see that your row count is reflective of the actual data residing in the database, but unfortunately InnoDB does NOT recover that space from the on-disk ibd files. What we generally recommend is that a user execute an OPTIMIZE TABLE / ALTER TABLE … ENGINE=InnoDB so that a table is rebuilt. Your restoration is effectively the same process, just with a little more work involved 
1 Like
@Michael_Coburn this db is optimized on the first sunday of every month so it would have been optimized just a few days ago. Also, I do believe that mysqltuner uses some math to determine the size of the data not necessarily just looking a that disk size of the tables correct?
2 Likes
@Michael_Coburn I think this is the query they do to get the size:
SELECT ENGINE,SUM(DATA_LENGTH+INDEX_LENGTH),COUNT(ENGINE),SUM(DATA_LENGTH),SUM(INDEX_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
2 Likes
@Michael_Coburn MySQL :: MySQL 8.0 Reference Manual :: 15.23 InnoDB Restrictions and Limitations … I assume that information schema table is where SHOW TABLE STATUS gets it’s information. If that is true then you are probably correct in that it’s just the size of the table on disk.
2 Likes
I found the issue. myloader with --innodb-optimize-keys uses alter statements to add all the secondary indexes after the tables are created. When using alter statements to add keys it requires an analyze table to update the table stats. So everything was fine, mysql was just being mysql.
2 Likes