List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:October 10 2012 9:09am
Subject:Re: Query Cache Crashing
View as plain text  
Hi Rick,

Thanks for the advise.  I have now set my query-cache to zero.

(I take your point about query cache too large.  I understand that a 
smaller cache size, and the use of the SQL_NO_CACHE and SQL_CACHE 
directives can be used to control which queries are cached.  Therefore 
trying to get the advantage without the disadvantage.  But this is a lot 
of work to change every query we ever run!)

I am working on reproducing the error, but so far it appears to be random.

Ben



On 2012-10-09 18:44, Rick James wrote:
> As for the crash, I don't know.  Instead, I recommend either shrinking the size (if
> you use a big QC) or turning it off.  This would make the issue go away.
>
> ANY modification to a particular table leads to ALL entries in the Query cache being
> purged.  For that reason, we (Yahoo) almost never use the QC on any of our many servers.
>
> Please provide
> SHOW GLOBAL VARIABLES LIKE 'query%';
> SHOW GLOBAL STATUS LIKE 'Qc%';
>
> Because of inefficiencies in 'pruning', having a query_cache_size bigger than 50M may
> actually degrade performance.
>
> In you have a reproducible test case, submit to bugs.mysql.com .
>
>
>> -----Original Message-----
>> From: Ben Clewett [mailto:ben@stripped]
>> Sent: Tuesday, October 09, 2012 4:47 AM
>> To: mysql@stripped
>> Subject: Query Cache Crashing
>>
>> Hi MySql,
>>
>> Since upgrading to 5.5.27 (5.5.27-ndb-7.2.8-cluster-gpl-log to be
>> exact) I have experienced problems with the query cache.  I am
>> wondering if I am the only one?
>>
>> Twice I have had a core-dump (show at end of mail) on two separate
>> servers running this version.
>>
>> Now I had a complete lock, where 'SHOW PROCESSLIST' showed this for
>> every connection:
>>
>> "Waiting for query cache lock"
>>
>> This resulted in connections building until the limit was hit.  I could
>> only cure this with a 'kill -9' on the mysqld process.
>>
>> Are any other users experiencing this?  Do any users know of a fix?
>>
>> Thanks!
>>
>> Ben Clewett.
>>
>>
>> Thread pointer: 0x7f6ea014cf90
>> Attempting backtrace. You can use the following information to find out
>> where mysqld died. If you see no messages after this, something went
>> terribly wrong...
>> stack_bottom = 410220e8 thread_stack 0x40000
>> /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x81c7f5]
>> /usr/sbin/mysqld(handle_fatal_signal+0x403)[0x6e9383]
>> /lib64/libpthread.so.0[0x7f6f491b2b30]
>> /usr/sbin/mysqld(_ZN11Query_cache12unlink_tableEP23Query_cache_block_ta
>> ble+0x1b)[0x5bd1ab]
>>
>> /usr/sbin/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_b
>> lock+0x7a)[0x5bdb5a]
>>
>> /usr/sbin/mysqld(_ZN11Query_cache27invalidate_query_block_listEP3THDP23
>> Query_cache_block_table+0x7e)[0x5be8ee]
>>
>> /usr/sbin/mysqld(_ZN11Query_cache12insert_tableEjPcP23Query_cache_block
>> _tablejhPFcP3THDS0_jPyEy+0x91)[0x5bf7e1]
>>
>> /usr/sbin/mysqld(_ZN11Query_cache25register_tables_from_listEP10TABLE_L
>> ISTjP23Query_cache_block_table+0x176)[0x5bfa76]
>>
>> /usr/sbin/mysqld(_ZN11Query_cache19register_all_tablesEP17Query_cache_b
>> lockP10TABLE_LISTj+0x15)[0x5bfbb5]
>>
>> /usr/sbin/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x39e
>> )[0x5bff5e]
>>
>> /usr/sbin/mysqld[0x5eb02d]
>> /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x269b)[0x5f0aeb]
>> /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x18a)[0x5f2cba
>> ]
>> /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13
>> 21)[0x5f4861]
>>
>> /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x68d807]
>> /usr/sbin/mysqld(handle_one_connection+0x54)[0x68d874]
>> /lib64/libpthread.so.0[0x7f6f491ab040]
>> /lib64/libc.so.6(clone+0x6d)[0x7f6f4847c08d]
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql
>
>
Thread
Query Cache CrashingBen Clewett9 Oct
  • RE: Query Cache CrashingRick James9 Oct
    • Re: Query Cache CrashingBen Clewett10 Oct