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.

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

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

  • 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.

    • 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

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

  • 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 ####################################################
    
    

@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!

Submit an Answer