0

I use MySQL server on Debian GNU/Linux machine with 4 CPUs (Linode 4096) and there are performace issue on havy load (in the middle of the day) but machine is not overload only queries are slow.

What other database I can use that will use those 4 CPUs? I read that MySQL use only one. Is there SQL dababse or do I need to use NoSQL? Most queries are selects.

3
  • 7
    Barking up the wrong tree here. Best guess is this is nothing to do with CPU use and everything to do with disk I/O.
    – Joel Coel
    May 29, 2012 at 15:13
  • Not necessarily - the last Oracle database we taxes to the limit had 2 database servers with 48 physical threads each (24 cores + hyperthreading). So, a db server CAN use a LOT of CPU, especially in data warehousing. But you are correct that for THIS problem this extremely likely is bardking the wrong tree and totally ignoring what the server is busy doing.
    – TomTom
    May 29, 2012 at 15:32
  • 5
    That said, the query is typical insofar as the first thought seems to be "dump SQL, get a NoSql database, those are better" without understanding the problem. Hey, my car is broken - let's get a helicopter, cars obviously are not capable to get me from a to b ;)
    – TomTom
    May 29, 2012 at 15:34

3 Answers 3

14

You are asking the wrong question. Find out what the performance bottleneck is instead of searching for a database server that makes better use of the resource you obviously have enough of (CPU power).

4
  • 3
    And that likely is a comination of (A) slow / overloaded / baddisc subsystem (no, a 2tb disc is SLOW for database work, sorry) and (B) bad indexes that do not allow efficient query execution. For A - note a SSD is abou 100 TIMES Faster than a high end disc, and a high end disc 2-3 times faster than a 2TB end user disc. (40.000 IOPS, 450 IOPS, around 250 maximum).
    – TomTom
    May 29, 2012 at 15:34
  • @jcubic, not intending to be rude but don't you think you should have done that before posting the question? May 30, 2012 at 0:16
  • @JohnGardeniers mysql was taking only 12-14% of the CPU and half of the system memory was free, so I just assume that it didn't use all the power of the server, so that's why I ask. Server performance is not my thing I'm programmer, so that's why I search for help. I doesn't mean that I didn't search before I post.
    – jcubic
    May 30, 2012 at 16:40
  • @jcubic, assumption is the mother of all errors. As soon as you assume something you're already doing it wrong. May 30, 2012 at 23:55
10

I don't know where you read that MySQL use[s] only one [CPU], but your source is wrong, at least for any modern version of MySQL. Your performance problem is coming from something else. My money is on bad indexing design.

Try asking MySQL to EXPLAIN why it's slow...

1
  • 1
    +1....and how difficult would it have been for you to check this assertion for yourself, jcubic?
    – symcbean
    May 29, 2012 at 21:39
5

What is interesting about this question is the fact that InnoDB in MySQL 5.0 is single-threaded. There are new options in MySQL 5.1.38's InnoDB Plugin, MySQL 5.5 and Percona's XtraDB that actually has settings for making InnoDB use more CPUs.

I wrote about this in the DBA StackExchange:

Unfortunately, human nature makes us forget to fully configure database options. With due diligence, you can make InnoDB use multiple CPUs.

With regard to the question, all databases can benefit from 4 CPUs. In the cases of MySQL, the OS and buffer get more kudos unless you tune InnoDB.

If your database is all MyISAM, my condolences. 120 CPUs will not help MyISAM.

You must log in to answer this question.

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