Les index multiples sur la table MYSQL sont-ils la raison des mises à jour et des INSERTS lents?

Dec 10 2020

Les performances de mon site (pile LAMP) se sont considérablement dégradées au cours des deux derniers jours malgré aucune mise à jour du code. Il semble que seules les insertions, les mises à jour et les suppressions d'une table MySQL particulière causent le problème. Toute page qui met à jour, insère ou supprime une entrée de la «table» des travaux prend environ 10 secondes à charger. (EG UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)

SELECT les requêtes semblent s'exécuter comme avant, bien qu'elles semblent être plus lentes si une mise à jour a lieu en même temps.

Le tableau compte environ 180 000 entrées. J'ai remarqué dans la vue PHPMyAdmin, qu'en plus de l'index "normal" sur le champ primaire, il y a un index sur le champ "entry_date" (voir image). Cela pourrait-il être un problème dans ce cas? Je ne sais pas pourquoi un index sur ce champ aurait été créé.

Sinon, quelle autre pourrait être la source du problème? J'ai vérifié l'espace sur le disque, ce qui semble correct. (7 Go disponibles) selon df.

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL DEFAULT '0', 
    `entry_date` date NOT NULL DEFAULT '0000-00-00', 
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00', 
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `description` text COLLATE latin1_german2_ci NOT NULL, 
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `status` tinyint(4) NOT NULL DEFAULT '0', 
    `cat_id` int(4) NOT NULL DEFAULT '0', 
    `price` decimal(4,2) NOT NULL DEFAULT '1.00', 
    `emailcount` smallint(5) NOT NULL DEFAULT '-1', 
    `emailcount2` int(11) NOT NULL DEFAULT '-1', 
    `recemailcount` int(11) NOT NULL DEFAULT '-1', 
    `archive` tinyint(4) NOT NULL DEFAULT '0', 
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL, 
    PRIMARY KEY (`job_id`), 
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

MISE À JOUR - Tout d'abord merci à tous les contributeurs. J'apprécie beaucoup. Ainsi, au cours des deux derniers jours, le problème a cessé, ce qui a rendu la recherche du problème encore plus difficile. Mais maintenant, il semble être de nouveau revenu. Permettez-moi de commencer par fournir le type de machine hébergée sur Google Cloud: g1-small (1 vCPU, 1,7 Go de mémoire). Je vais continuer à mettre à jour cela avec les informations supplémentaires qui ont été demandées par ceux qui commentent.

Réponses

6 ypercubeᵀᴹ Dec 10 2020 at 19:22

Pour répondre à la question spécifique:

Non , un index supplémentaire sur (entry_date)ou deux ne nuirait pas aux performances de mise à jour d'une titlecolonne différente .

Aditionellement:

Non , la version de MySQL, 5.6, n'est pas trop ancienne, même si certaines fonctionnalités modernes (comme les fonctions de fenêtre) manquent. Vous devriez avoir des performances décentes avec un matériel décent.

Nous ne pouvons que spéculer sur le problème en question mais ce qui pourrait ne pas être le cas ou l'expliquer:

matériel ancien et inefficace: vérifiez les spécifications du disque, mesurez sa performance.
index / table fragmentés. Consultez la documentation MySQL et les anciennes questions / réponses ici pour savoir comment défragmenter les tables MyISAM (OPTIMIZE TABLE).

Enfin et surtout:

Votre table utilise le moteur MyISAM , qui est une vieille nouvelle. InnODB l'a remplacé comme moteur par défaut dans MySQL il y a des années. Il n'y a pas de développement actif de ce moteur. Il manque plusieurs fonctionnalités par rapport à InnoDB (transactions, contraintes de clés étrangères, etc.) et des performances dans la plupart des charges de travail . Je vous suggère fortement de changer votre table (après avoir bien sûr testé que vos applications et procédures ne cassent pas) pour utiliser InnoDB.
Surtout pour votre question, InnoDB peut effectuer SELECTet UPDATEen même temps (généralement). MyISAM verrouille complètement la table; la mise à jour ou la sélection doit se terminer avant que la sélection ou la mise à jour puisse même commencer.
Lorsque vous passez de MyISAM à InnoDB, veillez à régler key_buffer_sizeet innodb_buffer_pool_size.

3 J.D. Dec 10 2020 at 19:20

Les index affectent légèrement les performances des opérations INSERT et DELETE, mais cela est généralement négligeable, surtout si vos index sont bien planifiés et que vous n'allez pas trop loin en plaçant 30 index sur la même table chacun avec 30 combinaisons différentes de champs. (En général, je m'en tiens à une règle de 5 sur 5 - 5 index maximum, 5 champs maximum par index. Bien sûr, ce n'est qu'une ligne directrice et non une règle stricte).

Cela étant dit, les index sont en fait utilisés pour augmenter les performances des requêtes UPDATE et SELECT, car l'index est utilisé pour localiser les lignes dans UPDATE ou SELECT.

Il est peu probable que le changement drastique que vous voyez soit lié à l'index entry_date que vous avez découvert (savez-vous si celui-ci a été ajouté récemment ou y était déjà avant le changement de performances?).

Deux choses que vous devriez examiner sont:

Si les index précédents pour vos requêtes sont toujours utilisés, en particulier pour les requêtes UPDATE et SELECT (et s'ils sont recherchés ou si une opération d'analyse est en cours). Cela peut changer en raison des modifications des statistiques de table des données au fil du temps. Vous pouvez utiliser l' instruction ANALYZE pour vérifier les statistiques d'une table.
L'autre chose que vous pouvez examiner est la fragmentation de l'index , qui est un phénomène naturel au fil du temps. Cela se produit lorsque davantage de données sont ajoutées à une table. (En général, cela ne devrait pas être un problème sur une petite table comme la vôtre, mais cela vaut la peine de le vérifier de toute façon.)

Je vais continuer à mettre à jour ma réponse avec plus de choses à examiner pendant que j'y pense. L'exécution d'un EXPLAIN sur vos requêtes peut également vous aider à comprendre le problème. Essayez de séparer les instructions UPDATE et INSERT qui s'exécutent lentement et essayez de comprendre si elles sont affectées par la même cause fondamentale (car encore une fois, elles agissent quelque peu inversement aux index, INSERT sont négligeables mais les UPDATE bénéficient généralement de l'index correct et devrait être plus rapide.)