ne pas copier dans la table tmp sur le disque mysql

Question

J'ai une question concernant les grandes requêtes mysql. Est-il possible de sauter le Copie dans la table tmp du disque Cette étape est beaucoup trop longue pour que je puisse obtenir les résultats de mes requêtes. J'ai lu sur la page MySQL que mysql effectue cette étape pour économiser de la mémoire, mais je ne me soucie pas d'économiser de la mémoire, je veux juste récupérer les résultats de mes requêtes RAPIDEMENT, j'ai assez de mémoire sur ma machine. De plus, mes tables sont correctement indexées, ce n'est donc pas la raison pour laquelle mes requêtes sont lentes.

Une aide ?

Merci.

Answer 1

Il y a deux choses que vous pouvez faire pour atténuer l'impact de cette situation.

OPTION #1 : Augmenter les variables tmp_table_size et/ou taille max_heap_table_size

Ces options régissent la taille d'une table temporaire en mémoire avant qu'elle ne soit considérée comme trop grande et qu'elle ne soit mise sur disque comme une table temporaire MyISAM. Plus ces valeurs sont grandes, moins vous aurez de chances d'obtenir le message "copying to tmp table on disk". Assurez-vous que votre serveur dispose de suffisamment de RAM et d'espace de stockage. max_connexions est modérément configuré si une seule connexion DB a besoin de beaucoup de RAM pour ses propres tables temporaires.

OPTION #2 : Utiliser un disque RAM pour les tables tmp

Vous devriez être en mesure de configurer un disque RAM sous Linux, puis de définir le paramètre tmpdir dans mysql comme étant le dossier dans lequel le disque RAM est monté.

Pour commencer, configurez un disque RAM dans le système d'exploitation.

Créez un dossier dans le Linux appelé /var/tmpfs

mkdir /var/tmpfs

Ensuite, ajoutez cette ligne à /etc/fstab (par exemple, si vous voulez un disque de 16 Go de RAM)

none                    /var/tmpfs              tmpfs   defaults,size=16g        1 2

et redémarrez le serveur.

Note : Il est possible de créer un disque RAM sans redémarrer. Rappelez-vous simplement de toujours ajouter la ligne susmentionnée à /etc/fstab pour avoir le disque RAM après un redémarrage du serveur.

Maintenant pour MySQL :

Ajouter cette ligne dans /etc/my.cnf

[mysqld]
tmpdir=/var/tmpfs

et redémarrez mysql.

OPTION #3 : Placez la table tmp sur le disque RAM dès que possible (en supposant que vous ayez appliqué l'OPTION #2 en premier).

Il se peut que vous souhaitiez forcer les tables tmp vers le disque RAM aussi rapidement que possible afin que MySQL ne s'essouffle pas à migrer de grandes tables tmp en mémoire vers un disque RAM. Ajoutez simplement ceci au fichier /etc/my.cnf :

[mysqld]
tmpdir=/var/tmpfs
tmp_table_size=2K

et redémarrez mysql. Cela fera que même la plus petite table temporaire sera créée directement dans le disque RAM. Vous pouvez périodiquement lancer ls -l /var/tmpfs pour voir les tables temporaires aller et venir.

Essayez-le ! !!

CAVEAT

Si vous ne voyez rien d'autre que des tables temporaires dans /var/tmpfs 24/7, cela pourrait avoir un impact sur la fonctionnalité/les performances du système d'exploitation. Pour vous assurer que /var/tmpfs n'est pas surpeuplé, essayez de régler vos requêtes. Une fois que vous l'aurez fait, vous devriez voir moins de tables temporaires se matérialiser dans /var/tmpfs.

Answer 2

Vous pouvez également ignorer la copie dans la table tmp. sur le disque partie (non traitée dans la réponse choisie)

1. Si vous évitez certains types de données :

Prise en charge des types de données de longueur variable (y compris BLOB et TEXT) non pris en charge par MEMORY.

de https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html (ou https://mariadb.com/kb/en/library/memory-storage-engine/ si vous utilisez mariadb).

2. Si votre table temporaire est suffisamment petite : comme indiqué dans la réponse choisie, vous pouvez

   Augmenter les variables tmp_table_size et/ou max_heap_table_size

Mais si vous divisez votre requête en requêtes plus petites (ne pas avoir la requête n'aide pas à analyser votre problème), vous pouvez la faire tenir dans une table temporaire en mémoire.