Re: Query Cache Crashing
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:b...@clewett.org.uk] Sent: Tuesday, October 09, 2012 4:47 AM To: mysql@lists.mysql.com 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 0x4 /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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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 0x4 /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_table+0x1b)[0x5bd1ab] /usr/sbin/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_block+0x7a)[0x5bdb5a] /usr/sbin/mysqld(_ZN11Query_cache27invalidate_query_block_listEP3THDP23Query_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_LISTjP23Query_cache_block_table+0x176)[0x5bfa76] /usr/sbin/mysqld(_ZN11Query_cache19register_all_tablesEP17Query_cache_blockP10TABLE_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+0x1321)[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
RE: Query Cache Crashing
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:b...@clewett.org.uk] Sent: Tuesday, October 09, 2012 4:47 AM To: mysql@lists.mysql.com 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 0x4 /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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql