Besoin de passer à un dédié ? je n'en sais rien...

Archaos · 28 Mai 2013

Bonjour ,

Mon hébergeur me relance de plus en plus au niveau des slowlogs...(c'est détaillé ici : http://www.phpbb-seo.com/fr/premod-phpbb-seo/sujet6818.html ).

je me demandais si finalement mon forum n'était pas trop important pour du mutualisé (5000 à 7000 VU)

Si vous pouviez m'éclairer ?

Merci bien

Bool · 29 Mai 2013

Hello,

alors rapidement :

# Query_time: 7.123007 Lock_time: 0.000047 Rows_sent: 15 Rows_examined: 1845
use base;
SET timestamp=1368077263;
SELECT p.post_id
FROM phpbb3_posts p
WHERE p.topic_id = 13794
AND p.post_approved = 1
ORDER BY p.post_time ASC
LIMIT 1830, 15;

Oui ce type de pagination est parfois très lourd pour MySQL, mais en l'occurence 1845 lignes traitées c'est ridicule... le fait que le serveur mette 7 secondes à les traiter indique qu'il est soit mal configuré, soit complètement surchargé.

# Query_time: 6.670910 Lock_time: 0.000053 Rows_sent: 15 Rows_examined: 604
# Query_time: 12.395050 Lock_time: 0.000062 Rows_sent: 1 Rows_examined: 4173
# Query_time: 10.456030 Lock_time: 0.000060 Rows_sent: 1 Rows_examined: 5042
# Query_time: 8.255936 Lock_time: 0.000067 Rows_sent: 15 Rows_examined: 814
# Query_time: 8.596141 Lock_time: 0.000055 Rows_sent: 1 Rows_examined: 3917
# Query_time: 5.777073 Lock_time: 0.000545 Rows_sent: 15 Rows_examined: 779

idem pour toutes ces requêtes.

Bref, est-ce que ça tournerait mieux sur un dédié ? Très probablement. Mais un mutu de meilleure qualité pourrait probablement suffire également.

Histoire de donner quelques chiffres, quelques stats issues d'un Magento sur une petite VM :

# Query_time: 2.421690 Lock_time: 0.000149 Rows_sent: 0 Rows_examined: 24284
# Query_time: 0.121926 Lock_time: 0.000075 Rows_sent: 11 Rows_examined: 56921

Une autre petite VM, avec le code d'un client non optimisé :

# Query_time: 2.081434 Lock_time: 0.000208 Rows_sent: 22 Rows_examined: 15954996
# Query_time: 0.365872 Lock_time: 0.000368 Rows_sent: 10 Rows_examined: 311861
# Query_time: 0.331744 Lock_time: 0.000152 Rows_sent: 10 Rows_examined: 311884
# Query_time: 0.566391 Lock_time: 0.000103 Rows_sent: 1 Rows_examined: 153217

Bref, oui c'est lent, mais d'après moi le problème ne vient pas de phpBB ici.

Archaos · 29 Mai 2013

Bonjour Bool et merci pour ta réponse,

Vu ainsi c'est clair qu'il y a peut être un souci de leur coté, surtout que les plus gros ralentissements arrivent à partir de 8-9h, l'heure où les backups sql sont faits de leurs cotés (mais c'est peut être des coïncidences).

Je suis embêté car j'avais choisi cet hébergeur (infomaniak), il y a plusieurs années déjà, au vu des avis positifs et la possibilité d'avoir une base de donnée "illimitée".
L'alternative serait de passer sur une petite VM si je comprends ?

Bool · 29 Mai 2013

Je n'ai donné les chiffres de petites VM que pour apporter quelques chiffres. Un petit dédié (kimsufi) ou un hébergement mutualisé un peu mieux adapté (offre SQLPro d'OVH ?) pourrait probablement convenir aussi.

Julia41 · 29 Mai 2013

Moi vu le temps d'exec, je te dirais qu'il te manque des INDEX SQL...

T'as accès à l'output de :

Code:

EXPLAIN SELECT p.post_id FROM phpbb3_posts p WHERE p.topic_id = 13794 AND p.post_approved = 1 ORDER BY  .post_time ASC LIMIT 1830, 15;

Car un index sur topic_id, post_approved et un autre sur post_time pourrait être pas mal.

Julia41 · 29 Mai 2013

Ah j'avais pas été sur l'autre forum, donc oui, créer cet INDEX sur "topic_id, post_approved" ferait du bien je pense.

Edit: aussi vu le nombre de message que tu as, un petit dédié pourrait te faire du bien je pense... Ca reste bien sûr à définir, mais là c'est clair, tu fais chier tes voisins en mutu, je comprends donc l'hébergeur.

Archaos · 29 Mai 2013

Merci pour les infos bool, je ne connaissais pas l'offre sqlpro

Bonjour Julia,

Julia41 a dit:
T'as accès à l'output de :

Code:

EXPLAIN SELECT p.post_id FROM phpbb3_posts p WHERE p.topic_id = 13794 AND p.post_approved = 1 ORDER BY .post_time ASC LIMIT 1830, 15;

voici :

je suis en train de créer l'index sur "topic_id, post_approved" ...

Edit :

voilà, si je ne me suis pas trompé :

avec le nouvel index :

Bool · 29 Mai 2013

Donc ce nouvel index n'apporte rien : MySQL préfère utiliser «tid_post_time», plus efficace. Par contre ce nouvel index va ralentir les inserts & updates (comme tous les indexes).

MySQL se coltine donc entre 2500 et 3500 lignes pour faire cette recherche. La question est : ce combien de messages contient ce topic ? Si le topic contient effectivement un petit millier de messages, tu ne pourras guère optimiser... à moins que le filtre «post_approved = 1» soit terriblement restrictif ?

Archaos · 29 Mai 2013

Le sujet contient un peu plus de 4000 messages.

à moins que le filtre «post_approved = 1» soit terriblement restrictif ?

je n'ai pas réussi à trouver d'info à ce sujet mais je n'utilise jamais d’approbation sur le forum, tout est à 1

Bool · 29 Mai 2013

Archaos a dit:
Le sujet contient un peu plus de 4000 messages.

Dans ce cas ça sert à rien d'ajouter des indexes, cette requête ne pourra pas descendre plus bas : elle traite déjà le minimum possible, et les lignes en question sont déjà triées MySQL n'a pas besoin de les retrier. De plus aucune jointure ici, ni table temporaire. Bref, rien à faire coté MySQL, si ce n'est revoir complètement le code (pour pré-calculer les numéros de page, par exemple).

Archaos · 29 Mai 2013

Ça me semble logique en effet, merci pour tes éclaircissements :wink:

Julia41 · 30 Mai 2013

Ouais, je rejoins ce que dit Bool, index inutile.
Moi c'est le temps d'exec qui me semble long, 7 secondes pour une requête plutôt simple.

A tout hasard, un index sur topic_id, post_approved,post_time
Voir, mais bon, j'pense pas que ça gagnerait non plus :/

A noter que créer ton INDEX en UNIQ (je pense que ça doit pas poser de soucis vu que topicid est déjà unique) aurait peut-être fait un réel gain...

Bref, tu peux tester, pas sûr que ça change grand chose, mais 7 secondes, ça reste énorme :/ Peut-être une petite couille de configuration du serveur ou autre.

Comme bool, est-ce que ce "post_approved = 1" est vraiment utile.

Si tu fais des benchs, penses bien à SQL_NO_CACHE tes requêtes pour être sûr de les avoir sans cache et de voir si tu as une vraie différence.

Archaos · 30 Mai 2013

Il préfère toujours tid_post_time, en unique, il refuse de créer l'index (duplicate)

Voici quand même le dernier slowlog d'hier (plus à jour).

Entre celui en lien sur le premier post et celui-ci, j'ai allégé le forum ( Allégement des tables de recherches ,suppression de gros topics, accès de plus de 450 000 posts interdit aux visiteurs) :

Code:

# Time: 130529  7:43:39
User
# Query_time: 6.322755  Lock_time: 0.000094 Rows_sent: 15  Rows_examined: 760
use forum
SET timestamp=1369806219;
SELECT p.post_id
	FROM phpbb3_posts p
	WHERE p.topic_id = 13794
		AND p.post_approved = 1
			
	ORDER BY p.post_time DESC
 LIMIT 745, 15;

# Time: 130529  7:55:19
User
# Query_time: 5.785542  Lock_time: 0.000049 Rows_sent: 15  Rows_examined: 25
use forum
SET timestamp=1369806919;
SELECT p.post_id
	FROM phpbb3_posts p
	WHERE p.topic_id = 13794
		AND p.post_approved = 1
		ORDER BY p.post_time DESC
 LIMIT 10, 15;

# Time: 130529  8:04:31
User
# Query_time: 14.825879  Lock_time: 0.007520 Rows_sent: 15  Rows_examined: 2080
use forum
SET timestamp=1369807471;
SELECT p.post_id
	FROM phpbb3_posts p
	WHERE p.topic_id = 13794
		AND p.post_approved = 1
	ORDER BY p.post_time DESC
 LIMIT 2065, 15;

# Time: 130529  8:06:43
User
# Query_time: 5.716585  Lock_time: 0.000090 Rows_sent: 15  Rows_examined: 801
use forum
SET timestamp=1369807603;
SELECT p.post_id
	FROM phpbb3_posts p
	WHERE p.topic_id = 25728
		AND p.post_approved = 1
		ORDER BY p.post_time DESC
 LIMIT 786, 15;
 
# Time: 130529  9:00:06
User
# Query_time: 5.401277  Lock_time: 0.000059 Rows_sent: 1  Rows_examined: 4426
use forum
SET timestamp=1369810806;
SELECT COUNT(p.post_id) AS prev_posts
			FROM phpbb3_posts p
			WHERE p.topic_id = 19082
				AND p.post_approved = 1 AND (p.post_time < 1369753396 OR (p.post_time = 1369753396 AND p.post_id <= 1105391));

# Time: 130529  9:00:12
User
# Query_time: 8.196636  Lock_time: 0.015441 Rows_sent: 1  Rows_examined: 4216
use forum
SET timestamp=1369810812;
SELECT COUNT(p.post_id) AS prev_posts
			FROM phpbb3_posts p
			WHERE p.topic_id = 8210
				AND p.post_approved = 1 AND (p.post_time < 1369763341 OR (p.post_time = 1369763341 AND p.post_id <= 1105460));

# Time: 130529  9:00:12
User
# Query_time: 10.450705  Lock_time: 0.000053 Rows_sent: 1  Rows_examined: 4413
SET timestamp=1369810812;
SELECT COUNT(p.post_id) AS prev_posts
			FROM phpbb3_posts p
			WHERE p.topic_id = 17294
				AND p.post_approved = 1 AND (p.post_time < 1369765550 OR (p.post_time = 1369765550 AND p.post_id <= 1105485));

# Time: 130529  9:00:14
User
# Query_time: 5.801662  Lock_time: 0.000079 Rows_sent: 1  Rows_examined: 5663
SET timestamp=1369810814;
SELECT COUNT(p.post_id) AS prev_posts
			FROM phpbb3_posts p
			WHERE p.topic_id = 25729
				AND p.post_approved = 1 AND (p.post_time < 1369772096 OR (p.post_time = 1369772096 AND p.post_id <= 1105613));

# Time: 130529 10:44:35
User
# Query_time: 6.417355  Lock_time: 0.000046 Rows_sent: 15  Rows_examined: 120
use forum
SET timestamp=1369817075;
SELECT p.post_id
	FROM phpbb3_posts p
	WHERE p.topic_id = 1579
		AND p.post_approved = 1
		ORDER BY p.post_time ASC
 LIMIT 105, 15;

# Time: 130529 16:23:09
User
# Query_time: 6.634875  Lock_time: 0.000036 Rows_sent: 10001  Rows_examined: 52065
use forum
SET timestamp=1369837389;
SELECT * FROM `phpbb3_search_wordmatch` LIMIT 42064,10001;

# Time: 130529 16:27:55
User
# Query_time: 6.869514  Lock_time: 0.000049 Rows_sent: 10001  Rows_examined: 1831065
use forum
SET timestamp=1369837675;
SELECT * FROM `phpbb3_search_wordmatch` LIMIT 1821064,10001;

# Time: 130529 21:55:50
User
 # Query_time: 5.461613  Lock_time: 0.000086 Rows_sent: 1  Rows_examined: 2013
use forum
SET timestamp=1369857350;
SELECT COUNT(p.post_id) AS prev_posts
			FROM phpbb3_posts p
			WHERE p.topic_id = 20328
				 AND (p.post_time < 1369778421 OR (p.post_time = 1369778421 AND p.post_id <= 1105719));

# Time: 130529 23:20:08
User
# Query_time: 6.715019  Lock_time: 0.000068 Rows_sent: 1  Rows_examined: 4433
use forum
SET timestamp=1369862408;
SELECT COUNT(p.post_id) AS prev_posts
			FROM phpbb3_posts p
			WHERE p.topic_id = 19082
				AND p.post_approved = 1 AND (p.post_time < 1369844256 OR (p.post_time = 1369844256 AND p.post_id <= 1106009));

# Time: 130529 23:49:19
User
# Query_time: 5.886671  Lock_time: 0.000090 Rows_sent: 228  Rows_examined: 11127
use forum
SET timestamp=1369864159;
SELECT t.topic_id
				FROM phpbb3_topics t, phpbb3_posts p
				WHERE p.poster_id = '13168'
					
					
					 AND p.post_approved = 1
					 AND p.forum_id NOT IN (20, 24, 25, 47)
					AND t.topic_id = p.topic_id
					
					
				GROUP BY t.topic_id, t.topic_last_post_time
				ORDER BY t.topic_last_post_time DESC
 LIMIT 250;

Il est moins "lourd"

5,7 secondes pour 25 rows ça fait beaucoup néanmoins, je ne comprends pas :

Code:

# Time: 130529  7:55:19
User
# Query_time: 5.785542  Lock_time: 0.000049 Rows_sent: 15  Rows_examined: 25
use forum
SET timestamp=1369806919;
SELECT p.post_id
	FROM phpbb3_posts p
	WHERE p.topic_id = 13794
		AND p.post_approved = 1
		ORDER BY p.post_time DESC
 LIMIT 10, 15;

Bool · 30 Mai 2013

# Query_time: 5.785542 Lock_time: 0.000049 Rows_sent: 15 Rows_examined: 25

Yep, serveur affreusement surchargé et/ou super mal configuré (par exemple : les indexes ne tiennent pas en mémoire).

Tu peux éventuellement en savoir un peu plus :

Code:

set profiling := 1;
SELECT SQL_NO_CACHE p.post_id
   FROM phpbb3_posts p
   WHERE p.topic_id = 13794
      AND p.post_approved = 1
      ORDER BY p.post_time DESC
LIMIT 10, 15;
show profile;

Archaos · 30 Mai 2013

il me semble que c'est normal en ce moment

Bool · 30 Mai 2013

Je suis pas certain que phpMyAdmin sache gérer ce type de requêtes multiples... il faudrait soit lancer ça dans un script maison, soit utiliser un vrai client MySQL (via MySQL Workbench par exemple).

Tu aurais du obtenir quelque chose de ce genre :

Code:

mysql> show profile;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000044 |
| Opening tables     | 0.000008 |
| System lock        | 0.000004 |
| Table lock         | 0.000006 |
| init               | 0.000012 |
| optimizing         | 0.000006 |
| statistics         | 0.000051 |
| preparing          | 0.000013 |
| executing          | 0.000003 |
| Sorting result     | 0.000005 |
| Sending data       | 0.000101 |
| end                | 0.000004 |
| query end          | 0.000004 |
| freeing items      | 0.000011 |
| logging slow query | 0.000003 |
| cleaning up        | 0.000004 |
+--------------------+----------+
16 rows in set (0.00 sec)

Note : là par exemple il s'agit d'un phpBB3 justement, le topic contient près de 8000 messages, et la requête dure moins d'1ms, sans query cache.

Archaos · 30 Mai 2013

je suis passé par cette méthode, ça peut donner une idée peut être ?

Code:

START TRANSACTION;

set profiling=1; 


SELECT SQL_NO_CACHE p.post_id
   FROM phpbb3_posts p
   WHERE p.topic_id = 13794
      AND p.post_approved = 1
      ORDER BY p.post_time DESC
LIMIT 10, 15;

show profiles;

COMMIT;

Bool · 30 Mai 2013

presque, avec show profile (qui affiche le détail du dernier "profile"), et non show profiles (qui affiche la liste des "profiles" disponibles).

Archaos · 30 Mai 2013

en effet :wink: avec show profile :

Bool · 30 Mai 2013

Donc quand les datas sont dans le buffer pool, la requête est très rapide, aucun problème particulier.

Conclusion : le serveur en question n'a pas assez de mémoire ou bien le buffer pool est trop petit. Soit tu arrives à forcer ton hébergeur à corriger ça de son coté, soit tu changes d'offre afin d'avoir plus de mémoire "pour toi".

Archaos · 30 Mai 2013

Merci vraiment , il ne me reste plus qu'à agir 8)