Hello I am currently running this server:
Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz
256GB RAM
2x250GB SSD RAID 1 (only for operating system)
2x2TB SSD in RAID 1 (only for accounts) (/home4)
2TB HDD for 10 accounts with a lot of emails (/home5)
6TB HDD for incremental backups and compressed backups (compressed ones are made and downloaded once a week to my PC) (/home3)
MariaDB 10.6.12-MariaDB
mod_mpm_prefork Installed 2.4.55-2.2.3.cpanel
PHP Handler is mod_lsapi and I am using Wordpress W3 Total Cache on resource demanding websites (~15 of them) so it can use memcached and APCu. I still haven't got time to install it on all Wordpress websites.
I am also having all ConfigServer products installed and working just fine.
I have 150 websites, 99% of them are Wordpress, and almost all of them are on Cloudflare.
Server load is around 2-5. Sometimes it goes to 10-15 for couple of minutes, but mostly 2-5.
RAM usage is around 60-90GB.
I would like to state that server runs just fine, but it bugs me for months now it this the correct setup, or is there anything that I could improve, since this server has great hardware. Do I have some bottlenecks somewhere in MySQL.
I've tried to increase table_open_cache = 524123 and open_files_limit = 1048576 since phpMyAdmin Advisor says I should increase these, but for some reason it will not allow me to increase more than values I wrote.
Could someone suggest what could be improved, and what did I wrong?
Thank you!
Nermin
Stats are bellow.
This is my disk usage:
/dev/sda1 5.5T 4.0T 1.2T 78% /home3
/dev/sdb2 2.0G 323M 1.6G 18% /boot
/dev/sdb4 219G 143G 65G 69% /
/dev/sdc1 1.8T 1.4T 406G 77% /home4
/dev/sdd1 1.8T 1.6T 162G 91% /home5
When I open phpMyAdmin in WHM and go see "Query statistics" it says
In phpMyAdmin "All status variables" (ones in alert values) are:
Now in phpMyAdmin Advisor section says these:
Possible performance issues
This is my.cnf setup that I have found on Github and modified it for several months now:
This is mysqltuner log:
Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz
256GB RAM
2x250GB SSD RAID 1 (only for operating system)
2x2TB SSD in RAID 1 (only for accounts) (/home4)
2TB HDD for 10 accounts with a lot of emails (/home5)
6TB HDD for incremental backups and compressed backups (compressed ones are made and downloaded once a week to my PC) (/home3)
MariaDB 10.6.12-MariaDB
mod_mpm_prefork Installed 2.4.55-2.2.3.cpanel
PHP Handler is mod_lsapi and I am using Wordpress W3 Total Cache on resource demanding websites (~15 of them) so it can use memcached and APCu. I still haven't got time to install it on all Wordpress websites.
I am also having all ConfigServer products installed and working just fine.
I have 150 websites, 99% of them are Wordpress, and almost all of them are on Cloudflare.
Server load is around 2-5. Sometimes it goes to 10-15 for couple of minutes, but mostly 2-5.
RAM usage is around 60-90GB.
I would like to state that server runs just fine, but it bugs me for months now it this the correct setup, or is there anything that I could improve, since this server has great hardware. Do I have some bottlenecks somewhere in MySQL.
I've tried to increase table_open_cache = 524123 and open_files_limit = 1048576 since phpMyAdmin Advisor says I should increase these, but for some reason it will not allow me to increase more than values I wrote.
Could someone suggest what could be improved, and what did I wrong?
Thank you!
Nermin
Stats are bellow.
This is my disk usage:
/dev/sda1 5.5T 4.0T 1.2T 78% /home3
/dev/sdb2 2.0G 323M 1.6G 18% /boot
/dev/sdb4 219G 143G 65G 69% /
/dev/sdc1 1.8T 1.4T 406G 77% /home4
/dev/sdd1 1.8T 1.6T 162G 91% /home5
When I open phpMyAdmin in WHM and go see "Query statistics" it says
Questions since startup: 270,074,994
ø per hour: 1,136,867
ø per minute: 18,948
ø per second: 316
In phpMyAdmin "All status variables" (ones in alert values) are:
| Aborted clients | 56 | The number of connections that were aborted because the client died without closing the connection properly. |
|---|---|---|
| Aborted connects | 254 | The number of failed attempts to connect to the MySQL server. |
| Created tmp disk tables | 13.1 M | The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based. |
| Handler read rnd | 16.1 G | The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly. |
| Handler read rnd next | 46.9 G | The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have. |
| Innodb buffer pool pages dirty | 192.2 k | The number of pages currently dirty. |
| Innodb buffer pool reads | 581 k | The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read. |
| Innodb row lock waits | 20.2 k | The number of times a row lock had to be waited for. |
| Key reads | 345 k | The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests. |
| Opened tables | 347.2 k | The number of tables that have been opened. If opened tables is big, your table cache value is probably too small. |
| Qcache free blocks | 1 | The number of free memory blocks in query cache. High numbers can indicate fragmentation issues, which may be solved by issuing a FLUSH QUERY CACHE statement. |
| Select full join | 644.1 k | The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables. |
| Select range check | 1.8 k | The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.) |
| Slow queries | 61 | The number of queries that have taken more than long_query_time seconds. |
| Sort merge passes | 68 | The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable. |
| Table locks waited | 156 | The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication. |
Now in phpMyAdmin Advisor section says these:
Possible performance issues
| Issue | Recommendation |
|---|---|
| Suboptimal caching method. | You are using the MySQL Query cache with a fairly high traffic database. It might be worth considering to use memcached instead of the MySQL Query cache, especially if you have multiple slaves. |
| There are lots of rows being sorted. | While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting. |
| There are too many joins without indexes. | This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins. |
| The rate of reading the first index entry is high. | This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running 'OPTIMIZE TABLE' might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries. |
| The rate of reading data from a fixed position is high. | This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable. |
| The rate of reading the next table row is high. | This indicates that many queries are doing full table scans. Add indexes where applicable. |
| Many temporary tables are being written to disk instead of being kept in memory. | Increasing max_heap_table_size and tmp_table_size might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the beginning of an Article by the Pythian Group |
| MyISAM key buffer (index cache) % used is low. | You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used. |
| The % of indexes that use the MyISAM key buffer is low. | You may need to increase key_buffer_size. |
| The rate of opening tables is high. | Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this. |
| Too many connections are aborted. | Connections are usually aborted when they cannot be authorized. This article might help you track down the source. |
| The InnoDB log file size is inadequately large. | It is usually sufficient to set innodb_log_file_size to 25% of the size of innodb_buffer_pool_size. A very big innodb_log_file_size slows down the recovery time after a database crash considerably. See also this blog entry |
| The query cache is not enabled. | The query cache is known to greatly improve performance if configured correctly. Enable it by setting query_cache_size to a 2 digit MiB value and setting query_cache_type to 'ON'. Note: If you are using memcached, ignore this recommendation. |
This is my.cnf setup that I have found on Github and modified it for several months now:
[mysqld]
performance-schema = 1
basedir = /usr
bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
datadir = /var/lib/mysql
max_allowed_packet = 512M
max_connect_errors = 1000000
pid_file = /var/lib/mysql/mysql.pid
port = 3306
skip_external_locking
socket = /var/lib/mysql/mysql.sock
tmpdir = /tmp
user = mysql
default_storage_engine = InnoDB
innodb_buffer_pool_instances = 64 # Use 1 instance per 1GB of InnoDB pool size - max is 64
innodb_buffer_pool_size = 175G # Use up to 70-80% of RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 44800M
innodb_sort_buffer_size = 8M # UPD - Defines how much data is read into memory for sorting operations before writing to disk (default is 1M / max is 64M)
innodb_stats_on_metadata = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 2000 # Depends on the storage tech - use 2000 for SSD, more for NVMe
query_cache_type = 0 # Enabled by default
key_buffer_size = 15M
low_priority_updates = 1
concurrent_insert = 2
max_connections = 300
back_log = 512
thread_cache_size = 256
thread_stack = 192K
interactive_timeout = 180
wait_timeout = 180
join_buffer_size = 1M
read_buffer_size = 2M
read_rnd_buffer_size = 4M # UPD
sort_buffer_size = 4M # UPD
table_open_cache_instances = 2
table_definition_cache = 40000 # UPD
table_open_cache = 524123 # UPD
open_files_limit = 1048576 # UPD
max_heap_table_size = 4095M
tmp_table_size = 4095M
ft_min_word_len = 3 # Minimum length of words to be indexed for search results
disable_log_bin = 1 # Binary logging disabled by default
log_error = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes = 0 # Disabled on production
long_query_time = 5
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql_slow.log
unix_socket = OFF
[mysqldump]
quick
quote_names
max_allowed_packet = 1024M
This is mysqltuner log:
>> MySQLTuner 2.0.9
* Jean-Marie Renouard <[email protected]>
* 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] Currently running supported MySQL version 10.6.12-MariaDB-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 (23M)
[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
[OK] Log file /var/lib/mysql/mysql_error.log is readable.
[!!] /var/lib/mysql/mysql_error.log contains 3145 warning(s).
[!!] /var/lib/mysql/mysql_error.log contains 9861 error(s).
[--] 81 start(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2023-02-22 22:55:38 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 2) 2023-02-20 20:48:05 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 3) 2023-02-20 20:44:56 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 4) 2023-02-17 21:41:25 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 5) 2023-02-17 21:40:21 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 6) 2023-02-17 21:39:22 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 7) 2023-02-17 21:38:36 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 8) 2023-02-17 21:36:18 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 9) 2023-02-17 21:33:19 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 10) 2023-02-17 21:31:40 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 80 shutdown(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2023-02-20 20:48:03 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 2) 2023-02-20 20:44:52 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 3) 2023-02-17 21:41:23 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 4) 2023-02-17 21:40:18 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 5) 2023-02-17 21:39:19 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 6) 2023-02-17 21:38:34 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 7) 2023-02-17 21:36:15 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 8) 2023-02-17 21:33:16 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 9) 2023-02-17 21:31:37 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 10) 2023-02-17 21:30:58 0 [Note] /usr/sbin/mariadbd: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in Aria tables: 32.0K (Tables: 1)
[--] Data in MyISAM tables: 510.2M (Tables: 297)
[--] Data in InnoDB tables: 12.8G (Tables: 16332)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in MEMORY tables: 0B (Tables: 86)
[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 are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 9d 21h 41m 47s (274M q [320.919 qps], 4M conn, TX: 1647G, RX: 54G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory : 251.6G
[--] Max MySQL memory : 332.4G
[--] Other process memory: 0B
[--] Total buffers: 179.2G global + 523.2M per thread (300 max threads)
[--] Performance_schema Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 258.3G (102.67% of installed RAM)
[!!] Maximum possible memory usage: 332.4G (132.11% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (60/274M)
[OK] Highest usage of available connections: 51% (155/300)
[OK] Aborted connections: 0.01% (252/4279274)
[!!] 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% (67 temp sorts / 37M sorts)
[!!] Joins performed without indexes: 644370
[!!] Temporary tables created on disk: 40% (13M on disk / 32M total)
[OK] Thread cache hit rate: 99% (155 created / 4M connections)
[OK] Table cache hit rate: 99% (286M hits / 287M requests)
[OK] table_definition_cache (40000) is greater than number of tables (17014)
[OK] Open file limit used: 0% (708/1M)
[OK] Table locks acquired immediately: 99% (388K immediate / 388K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 36.1% (5.4M used / 15.0M cache)
[!!] Key buffer size / total MyISAM indexes: 15.0M/23.0M
[!!] Read Key buffer hit rate: 90.7% (3M cached / 345K reads)
[!!] Write Key buffer hit rate: 76.7% (475K cached / 364K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 175.0G / 12.8G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 43.8G * 1/175.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1400 for 1 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% (257374941102 hits / 257375522076 total)
[!!] InnoDB Write Log efficiency: 78.55% (372858 hits / 474658 total)
[OK] InnoDB log waits: 0.00% (0 waits / 847516 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/520.0K
[OK] Aria pagecache hit rate: 97.1% (13B cached / 396M 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/lib/mysql/mysql_error.log file
Check error line(s) in /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.
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).
Temporary table size is already large: reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
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 (> 1.0M, or always use indexes with JOINs)
key_buffer_size (~ 5M)
key_buffer_size (> 23.0M)
Last edited by a moderator:
