Skip navigation links

MySQL Forums :: Performance :: Poor performance of MySQL 5.5


Advanced Search

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


Subject Views Written By Posted
Poor performance of MySQL 5.5 7647 Matteo Canella 05/23/2011 04:43AM
Re: Poor performance of MySQL 5.5 2777 Rick James 05/24/2011 08:14AM
Re: Poor performance of MySQL 5.5 3028 Matteo Canella 05/25/2011 04:10AM
Re: Poor performance of MySQL 5.5 3151 Rick James 05/25/2011 10:40AM
Re: Poor performance of MySQL 5.5 2044 Merlotti Nicola 05/31/2011 07:15AM
Re: Poor performance of MySQL 5.5 1779 Rick James 05/31/2011 09:03AM
Re: Poor performance of MySQL 5.5 1595 Matteo Canella 05/31/2011 11:15AM
Re: Poor performance of MySQL 5.5 2111 Matteo Canella 06/03/2011 01:44AM
Re: Poor performance of MySQL 5.5 1300 Matteo Canella 06/03/2011 02:20AM
Re: Poor performance of MySQL 5.5 2138 Rick James 06/03/2011 09:33AM
Re: Poor performance of MySQL 5.5 1632 Nicola Merlotti 06/03/2011 11:25AM
Re: Poor performance of MySQL 5.5 2457 Matteo Canella 06/09/2011 05:36AM
Re: Poor performance of MySQL 5.5 1441 Nicola Merlotti 06/03/2011 11:38AM
Re: Poor performance of MySQL 5.5 1246 Rick James 06/03/2011 07:13PM
Re: Poor performance of MySQL 5.5 1181 Vladislav Vaintroub 06/11/2011 07:30PM
Re: Poor performance of MySQL 5.5 1073 Vladislav Vaintroub 06/11/2011 07:32PM
Re: Poor performance of MySQL 5.5 1354 Matteo Canella 06/12/2011 10:09AM
Re: Poor performance of MySQL 5.5 1129 Jerry Richart 06/15/2011 01:08PM


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.