Hi all
hoping someone can help we have a huge server for just a couple of sites but are not getting the performance we would expect (the site is a big one)
we've run mySQL tuner and would love some input.
The server we are on has 256 GB ram and SSD drives 10 core / 20 thread CPU Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz
Also running litespeed and LSCACHE
hoping someone can help we have a huge server for just a couple of sites but are not getting the performance we would expect (the site is a big one)
we've run mySQL tuner and would love some input.
The server we are on has 256 GB ram and SSD drives 10 core / 20 thread CPU Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz
Also running litespeed and LSCACHE
Bash:
>> 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 10.3.23-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/magi.xxxxxxxx.com.err exists
[--] Log file: /var/lib/mysql/magi.xxxxxxxx.com.err(1M)
[OK] Log file /var/lib/mysql/magi.xxxxxxxx.com.err is readable.
[OK] Log file /var/lib/mysql/magi.xxxxxxxx.com.err is not empty
[OK] Log file /var/lib/mysql/magi.xxxxxxxx.com.err is smaller than 32 Mb
[!!] /var/lib/mysql/magi.xxxxxxxx.com.err contains 7929 warning(s).
[!!] /var/lib/mysql/magi.xxxxxxxx.com.err contains 5880 error(s).
[--] 38 start(s) detected in /var/lib/mysql/magi.xxxxxxxx.com.err
[--] 1) 2020-06-21 20:36:52 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-06-15 22:25:05 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-05-29 21:23:24 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-05-13 21:23:16 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-05-13 21:23:06 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-04-30 21:27:24 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-01-29 21:22:30 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2020-01-29 21:22:17 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2019-12-18 21:22:04 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2019-12-12 21:22:21 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 37 shutdown(s) detected in /var/lib/mysql/magi.xxxxxxxx.com.err
[--] 1) 2020-06-21 20:36:51 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-06-15 22:25:04 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-05-29 21:23:23 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-05-13 21:23:14 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-05-13 21:23:04 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-04-30 21:27:22 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2020-01-29 21:22:28 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2020-01-29 21:22:16 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2019-12-18 21:22:02 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2019-12-12 21:22:20 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: 272.3M (Tables: 105)
[--] Data in InnoDB tables: 9.7G (Tables: 548)
[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: 9d 0h 2m 51s (2B q [3K qps], 906K conn, TX: 2373G, RX: 624G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 251.6G
[--] Max MySQL memory : 73.1G
[--] Other process memory: 0B
[--] Total buffers: 8.4G global + 258.9M per thread (256 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 23.3G (9.26% of installed RAM)
[OK] Maximum possible memory usage: 73.1G (29.06% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (2K/2B)
[OK] Highest usage of available connections: 23% (59/256)
[OK] Aborted connections: 0.05% (421/906181)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2B selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (745 temp sorts / 2B sorts)
[!!] Joins performed without indexes: 21052
[!!] Temporary tables created on disk: 72% (8M on disk / 11M total)
[OK] Thread cache hit rate: 99% (59 created / 906K connections)
[OK] Table cache hit rate: 38% (874 open / 2K opened)
[!!] table_definition_cache(400) is lower than number of tables(918)
[OK] Open file limit used: 0% (264/40K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 40 thread(s).
[--] Using default value is good enough for your version (10.3.23-MariaDB-log)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 31.5% (42M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/91.7M
[OK] Read Key buffer hit rate: 100.0% (318M cached / 74K reads)
[!!] Write Key buffer hit rate: 67.9% (4M cached / 3M writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 8.0G/9.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 2.0G * 2/8.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 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% (501040406306 hits/ 501040918568 total)
[OK] InnoDB Write log efficiency: 99.63% (209546395 hits/ 210315550 total)
[OK] InnoDB log waits: 0.00% (0 waits / 769155 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 98.4% (754M cached / 12M 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:
Control warning line(s) into /var/lib/mysql/magi.mysticmedusa.com.err file
Control error line(s) into /var/lib/mysql/magi.mysticmedusa.com.err file
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
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
table_definition_cache(400) > 918 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 9.7G) if possible.
innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
