MySQL 8.0.12 key efficiency at 0.0% in MySQL Workbench

Question

I have a MySQL server where MySQL has been installed since december 2015. Then I upgraded it to MySQL 8.0.12 for about 2 weeks ago (2018-08-07).

• I created a dump file from the old database about 20GB
• Then I uninstalled MySQL 5.7 via the add/remove function in Windows Control panel.
• Then I installed the new MySQL 8.0.12 database via the msi installation file.
• I imported all the data from the dump-file - and changed character set to utf8mb4 for all databases and tables etc.

Everything worked fine - and the new database does also work now - but there is one thing I wonder about: There is no key efficiency in MySQL workbench:

The Server is a Windows Server 2012 64bit with 8GB RAM. And there is a lot of traffic every day. I have tried a lot of different options in the configuration file - in order to increase the performance of the database - but nothing seems to help. Another thing I think is strange is that the old folder \ProgramData\MySQL\MySQL 5.7 still contains the my.ini file which contains the current configuration. When I upgraded the MySQL server to 8.0.12 it also created another folder called \ProgramData\MySQL\MySQL 8.0\ - which contains all the data. Is that normal that a new version of MySQL automatically uses an old configuration file from the old version if MySQL..?

I have attached the my.ini configuration file here. Any good ideas to why nothing happens regarding key efficiency - and also any good ideas to which changes I should do in the configuration-file? (all paths are replaced with "????")

[mysqld]

skip_name_resolve=on

innodb_buffer_pool_size=6G

innodb_buffer_pool_instances=8

innodb_buffer_pool_chunk_size=64M

disconnect_on_expired_password=off

port=3306

datadir=????

character-set-server=utf8mb4

collation-server=utf8mb4_0900_ai_ci

default_authentication_plugin=mysql_native_password

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

log-output=FILE

general-log=0

general_log_file="????"

slow-query-log=1

slow_query_log_file="????"

long_query_time=10

log-error="????"

server-id=1

lower_case_table_names=1

secure-file-priv="????"

max_connections=151

table_open_cache=2000

tmp_table_size=16M

thread_cache_size=10

myisam_max_sort_file_size = 100M

myisam_sort_buffer_size = 100M

key_buffer_size=104857600

read_buffer_size=0

read_rnd_buffer_size=0

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=256M

innodb_log_file_size=2G

innodb_thread_concurrency=17

innodb_autoextend_increment=64

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=100M

max_allowed_packet = 16M

max_connect_errors=100

open_files_limit=4161

sort_buffer_size=100M

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

loose_mysqlx_port=33060

skip-character-set-client-handshake

mysql_firewall_mode = off

auto_generate_certs = off

sha256_password_auto_generate_rsa_keys = off

caching_sha2_password_auto_generate_rsa_keys = off

innodb_doublewrite = off

max_binlog_size = 1G

binlog_row_image = minimal

binlog_stmt_cache_size = 32768

binlog_expire_logs_seconds = 3600

binlog_cache_size = 32768

max_binlog_stmt_cache_size = 1G

binlog_row_metadata = MINIMAL

binlog-do-db = hmailserver

max_relay_log_size = 0

Answer 1

Suggestions to consider for your my.ini [mysqld] section (RPS=Rate Per Second)

# 20180826 05:30 from mysqlservertuning com
# myisam_max_sort_file_size=2G  # from 100G - you only have 8G
# read_rnd_buffer_size=256K  # from 1 character to reduce handler_read_rnd_next RPS
# read_buffer_size=128K  # from 8192 to reduce handler_read_next RPS
# tmp_table_size=32M  # from 16M to reduce created_tmp_tables RPS
# max_heap_table_size=32M  # from 16M to reduce created_tmp_disk_tables RPS
# thread_cache_size=100  # from 10 to reduce threads_created and CAP at 100 per refman
# innodb_buffer_pool_size=5G  # from 8M per SHOW GLOBAL STATUS today for data/ndx in RAM
# innodb_log_file_size=200M  # from 50M to extend minutes to next log rotation
# innodb_log_buffer_size=100M  # from 1M to support ~ 30 minutes of logging
# innodb_thread_concurrency=0  # from 17 see dba.stackexchange.com Question 5666
# innodb_flushing_avg_loops=10  # from 30 to reduce loop delay
# innodb_io_capacity=2000  # from 200 to allow higher IOPS

Save your current my.ini in \history with DATED timed filename such as 20180826hhmm-my.ini to allow going back to last working my.ini quickly.

Copy this BLOCK (including the leading date and our web site name)to END of your [mysqld] section and ENABLE one change per DAY by removing leading # and spacecharacter, monitor before moving on to next change.

Disable EARLIER same NAMED variable with leading # and space bar, to avoid confusion. In 5 years you will still have history of my.ini changes with approximate dates.

Normally only ONE change per day, monitor before moving to next change. In your situation, I would implement first 3 changes now, then one per day. If a change seems detrimental, go back to last working my.ini and let us know, please.

Answer 2

Observations:

• Version: 8.0.12
• 8 GB of RAM
• Uptime = 1d 00:25:21
• You are running on Windows.
• Running 64-bit version
• You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

innodb_buffer_pool_size 8388608 is REALLY BAD! Change to 5G. 8M is a very old default; did you carry that value over during an upgrade? This low number comes from SHOW VARIABLES; I don't know what happened to the 6G in the config file. Check which config file is being used.

innodb_log_file_size = 200M

There are also indications of poorly indexed or poorly written queries. Change long_query_time=2 and turn on the slowlog. This will help you identify the villains. Then we can discuss improving a couple of them (in another Question).

There may be more tuning items to discuss, but they may be just artifacts of the above items.

Details and other observations:

( Innodb_buffer_pool_reads ) = 45,168,170 / 87921 = 513 /sec -- InnoDB buffer_pool I/O read rate -- check innodb_buffer_pool_size

( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 8M = 0 -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 8M / 8192M = 0.10% -- % of RAM used for InnoDB buffer_pool

( innodb_buffer_pool_size ) = 8M -- InnoDB Data + Index cache -- 128M (an old default) is woefully small.

( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 340,244,003 / 2770300306 = 12.3% -- Read requests that had to hit disk -- Increase innodb_buffer_pool_size if you have enough RAM.

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((45168170 + 3155389) ) / 87921 = 549 /sec -- InnoDB I/O -- Increase innodb_buffer_pool_size?

( Innodb_buffer_pool_read_ahead_evicted ) = 8,281,795 / 87921 = 94 /sec

( innodb_log_buffer_size ) = 1M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions. -- Adjust innodb_log_buffer_size.

( Innodb_log_writes ) = 8,601,348 / 87921 = 98 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 87,921 / 60 * 48M / 4750519296 = 15.5 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 1,217,329 / 1568258 = 0.776 -- Churn -- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 340244003 + 3164195 ) / 87921 / 200 = 1952.9% -- If > 100%, need more io_capacity. -- Increase innodb_io_capacity if the drives can handle it.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( query_prealloc_size / _ram ) = 8,192 / 8192M = 0.00% -- For parsing. Pct of RAM

( query_alloc_block_size / _ram ) = 8,192 / 8192M = 0.00% -- For parsing. Pct of RAM

( Created_tmp_disk_tables ) = 234,424 / 87921 = 2.7 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size and max_heap_table_size. Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Created_tmp_tables ) = 234,424 / 242216 = 96.8% -- Percent of temp tables that spilled to disk -- Maybe increase tmp_table_size and max_heap_table_size; improve indexes; avoid blobs, etc.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (1313529 + 596764 + 51863 + 0) / 0 = INF -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 18,208,699 / 87921 = 207 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 18,208,699 / 20968533 = 86.8% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( Com_admin_commands ) = 2,588,206 / 87921 = 29 /sec -- Why so many DDL statements?

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days) -- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash. (Not relevant if log_bin = OFF)

( slave_pending_jobs_size_max / max_allowed_packet ) = 128M / 4M = 32 -- For parallel slave threads -- slave_pending_jobs_size_max must not be less than max_allowed_packet

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( back_log / max_connections ) = 80 / 151 = 53.0%

( Com_change_db / Connections ) = 2,588,247 / 2625 = 985 -- Database switches per connection -- (minor) Consider using "db.table" syntax

( Com_change_db ) = 2,588,247 / 87921 = 29 /sec -- Probably comes from USE statements. -- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.