MySQL Forums
Forum List  »  Optimizer & Parser

Re: Startup was very slow
Posted by: Charles Rodrigues Rimiru
Date: March 02, 2022 08:58AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
692
January 27, 2022 07:53AM
189
February 28, 2022 11:14AM
Re: Startup was very slow
190
March 02, 2022 08:58AM
259
March 02, 2022 10:58AM
90
October 09, 2022 09:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.