1

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 Right now

Network Status

Server Status

1

2 Answers 2

2
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).

More

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).

4
  • I tested it and I find that table_open_cache has a max value of 512K. If I try to set it to 3G, it is capped at 524288. Commented Mar 28, 2022 at 20:24
  • @BillKarwin - Thanks. But even that value is ludicrous. Do you happen to know how many bytes one entry takes?
    – Rick James
    Commented Mar 28, 2022 at 22:58
  • I don't know offhand, and glancing at the code it looks like it is very dynamic both because the number of elements in the cache rises and falls, and the content of each element in the cache has some info about table metadata, which varies. Commented Mar 28, 2022 at 23:47
  • I have moved all MySql to SSD disks I configured My.ini according to the advice you knowgave me but the problem remains I created a log where the transaction execution time returns me Transaction Time 0.96916508674622 Transaction Time 0.9769492149353 Transaction Time 1.976231098175 Transaction Time 1.5445539951324 Transaction Time 0.034237146377563 ** all should be like this instead each transaction has different times Suggestions? Commented Mar 29, 2022 at 14:49
2

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)

2
  • Yeah. Meanwhile "NVMe SSD" is about as fast as SSDs come.
    – Rick James
    Commented Mar 28, 2022 at 20:00
  • I have moved all MySql to SSD disks I configured My.ini according to the advice you knowgave me but the problem remains I created a log where the transaction execution time returns me Transaction Time 0.96916508674622 Transaction Time 0.9769492149353 Transaction Time 1.976231098175 Transaction Time 1.5445539951324 Transaction Time 0.034237146377563 ** all should be like this instead each transaction has different times Commented Mar 29, 2022 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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