4

I'm running Ubuntu 16.04 on a system with 64Gb RAM + 64Gb of swap partition. It's not a dedicated server so there're other processes running at the same time. Anyway when MySQL daemon is stopped, the maximum memory usage is aprox. 38Gb so MySQL could use up to 25Gb for its own without swapping (way too much IMHO).

htop is reporting about 1Gb RES (2.6Gb VIRT) when the MySQL daemon is started.

Everything works well but, it slowly increases the memory usage up to fullfil the entire system memory in about 2 days (phisical memory and swap memory).

When it's about to collapse htop reports about 25Gb RES (96Gb VIRT). Then somehow MySQL is automatically restarted and we're back to square one.

I ran the MySQLTuner script at the moment of peak memory usage:

# ./mysqltuner.pl
 >>  MySQLTuner 1.7.10 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 5.7.23-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(843B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 5 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MEMORY tables: 0B (Tables: 1)
[--] Data in MyISAM tables: 35.3G (Tables: 2792)
[--] Data in InnoDB tables: 16.0K (Tables: 1)
[OK] Total fragmented tables: 0

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 12h 20m 20s (8M q [61.889 qps], 171K conn, TX: 2G, RX: 2G)
[--] Reads / Writes: 65% / 35%
[--] Binary logging is disabled
[--] Physical Memory     : 62.8G
[--] Max MySQL memory    : 1.5G
[--] Other process memory: 82.6G
[--] Total buffers: 1.2G global + 1.8M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.2G (1.97% of installed RAM)
[OK] Maximum possible memory usage: 1.5G (2.33% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/8M)
[OK] Highest usage of available connections: 13% (20/151)
[OK] Aborted connections: 0.60%  (1037/171776)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 25.6% (557K cached / 2M selects)
[!!] Query cache prunes per day: 386310
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 57882
[OK] Temporary tables created on disk: 3% (52K on disk / 1M total)
[OK] Thread cache hit rate: 99% (20 created / 171K connections)
[!!] Table cache hit rate: 0% (416 open / 1M opened)
[OK] Open file limit used: 46% (481/1K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 38.5% (413M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/5.3G
[OK] Read Key buffer hit rate: 100.0% (42B cached / 7M reads)
[!!] Write Key buffer hit rate: 5.0% (4M cached / 222K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (5574849 hits/ 5575178 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control error line(s) into /var/log/mysql/error.log file
    Restrict Host for user@% to user@SpecificDNSorIp
    Dedicate this server to your database for highest performance.
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://...
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache (431)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://...
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 32M)
    join_buffer_size (> 512.0K, or always use indexes with JOINs)
    table_open_cache (> 431)
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Notes:

  • This system is using the MyISAM engine only. InnoDB engine is being used internally by MySQL itself only.
  • According to this, Maximum possible memory usage: 1.5G (which is obviously not true!)
  • Errors in /var/log/mysql/error.log are just some Aborted connections

My current config in my my.cnf file is:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#performance-schema=0

skip-name-resolve
lower_case_table_names = 1
default-storage-engine = MYISAM
character_set_server=latin1
collation-server=latin1_spanish_ci
secure_file_priv =
#sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#skip-innodb

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 10.88.1.5

#
# * Fine Tuning
#
key_buffer_size         = 1G
max_allowed_packet      = 32M
thread_stack            = 256K
thread_cache_size       = 24
tmp_table_size          = 32M
max_heap_table_size     = 32M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections         = 100
#table_open_cache        = 64
#thread_concurrency      = 10

read_buffer_size        = 256K
sort_buffer_size        = 512K
join_buffer_size        = 512K

#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 32M
query_cache_type        = 1

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
default_tmp_storage_engine = MYISAM

I already tried different values for key_buffer_size, thread_stack, read_buffer_size, sort_buffer_size, join_buffer_size and enabling/disabling the query_cache. None of them makes any significant difference.

Can anyone help?

Thanks in advance.

Edit:

Some more info as requested:

# ps eaxo rss,vsz,pid,command --sort rss | tail -n 55
16732 2534624 107493 /usr/sbin/named -f -u bind LANG=en_US.UTF-8 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin RESOLVCONF=no OPTIONS=-u bind
17732 385232 139905 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
17744 466856 172740 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
18240 466856 172737 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
19252 466868 172722 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
19412 466868 132925 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
19456 466872 172729 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
19948 466888 172731 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
20004 466868 132953 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
20996 466812 130247 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
21096 466868 102906 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
21484 388864 163258 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
21792 466868  72842 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
22000 466868 102905 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
23016 388972 164428 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
23392 466880  26122 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
23472 466868  94437 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
28588 397504 163212 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
30320 388988 164587 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
31516 390568 164754 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
42904 405572 176109 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
43316 320100  93396 /usr/bin/php /var/www/src/coodebox/scripts/coode_reviews.php 60 APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=en_US.UTF-8 APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/var/www/html/coodebox/ws/REST
56732 414504 164502 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
1098744 2794316 45939 /usr/lib/virtualbox/VBoxHeadless --comment XP-5012 --startvm 2abd8bb1-4f00-4362-819c-b10b8dd4b871 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1103480 2798412 45727 /usr/lib/virtualbox/VBoxHeadless --comment XP-5003 --startvm 4bbc1a71-461f-41d1-b69d-b284f99500cd --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1106228 2800460 45721 /usr/lib/virtualbox/VBoxHeadless --comment XP-5002 --startvm 51ecd0c8-cbc5-42fb-a72e-d08da1a736f4 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1110200 2806604 45791 /usr/lib/virtualbox/VBoxHeadless --comment XP-5006 --startvm 8eb5368c-56a4-4e4e-8ba2-8dec3a961050 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1114872 2804556 45885 /usr/lib/virtualbox/VBoxHeadless --comment XP-5010 --startvm ac303817-9449-417e-920c-a2f0fe893a0a --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1115996 2810700 45909 /usr/lib/virtualbox/VBoxHeadless --comment XP-5011 --startvm 17c097df-421f-4d67-85c7-3be00228eb72 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1117156 2792268 45763 /usr/lib/virtualbox/VBoxHeadless --comment XP-5005 --startvm c5299acb-3b9f-422c-90d8-2fe31725d9c0 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1117404 2792268 45863 /usr/lib/virtualbox/VBoxHeadless --comment XP-5009 --startvm a0fe46f9-7a1c-47ab-b3b4-aa20441db26c --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1128424 2814796 45715 /usr/lib/virtualbox/VBoxHeadless --comment XP-5001 --startvm b58e7653-0d72-42b5-b9a7-3d692b76e6fc --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1139780 2814796 45836 /usr/lib/virtualbox/VBoxHeadless --comment XP-5008 --startvm fb572cbd-b78d-4ef6-b6f8-a37c0d9510c8 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1140052 2837324 45743 /usr/lib/virtualbox/VBoxHeadless --comment XP-5004 --startvm af874537-e9bd-4e2e-897b-25001e5b6f2f --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1144164 2922436 45954 /usr/lib/virtualbox/VBoxHeadless --comment XP-5013 --startvm 3ed3b1a1-bd37-42c8-97c6-d6e6c4c25c4e --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1172692 2798412 46009 /usr/lib/virtualbox/VBoxHeadless --comment XP-5015 --startvm fffe8b4c-e0fc-4392-9d23-0685e8e4d091 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1218940 2894668 46627 /usr/lib/virtualbox/VBoxHeadless --comment XP-5007 --startvm d551f639-b6ac-4208-b122-6a9a47825d1e --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1246860 3037460 2271 /usr/lib/virtualbox/VBoxHeadless --comment A7-7004 --startvm ac7998be-0195-4a70-b90a-36b3c9bee5f7 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1349212 3046220 45982 /usr/lib/virtualbox/VBoxHeadless --comment XP-5014 --startvm 9f6a2cf1-933c-449b-8ad5-250453454df7 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1400096 3189792 76852 /usr/lib/virtualbox/VBoxHeadless --comment W7-6002 --startvm 2b62c82d-81e9-4fac-9a91-558cd37be161 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1769280 3549124 47192 /usr/lib/virtualbox/VBoxHeadless --comment ASHOINDEX2 --startvm 2b975e4c-81ce-4637-a990-c139478ca3ad --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1862860 3562476 18827 /usr/lib/virtualbox/VBoxHeadless --comment W7-6004 --startvm 07fe24bf-f61e-4a31-8698-8bb81ee0aa58 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
2182876 4030316 46788 /usr/lib/virtualbox/VBoxHeadless --comment W7-CGS --startvm 176bbaa0-14d0-4710-a39a-4efb27c67cce --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
2203432 4094584 44848 /usr/lib/virtualbox/VBoxHeadless --comment W2008R2_Terra AD --startvm 8d23ece3-c06e-4dd4-a4ec-ba979841e27c --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
2471096 4306412 106689 /usr/lib/virtualbox/VBoxHeadless --comment W7-6050 --startvm 7eb6e7bd-c191-42e0-9457-533eb5cae4f8 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
4236200 6012176 84909 /usr/lib/virtualbox/VBoxHeadless --comment W7_Conta --startvm a7943c66-ebd6-4979-8413-fb34dda3d080 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
5368420 7103980 46905 /usr/lib/virtualbox/VBoxHeadless --comment W7-6003 --startvm 2c34c221-8ebb-49c1-9efe-3a5a48af2eab --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
5628600 7465120 189125 /usr/lib/virtualbox/VBoxHeadless --comment W7-6001 --startvm 9e4f415e-8754-477d-acc5-a7de4248fc52 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
6344388 8120096 47107 /usr/lib/virtualbox/VBoxHeadless --comment W7-6009 --startvm bdef968a-ef10-4a7f-91c0-7938f59d6088 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
6385324 8192928 86248 /usr/lib/virtualbox/VBoxHeadless --comment W7-6012 --startvm 8322842f-2a8f-45b0-b739-671691ada611 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
6690988 8454508 47040 /usr/lib/virtualbox/VBoxHeadless --comment W7-6007 --startvm 8c1d5eca-3b93-448a-abba-875dc321f3a2 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
7269696 9028588 46972 /usr/lib/virtualbox/VBoxHeadless --comment W7-6005 --startvm 0db152e4-681d-4617-a07d-2d76571fbbb0 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
7510940 9319584 47005 /usr/lib/virtualbox/VBoxHeadless --comment W7-6006 --startvm e4f2507c-172c-4314-a44a-43e3ef66fcfb --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
7877836 9697440 47073 /usr/lib/virtualbox/VBoxHeadless --comment W7-6008 --startvm f2033ed9-aa7b-4b38-ac3f-f21e577a5985 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
17632060 65790608 137687 /usr/sbin/mysqld LANG=en_US.UTF-8 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin HOME=/nonexistent LOGNAME=mysql USER=mysql SHELL=/bin/false

# cat /proc/meminfo
MemTotal:       65852816 kB
MemFree:          282728 kB
MemAvailable:    7572992 kB
Buffers:          339380 kB
Cached:          6791312 kB
SwapCached:        51144 kB
Active:         18524112 kB
Inactive:        7280780 kB
Active(anon):   15971052 kB
Inactive(anon):  2739776 kB
Active(file):    2553060 kB
Inactive(file):  4541004 kB
Unevictable:       13632 kB
Mlocked:           13632 kB
SwapTotal:      67009532 kB
SwapFree:       24527708 kB
Dirty:              1076 kB
Writeback:             0 kB
AnonPages:      18640248 kB
Mapped:         37892856 kB
Shmem:             32504 kB
Slab:            1245464 kB
SReclaimable:     693236 kB
SUnreclaim:       552228 kB
KernelStack:       25712 kB
PageTables:       356720 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    99935940 kB
Committed_AS:   93746836 kB
VmallocTotal:   34359738367 kB
VmallocUsed:           0 kB
VmallocChunk:          0 kB
HardwareCorrupted:     0 kB
AnonHugePages:     18432 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:     1171636 kB
DirectMap2M:    54304768 kB
DirectMap1G:    13631488 kB

Edit 2:

even more info here https://pastebin.com/qWcmmNtU

  • Max MySQL memory: 1.5G Other process memory: 82.6G. You need to look for other processes consuming memory. – AlexD Sep 27 '18 at 18:28
  • 1
    According to htop, it is the mysql process the one that's consuming that kind of memory, no other processes. Also when mysql is stopped the memory gets freed. So it is mysql and no other process – garsax Sep 28 '18 at 6:43
  • What else runs on this machine, and about how much memory are those services using? The contents of /proc/meminfo and the first few lines of that htop will be helpful. – John Mahowald Sep 28 '18 at 12:56
  • 1
    Please provide output of ps eaxo rss,vsz,pid,command --sort rss – AlexD Sep 28 '18 at 15:27
  • I was going to ask you to show the actual htop output, but @AlexD's command will give better information. Please edit your question to show its output, at a time when MySQL's memory usage appears to be high. – Michael Hampton Sep 28 '18 at 17:18
2

When it's about to collapse htop reports about 25Gb RES (96Gb VIRT). Then somehow MySQL is automatically restarted and we're back to square one.

If the OOM-killer is terminating it, that would be visible in syslog. Automatically restarting implies your init scripts or some other HA system intervened. You should find out what happened specifically.

Databases usually have large shared memory and small process memory, not the other way around.

Enable that slow query log and find what queries are not using indexes. Look at the applications using this database and determine what they were doing at the time. Database processes allocate tens of GB of RAM for a reason.

  • 1
    I found this on my syslog: Oct 1 12:32:33 sol kernel: [2069786.519988] Out of memory: Kill process 119396 (mysqld) score 684 or sacrifice child Oct 1 12:32:33 sol kernel: [2069786.520450] Killed process 119396 (mysqld) total-vm:102262508kB, anon-rss:25614296kB, file-rss:0kB ... Oct 1 12:32:36 sol systemd[1]: Stopped MySQL Community Server. Oct 1 12:32:36 sol systemd[1]: Starting MySQL Community Server... – garsax Oct 2 '18 at 14:44
0

Suggestion to consider for Ubuntu OS

ulimit -a    report will likely reveal low number of Open Files available.

ulimit -n 48000    will enable additional file handles to the OS and MySQL

To make this change persistent across OS restarts see this URL

https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

Your ubuntu details may be slightly different.

Suggestions to consider for your my.cnf [mysqld] section Rate Per Second=RPS

open_files_limit=30000  # from ~ 1K to support your activity
query_cache_min_res_unit=512  # from 4096 to conserve QC space used
query_prealloc-size=32K  # from 8K to minimize malloc activity
query_alloc_block_size=32k  # from 8K to reduce VmallocTotal count of meminfo
table_open_cache=10000  # from 416 to reduce tables_opened RPS
table_definition_cache=3000  # ? to reduce opened_table_definitions count
key_buffer_size=4G  # from 1G to support 5.3G of MyISAM indexes per mysqltuner.pl
key_cache_age_threshold=7200  # from 300 seconds to reduce key_reads RPS
thread_cache_size=100  # from 24 to support 171K connections.  CAP is 100 per V5.7 refman
  • 1
    Thanks @wilson-hauck. I'm applying those changes and I'll come back to post whether the problem is solved or not – garsax Oct 8 '18 at 16:28
0

According to duck typing approach that's a memory leakage. Since you're on 5.7 you can use its built-in tooling to find out who's to blame: "… The good news is: starting with MySQL 5.7 we have memory allocation in performance_schema. Here is how we can use it …"

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.