MySQL's maximum memory usage is dangerously high and CPU run 100%
0
votes
2
answers
817
views
Hi we faced an issue on our server and it go verry slow for more tan 1 month and it go worst every day.
I am really not server administrator,
This is mysqltuner.pl result,
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 16.9G (Tables: 4633)
[--] Data in MyISAM tables: 13.3M (Tables: 150)
[--] Data in Aria tables: 4.1M (Tables: 1)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
[--] Up for: 14m 59s (9M q [10K qps], 7K conn, TX: 5G, RX: 2G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory : 125.8G
[--] Max MySQL memory : 367.5G
[--] Other process memory: 0B
[--] Total buffers: 52.4G global + 322.6M per thread (1000 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 75.8G (60.21% of installed RAM)
[!!] Maximum possible memory usage: 367.5G (292.12% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (22/9M)
[OK] Highest usage of available connections: 7% (74/1000)
[OK] Aborted connections: 0.00% (0/7652)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 48.7% (8M cached / 16M selects)
[!!] Query cache prunes per day: 27571979
[OK] Sorts requiring temporary tables: 0% (73 temp sorts / 135K sorts)
[!!] Joins performed without indexes: 5959
[OK] Temporary tables created on disk: 16% (27K on disk / 164K total)
[OK] Thread cache hit rate: 99% (74 created / 7K connections)
[OK] Table cache hit rate: 99% (1M hits / 1M requests)
[OK] table_definition_cache (7000) is greater than number of tables (4945)
[OK] Open file limit used: 1% (364/32K)
[OK] Table locks acquired immediately: 100% (884 immediate / 884 locks)
[OK] Binlog cache memory access: 99.45% (5746 Memory / 5778 Total)-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (23.4M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/5.9M
[!!] Read Key buffer hit rate: 91.5% (317 cached / 27 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 50.0G / 16.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 7.0G * 2/50.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 50
[--] Number of InnoDB Buffer Pool Chunk: 400 for 50 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.87% (693596513 hits / 694467712 total)
[OK] InnoDB Write Log efficiency: 99.80% (6253500 hits / 6265916 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12416 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/40.0K
[OK] Aria pagecache hit rate: 95.4% (151K cached / 6K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] This server is acting as master for 4 server(s).
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] No replication setup for this server or replication not started.
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 64M)
join_buffer_size (> 256.0M, or always use indexes with JOINs)
performance_schema=ON
key_buffer_size (~ 24M)#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve
sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
#
# * Fine Tuning
#
open_files_limit = 20240
sort_buffer_size = 2097144
table_cache = 7000
table_open_cache = 7000
table_definition_cache = 7000
tmp_table_size = 128M
max_heap_table_size = 128M
max_connections = 1000
connect_timeout = 10
wait_timeout = 120
interactive_timeout = 1800
max_allowed_packet = 64M
bulk_insert_buffer_size = 16M
thread_stack = 256K
thread_cache_size = 128
thread_pool_size = 24
log_warnings = 1
# MyISAM
key_buffer_size = 128M
join_buffer_size = 256M
myisam_recover_options = BACKUP
myisam_repair_threads = 1
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 131072
read_rnd_buffer_size = 262144
#
# * Query Cache Configuration
#
query_cache_type = 1
query_cache_limit = 32M
query_cache_size = 64M
#
# * 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 = 0
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
slow_query_log = on
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 4
#log_slow_rate_limit = 1000
#log_slow_verbosity = query_plan
#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
auto_increment_increment = 1
auto_increment_offset = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 2
max_binlog_size = 100M
binlog_format = ROW
sync_binlog = 0
#binlog_do_db = include_database_name
#binlog_ignore_db = exclude_database_name
#
# * Security Features
#
# Read the manual, too, if you want chroot!
#chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
#ssl-cipher = TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
#ssl = on
#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
#
# * 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!
## InnoDB tuning
innodb_file_per_table = on
innodb_buffer_pool_size = 50G
innodb_buffer_pool_instances = 50
innodb_log_file_size = 7G
innodb_log_buffer_size = 2G
innodb_thread_concurrency = 16
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_open_files = 1000
innodb_io_capacity = 800
innodb_io_capacity_max = 2000
innodb_lock_wait_timeout = 60
innodb_flush_method = O_DIRECT
innodb_doublewrite = true
innodb_use_native_aio = 1
innodb_flush_log_at_trx_commit = 2
default_storage_engine = InnoDB
#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
# this is only for embedded server
[embedded]
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.3]PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4162 mysql 20 0 64,4g 17,4g 10608 S 102,6 13,9 941:18.01 mysqld
9985 www-fou+ 20 0 395960 221516 126856 R 100,0 0,2 14:55.02 php-fpm7.1
1593 www-fou+ 20 0 381424 203876 123500 R 99,7 0,2 11:24.43 php-fpm7.1
19099 www-fou+ 20 0 381788 204924 124484 S 93,4 0,2 10:35.16 php-fpm7.1
24495 www-fou+ 20 0 383684 212224 129636 R 69,1 0,2 11:37.65 php-fpm7.1
15019 www-fou+ 20 0 339196 127520 89412 S 66,4 0,1 2:51.54 php-fpm7.1
11226 www-fou+ 20 0 361036 181036 121012 S 65,8 0,1 8:38.26 php-fpm7.1
13478 www-fou+ 20 0 333628 157600 125252 S 12,2 0,1 12:15.50 php-fpm7.1
26195 www-fou+ 20 0 343160 165036 123072 S 6,2 0,1 11:20.56 php-fpm7.1
22301 www-fou+ 20 0 340908 148244 108692 S 3,0 0,1 12:58.10 php-fpm7.1
21292 www-data 20 0 2316000 23908 4088 S 1,6 0,0 0:30.03 apache2
29112 www-fou+ 20 0 330464 156296 126860 S 1,6 0,1 10:24.75 php-fpm7.1
3229 root 20 0 12680 5084 2944 R 1,3 0,0 0:02.16 top
18330 www-data 20 0 2249248 28736 3908 S 1,3 0,0 4:00.44 apache2
31957 www-fou+ 20 0 326216 150860 125900 S 1,3 0,1 12:48.35 php-fpm7.1
892 www-fou+ 20 0 1044388 195248 15528 S 1,0 0,1 766:53.93 node
14357 www-fou+ 20 0 331084 121516 91724 S 1,0 0,1 2:17.92 php-fpm7.1
Asked by Mathieu
(9 rep)
Mar 29, 2023, 02:23 PM
Last activity: Aug 16, 2023, 12:07 PM
Last activity: Aug 16, 2023, 12:07 PM