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?
I apologize for the misleading information I gave, but the query takes 1.23 second on my dev machine.