0

Im trying to restore a database which is 1.3TB and its in AWS Aurora. I want to restore a copy of this database to an EC2 instance(32 vCPU, 244GB MEMORY).

I used mydumper to take backup from AWS RDS and it took 6 Hrs.

But Im trying to load this backup using myloader. Its running more than 12hrs, but still 30% data only restored.

Myloader Settings:

Threads: 200
Queries Per thread: 10000

MySQL Settings:

max_connections = 3000
wait_timeout = 86400
interactive_timeout = 86400
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
transaction_isolation = READ-UNCOMMITTED
innodb_purge_threads = 2
innodb_change_buffering = inserts
innodb_change_buffer_max_size = 30
query_cache_size = 0
innodb_buffer_pool_instances=20
innodb_log_buffer_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 1G
max_allowed_packet=1G

Is there any additional settings that I should change for faster restore?

0

I think it is choking on 200 threads. Recommend not using more than twice the number of CPU cores you have. And even that may be too high...

In particular, there is no use adding more threads after saturating the I/O. (I am not familiar with your configuration, so I cannot be more specific.)

innodb_buffer_pool_size =235G is dangerously high for 244GB of RAM. If there is swapping, that could be killing performance.

If you have "indexed every column", that is another performance killer for loading. And "indexing every column" is usually useless. Instead, discover what indexes you need, and have only them. This often includes 'composite' indexes.

Also, multiple UNIQUE (including PRIMARY) indexes on a table is costly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.