Anyway, I routinely run this SQL on my information_schema to find out what tables need to be optimzied, and I was surprised that all the innodb tables were listed with a "Data_free" size of "48234496". Is this normal for Innodb tables? Do I need to exclude those table types from the query?
SELECT concat( TABLE_SCHEMA, '.', TABLE_NAME ) , Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( 'information_schema', 'mysql') AND Data_free >0;
The other question I have is regarding the innodb settings in my.cnf. Before XenForo, I had Innodb support turned off, or I moved anything I used InnoDB with to a different MySQL server. It appears from using sqltuner.pl that my memory requirements might not be set correctly to utilize a good running innodb system.
These are my innodb related settings from my.conf. (Cent OS, MySQL 5.1.52)
# To enable the InnoDB Plugin, uncomment the 2 next lines ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so # To enable InnoDB-related INFORMATION_SCHEMA tables # Join the following options to above directive ;innodb_trx=ha_innodb_plugin.so ;innodb_locks=ha_innodb_plugin.so ;innodb_cmp=ha_innodb_plugin.so ;innodb_cmp_reset=ha_innodb_plugin.so ;innodb_cmpmem=ha_innodb_plugin.so ;innodb_cmpmem_reset=ha_innodb_plugin.so #I don't have the above options turned on because some of them aren't supported on my system for some reason. innodb_buffer_pool_size=20M
So if anybody has any suggestions about these innodb settings, or if I should just leave them alone unless I notice a problem, let me know.
This is a warning I got from MySQL runtime information in phpMyAdmin,regarding the buffer pool reads, obviously related to buffer pool size. This number was 5,523 within a minute of restarting the MySQL server.
Innodb_buffer_pool_reads - 5,523 - The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.