Estou gerenciando um servidor com as seguintes configurações e ajustei o MySQL usando mysqltuner.pl. No entanto, o servidor acabou consumindo 100% da memória instalada. Peço que você aconselhe para corrigir isso.
Ubuntu Server 18.04 RAM: 16 GB CPUs: 4 MySQL: 5.7.31
A seguir está o arquivo de configuração do meu servidor MySQL
[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
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 256
performance_schema=0
#performance-schema-instrument='memory/%=COUNTED'
#
# * Fine Tuning
#
key_buffer_size = 1M
max_allowed_packet = 24M
thread_stack = 192K
thread_cache_size = 9
tmp_table_size = 48M
max_heap_table_size = 48M
thread_cache_size = 12
join_buffer_size = 4M
wait_timeout = 120 #set to 10 minutes from 8 hours
interactive_timeout = 120 #set to 15 minutes from 8 hours
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options = BACKUP
max_connections = 275
open_files_limit = 8192
table_open_cache = 2750
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 256K
query_cache_size = 96M
query_cache_min_res_unit = 2k
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
slow_query_log = 1
slow_query_log_file = /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
Saída Mysqltuner.pl recebida agora há pouco
>> MySQLTuner 1.6.4 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[[0;32mOK[0m] Logged in using credentials from debian maintenance account.
[[0;34m--[0m] Skipped version check for MySQLTuner script
[[0;32mOK[0m] Currently running supported MySQL version 5.7.31-0ubuntu0.18.04.1-log
[[0;32mOK[0m] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;31m-FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m
[[0;34m--[0m] Data in InnoDB tables: 7G (Tables: 228)
[[0;31m!![0m] Total fragmented tables: 63
-------- Security Recommendations -------------------------------------------
[[0;32mOK[0m] There are no anonymous accounts for any database users
[[0;32mOK[0m] All database users have passwords assigned
[[0;31m!![0m] There is no basic password file list!
-------- CVE Security Recommendations ---------------------------------------
[[0;34m--[0m] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[[0;34m--[0m] Up for: 18h 41m 49s (21M q [325.215 qps], 246K conn, TX: 58G, RX: 3G)
[[0;34m--[0m] Reads / Writes: 95% / 5%
[[0;34m--[0m] Binary logging is disabled
[[0;34m--[0m] Total buffers: 8.2G global + 4.8M per thread (275 max threads)
[[0;32mOK[0m] Maximum reached memory usage: 9.3G (59.59% of installed RAM)
[[0;32mOK[0m] Maximum possible memory usage: 9.4G (60.43% of installed RAM)
[[0;32mOK[0m] Slow queries: 0% (10K/21M)
[[0;31m!![0m] Highest connection usage: 89% (247/275)
[[0;32mOK[0m] Aborted connections: 0.00% (1/246569)
[[0;32mOK[0m] Query cache efficiency: 75.8% (15M cached / 20M selects)
[[0;31m!![0m] Query cache prunes per day: 4043524
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (14K temp sorts / 1M sorts)
[[0;31m!![0m] Joins performed without indexes: 1764
[[0;32mOK[0m] Temporary tables created on disk: 0% (1K on disk / 126K total)
[[0;32mOK[0m] Thread cache hit rate: 98% (4K created / 246K connections)
[[0;31m!![0m] Table cache hit rate: 17% (2K open / 13K opened)
[[0;32mOK[0m] Open file limit used: 0% (39/5K)
[[0;32mOK[0m] Table locks acquired immediately: 100% (318 immediate / 318 locks)
-------- MyISAM Metrics ------------------------------------------------------
[[0;31m!![0m] Key buffer used: 18.7% (196K used / 1M cache)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 1.0M/43.0K
[[0;32mOK[0m] Read Key buffer hit rate: 97.9% (430 cached / 9 reads)
-------- InnoDB Metrics ------------------------------------------------------
[[0;34m--[0m] InnoDB is enabled.
[[0;32mOK[0m] InnoDB buffer pool / data size: 8.0G/7.6G
[[0;32mOK[0m] InnoDB buffer pool instances: 8
[[0;31m!![0m] InnoDB Used buffer: 71.86% (376697 used/ 524224 total)
[[0;32mOK[0m] InnoDB Read buffer efficiency: 100.00% (118056700441 hits/ 118056942522 total)
[[0;31m!![0m] InnoDB Write Log efficiency: 73.83% (741389 hits/ 1004247 total)
[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 262858 writes)
-------- ThreadPool Metrics --------------------------------------------------
[[0;34m--[0m] ThreadPool stat is disabled.
-------- AriaDB Metrics ------------------------------------------------------
[[0;34m--[0m] AriaDB is disabled.
-------- TokuDB Metrics ------------------------------------------------------
[[0;34m--[0m] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[[0;34m--[0m] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[[0;34m--[0m] No replication slave(s) for this server.
[[0;34m--[0m] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce or eliminate persistent connections to reduce connection usage
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:
Beware that open_files_limit (5000) variable
should be greater than table_open_cache ( 2357)
Variables to adjust:
max_connections (> 275)
wait_timeout (< 120)
interactive_timeout (< 120)
query_cache_size (> 96M)
join_buffer_size (> 4.0M, or always use indexes with joins)
table_open_cache (> 2357)
A seguir está o status atual do servidor. Eu reformulei o servidor mysql cerca de 6 horas atrás, tendo alterado wait_timeout & Interactive_timeout para 90. No entanto, atualmente a situação parece normal devido ao baixo tempo de atividade no servidor.
Estou administrando um site de notícias baseado em Drupal 7, que tem em média um tráfego diário de 90.000 visitas de página
Uso atual de recursos no servidor
Em resposta a @Wilson Hauck, sim, o servidor usa SSD como disco rígido. Consulte o link pastbin a seguir para encontrar os detalhes de B) MOSTRAR STATUS GLOBAL; após no mínimo 24 horas UPTIME C) MOSTRAR VARIÁVEIS GLOBAIS; D) MOSTRAR PROCESSLIST COMPLETO;
https://pastebin.ubuntu.com/p/YhWP9szmjP/
$ ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 63934
max locked memory (kbytes, -l) 16384
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 63934
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
$ iostat -xm 5 3
Linux 5.3.0-1032-aws (ip-172-31-21-134) 08/25/20 _x86_64_ (4 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
32.44 0.64 1.79 0.54 0.10 64.49
Device r/s w/s rMB/s wMB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
loop0 0.03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.16 0.00 0.00 1.01 0.00 0.13 0.00
loop1 0.12 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.24 0.00 0.00 1.01 0.00 0.13 0.00
loop2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.82 0.00 0.00 8.64 0.00 1.00 0.00
loop3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.09 0.00 0.00 1.48 0.00 0.33 0.00
loop4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 1.00 0.00
xvda 50.74 48.87 1.77 1.00 7.64 17.73 13.09 26.62 1.08 3.41 0.10 35.70 20.89 0.86 8.57
qavg-cpu: %user %nice %system %iowait %steal %idle
82.65 0.00 2.06 0.05 0.00 15.23
Device r/s w/s rMB/s wMB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
loop0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
xvda 5.00 15.80 0.04 0.20 0.00 12.80 0.00 44.76 0.68 1.18 0.00 8.96 12.66 1.73 3.60
avg-cpu: %user %nice %system %iowait %steal %idle
92.67 0.00 2.46 0.10 0.05 4.72
Device r/s w/s rMB/s wMB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
loop0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
xvda 5.80 9.80 0.16 0.09 6.00 11.00 50.85 52.88 1.28 0.69 0.00 27.72 9.39 2.62 4.08
Uso de recursos do servidor mais recente medido usando a ferramenta Glances
Responder1
Taxa por segundo = RPS
Sugestões a serem consideradas para sua seção my.cnf [mysqld]
thread_cache_size=100 # from 12 to reduce overhead of threads_created 18,140 in 4 days
innodb_io_capacity=1900 # from 200 to allow higher IOPS on your SSD media
read_buffer_size=512K # from 128K to reduce handler_read_next RPS of 446,320
read_rnd_buffer_size=96K # from 256K to reduce handler_read_rnd_next RPS of 19,363
innodb_lru_scan_depth=100 from 256 to conserve 50% of CPU cycles used for function
Você descobrirá que essas alterações reduzem significativamente a ocupação da CPU.
Existem muitas outras oportunidades para melhorar sua configuração. Veja meu perfil, perfil de rede para informações de contato e scripts de utilitários para download gratuito para melhorar o desempenho.