На нем крутится 3 сайта - 2 мелких на вордпрессе и магазин на престашоп.
С недавнего времени (пара-тройка дней) сервер начал сильно тормозить.
показывает главным нарушителем спокойствия mysql.
показывает массу спящих процессов.
Что делать, куда копать?...
SHOW ENGINE INNODB STATUS выдает вот такие результаты(часть вывода удалена из-за ограничения на размер поста. Удалены однотипные записи типа "---TRANSACTION 5220F87C6, not started"):
=====================================
180412 13:03:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 17 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5183 1_second, 4940 sleeps, 518 10_second, 2 background, 1 flush
srv_master_thread log flush and writes: 5861
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 61802, signal count 60572
Mutex spin waits 1687552, rounds 2340967, OS waits 37605
RW-shared spins 56869, rounds 797503, OS waits 14998
RW-excl spins 10983, rounds 368600, OS waits 8443
Spin rounds per wait: 1.39 mutex, 14.02 RW-shared, 33.56 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 5220F87FE
Purge done for trx's n:o < 5220F7AED undo n:o < 0
History list length 1400
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 33054, OS thread handle 0x7f671420f700, query id 9882814 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 33053, OS thread handle 0x7f671441f700, query id 9882759 localhost root
---TRANSACTION 5220F87C6, not started
MySQL thread id 33052, OS thread handle 0x7f699c0ae700, query id 9882732 localhost root
---TRANSACTION 0, not started
MySQL thread id 33051, OS thread handle 0x7f671587d700, query id 9882652 localhost root
---TRANSACTION 0, not started
MySQL thread id 33050, OS thread handle 0x7f67152f2700, query id 9882783 localhost root
---TRANSACTION 0, not started
MySQL thread id 33047, OS thread handle 0x7f67146f5700, query id 9881563 localhost root
---TRANSACTION 5220F856E, not started
MySQL thread id 33049, OS thread handle 0x7f699c04b700, query id 9882179 localhost root
---TRANSACTION 5220F8540, not started
MySQL thread id 33048, OS thread handle 0x7f67150a0700, query id 9882281 localhost root
---TRANSACTION 5220F8497, not started
MySQL thread id 33046, OS thread handle 0x7f6998294700, query id 9881535 localhost root
---TRANSACTION 5220F844A, not started
MySQL thread id 33044, OS thread handle 0x7f67157d8700, query id 9881349 localhost root
---TRANSACTION 5220F842F, not started
MySQL thread id 33045, OS thread handle 0x7f699816b700, query id 9882294 localhost root
---TRANSACTION 5220F8333, not started
MySQL thread id 33043, OS thread handle 0x7f67142f6700, query id 9882404 localhost root
---TRANSACTION 5220F8334, not started
MySQL thread id 33042, OS thread handle 0x7f6714947700, query id 9882139 localhost root
MySQL thread id 33034, OS thread handle 0x7f6998616700, query id 9882697 localhost root
---TRANSACTION 5220F87FD, not started
MySQL thread id 33033, OS thread handle 0x7f671560a700, query id 9882818 localhost root
SELECT out_of_stock
FROM `ps_stock_available`
WHERE (id_product = 115796) AND (id_product_attribute = 0) AND (id_shop = 1) AND (id_shop_group = 0)
LIMIT 1
---TRANSACTION 5220F832F, not started
MySQL thread id 33032, OS thread handle 0x7f6714deb700, query id 9882794 localhost root
---TRANSACTION 5220F3DE1, not started
MySQL thread id 31321, OS thread handle 0x7f671416a700, query id 9853167 localhost root
---TRANSACTION 5220EDE63, not started
MySQL thread id 31211, OS thread handle 0x7f6714062700, query id 9876842 localhost root
---TRANSACTION 5220D3E69, not started
MySQL thread id 31175, OS thread handle 0x7f6715733700, query id 9674084 localhost root
---TRANSACTION 5220F78DE, not started
MySQL thread id 31002, OS thread handle 0x7f67155e9700, query id 9875239 localhost root
---TRANSACTION 5220E0544, not started
MySQL thread id 30706, OS thread handle 0x7f671543c700, query id 9747240 localhost root
---TRANSACTION 5220D5E36, not started
MySQL thread id 30403, OS thread handle 0x7f67143dd700, query id 9688185 localhost root
---TRANSACTION 5220C1C26, not started
MySQL thread id 30106, OS thread handle 0x7f6714af4700, query id 9576542 localhost root
---TRANSACTION 5220F6FCA, not started
MySQL thread id 29775, OS thread handle 0x7f6713f9c700, query id 9870700 localhost root
---TRANSACTION 5220E6A22, not started
MySQL thread id 29496, OS thread handle 0x7f6715a09700, query id 9780754 localhost root
---TRANSACTION 5220D1060, not started
MySQL thread id 29317, OS thread handle 0x7f6713eb5700, query id 9657564 localhost root
---TRANSACTION 5220CEA05, not started
MySQL thread id 29172, OS thread handle 0x7f69981ce700, query id 9644340 localhost root
---TRANSACTION 5220F7E9E, not started
MySQL thread id 28877, OS thread handle 0x7f67155c8700, query id 9878536 localhost root
---TRANSACTION 5220E6A69, not started
MySQL thread id 28569, OS thread handle 0x7f6714e4e700, query id 9780814 localhost root
---TRANSACTION 5220D39C7, not started
MySQL thread id 28252, OS thread handle 0x7f67156f1700, query id 9672286 localhost root
---TRANSACTION 5220F87F9, ACTIVE 0 sec starting index read, thread declared inside InnoDB 23
mysql tables in use 12, locked 0
MySQL thread id 33039, OS thread handle 0x7f67159e8700, query id 9882798 localhost root Copying to tmp table
SELECT cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, pl.`name`, p.`is_virtual`,
pl.`description_short`, pl.`available_now`, pl.`available_later`, product_shop.`id_category_default`, p.`id_supplier`,
p.`id_manufacturer`, product_shop.`on_sale`, product_shop.`ecotax`, product_shop.`additional_shipping_cost`,
product_shop.`available_for_order`, product_shop.`price`, product_shop.`active`, product_shop.`unity`, product_shop.`unit_price_ratio`,
stock.`quantity` AS quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_s
Trx read view will not see trx with id >= 5220F87FA, sees < 5220F8658
---TRANSACTION 5220F8707, ACTIVE (PREPARED) 0 sec preparing
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 32936, OS thread handle 0x7f6714317700, query id 9882492 localhost root query end
INSERT INTO `ps_connections` (`id_guest`, `id_page`, `ip_address`, `http_referer`, `id_shop`, `id_shop_group`, `date_add`) VALUES ('420813', '1827', '1310282654', '', '1', '1', '2018-04-12 14:03:09')
---TRANSACTION 5220F867D, ACTIVE (PREPARED) 0 sec preparing
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 32921, OS thread handle 0x7f6714926700, query id 9882318 localhost root query end
INSERT INTO `ps_connections` (`id_guest`, `id_page`, `ip_address`, `http_referer`, `id_shop`, `id_shop_group`, `date_add`) VALUES ('420812', '11591', '663301705', '', '1', '1', '2018-04-12 14:03:09')
---TRANSACTION 5220F8658, ACTIVE (PREPARED) 1 sec preparing
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 32913, OS thread handle 0x7f6714d25700, query id 9882276 localhost root query end
INSERT INTO `ps_connections` (`id_guest`, `id_page`, `ip_address`, `http_referer`, `id_shop`, `id_shop_group`, `date_add`) VALUES ('420811', '13494', '3134736106', '', '1', '1', '2018-04-12 14:03:08')
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (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: 1; buffer pool: 0
71923 OS file reads, 128519 OS file writes, 54702 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 21.94 writes/s, 8.75 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9, seg size 11, 685 merges
merged operations:
insert 620, delete mark 1270, delete 2
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 17700857, node heap has 4695 buffer(s)
6468.27 hash searches/s, 10065.29 non-hash searches/s
---
LOG
---
Log sequence number 663880414554
Log flushed up to 663880413293
Last checkpoint at 663880410592
1 pending log writes, 0 pending chkp writes
50496 log i/o's done, 7.87 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 8791261184; in additional pool allocated 0
Dictionary memory allocated 9569060
Buffer pool size 524288
Free buffers 424173
Database pages 95420
Old database pages 35241
Modified db pages 23
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 88, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 94021, created 1399, written 85230
0.00 reads/s, 0.00 creates/s, 13.81 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: 95420, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 1023, id 140080730543872, state: sleeping
Number of rows inserted 49003, updated 226378, deleted 5311, read 315848325
5.12 inserts/s, 0.12 updates/s, 0.00 deletes/s, 15089.64 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Тема: Высокая нагрузка на сервер от mysql (Прочитано 1257 раз)
