Categorized | News

Optimising MySQL on Windows

Posted on 26 February 2004 by Demian Turner

I needed to do optimise some queries on a big dataset at work, so figured I’d export recent data to my windows development machine rather than tax the live servers.

Datadump: 1M rows, returned after lunch an hour later and only 70k rows had inserted 🙁

How To Optimise
By default a recent MySQL install on windows creates a my.ini file (not my.cnf) in c:/.  This file is practically empty and won’t do you much good.  So I overwrote it with the my-medium file supplied in the distro and altered two key values respective to the INNO DB table type I was using:

  • set-variable = innodb_buffer_pool_size=256M (this represent 50% of physical RAM on my machine)
  • innodb_flush_log_at_trx_commit=0 (this disables transaction logging that roughly doubles the resources required by MySQL)

Truncated the now 75k rows and reimported the data – whole million rows went in in under a minute!

nb: you may want to return to your default values after big imports, the above setting hoard a lot of RAM.

Bookmark and Share

Leave a Reply



Demian Turner's currently-reading book recommendations, reviews, favorite quotes, book clubs, book trivia, book lists



PHPkitchen recommends you also check out the following sites :

Accounting for Small Businesses

FreeAgent sign-up