Database Administrators Asked by enyceexdanny on December 22, 2021
UPDATE 6/23/2020:
Haven’t noticed any odd CPU spikes lately, but noticed that the MySQL memory footprint grows indefinitely until it goes OOM and is killed by the kernel. Where should I start looking first?
I’m looking for some MySQL tuning help. It seems that every so often mysqld process’ CPU consumption shoots to way over 100%, and I’m thinking it’s likely due to poor configuration.
This server is used to host about 50 accounts along with emails, mostly simple wordpress/joomla installs, along with 5 Magento 1.9 installations.
On a linode dedicated server:
16x AMD EPYC 7501 Cores
32 GB RAM
640 GB SSD Space
7 TB Bandwidth
PROCESSLIST: https://pastebin.com/8y12Kbj5
GLOBAL STATUS: https://pastebin.com/LgY6RMT3
GLOBAL VARIABLES: https://pastebin.com/VafyvKaQ
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) 128365
max locked memory (kbytes, -l) 64
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) 128365
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
iostat -xm 5 3
avg-cpu: %user %nice %system %iowait %steal %idle
0.92 0.13 0.23 0.02 0.01 98.69
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
loop0 0.00 0.00 0.00 0.59 0.00 0.00 13.75 0.00 0.28 1.15 0.27 0.49 0.03
sda 0.61 18.91 12.09 13.38 0.56 0.69 100.41 0.00 0.56 0.29 0.81 0.48 1.22
sdb 0.00 0.03 0.00 0.00 0.00 0.00 70.58 0.00 2.53 0.39 3.41 3.46 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
7.01 0.00 0.89 0.01 0.01 92.07
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
loop0 0.00 0.00 0.00 1.80 0.00 0.01 8.00 0.00 0.22 0.00 0.22 0.78 0.14
sda 0.00 31.60 0.00 24.60 0.00 0.39 32.33 0.00 0.66 0.00 0.66 0.46 1.12
sdb 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
avg-cpu: %user %nice %system %iowait %steal %idle
0.51 0.00 0.13 0.01 0.00 99.35
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
loop0 0.00 0.00 0.00 1.80 0.00 0.01 8.00 0.00 0.33 0.00 0.33 0.33 0.06
sda 0.00 11.20 0.00 29.80 0.00 0.30 20.83 0.00 0.92 0.00 0.92 0.20 0.60
sdb 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
Server version: 5.7.29-log MySQL Community Server (GPL)
My.cnf:
[mysqld]
# Required Settings
basedir = /usr/
bind_address = *
datadir = /var/lib/mysql/
max_allowed_packet = 256M
max_connect_errors = 1000000
port = 3306
skip_external_locking
socket = /var/lib/mysql/mysql.sock
tmpdir = /tmp
user = mysql
performance_schema = 1 # FROM 0 to enable better diagnostics
sql_mode = ""
# InnoDB Settings
default_storage_engine = InnoDB
innodb_buffer_pool_instances = 18 # FROM 1
# Update from 1G > 12G
innodb_buffer_pool_size = 18G
# ADDED 3/23/2020 IGNT
innodb_log_file_size = 2G
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 2
innodb_stats_on_metadata = 0
#innodb_thread_concurrency = 15
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# MyISAM Settings
query_cache_limit = 0 # from 4M
query_cache_size = 0 # from 128M
query_cache_type = 0 # from 1
low_priority_updates = 1
concurrent_insert = 2
# Connection Settings
max_connections = 150
# Buffer Settings
# IGNT DISABLED ALL TO CHECK
join_buffer_size = 128M
#read_buffer_size = 32M
#read_rnd_buffer_size = 64M
#sort_buffer_size = 64M
open_files_limit = 20000 # should be greater than table_open_cache
# Search Settings
ft_min_word_len = 4
# Logging
log_error = /var/lib/mysql/mysql_error.log
long_query_time = 5
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql_slow.log
# Custom ADDS IGNT
#skip-name-resolve
innodb_flush_log_at_trx_commit = 2
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
key_buffer_size = 256M
table_open_cache = 10000
table_definition_cache = 10000
tmp_table_size = 256M
max_heap_table_size = 256M
innodb_buffer_pool_instances = 12
MySQLtuner.pl results: (UPDATED 3/31/2020 8:48PM EST)
>> MySQLTuner 1.7.19 - 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] Currently running supported MySQL version 5.7.29-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/mysql_error.log exists
[--] Log file: /var/lib/mysql/mysql_error.log(692K)
[OK] Log file /var/lib/mysql/mysql_error.log is readable.
[OK] Log file /var/lib/mysql/mysql_error.log is not empty
[OK] Log file /var/lib/mysql/mysql_error.log is smaller than 32 Mb
[!!] /var/lib/mysql/mysql_error.log contains 2700 warning(s).
[!!] /var/lib/mysql/mysql_error.log contains 344 error(s).
[--] 32 start(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2020-03-24T09:58:01.690843Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-03-24T04:50:09.880286Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-03-24T04:48:14.253324Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-03-24T04:33:47.976873Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-03-24T04:20:51.868881Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-03-24T04:20:46.399280Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-03-23T12:05:22.733706Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2020-03-23T05:52:41.442704Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2020-03-23T05:48:46.305524Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2020-03-23T05:47:42.353869Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 47 shutdown(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2020-03-24T09:57:57.724742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-03-24T04:50:06.601039Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-03-24T04:48:04.688209Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-03-24T04:33:44.609318Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-03-24T04:20:48.549320Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-03-24T04:20:42.966162Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2020-03-23T12:05:19.945269Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2020-03-23T05:52:38.732313Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2020-03-23T05:48:43.664346Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2020-03-23T05:47:39.754910Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 169.9M (Tables: 811)
[--] Data in InnoDB tables: 1.6G (Tables: 5313)
[--] Data in MEMORY tables: 0B (Tables: 169)
[OK] Total fragmented tables: 0
-------- 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 is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 7d 14h 46m 59s (35M q [53.712 qps], 464K conn, TX: 62G, RX: 9G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory : 31.3G
[--] Max MySQL memory : 74.9G
[--] Other process memory: 0B
[--] Total buffers: 18.5G global + 384.9M per thread (150 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 67.4G (214.94% of installed RAM)
[!!] Maximum possible memory usage: 74.9G (238.92% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (3K/35M)
[!!] Highest connection usage: 86% (130/150)
[OK] Aborted connections: 0.03% (134/464170)
[!!] 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.
[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 70704
[!!] Temporary tables created on disk: 56% (3M on disk / 6M total)
[OK] Thread cache hit rate: 99% (658 created / 464K connections)
[!!] Table cache hit rate: 0% (7K open / 1M opened)
[OK] table_definition_cache(10000) is upper than number of tables(6572)
[OK] Open file limit used: 7% (1K/15K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 23.3% (62M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/27.0M
[OK] Read Key buffer hit rate: 100.0% (90M cached / 36K reads)
[!!] Write Key buffer hit rate: 73.2% (447K cached / 327K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 18.0G/1.6G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/18.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 12
[--] Number of InnoDB Buffer Pool Chunk : 144 for 12 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% (11369995313 hits/ 11370050077 total)
[!!] InnoDB Write Log efficiency: 68.92% (1625414 hits/ 2358577 total)
[OK] InnoDB log waits: 0.00% (0 waits / 733163 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- 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 warning line(s) into /var/lib/mysql/mysql_error.log file
Control error line(s) into /var/lib/mysql/mysql_error.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate persistent connections to reduce connection usage
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).
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
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 (15000) variable
should be greater than table_open_cache (7420)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
join_buffer_size (> 128.0M, or always use indexes with JOINs)
table_open_cache (> 7420)
innodb_buffer_pool_instances(=18)
Observations:
The More Important Issues:
Consider moving the rest of your MyISAM tables to InnoDB. Conversion from MyISAM to InnoDB
Contradiction: ulimit -n says 1024, but table_open_cache = 7420 -- Please double check both. 1024 is unreasonably low. The number of misses and overflows says that even 7420 is too low. Or perhaps there are too many tables in the schema design.
WordPress's meta tables have inefficient indexes. Help: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
innodb_buffer_pool_size seems to be much bigger than the dataset. This is not a performance problem, but it does waste RAM.
innodb_flush_neighbors = 0 since you have SSD.
There are some indications of poorly performing queries. See http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog fo setting up the slowlog to identify them and get started into fixing them. (High CPU comes from indexing and queries more than from tuning variables.)
There is a lot of "savepoint" usage. I wonder if the logic could be simplified.
Details and other observations:
( table_open_cache ) = 7,420 -- Number of table descriptors to cache
-- Several hundred is usually good.
( Table_open_cache_overflows ) = 443,831 / 253512 = 1.8 /sec
-- May need to increase table_open_cache (now 7420)
( Table_open_cache_misses ) = 455,487 / 253512 = 1.8 /sec
-- May need to increase table_open_cache (now 7420)
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 455,487 / (14634100 + 455487) = 3.0% -- Effectiveness of table_open_cache.
-- Increase table_open_cache (now 7420) and check table_open_cache_instances (now 16).
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second.
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 12 = 0.333 -- innodb_page_cleaners
-- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 12)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 1,102,876 * 16384 / 18432M = 93.5% -- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 1,102,876 / 1179504 = 93.5% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size (now 19327352832) is bigger than necessary?
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 1,215,627,264 / 18432M = 6.3% -- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 253,512 / 60 * 2048M / 580173312 = 15,639 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 2147483648). (Cannot change in AWS.)
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( join_buffer_size * Max_used_connections ) = (128M * 128) / 32768M = 50.0% -- (A metric for pondering the size of join_buffer_size.)
-- join_buffer_size (now 134217728) should probably be shrunk to avoid running out of RAM.
( character_set_server ) = character_set_server = latin1
-- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.
( net_buffer_length / max_allowed_packet ) = 16,384 / 256M = 0.01%
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Key_blocks_used * 1024 / key_buffer_size ) = 1,798 * 1024 / 256M = 0.69% -- Percent of key_buffer used . High-water-mark.
-- Lower key_buffer_size (now 268435456) to avoid unnecessary memory usage.
( Key_writes / Key_write_requests ) = 45,424 / 76731 = 59.2% -- key_buffer effectiveness for writes
-- If you have enough RAM, it would be worthwhile to increase key_buffer_size (now 268435456).
( Created_tmp_disk_tables ) = 789,774 / 253512 = 3.1 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size (now 268435456) and max_heap_table_size (now 268435456).
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.
( Created_tmp_disk_tables / Questions ) = 789,774 / 9007651 = 8.8% -- Pct of queries that needed on-disk tmp table.
-- Better indexes / No blobs / etc.
( Created_tmp_disk_tables / Created_tmp_tables ) = 789,774 / 1422159 = 55.5% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size (now 268435456) and max_heap_table_size (now 268435456); improve indexes; avoid blobs, etc.
( tmp_table_size ) = 256M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size (now 268435456) to avoid running out of RAM. Perhaps no more than 64M.
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (121849 + 94788 + 36542 + 0) / 97528 = 2.6 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.
( Select_scan ) = 1,428,764 / 253512 = 5.6 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 1,428,764 / 7922384 = 18.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries
( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin (now OFF) = OFF)
( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".
-- Galera desires 2; 2 requires BINLOG_FORMAT=ROW or MIXED
( long_query_time ) = 5 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show.
-- It can be helpful in the slowlog to see writes that could be interfering with Slave reads.
( back_log ) = 80 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections (now 150)) may help when doing lots of connections.
( max_connect_errors ) = 1,000,000 = 1.0e+6 -- A small protection against hackers.
-- Perhaps no more than 200.
Abnormally small:
innodb_lru_scan_depth / innodb_io_capacity = 0.256
Abnormally large:
Com_alter_user = 0.071 /HR
Com_check = 1.3 /HR
Com_drop_user = 0.014 /HR
Com_release_savepoint = 2.1 /HR
Com_rollback_to_savepoint = 0.045 /sec
Com_savepoint = 2.1 /HR
Com_show_create_db = 1.1 /HR
Com_show_create_func = 0.028 /HR
Com_show_create_user = 12 /HR
Com_show_events = 1.1 /HR
Com_show_grants = 12 /HR
Com_show_slave_hosts = 0.057 /HR
Com_stmt_send_long_data = 0.53 /HR
Handler_savepoint = 2.1 /HR
Handler_savepoint_rollback = 0.045 /sec
Innodb_buffer_pool_pages_free = 1.1e+6
Innodb_num_open_files = 5,206
Open_files = 1,834
Open_table_definitions = 6,511
Open_tables = 7,408
Select_full_range_join = 0.33 /sec
Select_full_range_join / Com_select = 1.1%
innodb_io_capacity_max = 8,000
optimizer_trace_offset = --1
table_definition_cache = 10,000
table_open_cache / max_connections = 49.5
Abnormal strings:
innodb_fast_shutdown = 1
low_priority_updates = ON
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
Answered by Rick James on December 22, 2021
To make the ulimit -n 128000 persistent across OS shutdown / restart or powerfailure, this URL is an overview. Your requirements may be different, depending on OS specifics.
Do not set your limit to 500,000 as listed in this URL, please. You should be good at 128000 for now.
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
The 28,000 cushion is for apps other than MySQL to have file handles available.
Rate Per Second = RPS
Suggestions for your my.cnf [mysqld] section
open_files_limit=100000 # from whatever you have today - I see varying #'s
innodb_open_files=64000 # from 7420 reported in SHOW GLOBAL STATUS;
table_open_cache=64000 # from 7420 reported in SHOW GLOBAL STATUS;
max_connect_errors=10 # from 1000000 - no point in allowing hacker/cracker abuse
thread_cache_size=100 # from 9 to reduce threads_created count
read_rnd_buffer_size=128K # from 256K to reduce handler_read_rnd_next RPS of 109,046
These changes will minimize workload of opening ~ 6000 tables per hour (current RPhr)
There are many more Global Variable opportunities to improve your instance performance.
Visit our website, Utility Scripts to download free Utility Scripts to assist with performance tuning. You will need findfragtables.sql and find-redundant-indexes.sql very helpful in reducing CPU cycle use and storage required for data tables and indexes.
Answered by Wilson Hauck on December 22, 2021
Your biggest problem currently is the table_open_cache size. This can be change dynamicly SET GLOBAL table_open_cache = 10000 (account for your open_file_limit of 15/16k as each open item needs a file descriptor).
I can't see what version you are using however if mariadb, look at its documentation otherwise mysql documentation (adjust URL to major version 5.7/8.0).
Innodb buffer pool/innodb_buffer_pool_instances isn't helping the pool is significantly larger than your datasize. (could be reduced down to ~4G - no harm being big, just a bit wasteful).
Look at setting long_query_time to 1 second or less and enable the slow query log. The mysql-tuner results on tmp table usage and joins not using index show there are a number of queries performing badly due to poor indexing. Once you have some common ones of these identified ask new questions about them, showing EXPLAIN {query}, the query and SHOW CREATE TABLE {tablename} for the tables involved. https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html will help prioritize the slow query log results.
Please state your MySQL version and OS if you need further clarification.
Answered by danblack on December 22, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP