Question

Why MySQL is not releasing the memory ?

Posted October 12, 2019 1.4k views
MySQL

I have a droplet with 4 GB of memory and MySQL running on it, Apache and PHP.

I don’t understand why MySQL not releasing the memory and don’t stop to take more and more memory until reached the 4 GB and making the server crashed. I noticed when the server crash the disk was reading some data very fast (~150 Mb/s).

I have already tried to set innodbbufferpool_size to 64M but seems not to work.

Finnaly, I have add some swap space of 4 GB to avoid crashing for the moment.

Add a comment
florianbrgnr
By:
florianbrgnr
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
3 answers
KDSys October 12, 2019

Hi @florianbrgnr,

It’s possible your thread or query cache size actually cause the problems.

Can you try and add the following to your my.cnf file 

thread_cache_size = 10
query_cache_size = 50M

Once these values have been configured, go ahead and restart MySQL.

One last thing, which MySQL version are you using?

Kind regards,
KDSys

Reply Report
  • florianbrgnr October 12, 2019

    Those settings help to slow down the memory load. But the memory is still increasing. Is MySQL supposed to have a max memory ?

    I’m using MySQL 5.7.27 on Ubuntu 18.04 64 bits.

    Thansk for the help.

    Reply Report
    • KDSys October 13, 2019

      Hi @florianbrgnr,

      Unfortunately, there is no memorylimit/maxmemory limit setting in MySQL. The only way you can manage or limit the memory usage of MySQL is to lower the settings for cache, buffer and pool sizes(the exact settingnames depend on what storage engine you are using.

      Some settings that apply for the MyISAM engine (the default engine) is:

      table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=64M
thread_cache_size=128
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1

      For InnoDB the settings look like

      innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M

      Please note the above values are only examples. You can try by lowering them and see if settings them lower will actually help with your problem.

      An Additional suggestion would be upgrading MySQL 5.7 to MariaDB 10.3.

      Please keep us posted on the issue.

      Kind regard,
      KDSys

      Reply Report
andmoo October 15, 2019

Hello @florianbrgnr,

I’d like to offer some further advice here if I may. It’s hard to diagnose things exactly based on the detail provided but I hope I can add some value here.

MySQL thrives on caching data in memory but there are also some per-thread buffers that can cause some issues if your SQL is not optimal. Due to the fact your memory is shared between 2 critical processes we might experience OOM killer caused by normal activity on the host.

You can get a better picture of what’s going on with the server by using a utility to summarise resource utilisation.

Percona’s pt-summary and pt-mysql-summary are a couple of programs that you can use to check what’s going on. 

$> wget percona.com/get/pt-summary
$> chmod +x ./pt-summary
$> ./pt-summary > pt-summary.report.txt

$> wget percona.com/get/pt-summary
$> chmod +x ./pt-mysql-summary
$> ./pt-summary > pt-mysql-summary.report.txt

Are your crashes predictable? Are there any imports or long running jobs ongoing when your instance is killed by the OS? If you can capture the output of SHOW PROCESS LIST on the instance when the symptoms are in the 11th hour and post here.

If you run the two utilities (they are open source Perl scripts so feel free to check them out prior to running on your server) and post the results here I’ll check them out.

BR

Andmoo

Reply Report
  • florianbrgnr October 15, 2019

    Hi,

    My server doesn’t crash anymore grace of swap space. However MySQL still use 90% of memory and use swap space. For the process of MySQL I have a cron job that killed query over 20 seconds of execution time.

    pt-summary:

    # Percona Toolkit System Summary Report ######################
        Date | 2019-10-15 19:08:32 UTC (local TZ: UTC +0000)
    Hostname | gnews
      Uptime | 1 day,  9:09,  1 user,  load average: 1.00, 0.90, 0.56
    Platform | Linux
     Release | Ubuntu 18.04.3 LTS (bionic)
      Kernel | 4.15.0-65-generic
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.27
     SELinux | No SELinux detected
 Virtualized | No virtualization detected
# Processor ##################################################
  Processors | physical = 2, cores = 2, virtual = 2, hyperthreading = no
      Speeds | 2x2294.608
      Models | 2xIntel(R) Xeon(R) Gold 6140 CPU @ 2.30GHz
      Caches | 2x25344 KB
# Memory #####################################################
       Total | 3.9G
        Free | 105.4M
        Used | physical = 2.7G, swap allocated = 4.0G, swap used = 1.4G, virtual = 4.1G
      Shared | 9.6M
     Buffers | 1.0G
      Caches | 921.6M
       Dirty | 660 kB
     UsedRSS | 2.8G
  Swappiness | 10
 DirtyPolicy | 20, 10
 DirtyStatus | 0, 0
  Locator   Size     Speed             Form Factor   Type          Type Detail
  ========= ======== ================= ============= ============= ===========
# Mounted Filesystems ########################################
  Filesystem  Size Used Type     Opts                                                                                                 Mountpoint
  /dev/vda1    78G  24% ext4     rw,relatime,data=ordered                                                                             /
  /dev/vda15  105M   4% vfat     rw,relatime,fmask=0022,dmask=0022,codepage=437,iocharset=iso8859-1,shortname=mixed,errors=remount-ro /boot/efi
  tmpfs       2.0G   0% tmpfs    rw,nosuid,nodev                                                                                      /dev/shm
  tmpfs       2.0G   0% tmpfs    rw,nosuid,noexec,relatime,size=403964k,mode=755                                                      /dev/shm
  tmpfs       2.0G   0% tmpfs    rw,nosuid,nodev,noexec,relatime,size=5120k                                                           /dev/shm
  tmpfs       2.0G   0% tmpfs    rw,nosuid,nodev,relatime,size=403960k,mode=700,uid=1000,gid=1000                                     /dev/shm
  tmpfs       2.0G   0% tmpfs    ro,nosuid,nodev,noexec,mode=755                                                                      /dev/shm
  tmpfs       2.0G   0% tmpfs    rw,nosuid,nodev                                                                                      /sys/fs/cgroup
  tmpfs       2.0G   0% tmpfs    rw,nosuid,noexec,relatime,size=403964k,mode=755                                                      /sys/fs/cgroup
  tmpfs       2.0G   0% tmpfs    rw,nosuid,nodev,noexec,relatime,size=5120k                                                           /sys/fs/cgroup
  tmpfs       2.0G   0% tmpfs    rw,nosuid,nodev,relatime,size=403960k,mode=700,uid=1000,gid=1000                                     /sys/fs/cgroup
  tmpfs       2.0G   0% tmpfs    ro,nosuid,nodev,noexec,mode=755                                                                      /sys/fs/cgroup
  tmpfs       395M   0% tmpfs    rw,nosuid,nodev                                                                                      /run/user/1000
  tmpfs       395M   0% tmpfs    rw,nosuid,noexec,relatime,size=403964k,mode=755                                                      /run/user/1000
  tmpfs       395M   0% tmpfs    rw,nosuid,nodev,noexec,relatime,size=5120k                                                           /run/user/1000
  tmpfs       395M   0% tmpfs    rw,nosuid,nodev,relatime,size=403960k,mode=700,uid=1000,gid=1000                                     /run/user/1000
  tmpfs       395M   0% tmpfs    ro,nosuid,nodev,noexec,mode=755                                                                      /run/user/1000
  tmpfs       395M   1% tmpfs    rw,nosuid,nodev                                                                                      /run
  tmpfs       395M   1% tmpfs    rw,nosuid,noexec,relatime,size=403964k,mode=755                                                      /run
  tmpfs       395M   1% tmpfs    rw,nosuid,nodev,noexec,relatime,size=5120k                                                           /run
  tmpfs       395M   1% tmpfs    rw,nosuid,nodev,relatime,size=403960k,mode=700,uid=1000,gid=1000                                     /run
  tmpfs       395M   1% tmpfs    ro,nosuid,nodev,noexec,mode=755                                                                      /run
  tmpfs       5.0M   0% tmpfs    rw,nosuid,nodev                                                                                      /run/lock
  tmpfs       5.0M   0% tmpfs    rw,nosuid,noexec,relatime,size=403964k,mode=755                                                      /run/lock
  tmpfs       5.0M   0% tmpfs    rw,nosuid,nodev,noexec,relatime,size=5120k                                                           /run/lock
  tmpfs       5.0M   0% tmpfs    rw,nosuid,nodev,relatime,size=403960k,mode=700,uid=1000,gid=1000                                     /run/lock
  tmpfs       5.0M   0% tmpfs    ro,nosuid,nodev,noexec,mode=755                                                                      /run/lock
  udev        2.0G   0% devtmpfs rw,nosuid,relatime,size=2006800k,nr_inodes=501700,mode=755                                           /dev
# Disk Schedulers And Queue Size #############################
         vda | [none] 128
# Disk Partioning ############################################
# Kernel Inode State #########################################
dentry-state | 17701    4042    45  0   0   0
     file-nr | 1600 0   400207
    inode-nr | 17254    57
# LVM Volumes ################################################
Unable to collect information
# LVM Volume Groups ##########################################
Unable to collect information
# RAID Controller ############################################
  Controller | No RAID controller detected
# Network Config #############################################
  Controller | Red Hat, Inc. Virtio network device
 FIN Timeout | 60
  Port Range | 60999
# Interface Statistics #######################################
  interface  rx_bytes rx_packets  rx_errors   tx_bytes tx_packets  tx_errors
  ========= ========= ========== ========== ========== ========== ==========
  lo       3500000000     150000          0 3500000000     150000          0
  eth0     3000000000    3500000          0 3500000000    3000000          0
# Network Devices ############################################
  Device    Speed     Duplex
  ========= ========= =========
  eth0       Unknown!   Unknown!  
# Network Connections ########################################
  Connections from remote IP addresses
    3.216.54.180        1
    13.107.21.200      10
    34.200.27.109       1
    34.202.153.183      2
    54.184.58.1         1
    74.121.199.234      2
    127.0.0.1          30
    164.132.170.85     60
    172.217.3.110       1
    178.197.249.14      5
    204.79.197.200     60
    209.97.175.88       2
  Connections to local IP addresses
    127.0.0.1          30
    157.230.179.93    125
  Connections to top 10 local ports
    443                60
    56958               1
    56960               1
    56962               1
    56968               1
    56970               1
    56974               1
    56976               1
    56978               1
    56980               1
  States of connections
    CLOSE_WAIT          8
    ESTABLISHED         5
    FIN_WAIT2           1
    LISTEN              8
    TIME_WAIT         150
# Top Processes ##############################################
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 6508 www-data  20   0  585968  20976  15664 S  12.5  0.5   0:07.29 apache2
 8492 florian   20   0   44400   3964   3452 R   6.2  0.1   0:00.01 top
    1 root      20   0  225504   5292   3584 S   0.0  0.1   0:13.34 systemd
    2 root      20   0       0      0      0 S   0.0  0.0   0:00.02 kthreadd
    4 root       0 -20       0      0      0 I   0.0  0.0   0:00.00 kworker/0:+
    6 root       0 -20       0      0      0 I   0.0  0.0   0:00.00 mm_percpu_+
    7 root      20   0       0      0      0 S   0.0  0.0   0:01.64 ksoftirqd/0
    8 root      20   0       0      0      0 I   0.0  0.0   1:20.52 rcu_sched
    9 root      20   0       0      0      0 I   0.0  0.0   0:00.00 rcu_bh
# Notable Processes ##########################################
  PID    OOM    COMMAND
 1005    -17    sshd
# Simplified and fuzzy rounded vmstat (wait please) ##########
  procs  ---swap-- -----io---- ---system---- --------cpu--------
   r  b    si   so    bi    bo     ir     cs  us  sy  il  wa  st
   2  1   100  125  2000  1000    100     60  14   1  80   4   0
   1  0     0    0  1000  1750    600   1500  30   8  56   6   0
   0  0     0    0   125  1500    300    500  16   1  79   4   0
   1  0     0    0   150  1500    300    500  13   1  79   7   0
   0  0    20    0   100  1500    300    500  10   1  81   8   0
# Memory mamagement ##########################################
Transparent huge pages are enabled.
# The End ####################################################

    pt-mysql-summary:

    # Percona Toolkit MySQL Summary Report #######################
              System time | 2019-10-15 19:13:38 UTC (local TZ: UTC +0000)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
                                   0    0   
# MySQL Executable ###########################################
       Path to executable | /usr/sbin/mysqld
              Has symbols | No
# Slave Hosts ################################################
No slaves found
# Report On Port 3306 ########################################
                     User | root@localhost
                     Time | 2019-10-15 19:13:38 (UTC)
                 Hostname | gnews
                  Version | 5.7.27-0ubuntu0.18.04.1 (Ubuntu)
                 Built On | Linux x86_64
                  Started | 2019-10-15 17:58 (up 0+01:14:49)
                Databases | 6
                  Datadir | /var/lib/mysql/
                Processes | 1 connected, 1 running
              Replication | Is not a slave, has 0 slaves connected
                  Pidfile | /run/mysqld/mysqld.pid (exists)
# Processlist ################################################

  Command                        COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  Query                                 1       1         0         0

  User                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  root                                  1       1         0         0

  Host                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  localhost                             1       1         0         0

  db                             COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  NULL                                  1       1         0         0

  State                          COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  starting                              1       1         0         0

# Status Counters (Wait 10 Seconds) ##########################
Variable                                Per day  Per second     10 secs
Aborted_clients                              40                        
Aborted_connects                             80                        
Bytes_received                        250000000        3000        3000
Bytes_sent                           1250000000       12500       15000
Com_change_db                               700                        
Com_delete                                  100                        
Com_insert                               100000           1            
Com_kill                                     40                        
Com_select                               175000           1           3
Com_set_option                           125000           1            
Com_show_binlogs                             20                        
Com_show_databases                           20                        
Com_show_engine_status                       20                        
Com_show_master_status                       20                        
Com_show_plugins                             20                        
Com_show_processlist                       1500                        
Com_show_slave_hosts                         20                        
Com_show_slave_status                        20                        
Com_show_status                             100                        
Com_show_storage_engines                     20                        
Com_show_variables                           40                        
Com_stmt_execute                           3000                        
Com_stmt_close                             3000                        
Com_stmt_prepare                           3000                        
Com_update                                70000                        
Connections                              125000           1           2
Created_tmp_disk_tables                     250                       1
Created_tmp_files                           125                        
Created_tmp_tables                         1750                       6
Flush_commands                               20                        
Handler_commit                           350000           3           2
Handler_delete                             5000                        
Handler_external_lock                    700000           7           6
Handler_read_first                         1750                        
Handler_read_key                        7000000          80          30
Handler_read_last                            40                        
Handler_read_next                     600000000        7000            
Handler_read_prev                           350                        
Handler_read_rnd                        2250000          25          25
Handler_read_rnd_next                 600000000        7000          80
Handler_rollback                            100                        
Handler_update                          2500000          30            
Handler_write                            450000           5          40
Innodb_buffer_pool_bytes_data        9000000000      100000      100000
Innodb_buffer_pool_pages_flushed        1500000          20           6
Innodb_buffer_pool_read_ahead           5000000          60            
Innodb_buffer_pool_read_ahead_evicted       22500                        
Innodb_buffer_pool_read_requests     1250000000       12500        8000
Innodb_buffer_pool_reads               15000000         175          20
Innodb_buffer_pool_wait_free              12500                        
Innodb_buffer_pool_write_requests       8000000          90          70
Innodb_data_fsyncs                       600000           6           5
Innodb_data_read                    300000000000     3500000      300000
Innodb_data_reads                      20000000         225          20
Innodb_data_writes                      2000000          25           9
Innodb_data_written                 50000000000      600000      200000
Innodb_dblwr_pages_written              1500000          20           5
Innodb_dblwr_writes                       70000                        
Innodb_log_write_requests               1250000          15           7
Innodb_log_writes                        250000           2           2
Innodb_os_log_fsyncs                     300000           3           3
Innodb_os_log_written                 800000000        9000        6000
Innodb_pages_created                      12500                        
Innodb_pages_read                      20000000         225          20
Innodb_pages_written                    1500000          20           6
Innodb_row_lock_time                       4000                        
Innodb_row_lock_waits                       300                        
Innodb_rows_deleted                        5000                        
Innodb_rows_inserted                     100000           1           5
Innodb_rows_read                     1250000000       15000          35
Innodb_rows_updated                      100000           1            
Innodb_num_open_files                      1000                        
Innodb_available_undo_logs                 2500                        
Key_read_requests                           125                        
Key_reads                                    60                        
Max_execution_time_set                      350                        
Open_table_definitions                     2500                        
Opened_files                               3000                        
Opened_table_definitions                   2500                        
Opened_tables                              6000                        
Qcache_hits                              250000           3           2
Qcache_inserts                           125000           1           1
Qcache_not_cached                         40000                       2
Queries                                  900000          10          10
Questions                                900000          10          10
Select_range                              35000                        
Select_scan                                2000                        
Sort_range                                30000                        
Sort_rows                               2250000          25          25
Sort_scan                                   350                        
Table_locks_immediate                      2000                        
Table_open_cache_hits                    350000           3           3
Table_open_cache_misses                    6000                        
Threads_created                             200                        
Uptime                                    90000           1           1
# Table cache ################################################
                     Size | 2000
                    Usage | 15%
# Key Percona Server features ################################
      Table & Index Stats | Not Supported
     Multiple I/O Threads | Enabled
     Corruption Resilient | Not Supported
      Durable Replication | Not Supported
     Import InnoDB Tables | Not Supported
     Fast Server Restarts | Not Supported
         Enhanced Logging | Not Supported
     Replica Perf Logging | Disabled
      Response Time Hist. | Not Supported
          Smooth Flushing | Not Supported
      HandlerSocket NoSQL | Not Supported
           Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
       InnoDB compression | ACTIVE
# Query cache ################################################
         query_cache_type | ON
                     Size | 64.0M
                    Usage | 0%
         HitToInsertRatio | 200%
# Schema #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
                      SSL | No
     Explicit LOCK TABLES | No
           Delayed Insert | No
          XA Transactions | No
              NDB Cluster | No
      Prepared Statements | Yes
 Prepared statement count | 0
# InnoDB #####################################################
                  Version | 5.7.27
         Buffer Pool Size | 512.0M
         Buffer Pool Fill | 100%
        Buffer Pool Dirty | 0%
           File Per Table | ON
                Page Size | 16k
            Log File Size | 2 * 128.0M = 256.0M
          Log Buffer Size | 16M
             Flush Method | 
      Flush Log At Commit | 1
               XA Support | ON
                Checksums | ON
              Doublewrite | ON
          R/W I/O Threads | 4 4
             I/O Capacity | 200
       Thread Concurrency | 0
      Concurrency Tickets | 5000
       Commit Concurrency | 0
      Txn Isolation Level | REPEATABLE-READ
        Adaptive Flushing | ON
      Adaptive Checkpoint | 
           Checkpoint Age | 9
             InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
       Oldest Transaction | 0 Seconds
         History List Len | 14
               Read Views | 0
         Undo Log Entries | 1 transactions, 1 total undo, 1 max undo
        Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
       Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
      Pending I/O Flushes | 0 buf pool, 0 log
       Transaction States | 1xnot started
# MyISAM #####################################################
                Key Cache | 16.0M
                 Pct Used | 20%
                Unflushed | 0%
# Security ###################################################
                    Users | 5 users, 0 anon, 0 w/o pw, 0 old pw
            Old Passwords | 0
# Encryption #################################################
No keyring plugins found
# Binary Logging #############################################
# Noteworthy Variables #######################################
     Auto-Inc Incr/Offset | 1/1
   default_storage_engine | InnoDB
               flush_time | 0
             init_connect | 
                init_file | 
                 sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         join_buffer_size | 256k
         sort_buffer_size | 256k
         read_buffer_size | 128k
     read_rnd_buffer_size | 256k
       bulk_insert_buffer | 0.00
      max_heap_table_size | 16M
           tmp_table_size | 16M
       max_allowed_packet | 16M
             thread_stack | 192k
                      log | 
                log_error | /var/log/mysql/error.log
             log_warnings | 2
         log_slow_queries | 
log_queries_not_using_indexes | OFF
        log_slave_updates | OFF
# Configuration File #########################################
              Config File | /etc/mysql/my.cnf

[mysqld]
thread_cache_size                   = 128
query_cache_limit                   = 1M
query_cache_size                    = 64M
query_cache_type                    = 1
innodb_buffer_pool_size             = 512M
innodb_log_file_size                = 128M
innodb_log_buffer_size              = 16M
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 6658
# The End ####################################################
    Reply Report
andmoo October 16, 2019

@florianbrgnr,

The output you attached doesn’t raise any flags for me so the next thing I would recommend is that you check out the SQL load on the system. One thing that could be happening is expensive queries are utilising memory to complete their tasks and this memory is not reflected as returned to the system however it’s still available for processes to use should it be required. The problem of OOM killer is that the (expensive) SQL queries will continue to arrive and push the server towards using swap or the OOM Killer will kill the process using the most memory which will almost always be MySQL.

There’s another utility you can use to analyse the MySQL Slow Log file that will give you some insight into your expensive queries. My advice is to spend some time tuning the worst offenders so you can reduce the memory needed to handle them. 

$> wget percona.com/get/pt-query-digest
$> chmod +x ./pt-query-digest
$> ./pt-query-digest {mysql slow log file path} > pt-query-digest.report.txt

The result of which will have a summary of queries ranked by load that you can analyse by using the EXPLAIN command.

e.g.

mysql> EXPLAIN select * from user ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |  474 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Good luck!

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help