| 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
|
|
|
| Re: innodb_buffer_pool_size possibly not being used? [message #1468 is a reply to message #1467 ] |
Sun, 24 June 2007 11:05  |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
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;
...
Martin Gallagher | Speeple: The latest news
|
|
|
|
| Re: innodb_buffer_pool_size possibly not being used? [message #2082 is a reply to message #1469 ] |
Wed, 17 October 2007 15:01 |
jclaybaugh
Messages: 1
Registered: October 2007
Location: San Francisco |
Junior Member |
|
|
thanks for posting this follow up -
you just saved me a ton of time!
|
|
|
Members
Search
Help
Register
Login
Home
Re: innodb_buffer_pool_size possibly not being used?
