Différence de performances entre Innodb et Myisam dans Mysql

J'ai une table mysql avec plus de 30 millions de disques qui a été à l'origine être stockées avec myisam. Voici une description de la table:

Je voudrais exécuter la requête suivante sur ce tableau qui serait généralement environ 30 secondes. Je voudrais changer @eid chaque fois pour éviter de base de données ou la mise en cache disque.

select count(fact_data.id) 
    from fact_data 
    where fact_data.entity_id=@eid 
        and fact_data.metric_id=1

J'ai ensuite converti cette table innoDB, sans faire d'autres changements et par la suite la même requête retourne maintenant en moins d'une seconde à chaque fois que j'exécute la requête. Même quand je définie de façon aléatoire @eid pour éviter la mise en cache, la requête renvoie en moins d'une seconde.

J'ai fait des recherches sur les différences entre les deux types de stockage pour tenter d'expliquer l'amélioration spectaculaire de la performance, mais n'ont pas été en mesure de trouver quoi que ce soit. En fait, beaucoup de ce que j'ai lu indique que Myisam devrait être plus rapide.

Les questions que je suis en cours d'exécution à l'encontre d'une base de données locale avec aucune autre processus affectant la base de données au moment des tests.

OriginalL'auteur opike | 2012-03-30

15

C'est un étonnamment grande différence de performances, mais je ne peux penser à quelques choses qui peut contribuer.

MyISAM a toujours été considérée comme plus rapide que InnoDB, mais pour les versions récentes de InnoDB, c'est vrai pour beaucoup, beaucoup moins de cas d'utilisation. MyISAM est généralement plus rapide pour table de scans de tables en lecture seule. Dans la plupart des autres cas d'utilisation, je trouve généralement que InnoDB pour être plus rapide. Souvent plusieurs fois plus rapide. Les verrous de Table sont un glas pour MyISAM dans la plupart de mon utilisation de MySQL.

MyISAM caches index dans son buffer de clé. Peut-être que vous avez défini la clé de la mémoire tampon trop petit pour efficacement cache l'index de votre un peu grande table.

MyISAM dépendent du système d'exploitation pour mettre en cache les données de la table de la .MYD fichiers dans le système d'exploitation de cache disque. Si le système d'exploitation est en cours d'exécution faible sur la mémoire, il va commencer le dumping son cache disque. Qui pourrait le forcer à garder la lecture à partir du disque.

InnoDB des caches dans les deux index et les données dans sa propre mémoire tampon. Vous pouvez dire à l'OS afin de ne pas utiliser son cache disque si vous définissez innodb_flush_method à O_DIRECT, mais ce n'est pas pris en charge sur mac OS X.

InnoDB généralement tampons de données et d'index dans les pages de 16 ko. Selon la façon dont vous modifiez la valeur de @eid entre les requêtes, il peut avoir déjà mis en cache les données d'une requête en raison de la lecture du disque à partir d'une requête précédente.

Assurez-vous que vous avez créé l'index de la même manière. Utilisez expliquer pour vérifier si MySQL est l'aide de l'index. Depuis que vous avez inclus la sortie de décrire la place de show create table ou montrer de l'index à partir de l', je ne peux pas dire si entity_id fait partie d'un indice composite. Si ce n'était pas la première partie d'un indice composite, il ne serait pas utilisé.

Si vous utilisez un relativement moderne version de MySQL, exécutez la commande suivante avant d'exécuter la requête:

ensemble de profilage = 1;

Qui fera tourner sur requête de profilage pour votre session. Après l'exécution de la requête, exécutez

afficher les profils;

Qui va vous montrer la liste des requêtes dont les profils sont disponibles. Je pense qu'il conserve le dernier 20 par défaut. En supposant que votre requête a été le premier, run:

afficher le profil pour la requête 1;

Vous verrez alors la durée de chaque étape dans l'exécution de votre requête. Ceci est extrêmement utile pour déterminer ce qui (par exemple, les verrous de table, le tri, la création de tables temporaires, etc.) est à l'origine d'une requête pour être lent.

OriginalL'auteur Robert Stewart
6

Mon premier soupçon serait que l'original de la table MyISAM et/ou index fragmenté au fil du temps du rendement qui se dégradent lentement. La table InnoDB n'aurait pas le même problème depuis que vous avez créé avec toutes les données (de sorte qu'il serait tout être stockées de manière séquentielle sur le disque).

Vous pouvez tester cette théorie par la reconstruction de la table MyISAM. La meilleure façon de le faire serait d'utiliser un "null" instruction ALTER TABLE:
```
ALTER TABLE mytable ENGINE = MyISAM;
```
Puis de vérifier les performances de voir si c'est mieux.

Une autre possibilité serait si la base de données elle-même est tout simplement à l'écoute pour InnoDB la performance plutôt que MyISAM. Par exemple, InnoDB utilise le innodb_buffer_pool_size parameter de connaître la quantité de mémoire allouée au stockage des données mises en cache et les index en mémoire. Mais MyISAM utilise le key_buffer paramètre. Si votre base de données a une grande innodb pool de mémoire tampon et d'une clé de petite taille de la mémoire tampon, puis InnoDB performance va être mieux que MyISAM performance, en particulier pour les grandes tables.

Raisonnable test simple de créer une nouvelle table MyISAM et le calendrier de la requête sur la table pourrait confirmer cette supposition.

OriginalL'auteur Eric Petroelje
1

Quel est votre indice de définitions, il ya des façons dont vous pouvez créer des index pour MyISAM dans lequel votre champs de l'index ne sera pas utilisé lorsque vous pensez qu'ils le feraient.

OriginalL'auteur Christopher Brand

Vous devez vous connecter pour publier un commentaire.