What database server will benefit from 4 CPUs

Question

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.

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 '12 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 '12 at 15:32
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 '12 at 15:34

score 14 · Accepted Answer · answered May 29 '12 at 15:12

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).

answered May 29 '12 at 15:12

Sven

98,649
14
180
226

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 '12 at 15:34
@jcubic, not intending to be rude but don't you think you should have done that *before* posting the question? – John Gardeniers May 30 '12 at 00: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 '12 at 16:40
@jcubic, assumption is the mother of all errors. As soon as you assume something you're already doing it wrong. – John Gardeniers May 30 '12 at 23:55

score 10 · Answer 2 · answered May 29 '12 at 15:13

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...

answered May 29 '12 at 15:13

voretaq7

79,879
17
130
214

1

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

score 5 · Answer 3 · edited Apr 13 '17 at 12:43

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.

What database server will benefit from 4 CPUs

3 Answers3