| Bug #21074 | Large query_cache freezes mysql server sporadically under heavy load | ||
|---|---|---|---|
| Submitted: | 14 Jul 2006 23:36 | Modified: | 12 Mar 2010 17:55 |
| Reporter: | Ananth Reddy | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S3 (Non-critical) |
| Version: | 5.0.27, 4.1.19-standard-log | OS: | Linux (RedHat 4) |
| Assigned to: | Kristofer Pettersson | CPU Architecture: | Any |
| Tags: | bfsm_2007_03_01, bfsm_2007_07_19, bfsm_2007_08_16, freeze, locks, query_cache | ||
[14 Jul 2006 23:36]
Ananth Reddy
[15 Jul 2006 12:56]
Valeriy Kravchuk
Thank you for a problem report. I do not think it is a bug. It looks like improper configuration. If your applcation does not benefit from query cache (all queries are different, or all tables are constantly updated), setting large query cache can lead to the behaviour you described. Please, read http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html. Anyway, if you still can repeat the behaviour, please, send the results of: SHOW STATUS LIKE 'Qcache%'; just after the freezing you described.
[18 Jul 2006 10:51]
Carsten Kosthorst
I can confirm this behaviour for 4.0.24_Debian-10sarge2 running on - you guessed it - Debian Sarge.
I am able to reproduce the behaviour that leads to the lock. Unfortunately, I am only able to provide two Python-Scripts:
--- mysql_create_and_fill_db.py start ---
import MySQLdb
host = '127.0.0.1'
user = 'root'
passwd = ''
# Create connection and cursor
conn = MySQLdb.connect(host, user, passwd, 'test')
cursor = conn.cursor()
# Create table
cursor.execute('drop table if exists mysql_test')
cursor.execute('CREATE TABLE `mysql_test` ( `id` INT( 10 ) UNSIGNED NOT NULL , INDEX ( `id` ) ) TYPE = MYISAM ;')
# Create some entries...
for x in xrange(1, 1000000):
cursor.execute('insert into mysql_test (id) values (%s)' % x)
# Close connection
conn.close()
--- mysql_create_and_fill_db.py start end ---
--- mysql_fill_query_cache.py start ---
import MySQLdb
import random
# MySQL-Parameters
host = '127.0.0.1'
user = 'root'
passwd = ''
# Create connection and cursor
conn = MySQLdb.connect(host, user, passwd, 'test')
cursor = conn.cursor()
# Fill the query cache with some random queries
random.seed()
for x in xrange(1, 1000000):
x = random.randint(1, 1000000)
cursor.execute('select id from mysql_test where id = %s' % x)
# Close connection
conn.close()
--- mysql_fill_query_cache.py end ---
query_cache_size is set to 512M, the other values are the standard Debian settings:
back_log 50
basedir /usr/
bdb_cache_size 8388600
bdb_log_buffer_size 0
bdb_home
bdb_max_lock 10000
bdb_logdir
bdb_shared_data OFF
bdb_tmpdir
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (March 5, 2005)
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1_de latin2 latin5 sjis swe7 tis620 ujis usa7 win1250 win1251ukr win1251
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /var/lib/mysql/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb DISABLED
have_crypt YES
have_innodb DISABLED
have_isam YES
have_raid YES
have_symlinkYES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_size 8388608
innodb_data_file_path
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir
innodb_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_table_locks ON
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language /usr/share/mysql/english/
large_files_support ON
license GPL
local_infileON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_optionsOFF
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 1024
pid_file /var/run/mysqld/mysqld.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 536870912
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /var/run/mysqld/mysqld.sock
sort_buffer_size 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack196608
tx_isolationREPEATABLE-READ
timezone CEST
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.24_Debian-10sarge2
version_comment Source distribution
version_compile_os pc-linux-gnu
wait_timeout28800
To reproduce this behaviour, execute the script 'mysql_create_and_fill_db.py'. This will create the table mysql_test within the database test and fill it with 1000000 rows. After that, execute the script 'mysql_fill_query_cache.py'. This will execute random queries on the table. Watch the script work and watch 'Qcache_queries_in_cache'. When the latter reaches approximately 400000, execute 'flush tables' or 'reset query cache'. This will prevent further queries from 'mysql_fill_query_cache.py' - or other queries entered at the MySQL prompt - to be executed. This can be verified through 'show processlist' (or running mytop).
Here's the output of "show status like 'Qc%'" when the system is locked:
mysql> show status like 'Qc%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_queries_in_cache | 365323 |
| Qcache_inserts | 436976 |
| Qcache_hits | 136956 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 162767552 |
| Qcache_free_blocks | 25957 |
| Qcache_total_blocks | 756606 |
+-------------------------+-----------+
8 rows in set (0.01 sec)
Flushing the tables took 51.71 seconds:
mysql> flush tables;
Query OK, 0 rows affected (51.71 sec)
During this time, no query is executed.
Please see also this thread from the MySQL forums:
http://forums.mysql.com/read.php?21,85701,85701#msg-85701
[31 Aug 2006 15:50]
Tomash Brechko
Valery, Slow 'flush tables' or 'reset query cache' reported in the last comment by Carsten Kosthorst is a subject of bug#21051 and the fix is pushed to 5.0.25 and 5.1.12. But the cause of the original report is different. The problem is known: freeing a single query is slow because of a linear search on a long list of free blocks, and the stack shows just that. You may set 'Verified'.
[27 Sep 2006 18:43]
Valeriy Kravchuk
Verified. See last comment from Tomash Brechko for the reason.
[20 Feb 2007 7:33]
Valeriy Kravchuk
Bug #26460 is a duplicate of this one.
[2 Mar 2007 20:20]
Valeriy Kravchuk
Look also at developer's comment in bug #21051: "Note however that the slowdown also happens when inserting into sorted list of free blocks, should be rewritten using balanced tree."
[27 Apr 2007 17:32]
Kristofer Pettersson
preliminary report: Partial invalidation of a large cache freezes all operations during the operation. There are some disagreement to whether this is a bug or a misuse of the query cache. * A fast patch is to use a trylock approach to time out query cache if flush is in progress. * Wiping the entire allocated memory block is also much faster for RESET QUERY CACHE, than invalidating each query. This might be considered a patch for certain user cases. * Changing the linked list into table-based trees and using a two phase invalidation: First mark each table as invalid with structural block and then use parallel threads to deallocate the tables and query cache. The N-M relationship between queries and tables might make this approach difficult.
[11 May 2007 18:30]
Sergei Golubchik
See also a comment in the bug#28382 (which is a duplicate of this bug)
[28 Jun 2007 19:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/29895 ChangeSet@1.2541, 2007-06-28 21:47:10+02:00, thek@adventure.(none) +10 -0 Bug#21074 Large query_cache freezes mysql server sporadically under heavy load Invaldating a subset of a sufficiently large query cache can take a long time. During this time the server is efficiently frozen and no other operation can be executed. This patch addresses this problem by moving the locks which cause the freezing and also by temporarily disable the query cache while the invalidation takes place.
[2 Jul 2007 11:09]
Konstantin Osipov
Also some early version of the patch were reviewed by Marc Alff
[2 Jul 2007 17:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/30108 ChangeSet@1.2541, 2007-07-02 19:14:48+02:00, thek@adventure.(none) +10 -0 Bug#21074 Large query_cache freezes mysql server sporadically under heavy load Invaldating a subset of a sufficiently large query cache can take a long time. During this time the server is efficiently frozen and no other operation can be executed. This patch addresses this problem by moving the locks which cause the freezing and also by temporarily disable the query cache while the invalidation takes place.
[5 Jul 2007 11:54]
Konstantin Osipov
Queued into 5.1 runtime
[13 Jul 2007 20:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/30920 ChangeSet@1.2528, 2007-07-14 00:18:12+04:00, kostja@bodhi.(none) +5 -0 A follow up after the patch for Bug#21074 - even though we now have exclusive name lock on the table name in mysql_rm_table_part2, we still should keep LOCK_open - some storage engines are not ready for locking scope change and assume that LOCK_open is kept. Still, the binary logging and query cache invalidation calls moved out of LOCK_open scope. Fixes some of the broken 5.1-runtime tests (tests break on asserts).
[13 Jul 2007 22:05]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/30927 ChangeSet@1.2529, 2007-07-14 02:04:48+04:00, kostja@bodhi.(none) +5 -0 A follow up after the patch for Bug#21074 - even though we now have exclusive name lock on the table name in mysql_rm_table_part2, we still should keep LOCK_open - some storage engines are not ready for locking scope change and assume that LOCK_open is kept. Still, the binary logging and query cache invalidation calls moved out of LOCK_open scope. Fixes some of the broken 5.1-runtime tests (tests break on asserts).
[16 Jul 2007 11:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/30957 ChangeSet@1.2533, 2007-07-16 15:57:20+04:00, kostja@bodhi.(none) +1 -0 A follow up after the fix for Bug#21074 - fix NDB tests breaking on asserts. The patch for Bug#21074 replaces acquisition of the global LOCK_open lock with exclusive locks on table names in such operations ad DROP TABLE and RENAME TABLE. Unfortunately, NDB internally assumes that LOCK_open is acquired and tries to release it. This dependency should be fixed by a separate (and significant in size) patch. For now we just satisfy it - after all, the original goal of the patch for Bug#21074 was to move query_cache_invalidate outside of the scope of LOCK_open, and we still can do that. This fixes some failing NDB tests in the runtime tree.
[17 Jul 2007 15:30]
Bugs System
Pushed into 5.1.21-beta
[25 Jul 2007 3:33]
Paul DuBois
Noted in 5.1.21 changelog. Under heavy load with a large query cache, invalidating part of the cache could cause the server to freeze (that is, to be unable to service other operations until the invalidation was complete).
[5 Aug 2007 11:29]
Norbert Tretkowski
Any plans to fix this in 5.0 too?
[13 Aug 2007 15:56]
James Day
Norbert, not yet decided. We'll see how well it works in 5.1 in beta first - in particular whether it breaks anything. The standard workaround is setting a query cache size that is closer to those the query cache was designed for, a maximum of perhaps 100M. Not a perfect workaround but it's usually good enough. Sometimes smaller sizes are needed. Longer term the query cache architecture needs to be improved to reflect the cache sizes that are now practical. That's on the to do list. James Day, Support Engineer, MySQL AB
[14 Aug 2007 13:53]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/32508 ChangeSet@1.2480, 2007-08-14 15:52:46+02:00, thek@adventure.(none) +1 -0 Bug #21074 Large query_cache freezes mysql server sporadically under heavy load Invaldating a subset of a sufficiently large query cache can take a long time. During this time the server is efficiently frozen and no other operation can be executed. This patch addresses this problem by setting a time limit on how long time a dictionary access request can take before giving up on the attempt. This patch does not work for query cache invalidations issued by DROP, ALTER or RENAME TABLE operations.
[28 Aug 2007 12:09]
MySQL Verification Team
testcase to show a problem every 5 minutes. For couple of mins, 0 queries happen.
Attachment: qc01.c (text/plain), 10.22 KiB.
[29 Aug 2007 14:11]
Konstantin Osipov
The patch is approved. Kristofer will work with Shane (support) to verify in production whether the patch solves the customer problem.
[3 Sep 2007 8:48]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/33574 ChangeSet@1.2480, 2007-09-03 10:47:24+02:00, thek@adventure.(none) +1 -0 Bug #21074 Large query_cache freezes mysql server sporadically under heavy load Invaldating a subset of a sufficiently large query cache can take a long time. During this time the server is efficiently frozen and no other operation can be executed. This patch addresses this problem by setting a time limit on how long time a dictionary access request can take before giving up on the attempt. This patch does not work for query cache invalidations issued by DROP, ALTER or RENAME TABLE operations.
[4 Sep 2007 15:49]
Kristofer Pettersson
The last change set is a 5.0 specific solution which is null merged into 5.1.
[7 Sep 2007 8:08]
Bugs System
Pushed into 5.1.23-beta
[7 Sep 2007 8:10]
Bugs System
Pushed into 5.0.50
[8 Oct 2007 11:58]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Documented bugfix in 5.0.50 changelog.
[1 Nov 2007 10:14]
Bugs System
Pushed into 6.0.4-alpha
[1 Nov 2007 10:16]
Bugs System
Pushed into 5.1.23-rc
[1 Nov 2007 10:17]
Bugs System
Pushed into 5.0.52
[10 Aug 2008 6:18]
Valeriy Kravchuk
Bug #33752 was marked as a duplicate of this one.
[9 Sep 2009 8:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82773 3048 Kristofer Pettersson 2009-09-09 Bug#39253 Large query cache still freezes server after fix for bug #21074 During a cache invalidation the server will be blocked on a mutex waiting for the query cache to finish. This patch reduces the time for this wait by making hash delete operations more efficient. @ sql/qc_hash.cc * Implemented new hash table based on chained hash entries and Jenkins hash function. - The hash entry is stored in the Query cache blocks to avoid internal memory allocations. - Delete is now a simple pointer operation instead of a linear search. - Hash search reorders hash entries by attempting to move the most accessed entry first in the chain. @ sql/qc_hash.h * Implemented new hash table based on chained hash entries and Jenkins hash function @ sql/sql_cache.cc * Implemented new hash table based on chained hash entries and Jenkins hash function - Replaced free_query() with dettach() and free_query_internal() @ sql/sql_cache.h * Implemented new hash table based on chained hash entries and Jenkins hash function @ sql/sql_plist.h * back port of double linked list from alazea tree.
[13 Nov 2009 19:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/90392 3192 Kristofer Pettersson 2009-11-13 Bug#39253 Large query cache still freezes server after fix for bug #21074 When the query cache is fragmented, the size of the free block lists in the memory bins grow which causes the query cache invalidation to become slow. Even if this is countered by defragmentation the hash_delete() operation still creates a bottle necks which can cause the server to hang for a short period of time depending on the size of the cache. This patch introduce a limit on the time the qc can block with a lock on SELECTs. Other operations which causes a change in the table data will still be blocked. @ sql/sql_cache.cc * Introduced a timeout value for the qc lock when entering send_result_to_client() and store_query() methods. @ sql/sql_cache.h * New signature for Query_cache::try_lock()
[20 Nov 2009 12:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/91071 3191 Kristofer Pettersson 2009-11-20 Bug#39253 Large query cache still freezes server after fix for bug #21074 This patch introduce a limit on the time the query cache can block with a lock on SELECTs. Other operations which causes a change in the table data will still be blocked. @ sql/sql_cache.cc * Introduced a timeout value for the qc lock when entering send_result_to_client() and store_query() methods. @ sql/sql_cache.h * New signature for Query_cache::try_lock()
[2 Dec 2009 8:04]
Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[3 Dec 2009 1:29]
Paul DuBois
Noted in 5.1.42 changelog. Setting report to NDI pending push to 5.6.x+.
[3 Dec 2009 2:32]
Paul DuBois
Removed the 5.1.42 changelog entry. I think the "push" message occurred because this bug is mentioned in the changeset comment, but really is associated with Bug#39253.
[16 Dec 2009 8:36]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:43]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:49]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[16 Dec 2009 15:29]
Paul DuBois
Noted in 5.5.1, 6.0.14 changelogs.
[12 Mar 2010 14:08]
Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:24]
Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:38]
Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
