1

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
6
  • "the MySQL-database seems super slow" -- what exactly is slow? How do you measure slowness, and what would be the condition of "not slow"? – mustaccio Sep 17 '20 at 17:25
  • @mustaccio thank you for your input. It makes sense. :) I've fixed the screenshot and added details about my understanding of speed and how I measure it. – jbcologne Sep 17 '20 at 20:28
  • You didn't say you were running Wordpress on that same server. I'd look at misbehaving plugins. Enable general log and see what queries are being run and how long it takes. Slow page loads might as well be caused by PHP, not the database. – mustaccio Sep 17 '20 at 20:33
  • @mustaccio again thank you, so far: i've made sure it's not about Wordpress: no plugins activated, checked wordpress debug log and got these php-errors. All execution time related, then set PHP execution time and memory limit up, checked PHP-Modules - everthing fine for the wordpress setup. Right now I can't imagine an exotic php config setting causing this. The only thing I found which looked unusual to me are these sleeping mysld-tasks in htop (see screenshot above). So I checked the values (that I use to know) of inno_db variables... – jbcologne Sep 17 '20 at 20:59
  • Additional information request. # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Sep 18 '20 at 20:57
0

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 = 1 to discover the slow queries. See http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Wordpress 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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.