title: Database performance drop, after upgrade to MySQL 8.0.20
body: <p>After upgraded the MySQL from version 5.7 to 8.0, I found out that the database performance is significant drop.</p>
  <p>Before upgrade the MySQL the CPU usage is stable around 30%+-, but after upgraded the CPU usage is become unstable and frequently having large spike.</p>
  <p>And recently I test out something very interesting, I'm keep run a same query for a few time, and found out that the duration taken becomes longer and longer. as per picture shown below.</p>
  <p><a href="https://i.stack.imgur.com/D51Fi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/D51Fi.png" alt="enter image description here" /></a></p>
  <p>I had read a lot of article and stack overflow post, but none of the solution is really get help.
  So hope that someone can share some idea or experience on tuning the MySQL8.0 with me.</p>
  <p>Will very appreciate it.</p>
  <p>Please let me know if needed any info for further investigate.</p>
  <p>Config my.ini:-</p>
  <pre><code>key_buffer_size = 2G
  max_allowed_packet = 1M
  
  ;Added to reduce memory used (minimum is 400)
  table_definition_cache = 600
  
  sort_buffer_size = 4M
  net_buffer_length = 8K
  read_buffer_size = 2M
  read_rnd_buffer_size = 2M
  myisam_sort_buffer_size = 2G
  ;Path to mysql install directory
  basedir=&quot;c:/wamp64/bin/mysql/mysql8.0.20&quot;
  log-error=&quot;c:/wamp64/logs/mysql.log&quot;
  ;Verbosity Value  1 Errors only, 2  Errors and warnings , 3 Errors, warnings, and notes
  log_error_verbosity=2
  ;Path to data directory
  datadir=&quot;c:/wamp64/bin/mysql/mysql8.0.20/data&quot;
  
  
  ;slow_query_log = ON
  ;slow_query_log_file = &quot;c:/wamp64/logs/slow_query.log&quot;
  
  ;Path to the language
  ;See Documentation:
  ; http://dev.mysql.com/doc/refman/5.7/en/error-message-language.html
  lc-messages-dir=&quot;c:/wamp64/bin/mysql/mysql8.0.20/share&quot;
  lc-messages=en_US
  
  ; The default storage engine that will be used when create new tables
  default-storage-engine=InnoDB
  ; New for MySQL 5.6 default_tmp_storage_engine if skip-innodb enable
  ; default_tmp_storage_engine=MYISAM
  
  ;To avoid warning messages
  secure_file_priv=&quot;c:/wamp64/tmp&quot;
  skip-ssl
  
  explicit_defaults_for_timestamp=true
  
  ; Set the SQL mode to strict
  sql-mode=&quot;&quot;
  ;sql-mode=&quot;STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER&quot;
  
  ;skip-networking
  
  ; Disable Federated by default
  skip-federated
  
  ; Replication Master Server (default)
  ; binary logging is required for replication
  ;log-bin=mysql-bin
  
  ; binary logging format - mixed recommended
  ;binlog_format=mixed
  
  ; required unique id between 1 and 2^32 - 1
  ; defaults to 1 if master-host is not set
  ; but will not function as a master if omitted
  server-id = 1
  
  ; Replication Slave (comment out master section to use this)
  
  ; New for MySQL 5.6 if no slave
  skip-slave-start
  
  
  ; The InnoDB tablespace encryption feature relies on the keyring_file
  ; plugin for encryption key management, and the keyring_file plugin
  ; must be loaded prior to storage engine initialization to facilitate
  ; InnoDB recovery for encrypted tables. If you do not want to load the
  ; keyring_file plugin at server startup, specify an empty string.
  early-plugin-load=&quot;&quot;
  
  ;innodb_data_home_dir = C:/mysql/data/
  innodb_data_file_path = ibdata1:12M:autoextend
  ;innodb_log_group_home_dir = C:/mysql/data/
  ;innodb_log_arch_dir = C:/mysql/data/
  
  ; You can set .._buffer_pool_size up to 50 - 80 %
  ; of RAM but beware of setting memory usage too high
  innodb_buffer_pool_size = 4G
  
  ; Set .._log_file_size to 25 % of buffer pool size
  innodb_log_file_size = 16M
  innodb_log_buffer_size = 8M
  innodb_thread_concurrency = 64
  innodb_flush_log_at_trx_commit = 2
  
  log_bin_trust_function_creators = 1;
  
  innodb_lock_wait_timeout = 120
  innodb_flush_method=normal
  innodb_use_native_aio = true
  
  innodb_flush_neighbors = 2
  innodb_autoinc_lock_mode = 1
  
  [mysqldump]
  quick
  max_allowed_packet = 16M
  
  [mysql]
  no-auto-rehash
  ; Remove the next comment character if you are not familiar with SQL
  ;safe-updates
  
  [isamchk]
  key_buffer_size = 20M
  sort_buffer_size = 20M
  read_buffer_size = 2M
  write_buffer_size = 2M
  
  [myisamchk]
  key_buffer_size = 256M ;20M hys
  sort_buffer_size_size = 20M
  read_buffer_size = 2M
  write_buffer_size = 2M
  
  [mysqlhotcopy]
  interactive-timeout
  
  [mysqld]
  port = 3306
  skip-log-bin
  default_authentication_plugin= mysql_native_password
  
  max_connections = 400
  max_connect_errors = 100000
  
  innodb_read_io_threads = 32
  innodb_write_io_threads = 8
  innodb_thread_concurrency = 64
  
  </code></pre>
  <p>Hardware:-
  Ram: 16GB
  CPU: 4 Cores 3.0 Ghz</p>
  <p>SHOW GLOBAL STATUS:
  <a href="https://pastebin.com/FVZrgnTw" rel="nofollow noreferrer">https://pastebin.com/FVZrgnTw</a></p>
  <p>SHOW ENGINE INNODB STATUS:
  <a href="https://pastebin.com/Rewp84Gi" rel="nofollow noreferrer">https://pastebin.com/Rewp84Gi</a></p>
  <p>SHOW GLOBAL VARIABLES:
  <a href="https://pastebin.com/3v6cM6KZ" rel="nofollow noreferrer">https://pastebin.com/3v6cM6KZ</a></p>