1

We're currently running a Wordpress site but need to import 10,000 - 20,000 posts per day from CSV files. Our current setup is an all-in-one LAMP server with 8GB RAM and 8 Virtual Cores. The import speed is quite slow, taking hours and hours.

My question is, will separating the MySQL server from the LAMP server yield any improvements in import speed? Our site isn't running slow or stalling during the import, it's just the import speed we want to improve.

Thanks

user2028856
  • 113
  • 2

1 Answers1

1
  • What table engine is being used? If InnoDB, try increasing the size of the double-write buffer, and importing in {fewer,a single} transaction(s) (and/or setting innodb_flush_log_at_trx_commit to something other than 1). If not InnoDB, switch! And set an appropriate innodb_buffer_pool_size.
  • Consider reformatting the data to be able to use MySQL's builtin LOAD DATA INFILE, which can be very speedy by comparison to bulk INSERTs.
  • Use extended insert syntax instead of line-by-line INSERTs if possible.

Once all these are done (or at least contemplated and rejected for good reasons), you'll likely be bottlenecked by actual machine parameters, instead of issues that can be resolved via MySQL tuning. At that point, moving onto a separate box makes sense. Making that jump any earlier is as likely to create problems (due e.g. to network latency) as it is to solve them.

BMDan
  • 7,249
  • 2
  • 23
  • 34
  • Yea the engine is innoDB right now. I think what I can do is implement step one. I can't really implement steps two and three because both the plugin and the CSV data aren't managed by myself. Thanks a lot for the information, it's really helped a lot. Btw, for our case do you think MyISAM is better or InnoDB is better in this case? Sorry for the noob question – user2028856 Nov 18 '13 at 21:39
  • On MySQL 5.5 or newer (or 5.1 with InnoDB Plugin), InnoDB is pretty much always better. On 5.6 or newer, it's truly always better. MyISAM is old and broken. Don't use it unless you truly must. Anyone saying otherwise is either going off of old information, has misconfigured (or not configured) their InnoDB tunables, or is a wizard. – BMDan Nov 22 '13 at 19:05
  • hahaha "or is a wizard" that's pretty good. Thanks for the information, now I'm definitely sticking with InnoDB :) – user2028856 Nov 23 '13 at 11:45