* kpettersson@stripped <kpettersson@stripped> [07/08/14 18:00]:
>
> ChangeSet@stripped, 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.
>
> sql/sql_cache.cc@stripped, 2007-08-14 15:52:44+02:00, thek@adventure.(none) +27 -1
> Changed mutex locking to a timed spinn lock. If access to query cache dictionary
> takes "a long time" (currently more than 0.1 seconds) the system fall backs on
> ordinary statement execution.
As discussed on IRC:
since this patch is for 5.0 only, Shane/Sinisa should perform acceptance
tests.
After that, the patch is OK to push into 5.0 and null-merge into
5.1.
Please see a minor comment below.
> diff -Nrup a/sql/sql_cache.cc b/sql/sql_cache.cc
> --- a/sql/sql_cache.cc 2007-05-10 14:54:53 +02:00
> +++ b/sql/sql_cache.cc 2007-08-14 15:52:44 +02:00
> @@ -1023,6 +1023,13 @@ Query_cache::send_result_to_client(THD *
> Query_cache_block_table *block_table, *block_table_end;
> ulong tot_length;
> Query_cache_query_flags flags;
> + const uint spin_treshold= 50000;
> + const double lock_time_treshold= 0.1; /* Time in seconds */
> + uint spin_count= 0;
> + int lock_status= 0;
> + ulong new_time= 0;
> + ulong stop_time= 0;
> +
> DBUG_ENTER("Query_cache::send_result_to_client");
>
> /*
> @@ -1069,7 +1076,26 @@ Query_cache::send_result_to_client(THD *
> }
> }
>
> - STRUCT_LOCK(&structure_guard_mutex);
> + 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)
You don't have to keep track/check for both, new_time and
spin_count - one of the parameters is enough.
> + {
> + spin_count++;
> + if (spin_count%5)
> + new_time= my_clock();
> + pthread_yield();
> + }
You could use my_sleep instead of spinning - but that's to be
decided based on Shane's tests.
> + if (lock_status != 0)
> + {
> + /*
> + Query cache is too busy doing something else.
> + Fall back on ordinary statement execution.
> + */
> + DBUG_RETURN(0);
> + }
--
-- Konstantin Osipov Software Developer, Moscow, Russia
-- MySQL AB, www.mysql.com The best DATABASE COMPANY in the GALAXY
