MySQL : Commandes avancées

La plupart de ces commandes sont disponibles pour MySQL ou MariaDB.

RAM

SELECT ( @@key_buffer_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * (
    @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

Check la max RAM qui sera utilisée par MySQL. Il est important de bien prendre en compte la RAM pouvant être utilisée par chaque connection.

Des sites existent tels que MySQLCalculator, MySQL DBA ou encore le plus visuel Avchinnikov

InnoDB

SELECT  ENGINE,
        ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
        ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
        ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
        COUNT(*) "Num Tables"
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE  table_schema not in ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")
    GROUP BY  ENGINE;

Nous permet de déterminer la taille de chaque moteur de stockage. Ainsi, nous pouvons dimensionner innodb_buffer_pool_size au plus juste (20% en plus de la valeur retournée)

MariaDB [(none)]> ...
+--------+---------+----------+----------+------------+
| ENGINE | Data MB | Index MB | Total MB | Num Tables |
+--------+---------+----------+----------+------------+
| CSV    |     0.0 |      0.0 |      0.0 |          2 |
| InnoDB |  1276.5 |    339.3 |   1615.7 |        172 |
| MyISAM |     0.5 |      0.1 |      0.6 |         25 |
+--------+---------+----------+----------+------------+
3 rows in set (0.007 sec)

Nous savons que dans notre exemple nous avons un moteur InnoDB utilisant 1.6G. Nous pouvons donc définir le pool à 2G.

Q=` ; mysql --skip-column-names -Be "select concat(alter table ${Q}, table_schema,${Q}.${Q}, table_name, ${Q} engine=innodb;)
from information_schema.tables where engine = MyISAM and table_schema not in (mysql)" | mysql

Cette commande nous sert à convertir toutes les tables MyISAM en InnoDB. Un use case parmis tant d'autres, une cluster Galera.

Misc

MariaDB [(none)]> SHOW (FULL) PROCESSLIST 'G;

Permet de lister les process SQL tournant d'une manière '"propre'" (''G), on peut également aller plus loin en détail en ajoutant FULL

MariaDB [(none)]>  KILL xxxx

Permet de kill le process MySQL xxxx (Attention, rollback de la transaction, peut également être long)

pt-show-grants
mysql --silent --skip-column-names --execute "select concat(',User,'@',Host,') as User from mysql.user" | sort | '
while read u
 do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed s/$/;/
done

Permet de dump la liste des users SQL (commande mysql si pt-show-grants pas dispo)

SELECT ENGINE,
       concat(TABLE_SCHEMA, TABLE_NAME) AS TABLE_NAME,
       round(DATA_LENGTH/1024/1024, 2) AS data_length,
       round(INDEX_LENGTH/1024/1024, 2) AS index_length,
       round(DATA_FREE/1024/1024, 2) AS data_free,
       (data_free/(index_length+data_length)) AS frag_ratio
FROM information_schema.tables
WHERE DATA_FREE > 0
ORDER BY data_free DESC LIMIT 10;

Permet de voir la fragmentation des tables MySQL. Il est possible de récuperer l'espace en faisant un OPTIMIZE TABLE

SELECT information_schema.system_variables.variable_name,
       information_schema.system_variables.default_value,
       global_variables.variable_value
FROM information_schema.system_variables,
     information_schema.global_variables
WHERE system_variables.variable_name=global_variables.variable_name
  AND system_variables.default_value <> global_variables.variable_value
  AND system_variables.default_value <> 0

Permet de lister les variables qui n'ont pas les valeurs par défaut