List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:August 14 2009 12:44am
Subject:RE: Erratic query performance
View as plain text  
Hi Leo,

Also include the EXPLAIN SELECT ...;  output, and the SHOW CREATE TABLE table\G for each
table used in the query.

Have you considered that your query's execution time depends on other activity on the
server?  Have you tried skipping the cache ( SELECT SQL_NO_CACHE ... ) and see if you get
consistent times?  What about running this directly through the mysql cli?

Regards,
Gavin Towey

-----Original Message-----
From: Leo Siefert [mailto:lsiefert@stripped]
Sent: Thursday, August 13, 2009 1:10 PM
To: mysql@stripped
Subject: Erratic query performance

I have a moderate sized database set up and a program that allows users to create ad-hoc
queries into the data based on entries in a form, so that I, as the programmer, have
control over the actual construction of the queries and can do what is needed to optimize
queries. I also keep a log of all queries that are run so that I can easily see the exact
query that may have caused a problem.

For the most part, unless a query is quite complex, there is no problem with the response
time - from a few seconds up to a minute or two for more complex queries or one returning
very large result sets. Recently a seemingly very simple query has resulted in
unacceptably long processing times.

After playing around with the query in PhpMyAdmin I am totally perplexed as to what could
be causing the problem. Sometimes the query will execute in less than 30 seconds, but
other times it takes from 4 to 10 or more minutes. It never seems to complete in between
30 seconds and 4 minutes.

To try to isolate the problem today I did a lot of testing on an isolated server - nothing
on it but MySql and this database and no one but me has access to it.  Tried rearranging
the joins and eliminating one of the joins as well as everything else I could think of to
figure out what could be causing the issue. Through all of the testing I got consistent
results in the four minute range for all of the variations I tried - repeated attempts
with the same query varied by only a second or two.

Then I want back to my program and ran the original query on the "public" database - the
same place that the problem had been originally found and instead of timing out the
gateway (five minute limit) as it had done consistently over the past few days it ran it
successfully in about 20 seconds. I was able to repeat this many times both using the
program and by entrering the query into PhpMyAdmin. Still takes 4 minutes on the private
server, though.

A couple of hours later - shortly before starting this message - I tried again on the
public server and again the response time was under 30 seconds. Trying again now and it's
on its way to timing out again. Checked and there are no other processes running on the
server - volume is usually low as there are less than 100 users total.

Any ideas of what could be causing the varied response time on a simple query when
everything on the server appears to be identical from one run to another? Are there
settings that can be made on the server to tweak response time for a database/query like
this?

Here are stats on the files involved in the query and the actual query I am trying to run.
Note that the number of receipts with amount >= 10000 is very smal compared to the
total number of records.

Main: 900,000 records, 500 Mb (886,361 where recordtype = "INDIVIDUAL")
  Primary key: id (int)
Receipt: 4,500,000 records, 700 Mb (6,817 where amount <= 10000)
  Primary key: id (int)
  Indexed on: mainid (int)
Committee: 4,500 records, 600Kb (1,476 where party = "D")
  Primary key: id (int)
  Indexed on: Comm_id (varchar(6))

create temporary table tmp type = heap
select distinct 3 filterid, m.id, "" GroupLevel, 0 GroupCum
from main m
left join receipt r on m.id = r.mainid
left join campaccommon.committee c on r.comm_id = c.Comm_id
where
   recordtype = "INDIVIDUAL"
   and c.party = "D"
   and r.amount >= 10000

Returns 294 records.

Thanks for any insight you can offer.

 - Leo Siefert

The information contained in this transmission may contain privileged and confidential
information. It is intended only for the use of the person(s) named above. If you are not
the intended recipient, you are hereby notified that any review, dissemination,
distribution or duplication of this communication is strictly prohibited. If you are not
the intended recipient, please contact the sender by reply email and destroy all copies
of the original message.
Thread
Erratic query performanceLeo Siefert13 Aug
  • Re: Erratic query performanceDan Nelson13 Aug
  • RE: Erratic query performanceGavin Towey14 Aug
  • RE: Erratic query performanceUS Data Export14 Aug