I have MySQL Database and its 1.5TB. I need to setup replication for this. I have 32core CPU and 250GB Memory. The problem restore is taking more time even I used mydumper/myloader to backup and restore.
Here is my MySQL Setting:
innodb_buffer_pool_size =235G
bulk_insert_buffer_size = 5G
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 22
innodb_adaptive_hash_index = 0
innodb_old_blocks_pct = 45
tx_isolation = READ-UNCOMMITTED
innodb_purge_threads = 2
innodb_change_buffering = inserts
innodb_change_buffer_max_size = 30
query_cache_size = 0
In myloader:
120 threads running to restore.
100000 queries per transaction.
But still its resorting more than a day. Is there any possibilities to restore
Best Answer
If you have "too many" threads doing the restore, they will stumble over each other and might run slower than if you used a fewer number of threads.
Add on
innodb_buffer_pool_instances=16I assume you are using SSDs? Otherwise, some of the settings are too high.
If you are I/O-bound, then probably there is nothing you can do to speed it up. Except...
In the future, consider setting up LVM so that both dump and restore can be on the order of one minute, even for multi-TB dumps. This is because of the "copy of write" technology at the block level of the OS and disk.