[a-h-aide] Mysql migration MyISAM et InnoDB

[David]

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


-------------- section suivante --------------
Une pièce jointe HTML a été nettoyée...
URL: <http://listes.auto-hebergement.fr/pipermail/auto-hebergement-aide/attachments/20130723/6118448b/attachment.html>

[Exca Flounty]

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

Post by David
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
------
[1]
http://www.mercereau.info/ispconfig-remplacer-amavisspamassasin-par-dspam/

[tranxene50]

Hello !

Post by David
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" ;)

Post by David
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).

Post by David
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-primer.sh

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).

Post by David
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

Post by David
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-size-in-mysql/

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+

[David]

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...

Post by tranxene50
Hello !

Post by David
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
Loading Image...._
Loading Image...._

Post by tranxene50

Post by David
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).

Post by David
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

Post by tranxene50
- 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

Post by tranxene50
-
https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
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...

Post by tranxene50
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).

Post by David
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
- l'application ne fonctionnera plus correctement
- les données seront corrompues/désynchronisées ou orphelines

Post by David
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-size-in-mysql/
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

[tranxene50]

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.

Post by David
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à !

[David]

Post by tranxene50
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.

Post by David
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 ?
---
[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