Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using mysqldump database > output.sql to dump all the data in a database. The size of the output file is around 3.3 GB. I am importing it using mysql < output.sql however it has been running for more than 24 hours and still hasn't finished.

Is this normal? Is there some way to speed up the import?

mysql> SHOW VARIABLES LIKE '%innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |
| ignore_builtin_innodb                   | OFF                    |
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 8388608                |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | OFF                    |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 1048576                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
37 rows in set (0.12 sec)

mysql> SHOW VARIABLES LIKE '%key_buffer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
1 row in set (0.03 sec)

mysql> SHOW VARIABLES LIKE '%table%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| big_tables                 | OFF      |
| innodb_file_per_table      | OFF      |
| innodb_table_locks         | ON       |
| lower_case_table_names     | 0        |
| max_heap_table_size        | 16777216 |
| max_tmp_tables             | 32       |
| old_alter_table            | OFF      |
| sql_big_tables             | OFF      |
| table_definition_cache     | 256      |
| table_lock_wait_timeout    | 50       |
| table_open_cache           | 64       |
| table_type                 | MyISAM   |
| tmp_table_size             | 16777216 |
| updatable_views_with_limit | YES      |
+----------------------------+----------+
14 rows in set (0.00 sec)
share|improve this question
    
Also, the tables are InnoDB –  Gordon Aug 2 '10 at 18:06

4 Answers 4

I can't imagine that much data taking more than an hour or two on a reasonably modern machine. I'd use MySQL Administrator (found in MySQL GUI Tools) or mtop to make sure it's not just hanging.

share|improve this answer

Probably your mysqld settings have been tuned to contain too low settings for memory usage.

For MyISAM tables, make sure you have key_buffer_size and table_cache properly tuned.

For InnoDB tables, make sure you have innodb_buffer_pool_size properly tuned. With InnoDB it also can be helpful to wrap the insert statements around a transaction.

Please post here output from the following commands:

SHOW VARIABLES LIKE '%innodb%';
SHOW VARIABLES LIKE '%key_buffer%';
SHOW VARIABLES LIKE '%table%';
share|improve this answer
    
output added to post –  Gordon Aug 2 '10 at 17:40

With
show full processlist;
you can see all process running, so if it's still inserting data, you should see it.

SHOW OPEN TABLES
SHOW TABLE STATUS

Should also give you some info. Give it a try and show us the results.

Should not take more than 2h. I agree.

share|improve this answer
up vote 0 down vote accepted

This actually turned out to be a disk space issue on my end. Nothing to see here.

share|improve this answer

Your Answer

 
discard

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

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