Я пытаюсь настроить производственный сервер MySQL с помощью InnoDb и не могу добиться от него желаемой производительности. Я использовал Инструменты Percona чтобы дать мне следующие конфигурации:
[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
Как вы можете видеть, буфер InnoDb в MySQL установлен почти на 1,5 ГБ ОЗУ (что составляет более или менее 37% от общей ОЗУ моего сервера). Проблема в том, что когда я запускаю какой-то конкретный запрос на этом сервере, его выполнение занимает 70 секунд, но тот же запрос занимает всего 0,02 секунды на моей машине разработки (с точно такими же данными). Это в том случае, если моя машина разработки имеет только 130 МБ innodb_buffer_pool_size
. Проблема усложняется, когда я использую top
чтобы увидеть, сколько памяти используется MySQL, что составляет всего 7,6% (из 37% предоставленных ему).
Еще одна подсказка заключается в том, что когда я запускаю один и тот же запрос дважды подряд, второй раз занимает столько же, сколько и первый раз, как если бы кеша не было.
Есть ли у кого-нибудь предложения, где искать?
[ОБНОВИТЬ]
Приношу свои извинения за предоставленную мной вводящую в заблуждение информацию, но на моей машине для разработки запрос занимает 1,23 секунды.
Один параметр, который замедляет модификацию данных, - это sync_binlog=1
. Это заставляет MySQL физически записывать запись журнала запросов на диск после каждой записи в базу данных. С участием sync_binlog=0
, запись кэшируется для последующей записи на диск.
С участием sync_binlog=0
, возможно, что данные подчиненной репликации будут повреждены, если ОС выйдет из строя до того, как запись binlog будет записана в базу данных. В противном случае записи бинлога не имеют большого значения.
Что еще работает на сервере? Если вы отдаете InnoDB только 37% серверной памяти, я предполагаю, что есть что-то еще, использующее оставшуюся часть - если она выделена, почему бы не увеличить ее примерно до ~ 80%? Хотя, учитывая то, что вы сказали, проблема не в этом. Моя первая мысль с такой разительной разницей - 70 секунд против 0,02 секунды - заключается в том, что что-то должно съедать ваш процессор или, что более вероятно, ваш дисковый ввод-вывод. Кроме того, проверяли ли вы журналы MySQL, чтобы узнать, указывают ли они на то, что происходит?
Если вы не против помочь мне с некоторой информацией о данных в БД и о том, на что похож запрос, это может помочь мне дать вам лучший совет. Убедитесь, что все индексы и ключи присутствуют в таблицах, и запустите «EXPLAIN [query]». Это даст вам немного информации о том, что InnoDB может проверить при выполнении этого запроса.
К сожалению, это лучшее, что я могу сделать без дополнительной информации. Удачи.