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

2
  • What is the bottleneck? Nov 18 '13 at 20:29
  • I'm actually not quite sure, either it's the plugin maxing out or mysql. Since the plugin is not developed by us, we can only try to eliminate any mysql bottlenecks Nov 18 '13 at 21:00
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.

3
  • 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 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 :) Nov 23 '13 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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