I got new to the project, my predecessor is far gone and I'm left on my own.
Currently the MySQL-database seems super slow and after checking some variables and values I cannot identify the problem on my own. I've also run mysqltuner but strangly it reports that the innodb engine is deactivated which isn't for sure since it's the default engine and the status is fine, so I don't think I can trust the results of mysqltuner.
How I measure speed:
(Please have a look at my htop screenshot)
-
Load Average now: 1.28, 1.86, 2.05 – I would expect values < 1
-
Gtmetrix shows a page respond time of 6sec (1,2MB with 41
http-requests) for MYSQL-driven page (WordPress) and <3sec for static
HTML-Page (1,5MB 35 Requests) -
Right now there's nobody on the server and I got like 50 mysqld
tasks, all slepping but AFAIK blocking 65.9% Memory -
Have found serveral PHP executiontime related errors (currently set 256MB and 60sec per script) in the apache error log. It seems to run out of time on simple update-queries, nothing fancy here
Right now for example "show processlist" is empty, but I can see a lot of mysqld tasks running with htop (https://ibb.co/qJzpz83 <- link to htop screenshot.) and a lot of memory usage. Does this look normal to you experts? I would kill these tasks but I really would like to find the reason for them first. Since I'm not sure which pieces of information are of interest I've provides the innoDB engine status (attached below) and the InnoDB-related variables (also attached below).
Thank you for your time and effort, I really need to get more into this kind of stuff so any help and ideas would be wonderful.
=====================================
2020-09-17 17:58:44 0x7ef90762f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 42194 srv_active, 0 srv_shutdown, 42152 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 125254
OS WAIT ARRAY INFO: signal count 124576
RW-shared spins 4411, rounds 5237, OS waits 830
RW-excl spins 17356, rounds 501666, OS waits 16806
RW-sx spins 4095, rounds 121485, OS waits 3998
Spin rounds per wait: 1.19 RW-shared, 28.90 RW-excl, 29.67 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 9920862
Purge done for trx's n:o < 9920862 undo n:o < 0 state: running but idle
History list length 26
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421099207277976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421099207277120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421099207276264, not started
0 lock struct(s), heap size 1136, 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: 748
419651 OS file reads, 9621589 OS file writes, 1888824 OS fsyncs
0.32 reads/s, 14020 avg bytes/read, 60.07 writes/s, 13.66 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5, 34 merges
merged operations:
insert 12, delete mark 38, delete 9
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 3112859, node heap has 169 buffer(s)
Hash table size 3112859, node heap has 111 buffer(s)
Hash table size 3112859, node heap has 193 buffer(s)
Hash table size 3112859, node heap has 209 buffer(s)
Hash table size 3112859, node heap has 366 buffer(s)
Hash table size 3112859, node heap has 109 buffer(s)
Hash table size 3112859, node heap has 200 buffer(s)
Hash table size 3112859, node heap has 214 buffer(s)
14711.84 hash searches/s, 379.26 non-hash searches/s
---
LOG
---
Log sequence number 287517725947
Log buffer assigned up to 287517725947
Log buffer completed up to 287517725947
Log written up to 287517725947
Log flushed up to 287517725947
Added dirty pages up to 287517725947
Pages flushed up to 287508837360
Last checkpoint at 287508837360
6634374 log i/o's done, 50.78 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 12877824000
Dictionary memory allocated 19713366
Buffer pool size 768000
Free buffers 334247
Database pages 432182
Old database pages 159682
Modified db pages 265
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 410863, created 21319, written 2300114
0.00 reads/s, 0.00 creates/s, 0.00 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: 432182, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 96000
Free buffers 41846
Database pages 53957
Old database pages 19937
Modified db pages 265
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 410863, created 21319, written 2300114
0.00 reads/s, 0.00 creates/s, 0.00 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: 432182, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 96000
Free buffers 41846
Database pages 53957
Old database pages 19937
Modified db pages 39
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 51648, created 2309, written 330263
0.00 reads/s, 0.00 creates/s, 0.00 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: 53957, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 96000
Free buffers 42236
Database pages 53561
Old database pages 19791
Modified db pages 2
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 51174, created 2387, written 243880
0.00 reads/s, 0.00 creates/s, 0.00 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: 53561, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 96000
Free buffers 42635
Database pages 53178
Old database pages 19650
Modified db pages 26
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 50955, created 2223, written 253853
0.00 reads/s, 0.00 creates/s, 0.00 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: 53178, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 96000
Free buffers 41500
Database pages 54292
Old database pages 20061
Modified db pages 31
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 51459, created 2833, written 256415
0.00 reads/s, 0.00 creates/s, 0.00 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: 54292, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 96000
Free buffers 41683
Database pages 54126
Old database pages 19997
Modified db pages 38
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 50921, created 3205, written 288400
0.00 reads/s, 0.00 creates/s, 0.00 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: 54126, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 96000
Free buffers 40936
Database pages 54876
Old database pages 20276
Modified db pages 27
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 52101, created 2775, written 255132
0.00 reads/s, 0.00 creates/s, 0.00 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: 54876, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 96000
Free buffers 41276
Database pages 54525
Old database pages 20147
Modified db pages 29
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 51794, created 2731, written 310088
0.00 reads/s, 0.00 creates/s, 0.00 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: 54525, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 96000
Free buffers 42135
Database pages 53667
Old database pages 19823
Modified db pages 73
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 50811, created 2856, written 362083
0.00 reads/s, 0.00 creates/s, 0.00 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: 53667, unzip_LRU len: 0
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=2007245, Main thread ID=139610143446784 , state=sleeping
Number of rows inserted 4368, updated 119517, deleted 4866, read 210363302
0.22 inserts/s, 2.54 updates/s, 0.07 deletes/s, 14802.22 reads/s
Number of system rows inserted 58, updated 367, deleted 0, read 4466273
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 42.46 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
INNODB VARIABLES
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 2 |
| innodb_buffer_pool_chunk_size | 1572864000 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 12582912000 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_deadlock_detect | ON |
| innodb_dedicated_server | OFF |
| innodb_default_row_format | dynamic |
| innodb_directories | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_doublewrite_batch_size | 0 |
| innodb_doublewrite_dir | |
| innodb_doublewrite_files | 2 |
| innodb_doublewrite_pages | 4 |
| innodb_fast_shutdown | 1 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | fsync |
| innodb_flush_neighbors | 0 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_fsync_threshold | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_idle_flush_pct | 100 |
innodb_idle_flush_pct | 100 |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_lru_scan_depth | 1024 |
| innodb_max_dirty_pages_pct | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 4000 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 4 |
| innodb_page_size | 16384 |
| innodb_parallel_read_threads | 4 |
| innodb_print_all_deadlocks | OFF |
| innodb_print_ddl_logs | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_encrypt | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 50 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
| innodb_use_native_aio | ON |
| innodb_validate_tablespace_paths | ON |
| innodb_version | 8.0.21 |
| innodb_write_io_threads | 4 |
[![htop screenshot][2]][2]
[1]: https://i.stack.imgur.com/vvZt1.png
[2]: https://i.stack.imgur.com/Y4JV8.png
Best Answer
Normal. (Probably)
The number of processes is probably controlled by your
max_connections. Each new web page is probably connecting the MySQL and failing to disconnect. Or some "connection pooling" is hanging onto it. Anyway, 60 connections in Sleep mode is very low impact. (In particular, it won't explain the high 'load average'.)"(1,2MB with 41 http-requests)" -- Does that mean 40 images on the page. Isn't that a bit cluttered?
"(1,5MB 35 Requests)" -- Should not take 6 seconds if the queries are "simple" and/or "well indexed".
Turn on the slowlog with
long_query_time = 1to discover the slow queries. See http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlogWordpress has inefficient schema for its "meta" tables. This describes a fix: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
"but AFAIK blocking 65.9% Memory" Good. And here's why: "innodb_buffer_pool_size | 12582912000" Good. That setting should be that high; it is the main tunable. I see that you don't appear to be swapping. Good. And you are consuming most of RAM. Good.
""show processlist" is empty" -- Fine. That means that all the connections have finished and gone away. Meanwhile the 60 are hanging around to save effort in starting up new connections. All Good.
"kill these tasks" No. Not worth the effort.
SHOW ENGINE INNODB STATUS-- Nothing exciting in it. (A "deadlock" would be exciting.)