|
Peux tu faire : mysql> show profile ipc for query 5; > Date: Fri, 12 Mar 2010 17:49:31 +0100 > From: gregoire.coutant@gmail.com > To: debian-user-french@lists.debian.org > Subject: 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 > > -- > Lisez la FAQ de la liste avant de poser une question : > http://wiki.debian.org/fr/FrenchLists > > Pour vous DESABONNER, envoyez un message avec comme objet "unsubscribe" > vers debian-user-french-REQUEST@lists.debian.org > En cas de soucis, contactez EN ANGLAIS listmaster@lists.debian.org > Archive: http://lists.debian.org/4B9A709B.2040008@gmail.com > Commander un cadeau en ligne discrètement? Voir la solution offerte par Internet Explorer8 |