MySQL Performance: MySQL => innodb_buffer_pool

innodb_buffer_pool_size possibly not being used? [message #1467]

Sat, 23 June 2007 19:16

jlederma
Messages: 2
Registered: June 2007

Junior Member

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

Report message to a moderator