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 "dbs34" qu'il faut optimiser. Ce résultat est le code SQL qu'il nous faut 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_errno())
{
$msg = "HTTP/1.1 428 Failed to connect to MySQL:" . mysqli_connect_error();
header($msg);
exit();
}
// Change character set to utf8
mysqli_set_charset($mysqli,"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 par la commande "crontab -i", 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'insère la ligne MAILTO="daniel@dhumbert.info". Ce qui donne la table cron suivante :
MAILTO="daniel@dhumbert.info"
@daily /usr/bin/php7.4-cli $HOME/MyCron.php
- $hostname : nom du serveur hébergeant la BdD (ressemble à dbxxx.hosting-data.io chez 1and1)
- $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.
SELECT
SUM(ROUND( (( DATA_LENGTH + INDEX_LENGTH ) / 1024 / 1024),2)) AS TailleMo
FROM information_schema.TABLES