I have a standalone docker stack running a Wordpress 5.9.3 installation under Nginx 1.19 + PHP-FPM 8.1.6 with MySQL 8.0.29. The server has 16x AMD Opteron cores and 16GB RAM.
Here is my docker-compose.yaml file:
php:
image: php:8.1.6-fpm
restart: unless-stopped
volumes:
- ./services/app:/var/www
app:
image: nginx:1.19
restart: unless-stopped
volumes:
- ./services/app:/var/www
db:
image: mysql:8.0.29
hostname: db
restart: unless-stopped
command: --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
security_opt:
- seccomp:unconfined
volumes:
- ./mysql/data:/var/lib/mysql
I have added security_opt: seccomp:unconfined in order to solve multiple mbind: Operation not permitted messages in the log, as per How to fix "mbind: Operation not permitted" in mysql error log.
Problem is, after a very short while, the mysql (db) container eats up the entire CPU forcing me to restart it to get it back working. Memory seems to be doing just fine.
MySQL container logs doesn't really show anything, except normal startup messages (at least for my level of knowledge):
db_1 | 2022-09-19 13:41:54+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1debian10 started.
db_1 | 2022-09-19 13:41:55+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
db_1 | 2022-09-19 13:41:55+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1debian10 started.
db_1 | 2022-09-19T13:41:57.995026Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
db_1 | 2022-09-19T13:41:57.995303Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.29) starting as process 1
db_1 | 2022-09-19T13:41:58.091072Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
db_1 | 2022-09-19T13:42:23.116277Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
db_1 | 2022-09-19T13:42:26.278541Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
db_1 | 2022-09-19T13:42:26.278898Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
db_1 | 2022-09-19T13:42:26.290448Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
db_1 | 2022-09-19T13:42:26.494786Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
db_1 | 2022-09-19T13:42:26.495108Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
After reading several articles in here, this is what I ended up adding the following to my cnf file:
[mysqld]
innodb_buffer_pool_size = 35G
innodb_buffer_pool_instances = 8
internal_tmp_mem_storage_engine = MEMORY
innodb_lru_scan_depth = 100
innodb_flush_neighbors = 2
thread_cache_size = 100
I have recreated the containers after every change along the way, but still no luck.
Also tried to limit the container CPU using the deploy policy on docker-compose.yaml, but all it does is to make it fail faster.
Now running SHOW ENGINE INNODB STATUS during one of the freezes gave me this. Which is probably a good thing, except I have no idea how to make any sense from it:
Type: InnoDB
Name: Empty
Status:
=====================================
2022-09-19 13:51:22 140202165667584 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 44 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 390 srv_active, 0 srv_shutdown, 143 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 154
OS WAIT ARRAY INFO: signal count 350
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 153365
Purge done for trx's n:o < 153353 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421699164044336, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164043528, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164041912, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164040296, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164037872, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164035448, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164033024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164039488, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164041104, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164042720, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164034640, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164032216, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164036256, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164037064, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164033832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164038680, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164031408, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421699164030600, 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:
Pending flushes (fsync) log: 0; buffer pool: 0
1148 OS file reads, 25434 OS file writes, 252 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 77.67 writes/s, 0.14 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 9296893, node heap has 1 buffer(s)
Hash table size 9296893, node heap has 1 buffer(s)
Hash table size 9296893, node heap has 0 buffer(s)
Hash table size 9296893, node heap has 0 buffer(s)
Hash table size 9296893, node heap has 1 buffer(s)
Hash table size 9296893, node heap has 1 buffer(s)
Hash table size 9296893, node heap has 10 buffer(s)
Hash table size 9296893, node heap has 4 buffer(s)
44.54 hash searches/s, 7.30 non-hash searches/s
---
LOG
---
Log sequence number 116624909
Log buffer assigned up to 116624909
Log buffer completed up to 116624909
Log written up to 116624909
Log flushed up to 116624909
Added dirty pages up to 116624909
Pages flushed up to 116624909
Last checkpoint at 116624909
127 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 618620
Buffer pool size 2293553
Free buffers 2266335
Database pages 27200
Old database pages 9932
Modified db pages 15335
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 34168, not young 984
16.20 youngs/s, 0.14 non-youngs/s
Pages read 1105, created 140335, written 25100
0.00 reads/s, 348.34 creates/s, 77.53 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27200, unzip_LRU len: 0
I/O sum[27192]:cur[264], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 286694
Free buffers 283337
Database pages 3355
Old database pages 1245
Modified db pages 1807
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4159, not young 122
3.03 youngs/s, 0.00 non-youngs/s
Pages read 186, created 17392, written 3096
0.00 reads/s, 41.16 creates/s, 9.67 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: 3355, unzip_LRU len: 0
I/O sum[3399]:cur[33], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 286694
Free buffers 283351
Database pages 3340
Old database pages 1225
Modified db pages 1931
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3945, not young 123
3.37 youngs/s, 0.14 non-youngs/s
Pages read 124, created 17413, written 3054
0.00 reads/s, 42.65 creates/s, 9.74 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3340, unzip_LRU len: 0
I/O sum[3399]:cur[33], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 286698
Free buffers 283300
Database pages 3395
Old database pages 1237
Modified db pages 1974
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4487, not young 71
2.60 youngs/s, 0.00 non-youngs/s
Pages read 95, created 17538, written 3117
0.00 reads/s, 43.96 creates/s, 9.65 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3395, unzip_LRU len: 0
I/O sum[3399]:cur[33], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 286694
Free buffers 283296
Database pages 3396
Old database pages 1233
Modified db pages 2012
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4769, not young 285
2.91 youngs/s, 0.00 non-youngs/s
Pages read 134, created 17706, written 3146
0.00 reads/s, 43.64 creates/s, 9.62 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3396, unzip_LRU len: 0
I/O sum[3399]:cur[33], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 286694
Free buffers 283197
Database pages 3495
Old database pages 1275
Modified db pages 1988
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4297, not young 64
2.73 youngs/s, 0.00 non-youngs/s
Pages read 144, created 17832, written 3153
0.00 reads/s, 46.04 creates/s, 9.74 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 3 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3495, unzip_LRU len: 0
I/O sum[3399]:cur[33], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 286691
Free buffers 283274
Database pages 3415
Old database pages 1247
Modified db pages 1959
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4439, not young 45
0.66 youngs/s, 0.00 non-youngs/s
Pages read 59, created 17666, written 3178
0.00 reads/s, 45.34 creates/s, 9.67 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3415, unzip_LRU len: 0
I/O sum[3399]:cur[33], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 286698
Free buffers 283243
Database pages 3453
Old database pages 1254
Modified db pages 1845
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3641, not young 66
0.50 youngs/s, 0.00 non-youngs/s
Pages read 163, created 17401, written 3212
0.00 reads/s, 43.03 creates/s, 9.74 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3453, unzip_LRU len: 0
I/O sum[3399]:cur[33], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 286690
Free buffers 283337
Database pages 3351
Old database pages 1216
Modified db pages 1819
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4431, not young 208
0.41 youngs/s, 0.00 non-youngs/s
Pages read 200, created 17387, written 3144
0.00 reads/s, 42.51 creates/s, 9.71 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: 3351, unzip_LRU len: 0
I/O sum[3399]:cur[33], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140202194016000 , state=sleeping
Number of rows inserted 400936, updated 0, deleted 0, read 409667
1004.82 inserts/s, 0.00 updates/s, 0.00 deletes/s, 397.56 reads/s
Number of system rows inserted 0, updated 317, deleted 0, read 19911
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 13.20 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
I appreciate any help. Thanks.
