MySQL – Restore 1TB database using theloader

awsMySQLmysql-5.6restore

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?

Best Answer

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.

Related Solutions

InnoDB INSERT Performance Capabilities in MySQL

You need to tune your InnoDB settings in the following areas:

Make InnoDB access all your cores
Increase innodb_buffer_pool_size to 12G
Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
Increase Log File Size (innodb_log_file_size) to 2047M
support separate tablespace files for individual InnoDB tables (enaled innodb_file_per_table)
support either high performance or high durability (ACID Compliance)
- High Performance : innodb_flush_log_at_trx_commit set to 0 or 2
- High Durability : innodb_flush_log_at_trx_commit set to 1 (Default)
- Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
- Your current setting for innodb_flush_log_at_trx_commit is good
- Your current setting for innodb_flush_method is good
Increase innodb_read_io_threads to 64
Increase innodb_write_io_threads to 64
Increase innodb_io_capactity to 10000

Here are my past posts on tuning the InnoDB storage engine

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Why does InnoDB store all databases in one file? (Mar 25, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
About single threaded versus multithreaded databases performance (May 26, 2011)
How to safely change MySQL innodb variable 'innodb_log_file_size'? (Feb 16, 2011)
How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)
Howto: Clean a mysql InnoDB storage engine? (October 29, 2010)

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before

Best Answer

Related Solutions

InnoDB INSERT Performance Capabilities in MySQL

Mysql – Cannot write to thesql database while select query in process

Related Question