Les performances d'insertion de MySQL se dégradent sur une grande table
Je travaille avec une immense table qui a plus de 250 millions de lignes. Le schéma est simple.
CREATE TABLE MyTable (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
oid INT NOT NULL,
long1 BIGINT NOT NULL,
str1 VARCHAR(30) DEFAULT NULL,
str2 VARCHAR(30) DEFAULT NULL,
str2 VARCHAR(200) DEFAULT NULL,
str4 VARCHAR(50) DEFAULT NULL,
int1 INT(6) DEFAULT NULL,
str5 VARCHAR(300) DEFAULT NULL,
date1 DATE DEFAULT NULL,
date2 DATE DEFAULT NULL,
lastUpdated TIMESTAMP NOT NULL,
hashcode INT NOT NULL,
active TINYINT(1) DEFAULT 1,
KEY oid(oid),
KEY lastUpdated(lastUpdated),
UNIQUE KEY (hashcode, active),
KEY (active)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 MAX_ROWS=1000000000;
La performance de l'insert a chuté de manière significative. Jusqu'à 150 millions de lignes dans la table, il sert à prendre de 5 à 6 secondes pour insérer 10 000 lignes. Maintenant, il a augmenté de 2 à 4 fois. Innodb est ibdata fichier a grandi à 107 GB. Innodb paramètres de configuration sont comme suit.
innodb_buffer_pool_size = 36G # Machine has 48G memory
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 50M
innodb_log_buffer_size = 20M
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
expire_logs_days = 4
IO temps d'attente est passé comme on le voit avec top
. J'ai essayé de changer la méthode flush à O_DSYNC, mais il n'a pas aidé. Le disque est sculptée de matériel configuration RAID 10. Dans une précédente installation avec un seul disque, IO n'était pas un problème.
Est partitionnement de la table seule option? Pouvez séparer 100G de fichier dans "petits" fichiers d'aide? Existe-il des variables qui doivent être accordées pour le RAID?
Mise à jour: C'est un système de test. J'ai la liberté de faire les modifications nécessaires.
source d'informationauteur Shashikant Kore
Vous devez vous connecter pour publier un commentaire.
Vous n'avez pas dit si c'était un système de test ou de production; je suis en supposant que c'est la production.
Il est probable que vous obtenez le tableau de taille où ses index (ou le tout) n'ont plus leur place dans la mémoire.
Cela signifie que MyISAM doit lire les pages en cours de plaquettes (en fonction de la distribution de vos nouvelles lignes des " valeurs de l'indice). Les pages de lecture (lectures aléatoires) est vraiment lent et doit être évitée si possible.
Partitionnement semble être la solution la plus évidente, mais MySQL de partitionnement peuvent ne pas convenir à votre cas d'utilisation.
Vous devriez certainement considérer toutes les options possibles - obtenir le tableau sur un serveur de test dans votre laboratoire pour voir comment il se comporte.
Votre clé primaire me semble que c'est peut-être pas nécessaire (vous avez un autre index unique), afin d'éliminer c'est une option.
Également envisager la innodb plugin et de compression, ce qui fera de votre innodb_buffer_pool aller plus loin.
Vous avez vraiment besoin d'analyser votre cas d'utilisation de décider si vous avez vraiment besoin de garder toutes ces données, et si le partitionnement est une solution sensée.
De faire des changements sur cette application sont susceptibles d'introduire de nouveaux problèmes de performance pour vos utilisateurs, si vous voulez vraiment être prudent ici. Si vous trouver un moyen pour améliorer la performance de l'insert, il est possible qu'il va réduire les performances de recherche de performances ou d'autres opérations. Vous aurez besoin de faire un approfondie de test de performance sur la production de matériel de qualité, avant de les libérer d'un tel changement.
De mon expérience avec Innodb, il semble frappé d'une limite pour écrire des systèmes intensifs, même si vous avez vraiment optimisé sous-système de disque. Je suis surpris que vous ayez réussi à obtenir jusqu'à 100 go.
C'est ce que twitter a frappé dans un certain temps il ya et réalisé qu'il avait besoin de croiser les voir http://github.com/twitter/gizzard.
Tout ceci dépend de votre cas d'utilisation, mais vous pouvez aussi passer de mysql à cassandra qu'il effectue vraiment bien pour écrire des applications intensives.(http://cassandra.apache.org)
Comme MarkR commenté ci-dessus, la performance de l'insert s'aggrave lorsque les index peuvent plus tenir dans votre pool de mémoire tampon. MyISAM a une au hasard IO mécanisme de réduction (appelé à l'insertion de la mémoire tampon) qui empêche certains de ces problèmes - mais il ne fonctionnera pas sur votre index UNIQUE. L'index sur (hashcode, active) doit être vérifiée à chaque insertion assurez-vous qu'aucune des entrées en double sont insérés. Si le hashcode de ne pas "suivre" la clé primaire, cette vérification pourrait être aléatoire IO.
Vous avez la possibilité de modifier le schéma?
Votre meilleur pari est de:
(a) Faire hashcode quelqu'un séquentiel, ou de trier par hashcode avant vrac insertion (en cela par lui-même aidera, car les lectures aléatoires sera réduite).
(b) Faire (hashcode,active) la clé primaire et insérer des données dans l'ordre de tri. Je devine votre demande probablement lit par hashcode - et une clé primaire de recherche est plus rapide.
Vous n'avez pas mentionné ce que votre charge de travail est, mais si il n'y a pas trop de lectures ou vous avez assez de mémoire principale, une autre option est d'utiliser une écriture optimisée backend MySQL, au lieu de innodb. Tokutek revendications 18x plus rapide des inserts et beaucoup plus plat de la courbe de performance comme le jeu de données augmente.
tokutek.com
http://tokutek.com/downloads/tokudb-performance-brief.pdf
Je vais second @MarkR commentaires à propos de la réduction de l'index. Une autre chose que vous devriez regarder est l'augmentation de votre innodb_log_file_size. Il augmente le crash de temps de récupération, mais il devrait aider. Être conscient que vous avez besoin de supprimer les anciens fichiers avant de redémarrer le serveur.
Général MyISAM conseils de réglages:
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
Vous devriez aussi être conscient de
LOAD DATA INFILE
pour faire des inserts. C'est beaucoup plus rapide.Augmentation de
innodb_log_file_size = 50M
àinnodb_log_file_size = 500M
Et la
innodb_flush_log_at_trx_commit
doit être 0 si vous supportez 1 sec de perte de données.