0

Recently I have enabled table compression (i.e. ROW_FORMAT=COMPRESSED) on half of the tables in a MySQL 5.6 server. I am observing:

  1. much higher write IOPS (~700 -> ~3000)
  2. dangerously lower dirty pages in buffer pool (7G -> 16MB)
  3. no change in number of buffer pool write requests
  4. much higher data write (200->1.2k) and fsync (60->300)

The compression aspect looks fine: 10ops compression attempts and 50ops uncompressed attempts, compression success ratio > 99%

~300K used 8192-byte pages in buffer pool. Redo logging is <1G per hour while the log file size is 6GB.

This number of write IOPS is worrying. Any possible explanation and solution?

server spec: Amazon RDS r5.xlarge (32GB ram)

variables:

innodb_log_compressed_pages=0
innodb_flush_log_at_trx_commit=2
innodb_io_capacity=1500
innodb_io_capacity_max=3000
innodb_log_file_size=3221225472
innodb_buffer_pool_size={DBInstanceClassMemory*3/4} (=32GB*3/4)
mysql> show global status like '%buffer_pool%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_pages_data         | 1507853     |
| Innodb_buffer_pool_bytes_data         | 24750940160 |
| Innodb_buffer_pool_pages_dirty        | 289         |
| Innodb_buffer_pool_bytes_dirty        | 4472832     |
| Innodb_buffer_pool_pages_flushed      | 585246048   |
| Innodb_buffer_pool_pages_free         | 2042        |
| Innodb_buffer_pool_pages_misc         | 2873        |
| Innodb_buffer_pool_pages_total        | 1512768     |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 19708056    |
| Innodb_buffer_pool_read_ahead_evicted | 180068      |
| Innodb_buffer_pool_read_requests      | 54954180112 |
| Innodb_buffer_pool_reads              | 202976263   |
| Innodb_buffer_pool_wait_free          | 52377       |
| Innodb_buffer_pool_write_requests     | 11397188948 |
+---------------------------------------+-------------+
17 rows in set (0.00 sec)

Thanks in advance. Please let me know what else is needed to diagnose this issue.

UPDATE 20190624:

On MySQL server with compressed InnoDB tables:

mysql> select name, count from information_schema.INNODB_METRICS where name like '%flush%tot%';
+-------------------------------------+--------+
| name                                | count  |
+-------------------------------------+--------+
| buffer_flush_batch_total_pages      | 253977 |
| buffer_flush_neighbor_total_pages   |      0 |
| buffer_flush_adaptive_total_pages   |      0 |
| buffer_flush_sync_total_pages       |      0 |
| buffer_flush_background_total_pages | 253977 |
+-------------------------------------+--------+
5 rows in set (0.00 sec)

mysql> select name, count from information_schema.INNODB_METRICS where name like '%LRU%';
+------------------------------------------+--------+
| name                                     | count  |
+------------------------------------------+--------+
| buffer_LRU_batch_scanned                 |  95115 |
| buffer_LRU_batch_num_scan                |   8330 |
| buffer_LRU_batch_scanned_per_call        |      3 |
| buffer_LRU_batch_total_pages             |  24007 |
| buffer_LRU_batches                       |    580 |
| buffer_LRU_batch_pages                   |     32 |
| buffer_LRU_single_flush_scanned          |      0 |
| buffer_LRU_single_flush_num_scan         |      0 |
| buffer_LRU_single_flush_scanned_per_call |      0 |
| buffer_LRU_single_flush_failure_count    |      0 |
| buffer_LRU_get_free_search               | 104032 |
| buffer_LRU_search_scanned                |      0 |
| buffer_LRU_search_num_scan               |      0 |
| buffer_LRU_search_scanned_per_call       |      0 |
| buffer_LRU_unzip_search_scanned          |      0 |
| buffer_LRU_unzip_search_num_scan         |      0 |
| buffer_LRU_unzip_search_scanned_per_call |      0 |
| innodb_dict_lru_usec                     |     12 |
+------------------------------------------+--------+
18 rows in set (0.00 sec)

On servers without compressed tables:

mysql> select name, count from information_schema.INNODB_METRICS where name like '%flush%tot%';
+-------------------------------------+-------+
| name                                | count |
+-------------------------------------+-------+
| buffer_flush_batch_total_pages      | 53007 |
| buffer_flush_neighbor_total_pages   |     0 |
| buffer_flush_adaptive_total_pages   | 51503 |
| buffer_flush_sync_total_pages       |     0 |
| buffer_flush_background_total_pages |  1504 |
+-------------------------------------+-------+
5 rows in set (0.00 sec)

mysql> select name, count from information_schema.INNODB_METRICS where name like '%LRU%';
+------------------------------------------+---------+
| name                                     | count   |
+------------------------------------------+---------+
| buffer_LRU_batch_scanned                 | 3049861 |
| buffer_LRU_batch_num_scan                |    5531 |
| buffer_LRU_batch_scanned_per_call        |     264 |
| buffer_LRU_batch_total_pages             |  115647 |
| buffer_LRU_batches                       |     558 |
| buffer_LRU_batch_pages                   |      74 |
| buffer_LRU_single_flush_scanned          |       0 |
| buffer_LRU_single_flush_num_scan         |       0 |
| buffer_LRU_single_flush_scanned_per_call |       0 |
| buffer_LRU_single_flush_failure_count    |       0 |
| buffer_LRU_get_free_search               |  241555 |
| buffer_LRU_search_scanned                |       0 |
| buffer_LRU_search_num_scan               |       0 |
| buffer_LRU_search_scanned_per_call       |       0 |
| buffer_LRU_unzip_search_scanned          |       0 |
| buffer_LRU_unzip_search_num_scan         |       0 |
| buffer_LRU_unzip_search_scanned_per_call |       0 |
| innodb_dict_lru_usec                     |    2315 |
+------------------------------------------+---------+
18 rows in set (0.00 sec)

Something is wrong with flushing. Is it LRU flushing?

Update 2, 20190624

mysql> show engine innodb status\G                                                                                                   *************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2019-06-24 05:07:19 2b7fd26b8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 57 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2883931 srv_active, 0 srv_shutdown, 22 srv_idle
srv_master_thread log flush and writes: 2883949
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 10922971
OS WAIT ARRAY INFO: signal count 13732101
Mutex spin waits 79801297, rounds 345255874, OS waits 4296444
RW-shared spins 19805465, rounds 124410653, OS waits 1551832
RW-excl spins 16342291, rounds 229708078, OS waits 4365141
Spin rounds per wait: 4.33 mutex, 6.28 RW-shared, 14.06 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
***
------------
TRANSACTIONS
------------
Trx id counter 2085218349
Purge done for trx's n:o < 2085218349 undo n:o < 0 state: running but idle
History list length 1842
LIST OF TRANSACTIONS FOR EACH SESSION:
***
--------
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 (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
479870444 OS file reads, 1531031465 OS file writes, 546509468 OS fsyncs
184.38 reads/s, 13381 avg bytes/read, 774.43 writes/s, 257.03 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 649692, seg size 649694, 215871583 merges
merged operations:
 insert 215199115, delete mark 62999803, delete 7408329
discarded operations:
 insert 6585, delete mark 6377, delete 8
Hash table size 49050979, node heap has 0 buffer(s)
0.00 hash searches/s, 3048.39 non-hash searches/s
---
LOG
---
Log sequence number 3588126694314
Log flushed up to   3588126643099
Pages flushed up to 3588126640963
Last checkpoint at  3588126216285
0 pending log writes, 0 pending chkp writes
200222612 log i/o's done, 63.28 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 25366102016; in additional pool allocated 0
Dictionary memory allocated 17630656
Buffer pool size   1512768
Free buffers       11875
Database pages     1496801
Old database pages 552440
Modified db pages  339
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 195194078, not young 1679061285
89.05 youngs/s, 652.99 non-youngs/s
Pages read 474927240, created 33417104, written 1296364496
184.38 reads/s, 2.42 creates/s, 695.92 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 4 / 1000 not 35 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1496801, unzip_LRU len: 149674
I/O sum[337816]:cur[808], unzip sum[39216]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   189096
Free buffers       1464
Database pages     187318
Old database pages 69149
Modified db pages  45
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24719463, not young 209419673
11.33 youngs/s, 80.45 non-youngs/s
Pages read 59819438, created 4185555, written 194151827
23.39 reads/s, 0.40 creates/s, 88.05 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 4 / 1000 not 35 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 187318, unzip_LRU len: 18729
I/O sum[42227]:cur[101], unzip sum[4902]:cur[0]
---BUFFER POOL 1
Buffer pool size   189096
Free buffers       1497
Database pages     187362
Old database pages 69144
Modified db pages  38
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24214487, not young 212246302
11.47 youngs/s, 106.58 non-youngs/s
Pages read 59202502, created 4175977, written 150051660
24.70 reads/s, 0.25 creates/s, 83.61 writes/s
Buffer pool hit rate 991 / 1000, young-making rate 4 / 1000 not 40 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 187362, unzip_LRU len: 18736
I/O sum[42227]:cur[101], unzip sum[4902]:cur[0]
---BUFFER POOL 2
Buffer pool size   189096
Free buffers       1486
Database pages     186915
Old database pages 68986
Modified db pages  25
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24600669, not young 214933840
9.91 youngs/s, 63.33 non-youngs/s
Pages read 59563875, created 4175341, written 154746580
21.42 reads/s, 0.32 creates/s, 77.96 writes/s
Buffer pool hit rate 984 / 1000, young-making rate 7 / 1000 not 49 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 186915, unzip_LRU len: 18691
I/O sum[42227]:cur[101], unzip sum[4902]:cur[0]
---BUFFER POOL 3
Buffer pool size   189096
Free buffers       1492
Database pages     186811
Old database pages 68943
Modified db pages  35
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24417722, not young 209693517
9.81 youngs/s, 58.86 non-youngs/s
Pages read 59568188, created 4178437, written 176785625
23.09 reads/s, 0.28 creates/s, 92.82 writes/s
Buffer pool hit rate 991 / 1000, young-making rate 3 / 1000 not 23 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 186811, unzip_LRU len: 18681
I/O sum[42227]:cur[101], unzip sum[4902]:cur[0]
---BUFFER POOL 4
Buffer pool size   189096
Free buffers       1479
Database pages     186924
Old database pages 68993
Modified db pages  54
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24058917, not young 208156601
11.11 youngs/s, 78.21 non-youngs/s
Pages read 58702241, created 4191399, written 167593474
21.84 reads/s, 0.42 creates/s, 99.68 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 3 / 1000 not 22 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 186924, unzip_LRU len: 18693
I/O sum[42227]:cur[101], unzip sum[4902]:cur[0]
---BUFFER POOL 5
Buffer pool size   189096
Free buffers       1498
Database pages     187119
Old database pages 69054
Modified db pages  38
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24067645, not young 209177453
12.70 youngs/s, 84.17 non-youngs/s
Pages read 58571557, created 4161104, written 149359366
24.00 reads/s, 0.25 creates/s, 92.09 writes/s
Buffer pool hit rate 991 / 1000, young-making rate 5 / 1000 not 33 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 187119, unzip_LRU len: 18712
I/O sum[42227]:cur[101], unzip sum[4902]:cur[0]
---BUFFER POOL 6
Buffer pool size   189096
Free buffers       1471
Database pages     186894
Old database pages 68986
Modified db pages  77
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24976199, not young 208040870
11.11 youngs/s, 86.23 non-youngs/s
Pages read 60410519, created 4190663, written 155569591
22.65 reads/s, 0.28 creates/s, 87.42 writes/s
Buffer pool hit rate 989 / 1000, young-making rate 5 / 1000 not 41 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 186894, unzip_LRU len: 18687
I/O sum[42227]:cur[101], unzip sum[4902]:cur[0]
---BUFFER POOL 7
Buffer pool size   189096
Free buffers       1488
Database pages     187458
Old database pages 69185
Modified db pages  27
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24138976, not young 207393029
11.61 youngs/s, 95.16 non-youngs/s
Pages read 59088920, created 4158628, written 148106373
23.30 reads/s, 0.23 creates/s, 74.28 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 7 / 1000 not 59 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 187458, unzip_LRU len: 18745
I/O sum[42227]:cur[101], unzip sum[4902]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 6474, id 47827495421696, state: sleeping
Number of rows inserted 274975508, updated 219793571, deleted 66056069, read 27317620841
69.70 inserts/s, 64.74 updates/s, 26.47 deletes/s, 20057.49 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

MySQL variables & show global status: https://gist.github.com/carsonip/e5e554bd31f27fdabf637bfad37bd808

Posted on GitHub Gist because it is too long to be in this post.

  • Those STATUS numbers are useless without dividing by Uptime. – Rick James Jun 13 at 15:56
  • COMPRESSED is not as great as one might hope. – Rick James Jun 13 at 15:57
  • @RickJames I know it isn't that great, but it is the best feasible solution on RDS. Also I would expect a decrease in writes, not increase. Somehow the buffer pool is messed up as far as I can tell – Carson Ip Jun 13 at 16:37
  • @RickJames I have updated the post with some evidence of change in flushing behavior – Carson Ip Jun 24 at 4:04
  • I can't promise to find the solution, but can you post SHOW GLOBAL STATUS; and SHOW VARIABLES;, plus RAM size. I may find some clues in other parts than what you just posted. – Rick James Jun 24 at 5:06
0

A rather busy system:

Observations:

  • Version: 5.6.40-log
  • 32 GB of RAM
  • Uptime = 34d 16:13:26
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

The server is opening a lot of tables. Need to increase table_open_cache to perhaps 10000 (which is quite large)

buffer_pool_size (24G) is probably optimal, contrary to the chatter below.

Max_used_connections = 1772; this is quite high. Look at the clients to see if they are properly closing connections and whether they can cut back on how many connections they request. When too many connections are busy, MySQL stumbles over itself.

A lot of using "USE dbname".

There are some indications of inefficient queries. Consider using the Slowlog to research that further.

Be aware that init_connect is ignored by root.

SHOW BINLOGS once a minute? Perhaps the cloud service is doing that instead of automatically expiring them?

Details and other observations:

( Innodb_buffer_pool_pages_flushed ) = 1,466,258,093 / 2996006 = 489 /sec -- Writes (flushes) -- check innodb_buffer_pool_size

( Opened_tables ) = 121,147,086 / 2996006 = 40 /sec -- Frequency of opening Tables -- increase table_open_cache

( table_open_cache ) = 7,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( Table_open_cache_overflows ) = 121,139,435 / 2996006 = 40 /sec -- May need to increase table_open_cache

( Table_open_cache_misses ) = 121,147,086 / 2996006 = 40 /sec -- May need to increase table_open_cache

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 121,147,086 / (2115623537 + 121147086) = 5.4% -- Effectiveness of table_open_cache. -- Increase table_open_cache and check table_open_cache_instances.

( innodb_lru_scan_depth ) = 1,500 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((297008360 + 1466258093) ) / 2996006 = 588 /sec -- InnoDB I/O -- Increase innodb_buffer_pool_size?

( Innodb_os_log_written ) = 1,030,229,547,520 / 2996006 = 343867 /sec -- This is an indicator of how busy InnoDB is. -- Very idle or very busy InnoDB.

( Innodb_log_writes ) = 205,687,406 / 2996006 = 69 /sec

( Innodb_dblwr_writes ) = 18,290,286 / 2996006 = 6.1 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them. -- (Symptom of other issues)

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = OFF -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890).

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 3,604 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections is too high and various memory settings are high, you could run out of RAM.

( local_infile ) = local_infile = ON -- local_infile = ON is a potential security issue

( Created_tmp_tables ) = 118,327,611 / 2996006 = 39 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (282671715 + 225888491 + 66810907 + 0) / 558181328 = 1.03 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 9,827,645 / 2996006 = 3.3 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (282671715 + 66810907 + 0 + 0 + 225888491 + 0) / 2996006 = 192 /sec -- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( binlog_error_action ) = binlog_error_action = IGNORE_ERROR -- What to do if the binlog cannot be written. -- IGNORE_ERROR is the default for backwards compatibility, but ABORT_SERVER is recommended.

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days) -- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash. (Not relevant if log_bin = OFF)

( Com_change_db / Connections ) = 311,585,827 / 1071326 = 290 -- Database switches per connection -- (minor) Consider using "db.table" syntax

( Com_change_db ) = 311,585,827 / 2996006 = 104 /sec -- Probably comes from USE statements. -- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( thread_cache_size ) = 700 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)

( thread_cache_size / max_connections ) = 700 / 3604 = 19.4% -- (0 for Windows)

Abnormally large:

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 619
Com_commit = 186 /sec
Com_create_trigger = 0.0012 /HR
Com_delete = 22 /sec
Com_drop_index = 0.038 /HR
Com_drop_trigger = 0.0012 /HR
Com_flush = 13 /HR
Com_purge = 12 /HR
Com_show_binlogs = 60 /HR
Com_show_slave_status = 0.2 /sec
Com_show_status = 1 /sec
Handler_prepare = 513 /sec
Innodb_data_fsyncs = 191 /sec
Innodb_data_writes = 537 /sec
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 462 /sec
Innodb_data_written = 14870182 /sec
Innodb_dblwr_pages_written = 456 /sec
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 74.7
Innodb_log_write_requests = 745 /sec
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 590.3MB
Innodb_pages_created = 11 /sec
Innodb_pages_read + Innodb_pages_written = 619 /sec
Innodb_pages_written = 456 /sec
Key_reads+Key_writes + Innodb_pages_read+Innodb_pages_written+Innodb_dblwr_writes+Innodb_buffer_pool_pages_flushed = 1114 /sec
Max_used_connections = 1,772
Open_tables = 6,992
Performance_schema_table_handles_lost = 1.22e+9
Performance_schema_table_instances_lost = 533
Threads_cached = 646
host_cache_size = 783
innodb_spin_wait_delay = 96
optimizer_trace_offset = --1
performance_schema_max_cond_instances = 18,516
performance_schema_max_socket_instances = 7,228
performance_schema_max_thread_instances = 7,308
slave_parallel_workers = 8
slave_pending_jobs_size_max = 64MB

Abnormal strings:

init_connect = SET NAMES utf8mb4
innodb_data_home_dir = /rdsdbdata/db/innodb
innodb_fast_shutdown = 1
innodb_log_compressed_pages = OFF
innodb_monitor_enable = all
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_recovery = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
time_zone = UTC
tx_isolation = READ-COMMITTED
  • Thanks for the answer. Great catch on the table_open_cache, that has to be fixed, although I doubt it has anything to do with my problem. The observation on high binlog r/w is interesting. USE dbname comes from ProxySQL and sharding. Number of connections is usually stable and not high (~200), but it may spike to 1k in case of application restarts. – Carson Ip Jun 25 at 9:31
  • After all these observations, do you mean that the issue I am facing is due to a small buffer pool? – Carson Ip Jun 25 at 9:32
  • @CarsonIp - re buffer_pool_size -- I don't recommend any change there. 24GB out of 32GB is reasonable and possibly mandated by your cloud provider. The various automated checks I had were grumbling about it. Going to a larger VM (hence larger buffer_pool) might help, but the clues are not strong enough for me to recommend that. – Rick James Jun 25 at 17:53
  • @CarsonIp - Yeah, I can see Max_used_connections spiking. However, since it is a "high-water mark", we can't easily see if things settled down. Does the service have graphs on "threads running" and/or "threads connected"? They might show the spike you mentioned. "Threads_running" over about 30 is a bad sign. But that metric is transient, so one has to catch it in the act. – Rick James Jun 25 at 17:56
  • I have graphs on threads. Since I use connection pooling in ProxySQL, "threads connected" in MySQL is very stable at 100-200, but "threads running" is usually less than 10 (avg about 3). There were times that "threads running" was high, but have been fixed by tuning thread concurrency. I don't see a problem on threads at the moment. But the fsync/flushing behavior is still unsolved and concerning. – Carson Ip Jun 26 at 2:47

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.