0

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/27/2020 4:17AM EST)


-------- Storage Engine Statistics -----------------------------------------------------------------
 >>  MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
 >>  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(686K)
[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 2691 warning(s).
[!!] /var/lib/mysql/mysql_error.log contains 284 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: 144.5M (Tables: 811)
[--] Data in InnoDB tables: 1.7G (Tables: 5313)
[--] Data in MEMORY tables: 1.3M (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: 2d 22h 16m 10s (8M q [35.477 qps], 152K conn, TX: 17G, RX: 2G)
[--] 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: 66.6G (212.54% 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% (1K/8M)
[OK] Highest usage of available connections: 85% (128/150)
[OK] Aborted connections: 0.04%  (63/152282)
[!!] 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% (898 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 27512
[!!] Temporary tables created on disk: 55% (787K on disk / 1M total)
[OK] Thread cache hit rate: 99% (260 created / 152K connections)
[!!] Table cache hit rate: 1% (7K open / 421K opened)
[OK] table_definition_cache(10000) is upper than number of tables(6572)
[OK] Open file limit used: 8% (1K/15K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (50M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/27.0M
[OK] Read Key buffer hit rate: 100.0% (27M cached / 9K reads)
[!!] Write Key buffer hit rate: 59.1% (76K cached / 45K 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.7G
[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% (3964205695 hits/ 3964255960 total)
[!!] InnoDB Write Log efficiency: 72.98% (558959 hits/ 765912 total)
[OK] InnoDB log waits: 0.00% (0 waits / 206953 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.
    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 ***
    join_buffer_size (> 128.0M, or always use indexes with JOINs)
    table_open_cache (> 7420)
    innodb_buffer_pool_instances(=18)
0

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.

  • 1
    When you say "This can be change dynamically", can you elaborate? What value should I be setting to? Sorry, I'm not too familiar with MySQL settings. And I'm using Server version: 5.7.29-log MySQL Community Server (GPL). – enyceexdanny 2 days ago
  • Welcome back to dba.SE! Additional information request. Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report (and we will know the version running) AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck 2 days ago
  • Please view my profile, Network profile for contact info where we have free downloadable Utility Scripts to assist with performance tuning. A Skype session would not be harmful when you have time. – Wilson Hauck 2 days ago
  • dynamic change of settings can occur without a server restart. Don't forget to put any change the run in SQL, like 'SET GLOBAL ....", into your configuration file so it will be there on next restart (assuming it had a positive gain). – danblack 2 days ago
  • 1
    @WilsonHauck Hey. Thanks for helping me out again. Moved servers, and trying to optimize it the best I can. I've updated the original post with the updates. Let me know if you need anything else. Thanks! – enyceexdanny yesterday
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.