En utilisant mysql-report, on peut obtenir des renseignements très utiles sur la consommation des ressources MySQL. Il suffit de suivre les ressources qui montent trop vite à la valeur maximale et puis les augmenter progressivement pour optimiser la configuration de MySQL.

Avec WPMU, je me suis aperçu que les upgrades sollicitaient beaucoup le serveur et le démon mysql.

Au point que parfois le serveur Dedibox partait en vrille! La seule méthode pour récupérer le serveur était un reboot matériel…

Il fallait donc optimiser la montée en charge de MySQL.

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_open-files-limit

WARNING: si on laisse aux threads de MySQL la liberté d'ouvrir trop de fichiers, ça peut planter sévèrement le serveur entier…

Il faut commencer par bien comprendre comment le serveur MySQL opère pour gérer les requêtes envoyées par PHP.

Il y a un seul serveur MySQL, en tant que process, qui gère l'ensemble des requêtes entrantes.

Puis ce process utilise des threads pour accéder aux fichiers de données et traiter les requêtes.

On a alors 3 niveaux de gestion de la mémoire:

• la mémoire globale du process
• la mémoire des threads
• la mémoire des requêtes

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

En utilisant mysqlreport, on peut suivre les performances du serveur MySQL.

Par exemple, il est possible d'ajouter une tache cron pour recevoir un rapport périodiquement (toutes les heures ?) et suivre l'évolution des performances du serveur MySQL.

http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

• key_buffer_size
• table_cache

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

WARNING: La valeur par défaut est 64. Mais cela ne représente pas la valeur des file descriptor Linux utilisés!!!

Sous MySQL, une table ouverte peut représenter jusqu'à 5 ou 10 “file descriptor” soit pour linux 5 ou 10 fichiers ouverts.

Pour connaître la limite unix il faut regarder les commandes et fichiers:

ulimit -n
ulimit -a
cat /etc/security/limits.conf

Généralement la valeur par défaut est 1024.

Le process MySQL démarre sous son propre user mysql. Il faut alors modifier /etc/security/limits.conf pour paramétrer la bonne valeur.

Mais mysql démarre aussi avec son propre paramètre open-files-limit!!! Il faut aussi ajuster cette valeur!!!

Le script /usr/bin/mysqld_safe gère le lancement du démon mysqld

mysqld_safe:46:  --open-files-limit=LIMIT   Limit the number of open files
mysqld_safe:185:      --open-files-limit=*) open_files="$val" ;;
mysqld_safe:397:  if test -n "$open_files"
mysqld_safe:399:    ulimit -n $open_files
mysqld_safe:400:    append_arg_to_args "--open-files-limit=$open_files"

http://dev.mysql.com/doc/refman/5.1/en/mysqld-safe.html

Il semble possible de configurer cette valeur dans le fichier my.cnf

[mysqld_safe]
open-files-limit=4096

Et pour savoir la valeur prise en compte par MySQL, il faut regarder un “show variables”

mysql> show variables;

Enfin, il faut tenir compte de la mémoire RAM du serveur: si on prend en gros 1Mo pour une table, 1.000 tables ouvertes vont prendre 1Go de mémoire… Un process apache2 prend entre 10Mo et 15Mo, 100 process apache2 vont alors occuper entre 1Go et 1.5Go de mémoire.

WARNING: il ne faut surtout passer en mode “memory swap”, c'est à dire utiliser plus que la mémoire RAM disponible. Les performances deviennent alors très lentes et l'administrateur perd la main sur tout le système…

L'utilisation par MySQL des tables peut étonner: chaque thread va ouvrir ses propres tables. Mais pour un thread, une table ne sera chargée qu'une fois en mémoire. Par contre, les “file descriptor” seront utilisés pour compter le nombre de fois que la table est “ouverte”.

Quelques exemples:

5 visiteurs pour une page d'accueil ⇒ 5 connections MySQL ⇒ 5 threads ⇒ 5x le nombre de tables ouvertes pour la page d'accueil!!!

WordPress utilise en gros 10 tables MySQL. Si on considère qu'elles sont toutes consultées pour la page d'accueil et qu'on a 100 personnes qui viennent en même temps:

100 connections x 10 tables ⇒ 1.000 tables ouvertes ⇒ entre 5.000 et 10.000 fichiers comptés ouverts

1.000 connections x 10 tables ⇒ 10.000 tables ouvertes ⇒ entre 50.000 et 100.000 fichiers comptés ouverts

Si on considère des sites assez fournis en contenu, on peut prendre une taille moyenne de 1Mo pour chaque table. Ce qui donnerait:

• 100 connections ⇒ 1.000 tables ouvertes ⇒ 1Go de mémoire RAM utilisée
• 1.000 connections ⇒ 10.000 tables ouvertes ⇒ 10Go de mémoire RAM utilisée

Note: En réalité, il n'y a pas un thread par connection; chaque thread peut gérer une liste de connections. Le nombre de tables ouvertes devrait donc être moindre…

http://highervisibilitywebsites.com/node/36

For a setup with 500mb or ram paste this in your my.cnf file:

[mysqld]
max_connections = 800
max_user_connections = 800
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 999999
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M

For a system with 256mb of ram:

[mysqld]
max_connections=500
max_user_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 999999
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M

* To start mysql server:

# /etc/init.d/mysql start

* To stop mysql server:

# /etc/init.d/mysql stop

To restart mysql server: Mais le serveur entier est parti en vrille peu après le restart ???

# /etc/init.d/mysql restart

Cet article explique quelle mémoire est partagée entre les threads et quelle mémoire est allouée pour chacun des threads !

http://dev.mysql.com/doc/refman/5.0/en/memory-use.html