Hi Peter,
(i) the bug site suggested 8.0 has optimisations that will help, have you tried to mount a version of the system on 8.0?
I didn't build a version on 8.0
(ii) how have you proved that partitioning improves performance in your system?
We perform a stress test with a volume 5 times larger.
And we need partitioning because we have to purge the data every 6 months.
(iii) is your hardware adequate & optimised for the load it is carrying?
Yes it is suitable.
(iv) please post my.cnf contents
log_bin=mysql-bin
binlog_format=MIXED
wait_timeout=3600
interactive_timeout=3600
bulk_insert_buffer_size=256M
performance_schema=ON
innodb_io_capacity=4000
innodb_write_io_threads=64
innodb_read_io_threads=64
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_max_dirty_pages_pct=0
innodb_adaptive_hash_index_parts=16
innodb_page_cleaners=8
tmpdir="E:/MySQL/temp/"
enable-named-pipe
shared-memory
shared-memory-base-name=MYSQL3306
socket=MYSQL3306
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.7/"
datadir=E:/MySQL/Data/
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="general.log"
slow-query-log=1
slow_query_log_file="slow.log"
long_query_time=3
expire_logs_days=3
log-error="error.err"
server-id=100
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
max_connections=7500
table_open_cache=8192
tmp_table_size=128M
thread_cache_size=3500
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=64M
key_buffer_size=64M
read_buffer_size=8192
read_rnd_buffer_size=256k
transaction-isolation=READ-COMMITTED
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_buffer_pool_size=100000M
innodb_log_file_size=2000M
innodb_thread_concurrency=49
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=50000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=582
flush_time=0
join_buffer_size=64M
max_allowed_packet=1024M
max_connect_errors=100
open_files_limit=300000
sort_buffer_size=256K
table_definition_cache=400000
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
(v) let's see the results of
https://www.artfulsoftware.com/infotree/tip.php?id=1339&r=1, please post inside BBCode code tags for human readability.
https://pastebin.com/zYrrE8HP