Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am running a MySQL 5.0 currently on a single CPU on a windows 2003 machine. I'm thinking of adding another processor to increase performance, as some of the apps on the server are slowing down when database requests are made. Will MySQL be able to even the load across the two cpus?

share|improve this question
add comment

2 Answers 2

MySQL Server is most certainly multi-threaded. Each query, however is executed in a single thread. In the majority of cases, issues with "slowness" are not due to lack of processing power... but rather a lack of RAM and/or proper indexes. As queries are run, the system will cache the data in RAM... and as you run out of RAM, the OS starts using swap-space to compensate. Swap... is SLOW.

Solutions can include:

  • Throw more RAM at the box.
  • Set limits in my.cnf. This can also have side-effects where limits are too low to complete the tasks.
  • Optimize indexes. Sometimes adding indexes can save the system a lot of memory so it won't have to lookup the same values for each query... sometimes it can have the opposite effect where you're indexing fields that really don't need to be, and it just eats up RAM.

There are a HUGE number of variables to look at. There are entire degrees based on proper database-management. This is not a simple "flip-this-switch" question/answer. You should probably hire a consultant or someone who deals with these sorts of issues regularly.

*edit* The solutions I mentioned are in no particular order. There is no single magic-bullet that will fix your problems. Resolving your problems can involve all 3 I mentioned plus additional options. My list is incomplete, and can easily be added to. Memcaching, clustering, and many other options should be considered as well.

share|improve this answer
    
note that you suggestions are roughly in the reverse order of importance: 3: bad indexes/queries will be slow no mater what. 2: default my.cnf would use a small percentage of resources. 1: finally, trhow hardware at the problem –  Javier Sep 27 '12 at 14:30
    
All three are viable solutions to the problem. Sometimes it's cheaper/easier to simply throw more RAM into the pot than it is to spend hours debugging what indexes need to be added, or how they can be optimized... etc... I did, however, remove the ordered list and replaced with an unordered one... implying no weights on the solutions. –  TheCompWiz Sep 27 '12 at 14:36
    
Adding more ram won't do a thing if the config file limits it's use. Using more ram won't speed a query that drops to linear scanning because there's no useful index –  Javier Sep 27 '12 at 14:40
    
His issue is with performance over-time. We're not talking about starting a server and running a single slow query. As his system runs... it becomes slower. This suggests that there is insufficient physical memory, and then it swaps. RAM can certainly fix that problem. It should not be omitted. –  TheCompWiz Sep 27 '12 at 14:45
    
"some of the apps on the server are slowing down when database requests are made" to me that sounds that it used to be fast enough, and no longer it is. I'm sure the machine has been rebooted more than once since put into production, and if the performance was OK after each rebooting he would've mentioned it. To me this sounds like most queries were OK with little data, and now (presumably with more data) things are falling apart. In short, check indexes first –  Javier Sep 27 '12 at 17:31
add comment

I got good news and bad news for you

GOOD NEWS

MySQL Server is multithreaded

BAD NEWS

The InnoDB Storage Engine before MySQL 5.5 and MySQL 5.1's InnoDB Plugin (starting at MySQL 5.1.38) is not multithreaded.

SUGGESTION

Please upgrade to MySQL 5.5. Once you upgrade to MySQL 5.5 or Percona Server 5.x, you must still configure InnoDB for multiple core activity.

I have written about this dozens of times in the DBA StackExchange. Here are just a few:

From these posts, you will learn that InnoDB must be properly tuned to get it to access more cores.

share|improve this answer
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.