1

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
  • I'm facing something similar, though with a 8GB server. What's RSS/VSZ reported by ps aux? Can you add the message reported by OOM killer? As configured, you could need up to 256GB to support his config. it's probably unlikely you'll have 5000 connections using a 32MB temp table... but it could happen :) – Kevin Bott Sep 28 '18 at 18:45

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.