[prev in list] [next in list] [prev in thread] [next in thread]
List: mysql-internals
Subject: Re: sql_log_slow and executable comment version fun.
From: "Eric Bergen" <eric () provenscaling ! com>
Date: 2007-08-22 6:04:41
Message-ID: 28fd48f00708212304o3fb2e5d1j356a379ef640a45c () mail ! gmail ! com
[Download RAW message or body]
That's a good call Rick. I'll try to do that in a different patch.
Chad,
http://ebergen.net/patches/sql_log_slow.patch has been updated with
the rerecorded mysqldump test and a bug fix for an uninitialized
value. The mysqldump test was complaining about the new output from
setting the sql_log_slow viarable in it's output.
On 8/6/07, Rick James <rjames@yahoo-inc.com> wrote:
> As long as we are talking about wish lists... Plz include slow queries from
> the replication stream -- at least optionally.
>
> > -----Original Message-----
> > From: Chad MILLER [mailto:cmiller@mysql.com]
> > Sent: Monday, July 30, 2007 12:10 PM
> > To: Eric Bergen
> > Cc: internals@lists.mysql.com
> > Subject: Re: sql_log_slow and executable comment version fun.
> >
> > Hi Eric. Sorry for the delay.
> >
> > On 19 Jul 2007, at 00:48, Eric Bergen wrote:
> >
> > > The slow query log is a great tool for finding long running queries.
> > > One thing it doesn't do is allow you to not log known slow queries.
> > > Slow queries from things like importing large sql files. With
> > > long_query_time set low enough bulk insert queries from
> > dump files are
> > > also written out to the slow query log. The entire query is
> > written to
> > > the log. On a large dump file the log can quickly grow to gigs in
> > > size.
> > >
> > > This patch serves two purposes. First it adds a variable to
> > mysql that
> > > works exactly like sql_log_bIn in that it allows clients to prevent
> > > queries from being logged at the session level. Second it updates
> > > mysqldump to add this variable to it's dump output.
> >
> > I agree it could be better, and I like the general idea. Do you
> > really need a new variable? Could a session-level
> > (long_query_time =
> > 0 and log_queries_not_using_indexes = FALSE) do the same thing?
> >
> > In any case, I'll add a few minor notes to the patch, included below.
> >
> > > A possible side effect of this new variable output is that
> > it's tagged
> > > with a version number. In the old days this would be fine
> > since newer
> > > versions of mysql will know about the variable and act accordingly.
> > > However, with mysql enterprise it's possible that newer versions of
> > > enterprise will have a higher version number than what's in
> > output of
> > > mysql yet not support the new variable. Importing a dump
> > file created
> > > on a version of mysql with this patch then trying to import it on a
> > > newer version without the patch will error. What are the lists
> > > thoughts on how to handle this?
> >
> > Good catch. One way we're avoiding this problem is by adding
> > all new
> > features into the alpha (or development) tree as normal. With this
> > feature as an example, if it were ready now, it would go into 5.2.
> >
> > - chad
> >
> >
> > > diff -aur mysql-5.0.41-orig/client/mysqldump.c mysql-5.0.41/client/
> > > mysqldump.c
> > > --- mysql-5.0.41-orig/client/mysqldump.c 2007-06-24
> > > 19:58:29.000000000 -0700
> > > +++ mysql-5.0.41/client/mysqldump.c 2007-06-24 19:58:48.000000000
> > > -0700
> > > @@ -579,6 +579,7 @@
> > > ");
> > > }
> > > fprintf(sql_file,
> > > + "/*!50041 SET @SQL_LOG_SLOW=0 */;\n"
> > > "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,
> > SQL_MODE='%s%s%
> > > s' */;\n"
> > > "/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0
> > > */;\n",
> > >
> > > path?"":"NO_AUTO_VALUE_ON_ZERO",compatible_mode_normal_str[0]
> > > ==0?"":",",
> > > diff -aur mysql-5.0.41-orig/sql/mysql_priv.h mysql-5.0.41/sql/
> > > mysql_priv.h
> > > --- mysql-5.0.41-orig/sql/mysql_priv.h 2007-06-24
> > > 19:58:29.000000000 -0700
> > > +++ mysql-5.0.41/sql/mysql_priv.h 2007-06-24
> > 20:31:40.000000000 -0700
> > > @@ -344,6 +344,7 @@
> > > #define OPTION_TABLE_LOCK (ULL(1) << 21) // THD, intern
> > > #define OPTION_QUICK (ULL(1) << 22) // SELECT (for
> > > DELETE)
> > > #define OPTION_QUOTE_SHOW_CREATE (ULL(1) << 23) // THD, user
> > > +#define OPTION_LOG_SLOW (ULL(1) << 23) // THD, user
> >
> > (ITYM "24".)
> >
> > >
> > > /* Thr following is used to detect a conflict with DISTINCT
> > > in the user query has requested */
> > > diff -aur mysql-5.0.41-orig/sql/set_var.cc
> > mysql-5.0.41/sql/set_var.cc
> > > --- mysql-5.0.41-orig/sql/set_var.cc 2007-06-24
> > 19:58:29.000000000
> > > -0700
> > > +++ mysql-5.0.41/sql/set_var.cc 2007-06-24
> > 20:28:11.000000000 -0700
> > > @@ -95,6 +95,7 @@
> > > static bool set_log_update(THD *thd, set_var *var);
> > > static int check_pseudo_thread_id(THD *thd, set_var *var);
> > > static bool set_log_bin(THD *thd, set_var *var);
> > > +static bool set_log_slow(THD *thd, set_var *var);
> > > static void fix_low_priority_updates(THD *thd, enum_var_type type);
> > > static void fix_tx_isolation(THD *thd, enum_var_type type);
> > > static int check_completion_type(THD *thd, set_var *var);
> > > @@ -518,6 +519,10 @@
> > > check_log_update,
> > > set_log_bin,
> > > OPTION_BIN_LOG);
> > > +static sys_var_thd_bit sys_log_slowlog("sql_log_slow",
> > > + check_log_update,
> > > + set_log_slow,
> > > + OPTION_LOG_SLOW);
> > > static sys_var_thd_bit sys_sql_warnings("sql_warnings", 0,
> > > set_option_bit,
> > > OPTION_WARNINGS);
> > > @@ -667,6 +672,7 @@
> > > &sys_log_binlog,
> > > &sys_log_off,
> > > &sys_log_queries_not_using_indexes,
> > > + &sys_log_slowlog,
> > > &sys_log_update,
> > > &sys_log_warnings,
> > > &sys_long_query_time,
> > > @@ -2946,6 +2952,19 @@
> > > return 0;
> > > }
> > >
> > > +static bool set_log_slow(THD *thd, set_var *var)
> > > +{
> > > + DBUG_PRINT("info", ("Setting sql_log_slow to (%d)",
> > opt_slow_log));
> > > + if (opt_slow_log)
> > > + {
> > > + DBUG_PRINT("info", ("opt_slow_log detected doing or of (%d)
> > > \n", OPTION_LOG_SLOW));
> > > + ((sys_var_thd_bit*) var->var)->bit_flag|= OPTION_LOG_SLOW;
> > > + }
> >
> > That's awkward phrasing in the DBUG_PRINT.
> >
> > (And the indentation is weird in the assignment.)
> >
> > > +
> > > + set_option_bit(thd, var);
> > > + return 0;
> > > +}
> > > +
> > > static int check_pseudo_thread_id(THD *thd, set_var *var)
> > > {
> > > var->save_result.ulonglong_value= var->value->val_int();
> > > diff -aur mysql-5.0.41-orig/sql/sql_parse.cc mysql-5.0.41/sql/
> > > sql_parse.cc
> > > --- mysql-5.0.41-orig/sql/sql_parse.cc 2007-06-24
> > > 19:58:29.000000000 -0700
> > > +++ mysql-5.0.41/sql/sql_parse.cc 2007-06-24
> > 20:33:27.000000000 -0700
> > > @@ -2178,6 +2178,12 @@
> > > start_of_query= thd->start_time;
> > > thd->end_time(); // Set start time
> > >
> > > + if ((thd->options & OPTION_LOG_SLOW) == 0)
> > > + {
> > > + DBUG_PRINT("info", ("Found slow option not set, returning\n"));
> > > + return;
> > > + }
> > > +
> > > /*
> > > Do not log administrative statements unless the appropriate
> > > option is
> > > set; do not log into slow log if reading from backup.
> >
> > You're missing tests. I bet this changes the output of some tests,
> > also.
> >
> > - chad
> >
> > --
> > Chad Miller, Software Developer chad@mysql.com
> > MySQL Inc., www.mysql.com
> > Orlando, Florida, USA 13-20z, UTC-0400
> > Office: +1 408 213 6740 sip:6740@sip.mysql.com
> >
> >
> >
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=eric.bergen@provenscaling.com
>
>
--
high performance mysql consulting.
http://provenscaling.com
--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?unsub=mysql-internals@progressive-comp.com
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic