From: Ben Clewett Date: October 10 2012 9:09am Subject: Re: Query Cache Crashing List-Archive: http://lists.mysql.com/mysql/228358 Message-Id: <50753B31.4080709@clewett.org.uk> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > >