2

Im using 32vCPU and 120GB VM for Mysql 5.6.

  1. it's a dedicated MySQL server.
  2. 15000 IOPS SSD disk.
  3. Total database size 40G

This server was working fine for a couple of months. Memory usage is never crossed 60GB.

But a few days back the mysql process killed due to OOM. After restart memory usage is fine, but its gradually increasing within 6 to hrs it'll reach the maximum limit and killed by OOM Killer.

After installing the mysql, I used percona configuration wizard to generate the my.cnf file.

Here are the important parameters:

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 5000
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 50

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 90G

The last downtime happened yesterday. I have verified that the InnoDB buffer never crossed 30GB for the whole day.

Buffer Pool usage

enter image description here

Memory used:

The both highlighted points are the maximum limit. enter image description here

InnoDB IO

enter image description here

Mysql client thread activity:

enter image description here

Temo Objects:

enter image description here

Mysql Internal memory

enter image description here

From a shell script:

+------------------------------------------+--------------------+
|                          key_buffer_size |          32.000 MB |
|                         query_cache_size |           0.000 MB |
|                  innodb_buffer_pool_size |       92160.000 MB |
|          innodb_additional_mem_pool_size |           8.000 MB |
|                   innodb_log_buffer_size |           8.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |       92208.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           0.250 MB |
|                         read_buffer_size |           0.125 MB |
|                     read_rnd_buffer_size |           0.250 MB |
|                         join_buffer_size |           0.250 MB |
|                             thread_stack |           0.250 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |          32.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          33.156 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |               1399 |
|                          max_connections |               5000 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |      138593.594 MB |
|                              TOTAL (MAX) |      257989.250 MB |
+------------------------------------------+--------------------+

I just wanted to know that, InnoDB never crossed >30, but how mysql used all the memory?

  • The fact that MySQL is killed by the oomkiller does not mean it's MySQL memory use that is growing; oomkiller just picks the process with most usage. What else is running on the server, besides MySQL. – mustaccio Sep 12 '18 at 13:14
  • This is dedicated for mysql, other services. – Bhuvanesh Sep 12 '18 at 14:03
  • Mysql memory consumption consist of the global shared consumption and cumulated per-client resources. It would be very interesting to see other unimportant parameters of the my.cnf file. – Kondybas Sep 12 '18 at 17:01
  • I have verified that global buffers are 96GB – Bhuvanesh Sep 13 '18 at 12:12
  • 1
    Hi Kevin, I fixed this issue by reducing the max_prepare_statement_count value. Earlier it was the max value. – Bhuvanesh Sep 28 '18 at 20:28

Your Answer

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

Browse other questions tagged or ask your own question.