List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 19 1999 10:39am
Subject:Hunting a rogue query
View as plain text  
>>>>> "Jean-Francois" == Jean-Francois Dockes
> <jean-francois.dockes@stripped> writes:

Jean-Francois> Hello,
Jean-Francois> One of the queries that we run on our web site (musicmaker.com) is badly
Jean-Francois> constructed and uses very big temp files (in the 200 Mbytes range).
Jean-Francois> Needless to say, this has an adverse effect on the site response time
Jean-Francois> for other users, and it sometimes causes error when the temp space fills
Jean-Francois> up. 

Jean-Francois> When the problem occurs, we usually have one or several minutes to do
Jean-Francois> whatever would retrieve the query text, but we can't find the right way
Jean-Francois> to do it.

Jean-Francois> "mysqladmin proc" doesn't work for us because it truncates the text of  
Jean-Francois> the query, and we don't see the WHERE clause, where the interesting
Jean-Francois> stuff is.

Jean-Francois> We can't use a full log either because there are dozens of queries per
Jean-Francois> second, and it's very difficult to pinpoint the culprit.

Jean-Francois> By looking at the mysql source file sql/sql_show.cc, it seems that the
Jean-Francois> mysqld_list_processes() routine rather arbitrarily limits the displayed
Jean-Francois> text to 100 characters. 

Jean-Francois> Can this value be safely changed ?

Yes. I just changed the code in 3.23.3 with the following patch:

*** /my/monty/master/mysql-3.23.3-alpha/sql/sql_show.cc	Mon Aug 30 00:03:09 1999
--- ./sql_show.cc	Sun Sep 19 13:37:40 1999
***************
*** 599,605 ****
    field_list.push_back(new Item_empty_string("Time",7));
    field_list.push_back(field=new Item_empty_string("State",30));
    field->maybe_null=1;
!   field_list.push_back(field=new Item_empty_string("Info",100));
    field->maybe_null=1;
    if (send_fields(thd,field_list,1))
      DBUG_VOID_RETURN;
--- 599,605 ----
    field_list.push_back(new Item_empty_string("Time",7));
    field_list.push_back(field=new Item_empty_string("State",30));
    field->maybe_null=1;
!   field_list.push_back(field=new Item_empty_string("Info",PROCESS_LIST_WIDTH));
    field->maybe_null=1;
    if (send_fields(thd,field_list,1))
      DBUG_VOID_RETURN;
***************
*** 642,649 ****
  	if (tmp->query)
  	{
  	  uint length=strlen(tmp->query);
! 	  if (length > 100)
! 	    length=100;
  	  thd_info->query=(char*) sql_memdup(tmp->query,length+1);
  	  thd_info->query[length]=0;
  	}
--- 642,649 ----
  	if (tmp->query)
  	{
  	  uint length=strlen(tmp->query);
! 	  if (length > PROCESS_LIST_WIDTH)
! 	    length=PROCESS_LIST_WIDTH;
  	  thd_info->query=(char*) sql_memdup(tmp->query,length+1);
  	  thd_info->query[length]=0;
  	}


Add the patch and define PROCESS_LIST_WIDTH in mysql_priv.h to a value 
that is big enough for you!

Jean-Francois> Has anybody got a better idea ?

Jean-Francois> (PS: of course, the queries are dynamically constructed by bits and
Jean-Francois> pieces, in many different places, so that inspecting the code is not an
Jean-Francois> easy solution either).

Regards,
Monty
Thread
Hunting a rogue queryJean-Francois Dockes17 Sep
  • Re: Hunting a rogue querymatthew mcglynn17 Sep
  • Hunting a rogue queryMichael Widenius19 Sep