0

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.

1
  • Are there any other MySQL config differences between the production machine and the dev machine besides memory? 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.
    – Ladadadada
    Mar 17, 2014 at 8:02

2 Answers 2

0

What else is running on the server? If you're only giving InnoDB 37% of the server memory, I am assuming there is something else using the rest of it - if it's dedicated, why not give it closer to ~80%? Though, given what you have said, that probably isn't the problem. My first thought, with such a stark difference - 70 seconds vs 0.02 seconds - is that there must be something eating up your CPU or, perhaps more likely, your disk I/O. Also, have you checked the MySQL logs to see if they are giving any indication as to what is going on?

If you wouldn't mind helping me out with some information about the data in the DB and what the query is like, that could help me give you better advice. Make sure all the indexes and keys are present in the tables, and run "EXPLAIN [query]". It will give you a bit of info on what InnoDB might check when you execute that query.

Unfortunately that's the best I can do without some more information. Good luck.

1
  • This server also serves Apache and the reason why I gave MySQL only 37% of RAM is that I wanted to see if it's not enough and then I would rise it up to higher volumes like 80%. But as I said, it hardly uses 8% so I don't see why I should give it more!? Not that I don't want to hide my query or anything (it's a little bit complicated one) but as I said the two machines of production and development have the exact same schema & data. So if there's any problem with the indexes or anything, it should also affect the development but it doesn't.
    – Mehran
    Mar 17, 2014 at 7:57
0

One setting that slows down data modifications is sync_binlog=1. It causes MySQL to write the query log entry physically to disk after every write to the database. With sync_binlog=0, the write is cached to be written later to disk.

With sync_binlog=0, it is possible that slave replication data becomes corrupt if the OS crashes before binlog entry has been written to the database. Otherwise, binlog entries do not matter that much.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .