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

Innodb questions

Discussion in 'XenForo Questions and Support' started by SneakyDave, Mar 11, 2011.

  1. SneakyDave

    SneakyDave Well-Known Member

    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.
     

Share This Page