MySql Memory problem & swap is 100%

Question

We have a live database running several thousand databases.

I have tested the configuration several times before this live went. but the memory utilization is now 93% of 40 GB RAM. The swap memory is full.

What we have seen is that the slave had a bug and was not replicated further. Now he lags behind several days. Could that be the problem?

GLOBAL STATUS

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 215930, seg size 215932, 5623 merges
merged operations:
 insert 1773, delete mark 236303, delete 71946
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 5610743, node heap has 157 buffer(s)
Hash table size 5610743, node heap has 2172 buffer(s)
Hash table size 5610743, node heap has 39 buffer(s)
Hash table size 5610743, node heap has 3050 buffer(s)
Hash table size 5610743, node heap has 1056 buffer(s)
Hash table size 5610743, node heap has 984 buffer(s)
Hash table size 5610743, node heap has 1290 buffer(s)
Hash table size 5610743, node heap has 72 buffer(s)
258.51 hash searches/s, 108.91 non-hash searches/s
---
LOG
---
Log sequence number 1620776677814
Log flushed up to   1620776677814
Pages flushed up to 1620776591395
Last checkpoint at  1620776591395
0 pending log flushes, 0 pending chkp writes
38575045 log i/o's done, 2.98 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 23225499648
Dictionary memory allocated 70308446
Buffer pool size   1384279
Free buffers       13312
Database pages     1362147
Old database pages 502557
Modified db pages  82
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 979965, not young 59656723
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5099852, created 19653707, written 36150135
0.00 reads/s, 0.00 creates/s, 7.91 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: 1362147, unzip_LRU len: 0
I/O sum[4628]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   106483
Free buffers       1024
Database pages     104786
Old database pages 38660
Modified db pages  25
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 77429, not young 4824546
0.00 youngs/s, 0.00 non-youngs/s
Pages read 394040, created 1517069, written 3477955
0.00 reads/s, 0.00 creates/s, 1.78 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: 104786, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   106483
Free buffers       1024
Database pages     104775
Old database pages 38656
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 78287, not young 4727154
0.00 youngs/s, 0.00 non-youngs/s
Pages read 396963, created 1518902, written 2376642
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: 104775, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   106483
Free buffers       1024
Database pages     104774
Old database pages 38656
Modified db pages  2
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 75699, not young 4658082
0.00 youngs/s, 0.00 non-youngs/s
Pages read 391585, created 1515480, written 2513307
0.00 reads/s, 0.00 creates/s, 0.27 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: 104774, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   106483
Free buffers       1024
Database pages     104784
Old database pages 38660
Modified db pages  20
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 75657, not young 4488749
0.00 youngs/s, 0.00 non-youngs/s
Pages read 391929, created 1518940, written 4053906
0.00 reads/s, 0.00 creates/s, 1.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: 104784, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   106483
Free buffers       1024
Database pages     104776
Old database pages 38657
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 74464, not young 4531420
0.00 youngs/s, 0.00 non-youngs/s
Pages read 392554, created 1510062, written 2667535
0.00 reads/s, 0.00 creates/s, 0.69 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: 104776, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   106483
Free buffers       1024
Database pages     104791
Old database pages 38662
Modified db pages  23
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 74577, not young 4489360
0.00 youngs/s, 0.00 non-youngs/s
Pages read 391181, created 1510676, written 2478167
0.00 reads/s, 0.00 creates/s, 1.80 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: 104791, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   106483
Free buffers       1024
Database pages     104796
Old database pages 38664
Modified db pages  1
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 75486, not young 4625322
0.00 youngs/s, 0.00 non-youngs/s
Pages read 392179, created 1508996, written 2470628
0.00 reads/s, 0.00 creates/s, 0.20 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: 104796, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   106483
Free buffers       1024
Database pages     104772
Old database pages 38655
Modified db pages  4
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 75670, not young 4592105
0.00 youngs/s, 0.00 non-youngs/s
Pages read 393926, created 1502483, written 3119121
0.00 reads/s, 0.00 creates/s, 0.47 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: 104772, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 8
Buffer pool size   106483
Free buffers       1024
Database pages     104777
Old database pages 38657
Modified db pages  1
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 75957, not young 4647493
0.00 youngs/s, 0.00 non-youngs/s
Pages read 392064, created 1509705, written 2991351
0.00 reads/s, 0.00 creates/s, 0.24 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: 104777, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 9
Buffer pool size   106483
Free buffers       1024
Database pages     104769
Old database pages 38654
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 76610, not young 4613488
0.00 youngs/s, 0.00 non-youngs/s
Pages read 393144, created 1509549, written 2431838
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: 104769, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 10
Buffer pool size   106483
Free buffers       1024
Database pages     104770
Old database pages 38654
Modified db pages  2
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 73017, not young 4445567
0.00 youngs/s, 0.00 non-youngs/s
Pages read 389437, created 1508954, written 2473286
0.00 reads/s, 0.00 creates/s, 0.18 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: 104770, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 11
Buffer pool size   106483
Free buffers       1024
Database pages     104788
Old database pages 38661
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 73345, not young 4399882
0.00 youngs/s, 0.00 non-youngs/s
Pages read 390836, created 1507702, written 2689493
0.00 reads/s, 0.00 creates/s, 0.04 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: 104788, unzip_LRU len: 0
I/O sum[356]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 12
Buffer pool size   106483
Free buffers       1024
Database pages     104789
Old database pages 38661
Modified db pages  4
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 73767, not young 4613555
0.00 youngs/s, 0.00 non-youngs/s
Pages read 390014, created 1515189, written 2406906
0.00 reads/s, 0.00 creates/s, 0.53 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: 104789, unzip_LRU len: 0
I/O sum[356]: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=1417, Main thread ID=139746845320960, state: sleeping
Number of rows inserted 395996479, updated 15665769, deleted 4779050, read 11353664732
20.71 inserts/s, 1.76 updates/s, 0.18 deletes/s, 860.09 reads/s

cat /etc/mysql/mysql.conf.d/mysqld.cnf

thread_cache_size       = 40

myisam-recover-options  = BACKUP


innodb_data_file_path = ibdata1:10M:autoextend
log_error = /var/log/mysql/error.log

server-id               = 11
log_bin                 = /var/lib/mysql/binlog/mysql-bin.log
innodb_buffer_pool_instances = 13
log-slave-updates


default_storage_engine=InnoDB
innodb_file_per_table=1
innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_buffer_pool_size = 20G
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb-flush-log-at-trx-commit = 1

wait_timeout = 300
interactive_timeout = 300
max_connections = 300



binlog_cache_size = 1M
sync_binlog = 8
expire_logs_days        = 7
max_binlog_size   = 128M
table_open_cache               = 10240
table_definition_cache         = 4096
max_user_connections           = 400
query-cache-type = 0
query_cache_size = 0

tmp-table-size                 = 32M
max-heap-table-size            = 32M
open-files-limit               = 65535

mysql version :

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.7.19                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| tls_version             | TLSv1,TLSv1.1               |
| version                 | 5.7.19-0ubuntu0.16.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | Linux     

Ram Status

free -m
           total       used     free      shared    buffers     cached 
mem:       40237       36748    287       252       3201        2730
swap:      12347       11792    555

I have provided the output of SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES externally because I have problems with the editor

SHOW GLOBAL VARIABLES https://gist.github.com/anonymous/744c6bac6f09196f994c078194c5d086

SHOW GLOBAL STATUS https://gist.github.com/anonymous/ddd51bb89ac933dbca5e5d61f88a83d7

Could someone please help me before this chrashed?

Answer 1

Looking at just these 3 items from SHOW GLOBAL STATUS:

| Com_stmt_execute                    | 134607738                                        |

| Com_stmt_close                      | 18971668                                         |

| Com_stmt_prepare                    | 22616559                                         |

My research usually finds prepare followed by execute then close. Here we have ~ 5 executes for each com_stmt_prepare and close is short by 4 million.

Is a close missing and leaving resources in use? Just my initial thoughts.

---

After reviewing your GLOBAL STATUS and GLOBAL VARIABLES, additional suggestions for improving response time follow:

• For your Linux version implement noatime and nodiratime to prevent posting last access information on a READ of data.

• In your mysqld.cnf [mysqld] section consider the following changes or additions:

  thread_cache_size = 100  # from 40 to minimize thread creation 
  key_buffer_size = 2M  # from 8M because less than 1M in use
  innodb_change_buffer_max_size = 3  # from 25 because less than 2% HWM used
  innodb_log_buffer_size = 64M  # from 16M to minimize Write frequency
  innodb_read_ahead_threshold = 8  # from 56, initiate read earlier to reduce READS
  max_join_size = 1024B  # from 18digit # to ~ 1Billion - a reasonable limit
  max_seeks_for_key = 64  # from 18digit # to 64 max seeks in optimizer
  max_write_lock_count = 128  # from 18digit # to give READ a shot at cpu
  sql_select_limit = 1024M  # from 18digit # to ~ 1Billion - a reasonable limit
  have_query_cache = 0  # from YES to avoid CPU overhead for QC that is not used
  innodb_print_all_deadlocks = 1  # you need in your error.log
  

• 2 minutes of your General Log would probably give us needed information to analyze the frequency of com_alter_table, com_create_table, and com_drop_table.

• We may see a way to minimize the pounding on your system every second. Drop table, Create table may be able to use TRUNCATE / INSERT instead.

• Many indexes appear to be missing based on scan counts.

Keep us aware of your progress, please.

Answer 2

Observations:

Version: 5.7.19-0ubuntu0.16.04.1-log 
40 GB of RAM 
Uptime = 25d 17:48:49 
You are not running on Windows. 
Running 64-bit version 
You appear to be running entirely (or mostly) InnoDB. 

The More Important Issues

Thousands of tables is usually a bad idea, both for the OS and for MySQL.

You set table_open_cache to 10240, yet it is set to only 400. Check ulimit in the OS. Apparently that is set so small that MySQL automatically decreased table_open_cache. See note below.

Clearly from several metrics, 400 is too small. It is not possible to say what the "optimal" value. Suggest you increase it (after fixing ulimit) until (Table_open_cache_misses / Uptime) gets below 2/sec.

But...

The buffer_pool is only 53% of RAM, so I am surprised that you are at 93%. Are there other applications chewing up lots of RAM? If so, either they need to shrink, or innodb_buffer_pool_size should be shrunk.

As you increase table_open_cache, you should decrease the buffer pool slightly to make room.

ALTER TABLE every second !!! Ouch. What is going on?

Set long_query_time=2 (or 1) and turn on the slowlog. This will help locate the table scans, etc, that are a performance drag. See http://mysql.rjweb.org/doc.php/mysql_analysis

If you are using parallel replication threads, see the note below.

Recommend you fix the above issues, then come back for more discussion.

Details and other observations

( innodb_buffer_pool_size / _ram ) = 21632M / 40960M = 52.8% -- % of RAM used for InnoDB buffer_pool

( Opened_tables ) = 22,915,896 / 2224129 = 10 /sec -- Frequency of opening Tables -- increase table_open_cache

( Opened_table_definitions ) = 8,815,912 / 2224129 = 4 /sec -- Frequency of opening .frm files -- Increase table_definition_cache and/or table_open_cache.

( open_files_limit ) = 1,024 -- ulimit -n -- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( Table_open_cache_overflows ) = 20,395,651 / 2224129 = 9.2 /sec -- May need to increase table_open_cache

( Table_open_cache_misses ) = 22,912,426 / 2224129 = 10 /sec -- May need to increase table_open_cache

( Innodb_os_log_written ) = 576,653,938,688 / 2224129 = 259271 /sec -- This is an indicator of how busy InnoDB is. -- Very idle or very busy InnoDB.

( bulk_insert_buffer_size / _ram ) = 8M / 40960M = 0.02% -- Buffer for multi-row INSERTs and LOAD DATA -- Too big could threaten RAM size. Too small could hinder such operations.

( (Com_insert + Com_update + Com_delete) / Com_commit ) = (11596313 + 12489901 + 2164707) / 11236257 = 2.34 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 16,244,739 / 2224129 = 7.3 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 16,244,739 / 147304751 = 11.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 22616559 - 18971668 ) / ( 22616559 + 18971668 ) = 8.8% -- Are you closing your prepared statements? -- Add Closes.

( Com_stmt_close / Com_stmt_prepare ) = 18,971,668 / 22616559 = 83.9% -- Prepared statements should be Closed. -- Check whether all Prepared statements are "Closed".

( Com_alter_table ) = 2,313,823 / 2224129 = 1 /sec -- Why so many ALTERs?

( slave_pending_jobs_size_max / max_allowed_packet ) = 16M / 4M = 400.0% -- For parallel slave threads -- slave_pending_jobs_size_max must not be less than max_allowed_packet

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( back_log / max_connections ) = 92 / 150 = 61.3%

( Aborted_connects / Connections ) = 442,453 / 2141555 = 20.7% -- Perhaps a hacker is trying to break in?

High values

These are unusually high; what is going on??

Com_create_db = 39 /HR
Com_create_table = 0.53 /sec
Com_create_user = 17 /HR
Com_drop_db = 32 /HR
Com_drop_table = 0.53 /sec
Com_drop_user = 8.3 /HR
Com_grant = 17 /HR
Com_rename_table = 0.49 /sec
Com_show_databases = 21 /HR
Com_stmt_reprepare = 3.6 /HR
Innodb_pages_created = 8.9 /sec
Open_table_definitions = 3,985
Prepared_stmt_count = 783
binlog_cache_size = 1.05e+6
eq_range_index_dive_limit = 200
sync_binlog = 8