BdD : Optimiser l'espace occupé (tables innodb)
Sur un des serveurs 1and1 (Ionos) hébergeant une de mes bases de données, je me suis heurté à un problème de dépassement de capacité de stockage avec pour conséquence le verrouillage des accès utilisateurs à mon site. Le manque de clarté de l'aide en ligne de mon hébergeur sur ce sujet, m'a conduit à l'écriture de ce paragraphe.
En effet, il s'agit pour optimiser l'espace occupé par les tables de la BdD, d'exécuter la requête affichée dans l'onglet " Lister table" ci-dessous, et cela de manière automatique avec le service CRON fourni par ce serveur.
1. Les requêtes
La requête SQL de l'onglet "Lister tables" ci-dessous fournit la liste de tables dont il nous faut optimiser l'espace occupé.
L'onglet "Exemple de résultat" illustre le résultat produit par cette requête sur la base "dbs34" qu'il faut optimiser. Ce résultat est le code SQL qu'il nous faudra exécuter pour optimiser l'espace occupé par cette BdD.
L'onglet "MyCron.php" est un programme, écrit en php. La boucle "while {..}" de ce script exécute pour chaque table dépassant le seuil des 60%, la requête "ALTER TABLE .... FORCE" qui optimise l'espace.
Ce programme devant être exécuté régulièrement, il sera placé dans une tâche CRON.
SELECT concat('ALTER TABLE `', table_schema, '`.`', table_name, '` FORCE;')
FROM information_schema.tables
WHERE data_free / (data_length + index_length + data_free) > 0.6
ORDER BY data_free DESC;
<?php
header('Content-Type: text/html; charset=UTF-8');
$hostname = 'localhost';
$database = 'dbs34';
$username = 'root';
$password = '';
$mysqli = new mysqli($hostname, $username, $password, $database);
// vérifier la connection
if (mysqli->connect_error) {
die("Failed to connect to MySQL:" . mysqli->connect_error);
}
// Change character set to utf8
mysqli->set_charset("utf8");
//------------------------------------------------------------------------------------------------
// Interrogation de la base pour créer la liste des commandes à exécuter pour optimiser les tables
//------------------------------------------------------------------------------------------------
$sql = "SELECT concat('ALTER TABLE `', table_schema, '`.`', table_name, '` FORCE;')
FROM information_schema.tables
WHERE data_free / (data_length + index_length + data_free) > 0.6
ORDER BY data_free DESC" ;
$result = $mysqli
->query($sql);
$data = array();
if ($result->num_rows > 0) {
// output data : la liste des tables à optimiser
while($row = $result->fetch_row()) {
$sql = $row[0];
$data[]= $row;
// Optimiser chaque table par exemple : ALTER TABLE `dbs34`.`83batch` FORCE;
$result2 = $mysqli
->query($sql);
}
}
else {
//pas de résultat
$data[] = "vide";
};
$mysqli->close();
echo json_encode( $data );
ALTER TABLE `dbs34`.`83batch` FORCE;
ALTER TABLE `dbs34`.`83watchdog` FORCE;
ALTER TABLE `dbs34`.`83key_value_expire` FORCE;
ALTER TABLE `dbs34`.`83cache_default` FORCE;
ALTER TABLE `dbs34`.`83config` FORCE;
ALTER TABLE `dbs34`.`83search_dataset` FORCE;
ALTER TABLE `dbs34`.`83locales_target` FORCE;
ALTER TABLE `dbs34`.`83block_content_revision__body` FORCE;
ALTER TABLE `dbs34`.`83cache_config` FORCE;
ALTER TABLE `dbs34`.`83router` FORCE;
ALTER TABLE `dbs34`.`83locales_source` FORCE;
ALTER TABLE `dbs34`.`83queue` FORCE;
ALTER TABLE `dbs34`.`83node__layout_builder__layout` FORCE;
ALTER TABLE `dbs34`.`83key_value` FORCE;
ALTER TABLE `dbs34`.`83node_revision__layout_builder__layout` FORCE;
ALTER TABLE `dbs34`.`83config_export` FORCE;
ALTER TABLE `dbs34`.`83block_content__body` FORCE;
2.Mise en place d'une tâche CRON
Pour cette mise en place, dans le cas de mon hébergeur 1and1 et de mon abonnement (serveur linux), il me faut accéder en "mode console" à ce serveur puis en utilisant la commande "crontab", créer ou modifier la table listant les tâches automatisée.
Je dois définir la tâche : exécuter le script "MyCron.php" de l'onglet ci-dessus. Ce script étant situé à la racine de mon répertoire privé sur le serveur, la commande à saisir est :
/usr/bin/php7.4-cli $HOME/MyCron.php
Cette commande lance "php7.4-cli", un interpréteur php, avec "$HOME/MyCron.php" pour argument.
Je souhaite que cette tâche soit exécutée tous les jours. J'utilise donc la variable @daily (équivalent de 0 0 * * *). Pour que le résultat du script (produit par l'instruction "echo json.encode($data)") me parvienne, j'initialise le paramètre MAILTO à "daniel@dhumbert.info". Ce qui donne les instructions suivantes à placer dans la table "cron" :
MAILTO="daniel@dhumbert.info"
@daily /usr/bin/php7.4-cli $HOME/MyCron.php
La commande "crontab -e" ouvre la table Cron en mode "édition". La frappe de la touche "i" me met en mode "insertion". Il me reste à taper les instructions vues ci-dessus et de les enregistrer par les frappes sur les touches esc : w q du clavier.
$hostname : nom du serveur hébergeant la BdD (ressemble à dbxxx.hosting-data.io chez 1and1 Ionos)
$database : le nom de la BdD à optimiser
$username et $password : l'identifiant et le mot de passe d'un utilisateur ayant accès en modification à la BdD
L'image ci-contre montre le courriel reçu suite à l'exécution de la tâche CRON. Il montre que 3 tables ont été mise à jour :
cache_config
key_value_expire
cache_default
NB : toutes les tables de mon site sont préfixées.
- le nom des tables de la base "d15" dont le ratio "espace libre / espace alloué" dépasse les 20% ;
- la taille des champs :
- DATA_FREE
- DATA_LENGTH
- INDEX_LENGTH
- la taille de la table en Mo ;
SELECT
TABLE_NAME as 'Table',
DATA_FREE as 'Free',
DATA_LENGTH as 'Length',
INDEX_LENGTH as 'Index',
(DATA_LENGTH + INDEX_LENGTH) /(1024 * 1024)as 'Taille en Mo'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'd15'and(DATA_FREE / ( DATA_LENGTH + INDEX_LENGTH + DATA_FREE))> 0.2;
Une valeur élevée de "DATA_FREE" indique que de l'espace alloué n'est pas utilisé. Une valeur excessive indique qu'il faut optimiser le stockage en reconstruisant ou défragmentant la table.
SELECT
SUM(ROUND( (( DATA_LENGTH + INDEX_LENGTH ) / 1024 / 1024),2)) AS TailleMo
FROM information_schema.TABLES
SHOW TABLE STATUS FROM d11 WHERE name='83config'