MySQL Forums
Forum List  »  Performance

Re: Help make my big new server fast
Posted by: Rick James
Date: October 07, 2010 10:01PM

Four cores on old box, 24 (12 real cores, w/hyperthreading) on new box. Old box 16GB RAM, new box 144GB RAM. -- Aha!

HyperThreading -- turn it off; the pairs of heads tend to clobber each other's caches.

Also, MySQL slows down after about 4 or 8 or 16 cores (depending on the benchmark and workload). This is a known problem and is somewhat addressed in the latest 5.1 and 5.5 for InnoDB.

"disabling the query cache" -- That was another case where MySQL was stumbling over itself due to a global mutex on the Query cache. It was so bad that even when you have query_cache_type = DEMAND and use SELECT SQL_NO_CACHE, it still took out the mutex. And with 24 threads doing this, none of them get very far very fast.

MyISAM stumbles over a mutex on the key_buffer, which it needs a lot. I don't know if having multiple key_buffers means multiple mutexes; if so, that might help a lot.

I have heard of one site getting well over 10K qps in InnoDB. And another (very limited) benchmark getting 180K qps. It may well be that switching to InnoDB will get your 12K, and more. But several different things need tuning differently, including your indexes.

"hitting only well-indexed DBs" -- don't forget that a table with N indexes has to update at least N+1 (for MyISAM) blocks on disk.

STATUS LIKE 'Key%' -- looks efficient.

"throttle the system down to only using 4 cores for MySQL" -- yes, but the details are buried in the OS. I would
1. turn off hyperthreading
2. go down to 8 cores.
3. If there is some way to set the affinity of the processes -- Tie MySQL to those 8 cores; tie other things to the rest.

Check to see if you are swapping. Yeah, even with 144GB. "NUMA" architecture (where some of the RAM is hooked to each CPU) seems to have spurious swapping in Linux. (I'm still learning the details of this nasty.)

LOCK TABLES -- and you got 8K qps? Impressive.

LOCK TABLES -- mostly turns into BEGIN & COMMIT. But there is more to change.

"yanking a processor to speed up your box just sounds counterintuitive" -- yeah, bummer.

What are you doing that needs 8K+ qps? What does the schema look like?

Options: ReplyQuote


Subject
Views
Written By
Posted
2885
October 04, 2010 11:31AM
957
October 05, 2010 07:27PM
Re: Help make my big new server fast
1418
October 07, 2010 10:01PM
1028
October 08, 2010 02:27PM
924
October 08, 2010 08:03PM
1442
October 08, 2010 09:14PM


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.