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

 

Cliquez sur le bouton pour copier le code
dans le presse papier
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;
         
Cliquez sur le bouton pour copier le code
dans le presse papier
<?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 );
Cliquez sur le bouton pour copier le code
dans le presse papier
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

Dans le fichier "MyCron.php" de l'onglet ci-dessus,  if faut bien évidemment modifier le contenu des variables  
  • $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.

Cliquer pour agrandir l'image

Utilisez la requête SQL suivante pour calculer la taille de votre BdD en Mo avec 2 décimales :
 SELECT
       SUM(ROUND( (( DATA_LENGTH + INDEX_LENGTH ) / 1024 / 1024),2)) AS TailleMo
       FROM information_schema.TABLES