List:General Discussion« Previous MessageNext Message »
From:Leo Siefert Date:August 13 2009 8:10pm
Subject:Erratic query performance
View as plain text  
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

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