Je transmet depuis la liste user;
JLuc
-------- Original Message From: Philippe Hensmans <philippe@hensmans.org>
Depuis quelques temps, le serveur (dédié) qui nous héberge connaît des
coupures SQL. Notre hébergeur m'envoie le message suivant. Je cherche
donc à voir comment éviter ce problème... Des idées?
Merci d'avance...
J'ai vérifié votre serveur à plusieurs reprises lors de coupures SQL et j'ai fréquemment constaté la présence de plusieurs requêtes dans la db "blog", mais ceci est peut-être subjectif...
Voici les dernières lignes logguées concernant les requètes SQL longues, vous constaterez des requêtes retournant plusieurs milliers de lignes à chaque fois...
# Query_time: 5 Lock_time: 0 Rows_sent: 2578 Rows_examined: 11110
SET timestamp=1174421374;
SELECT articles.id_article, articles.lang
FROM `blog`.spip_articles AS `articles`
WHERE (articles.id_rubrique = '2')
AND (articles.statut = "publie")
AND (articles.date < NOW())
ORDER BY articles.titre;
# Time: 070320 21:09:40
# User@Host: amnestyinternati[amnestyinternati] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 2578 Rows_examined: 11110
SET timestamp=1174421380;
SELECT articles.id_article, articles.lang
FROM `blog`.spip_articles AS `articles`
WHERE (articles.id_rubrique = '2')
AND (articles.statut = "publie")
AND (articles.date < NOW())
ORDER BY articles.titre;
# Time: 070320 21:09:52
# User@Host: amnestyinternati[amnestyinternati] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 2578 Rows_examined: 11110
SET timestamp=1174421392;
SELECT articles.id_article, articles.lang
FROM `blog`.spip_articles AS `articles`
WHERE (articles.id_rubrique = '2')
AND (articles.statut = "publie")
AND (articles.date < NOW())
ORDER BY articles.titre;
/usr/sbin/mysqld, Version: 4.1.9-standard-log. started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 070320 21:13:57
# User@Host: amnestyinternati[amnestyinternati] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 2960 Rows_examined: 16131
use blog;
SELECT SUM( index.points * ( 1 +99 * ( index.hash
IN (16322453725967213292,9946270805639415912) ) ) ) AS points, index.id_table, index.id_objet, index.hash, SUM(FIELD(L1.type,'articles','documents')) AS cpt1
FROM `blog`.spip_types_tables AS `L1`, `blog`.spip_index AS `index`
WHERE ((hash IN (16322453725967213292,15727051636356755537,4424165656241277178,10986166391100042493,14515901088408559438,1352325084788897395,9946270805639415912)))
AND index.id_table=L1.id_table
GROUP BY index.id_table, index.id_objet, index.hash, CONCAT(index.id_table,':',index.id_objet)
HAVING (cpt1 <> 0)
ORDER BY cpt1;
# Time: 070320 21:14:22
# User@Host: amnestyinternati[amnestyinternati] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 11315 Rows_examined: 59792
SELECT SUM( index.points * ( 1 +99 * ( index.hash
IN (15860168820648813115,6440460935001836392,1166089425247988560,4763480195061959176) ) ) ) AS points, index.id_table, index.id_objet, index.hash, SUM(FIELD(L1.type,'articles','documents')) AS cpt1
FROM `blog`.spip_types_tables AS `L1`, `blog`.spip_index AS `index`
WHERE ((hash IN (15860168820648813115,14427235139787473644,10024369985339913790,93904560613210109,9970794697439375459,7145184498264267253,1224879440853719495,10244618272167696680,5609094992638492656,5333752026937716097,8990526223607429862,4642687900464734176,9871937937889298449,12606399509908612525,8834832206441488402,5445974150627280452,3951688576906231717,17896132080181884402,7697926376860206409,6802914259853567832,14711378217818421184,8343325949310205317,11119170010107262062,5067584379625002399,9596434642170418056,3299625775986916932,14020108833696129910,9236380621593509171,14702144241361573058,8682231304404728559,11907518284751021211,1111897689504380689,13081418628044418458,15543485252335842554,18170674397610427672,14497734905347063748,12594038087403916071,9118386370796964699,2642868410849990123,1834689073849948166,2304549927401597023,4090038737941838586,4310700353301167228,8131906423206389195,12680248883361396816,10163100013198837028,829055789690288583,2545067686249269619,6065631970144
996595,1329596763790331350,16884969048028447316,2764693200636595770,4071405243447564523,17159027895164181770,5499449911844889118,13006910169764317416,6440460935001836392,1166089425247988560,4213300898513071647,3755079255445402921,7901270293344409207,16088463713501103312,1423209552305762909,17724832401095825258,16487348911040939054,2231308586449750754,16505568037001359582,3555999645998392081,11859068865256525588,13169635147380791793,15077167869845432747,9205605111486967046,7866312140066637622,12399886537413683703,4763480195061959176,8431220505795782991,11791283345198972788,1301100609153538077,16241118624469061112,2681613186065924876,14763889748334163637,15512866861713862656,13002977193272118661,15850471484633464468,8084953566033955593,14619341748070084166,11661785767706808743)))
AND index.id_table=L1.id_table
GROUP BY index.id_table, index.id_objet, index.hash, CONCAT(index.id_table,':',index.id_objet)
HAVING (cpt1 <> 0)
ORDER BY cpt1;
# Time: 070320 21:16:52
# User@Host: amnestyinternati[amnestyinternati] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 36830
SELECT SUM( index.points * ( 1 +99 * ( index.hash
IN (3486220673238053218,13145615283803027734,8114830841247395335,6440460935001836392,16145556940998996016) ) ) ) AS points, index.id_table, index.id_objet, index.hash, SUM(FIELD(L1.type,'articles','documents')) AS cpt2
FROM `blog`.spip_types_tables AS `L1`, `blog`.spip_index AS `index`
WHERE ((hash IN (3486220673238053218,11519116491926474374,13145615283803027734,8114830841247395335,1667540960798500550,16943338892533638273,11324285580518431148,15107174001494139979,6440460935001836392,16145556940998996016,1688901414795664613,8894243552249585,3742245627134513968,50838244244092965,5898456718117672531,14459624906896007663,13850268264390164817,1870043690558980961,10302364001719383102,13950353115307293945,4173211681395498613,6251204841433882804,6377983926262082889,1300239178631339171,283074842384028947,9962639158789508027,4028516571235839132,800474816282020967,2031526190204424874,945512327095412567,8191868549916188543,1011627119829629240,10617349318867054901,2804118476712946428,8618750895096361124,13083086900717705721,5420606464768404718,9430529021343053157,8156352387606959346,10506239051997691308,11733775608405801468,12941271700018124297,16111179731649844488,1650075081010299212,15733675485983580208,15882277889542736215)))
AND index.id_table=L1.id_table
GROUP BY index.id_table, index.id_objet, index.hash, CONCAT(index.id_table,':',index.id_objet)
HAVING (cpt2 <> 0)
ORDER BY points DESC
LIMIT 0,1;
# User@Host: amnestyinternati[amnestyinternati] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 36830
SELECT SUM( index.points * ( 1 +99 * ( index.hash
IN (3486220673238053218,13145615283803027734,8114830841247395335,6440460935001836392,16145556940998996016) ) ) ) AS points, index.id_table, index.id_objet, index.hash, SUM(FIELD(L1.type,'articles','documents')) AS cpt2
FROM `blog`.spip_types_tables AS `L1`, `blog`.spip_index AS `index`
WHERE ((hash IN (3486220673238053218,11519116491926474374,13145615283803027734,8114830841247395335,1667540960798500550,16943338892533638273,11324285580518431148,15107174001494139979,6440460935001836392,16145556940998996016,1688901414795664613,8894243552249585,3742245627134513968,50838244244092965,5898456718117672531,14459624906896007663,13850268264390164817,1870043690558980961,10302364001719383102,13950353115307293945,4173211681395498613,6251204841433882804,6377983926262082889,1300239178631339171,283074842384028947,9962639158789508027,4028516571235839132,800474816282020967,2031526190204424874,945512327095412567,8191868549916188543,1011627119829629240,10617349318867054901,2804118476712946428,8618750895096361124,13083086900717705721,5420606464768404718,9430529021343053157,8156352387606959346,10506239051997691308,11733775608405801468,12941271700018124297,16111179731649844488,1650075081010299212,15733675485983580208,15882277889542736215)))
AND index.id_table=L1.id_table
GROUP BY index.id_table, index.id_objet, index.hash, CONCAT(index.id_table,':',index.id_objet)
HAVING (cpt2 <> 0)
ORDER BY points DESC
LIMIT 0,1;
# Time: 070320 21:16:55
# User@Host: amnestyinternati[amnestyinternati] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 36830
SELECT SUM( index.points * ( 1 +99 * ( index.hash
IN (3486220673238053218,13145615283803027734,8114830841247395335,6440460935001836392,16145556940998996016) ) ) ) AS points, index.id_table, index.id_objet, index.hash, SUM(FIELD(L1.type,'articles','documents')) AS cpt2
FROM `blog`.spip_types_tables AS `L1`, `blog`.spip_index AS `index`
WHERE ((hash IN (3486220673238053218,11519116491926474374,13145615283803027734,8114830841247395335,1667540960798500550,16943338892533638273,11324285580518431148,15107174001494139979,6440460935001836392,16145556940998996016,1688901414795664613,8894243552249585,3742245627134513968,50838244244092965,5898456718117672531,14459624906896007663,13850268264390164817,1870043690558980961,10302364001719383102,13950353115307293945,4173211681395498613,6251204841433882804,6377983926262082889,1300239178631339171,283074842384028947,9962639158789508027,4028516571235839132,800474816282020967,2031526190204424874,945512327095412567,8191868549916188543,1011627119829629240,10617349318867054901,2804118476712946428,8618750895096361124,13083086900717705721,5420606464768404718,9430529021343053157,8156352387606959346,10506239051997691308,11733775608405801468,12941271700018124297,16111179731649844488,1650075081010299212,15733675485983580208,15882277889542736215)))
AND index.id_table=L1.id_table
GROUP BY index.id_table, index.id_objet, index.hash, CONCAT(index.id_table,':',index.id_objet)
HAVING (cpt2 <> 0)
ORDER BY points DESC
LIMIT 0,1;
# Time: 070320 21:16:57
# User@Host: amnestyinternati[amnestyinternati] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 8519 Rows_examined: 45348
SELECT index.id_objet*(index.id_table=1) AS id_article, index.id_objet*(index.id_table=4) AS id_document, SUM( index.points * ( 1 +99 * ( index.hash
IN (3486220673238053218,13145615283803027734,8114830841247395335,6440460935001836392,16145556940998996016) ) ) ) AS points, index.id_table, index.id_objet, index.hash, SUM(FIELD(L1.type,'articles','documents')) AS cpt3
FROM `blog`.spip_types_tables AS `L1`, `blog`.spip_index AS `index`
WHERE ((hash IN (3486220673238053218,11519116491926474374,13145615283803027734,8114830841247395335,1667540960798500550,16943338892533638273,11324285580518431148,15107174001494139979,6440460935001836392,16145556940998996016,1688901414795664613,8894243552249585,3742245627134513968,50838244244092965,5898456718117672531,14459624906896007663,13850268264390164817,1870043690558980961,10302364001719383102,13950353115307293945,4173211681395498613,6251204841433882804,6377983926262082889,1300239178631339171,283074842384028947,9962639158789508027,4028516571235839132,800474816282020967,2031526190204424874,945512327095412567,8191868549916188543,1011627119829629240,10617349318867054901,2804118476712946428,8618750895096361124,13083086900717705721,5420606464768404718,9430529021343053157,8156352387606959346,10506239051997691308,11733775608405801468,12941271700018124297,16111179731649844488,1650075081010299212,15733675485983580208,15882277889542736215)))
AND index.id_table=L1.id_table
GROUP BY index.id_table, index.id_objet, index.hash, CONCAT(index.id_table,':',index.id_objet)
HAVING (cpt3 <> 0)
ORDER BY points DESC;