Piwigo.org

plg · 2015-03-12 17:14:31

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.

sylvsteph · 2015-03-17 23:21:35

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)

sylvsteph · 2015-03-27 13:32:08

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;

sylvsteph · 2015-03-28 09:10:14

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)

sylvsteph · 2015-03-29 13:45:10

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

sylvsteph · 2015-03-31 07:16:23

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;

sylvsteph · 2015-03-31 07:17:57

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;

sylvsteph · 2015-03-31 07:19:27

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

Mascarille · 2015-03-31 17:32:28

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

sylvsteph · 2015-04-01 07:17:53

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)

sylvsteph · 2015-04-01 20:44:43

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.

flop25 · 2015-04-02 10:16:19

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

sylvsteph · 2015-04-02 23:15:47

Oooops. Désolée

plg · 2015-04-03 10:59:34

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

sylvsteph · 2015-04-12 14:52:43

C'est là: http://sylvsteph.piwigo.com/

Piwigo.org

Annonce

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

plg a écrit:

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

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

Re: Problème requêtes piwigo

Pied de page des forums