From: Date: July 3 2008 8:45pm Subject: Re: mutex contention for the query cache List-Archive: http://lists.mysql.com/internals/35782 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:17 AM, Rick James wrote: > Any clue of how often a thread cannot get to the query cache on first > try? > That is easy to measure for InnoDB -- SHOW MUTEX STATUS lists sleep() calls per mutex. Alas, other code above storage engines provides very little status on internal locks (contention, lock holders, lock waiters). 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. > > Rick James > MySQL Geeks - Consulting & Review > > > >> -----Original Message----- >> From: MARK CALLAGHAN [mailto:mdcallag@stripped] >> Sent: Thursday, July 03, 2008 10:45 AM >> To: internals@stripped >> Subject: Re: mutex contention for the query cache >> >> On Thu, Jul 3, 2008 at 10:28 AM, MARK CALLAGHAN >> wrote: >> > Things have changed from 5.0.37 to 5.0.62. Someone added spin lock >> > behavior for non-windows platforms. Work is still done to initialize >> > the search key after the lock is obtained. But that work does not >> > include memory allocation. >> > >> > Any chance the inlined spin lock can get put into a class so we have >> > some chance of reuse? >> > >> >> And astute readers will notice this isn't a spin lock as it sleeps >> immediately after each failure to get the lock. >> >> > From Query_cache::send_result_to_client(): >> > >> > #ifdef __WIN__ >> > STRUCT_LOCK(&structure_guard_mutex); >> > #else >> > stop_time= my_clock()+(ulong)lock_time_treshold*CLOCKS_PER_SEC; >> > while ((lock_status= >> pthread_mutex_trylock(&structure_guard_mutex)) == EBUSY >> > && spin_count < spin_treshold >> > && new_time < stop_time) >> > { >> > spin_count++; >> > if (spin_count%5) >> > new_time= my_clock(); >> > my_sleep(0); >> > } >> > >> > if (lock_status != 0) >> > { >> > /* >> > Query cache is too busy doing something else. >> > Fall back on ordinary statement execution. We also mark this >> > query as unsafe to cache because otherwise this thread will >> > still be halted when the result set is stored to the cache. >> > */ >> > thd->lex->safe_to_cache_query= FALSE; >> > goto err; >> > } >> > #endif >> > >> > >> > On Thu, Jul 3, 2008 at 10:20 AM, MARK CALLAGHAN >> wrote: >> >> The query cache has a mutex that is locked while it is >> searched. This >> >> is not a spin lock, so many threads will go to sleep when there is >> >> contention. And it is made worse because work is done to create the >> >> search key in Query_cache::send_result_to_client() (work == memory >> >> allocation and other byte copying) after the mutex is locked. >> >> >> >> Are there plans to fix this? >> >> I don't have benchmark results (yet), but I am willing to bet that >> >> this is a problem. >> >> >> >> -- >> >> Mark Callaghan >> >> mdcallag@stripped >> >> >> > >> > >> > >> > -- >> > Mark Callaghan >> > mdcallag@stripped >> > >> >> >> >> -- >> Mark Callaghan >> mdcallag@stripped >> >> -- >> MySQL Internals Mailing List >> For list archives: http://lists.mysql.com/internals >> To unsubscribe: >> http://lists.mysql.com/internals?unsub=rjames@stripped >> >> > -- Mark Callaghan mdcallag@stripped