Cette page de 2009 reste d'actualité (au moins pour les tables MyISAM) et les remarques et conseils peuvent s'appliquer à la variable "innodb_buffer_size" qui remplit à peu près la même fonction pour InnoDB.

Sur quelle information optimiser la valeur de ce paramètre ? 
Ce paramètre est réservé aux tables "MyIsam", de plus en plus rares au profit des tables InnoDB.

KEY_BUFFER = taille réservée au buffer des index. Sa taille doit être assez importante, l'idéal est que tous les index résident en mémoire limitant ainsi les IO. 
Vous pouvez calculer cette taille en évaluant la taille des index de votre base de données. L'utilisation de MySQL-Admin vous permet de suivre "en direct" l'évolution de l'utilisation de ce cache. Voir dans la section "Health", onglet "Memory Health" de MySQL-Admin. La partie "Key efficiency" vous permet de voir la partie du buffer effectivement utilisée ainsi que le "hitrate". 
Le "hitrate" est le pourcentage de clés effectivement lues depuis le buffer. 
Attention : Ne pas tenir compte des valeurs présentes peu après un redémarrage, il faut attendre plusieurs heures que le fonctionnement de la base se stabilise et que l'utilisation corresponde à l'utilisation moyenne normale des applications liées à cette base.

INNODB_BUFFER_POOL_SIZE = taille réservée au buffer Innodb (data + index) 
Plus la valeur est élevée, meilleur est le fonctionnement de la base ... La mémoire étant bien moins rare qu'il y a quelques années on peut mettre ce qu'il faut. 
Si on a assez de mémoire pour contenir la taille de la base ne pas hésiter, mais calculer au moins le volume des tables les plus accédées plus 20 ou 30%.

INNODB_BUFFER_POOL_INSTANCES = Nombre de "sous pools" 
Une valeur de 1 par Gigaoctet de POOL_SIZE est à considérer comme correcte, mais mettre une valeur proche de celle du nombre de coeurs de processeur est une option à ne pas négliger.

• Des buffers trop petits --> hitrate bas et :
  •     temps de réponse allongé
  •     IO plus élevées
• Des buffers trop grands --> mémoire gachée.

Il n'est pas la peine de mobiliser 8 Gigaoctets de mémoire si seulement 50% sont utilisés, la mémoire économisée ici peut avec profit être utilisée ailleurs, c'est le cas de la plupart des paramètres de mémoire, trop petits cela ralentit, trop grands cela utilise inutilement de la mémoire. 
Si votre base est "petite" par rapport à la mémoire mettez ce qu'il faut, sur une machine spécialisée "base de données" 75/80% de la mémoire est parfait pour InnoDB. 
Toutefois si l'ensemble de vos tables ne "pèse" que 30 Go il est inutile de réserver 64 Go de mémoire !

TABLE_OPEN_CACHE = nombre de tables en cache. 
Une cette valeur égale au nombre de tables si cela est possible. Note : on trouve un tas de choses à ce sujet ... 
Une taille peu différente du nombre de tables de la base semble en général favorable.

Un autre point à surveiller est la mémoire affectée aux tris et autres zones temporaires (TMP_TABLE_SIZE et MAX_HEAP_TABLE_SIZE). Si certaines requêtes ramènent beaucoup de lignes à trier cela nécessite de la place et, là aussi, affecter un peu de mémoire n'est pas un luxe. En général 64 à 320Mo sont suffisants, surveiller le pourcentage de tables temporaires sur disque. La valeur de READ_IO_THREADS et WRITE_IO_THREADS peut être au minimum de 2, mais sur une machine multiprocesseurs aller presque jusqu'au nombre de coeurs est une bonne pratique ... 
La taille des READ, SORT et JOIN BUFFER_SIZE est à suivre et des valeurs de 2 à 16M sont en général adaptées.

Mais il faut aussi penser à l'activité de création/modification de la base et affecter un minimum de mémoire au INNO_DB_LOG_BUFFER_SIZE afin de fluidifier les écritures sur le log. 
A propos de accès disques, plus ils sont rapides, mieux c'est. L'utilisation de SSD permet d'atteindre des vitesses impressionnantes et la mise en RAID 1 permet d'augmenter encore ce type de performance avec toutefois un certain risque, le RAID 10 est parfait. Voir ici l'influence de la variable INNODB_IO_CAPACITY qui permet à la base d'optimiser son fonctionnement en fonction de la vitesse des I/O, en général de 100 à 1000 pour des disques standard et > 10000 pour des SSD. 
Sur des systèmes largement multi-processeurs on peut aussi jouer sur les paramètres INNODB_READ_IO_THREADS et INNODB_WRITE_IO_THREADS et les augmenter à 2,3 ... afin d'accélérer au maximum les IO, base en lecture --> forcer le nombre de READ_IO_THREADS, si beaucoup d'écritures forcer le nombre de WRITE_IO_THREADS. Toutefois si vous n'avez pas de SSD ou de multiples disque de données cela peut être très peu efficace. 
Pour l'ajustement de ces valeurs suivre l'évolution des compteurs et ratios fournis par un système de suivi de performance tel que Shinken + Graphite/InfluxDB ou d'autres produits non libres, Mysql Workbench fournit aussi l'accès à beaucoup d'informations utiles.

Erreur mise à jour MariaDB. 
Mise à jour de  10.1.23-9+deb9u1 vers 10.1.26-0+deb9u1 
Lors de cette mise à jour normale par apt-get upgrade l'installation s'est "plantée" avec la trace suivante : 
regarding mariadb-server-10.1_10.1.26-0+deb9u1_amd64.deb containing mariadb-server-10.1, pre-dependency problem: 
mariadb-server-10.1 pre-depends on mariadb-common (>= 10.1.26-0+deb9u1) 
mariadb-common latest configured version is 10.1.23-9+deb9u1. 
...... 
update-alternatives: error: alternative path /etc/mysql/mariadb.cnf doesn't exist 

Une solution : 
cd /etc/mysql 
ln -s ./mariadb.conf.d/50-server.cnf ./mariadb.cnf 

Quand on relance l'installation du paquet "fautif" on obtient : 
dpkg -i mariadb-common_10.1.26-0+deb9u1_all.deb 
(Reading database ... 192496 files and directories currently installed.) 
Preparing to unpack mariadb-common_10.1.26-0+deb9u1_all.deb ... 
Unpacking mariadb-common (10.1.26-0+deb9u1) over (10.1.26-0+deb9u1) ... 
Setting up mariadb-common (10.1.26-0+deb9u1) ... 
update-alternatives: warning: forcing reinstallation of alternative /etc/mysql/mariadb.cnf because link group my.cnf is broken

Le paquet s'est bien installé, voyons voir l'installation des paquets "client" : 
dpkg -i mariadb-client*deb 
(Reading database ... 192496 files and directories currently installed.) 
Preparing to unpack mariadb-client-10.1_10.1.26-0+deb9u1_amd64.deb ... 
Unpacking mariadb-client-10.1 (10.1.26-0+deb9u1) over (10.1.26-0+deb9u1) ... 
Preparing to unpack mariadb-client-core-10.1_10.1.26-0+deb9u1_amd64.deb ... 
Unpacking mariadb-client-core-10.1 (10.1.26-0+deb9u1) over (10.1.26-0+deb9u1) ... 
Setting up mariadb-client-core-10.1 (10.1.26-0+deb9u1) ... 
Setting up mariadb-client-10.1 (10.1.26-0+deb9u1) ... 
Processing triggers for man-db (2.7.6.1-2) ... 
Processing triggers for menu (2.1.47+b1) ... 

C'est OK et les paquets "serveur" de MariaDB s'installent eux aussi correctement et une connexion à la base s'effectue normalement.

Le plugin "Federatedx" responsable de la gestion des tables "federated" est livré en standard avec MariaDB mais n'est pas activé par défaut, le paramètre "federated", si présent dans les fichiers de configuration, provoque une erreur.

Pour quelques détails sur les tables "federated" voir ici. 

L'import sans précaution d'une base comprenant des tables "federated" ne donne pas les résultats escomptés, les tables sont bien créées, mais avec le type "InnoDB" ce qui n'est pas le but recherché. 
En effet l'objet "serveur" auquel sont rattachées les tables "federated" ne fait pas partie de l'export.

L'activation est très simple : 
Dans une console "mysql" administrateur : 
INSTALL SONAME 'ha_federatedx'; 
Ensuite jouter dans le fichier de config : 
federated 
On redémarre et c'est OK. 
Remarque ; 
Même en cas d'export/import la création du "serveur" doit être exécutée sur la nouvelle machine avant toute tentative de création d'une table "federated" sinon celle-ci sera créée en ="InnoDB".

Ce script de :  Nathaniel Hendler   permet d'analyser finement le fameux "mysql-slow.log". 
Rappel : 
L'enregistrement des requêtes lentes ou des requêtes sans index se paramètre dans le fichier "my.cnf" : 

# Here you can see queries with especially long duration 
log_slow_queries    = /var/log/mysql/mysql-slow.log 
long_query_time = 2 
log-queries-not-using-indexes 

Ici les lignes sont décommentées afin d'activer l'enregistrement. 
Attention, assez consommateur de ressources,peu conseillé sur des machines de production qui ont déjà des problèmes de temps de réponse ..... 

Una analyse visuelle de ce fichier peut être intéressante mais devient rapidement rébarbative, heureusement "mysql_slow_log_parser.pl" est arrivé. 

Ce petit script Perl effectue un regroupement des "queries" coûteux en fournissant pour chaque query identifié :

• le nombre d'occurences
• le temps total utilisé et le temps moyen
• le nombre de rangs analysés
• un "modèle" du query, dépourvu des variables
• un exemple de query avec les valeurs des variables

Ces résultats sont une mine d'or pour les développeurs car tous les ordres SQL critiques (criticables) apparaissent en clair avec même un exemple à soumettre à "EXPLAIN". 
L'utilisation de ce module aide à repérer :

• les index manquants ou non judicieux
• les requêtes mal faites ...

Le comptage de chaque type de requête permet de se pencher d'abord sur les plus critiques (les plus nombreuses/coûteuses). 

En bref un très bon outil que l'on peut trouver facilement sur Internet.

J'ai rencontré un problème bizarre lors d'une mise à jour de MariaDB.

La mise à jour se passe mal et l'installation du paquet "mariadb-server-10.1_10.1.26-0+deb9u1_amd64.deb" échoue avec le message : 
E: mariadb-server-10.1: subprocess installed post-installation script returned error exit status 2

En examinant les logs on trouve un message : 
[ERROR] /usr/sbin/mysqld: unknown option '--federated' 
[ERROR] Aborting 

Si on commente dans le fichier "50-server.cnf" la ligne "federated" le démarrage se passe bien. 
Il faut alors terminer la mise à jour "ratée" ... 
On peut alors dé-commenter la ligne "federated" et relancer MariaDB.

​Et cela fonctionne.

En effet le plugin "federated" a été déclaré obsolete et doit être remplacé par "ha_federatedx" ou il faut utiliser mariadb-connect ou FederatedX.

Le partitionnement c'est parfois très commode et cela permet de débarrasser une base opérationnelle de données "anciennes" qui sont mieux placées dans des archives. 
C'est ce qui a été fait chez un client où les données d'opération sont reportées dans des tables d'archives dès qu'elles sont jugées inutiles aux opérations courantes, elles restent ainsi disponibles pour des statistiques, des études commerciales et ... 
Toutefois au bout de quelques mois elles sont utilisées de plus en plus rarement et leur présence n'est plus justifiée dans la base opérationnelle.  
Le partitionnement par semaine/mois permet de se débarrasser facilement et rapidement de ces données par un simple "ALTER TABLE TRUNCATE PARTITION XXXXX" qui supprime l'intégralité du contenu en quelques instants au lieu d'heures à défiler des ordres "DELETE. 
Or lors de la création d'une de ces tables d'archives une petite erreur de frappe s'est glissée, le nom des partitions comporte une référence directe au numéro de mois. Les partitions sont donc nommées de P01 à P12, un processus automatique supprime les partitions du mois N avec un ordre de troncature de la partition "PN", la faute de frappe concernait la partition "P11" malheureusement orthographiée "P21" ce qui aurait provoqué le plantage du processus d'archivage/purge lorsqu'il aurait voulu traiter la partitions P11 associée au mois 11. 
Mais quel ordre SQL permet de faire cela ? Eh bien non il n'y a pas d'ordre dédié au renommage de partitions ... il faut utiliser la réorganisation de partitions avec l'ordre suivant :

Et le tour est joué en ... quelques temps, car ici la partition contient plus de 50 millions de rangs et occupe environ 8Go, mais cela s'est bien exécuté en un temps raisonnable :

Oui, c'est une belle base avec une activité certaine dont voici quelques chiffres tirés de la fin d'un "SHOW ENGINE INNODB STATUS" :

Vous avez perdu le mot de passe "root" de votre belle installation de MariaDB !

Note 2021 : Cela ne fonctionne plus avec les versions récentes de MariaDB car la table mysql.user n'est plus qu'une vue vers une structure plus complexe, il faut obligatoirement passer par un "init_file" pour re-créer un mot de passe "root". Ce fichier "init_file" peut être exécuté au démarrage de MariaDB, avant le démarrage complet, et permet d'exécuter des commandes en passant outre à la vérification des droits. A ne pas laisser entre toutes les mains ... et à bien supprimer du fichier de paramétrage après usage !

Pas de panique la procédure suivante vous sortira de l'embarras (à condition de stopper votre base quelques instants ce qui peut ne pas être évident pour une base de production ... mais pour celles la vous avez soigneusement sauvegardé les mots de passe dans un gestionnaire spécialisé).

• Stopper MariaDB
• En root lancer lé commande suivante :

mysqld_safe --skip-grant-tables --skip-networking & 
Ici aucun accès ne sera possible aux commandes "classiques" "SET PASSWORD..." ou "ALTER USER ..." il faut mettre à jour directement la table "mysql.user" :

mysql -u root 
UPDATE mysql.user SET authentication_string = PASSWORD('nouveau_mot_de_passe')  
where user = 'root' 
and host = 'localhost'; 
commit; 
\q

Il suffira alors de stopper (gentiment) notre "mysqld_safe" avec la commande : 
mysqladmin -u root shutdown

Puis de relancer MariaDB avec systemctl et se retrouver dans un état normal.

Ceci fonctionne parfaitement avec un MariaDB 10.3