1

I am migrating from a 4 year old setup to a brand new high performance server and am experiencing slower performance than on the old machine.

The old setup is a Ubuntu 16.04 Server on bare metal Intel i7 64G RAM and MySQL 5.7 and is under medium load with many services running.

The new setup is an AMD Ryzen™ 9 5950X with 128G RAM, no load and only MySQL 8 running on Ubuntu 20.04. Both are bare metal machines and I am the only one who can access it.

Now the database I am testing against holds a table with 100M rows. The new one holds an exact copy, imported after a mysqldump. I am running the exact same simple query without any join, just from one table.

The old server returns the result in about 120ms the new one returns in about 200ms.

10 rows retrieved starting from 1 in 192 ms (execution: 184 ms, fetching: 8 ms)

I am using DataGrip for the query (connected via SSH). If I connect via SSH terminal directly and use MySQL on the console the result is returned in 0.00s

How is that even possible? I did run a few tests via mysqltuner, but found nothing that might be of help.

Here is part of the the my.cnf from the new server:

[mysqld]
default_authentication_plugin= mysql_native_password
innodb_buffer_pool_size = 100G
innodb_buffer_pool_instances = 64
innodb_buffer_pool_chunk_size = 134217728
innodb_log_file_size = 12G    
collation_server        = utf8_unicode_ci
character_set_server    = utf8

This is mysqltuner output:

mysqltuner
 >>  MySQLTuner 1.7.13 - Major Hayden <major@mhtx.net>
 >>  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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [!!] Currently running unsupported MySQL version 8.0.25-0ubuntu0.20.04.1
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /mnt/mysql/data/leo.err(0B)
[!!] Log file /mnt/mysql/data/leo.err doesn't exist
[!!] Log file /mnt/mysql/data/leo.err isn't readable.
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 16.8G (Tables: 39)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1m 47s (419 q [3.916 qps], 45 conn, TX: 402K, RX: 35K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 125.8G
[--] Max MySQL memory    : 104.2G
[--] Other process memory: 136.7M
[--] Total buffers: 104.0G global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 104.0G (82.72% of installed RAM)
[OK] Maximum possible memory usage: 104.2G (82.85% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/419)
[OK] Highest usage of available connections: 1% (2/151)
[!!] Aborted connections: 4.44%  (2/45)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19 sorts)
[!!] Joins performed without indexes: 18
[OK] Temporary tables created on disk: 0% (0 on disk / 23 total)
[OK] Thread cache hit rate: 95% (2 created / 45 connections)
[OK] Table cache hit rate: 80% (327 open / 408 opened)
[OK] Open file limit used: 0% (2/10K)
[OK] Table locks acquired immediately: 100% (9 immediate / 9 locks)
[OK] Binlog cache memory access: 0% (0 Memory / 0 Total)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 104.0G/16.8G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 12.0G * 2/104.0G should be equal 25%
[OK] InnoDB buffer pool instances: 64
[--] Number of InnoDB Buffer Pool Chunk : 832 for 64 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 13.96% (5858 hits/ 41974 total)
[OK] InnoDB Write log efficiency: 98.44% (630 hits/ 640 total)
[OK] InnoDB log waits: 0.00% (0 waits / 10 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
 
-------- 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)

What is a recommended aproach to narrow down the problem?

3
  • see dba.stackexchange.com/questions/267143/… you will find also a lote more that resolves around that theme
    – nbk
    May 15, 2021 at 17:58
  • 1
    It's just a simple where query. The latency issue might be the reason. The new server is about 2K away while the other one is just 100KM away. However DatGrip is connected via SSH tunnel. Why is the query issued over terminal ssh so fast (it really is!!)
    – merlin
    May 15, 2021 at 18:35
  • Posting your complete my.cnf would be more helpful. Also, from OS Command Prompt, post text results of ulimit -a report so we can see the limits allowed. Is there a good reason to not have a readable error log? May 23, 2021 at 6:52

1 Answer 1

1

You may find significant relief with these suggestions for your my.cnf [mysqld] section

innodb_buffer_pool_size=32G  # from 104G because you have 16.8G of data
innodb_buffer_pool_instances=16  # from 64 should be adequate
innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function.

Posting to pastebin.com and sharing the links of

SHOW GLOBAL STATUS;     minimum of 24 hours uptime 
SHOW GLOBAL VARIABLES;

along with other information already requested would allow workload analysis to provide suggestions to improve performance.

2
  • @merlin Could you summarize current run time for the query in question? Second trip? Now that you have applied the 3 suggestions. Thank you. Jun 16, 2021 at 15:00
  • @merlin Any good or bad news you can share with us? Maybe next week you will be able to post to pastebin.com and share the links for TEXT output of SHOW GLOBAL STATUS; after mimimum 24 hours uptime SHOW GLOBAL VARIABLES; for workload analysis to provide suggestions to improve performance. Jun 23, 2021 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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