Speeding up mysql dumps and imports [closed]

by

Assuming that you’re using InnoDB…

I was in the situation of having a pile of existing mysqldump output files that I wanted to import in a reasonable time. The tables (one per file) were about 500MB and contained about 5,000,000 rows of data each. Using the following parameters I was able to reduce the insert time from 32 minutes to under 3 minutes.

innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT

You’ll also need to have a reasonably large innodb_buffer_pool_size setting.

Because my inserts were a one-off I reverted the settings afterwards. If you’re going to keep using them long-term, make sure you know what they’re doing.

I found the suggestion to use these settings on Cedric Nilly’s blog and the detailed explanation for each of the settings can be found in the MySQL documentation.

More Related Contents:

  1. Restoring MySQL database from physical files
  2. Can I restore a single table from a full mysql mysqldump file?
  3. How to take backup of a single table in a MySQL database?
  4. Dump a mysql database to a plaintext (CSV) backup from the command line
  5. What is the best way to do incremental backups in MySQL?
  6. How to import a SQL Server .bak file into MySQL?
  7. Mysql replication with UPDATE JOIN on an ignored table
  8. SQL plages de dates [closed]
  9. How to find similar results and sort by similarity?
  10. Join tables from two different server
  11. Insert into multiple tables in one query
  12. Why does MySQL report a syntax error on FULL OUTER JOIN?
  13. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  14. Mixing ANSI 1992 JOINs and COMMAs in a query
  15. In Apache Spark 2.0.0, is it possible to fetch a query from an external database (rather than grab the whole table)?
  16. How to extract the nth word and count word occurrences in a MySQL string?
  17. How to delete a MySQL record after a certain time
  18. Exporting a table from Amazon RDS into a CSV file
  19. Force InnoDB to recheck foreign keys on a table/tables?
  20. Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT
  21. Let MySQL users create databases, but allow access to only their own databases
  22. Second SELECT query if first SELECT returns 0 rows
  23. How to restore to a different database in SQL Server?
  24. MySQL Error: #1142 – SELECT command denied to user
  25. MySQL: How do I use delimiters in triggers?
  26. MySQL connection over SSH tunnel
  27. QMYSQL driver available but not loaded
  28. UTF8 problem with MySQL 5
  29. Bind address and MySQL server [closed]
  30. How can I make a table in MySQL called “order”?

Leave a Comment