[a-h-aide] Mysql migration MyISAM et InnoDB
Bonjour à tous, Je cherche à gagner de la mémoire vive (car mon piti serveur part trop en SWAP) mon plus gros poste de dépense (après avoir viré SpamAssasin/Amavis pour Dspam <http://www.mercereau.info/ispconfig-remplacer-amavisspamassasin-par-dspam/>) est maintenant Mysql. Après plusieurs recherches sur le Web de l'internet et plusieurs tests je me suis rendu compte que InnoDB était très gourmand en RAM Consommation mémoire sur un serveur en désactivant InnoDB : 22048 Consommation mémoire sur un serveur avec InnoDB : 380792 J'ai malheureusement énormément de table en InnoDB, j'ai trouvé des scripts pour automatiser la migration vers MySAM mais j'ai peur de foutre le bordel monstre (Hode, TinyTinyRSS, Joomla, Wordpress... comment ça va réagir ?) Donc je voulais, par ce présent email être rassuré ou lynché pour cette migration Note : j'ai déjà joué beaucoup avec skip-locking, table_open_cache, sort_buffer_size... mais si vous avez des conseils je prends aussi. David
Afficher les réponses par date
Salut David. InnoDB intègre des mécanismes qui vont répercuter les suppressions de certaines tables dans d'autres, mais aussi les modifications (via les clé primaires d'une table utilisées comme clés externes d'autres tables). Par exemple, si tu as une table qui contient des noms d'utilisateurs, et une autre qui dit quel article a été écrit par qui, un InnoDB bien configuré est capable de répercuter un changement de nom d'utilisateur dans chacune des autres tables où il apparaît. Ça simplifie énormément la structure d'une base de données et la manière de gérer ses enregistrements quand on programme un site web, mais effectivement ça peut créer un bordel monstre si ces mécanismes sont utilisés et que tu les désactive. C'est quoi les scripts que tu as trouvé ? À+ --- -- Exca -- http://www.sychlora.com Le 2013-07-23 09:55, David a écrit :
Bonjour à tous,
Je cherche à gagner de la mémoire vive (car mon piti serveur part trop en SWAP) mon plus gros poste de dépense (après avoir viré SpamAssasin/Amavis pour Dspam [1]) est maintenant Mysql. Après plusieurs recherches sur le Web de l'internet et plusieurs tests je me suis rendu compte que InnoDB était très gourmand en RAM
Consommation mémoire sur un serveur en désactivant InnoDB : 22048 Consommation mémoire sur un serveur avec InnoDB : 380792
J'ai malheureusement énormément de table en InnoDB, j'ai trouvé des scripts pour automatiser la migration vers MySAM mais j'ai peur de foutre le bordel monstre (Hode, TinyTinyRSS, Joomla, Wordpress... comment ça va réagir ?) Donc je voulais, par ce présent email être rassuré ou lynché pour cette migration
Note : j'ai déjà joué beaucoup avec skip-locking, table_open_cache, sort_buffer_size... mais si vous avez des conseils je prends aussi.
David
Links: ------ [1]
http://www.mercereau.info/ispconfig-remplacer-amavisspamassasin-par-dspam/
Hello !
Je cherche à gagner de la mémoire vive (car mon piti serveur part trop en SWAP) [...]
www.linuxatemyram.com Que racontent "free -m" et "cat /proc/meminfo" ? Tu peux configurer "l'agressivité" de la SWAP directement avec /proc/sys/vm/swappiness ("vm.swappiness = XX" dans /etc/sysctl.conf) https://en.wikipedia.org/wiki/Swappiness Essaye avec une valeur de 10, démonte la swap (swapoff -va) et remonte la (swapon -va). Attention, le système peut "freezer" pendant plusieurs secondes lors du démontage et, pire cas de figure, si vraiment les applis consomment plus de RAM que celle réellement disponible, "OOM killer" sera de sortie (et ça, c'est pas bon...) Bref, avant : "free -m" et "cat /proc/meminfo" ;)
Après plusieurs recherches sur le Web de l'internet et plusieurs tests je me suis rendu compte que InnoDB était très gourmand en RAM
Mmmmh... Certes, InnoDB a une empreinte mémoire légèrement plus élevée que MyISAM mais, dans les faits, ce n'est pas un problème. En fait, j'aurais plutôt envie de te conseiller l'inverse : tout passer en InnoDB (non, ce n'est pas un troll) La contention (row locking) est meilleure, tu disposeras de toutes les fonctionnalités (contraintes d'intégrité, transactions, isolation, etc) et tu pourras faire des backups fiables (car le dump sera atomique).
Consommation mémoire sur un serveur en désactivant InnoDB : 22048 Consommation mémoire sur un serveur avec InnoDB : 380792
Sans voir ton fichier de conf (/etc/mysql/my.cnf), on ne peut rien déduire mais à priori c'est lié à ta configuration. Est-ce que tu peux télécharger et lancer : - https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl - https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-prim... Si MySQL ne tourne pas depuis 48 heures, les scripts vont "râler" mais ce n'est pas bien grave. Pour faire simple, MySQL une fois configuré consommera la mémoire que tu lui aura alloué, quelle que soit la taille de tes bases de données. Si tu mets une valeur trop basse, tu auras beaucoup d'entrées/sorties à cause des lectures sur le disque et, si tu mets une valeur trop haute, MySQL va s'accaparer autant de mémoire qu'il le peut sans pour autant atteindre la limite supérieure donc cela ne sert à rien. En fait, tout dépend du nombre de connexions simultanées que tu veux autoriser et de la mémoire que tu souhaites allouer au buffer InnoDB ainsi qu'au cache (en supposant que ce soit vraiment utile).
J'ai malheureusement énormément de table en InnoDB, j'ai trouvé des scripts pour automatiser la migration vers MySAM mais j'ai peur de foutre le bordel monstre (Hode, TinyTinyRSS, Joomla, Wordpress... comment ça va réagir ?) Donc je voulais, par ce présent email être rassuré ou lynché pour cette migration
Je confirme ce que dit Exca : si une application repose sur des contraintes d'intégrité (typiquement les DELETE en CASCADE), basculer en MyISAM va engendrer deux choses : - l'application ne fonctionnera plus correctement - les données seront corrompues/désynchronisées ou orphelines
Note : j'ai déjà joué beaucoup avec skip-locking, table_open_cache, sort_buffer_size... mais si vous avez des conseils je prends aussi.
Gare au sort_buffer_size ! (mal configuré c'est un gouffre à RAM) http://www.mysqlperformanceblog.com/2010/10/25/impact-of-the-sort-buffer-siz... skip-external-locking : pas de souci tant que tu n'as qu'un seul serveur MySQL qui accède aux données physiques des BDD (variable "datadir"). table_open_cache : tout dépend du nombre de tes tables (actives). A+ -- tranxene50 tranxene50@developpeur-neurasthenique.fr
Bonjour et merci pour vos réponses, Vous m'avez dissuadé de faire cette connerie :-p du coup je vais me concentrer à optimiser ma conf mysql... @Exca : je n'ai pas retrouvé mais c'était juste des moulinettes bash Alter table brutale... @tranxene50 : Le 23/07/2013 21:48, tranxene50 a écrit :
Hello !
Je cherche à gagner de la mémoire vive (car mon piti serveur part trop en SWAP) [...] www.linuxatemyram.com
Que racontent "free -m" et "cat /proc/meminfo" ?
Tu peux configurer "l'agressivité" de la SWAP directement avec /proc/sys/vm/swappiness ("vm.swappiness = XX" dans /etc/sysctl.conf)
https://en.wikipedia.org/wiki/Swappiness
Essaye avec une valeur de 10, démonte la swap (swapoff -va) et remonte la (swapon -va).
Attention, le système peut "freezer" pendant plusieurs secondes lors du démontage et, pire cas de figure, si vraiment les applis consomment plus de RAM que celle réellement disponible, "OOM killer" sera de sortie (et ça, c'est pas bon...)
Bref, avant : "free -m" et "cat /proc/meminfo" ;)
J'ai oublié de précisé que c'est un VPS OpenVZ donc pas certain que Swappiness soit paramétrable à l'intérieur de la VM... !? (en tous cas je n'ai pas la possibilité de démonter/remonter ma swap, ni même d'en ajouter via du swapfile...) http://pastebin.com/zYft6Eu8 http://dl.zici.fr/filestore/28/Mhq9zevQmh.memory-day.png._ http://dl.zici.fr/filestore/70/wqzzY8Vslf.memory-week.png._
Après plusieurs recherches sur le Web de l'internet et plusieurs tests je me suis rendu compte que InnoDB était très gourmand en RAM Mmmmh... Certes, InnoDB a une empreinte mémoire légèrement plus élevée que MyISAM mais, dans les faits, ce n'est pas un problème.
En fait, j'aurais plutôt envie de te conseiller l'inverse : tout passer en InnoDB (non, ce n'est pas un troll)
La contention (row locking) est meilleure, tu disposeras de toutes les fonctionnalités (contraintes d'intégrité, transactions, isolation, etc) et tu pourras faire des backups fiables (car le dump sera atomique).
Consommation mémoire sur un serveur en désactivant InnoDB : 22048 Consommation mémoire sur un serveur avec InnoDB : 380792 Sans voir ton fichier de conf (/etc/mysql/my.cnf), on ne peut rien déduire mais à priori c'est lié à ta configuration.
Le voici : http://pastebin.com/QY61NfUc N'étant pas très pointu en config mysql je me suis certainement basé à l'époque sur ce tuto : http://wiki.vpslink.com/Low_memory_MySQL_/_Apache_configurations
Est-ce que tu peux télécharger et lancer :
- https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Cool ces scripts, je connaissais pas c'est parfais !!! Résultat : http://pastebin.com/Z003FKMF
- https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-prim...
Si MySQL ne tourne pas depuis 48 heures, les scripts vont "râler" mais ce n'est pas bien grave.
Pour faire simple, MySQL une fois configuré consommera la mémoire que tu lui aura alloué, quelle que soit la taille de tes bases de données.
Si tu mets une valeur trop basse, tu auras beaucoup d'entrées/sorties à cause des lectures sur le disque et, si tu mets une valeur trop haute, MySQL va s'accaparer autant de mémoire qu'il le peut sans pour autant atteindre la limite supérieure donc cela ne sert à rien.
Dans mon cas je penses qu'il est nécessaire de diminuer l’occupation mémoire et de faire plus d'I/O sur disque...
En fait, tout dépend du nombre de connexions simultanées que tu veux autoriser et de la mémoire que tu souhaites allouer au buffer InnoDB ainsi qu'au cache (en supposant que ce soit vraiment utile).
J'ai malheureusement énormément de table en InnoDB, j'ai trouvé des scripts pour automatiser la migration vers MySAM mais j'ai peur de foutre le bordel monstre (Hode, TinyTinyRSS, Joomla, Wordpress... comment ça va réagir ?) Donc je voulais, par ce présent email être rassuré ou lynché pour cette migration Je confirme ce que dit Exca : si une application repose sur des contraintes d'intégrité (typiquement les DELETE en CASCADE), basculer en MyISAM va engendrer deux choses :
- l'application ne fonctionnera plus correctement - les données seront corrompues/désynchronisées ou orphelines
Note : j'ai déjà joué beaucoup avec skip-locking, table_open_cache, sort_buffer_size... mais si vous avez des conseils je prends aussi. Gare au sort_buffer_size ! (mal configuré c'est un gouffre à RAM)
http://www.mysqlperformanceblog.com/2010/10/25/impact-of-the-sort-buffer-siz...
skip-external-locking : pas de souci tant que tu n'as qu'un seul serveur MySQL qui accède aux données physiques des BDD (variable "datadir").
table_open_cache : tout dépend du nombre de tes tables (actives).
A+
Je vais essayer de tuner mon my.cnf ! Si vous avez d'autres conseils poussés au vu des éléments supplémentaires que j'ai apporté n'hésitez pas ! Merci bien, David -- http://www.zici.fr http://www.mercereau.info
Hello ! Alors, déjà, ton VPS va bien ! :) En fait, pas tout à fait : tu as grosso-mode 200/300 Mo de RAM inutilisé et ça, c'est mal... La mémoire qui ne sert pas, c'est de RAM perdue. Le fait que le VPS swappe est également normal : par défaut, Linux va déposer en SWAP les données qui ne sont que très rarement utilisé. Cela n'affecte pas les performances, bien au contraire : seules les données "chaudes" restent en RAM. Concernant OpenVZ, la SWAP n'est pas forcément stockée sur le disque, c'est le principe de la "VSwap" : http://openvz.org/VSwap Tant que le serveur hôte dispose de suffisamment de RAM, la VSwap est, justement, stockée en RAM. (Mais OpenVZ va ralentir artificiellement les entrées/sorties, pour simuler la swap "normale") Si le serveur hôte commence a être surchargé, alors la VSwap passe sur le disque.
J'ai oublié de précisé que c'est un VPS OpenVZ donc pas certain que Swappiness soit paramétrable à l'intérieur de la VM... !?
Seul le serveur hôte peut accéder/modifier ces paramètres, pas possible au sein du VPS. Si c'est pas indiscret, quel est ton hébergeur ? --- Pour MySQL, je te propose de "consommer" 270 Mo de RAM, grand maximum : [mysqld] skip-external-locking skip-name-resolve skip-symbolic-links # --- Log general_log = 0 slow_query_log = 1 long_query_time = 1 log_queries_not_using_indexes = 0 log_warnings = 2 # --- Network back_log = 128 max_connections = 48 max_user_connections = 16 connect_timeout = 60 interactive_timeout = 900 wait_timeout = 3600 # --- Conf default-storage_engine = InnoDB local_infile = 0 secure_auth = 1 # --- Limits max_allowed_packet = 16M max_heap_table_size = 32M tmp_table_size = 32M join_buffer_size = 256K sort_buffer_size = 1M table_definition_cache = 512 table_open_cache = 1024 # --- Threads slow_launch_time = 1 thread_cache_size = 16 # --- Cache query_cache_type = 1 query_cache_size = 16M query_cache_limit = 1M query_cache_min_res_unit = 1k # --- MyISAM key_buffer = 16M # --- InnoDB ignore-builtin-innodb innodb-status-file = 1 # Attention, tout doit être sur une seule ligne ! plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so innodb_additional_mem_pool_size = 8M innodb_buffer_pool_size = 128M innodb_file_format = Barracuda innodb_file_format_check = Barracuda innodb_lock_wait_timeout = 10 innodb_log_buffer_size = 8M innodb_open_files = 1024 innodb_rollback_on_timeout = 1 [myisamchk] key_buffer = 16M [mysql] no-auto-rehash [mysqldump] max_allowed_packet = 64M --- Juste au cas où : sauvegarde des bases de données et de ton ancien fichier de conf. Tu laisses tourner deux/trois jours, sur Munin la consommation de RAM va augmenter (couleur verte) mais c'est tout à fait normal. Et ensuite, rebelote : mysqltuner.pl & tuning-primer.sh. Encore indiscret : c'est pour zici.fr ? Voilà ! -- tranxene50 tranxene50@developpeur-neurasthenique.fr
Le 24/07/2013 22:13, tranxene50 a écrit :
Hello !
Alors, déjà, ton VPS va bien ! :)
En fait, pas tout à fait : tu as grosso-mode 200/300 Mo de RAM inutilisé et ça, c'est mal...
La mémoire qui ne sert pas, c'est de RAM perdue.
Le fait que le VPS swappe est également normal : par défaut, Linux va déposer en SWAP les données qui ne sont que très rarement utilisé.
Cela n'affecte pas les performances, bien au contraire : seules les données "chaudes" restent en RAM.
Concernant OpenVZ, la SWAP n'est pas forcément stockée sur le disque, c'est le principe de la "VSwap" : http://openvz.org/VSwap
Tant que le serveur hôte dispose de suffisamment de RAM, la VSwap est, justement, stockée en RAM. (Mais OpenVZ va ralentir artificiellement les entrées/sorties, pour simuler la swap "normale")
Si le serveur hôte commence a être surchargé, alors la VSwap passe sur le disque.
J'ai oublié de précisé que c'est un VPS OpenVZ donc pas certain que Swappiness soit paramétrable à l'intérieur de la VM... !? Seul le serveur hôte peut accéder/modifier ces paramètres, pas possible au sein du VPS.
Si c'est pas indiscret, quel est ton hébergeur ?
---
Pour MySQL, je te propose de "consommer" 270 Mo de RAM, grand maximum :
[mysqld] skip-external-locking skip-name-resolve skip-symbolic-links
# --- Log
general_log = 0
slow_query_log = 1 long_query_time = 1
log_queries_not_using_indexes = 0 log_warnings = 2
# --- Network
back_log = 128 max_connections = 48 max_user_connections = 16
connect_timeout = 60 interactive_timeout = 900 wait_timeout = 3600
# --- Conf
default-storage_engine = InnoDB
local_infile = 0 secure_auth = 1
# --- Limits
max_allowed_packet = 16M max_heap_table_size = 32M tmp_table_size = 32M
join_buffer_size = 256K sort_buffer_size = 1M
table_definition_cache = 512 table_open_cache = 1024
# --- Threads
slow_launch_time = 1 thread_cache_size = 16
# --- Cache
query_cache_type = 1 query_cache_size = 16M query_cache_limit = 1M
query_cache_min_res_unit = 1k
# --- MyISAM
key_buffer = 16M
# --- InnoDB
ignore-builtin-innodb
innodb-status-file = 1
# Attention, tout doit être sur une seule ligne ! plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
innodb_additional_mem_pool_size = 8M innodb_buffer_pool_size = 128M innodb_file_format = Barracuda innodb_file_format_check = Barracuda innodb_lock_wait_timeout = 10 innodb_log_buffer_size = 8M innodb_open_files = 1024 innodb_rollback_on_timeout = 1
[myisamchk] key_buffer = 16M
[mysql] no-auto-rehash
[mysqldump] max_allowed_packet = 64M
---
Juste au cas où : sauvegarde des bases de données et de ton ancien fichier de conf.
Tu laisses tourner deux/trois jours, sur Munin la consommation de RAM va augmenter (couleur verte) mais c'est tout à fait normal.
Et ensuite, rebelote : mysqltuner.pl & tuning-primer.sh.
Encore indiscret : c'est pour zici.fr ?
Voilà !
Bonjour, Merci tranxene50, à chacun de tes emails je me couche moins bête :-) Je vais tester et potasser tes conseils... Pour tes questions indiscrètes : - L'hébergeur c'est OVH pour cette VM, pour encore 2, 3 mois, je vais (re)passer "à la maison" très bientôt ; - Et oui c'est pour Zici - qui est vieillissant - (mais pas que) ; Merci encore, David -- http://www.zici.fr http://www.mercereau.info
participants (3)
-
David -
Exca Flounty -
tranxene50