All we need is an easy explanation of the problem, so here it is.
I have two servers:
- Server A: AMD Epyc 7371 – 16c / 32t – 3.1 GHz / 3.8 GHz 256 GB ECC 2400 MHz 2 × 960 GB NVMe SSD MySql 8.0.26
- Server B: Dual Intel Xeon Gold 6242R – 20c / 40t – 3.1 GHz / 4.1 GHz 384 GB ECC 2933 MHz 6 × 3.84 TB NVMe SSD 2 × 480 GB SATA SSD
On server A, despite it being smaller, the database works well and transactions are very fast with over 200 concurrent users.
On the much larger server B this happens to me: up to 50 concurrent users is not a problem and the database works well, but after the number of users to go beyond 50, the database starts to slow down.
Both servers have the same database, same tables, same stored procedure, same applications, in practice they are a copy of each other.
How can I figure out where the problem lies?
This is the configuration file:
[client] pipe= socket=MYSQL port=3306 [mysql] no-beep default-character-set= server_type=1 [mysqld] port=3306 datadir=E:/ProgramData/MySQL/MySQL Server 8.0\Data character-set-server= default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" log-output=FILE general-log=0 general_log_file="NS31525947.log" slow-query-log=1 slow_query_log_file="NS31525947-slow.log" long_query_time=10 log-error="NS31525947.err" log-bin="NS31525947-bin" server-id=1 lower_case_table_names=1 secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" max_connections = 500 table_open_cache=3G tmp_table_size=3G thread_cache_size=100 myisam_max_sort_file_size=10G myisam_sort_buffer_size=68G key_buffer_size=61M read_buffer_size=23M read_rnd_buffer_size=256K innodb_flush_log_at_trx_commit=0 innodb_log_buffer_size=512M innodb_buffer_pool_size=10G innodb_log_file_size=1G innodb_thread_concurrency=0 innodb_autoextend_increment=64 innodb_buffer_pool_instances=8 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 back_log=80 flush_time=0 join_buffer_size=256K max_allowed_packet=4M max_connect_errors=100 open_files_limit=10000 sort_buffer_size=256K table_definition_cache=1400 binlog_row_event_max_size=8K sync_relay_log=10000 sync_relay_log_info=10000 loose_mysqlx_port=33060 default_authentication_plugin = mysql_native_password
I also tried to put the
my.ini from server A on server B, but nothing changed.
I entered the database status references
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
table_open_cache=3G -- NO! That's files, not bytes! Drop to a few thousand innodb_buffer_pool_size=10G -- increase to about 70% of RAM
No more than 1% of available RAM (each):
tmp_table_size=3G --> 200M max_heap_table_size --> 200M myisam_sort_buffer_size=68G --> 200M
If you would like further analysis of settings, see http://mysql.rjweb.org/doc.php/mysql_analysis#tuning
That analysis will include discovering whether you are I/O-bound, hence confirming that SATA vs NVMe is a big issue.
Speeding up queries is another way to scale; let’s analyze the SlowLog
The Answer talking about SSDs leads to the need to speed up "slow" queries. Note that the slowlog measures elapsed time, hence captures disk I/O. Be sure to set
long_query_time = 1.0 (or lower).
Buffer_pool of 10G; data folder with 13.8GB… When a server is first started, nothing is in the buffer_pool and queries run slower than they will eventually. This is due to I/O. (And as already discussed the machines have different I/O speeds.)
Eventually the buffer_pool will fill up and further actions may (or may not, depending on the "working set size") lead to thrashing — I/O to bump out stuff in the cache in order to load new things. This I/O will be especially problematic if queries are doing full table scans.
Since you have lots of RAM, you should increase
innodb_buffer_pool_size. However, if your data size continues to grow, you may eventually hit the sluggishness again. You should locate any "slow" queries and consider how to avoid "full table scans" (or whatever is making them slow).
The one word that grabs my attention is SATA. SATA is slower than SSD
There is a webpage entitled Types of hard drives: SATA vs. SSD vs. NVMe
It says in part
SSD stands for Solid State Drive. These disks don’t have any moving parts. Instead, all of the data is stored on non-volatile flash memory. That means that there isn’t a needle that has to move to read or write data and that they are significantly faster than SATA drives. It’s difficult to find an exact speed because it varies by manufacturer and form factor, but even the lower-performing drives are comparable to SATA drives.
I would suggest putting everything on SSDs. I mentioned getting away from SATA in my old post MySQL/Percona Server is taking long and I'd prefer to skip all this waiting even if it means data loss
I would also suggest splitting the redo logs away from the data using separate disks (See my old post MySQL on SSD – what are the disadvantages?)
I would also set innodb_thread_concurrency to 64 (See my old post MariaDB 10.1.22 to use more RAM rather than CPU)
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂