Erratic query performance

--0-723848789-1250194205=:40800
Content-Type: text/plain; charset=us-ascii

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

--0-723848789-1250194205=:40800--

Re: Erratic query performance

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
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Erratic query performance

Hi Leo,

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

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

Regards,
Gavin Towey

-----Original Message-----
From: Leo Siefert [mailto:lsiefert@sbcglobal.net]
Sent: Thursday, August 13, 2009 1:10 PM
To: mysql@lists.mysql.com
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 qu=
eries that are run so that I can easily see the exact query that may have c=
aused a problem.

For the most part, unless a query is quite complex, there is no problem wit=
h the response time - from a few seconds up to a minute or two for more com=
plex 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 s=
erver - nothing on it but MySql and this database and no one but me has acc=
ess 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 causi=
ng the issue. Through all of the testing I got consistent results in the fo=
ur 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" d=
atabase - the same place that the problem had been originally found and ins=
tead of timing out the gateway (five minute limit) as it had done consisten=
tly over the past few days it ran it successfully in about 20 seconds. I wa=
s able to repeat this many times both using the program and by entrering th=
e query into PhpMyAdmin. Still takes 4 minutes on the private server, thoug=
h.

A couple of hours later - shortly before starting this message - I tried ag=
ain 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 th=
ere are less than 100 users total.

Any ideas of what could be causing the varied response time on a simple que=
ry when everything on the server appears to be identical from one run to an=
other? 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 >=3D 10000 is =
very smal compared to the total number of records.

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

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

Returns 294 records.

Thanks for any insight you can offer.

- Leo Siefert

The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Erratic query performance

>
>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?
>
[JS] Is it possible that there are locking conflicts? They can produce the
kind of behavior you are describing.

>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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org