Проблема с MySQL

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

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

Что имеется: три сервера 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 :) Но это не происходит...