Re: Analyse ralentissement transaction MySQL
Re,
Le 12/03/2010 15:16, vic ros a écrit :
mysql> set profiling=1;
mysql> ta requete sql;
mysql> show profiles;
regarde le numéro correspondant à ta query (par exemple 3)
mysql> show profile cpu for query 3;
mysql> show profile block io for query 3;
mysql> set profiling=0;
ca va te donner une piste.
En effet c'est intéressant :
mysql> SELECT * FROM content WHERE state = '1' AND id != '5865' AND id
!= '9110' ORDER BY hits DESC;
...
4992 rows in set (1 min 16,37 sec)
mysql> show profiles;
+----------+-------------+----------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query
|
+----------+-------------+----------------------------------------------------------------------------------------------+
| 1 | 0.00004100 | SELECT * FROM content WHERE state = '1' AND
id NOT IN ('5865','9110') ORDER BY hits DESC |
| 2 | 0.00001300 | SELECT DATABASE()
|
| 3 | 0.00026800 | show databases
|
| 4 | 0.00001300 | show tables
|
| 5 | 76.04841400 | SELECT * FROM content WHERE state = '1' AND
id NOT IN ('5865','9110') ORDER BY hits DESC |
+----------+-------------+----------------------------------------------------------------------------------------------+
5 rows in set (0,07 sec)
mysql> show profile cpu for query 5;
+--------------------------------+-----------+-----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+-----------+-----------+------------+
| (initialization) | 0.000003 | 0 | 0 |
| checking query cache for query | 0.000024 | 0 | 0 |
| checking permissions | 0.000003 | 0 | 0 |
| Opening tables | 0.000005 | 0 | 0 |
| System lock | 0.000003 | 0 | 0 |
| Table lock | 0.000015 | 0 | 0 |
| init | 0.000022 | 0 | 0 |
| optimizing | 0.000005 | 0 | 0 |
| statistics | 0.000037 | 0 | 0 |
| preparing | 0.00001 | 0 | 0 |
| executing | 0.000002 | 0 | 0 |
| Sorting result | 0.018519 | 0.004 | 0.016001 |
| Sending data | 76.029711 | 38.074379 | 19.865241 |
| end | 0.000009 | 0 | 0 |
| query end | 0.000003 | 0 | 0 |
| freeing items | 0.000008 | 0 | 0 |
| closing tables | 0.000009 | 0 | 0 |
| logging slow query | 0.000026 | 0 | 0 |
+--------------------------------+-----------+-----------+------------+
18 rows in set (0,03 sec)
mysql> show profile block io for query 5;
+--------------------------------+-----------+--------------+---------------+
| Status | Duration | Block_ops_in |
Block_ops_out |
+--------------------------------+-----------+--------------+---------------+
| (initialization) | 0.000003 | 0 |
0 |
| checking query cache for query | 0.000024 | 0 |
0 |
| checking permissions | 0.000003 | 0 |
0 |
| Opening tables | 0.000005 | 0 |
0 |
| System lock | 0.000003 | 0 |
0 |
| Table lock | 0.000015 | 0 |
0 |
| init | 0.000022 | 0 |
0 |
| optimizing | 0.000005 | 0 |
0 |
| statistics | 0.000037 | 0 |
0 |
| preparing | 0.00001 | 0 |
0 |
| executing | 0.000002 | 0 |
0 |
| Sorting result | 0.018519 | 0 |
0 |
| Sending data | 76.029711 | 0 |
0 |
| end | 0.000009 | 0 |
0 |
| query end | 0.000003 | 0 |
0 |
| freeing items | 0.000008 | 0 |
0 |
| closing tables | 0.000009 | 0 |
0 |
| logging slow query | 0.000026 | 0 |
0 |
+--------------------------------+-----------+--------------+---------------+
18 rows in set (0,03 sec)
Donc sending data qui prend 99% du temps...
Des idées sur comment analyser ça ? Surement qque chose qui coince entre
le serveur et la destination mais où ?
Un tracert vers le destinataire ne me retourne rien d'anormal, j'ai du
mal voir par quel bout prendre le problème.
Greg
Reply to: