• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Innodb questions

SneakyDave

Well-known member
#1
I haven't had a lot of experience with Innodb tables since a bad project using them in MySQL 4.0, but obviously, most of XenForo's tables are Innodb, so I don't have a choice.

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?
Code:
SELECT concat( TABLE_SCHEMA,  '.', TABLE_NAME ) , Data_free
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT
IN (
 'information_schema',  'mysql')
AND Data_free >0;
Another tuning tool I use (sqltuner.pl) is telling me that I have 200+ fragmented tables, but I'm sure I'm sure I don't, but I assume these tools use the same mechanism above to determine fragmentation? I assume I should just ignore the notice, as it appears it is all the innodb tables.

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)
Code:
# 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
I'm running on a VPS, 512M RAM, and although I haven't had any noticeable performance problems with the database, sqltuner.pl consistenly tells me that I should think about increasing my innodb_buffer_pool_size to greater than 77M, which which surely eats up a lot more memory.

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.