Comments - Possible dead lock (OLS) - Possible mariaDB issue
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
The version is:
11.5.2-MariaDB-ubu2404
The innoDB status is bellow. However, there's an old app that still runs on MyISAM tables on the server.
***** 1. row ***** Type: InnoDB Name: Status:
2024-12-08 12:43:59 0x70d7be2d36c0 INNODB MONITOR OUTPUT
Per second averages calculated from the last 7 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 54405 srv_idle srv_master_thread log flush and writes: 54405 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 18467490 Purge done for trx's n:o < 18467490 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: -------- FILE I/O -------- Pending flushes (fsync): 0 868621 OS file reads, 82129 OS file writes, 41981 OS fsyncs 5.28 reads/s, 885 avg bytes/read, 2.86 writes/s, 1.14 fsyncs/s --- LOG --- Log sequence number 52133102873 Log flushed up to 52133102512 Pages flushed up to 52106926654 Last checkpoint at 52106926654 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 7549747200 Dictionary memory allocated 46755048 Buffer pool size 454272 Free buffers 1004 Database pages 453268 Old database pages 167333 Modified db pages 3115 Percent of dirty pages(LRU & free pages): 0.686 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 1317972, not young 19769057 0.00 youngs/s, 2.29 non-youngs/s Pages read 867504, created 2613, written 6366 5.28 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 5.00/s LRU len: 453268, unzip_LRU len: 0 I/O sum[6]:cur[4], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB state: sleeping ---------------------------- END OF INNODB MONITOR OUTPUT
Significant CPU usage and application not receiving a response sounds like a slow query.
Look at enabling the slow query log with a low log_slow_query_time - 2 seconds? How long is the application waiting. Look at this slow query log around the stalled time.
EXPLAIN FORMAT=JSON included details on execution of the query.
When you have details, include them in a question on stackoverflow https://stackoverflow.com/ along with the table structures.
Hi,
We have analyzed the slow query log, and it does not look that any slow queries are related to this problem.
Is there any other log we could look at?
Thanks Alex