Hello all,
I am having a strange problem where my database server load average increase as the number of updates and selects on 3 of my larges tables go up. The biggest ones (160GB) are MyISAM tables and the third is an innodb table (2GB).
The server is my master database so it gets a lot of updates but because the large tables are so big, I have to do selects on them from the master because if I did my selects on the slave it will increase their replication lag. I am considering moving to innodb but I want to make sure I have tried all options before I make that move because the tables are soo large. Has anyone had this problem before and maybe have any ideas how to solve it?
I also noticed that the more the number of active connections I have, the cpu system waits go up when there is a lot of activity on these tables.
An help would be welcomed. my mysql config is show below.
[mysqld]
log-bin
replicate-ignore-db=mysql
log-slave-updates
server-id = 52001001
set-variable = max_connections=2500
set-variable = back_log=20
set-variable = key_buffer=8000M
set-variable = table_cache=4000
set-variable = max_allowed_packet=4M
set-variable = sort_buffer=2M
set-variable = record_buffer=4M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=200
set-variable = long_query_time=10
set-variable = query_cache_size=256M
set_variable = wait_timeout=60
transaction-isolation = READ-COMMITTED
skip-slave-start
innodb_additional_mem_pool_size=20M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=256M
innodb_buffer_pool_size=2G
Have eight threads running concurrently
innodb_thread_concurrency=8
let’s have a file per table
innodb_file_per_table
############################################################ ############
log-bin = /var/lib/mysql/b2db01-new-bin
relay-log = /var/lib/mysql/b2db09-relay-bin
log-slow-queries = /var/lib/mysql/slow.log
skip-name-resolve
tmpdir = /var/lib/mysql/tmp
#core-file
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
Default to using old password format for compatibility with mysql 3.x
clients (those using the mysqlclient10 compatibility package).
old_passwords=1
#user=root
[mysql.server]
user=mysql
#core_file_size = unlimited
[mysqld_safe]
log-error=/var/log/mysqld.log
log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/lib/mysql/mysql.sock
#user = root