Annonce

#16 2015-03-12 17:14:31

plg
Équipe Piwigo
Nantes, France, Europe
2002-04-05
12639

Re: Problème requêtes piwigo

Bonjour sylvsteph,

Est-ce que tu es passé sur la version 2.7 ? je pense que oui (car jQuery 1.11.1) donc ça m'étonne un peu.

Il y a combien d'albums ?

J'ai vu le cas d'un autre utilisateur sur Infomaniak cette semaine. Son Piwigo était franchement lent (environ 10k albums). Je lui ai proposé de passer sur Piwigo.com, mais bon si on peut comprendre ce qui ne va pas et améliorer globalement les perfs quelque soit l'hébergeur c'est mieux !

Tu peux nous montrer tes slowlogs ? c'est peut-être des requêtes SQL différentes de la dernière fois.


Les historiens ont établi que Pierrick était le premier utilisateur connu de Piwigo.

Hors ligne

#17 2015-03-17 23:21:35

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Bonjour,

J'ai la version 2.7.4

Il y a 24 albums et plusieurs centaines de sous-albums. 98567 photos

J'ai fait une maintenance dans piwigo, mais j'obtiens un message d'erreur que j'ai mis dans ce post:
http://fr.piwigo.org/forum/viewtopic.php?id=26083

Sylvie

Dernière modification par sylvsteph (2015-04-02 08:05:39)

Hors ligne

#18 2015-03-27 13:32:08

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

La dernière requête:

# Time: 150326 19:07:55
# User@Host: ...[...] @ localhost []
# Query_time: 5.371920  Lock_time: 0.000508 Rows_sent: 195  Rows_examined: 154436
use basedonnees;
SET timestamp=1427393275;
SELECT CONCAT_WS('-',YEAR(date_creation),MONTH(date_creation),DAYOFMONTH(date_creation)) AS period FROM piwigo..._images
INNER JOIN piwigo..._image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0)
AND date_creation IS NOT NULL
GROUP BY period;

L'avant-dernière:

# Time: 150324 12:00:14
# User@Host: ...[...] @ localhost []
# Query_time: 8.953652  Lock_time: 0.000066 Rows_sent: 125  Rows_examined: 196843
use basedonnees;
SET timestamp=1427194814;
SELECT id
  FROM piwigo..._images
  WHERE level <= 0
  ORDER BY RAND() LIMIT 125;

Hors ligne

#19 2015-03-28 09:10:14

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Et ce matin:

# Time: 150327  8:12:38
# User@Host: ...[...] @ localhost []
# Query_time: 6.133338  Lock_time: 0.000158 Rows_sent: 7  Rows_examined: 201579
use basedonnees;
SET timestamp=1427440358;
SELECT DISTINCT(WEEKDAY(date_creation)) as period,
  COUNT(DISTINCT id) as nb_images FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0) AND WEEK(date_creation, 5)+1=14
  GROUP BY period;
# Time: 150327  9:51:28
# User@Host: ...[...] @ localhost []
# Query_time: 5.892173  Lock_time: 0.000158 Rows_sent: 7  Rows_examined: 202265
use basedonnees;
SET timestamp=1427446288;
SELECT DISTINCT(WEEKDAY(date_creation)) as period,
  COUNT(DISTINCT id) as nb_images FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0) AND WEEK(date_creation, 5)+1=48
  GROUP BY period;
# Time: 150327 10:12:14
# User@Host: ...[...] @ localhost []
# Query_time: 8.173675  Lock_time: 0.000122 Rows_sent: 97665  Rows_examined: 392411
use basedonnees;
SET timestamp=1427447534;
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    1=1
AND (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0)
  ORDER BY file ASC, date_creation ASC, id ASC;
# Time: 150327 11:42:38
# User@Host: ...[...] @ localhost []
# Query_time: 5.031549  Lock_time: 0.000120 Rows_sent: 7  Rows_examined: 202265
use basedonnees;
SET timestamp=1427452958;
SELECT DISTINCT(WEEKDAY(date_creation)) as period,
  COUNT(DISTINCT id) as nb_images FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0) AND WEEK(date_creation, 5)+1=48
  GROUP BY period;
# Time: 150327 12:11:32
# User@Host: ...[...] @ localhost []
# Query_time: 12.197546  Lock_time: 0.000136 Rows_sent: 7  Rows_examined: 201579
use basedonnees;
SET timestamp=1427454692;
SELECT DISTINCT(WEEKDAY(date_creation)) as period,
  COUNT(DISTINCT id) as nb_images FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0) AND WEEK(date_creation, 5)+1=14
  GROUP BY period;
# Time: 150327 16:17:19
# User@Host: ...[...] @ localhost []
# Query_time: 7.975149  Lock_time: 0.000131 Rows_sent: 195  Rows_examined: 154436
use basedonnees;
SET timestamp=1427469439;
SELECT CONCAT_WS('-',YEAR(date_creation),MONTH(date_creation),DAYOFMONTH(date_creation)) AS period FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0)
AND date_creation IS NOT NULL
GROUP BY period;
# Time: 150327 17:24:18
# User@Host: ...[...] @ localhost []
# Query_time: 7.468749  Lock_time: 0.000383 Rows_sent: 6646  Rows_examined: 210373
use basedonnees;
SET timestamp=1427473458;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0)
   AND WEEKDAY(date_creation)=3
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;
# Time: 150327 19:04:27
# User@Host: ...[...] @ localhost []
# Query_time: 6.269664  Lock_time: 0.000536 Rows_sent: 11646  Rows_examined: 220373
use basedonnees;
SET timestamp=1427479467;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1488,1553,1571,1576,1577) AND level<=0)
   AND WEEKDAY(date_creation)=6
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;

Dernière modification par sylvsteph (2015-03-28 09:11:12)

Hors ligne

#20 2015-03-29 13:45:10

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Pas de slowlog pour hier. Hier matin, j'ai renommé tout, base de donnée et préfixe des tables.

Hors ligne

#21 2015-03-31 07:16:23

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Les slowlogs d'hier:

# Time: 150329 14:05:36
# User@Host: Nom[Nom] @ localhost []
# Query_time: 6.031478  Lock_time: 0.000106 Rows_sent: 1052  Rows_examined: 199185
use nomdedomaine;
SET timestamp=1427630736;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
   AND WEEK(date_creation, 5)+1=19
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;
# Time: 150329 16:24:55
# User@Host: Nom[Nom] @ localhost []
# Query_time: 5.290752  Lock_time: 0.000164 Rows_sent: 195  Rows_examined: 154436
use nomdedomaine;
SET timestamp=1427639095;
SELECT CONCAT_WS('-',YEAR(date_creation),MONTH(date_creation),DAYOFMONTH(date_creation)) AS period FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
AND date_creation IS NOT NULL
GROUP BY period;
# Time: 150329 17:26:31
# User@Host: Nom[Nom] @ localhost []
# Query_time: 9.538354  Lock_time: 0.000138 Rows_sent: 208  Rows_examined: 197497
use nomdedomaine;
SET timestamp=1427642791;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
   AND WEEK(date_creation, 5)+1=24 AND WEEKDAY(date_creation)=5
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;
# Time: 150329 19:16:40
# User@Host: Nom[Nom] @ localhost []
# Query_time: 5.420723  Lock_time: 0.000094 Rows_sent: 751  Rows_examined: 198583
use nomdedomaine;
SET timestamp=1427649400;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
   AND WEEK(date_creation, 5)+1=22 AND WEEKDAY(date_creation)=5
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;
/opt/mysql/bin/mysqld, Version: 5.5.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 150329 20:40:54
# User@Host: Nom[Nom] @ localhost []
# Query_time: 12.034662  Lock_time: 0.000106 Rows_sent: 7792  Rows_examined: 212665
use nomdedomaine;
SET timestamp=1427654454;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
   AND WEEKDAY(date_creation)=1
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;
# Time: 150329 22:44:59
# User@Host: Nom[Nom] @ localhost []
# Query_time: 8.457435  Lock_time: 0.000155 Rows_sent: 297  Rows_examined: 197675
use nomdedomaine;
SET timestamp=1427661899;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
   AND WEEK(date_creation, 5)+1=29 AND WEEKDAY(date_creation)=2
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;

Hors ligne

#22 2015-03-31 07:17:57

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Les slowlogs ce matin:

# Time: 150330 11:30:30
# User@Host: Nom[Nom] @ localhost []
# Query_time: 16.031985  Lock_time: 0.000141 Rows_sent: 15  Rows_examined: 276338
use nomdedomaine;
SET timestamp=1427707830;
SELECT DISTINCT(id)
  FROM piwigo_images
    INNER JOIN piwigo_image_category AS ic ON id = ic.image_id
  WHERE hit > 0
    AND (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
     ORDER BY hit DESC, id DESC
  LIMIT 15;
# Time: 150330 11:30:58
# User@Host: Nom[Nom] @ localhost []
# Query_time: 9.744089  Lock_time: 0.000080 Rows_sent: 7307  Rows_examined: 211695
use nomdedomaine;
SET timestamp=1427707858;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
   AND WEEKDAY(date_creation)=2
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;
# Time: 150330 11:33:29
# User@Host: Nom[Nom] @ localhost []
# Query_time: 7.719280  Lock_time: 0.000102 Rows_sent: 137  Rows_examined: 154320
use nomdedomaine;
SET timestamp=1427708009;
SELECT CONCAT_WS('-','any',WEEK(date_creation, 5)+1,WEEKDAY(date_creation)) AS period FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
AND date_creation IS NOT NULL
GROUP BY period;
# Time: 150330 11:40:20
# User@Host: Nom[Nom] @ localhost []
# Query_time: 5.327435  Lock_time: 0.000212 Rows_sent: 0  Rows_examined: 235
use nomdedomaine;
SET timestamp=1427708420;
SELECT
    i.id as image_id,
    i.file as image_file,
    i.comment,
    i.path,
    c.id as catid,
    c.name,
    c.permalink,
    i.name as imgname

  FROM piwigo_categories c
    JOIN piwigo_image_category ic ON ic.category_id = c.id
    JOIN piwigo_images i ON i.id = ic.image_id
, piwigo_user_infos ui
          LEFT JOIN piwigo_favorites f ON ui.user_id=f.user_id
  WHERE i.id IN (33410,58742,87524,51826,72256,20584,90945,43962,424,25108,39645,74029,97665,95159,39512,18039,15570,42389,10772,46740,59374,18141,77120,32673,85217,73194,66260,1722,91501,72865,84262,33124,97115,57965,10422,29814,46444,29064,7083,69143,33066,9705,974,45778,18169,77358,4596,42626,36171,77552,57386,101372,32563,46530,57214,27165,95099,68418,72171,93335,93621,1964,49097,58449,95937,70418,81042,13904,54119,76765,53314,997,17155,40262,74830,36496,45322,43321,2849,22694,80552,42318,89706,28456,63502,98813,68337,49019,82379,2068,15103,54379,56294,1044,27324,56562,8046,22830,60408,56011,5766,90820,45357,66621,46787,58836,74418,74419,99559,33500,51805,83311,22726,99033,67042,96736,93644,77377,99125,98533,20434,66323,54985,31235,51437)
    AND i.level <= 0
AND c.id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577)  AND ui.status='webmaster'
                         AND f.image_id = i.id  ORDER BY RAND() LIMIT 25;
# Time: 150330 11:44:41
# User@Host: Nom[Nom] @ localhost []
# Query_time: 6.253812  Lock_time: 0.000142 Rows_sent: 31  Rows_examined: 154108
use nomdedomaine;
SET timestamp=1427708681;
SELECT CONCAT_WS('-','any','any',DAYOFMONTH(date_creation)) AS period FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
AND date_creation IS NOT NULL
GROUP BY period;
# Time: 150330 11:49:43
# User@Host: Nom[Nom] @ localhost []
# Query_time: 5.340066  Lock_time: 0.000154 Rows_sent: 6  Rows_examined: 204473
use nomdedomaine;
SET timestamp=1427708983;
SELECT DISTINCT(WEEKDAY(date_creation)) as period,
  COUNT(DISTINCT id) as nb_images FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0) AND WEEK(date_creation, 5)+1=33
  GROUP BY period;
# Time: 150330 13:03:21
# User@Host: Nom[Nom] @ localhost []
# Query_time: 9.056022  Lock_time: 0.000126 Rows_sent: 0  Rows_examined: 0
use nomdedomaine;
SET timestamp=1427713401;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
   AND date_creation IS NOT NULL
  ORDER BY date_creation DESC, file ASC, date_creation ASC, id ASC;
# Time: 150330 13:20:04
# User@Host: Nom[Nom] @ localhost []
# Query_time: 13.214245  Lock_time: 0.000110 Rows_sent: 20  Rows_examined: 135077
use nomdedomaine;
SET timestamp=1427714404;
SELECT DISTINCT(WEEK(date_creation, 5)+1) as period,
  COUNT(DISTINCT id) as nb_images FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0) AND date_creation BETWEEN '2011-01-01' AND '2011-12-31 23:59:59'
  GROUP BY period;
# Time: 150330 15:04:27
# User@Host: Nom[Nom] @ localhost []
# Query_time: 5.827881  Lock_time: 0.000073 Rows_sent: 1  Rows_examined: 2
use nomdedomaine;
SET timestamp=1427720667;
SELECT id,uppercats,commentable,visible,status,global_rank
  FROM piwigo_image_category
    INNER JOIN piwigo_categories ON category_id = id
  WHERE image_id = 34816
AND (id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577));
# Time: 150330 15:12:36
# User@Host: Nom[Nom] @ localhost []
# Query_time: 6.429501  Lock_time: 0.000226 Rows_sent: 0  Rows_examined: 235
use nomdedomaine;
SET timestamp=1427721156;
SELECT
    i.id as image_id,
    i.file as image_file,
    i.comment,
    i.path,
    c.id as catid,
    c.name,
    c.permalink,
    i.name as imgname

  FROM piwigo_categories c
    JOIN piwigo_image_category ic ON ic.category_id = c.id
    JOIN piwigo_images i ON i.id = ic.image_id
, piwigo_user_infos ui
          LEFT JOIN piwigo_favorites f ON ui.user_id=f.user_id
  WHERE i.id IN (68464,101652,43353,35741,69126,22772,38772,17717,22467,76556,84204,98597,66980,14313,101346,26362,37325,74575,43439,52095,74849,58140,57153,66626,23947,26061,76937,50767,69659,77285,72250,77909,69073,59298,58741,91482,37009,31962,52358,86536,95058,101625,47599,13488,37505,29321,56876,75177,37104,77098,26968,10727,40694,7107,12781,20487,14255,70687,34260,52955,17250,26240,62041,75481,85406,86650,71460,2132,98840,76425,59886,41260,7480,69370,86476,46834,4073,39073,86514,67328,91743,44321,91335,16325,62119,47402,71092,10536,82042,49836,101953,44032,81923,2698,6642,100491,46299,20474,41682,93219,38922,79079,98164,23035,86509,1079,57663,69051,17212,73161,29969,10913,89158,102095,10141,79032,17312,24669,35085,36610,99465,100431,23304,73994,41067)
    AND i.level <= 0
AND c.id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577)  AND ui.status='webmaster'
                         AND f.image_id = i.id  ORDER BY RAND() LIMIT 25;
# Time: 150330 15:18:57
# User@Host: Nom[Nom] @ localhost []
# Query_time: 13.308450  Lock_time: 0.000124 Rows_sent: 31  Rows_examined: 135077
use nomdedomaine;
SET timestamp=1427721537;
SELECT DISTINCT(DAYOFMONTH(date_creation)) as period,
  COUNT(DISTINCT id) as nb_images FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0) AND date_creation BETWEEN '2011-01-01' AND '2011-12-31 23:59:59'
  GROUP BY period;
# Time: 150330 17:14:18
# User@Host: Nom[Nom] @ localhost []
# Query_time: 10.247602  Lock_time: 0.000112 Rows_sent: 195  Rows_examined: 154436
use nomdedomaine;
SET timestamp=1427728458;
SELECT CONCAT_WS('-',YEAR(date_creation),MONTH(date_creation),DAYOFMONTH(date_creation)) AS period FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
AND date_creation IS NOT NULL
GROUP BY period;
# Time: 150330 17:27:28
# User@Host: Nom[Nom] @ localhost []
# Query_time: 5.619303  Lock_time: 0.091695 Rows_sent: 12  Rows_examined: 110345
use nomdedomaine;
SET timestamp=1427729248;
SELECT DISTINCT(DAYOFMONTH(date_creation)) as period,
  COUNT(DISTINCT id) as nb_images FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0) AND date_creation IS NOT NULL AND MONTH(date_creation)=1
  GROUP BY period;
# Time: 150330 17:31:16
# User@Host: Nom[Nom] @ localhost []
# Query_time: 6.878638  Lock_time: 0.000095 Rows_sent: 5  Rows_examined: 1025
use nomdedomaine12;
SET timestamp=1427729476;
SELECT s.*, t.* FROM tr56jhx00e_stories s, tr56jhx00e_topics t WHERE (s.published > 0 AND s.published <= 1427729469) AND (s.expired = 0 OR s.expired > 1427729469) AND (s.topicid=t.topic_id)  AND s.ihome=0 AND t.topic_frontpage=1 ORDER BY s.published DESC LIMIT 480, 5;
# Time: 150330 17:54:41
# User@Host: Nom[Nom] @ localhost []
# Query_time: 5.365051  Lock_time: 0.000277 Rows_sent: 0  Rows_examined: 0
use nomdedomaine;
SET timestamp=1427730881;
SELECT data
  FROM piwigo_sessions
  WHERE id = '42F988d5ef206ae736be7314963a72da573b';
# Time: 150330 19:33:30
# User@Host: Nom[Nom] @ localhost []
# Query_time: 6.032404  Lock_time: 0.000114 Rows_sent: 17817  Rows_examined: 152398
use nomdedomaine;
SET timestamp=1427736810;
SELECT DISTINCT id  FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
   AND date_creation BETWEEN '2012-01-01' AND '2012-12-31 23:59:59'
  ORDER BY date_creation ASC, file ASC, date_creation ASC, id ASC;
# Time: 150330 19:41:55
# User@Host: Nom[Nom] @ localhost []
# Query_time: 7.871919  Lock_time: 0.000153 Rows_sent: 195  Rows_examined: 154436
use nomdedomaine;
SET timestamp=1427737315;
SELECT CONCAT_WS('-',YEAR(date_creation),MONTH(date_creation),DAYOFMONTH(date_creation)) AS period FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
AND date_creation IS NOT NULL
GROUP BY period;
# Time: 150330 20:11:42
# User@Host: Nom[Nom] @ localhost []
# Query_time: 5.520841  Lock_time: 0.000061 Rows_sent: 0  Rows_examined: 1
use nomdedomaine;
SET timestamp=1427739102;
UPDATE
  piwigo_images
  SET hit = hit+1, lastmodified = lastmodified
  WHERE id = 51191;
# Time: 150330 22:06:21
# User@Host: Nom[Nom] @ localhost []
# Query_time: 9.109326  Lock_time: 0.000111 Rows_sent: 195  Rows_examined: 154436
use nomdedomaine;
SET timestamp=1427745981;
SELECT CONCAT_WS('-',YEAR(date_creation),MONTH(date_creation),DAYOFMONTH(date_creation)) AS period FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
    WHERE (category_id NOT IN (107,51,52,54,55,280,308,403,427,943,1232,1394,1553,1571,1576,1577) AND level<=0)
AND date_creation IS NOT NULL
GROUP BY period;

Hors ligne

#23 2015-03-31 07:19:27

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Bonjour,
Je n'ai pas gardé les slowlogs précédents. J'ai l'impression que c'est de pire en pire.
Merci d'avance si vous pouvez m'aider.
Sylvie

Hors ligne

#24 2015-03-31 17:32:28

Mascarille
Membre
Lyon Avignon
2009-12-21
799

Re: Problème requêtes piwigo

Bonjour,

je crois qu'il y a assez d'exemples.

Certaines requêtes me semblent très anodines et je suis surpris qu'elles ressortent comme pouvant générer des  slowslogs. J'ai moi même de grosses bases piwigo (60 000 images, 2 500 albums) et hormis deux problèmes spécifiques :
- à la création des vignettes après un gros chargement (mes photos sont en HD et "très lourdes")
- sur des requêtes "flat" des moteurs de recherche,
je n'ai pas de  ralentissement noté de ma machine. Il est vrai que je suis mon propre hébergeur et que j'ai une "bonne" machine dédiée.

J'ai aussi une grosse base chez piwigo.com (50 000 photos) et "mon hébergeur" ne m'a jamais tiré les oreilles.

Je me demande si votre hébergeur n'est pas un peu "maniak" ? Ou qu'il n'essaye pas de vous vendre un forfait plus conséquent.

Pour aller plus loin, il faudrait accéder à l'access log pour repérer les requêtes correspondant aux anomalies citées en utilisant l'horodatage fourni. Si ce sont des requêtes issues de moteurs de recherche, essayer de les réguler avec robot.txt

Sinon, l'amélioration des requêtes en cause entraîne une refonte partielle du noyau de piwigo... pas évident, d'autant que je n'ai pas vu beaucoup de messages dans ce forum qui se plaignent de ces problèmes. Une amélioration a d'ailleurs été apportée avec la 2.7.

En ce qui me concerne, j'ai installé plusieurs bases - essentiellement pour tester les maj majeures sur les bases que je considère comme... "les moins importantes" :

www.mascarille.com pour mon travail (théâtre et danse), www.mascarille.com/tdm pour les voyages.
Est-ce une solution pour vous ? on passe facilement de l'une à l'autre sans que les utilisateurs ne s'en rendent vraiment compte.

Bonne journée


Photographe spectacle vivant : www.mascarille.com
Voyages : www.mascarille.com/tdm
Base de connaissance sur le théâtre : www.mascarille.fr

Hors ligne

#25 2015-04-01 07:17:53

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Mon hébergeur est infomaniak.
J'ai installé un compteur piwik. Mes deux autres sites (principaux) sont visités mais le site photos qui est dans un sous-dossier destiné à la famille et aux amis a eu depuis samedi 6 visites et 46 pages vues, 9 visites et 16 pages vues , 3 visites et 7 pages vues hier.
J'ai mis le site en maintenance.

Dernière modification par sylvsteph (2015-04-01 07:18:15)

Hors ligne

#26 2015-04-01 20:44:43

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Bonjour,
J'ai ouvert un compte sur piwigo.com pour faire un test. Comment dois-je faire quand j'aurai sauvegardé ma base de données actuelle, pour la transférer sur le nouveau site piwigo.com? Quel est le nom de la base de données pour remplacer l'ancienne dans le fichier base, etc.
Y a-t-il un mode d'emploi?
Merci d'avance.
Sylvie

plg a écrit:

Bonjour sylvsteph,

Est-ce que tu es passé sur la version 2.7 ? je pense que oui (car jQuery 1.11.1) donc ça m'étonne un peu.

Il y a combien d'albums ?

J'ai vu le cas d'un autre utilisateur sur Infomaniak cette semaine. Son Piwigo était franchement lent (environ 10k albums). Je lui ai proposé de passer sur Piwigo.com, mais bon si on peut comprendre ce qui ne va pas et améliorer globalement les perfs quelque soit l'hébergeur c'est mieux !

Tu peux nous montrer tes slowlogs ? c'est peut-être des requêtes SQL différentes de la dernière fois.

Hors ligne

#27 2015-04-02 10:16:19

flop25
Équipe Piwigo
2006-07-06
6544

Re: Problème requêtes piwigo

bonjour
les questions concernant piwigo.com sont à psoer à eux directement merci

Hors ligne

#28 2015-04-02 23:15:47

sylvsteph
Membre
2010-05-22
164

Re: Problème requêtes piwigo

Oooops. Désolée

Hors ligne

#29 2015-04-03 10:59:34

plg
Équipe Piwigo
Nantes, France, Europe
2002-04-05
12639

Re: Problème requêtes piwigo

Piwigo en cours de transfert vers Piwigo.com, on va voir ce qui pose problème (ou pas, vu que Piwigo.com c'est tip top ;-)


Les historiens ont établi que Pierrick était le premier utilisateur connu de Piwigo.

Hors ligne

#30 2015-04-12 14:52:43

sylvsteph
Membre
2010-05-22
164

Hors ligne

Pied de page des forums

Propulsé par FluxBB

github twitter newsletter Faire un don Piwigo.org © 2002-2024 · Contact