In the last episode (Aug 13), Leo Siefert said:
> 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.
What is the disk and CPU activity during this time? Does a second identical
query return quickly? Could be a caching effect. If the tables and indexes
aren't cached, the query will run slow, and once they are cached it runs
fast. If someone else runs a query that pushes the rows you're interested
in out of cache, it goes slow again. Do you have enough memory (and is
key_buffer_size set high enough) to cache all the indexes you are using?
"show status like 'key%'" before and after the query might be useful. Do
you have enough memory to cache all the tables as well?
An EXPLAIN of the query would be useful, just to verify that it is using the
indexes you expect. You can't explain a create table query, so just explain
the select part. If you're not selecting any fields from receipt (i.e.
it's just being used to join main and campaccommon), an index on (mainid,
comm_id) could let you bypass a lot of random table accesses.
> 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.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
