Edit : voir aussi ma réponse, la principale différence est la LIMIT que phpmyadmin ajoute, mais je ne comprends toujours pas et phpmyadmin est encore plus lent que mysqli.
Sur notre serveur de base de données (+ web), nous avons une énorme différence de performances lorsque vous faites une requête dans phpmyadmin vs le faites depuis php (mysqli) ou directement sur le mariadb serveur. 60 secondes contre
Cette requête fonctionne assez bien:
[mysqld] innodb_buffer_pool_size=2G innodb_buffer_pool_instances=4 innodb_flush_log_at_trx_commit=2 tmp_table_size=64M max_heap_table_size=64M join_buffer_size=4M sort_buffer_size=8M optimizer_search_depth=5
Mais, uniquement dans phpMyAdmin, la requête devient extrêmement lent lorsque nous changeons 2020-05-02 en 2020-05-01 .
SHOW PROCESSLIST montre que le queryu est principalement Envoi de données en cours d'exécution.
Suivant mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts J'ai fait la série de requêtes suivante: p >
MariaDB 10.4 InnoDB CentOs7 phpMyAdmin 4.9.5 php 5.6 Apache
Les différences sont fascinantes. (J'ai omis toutes les valeurs égales à 0 dans tous les cas). Et cohérent dans le temps.
CREATE TABLE `TitelDaggegevens` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`isbn` decimal(13,0) NOT NULL,
`datum` date NOT NULL,
`volgendeDatum` date DEFAULT NULL,
`prijs` decimal(8,2) DEFAULT NULL,
`prijsExclLaag` decimal(8,2) DEFAULT NULL,
`prijsExclHoog` decimal(8,2) DEFAULT NULL,
`stadiumDienstverlening` char(2) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`stadiumLevenscyclus` char(1) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`gewicht` double(7,3) DEFAULT NULL,
`volume` double(7,3) DEFAULT NULL,
`24uurs` tinyint(1) DEFAULT NULL,
`UitgeverCode` varchar(4) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`imprintId` int(11) DEFAULT NULL,
`distributievormId` tinyint(4) DEFAULT NULL,
`boeksoort` char(1) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`publishingStatus` tinyint(4) DEFAULT NULL,
`productAvailability` tinyint(4) DEFAULT NULL,
`voorraadAlles` mediumint(8) unsigned DEFAULT NULL,
`voorraadBeschikbaar` mediumint(8) unsigned DEFAULT NULL,
`voorraadGeblokkeerdEigenaar` smallint(5) unsigned DEFAULT NULL,
`voorraadGeblokkeerdCB` smallint(5) unsigned DEFAULT NULL,
`voorraadGereserveerd` smallint(5) unsigned DEFAULT NULL,
`fondskosten` enum('depot leverbaar','depot onleverbaar','POD','BOV','eBoek','geen') COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ISBN+datum` (`isbn`,`datum`) USING BTREE,
KEY `UitgeverCode` (`UitgeverCode`),
KEY `Imprint` (`imprintId`),
KEY `VolgendeDatum` (`volgendeDatum`),
KEY `Index op voorraad om maxima snel te vinden` (`isbn`,`voorraadAlles`) USING BTREE,
KEY `fondskosten` (`fondskosten`),
KEY `Datum+isbn+fondskosten` (`datum`,`isbn`,`fondskosten`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16519430 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
Les résultats EXPLAIN sont identiques dans tous les cas (phpmyadmin / mysqli / putty + mariadb).
[rows] => 422796 for 2020-05-01
[rows] => 450432 for 2020-05-02
La seule différence réside dans les lignes:
[select_type] => SIMPLE
[table] => TitelDaggegevens
[type] => range
[possible_keys] => fondskosten,Datum+isbn+fondskosten
[key] => Datum+isbn+fondskosten
[key_len] => 3
[ref] =>
[Extra] => Using index condition; Using filesort
La question
Peut nous donnez-vous des directions dans lesquelles nous devrions chercher à résoudre ce problème? Nous avons travaillé pendant une semaine pour optimiser le serveur mariadb (maintenant optimal, sauf dans phpmyadmin) et restreindre certains de nos problèmes à l'exemple ci-dessous. Nous utilisons beaucoup phpmyadmin mais avons peu ou pas d'expérience avec ce qui se trouve sous la surface (comme la façon dont il se connecte à la base de données).
À propos de l'indexation / de la commande
Dans la requête lente, si nous changeons le Nous * savons * que nous pouvons résoudre cette requête particulière par une meilleure indexation, que nous sommes déjà prêts à implémenter. Cependant, nous voulons savoir ce qui cause les différents temps entre phpmyadmin et mysqli / directement. Les détails: TitelDaggegevens contient La structure de la table: Configuration de notre web virtuel + base de données + serveur de messagerie: Quelques paramètres de configuration mariadb importants que nous avons modifiés par rapport à ce que notre serveur Web virtuel avait par défaut: ORDER BY du champ isbn indexé en un champ non indexé ou si nous omettons le ORDER BY code> dans l'ensemble, tout a à nouveau sa vitesse d'éclair normale. Changer le ORDER BY en clé primaire id le ralentit également, mais toujours 10 fois plus rapide qu'avec le champ indexé isbn . P > | how: | server/MySqli | phpMyAdmin
| date used in query: | 2020-05-02 | 2020-05-01 | 2020-05-02 | 2020-05-01
| records returned: | 6912 | 1 | 6912 | 1
| avg speed: | 0.27s | 0.00s | 0.52s | 60s (!)
| Variable_name | Value | Value | Value | Value
| Handler_icp_attempts | 213197 | 206286 | 213197 | 0
| Handler_icp_match | 6912 | 1 | 6912 | 0
| Handler_read_next | 6912 | 1 | 26651 | 11728896 (!)
| Handler_read_key | 1 | 1 | 151 | 4
| Handler_commit | 1 | 1 | 152 | 5
| Handler_read_first | 0 | 0 | 1 | 1
| Handler_read_rnd_next | 0 | 0 | 82 | 83
| Handler_read_rnd | 0 | 0 | 0 | 1
| Handler_tmp_write | 0 | 0 | 67 | 67
FLUSH STATUS;
SELECT-query above with one of the two dates;
SHOW SESSION STATUS LIKE 'Handler%';
SELECT * FROM `TitelDaggegevens`
WHERE `datum` > '2020-03-31' AND datum < '2020-05-02' AND `fondskosten` IS NULL
ORDER BY isbn;
4 Réponses :
La plus grande différence est bien sûr que phpmyadmin ajoute une LIMITE à la requête. Cela donne l'explication principale. Je ne peux pas croire que ce ne soit pas la première chose que nous avons essayée, je suis très gêné.
Cependant, la différence de vitesse entre phpMyAdmin et mysqli est toujours grande, et les résultats sont toujours différents (01/05/2020 le server ou mysqli):
+------+-------------+------------------+-------+------------------------------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------+-------+------------------------------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | TitelDaggegevens | index | fondskosten,Datum+isbn+fondskosten | ISBN+datum | 9 | NULL | 1351 | Using where | +------+-------------+------------------+-------+------------------------------------+------------+---------+------+------+-------------+
Vitesse avec limit et 2020-05-02: tout autour de 0,17-0,2
Vitesse avec limit et 01/05/2020:
php / mysqli: réclamé: 3,5 secondes mais la page se charge pendant environ 30 secondes
putty / mariadb: claime également 3,5 secondes mais montre les résultats après environ 30 secondes
phpmyadmin: réclamé et en temps réel environ 60secs
De plus, EXPLAIN change considérablement avec une LIMIT:
(avec les lignes 1268 avec datum
+----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | Handler_commit | 1 | | Handler_read_first | 1 | | Handler_read_next | 11733306 | | rest | 0 | +----------------------------+----------+
Nous ne pouvons toujours pas voir votre dernière QUERY. SELECT .....; Pensez à activer le journal général avant d'exécuter la requête dans chacun d'eux. Ensuite, désactivez le journal général, publiez également la sortie du journal général. Merci
Je suis désolé. SELECT * FROM TitelDaggegevens WHERE datum> '2020-03-31' AND datum <'2020-05-02' AND fondskosten IS NULL ORDER BY isbn LIMIT 0,25 . La requête plus la limite.
Apparemment, les 30 jours et plus demandés ont poussé l'optimiseur dans une direction inattendue. Est-ce que la sélection simple = (un jour), puisque vous voulez vraiment les données d'un jour, renvoie les résultats plus rapidement?
Encore une fois, il ne s'agit pas d'optimiser la requête. Un simple index fera cela. Ou en laissant de côté la LIMITE. La question principale est (maintenant): pourquoi le rapport de requête prend-il 4 secondes alors qu'il prend en fait 60 secondes?
J'ai le fichier journal (merci pour le conseil) mais il est très long (également à cause d'autres choses qui se passent en même temps). Pendant la minute que prend la requête, rien n'est enregistré du tout. Donc aucune aide là-bas. Principalement beaucoup de choses dont phpmyadmin a besoin pour afficher la requête après la requête et un peu de préparation avant. (Parce que je l'ai couru depuis phpmyadmin.)
De plus, aucune différence dans le fichier journal entre une requête rapide et une requête lente (sauf pour les choses nécessaires pour afficher le résultat dans phpmyadmin). En gros, c'est 200526 13:02:39 31186 Requête SHOW WARNINGS 31186 Requête SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS' (3x this) 31186 Requête SELECT DATABASE () 31186 Init DB admin_dvu 31186 Requête SELECT * FROM TitelDaggegevens WHERE datum> '2020- 03-31 'AND datum <' 2020-05-01 'AND fondskosten IS NULL ORDER BY isbn LIMIT 0,25 200526 13:04:04 31186 Requête SHOW WARNINGS
Quelle est la distance géographique en KM ou en Miles des 60 secondes dont vous parlez? Afficherait la liste complète des processus; vous dire qu'il «envoyait des données»? Si tel est le cas, c'est probablement le délai de livraison des paquets. Si vous publiez vos journaux sur pastebin.com qui a une limite de taille plus élevée que SO, nous pourrons peut-être vous aider à comprendre ce qui se passe.
Le journal des modifications pour 4.9.5 suggère "Pour rappel, phpMyAdmin 4.9 est dans la phase de support à long terme où il ne recevra que des correctifs de sécurité importants et des corrections de bogues critiques. Les utilisateurs sont suggérés de migrer vers la version 5.0." par Isaac de phpMyAdmin.
Nous adorerions (la première chose que j'ai essayée) mais nous devrons d'abord vérifier / mettre à jour environ 1000 pages php vers php 7, car nous sommes toujours sur php 5.6.
Wilson, merci encore. Il n'y a aucune distance physique. Les «données d'envoi» se sont avérées se produire même lorsqu'elles sont exécutées directement sur le serveur. Par conséquent, nous avons conclu, également à partir des fichiers journaux, du profilage, de l'état de la session, que ce n'est qu'une stratégie de requête étrange que mariaDB prend dans ce cas très spécifique. Nous avons embauché un expert qui a garanti qu'il s'agissait d'un comportement normal. Un index supplémentaire résout ce cas et après une analyse approfondie, nous avons constaté qu'il n'y a AUCUN problème entre phpmyadmin et notre mariadb. Merci beaucoup pour votre aide, j'ai vraiment beaucoup appris de vos réponses. Je peux faire mes propres diagnostics maintenant!
Magnifique. Voir le profil, le profil réseau pour les informations de contact et les scripts utilitaires téléchargeables gratuitement pour vous aider à optimiser les performances.
Pensez à créer optimizer_search_depth = 16 plutôt que 5
et
SELECT * FROM TitelDaggegevens
O datum ENTRE '2020-03-31' ET '2020-05-02' ET fondskosten EST NULL
ORDER BY isbn;
Bon appel. Mais c'était 16 quand nous avons rencontré le problème pour la première fois, nous l'avons abaissé pour d'autres raisons. Le BETWEEN ne fait aucune différence (mais il doit être 2020-04-30 dans la requête de problème). Cela m'inquiéterait beaucoup pour le moteur mariadb s'il avait fait une différence. ;)
Un mois supplémentaire de données fera une différence dans le temps requis. Avez-vous envisagé dans phpmyadmin d'activer le profilage pour la session pour obtenir des détails sur les ressources utilisées? Un autre niveau dans votre recherche de la raison du temps nécessaire.
Pour ce genre de problème, publier la QUERY et l'EXPLAIN (votre QUERY) serait très utile pour le test de phpmyadmin pour le 2020-05-01 qui avait le nombre élevé de handler_read_next, s'il vous plaît.
Je sais. C'est pourquoi je les ai mis à la fois dans la question et dans la version mise à jour (ma propre réponse);) Merci Wilson, nous allons nous pencher sur le profilage.
@WilsonHauck - (Nit pick) BETWEEN est inclusif, donc BETWEEN '2020-03-31' ... ne donne pas la même plage.
Je prédis que le profilage dira 99% dans une chose inutile comme "Envoi de données".
Nous l'avons examiné par un spécialiste, en plus de tous vos conseils.
Après BEAUCOUP de tests, il s'est avéré que le LIMIT 0,25 ajouté par phpMyAdmin était la SEULE chose qui causait le retard extrême. L'expert n'a pu trouver AUCUNE différence entre mysqli / phpmyadmin et son exécution directement sur le serveur mariadb.
Parfois, une TRES petite différence dans la requête (comme l'ajout d'une LIMITE pour une requête qui ne renvoie de toute façon qu'un seul enregistrement) peut entraîner une requête à prendre 100 000 fois plus longtemps car elle analysera un index entier car le moteur verra une autre stratégie adaptée à cette requête. C'est un comportement standard.
Nous avions déjà trouvé un index qui éliminait ce problème spécifique, mais maintenant nous sommes également assurés qu'il n'y a rien de mal avec notre DB. Quelque chose dont nous n'étions pas sûrs car cela semblait un comportement extrême. Donc: beaucoup de bruit pour rien.
CEPENDANT, j'ai beaucoup appris de ces expériences. Tant de notre expert que de cette communauté. J'ai appris les diagnostics MySQL, la journalisation, la façon dont mariaDB gère les requêtes ... Pour chaque diagnostic qui s'est avéré ne pas être le problème, j'ai appris des choses à éviter ou à rechercher dans les tables, les index ou les requêtes.
MERCI À TOUS, en particulier @Rick James, @Wilson Hauck et @ExploitFate
J'ai trouvé que MySQL a tendance à ignorer LIMIT . Je me demande si MariaDB en tient compte, mais foiré dans vos «BEAUCOUP» exemples.
MariaDB adopte simplement une approche différente basée sur de nombreux facteurs, et une approche très malchanceuse dans ce cas. Mais la LIMITE et la date spécifique ensemble étaient la raison pour laquelle il a soudainement fallu 100 000 fois plus longtemps.
(Je suis plutôt en retard. Heureux de voir que vous l'avez "résolu".)
Vous en avez trouvé un étrange et vous avez bien enquêté.
Existe-t-il un moyen d'obtenir EXPLAIN de phpmyadmin? Si tel est le cas, cela pourrait donner un autre indice.
Les numéros de Handler impliquent fortement qu'un EXPLAIN différent a été utilisé.
Il est clair que phpmyadmin modifie la requête (au moins en ajoutant le LIMIT ). Je me demande si cela a gâché la requête accidentellement. Avez-vous activé le Slowlog ou le journal général à ce moment-là? L'un ou l'autre devrait avoir le SQL comme exécuté .
Le remplacement de l'index uniquement sur (fondskosten) par INDEX (fondskosten, datum) devrait améliorer les performances.
("Envoi de données", comme toujours, est une information inutile fournie par le moteur.)
Suggère de déposer un bug avec mariadb.com.