GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

innodb_buffer_pool_size possibly not being used?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • innodb_buffer_pool_size possibly not being used?

    I'm trying to performance tune/debug performance on a mysql 5.0.27 install. All tables are innodb, and the machine is running centos4_x86_64 on a Dual Dual Core 2.6g (intel 5150) machine with 16g of ram. Using tools such as innotop I see disk reads in the 5k/s - 10k/s range and the db is not incredibly large. From the output of show innodb status below, I suspect that only 26M is being used for buffer_pool cache instead of the 3G as directed in the conf file. Am I reading this wrong? Is there a way to tell what mysql believes its limits are?

    Buffer related output from 'show innodb status'

    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 26576648; in additional pool allocated 1048576
    Buffer pool size 512
    Free buffers 0
    Database pages 491
    Modified db pages 9
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 23909121, created 240, written 29564
    7354.91 reads/s, 0.13 creates/s, 9.72 writes/s
    Buffer pool hit rate 992 / 1000


    -------------------------------------------------

    Innodb related section fix my.cnf:

    #innoDB options
    # Uncomment the following if you are using InnoDB tables
    innodb_data_home_dir = /var/lib/mysql/
    innodb_data_file_path = ibdata1:4G;ibdata2:100M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql/
    innodb_log_arch_dir = /var/lib/mysql/

    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    #innodb_buffer_pool_size = 1G
    innodb_buffer_pool_size = 3G
    innodb_additional_mem_pool_size = 512M

    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 128M
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 128M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50

    default-storage-engine=innodb

  • #2
    Hi,

    What is the combined size of your innodb tables?

    Also note innodb's buffers aren't populated automatically.

    For example, I have a 8Gb innodb buffer, and 6Gb of innodb based data. To load the data into the innodb cache when mysql starts I have a .SQL file with init SQL statements (my.cnf):

    init_file = /usr/local/mysql/var/init.sql

    The queries in the init.sql file contain queries that require full table scans and helps populate innodb buffers and get the server chugging along optimially:

    ...
    SELECT COUNT(id) FROM speeple.news_items;
    ...

    Comment


    • #3
      This is all fixed now, for those that stumble across this posting. This was escalated to a professional engagement with mysqlperformanceblog consulting, when Vadim quickly noticed that innodb settings go in the [mysqld] section, not the [mysql.server] section. Of course I didn't include that part in my original post.... Now the db is using 10G for its buffer_pool cache and disk reads are in the 0-50 per second range. Thanks Speeple and especially Vadim.

      Comment


      • #4
        thanks for posting this follow up -

        you just saved me a ton of time!

        Comment

        Working...
        X