Проблема с MySQL

12
Евгений Русаченко
На сайте с 17.04.2013
Offline
130
4638

Здравствуйте.

Борюсь с некоторой проблемой уже несколько недель и все никак не пойму в чём проблема. Прошу помощи у Вас, уважаемые участники сообщества!

Что имеется: три сервера CentOS 6.6 и базами данных MySQL 5.5 (без патчей). Работает это все с Nginx / Apache и PHP-FPM.

Проблема в следующем: на одном сервере MySQL работает неадекватно, конкретно InnoDB. Запросы просто зависают без видимых причин в статусе "query end" примерно раз 1-3 дня, так как сервер настроен корректно по использованию памяти, то MySQL просто использует все доступные соединения (300) и рвет новые подключения. Так все держится до перезагрузки MySQL, либо очистки выделенного InoDB Buffer Pool.

mysql> SHOW ENGINE INNODB STATUS\G

*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
150304 5:18:00 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 58 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 32450 1_second, 32125 sleeps, 3236 10_second, 87 background, 87 flush
srv_master_thread log flush and writes: 34931
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 33850, signal count 32977
Mutex spin waits 1055527, rounds 3701415, OS waits 24740
RW-shared spins 8305, rounds 187789, OS waits 4589
RW-excl spins 1560, rounds 88559, OS waits 2067
Spin rounds per wait: 3.51 mutex, 22.61 RW-shared, 56.77 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
150303 21:33:03
*** (1) TRANSACTION:
TRANSACTION DA702A4, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 101609, OS thread handle 0x7f8f13c1a700, query id 3055803 localhost tarea_Psychfli updating
DELETE FROM wp_6_options WHERE option_name = 'rewrite_rules'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29197 page no 4 n bits 480 index `option_name` of table `tarea_selfnet`.`wp_6_options` trx id DA702A4 lock_mode X waiting
Record lock, heap no 275 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 13; hex 726577726974655f72756c6573; asc rewrite_rules;;
1: len 8; hex 0000000000002142; asc !B;;

*** (2) TRANSACTION:
TRANSACTION DA702A3, ACTIVE 0 sec starting index read, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 101612, OS thread handle 0x7f8f132d2700, query id 3055801 localhost tarea_Psychfli updating
DELETE FROM wp_6_options WHERE option_name = 'rewrite_rules'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 29197 page no 4 n bits 480 index `option_name` of table `tarea_selfnet`.`wp_6_options` trx id DA702A3 lock_mode X locks rec but not gap
Record lock, heap no 275 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 13; hex 726577726974655f72756c6573; asc rewrite_rules;;
1: len 8; hex 0000000000002142; asc !B;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29197 page no 4 n bits 480 index `option_name` of table `tarea_selfnet`.`wp_6_options` trx id DA702A3 lock_mode X waiting
Record lock, heap no 275 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 13; hex 726577726974655f72756c6573; asc rewrite_rules;;
1: len 8; hex 0000000000002142; asc !B;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter DB7813A
Purge done for trx's n:o < DB77DE7 undo n:o < 0
History list length 1255
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 359603, OS thread handle 0x7f8e1234d700, query id 12352923 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION DB78139, not started
MySQL thread id 359613, OS thread handle 0x7f8e13df7700, query id 12352884 localhost uatodayo_wp1
---TRANSACTION DB77D91, not started
MySQL thread id 358511, OS thread handle 0x7f8f130ca700, query id 12342479 localhost diesupru_main
---TRANSACTION DB77EA0, ACTIVE 43 sec, thread declared inside InnoDB 344
mysql tables in use 2, locked 0
MySQL thread id 359369, OS thread handle 0x7f8e120c3700, query id 12345448 localhost photoclu_photo Sending data
SELECT 1
FROM pc_comments c
LEFT JOIN pc_users u ON u.id = c.user_id
WHERE c.published = 1
AND (c.is_hidden=0)
Trx read view will not see trx with id >= DB77EA1, sees < DB77DCD
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1374157 OS file reads, 868859 OS file writes, 142322 OS fsyncs
2.36 reads/s, 16384 avg bytes/read, 7.57 writes/s, 1.59 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1278, seg size 1280, 7456 merges
merged operations:
insert 9828, delete mark 39430, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 8850461, node heap has 1 buffer(s)
0.00 hash searches/s, 26530.21 non-hash searches/s
---
LOG
---
Log sequence number 65698858990
Log flushed up to 65698858990
Last checkpoint at 65698839262
0 pending log writes, 0 pending chkp writes
256028 log i/o's done, 2.47 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4395630592; in additional pool allocated 0
Dictionary memory allocated 315591159
Buffer pool size 262143
Free buffers 0
Database pages 262142
Old database pages 96747
Modified db pages 72
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1277016, not young 0
3.40 youngs/s, 0.00 non-youngs/s
Pages read 1334322, created 35912, written 589358
2.36 reads/s, 0.07 creates/s, 5.10 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 262142, unzip_LRU len: 0
I/O sum[128]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
2 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
Main thread process no. 56688, id 140252525274880, state: sleeping
Number of rows inserted 543621, updated 137943, deleted 75864, read 813358163
0.91 inserts/s, 0.78 updates/s, 0.07 deletes/s, 26469.72 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

Исходя из SHOW ENGINE INNODB STATUS видно, что MySQL ловит DEADLOCK'и. Запросы произвольные, не всегда одни и те же.

Я изучил этот вопрос, на сколько понял - они должны быть безобидны и не приводить к подобному поведению MySQL.

Далее, если посмотреть лог MySQL, там можно найти следующее в момент проблем (смотрите вложение).

Нашел рекомендации добавить параметр innodb_adaptive_hash_index = 0, но это не помогло.

Есть еще один и достаточно интересный момент, добавив параметр table_open_cache в любом нестандартном размере (более 400), в MySQL начинает утекать память. То есть, если при обычном использовании MySQL кушает 10 гб памяти (допустим), то при установке table_open_cache в 800, MySQL откусывает всю доступную память сервера, может уйти в SWAP и его убьет OOM Killer, подчеркну, что такое поведение только на проблемном сервере, на двух других проблем нет.

Ниже файл конфигурации /etc/my.cnf

[mysqld]

character-set-server=utf8
init-connect='SET NAMES utf8'
collation-server=utf8_general_ci

local-infile = 0
tmpdir = /dev/shm

# Буферы
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M

# Оптимизация производительности
key_buffer_size = 2G
thread_concurrency = 4
tmp_table_size = 128M
max_heap_table_size = 128M
query_cache_size = 128M
max_connections = 300
max_user_connections = 32
thread_cache_size = 4
max_delayed_threads = 0

# Лимит на время выполнения запросов
wait_timeout = 60
interactive_timeout = 60

# InnoDB
innodb_log_file_size = 128M
innodb_additional_mem_pool_size = 64M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 4G
innodb_adaptive_hash_index = 0
innodb_file_per_table
skip-innodb-doublewrite

В /var/log/messages ничего нет, только если OOM killer убивает MySQL :) Но это не происходит...

txt mysql.txt
Быстрый хостинг (https://lite.host/hosting/dlya-blogov) от 99 рублей RU и РФ домены (https://lite.host/domains) по 175 рублей с продлением Бесплатные SSL сертификаты (https://goo.gl/DsplNq) | Поддержка PHP 5.2 - 7.4 | SSH | Помощь с переносом
Евгений Русаченко
На сайте с 17.04.2013
Offline
130
#1

Для наглядности график соединений и памяти.

png mysql_threads-day.png
png memory-day.png
[Удален]
#2

Евгений Русаченко, вам поручили решить эту проблему или работали с проектом изначально? Если имеются предпосылки (изменение конфига, обновление ПО, скриптов, рост нагрузки и т.д.), это сильно облегчило бы задачу. Я больше всего склоняюсь к конфигу.

pupseg
На сайте с 14.05.2010
Offline
329
#3

запросы на вставку коммитятся ? commit используете часто?

Качественная помощь в обслуживании серверов. (/ru/forum/661100) Бесплатных консультаций не даю, не помогаю, не обучаю. Минималка от 100$. Как пропатчить KDE-просьба не спрашивать. Есть форумы (http://linux.org.ru) и полезные сайты (http://www.opennet.ru/).
N
На сайте с 06.05.2007
Offline
419
#4
Евгений Русаченко:
ри сервера CentOS 6.6 и базами данных MySQL 5.5 (без патчей)

Начнем с того, что в centos 6.6 должен быть mysql-server-5.1.73

Вы откуда ставили mysql и как давно обновляли ?

Обычно подвисание на этапе query_end связано с очисткой кеша запросов или фиксацией транзакций.

Так что можно попробовать снизить query_cache_size = 128M .

И настройка skip-innodb-doublewrite - крайне странная. Ее никто не использует, потому что она фактически отключает нормальную фиксацию транзакций. Не исключено, что с этим и связан какой-то редкий баг.

Если хотите фиксацию хоть как-то ускорить, лучше включите innodb-doublewrite, а innodb_flush_log_at_trx_commit = 0. По крайней мере, так будет более традиционно.

Кнопка вызова админа ()
Евгений Русаченко
На сайте с 17.04.2013
Offline
130
#5
-ez:
Евгений Русаченко, вам поручили решить эту проблему или работали с проектом изначально? Если имеются предпосылки (изменение конфига, обновление ПО, скриптов, рост нагрузки и т.д.), это сильно облегчило бы задачу. Я больше всего склоняюсь к конфигу.

По сути это сервер с огромным числом сайтов на Joomla, Drupal, WordPress и возможно чем-то самописным. MySQL строго настроен на использование 1/3 памяти сервера за пределы которого и не вылезает даже при 300 активных соединениях. По практике, если бы проблема была в запросах, то они просто бы тупили, но 1 запрос никак не мог бы вызывать очередь из 300 соединений и в какой-то степени положить MySQL.

---------- Добавлено 04.03.2015 в 16:09 ----------

pupseg:
запросы на вставку коммитятся ? commit используете часто?

Понятия не имею, за скрипты которые используются на сервере ответственности не несу и лезть мне в них нельзя. Моя задача только в том, чтобы сервер хорошо работал и эти скрипты хорошо работали.

Если где-то в статистике можно посмотреть число commit's, то ткните пальцем, посмотрю :)

---------- Добавлено 04.03.2015 в 16:20 ----------

netwind:
Начнем с того, что в centos 6.6 должен быть mysql-server-5.1.73
Вы откуда ставили mysql и как давно обновляли ?

Обычно подвисание на этапе query_end связано с очисткой кеша запросов или фиксацией транзакций.
Так что можно попробовать снизить query_cache_size = 128M .
И настройка skip-innodb-doublewrite - крайне странная. Ее никто не использует, потому что она фактически отключает нормальную фиксацию транзакций. Не исключено, что с этим и связан какой-то редкий баг.
Если хотите фиксацию хоть как-то ускорить, лучше включите innodb-doublewrite, а innodb_flush_log_at_trx_commit = 0. По крайней мере, так будет более традиционно.

MySQL собрался из исходников с официального сайта. MySQL 5.1 уже как-то слишком устарел на мой взгляд и 5.5 отлично работал и работает на CentOS 6.6, только не на этом сервере...

Подвисают именно InnoDB запросы, query_cache_size для них тоже используется? Всегда считал, что он только для MyISAM таблиц. Если да, то тогда попробую вовсе отключить, а не снижать. Если поможет - с меня какой-нибудь презент.

По skip-innodb-doublewrite ситуация такова. tmp папка вынесена в оперативную память и там O_DIRECT не работает корректно, лог заваливается ошибками:

141225 2:12:31 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
141225 2:12:31 InnoDB: Failed to set O_DIRECT on file /dev/shm/#sqlad50b_9d07_d.ibd: CREATE: Invalid argument, continuing anyway

В качестве аналога для O_DIRECT и включили skip-innodb-doublewrite.

Пробовали и без него тоже, это было добавлено недавно. Изначально, когда проблем еще не было, конфигурационный файл был еще куда проще:

[mysqld]

character-set-server=utf8
init-connect='SET NAMES utf8'
collation-server=utf8_general_ci

local-infile = 0
tmpdir = /dev/shm

# Буферы
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M

# Оптимизация производительности
key_buffer_size = 2G
tmp_table_size = 128M
max_heap_table_size = 128M
query_cache_size = 128M
max_connections = 300
max_user_connections = 32

# InnoDB
innodb_log_file_size = 128M
innodb_additional_mem_pool_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 4G
innodb_file_per_table

Данный типовой конфиг стоит на десятках серверов и проблем нет. А на этом вот закрылась проблема. С этим конфигурационным файлом и опцией table_open_cache тоже происходит утечка памяти, с этого так скажем всё и начиналось. Опцию убрали, стали запросы вешаться в query end.

Лимит открытых файлов достаточно высок, в этом не может быть проблемы:

fs.file-max = 3275006

N
На сайте с 06.05.2007
Offline
419
#6
Евгений Русаченко:
MySQL 5.1 уже как-то слишком устарел на мой взгляд

Ну так в Centos вообще почти все устарело в момент выхода. Это такая идеология дистрибутива. Зачем тогда ставить, если все равно прыгаете вперед ?

Евгений Русаченко:
Подвисают именно InnoDB запросы, query_cache_size для них тоже используется? Всегда считал, что он только для MyISAM таблиц. Если да, то тогда попробую вовсе отключить, а не снижать. Если поможет - с меня какой-нибудь презент.

Кеш запросов используется независимо от движка хранения. Шансов, на мой взгляд, немного. 128 мб это не такой уж большой размер кеша.

По skip-innodb-doublewrite ситуация такова. tmp папка вынесена в оперативную память и там O_DIRECT не работает корректно, лог заваливается ошибками:

И все же, может убрать ?

вы и так от innodb_flush_log_at_trx_commit = 0 выиграете сравнительно неплохо. Некоторый перерасход памяти не так важен.

Евгений Русаченко
На сайте с 17.04.2013
Offline
130
#7
netwind:
Ну так в Centos вообще почти все устарело в момент выхода. Это такая идеология дистрибутива. Зачем тогда ставить, если все равно прыгаете вперед ?


Кеш запросов используется независимо от движка хранения. Шансов, на мой взгляд, немного. 128 мб это не такой уж большой размер кеша.


И все же, может убрать ?
вы и так от innodb_flush_log_at_trx_commit = 0 выиграете сравнительно неплохо. Некоторый перерасход памяти не так важен.

Кроме PHP и MySQL ничем не прыгали, всё остальное стоит из коробки.

Убрать попробовать можно, попытка не пытка. Так как всё что можно было перепробовано уже.

innodb_flush_log_at_trx_commit на мой взгляд не безопасно. Хоть сервер стоит и в ДЦ, но никаких ИБП и RAID контроллера с батарейкой нет, чтобы такое ставить.

pupseg
На сайте с 14.05.2010
Offline
329
#8
Евгений Русаченко:
Хоть сервер стоит и в ДЦ, но никаких ИБП и RAID контроллера с батарейкой нет, чтобы такое ставить.

для этого должен бакап стоять, а не рейд-контроллер :)

Евгений Русаченко
На сайте с 17.04.2013
Offline
130
#9
pupseg:
для этого должен бакап стоять, а не рейд-контроллер :)

Сам факт повышенного шанса повреждения базы данных не радует. Поэтому такое точно использовать не буду.

N
На сайте с 06.05.2007
Offline
419
#10
Евгений Русаченко:
innodb_flush_log_at_trx_commit на мой взгляд не безопасно.

Так вы уже поставили 2 . Уже все. Реальные пацаны в вас пальцем будут тыкать и смеяться.

Innodb не повредится от 0, просто некоторые свежие данные могут потеряться и только лишь в случае внезапной перезагрузки.

12

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий