From: Date: July 3 2008 9:27pm Subject: Re: mutex contention for the query cache List-Archive: http://lists.mysql.com/internals/35788 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On Thu, Jul 3, 2008 at 11:58 AM, Rick James wrote: >> A hint for me has been high idle time reported by vmstat for workloads >> that should be CPU bound on multi-core servers. That has been the case >> for Linux 2.6 on 8 core servers. > > I continue to run into anecdotal evidence saying: > * HyperThreading slows down MySQL; turn it off. > * 8-core Linux box runs slower than 4-core; turn off 4 cores. > * "My db is melting down". But the stats show low Disk usage, cpu > never above 12%. This is on 8-core box. (12% ~= 1/8, as if ONLY ONE > cpu is being used) > * "MySQL does a poor job on inter-cpu contention" There is a lot of work to be done before MySQL provides reasonable scalability beyond 4 cores. And in many cases, the same workload running on more than 4 cores is slower than running on 4 cores. I don't expect linear scalability, but negative or no scalability is disappointing. Running SHOW MUTEX STATUS will help you determine whether there is excessive contention for InnoDB mutexes. I have non-anecdotal evidence that: * doing a parallel reload of a database using InnoDB is slower on 8 cores with 8 concurrent sessions than on 4 cores with 4 concurrent sessions, even with our SMP patch, because there is a bottleneck that we have yet to fix * doing many concurrent joins on multi-core with MyISAM can be very slow because of the global MyISAM key cache mutex * doing many concurrent joins on multi-core with InnoDB can get slower with 8 cores because of mutex contention -- but our pending patch should fix much of this The hot spots are: 1) global mutex on query cache 2) global mutex on MyISAM key cache 3) global mutex on InnoDB memory allocator 4) inefficient rw-mutex in InnoDB 5) contention on transaction log mutex in InnoDB If you want to run well on SMP beyond 4 cores * don't use the query cache to fix 1) * limit your use of MyISAM or switch to Maria to fix 2) * get the Google patch when it is released to fix 3) and 4) * hope for another Google patch to fix 5) > > Sorry, I don't yet have enough evidence to distinguish these possible > variables: > * 4.1 or 5.1 (rather current versions of either) > * RHEL 4u4 or 4u6 > * 32-bit or 64-bit > * MyISAM or InnoDB 5.0 has a few important fixes for SMP in InnoDB, so it should be better than 4.X, but InnoDB in 5.0 uses more CPU because of support for the new row format. And MySQL 5 in general used a lot more CPU for parsing until 5.0.54. I don't know about RHEL versions, but if one of them doesn't use NPTL then that will have much more overhead on SMP. > > I see that Venu has talked to you: > http://venublog.com/2008/06/04/innodb-performance-on-4-and-8-core-cpu/ > > Do you have any further comments? We stress MySQL a lot here. And some > groups abandon MySQL for the big O. I would like to make use of 8 > cores, or help them otherwise get the most out of their MySQL box. > I would like to see SMP performance as the #1 priority for MySQL. It may be now, but the fix is to give us new stuff (Maria, Falcon) rather than to fix the old stuff. -- Mark Callaghan mdcallag@stripped