Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

i've a mysqldump ( 1.5G large ) of a table I need to import.

The server i bought, which is clean and not under load, takes > 2hours to import it.

I read many tweaks i can use to speed up the import process but none of them are really helping.

Particoularly I did reformat the dump as folllow:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

--- 
Original dump content.
---

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

This, as I already said, didn't help. I also enabled the following stuff in my my.cnf

innodb_additional_mem_pool_size = 20971520
innodb_buffer_pool_size = 536870912
innodb_file_per_table
innodb_log_buffer_size = 8388608
join_buffer_size = 67104768
max_allowed_packet = 5241856
max_binlog_size = 1073741824
max_heap_table_size = 41943040
read_buffer_size = 20967424
sort_buffer_size = 67108856
table_cache = 256
thread_cache_size = 128
thread_stack = 327680
tmp_table_size = 41943040

You can find my hardware specs here: http://www.hetzner.de/en/hosting/produkte_rootserver/ex4

Just for completeness i'm using percona xtradb cluster server 5.5 but the mysql cluster is composed by only one node.

UPDATE. I'm using software raid 1.

share|improve this question
    
Those specs don't mention RAID which is pretty important for what is likely to be a disk-bound operation. Check what resource is at 100% during the import and if it's disk, add some performance stats to your question. –  Ladadadada Jan 20 '13 at 18:20
    
Thanks @Ladadadada I updated the details. Thanks again –  Andrea Baccega Jan 20 '13 at 23:17
    
I'm not sure what the point of this question is. Is it unclear why this takes so long? Perhaps examining how the dump was created helps, dumps created with --skip-opt take ages to import. –  fuero Jan 21 '13 at 0:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.