Question 22040 in Mysql Question posted in
The official MySQL documentation can be found here.

I need assistance tuning the MySQL performances – MySQL's maximum memory usage is dangerously high and Temporary tables created on disk

can someone help me tune the MySQL performances for my WordPress site? I just want to make sure everything is optimized to the maximum. Thanks!

Server details:

CentOS v7.9

Apache / PHP (It’s a WordPress site)

vCPU/s:1 vCPU

RAM:2048.00 MB

Storage: 64 GB NVMe

Database size: Around 60MB

MySQL Tuner:

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log (5M)
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] Log file /var/log/mysqld.log is readable.
[!!] /var/log/mysqld.log contains 4563 warning(s).
[!!] /var/log/mysqld.log contains 1322 error(s).
[--] 135 start(s) detected in /var/log/mysqld.log
[--] 1) 2022-11-28  8:37:10 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2022-11-28  8:37:09 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2022-11-27  8:55:24 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2022-11-27  8:50:27 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2022-11-27  8:46:18 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2022-11-27  8:34:09 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2022-11-27  8:34:07 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2022-11-27  8:34:02 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2022-11-27  8:33:14 140012789491904 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2022-11-27  8:33:12 140338477058240 [Note] /usr/sbin/mysqld: ready for connections.
[--] 60 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2022-11-28  8:37:10 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2022-11-27  8:50:26 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2022-11-27  8:46:18 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2022-11-27  8:34:08 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2022-11-27  8:34:06 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2022-11-27  8:33:34 140012513359616 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2022-11-27  8:33:14 140338206811904 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2022-11-27  8:33:12 140442772428544 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2022-11-27T08:32:27.508361Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2022-11-26T18:54:46.107607Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 169.2M (Tables: 137)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- 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: 25m 30s (14K q [9.314 qps], 271 conn, TX: 138M, RX: 1M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 1.8G
[--] Max MySQL memory    : 13.8G
[--] Other process memory: 0B
[--] Total buffers: 938.0M global + 264.7M per thread (50 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 9.7G (540.65% of installed RAM)
[!!] Maximum possible memory usage: 13.8G (771.06% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 3% (482/14K)
[OK] Highest usage of available connections: 68% (34/50)
[OK] Aborted connections: 0.74% (2/271)
[!!] CPanel and Flex system skip-name-resolve should be on
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Joins performed without indexes: 11
[!!] Temporary tables created on disk: 70% (1K on disk / 1K total)
[OK] Thread cache hit rate: 78% (58 created / 271 connections)
[!!] Table cache hit rate: 5% (17K hits / 326K requests)
[OK] table_definition_cache (400) is greater than number of tables (304)
[OK] Open file limit used: 0% (311/40K)
[OK] Table locks acquired immediately: 100% (15K immediate / 15K locks)

-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (46.7M used / 256.0M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/10.2M
[OK] Read Key buffer hit rate: 98.6% (195K cached / 2K reads)
[!!] Write Key buffer hit rate: 91.7% (484 cached / 444 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] No tables are Innodb
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 512.0M / 0B
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 64.0M * 2/512.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk: 4 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 82.98% (2554 hits / 3078 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits / 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[!!] Aria pagecache hit rate: 90.8% (10K cached / 1K reads)

-------- 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Check warning line(s) in /var/log/mysqld.log file
    Check error line(s) in /var/log/mysqld.log file
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    name resolution is enabled due to cPanel doesn't support this disabled.
    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
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64:
    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.
    For more details see: 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 (40000) variable
    should be greater than table_open_cache (1024)
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    skip-name-resolve=0
    join_buffer_size (> 4.0M, or always use indexes with JOINs)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_open_cache (> 1024)
    performance_schema=ON
    key_buffer_size (~ 48M)

My.CNF


[mysqld]
performance-schema=0

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
# pid-file=/var/run/mysqld/mysqld.pid

innodb_buffer_pool_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit  = 1
innodb_log_file_size = 64M
# innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_instances = 1
innodb_log_buffer_size = 10M

max_connections = 50
wait_timeout = 3600
interactive_timeout = 3600

join_buffer_size = 4M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M
key_buffer_size = 256M
max_allowed_packet=268435456
read_rnd_buffer_size = 256K

thread_cache_size = 8
open_files_limit=40000
table_open_cache = 1024
default-storage-engine = MyISAM
innodb_file_per_table = 1

query_cache_type = 0
query_cache_limit = 0
query_cache_size = 0

max_heap_table_size = 32M
tmp_table_size = 32M

Specifically, how do I reduce the "MySQL’s maximum memory usage" and "Temporary tables created on disk", because I feel those are terribly wrong?

Tags:

3

Answers


  1. 0

    Some questions:

    • Is there a specific reason for running WordPress on MyISAM as opposed to InnoDB?
    • Do you know how much memory is being used by Apache and PHP during peak load?
    • What version of MySQL are you running?
    • What versions are Apache, PHP and WordPress?
    • What is the default temp storage engine (SHOW VARIABLES LIKE 'default_tmp_stor%';)?

    Your mysqltuner output suggests your current db size is –

    [--] Data in MyISAM tables: 169.2M (Tables: 137)

    and current key buffer stats are –

    [!!] Key buffer used: 18.2% (46.7M used / 256.0M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/10.2M

    so it should be safe to reduce the key_buffer_size to 64M (from 256M).

    Your max_allowed_packet=268435456 (256M) seems unnecessarily high but is unlikely to cause issues. This accounts for the majority (97%) of mysqltuner’s per thread usage calculation. Note that this is a maximum value and not the actual size of the buffer allocated by default (net_buffer_length default 16,384 – 16K). – read more

    [!!] Maximum reached memory usage: 9.7G (540.65% of installed RAM)

    Is rather alarmist and incorrect. It is just the global buffer + (per thread * max used connections) –

    [--] Total buffers: 938.0M global + 264.7M per thread (50 max threads)
[OK] Highest usage of available connections: 68% (34/50)

so 938.0M + (264.7M * 34) = 9,937.8M = 9.7G

    Doing the same calculation based on default value for max_allowed_packet (4M) gives a very different result –

    so 938.0M + (12.7M * 34) = 1,369.8M = 1.3G

    You currently have 512MB allocated to InnoDB’s buffer pool, which is not being used. Turning off InnoDB has been deprecated, so instead reduce the size of the buffer pool –

    innodb_buffer_pool_size = 8M
# innodb_log_files_in_group = 2
# innodb_flush_log_at_trx_commit  = 1
# innodb_log_file_size = 64M
# innodb_additional_mem_pool_size = 32M
# innodb_buffer_pool_instances = 1
# innodb_log_buffer_size = 10M

    [!!] Temporary tables created on disk: 70% (1K on disk / 1K total)

    The numbers are so low, due to the recent restart (25m 30s), that it is impossible to read anything meaningful into them. There are certain query conditions that will always require a temporary disk table. As WP uses TEXT columns for posts, this is probably why the number of temp disk tables is high.

    Login or Signup to reply.
  2. 0

    Unpanic. "MySQL’s maximum memory usage" is not computable and not meaningful.

    Do you have any "swap" space allocated?

    Do plan on moving from MyISAM to InnoDB.

    key_buffer_size = 100M
max_allowed_packet = 20M
max_connections = 40

    Set Apache’s limit on children to no more than MySQL’s max_connections.

    After you change over:

    innodb_buffer_pool_size = 200M

    Also, after changing to InnoDB, install this plugin: WP Index Improvements

    And see nnichols’ answer.

    Login or Signup to reply.
  3. 0

    Suggestion to consider for improving your MyISAM tables performance in [mysqld] section:

    key_cache_division_limit=50  # from 100 (percent) for Hot/Warm caching
key_cache_block_size=16384  # from 1024 to reduce number of blocks to be managed

    There are many other opportunities to improve performance. Please view profile for contact info.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search