let mysql use full resources

Question

We have a Windows server 2008 R2. It has 24GB RAM and 2.926 Mhz, 8 Core(s), 8 Logical Process.

We have a large MySQL database and it's taking a lot of time to execute some queries. But while running queries, I have observed it's NOT utilizing full resources. I am thinking that if I let MySQL utilize maximum resources on my server, it can reduce execution time.

How can I let my MySQL server utilize maximum resources in a healthy way?

Answer 1

before you do this - you need to make sure that the bottleneck isn't your disks. I know you state in your question that the mySQL server inst utilising all resources, but if you are just checking task manager, you are only seeing your CPU and RAM usage, which doesn't give a full picture.

Very often, the reason that MySQL doesn't appear to be hammering your server is that it is working as fast as the hard drives will allow it to... after all, the CPU can only process the info as fast as the hard drives can retrieve it and the hard drive is normally the slowest part of a machine due to its mechanics.

Open resource monitor (not just task manager as people generally do) and check that your disks aren't utilised 100% otherwise playing with your MySQL instance won't do anything.

If you can confirm that your drives aren't being fully utilised, then we can look at optimising MySQL. Probably the easiest and simplest way of doing this (without having to trawl through all the config files) is to use something like MySQL Tuner which is a freebie and will cut the time it takes to optimise massively.

It may even be worth running if your disks are being hammered to see if you can squeeze a little more out of them.

Lastly - I don't know what your ability to rebuild the box is - but I often see people build a SQL server in RAID 5. Raid 0+1 (stripe and mirror) is the fastest way to have mySQL running while retaining a level of redundancy on the disks, however, this requires a large number of disks to mirror on. It may also be worth investing in faster disks... 15l instead of 10k, or SSD if you have cash to burn.

Hope this helps.