Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm running Windows 7 (professional) with an intel Q8400 and MySQL 5.1. Whenever I have heavy MySQL queries (eg. inserting or updating 10,000-40,000 records, which takes several seconds) my UI stutters and freezes on and off. Applications are generally responsive (not frozen) but they don't do much until the SQL querying finishes.

In CPU usage, MySQL eats up an entire processer (25% in Task Manager) out of four; the other three are unused.

What can I do to make the performance better? These queries run for minutes sometimes, so it's a pretty major nuisance.

Edit: I have a single SATA HD (Samsung HD502HJ)

share|improve this question
If I had to guess, I'd say your HDD. – Daniel Beck Dec 10 '10 at 15:15
I have a Samsung HD502HJ (SATA, at least). How can I tell if it's HDD, and if it is, what might I be able to do about it (short of buying a new/replacement HDD)? – ashes999 Dec 10 '10 at 15:17
up vote 0 down vote accepted

Most likely this is due to a bottleneck in I/O. I'm assuming you're using either a single hard drive or a simple mirror. If either is the case, you don't have a disk subsystem in place that would provide enough throughput to support such large queries without interruption to other things running on the system.

Basically, unless you want to invest in a few more hard drives and a good RAID controller, you'll have to live with it. Large reads/writes are usually always disk-bound, not CPU-bound in modern machines.

Edit: In light of previous comments on your OP, there isn't much you can do about this with a single hard drive. Most servers that deal with large SQL queries have 6-8 hard drives in them. Best practices dictate that you have at least three physically separate sets of hard drives. One is for the OS, Swap, and application, a second is for the databases, and the third is for the transaction logs. This prevents multiple writes from individual commits happening on the same volume.

Basically, if you want to run MySQL on a desktop instead of a server and do large queries, there is no amount of tuning that you can do that will alleviate this bottleneck.

share|improve this answer
How can I verify if this is the case? I heard Windows 7 has some sort of "RAM drive" feature built-in; would I able to leverage that to alleviate the problem? – ashes999 Dec 10 '10 at 15:19
@ashes - No, you're probably talking about ReadyBoost. You can use something like… to test your max bandwidth and then watch the disk usage during your queries to see if it tops out where SQLIO says that it will, but I can almost guarantee that this is your issue without you even running it. Large SQL queries were not meant to be run on a system with a single hard drive in it. – MDMarra Dec 10 '10 at 15:23
Ok, I'll take your word for it. Disappointing that there's no solution, though. I'm using this machine fro dev, so it's not worth it for me to buy more HDDs. – ashes999 Dec 10 '10 at 15:27
@ashes - It's just the way it works. It still might be worthwhile to see what you have set for MAXCPU and run the benchmark though, just to be sure. – MDMarra Dec 10 '10 at 15:30
Hard drives are relatively cheap, but for RAID the motherboard would have to support it, or you would need a card, which adds up. – Bratch Dec 10 '10 at 15:40

To take advantage of the multiple processors, you should be able to create an application that splits the SQL statements and spread the work across multiple threads. This will allow the queries to essentially be inserted at 4 times the speed.

share|improve this answer
Not a bad idea... – ashes999 Dec 10 '10 at 20:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .