Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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 microsoft.com/downloads/en/… 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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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