We're using MySQL 5.7 and have a bigger setup, where each of our customers has their own database with tables. All databases with the same setup of tables, it's just to contain them for themselves.
Our server currently has 32gb memory and we're running into the issue where MySQL takes all the memory over the span of 3 to 7 days depending on the activity.
We do know that our system do need optimizations as well and we're working on that, but it's a slow process since we can't figure out which queries are the worst.
Have attached some different informations and a picture of todays sudden memory increase
MySQLTuner
>> MySQLTuner 1.8.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.7.36-0ubuntu0.18.04.1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: ARCHIVE BLACKHOLE CSV -FEDERATED InnoDB MEMORY MRG_MYISAM MyISAM PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 32.4G (Tables: 45628)
[--] Data in MyISAM tables: 37.8M (Tables: 22)
[!!] Total fragmented tables: 7
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5d 13h 57m 55s (60M q [125.132 qps], 3M conn, TX: 264G, RX: 24G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 31.4G
[--] Max MySQL memory : 22.6G
[--] Other process memory: 0B
[--] Total buffers: 20.1G global 17.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 20.8G (66.35% of installed RAM)
[OK] Maximum possible memory usage: 22.6G (72.02% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (400/60M)
[OK] Highest usage of available connections: 29% (44/151)
[OK] Aborted connections: 0.00% (1/3159508)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[!!] Sorts requiring temporary tables: 38% (2M temp sorts / 7M sorts)
[!!] Joins performed without indexes: 27618
[!!] Temporary tables created on disk: 52% (1M on disk / 3M total)
[OK] Thread cache hit rate: 99% (12K created / 3M connections)
[OK] Table cache hit rate: 28% (38M hits / 137M requests)
[!!] table_definition_cache(36000) is lower than number of tables(45930)
[OK] Open file limit used: 0% (10/5K)
[OK] Table locks acquired immediately: 100% (618K immediate / 618K locks)
[OK] Binlog cache memory access: 99.45% (1719946 Memory / 1729414 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/10.0M
[!!] Read Key buffer hit rate: 85.1% (877K cached / 130K reads)
[OK] Write Key buffer hit rate: 100.0% (927 cached / 927 writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 20.0G/32.4G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/20.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 20
[--] Number of InnoDB Buffer Pool Chunk : 160 for 20 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: 100.00% (25437207254 hits/ 25437676712 total)
[!!] InnoDB Write Log efficiency: 56.94% (2658589 hits/ 4669027 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2010438 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- 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 1 server(s).
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] No replication setup for this server or replication not started.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control error line(s) into /var/log/mysql/error.log file
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `data_al6`.`files`; -- can free 141 MB
OPTIMIZE TABLE `data_bo3`.`files`; -- can free 102 MB
OPTIMIZE TABLE `data_ha3`.`files`; -- can free 19 MB
OPTIMIZE TABLE `data_ju1`.`files`; -- can free 88 MB
OPTIMIZE TABLE `data_kc1`.`files`; -- can free 19 MB
OPTIMIZE TABLE `data_re41`.`files`; -- can free 33 MB
OPTIMIZE TABLE `data_so6`.`files`; -- can free 39 MB
Total freed space after theses OPTIMIZE TABLE : 441 Mb
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
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).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
sort_buffer_size (> 256K)
read_rnd_buffer_size (> 256K)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(36000) > 45930 or -1 (autosizing if supported)
innodb_buffer_pool_size (>= 32.4G) if possible.
my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[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
innodb_buffer_pool_size=20G
table_definition_cache=36000
innodb_buffer_pool_instances=20
skip-external-locking
#
# * Default charset
#
character-set-server = utf8
#
# * Fine Tuning
#
# Obsolete key_buffer option renamed to key_buffer_size by maintainer script
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam_recover_options = BACKUP
#max_connections = 100
table_open_cache = 128
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 64M
#
# * Logging and Replication
#
log_error = /var/log/mysql/error.log
#
#################################################################
# Slow query log:
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
slow_query_log = 1
#################################################################
#
# 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
binlog-format = mixed
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_file_per_table
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 2
max_binlog_size = 512M
log_bin_trust_function_creators = 1
# innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size used
# min. innodb_log_file_size = (10 * MEDIUM_BLOB) / innodb_log_files_in_group = (10 * 16 MB) / 2 = 80 MB;
innodb_log_file_size = 2G
#
# * InnoDB
#
# Make sure strict mode is disabled:
sql_mode =IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# Make sure timestamp defaults are explicitly declared:
explicit_defaults_for_timestamp
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
# Obsolete key_buffer option renamed to key_buffer_size by maintainer script
key_buffer_size = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
CodePudding user response：
Just ONE primary cause of OOM.
com_savepoint counted 3,882 during your 1.1 days of SHOW GLOBAL STATUS
com_release_savepoint counted 0 releases.
Net result is each failure to release savepoint when it is no longer needed equals resources not released with eventual OOM. Find mysql savepoint tutorial and look at closely, please.
There are many more configuration needs. In another answer by your Monday AM, I will give you aditional suggestions to settle down your table open thrashing that is killing performance. View profile for contact info, please.
CodePudding user response：
Rate Per Second = RPS
Suggestions to consider to reduce table_open thrashing ONLY situation.
OS per your ulimit -a report has Open Files limit of 1024 From your OS Command prompt, ulimit -n 500000 and press Enter will enable dynamic limit of Open Files to support MySQL and other apps when needed. In MySQL alone, your system added ~ 1,500 tables in less than a week to new MySQL table/file count of 47,148 today.
To make this persistent across OS stop/start, follow this URL for a guide, please,
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
your specifics may be slightly different. Do apply 500,000 as this guide was
trying to accomplish. Try to get this persistent in a controlled environment in the next few days. If not, just use ulimit -n 500000 to dynamically enable the higher limit before starting MySQL after uncontrolled OOM crash. Use ulimit -a anytime when in doubt to determine current Open Files limit.
Suggestions to consider for your my.cnf
table_open_cache=120000 # from 128 to reduce opened_tables RPS of 218
innodb_open_files=120000 # from 300 - this should always be = table_open_cache
table_definition_cache=75000 # from 36000 for more than table_count
to reduce opened_table_definitions RPS of 148
open_files_limit=256000 # from 5000 - to reduce opened_files RPS of 226
These changes include Static Global Variables and will require MySQL start/stop.
View profile for contact info, if needed. Many more performance tuning opportunities exist for your application. We would like to assist.