I'm trying to configure a MySQL production server using InnoDb and I can not get the performance I want out of it. I've used Percona tools to give me the following configurations:
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 1456M
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
As you can see MySQL's InnoDb buffer is set to almost 1.5GB of RAM (which more or less 37% of my server's total RAM). The problem is that when I run some specific query on this server it takes 70 seconds to run but the same query takes only 0.02 seconds on my development machine (with the exact same data). It's in the case that my development machine has only 130mB of innodb_buffer_pool_size
. The problem gets complicated when I use top
to see how much memory is used by MySQL, which is only 7.6% (out of 37% given to it).
One more clue is that when I run the same query twice sequentially, the second time takes as much as the first time as if there's no cache available.
Does anyone have any suggestion where to look?
[UPDATE]
I apologize for the misleading information I gave, but the query takes 1.23 second on my dev machine.
query-cache-size
could certainly cause this. Indexes on the data could also cause this if there is a difference with them between production and dev.