Performance of the same MySQL database is different on different servers



  • 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



  • The one word that grabs my attention is SATA. SATA is slower than SSD

    There is a webpage entitled https://www.pluralsight.com/blog/it-ops/types-of-hard-drives-sata-ssd-nvme#:%7E:text=SSD%20stands%20for%20Solid%20State,significantly%20faster%20than%20SATA%20drives.

    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 https://dba.stackexchange.com/questions/84550/mysql-percona-server-is-taking-long-and-id-prefer-to-skip-all-this-waiting-even/84552#84552

    I would also suggest splitting the redo logs away from the data using separate disks (See my old post https://dba.stackexchange.com/questions/58341/mysql-on-ssd-what-are-the-disadvantages/58375#58375 )

    I would also set innodb_thread_concurrency to 64 (See my old post https://dba.stackexchange.com/questions/183403/mariadb-10-1-22-to-use-more-ram-rather-than-cpu/183425#183425 )




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2