innodb_buffer_pool_size possibly not being used?

MySQL & MariaDB Other MySQL® Questions
1

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;
…

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.