I have a problem with which i fail to win. Have new server set up with Debian 10 (64) and MariaDB 10.4. There's no traffic on this server yet. So no slow queries or queries in total however, MariaDB takes 300% CPU already. No matter what I do and how i tinker configuration. IT's always this way. What is enabled: data at rest encryption. There was replication from it (MASTER to SLAVE) but i switched it off suspecting it could be it, but not... Iotop shows no data writes that could cause this CPU usage, so what is going on, anyone had such problem?
PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command
24419 mysql 20 0 19.4G 2008M 23268 S 299. 5.5 22:51.15 /usr/sbin/mysqld
24445 mysql 20 0 19.4G 2008M 23268 S 79.3 5.5 5:40.03 /usr/sbin/mysqld
24448 mysql 20 0 19.4G 2008M 23268 R 76.1 5.5 5:38.87 /usr/sbin/mysqld
24446 mysql 20 0 19.4G 2008M 23268 R 72.4 5.5 5:41.99 /usr/sbin/mysqld
24447 mysql 20 0 19.4G 2008M 23268 R 70.9 5.5 5:39.02 /usr/sbin/mysqld
MariaDB [(none)]> show full processlist;
+-----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 134 | root | localhost | NULL | Query | 0 | Init | show full processlist | 0.000 |
+-----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+
6 rows in set (0.000 sec)
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
Current DISK READ: 0.00 B/s | Current DISK WRITE: 59.00 K/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
430 be/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [jbd2/dm-0-8]
14633 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/5:0-mm_percpu_wq]
1 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % init
2 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kthreadd]
3 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [rcu_gp]
4 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [rcu_par_gp]
6 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/0:0H-events_highpri]
8 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/0:1H-events_highpri]
9 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [mm_percpu_wq]
10 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/0]
11 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [rcu_sched]
12 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [rcu_bh]
13 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/0]
15 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [cpuhp/0]
root@as1:/home/lukasz# uptime
09:24:36 up 8 days, 1 min, 4 users, load average: 2.90, 2.86, 2.94
EDIT: version: 10.4.27-MariaDB-1:10.4.27+maria~deb10-log EDIT: show engine innodb status:
=====================================
2022-11-22 09:53:59 0x7fe4a44fd700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 149 srv_active, 0 srv_shutdown, 1921 srv_idle
srv_master_thread log flush and writes: 2070
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 45741
OS WAIT ARRAY INFO: signal count 22662
RW-shared spins 1314, rounds 24199, OS waits 636
RW-excl spins 26, rounds 586, OS waits 13
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 18.42 RW-shared, 22.54 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1029611
Purge done for trx's n:o < 1029611 undo n:o < 0 state: running but idle
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422094963908992, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422094963904776, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422094963900560, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
52839 OS file reads, 4450 OS file writes, 2433 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.07 writes/s, 0.07 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 81, seg size 83, 88 merges
merged operations:
insert 114, delete mark 6, delete 3
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 2212699, node heap has 84 buffer(s)
Hash table size 2212699, node heap has 187 buffer(s)
Hash table size 2212699, node heap has 133 buffer(s)
Hash table size 2212699, node heap has 105 buffer(s)
Hash table size 2212699, node heap has 83 buffer(s)
Hash table size 2212699, node heap has 148 buffer(s)
Hash table size 2212699, node heap has 42 buffer(s)
Hash table size 2212699, node heap has 147 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 118676575056
Log flushed up to 118676575056
Pages flushed up to 118676575056
Last checkpoint at 118676575047
0 pending log flushes, 0 pending chkp writes
958 log i/o's done, 0.07 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8606711808
Dictionary memory allocated 966240
Buffer pool size 513728
Free buffers 458354
Database pages 49418
Old database pages 18397
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 6, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 49264, created 154, written 2896
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 49418, unzip_LRU len: 10504
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 64216
Free buffers 57916
Database pages 5564
Old database pages 2073
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5560, created 4, written 323
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5564, unzip_LRU len: 1282
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 64216
Free buffers 58029
Database pages 5539
Old database pages 2064
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5539, created 0, written 134
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5539, unzip_LRU len: 1104
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 64216
Free buffers 57604
Database pages 5919
Old database pages 2204
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5918, created 1, written 108
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5919, unzip_LRU len: 1206
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 64216
Free buffers 56793
Database pages 6636
Old database pages 2469
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6614, created 22, written 777
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6636, unzip_LRU len: 1441
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 64216
Free buffers 56203
Database pages 7075
Old database pages 2631
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 7075, created 0, written 704
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7075, unzip_LRU len: 1725
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 64216
Free buffers 56942
Database pages 6403
Old database pages 2383
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6403, created 0, written 58
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6403, unzip_LRU len: 1549
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 64216
Free buffers 57495
Database pages 6117
Old database pages 2278
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6054, created 63, written 561
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6117, unzip_LRU len: 1037
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 64216
Free buffers 57372
Database pages 6165
Old database pages 2295
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6101, created 64, written 231
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6165, unzip_LRU len: 1160
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=14405, Main thread ID=140610963961600, state: sleeping
Number of rows inserted 107, updated 488, deleted 12, read 22433827
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 1
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
