Slow query Performance

am 16.07.2009 00:44:03 von tachu1+mysql

--0016e6475d88c60b1d046ec64a78
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I'm having random query slowness that i can only reproduce once. My main
question is that the query runs faster the second time around but i dont
have query cache enabled here is some info from mysql profiler;

The time is spent mostly on the sending data step
first time around

63 rows in set (0.51 sec)

show profile all;
+--------------------+----------+----------+------------+--- ---------------=
-+---------------------+--------------+---------------+----- ----------+----=
---------------+-------------------+-------------------+---- ---+-----------=
------------+---------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary
| Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent |
Messages_received | Page_faults_major | Page_faults_minor | Swaps |
Source_function | Source_file | Source_line |
+--------------------+----------+----------+------------+--- ---------------=
-+---------------------+--------------+---------------+----- ----------+----=
---------------+-------------------+-------------------+---- ---+-----------=
------------+---------------+-------------+
| starting | 0.000165 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
NULL | NULL | NULL |
| Opening tables | 0.000033 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
open_tables | sql_base.cc | 4450 |
| System lock | 0.000020 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_lock_tables | lock.cc | 258 |
| Table lock | 0.000028 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_lock_tables | lock.cc | 269 |
| init | 0.000052 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_select | sql_select.cc | 2337 |
| optimizing | 0.000036 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
optimize | sql_select.cc | 762 |
| statistics | 0.000233 | 0.001000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
optimize | sql_select.cc | 944 |
| preparing | 0.000031 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
optimize | sql_select.cc | 954 |
| executing | 0.000017 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
exec | sql_select.cc | 1638 |
| Sending data | 0.504797 | 0.129980 | 0.012998 | 429
| 38 | 2456 | 64 | 0
| 0 | 0 | 0 | 0 |
exec | sql_select.cc | 2177 |
| end | 0.000054 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_select | sql_select.cc | 2382 |
| query end | 0.000023 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_execute_command | sql_parse.cc | 4799 |
| freeing items | 0.000063 | 0.000000 | 0.000999 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_parse | sql_parse.cc | 5805 |
| logging slow query | 0.000018 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
log_slow_statement | sql_parse.cc | 1608 |
| cleaning up | 0.000020 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
dispatch_command | sql_parse.cc | 1575 |
+--------------------+----------+----------+------------+--- ---------------=
-+---------------------+--------------+---------------+----- ----------+----=
---------------+-------------------+-------------------+---- ---+-----------=
------------+---------------+-------------+
15 rows in set (0.00 sec)

Second time around

63 rows in set (0.00 sec)

mysql> show profile
all;+--------------------+----------+----------+------------ +--------------=
-----+---------------------+--------------+---------------+- --------------+=
-------------------+-------------------+-------------------+ -------+-------=
----------------+---------------+-------------+|
Status | Duration | CPU_user | CPU_system | Context_voluntary |
Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent |
Messages_received | Page_faults_major | Page_faults_minor | Swaps |
Source_function | Source_file | Source_line |
+--------------------+----------+----------+------------+--- ---------------=
-+---------------------+--------------+---------------+----- ----------+----=
---------------+-------------------+-------------------+---- ---+-----------=
------------+---------------+-------------+
| starting | 0.000142 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
NULL | NULL | NULL |
| Opening tables | 0.000028 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
open_tables | sql_base.cc | 4450 |
| System lock | 0.000023 | 0.000000 | 0.001000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_lock_tables | lock.cc | 258 |
| Table lock | 0.000030 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_lock_tables | lock.cc | 269 |
| init | 0.000040 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_select | sql_select.cc | 2337 |
| optimizing | 0.000030 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
optimize | sql_select.cc | 762 |
| statistics | 0.000217 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
optimize | sql_select.cc | 944 |
| preparing | 0.000029 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
optimize | sql_select.cc | 954 |
| executing | 0.000018 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
exec | sql_select.cc | 1638 |
| Sending data | 0.000936 | 0.000999 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
exec | sql_select.cc | 2177 |
| end | 0.000026 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_select | sql_select.cc | 2382 |
| query end | 0.000020 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_execute_command | sql_parse.cc | 4799 |
| freeing items | 0.000046 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
mysql_parse | sql_parse.cc | 5805 |
| logging slow query | 0.000018 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
log_slow_statement | sql_parse.cc | 1608 |
| cleaning up | 0.000019 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
dispatch_command | sql_parse.cc | 1575 |
+--------------------+----------+----------+------------+--- ---------------=
-+---------------------+--------------+---------------+----- ----------+----=
---------------+-------------------+-------------------+---- ---+-----------=
------------+---------------+-------------+
15 rows in set (0.00 sec)

Any ideas how i can improve the performance of the query. the explain

explain select user_id,result_id from score where quiz_id=3D'495536' and
user_id in
(594939703,641833475,648583496,663932271,791002140,844089643 ,1014189359,101=
4996058,1021011357,1035297313,1043753292,1103136802,11070701 31,1114628916,1=
129457032,1133091309,1188705251,1211995704,1219452575,121963 1303,1239604246=
,1241474238,1266412488,1266549868,1288719892,1289732597,1317 205736,13460896=
61,1350738033,1354967647,1356046070,1369391720,1374076904,14 06156780,140730=
2487,1414151928,1425275210,1457839666,1466635900,1484315366, 1493410149,1536=
834812,1544094394,1572354290,1575139632,1578136049,158712953 4,1592996678,15=
94617334,1615538051,1615906710,1621733854,1622940529,1646693 120,1674002418,=
1684166314,1684535294,1701864533,1703227082,1711823847,17133 53427,173290386=
0,1752595138,1758240924,1813245914,1815724221,1839942291,100 000015406640);
+----+-------------+-------+-------+-----------------+------ ---+---------+-=
-----+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+-------+-----------------+------ ---+---------+-=
-----+------+-------------+
| 1 | SIMPLE | score | range | user_id,quiz_id | user_id | 12 |
NULL | 68 | Using where |
+----+-------------+-------+-------+-----------------+------ ---+---------+-=
-----+------+-------------+
1 row in set (0.02 sec)

UNIQUE KEY `user_id` (`user_id`,`quiz_id`),

--0016e6475d88c60b1d046ec64a78--

Re: Slow query Performance

am 16.07.2009 05:53:05 von Darryle steplight

Re: Slow query Performance

am 16.07.2009 16:08:03 von Don Read

On Wed, 15 Jul 2009 23:53:05 -0400 Darryle Steplight said:

> Can you show us the output of DESCRIBE score and SHOW INDEX FROM score?
>=20
> On Wed, Jul 15, 2009 at 6:44 PM, Tachu=AE wrote:
> > I'm having random query slowness that i can only reproduce once. My main
> > question is that the query runs faster the second time around but i dont
> > have ...

> >=20
>=20
> --=20
> A: It reverses the normal flow of conversation.
> Q: What's wrong with top-posting?
> A: Top-posting.
> Q: What's the biggest scourge on plain text email discussions?
>=20

Anybody else see the irony here?

--=20
Don Read don_read@att.net
It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.

--
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: Slow query Performance

am 16.07.2009 16:29:43 von Martin Gainty

--_4e9e422f-7cc5-4482-84bd-2a22da48d61a_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

when my.ini has query-cache-type =3D 1 setting
the query results are placed in cache on first read
second and consequent reads reference resultset from cache

http://www.databasejournal.com/features/mysql/article.php/31 10171/MySQLs-Qu=
ery-Cache.htm

Martin Gainty=20
Confucius say "Big Dog in door prevents newspaper from being stolen"
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9
=20
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes=
pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..

> Date: Thu=2C 16 Jul 2009 10:08:03 -0400
> From: don_read@att.net
> To: dsteplight@gmail.com
> CC: mysql@lists.mysql.com
> Subject: Re: Slow query Performance
>=20
> On Wed=2C 15 Jul 2009 23:53:05 -0400 Darryle Steplight said:
>=20
> > Can you show us the output of DESCRIBE score and SHOW INDEX FROM score?
> >=20
> > On Wed=2C Jul 15=2C 2009 at 6:44 PM=2C Tachu=AE=
wrote:
> > > I'm having random query slowness that i can only reproduce once. My m=
ain
> > > question is that the query runs faster the second time around but i d=
ont
> > > have ...
>=20
>
>=20
> > >=20
> >=20
> > --=20
> > A: It reverses the normal flow of conversation.
> > Q: What's wrong with top-posting?
> > A: Top-posting.
> > Q: What's the biggest scourge on plain text email discussions?
> >=20
>=20
> Anybody else see the irony here?
>=20
> --=20
> Don Read don_read@att.net
> It's always darkest before the dawn. So if you are going to
> steal the neighbor's newspaper=2C that's the time to do it.
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>=20

____________________________________________________________ _____
Insert movie times and more without leaving Hotmail=AE.=20
http://windowslive.com/Tutorial/Hotmail/QuickAdd?ocid=3DTXT_ TAGLM_WL_HM_Tut=
orial_QuickAdd_062009=

--_4e9e422f-7cc5-4482-84bd-2a22da48d61a_--

Re: Slow query Performance

am 16.07.2009 22:34:57 von Dan Nelson

In the last episode (Jul 15), Tachu(R) said:
> I'm having random query slowness that i can only reproduce once. My main
> question is that the query runs faster the second time around but i dont
> have query cache enabled here is some info from mysql profiler;
>
> The time is spent mostly on the sending data step
> first time around
>
> 63 rows in set (0.51 sec)
>
> Second time around
>
> 63 rows in set (0.00 sec)
>
> Any ideas how i can improve the performance of the query. the explain
>
> explain select user_id,result_id from score where quiz_id='495536' and
> user_id in (594939703, 641833475, 648583496, 663932271, 791002140,
> 844089643, 1014189359, 1014996058, 1021011357, 1035297313, 1043753292,
> 1103136802, 1107070131, 1114628916, 1129457032, 1133091309, 1188705251,
> 1211995704, 1219452575, 1219631303, 1239604246, 1241474238, 1266412488,
> 1266549868, 1288719892, 1289732597, 1317205736, 1346089661, 1350738033,
> 1354967647, 1356046070, 1369391720, 1374076904, 1406156780, 1407302487,
> 1414151928, 1425275210, 1457839666, 1466635900, 1484315366, 1493410149,
> 1536834812, 1544094394, 1572354290, 1575139632, 1578136049, 1587129534,
> 1592996678, 1594617334, 1615538051, 1615906710, 1621733854, 1622940529,
> 1646693120, 1674002418, 1684166314, 1684535294, 1701864533, 1703227082,
> 1711823847, 1713353427, 1732903860, 1752595138, 1758240924, 1813245914,
> 1815724221, 1839942291, 100000015406640);
>
> +----+-------------+-------+-------+-----------------+------ ---+---------+------+------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+-------+-------+-----------------+------ ---+---------+------+------+-------------+
> | 1 | SIMPLE | score | range | user_id,quiz_id | user_id | 12 | NULL | 68 | Using where |
> +----+-------------+-------+-------+-----------------+------ ---+---------+------+------+-------------+
> 1 row in set (0.02 sec)
>
> UNIQUE KEY `user_id` (`user_id`, `quiz_id`),

Try swapping those fields in the compound index. The way you have it, mysql
has to jump to each of the 68 user_id values in the index and see if one of
the quiz_ids is 495536. If you have an index on (quiz_id,user_id), mysql
only has to jump to the 495536 quiz_id section, and all the user_ids are all
right there.

That should cut your query time by 50% (since you still have to do 68 seeks
to the table rows to fetch result_id). If you also add result_id to your
compound index, then mysql will be able to get all its information from the
index without having to go to the table at all.

--
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