I currently have a dedicated database server with 8 GBs of RAM and 8 1.60
GHz processors. The tables on my databases are almost exclusively InnoDB,
except for 2-3 tables that are MyISAM and used for logging purposes (lots of
INSERT DELAYED statements). I have the following settings in my my.cnf, and
I'm having trouble adjusting the innodb_buffer_pool_size to something
logical. I first tried setting it to 6000M, but the server went OOM and
eventually crashed. I've subsequently kept bringing it down, and now it's
at 4000M but it looks like swap is still being hit.
$ free -m
total used free shared buffers cached
Mem: 7982 7943 38 0 8 175
-/+ buffers/cache: 7759 222
Swap: 1992 702 1289
I spent some time looking at various Google links to figure out memory
usage, and what I'm confused by is how mysqld is still talking up 8388m of
virtual memory (according to top) and has 6.7g of physical memory used.
http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/
http://www.mysqlperformanceblog.com/2009/02/12/how-much-memory-can-mysql-use-in-the-worst-case/
What I'm trying to figure out is
1. Are there settings I should turn down for myisam or myisamchk, and is
that why I'm hitting 6.7GBs of actual memory?
2. Is 4000M the correct setting for innodb_buffer_pool_size?
3. Even if it is 6.7 GBs of memory, isn't 1.3 GBs of RAM (give or take) more
than enough to run the rest of the machine? I don't see anything else
coming close to the memory footprint of mysql, and I'm not sure why swap is
still getting hit.
[mysqld]
#datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 320
safe-show-database
skip-locking
key_buffer = 192M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 32M
thread_concurrency = 8
wait_timeout = 15
innodb_buffer_pool_size=4000M
innodb_log_buffer_size=4M
#innodb_log_file_size=128M
#innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-error=/var/log/mysql/mysqlerror.log
#innodb_file_per_table
sql-mode="NO_AUTO_VALUE_ON_ZERO"
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
#no-auto-rehash
max_allowed_packet = 1M
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
