A
A
akelsey2019-05-27 15:05:36
MySQL
akelsey, 2019-05-27 15:05:36

Slow restore performance from MySQL 5.7 dump (slow insert), any suggestions?

Need help from a MySQL guru, after migrating a project from one server to another, I ran into a performance problem.
Configuration differences
were (left): Opteron, 16GB RAM, supermicro platform, 1TB 7200 xdd 64MB cache, ubuntu 14.04, mysql 5.5
Now (right): zion, 32GB RAM, supermicro, 1TB 7200, ubuntu 18.04, mysql 5.7.
video on youtube

my.cnf

[mysqld_safe]
nice = 0
socket = /var/run/mysqld/mysqld.sock
[mysqld]
sql-mode =
log_error = /var/log/mysql/error.log
skip-external-locking
sync_binlog = 0
default-storage-engine = innodb
innodb_buffer_pool_size = 24G
innodb_file_per_table = 1
innodb_log_file_size = 640M
innodb_flush_log_at_trx_commit = 2
innodb_flush_log_at_timeout = 0
innodb_flush_method = O_DIRECT
innodb_doublewrite = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 0
bulk_insert_buffer_size = 512M
performance_schema_max_thread_instances = 1000
thread_stack = 196608
max_allowed_packet = 16M
max_connections = 256
max_heap_table_size = 512M
max_join_size = 1000000
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M
key_buffer_size = 16M
thread_stack = 256K
thread_cache_size = 16M
query_cache_type = 1
query_cache_limit = 1G
query_cache_size = 64M
table_definition_cache = 4096
table_open_cache = 4096
tmp_table_size = 256M
tmpdir = /var/lib/mysql/tmp
innodb_tmpdir = /var/lib/mysql/tmp
innodb_flush_method = O_DIRECT
transaction-isolation = READ-COMMITTED
# read_buffer_size = 2M
# read_rnd_buffer_size = 2M
# sort_buffer_size = 2M
# join_buffer_size = 2M
log-queries-not-using-indexes
table_open_cache = 16384
table_open_cache_instances = 16384
thread_cache_size = 16

/var/lib/mysql/tmp => ramdrive tmpfs 2GB
writing a 1Gb file via DD is faster on Zion, faster in everything - but INSERTs are just not there.
The base of 234M on the old server is restored in 1:52, on the new 4:42 - 2.5 times (and this is at best, usually it takes up to 8 minutes for a ristor).
Any ideas how to tweak this?
UPD:
Am I correct? A large file is created a little faster. Those. the overall performance of the disk is even higher than the previous one.
When you try to restore the database - there are a lot of INSERT operations, this is logical, but MySQL feels on the left - it makes it much more fun, i.e. it feels like some kind of cache is not enough on MySQL 5.7 on the right, and it constantly commits to disk every insert.
How to make it so that he stuffed everything into a tempo table. And then committed to the base.
Interestingly, I don't see any files in /var/lib/mysql/tmp at all.
UPD2:
Since. everything you need for Bitrix, added tags, and examples of the old site and the new one. Please pay attention to the slow recording even on the site itself:
old site
5cebe128a21ed808853360.png
new website
5cebe110315b2384840864.png

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
akelsey, 2019-05-28
@akelsey

I will answer myself.
Killed a lot of time. I tested IOPS, even reinstalled the host OS build on a clean OS from the vendor (Ubuntu 18.04).
Bottom line, I throw in tags to google: Slow INSERT, Slow MySQL Ubuntu 18.04 write due to:
apt install cpufrequutils
echo 'GOVERNOR="performance"' | sudo tee /etc/default/cpufrequtils
sudo systemctl disable ondemand
The results are not super-duper yet either, but it's already a win (2.5x record!):
5ced93f80469a284852060.png

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question