MySQL Forums
Forum List  »  Performance

Re: Poor performance of MySQL 5.5
Posted by: Rick James
Date: May 25, 2011 10:40AM

NUMA and HT are not deadly, just less than optimal. The benchmarks I have seen (sorry, don't have urls handy), show 32 cores, under stressful benchmarks, running had half the transactions per second as 8 core. The latest 5.5/5.6 and Percona show 32 cores running about as fast as 8.

NUMA is a curious solution to a tough architecture problem. But it seems to have two problems:
* Slow access to some 'other' memory because of having to leave this CPU, chatter with some other CPU, etc.
* Risk of swapping because of poor OS design for allocating memory. Fix this by telling the BIOS to allocate memory "round robin". (Or something like that.) Yes, that would seem to aggravate the other problem, but not really -- MySQL wants to use lots of RAM, so it has to go off-CPU anyway.

If NUMA caused unnecessary swapping, that might explain your entire problem.

Suggest you try two separate tests:
* Turn off HT. (Does that mean you have only 4 cores running?)
* Pin mysqld to one CPU, but allow all 8 cores within that CPU.

Oh, another note... Some very new version (5.6? Percona?) put a lot of effort into optimizing for Windows.

Sorry, I don't deal with actual benchmarking of servers, so I have none to recommend. Occasionally I hear of cases where a machine is twice as slow as one might expect based on comparing specs. Yours seems to be an anecdote feeding into this. Otherwise, I recommend ignoring CPU specs when picking a machine -- RAM and RAID, each, _can_ make a bigger difference (depending on dataset size).

What I find in my production experience is that a well tuned MySQL with well optimized queries rarely uses more than one core. That is, the benchmarks that slam multi-core boxes are artificial. A CPU that is full today, will meltdown tomorrow; the system is overdue for an overhaul.

Granted, none of what I say can adequately explain what you are seeing. I will be eager to hear if one of my hints doubles the speed.

If you would like to further tune you queries, let's see them. (SHOW CREATE TABLE, SHOW TABLE STATUS, EXPLAIN, etc.)

Options: ReplyQuote

Written By
May 23, 2011 04:43AM
May 24, 2011 08:14AM
Re: Poor performance of MySQL 5.5
May 25, 2011 10:40AM
May 31, 2011 09:03AM
June 03, 2011 09:33AM
June 03, 2011 07:13PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.