MySql Memory problem & swap is 100%

innodbmemoryMySQL

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?

Best Answer

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.

Related Question