O uso de memória do MySQL é muito alto

Question

O uso de memória do MySQL é muito alto

#1 resposta do (2 votos)
#2 resposta do (0 votos)
#3 resposta do (0 votos)

2

Estou executando o Ubuntu 16.04 em um sistema com 64 GB de RAM + 64 GB de partição swap. Não é um servidor dedicado, então existem outros processos sendo executados ao mesmo tempo. De qualquer forma, quando o daemon do MySQL é interrompido, o uso máximo de memória é aprox. 38Gb para que o MySQL possa usar até 25Gb por conta própria sem trocar (demais IMHO).

htop está relatando sobre 1Gb RES (VIRT 2.6Gb) quando o daemon do MySQL é iniciado.

Tudo funciona bem, mas aumenta lentamente o uso da memória até o total da memória do sistema em cerca de 2 dias (memória física e memória swap).

Quando está prestes a entrar em colapso, publique relatórios sobre 25Gb RES (96Gb VIRT). Então, de alguma forma, o MySQL é reiniciado automaticamente e estamos de volta à estaca zero.

Eu executei o script MySQLTuner no momento de pico de uso da memória:

# ./mysqltuner.pl
 >>  MySQLTuner 1.7.10 - Major Hayden <[email protected]>
 >>  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.

Notas:

Este sistema está usando apenas o mecanismo MyISAM. O mecanismo InnoDB está sendo usado internamente apenas pelo MySQL.
De acordo com isso, Máximo uso de memória possível: 1.5G (o que obviamente não é verdade!)
Erros em /var/log/mysql/error.log são apenas algumas conexões anuladas

Minha configuração atual no meu arquivo my.cnf é:

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

Já experimentei valores diferentes para key_buffer_size, thread_stack, read_buffer_size, sort_buffer_size, join_buffer_size e ativar / desativar o query_cache . Nenhum deles faz diferença significativa.

Alguém pode ajudar?

Obrigado antecipadamente.

Editar:

Mais algumas informações conforme solicitado:

# 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

Editar 2:

ainda mais informações aqui link

mysql memory

por garsax 27.09.2018 / 16:11

3 respostas

Tags mysql memory

Registre variável ansiável em múltiplas tarefas NGINX Reescreva o subdomínio com múltiplos. Como -

score 2 · Answer 1

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.

Se o OOM-killer estiver terminando, isso seria visível no syslog. A reinicialização automática implica em seus scripts de inicialização ou em algum outro sistema de HA interposto. Você deve descobrir o que aconteceu especificamente.

Bancos de dados geralmente têm memória compartilhada grande e memória de processo pequena, e não o contrário.

Ative esse log de consultas lentas e encontre quais consultas não estão usando índices. Observe os aplicativos que usam esse banco de dados e determine o que eles estavam fazendo no momento. Processos de banco de dados alocam dezenas de GB de RAM por um motivo.

score 0 · Answer 2

Sugestão a considerar para o sistema operacional Ubuntu

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

Para tornar essa alteração persistente nas reinicializações do sistema operacional, consulte este URL

link

Os detalhes do seu Ubuntu podem ser ligeiramente diferentes.

Sugestões a considerar para sua seção my.cnf [mysqld] Taxa por segundo = 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

Por favor, veja meu perfil, Perfil de rede para informações de contato, incluindo meu Skype ID e entre em contato comigo depois de aplicar as sugestões + 3 dias.

score 0 · Answer 3

De seus dados de 8 a 18 de outubro, mostre os dados do STATUS GLOBAL, a indicação é que você tem 1.860 com_prepare_sql, 1.860 com_execute_sql e 798 com_dealloc_sql. Isso deixa os recursos em uso para 1.062 operações preparadas que NUNCA foram lançadas pelo MySQL. Resultados na OOM ao longo do tempo. Revise as documentações de sintaxe PREPARE, por favor. DEALLOC / CLOSE é necessário, antes de sair da sessão. Alguma das sugestões de 7 de outubro de 18 foi aplicada a partir de hoje? A conexão do Skype seria muito útil quando você tiver alguns minutos. Estou em UTC -6 horas. Skype ID é wlhauck atsign aol ponto com.