|
Membre
Date d'inscription: octobre 2006
Messages: 3 798
|
Script de mesure de la taille d'une base de données
Bonjour à toutes et à toutes,
J'ai réalisé un petit script, basé sur show table status, qui donne le total de la taille d'une base de données plus pour chaque table :
- La taille des Data,
- La taille de l'index,
- Le nombre de lignes,
Code PHP:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Taille d'une base de données</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?PHP
// Paramètres d'entrée à modifier :
// --------------------------------
$DBhost = "mysql5_1";
$DBName = "xxxxx";
$DBowner = "yyyyy";
$DBpw = "zzzzz";
// DOCUMENTATION
// -------------
/*
// http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html
0 Name The name of the table.
1 Engine The storage engine for the table. See Chapter 14, Storage Engines and Table Types.
2 Version The version number of the table's .frm file.
3 Row_format The row storage format (Fixed, Dynamic, Compressed, Redundant, Compact). Starting with MySQL/InnoDB 5.0.3, the format of InnoDB tables is reported as Redundant or Compact. Prior to 5.0.3, InnoDB tables are always in the Redundant format.
4 Rows The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
The Rows value is NULL for tables in the INFORMATION_SCHEMA database.
5 Avg_row_length The average row length.
6 Data_length The length of the data file.
7 Max_data_length The maximum length of the data file. This is the total number of bytes of data that can be stored in the table, given the data pointer size used.
8 Index_length The length of the index file.
9 Data_free The number of allocated but unused bytes.
10 Auto_increment The next AUTO_INCREMENT value.
11 Create_time When the table was created.
12 Update_time When the data file was last updated. For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its tablespace and the data file timestamp does not apply.
13 Check_time When the table was last checked. Not all storage engines update this time, in which case the value is always NULL.
14 Collation The table's character set and collation.
15 Checksum The live checksum value (if any).
16 Create_options Extra options used with CREATE TABLE.
17 Comment The comment used when creating the table (or information as to why MySQL could not access the table information).
*/
// Ouverture de la base et accès aux tables
// ----------------------------------------
$fh_db=mysql_connect($DBhost, $DBowner, $DBpw);
if (($fh_db))
{
// echo "DATABASE <b> >>$fh_db<< " . $DBName . "</b> Connexion OK <br> ";
$sel=mysql_select_db($DBName,$fh_db);
if ($sel) echo "DATABASE <b>" . $DBName . "</b> Ouverture OK <br> ";
else echo "DATABASE <b>" . $DBName . "</b> Ouverture <font color=red> impossible </font> <br> ";
}
else
{
echo "DATABASE <b>" . $DBName . "</b> Connexion <font color=red> impossible </font> <br> ";
exit;
}
$Aff_size = "";
$Total = 0;
$Total_data = 0;
$Total_index = 0;
$Tab_tables = array();
$result_count_row = 0;
$count = 0;
$sql_query = "SHOW TABLE STATUS FROM $DBName";
$result_query = mysql_query($sql_query);
if (!isset($result_query)) echo " <br> #### " . mysql_error() . " <br> #### " . "Requête : \$sql_query=$sql_query <br> <br> \n";
else
{
while ($result_query && $row=mysql_fetch_row($result_query))
{
$count = $count + 1;
$Table_liste = $row[0]; // Name
$row_1 = $row[1]; // Engine
$row_2 = $row[2]; // Version
$row_3 = $row[3]; // Row_format
$row_4 = $row[4]; // Rows
$row_5 = $row[5]; // Avg_row_length
$Table = $row ['0']; // Name
$Tab_tables ["$Table"]['Total_Rows'] = $row ['4']; // Rows
$Tab_tables ["$Table"]['Table_Size'] = $row ['6']; // Data_length
$Tab_tables ["$Table"]['Index_Size'] = $row ['8']; // Index_length
$Total = $Total + $Tab_tables ["$Table"]['Table_Size'] + $Tab_tables ["$Table"]['Index_Size'];
$Total = $Total + $Tab_tables ["$Table"]['Table_Size'] + $Tab_tables ["$Table"]['Index_Size'];
$Total_data = $Total_data + $Tab_tables ["$Table"]['Table_Size'];
$Total_index = $Total_index + $Tab_tables ["$Table"]['Index_Size'];
}
}
// Contenu de chaque table MySQL
$Tab_col = array();
reset ($Tab_tables);
while(list($Table, $data) = each($Tab_tables))
{
$count = 0;
$sql_query_col="SHOW COLUMNS FROM $Table";
$result_col=mysql_query($sql_query_col);
$Tableau_colonnes = array();
while ($result_col && $row=mysql_fetch_array($result_col))
{
$count = $count + 1;
$Nom_col = $row[0];
$Type_col = $row[1];
$Tab_col ["$Table"]['Liste_col']["$Nom_col"]['Type_col']= $Type_col;
}
$Tab_col ["$Table"]['Liste_col']['Nb_col']= $count;
}
// Fermeture de la connexion
mysql_close($fh_db);
// Affichage des tailles des tables MySQL
$Total_len = strlen($Total);
if ($Total_len > 3 && $Total_len <= 6) $Total = substr($Total, 0 , $Total_len - 3 ) . " " . substr($Total, $Total_len - 3 , 3);
if ($Total_len > 6 && $Total_len <= 9) $Total = substr($Total, 0 , $Total_len - 6 ) . " " . substr($Total, $Total_len - 6 , 3) . " " . substr($Total, $Total_len - 3 , 3);
$Aff_size .= "<center> \n";
$Aff_size .= "<table cellspacing=0 cellpadding=0 border=0 > \n";
$Aff_size .= "<tr><td colspan=4> </td></tr> \n";
$Aff_size .= "<tr><td colspan=4> <center> Base : <b> $DBName </b> Taille : <b> $Total </b> </center> </td></tr> \n";
$Aff_size .= "<tr><td colspan=4> </td></tr> \n";
$Aff_size .= "<tr> \n";
$Aff_size .= "<td> <b> Table </b> </td> \n";
$Aff_size .= "<td> <b> Taille data </b> </td> \n";
$Aff_size .= "<td> <b> Taille index </b> </td> \n";
$Aff_size .= "<td> <b> Lignes </b> </td> \n";
$Aff_size .= "</tr> \n";
$Aff_size .= "<tr><td colspan=4> </td></tr> \n";
// Affichage des tables
reset ($Tab_tables);
while(list($Table, $data) = each($Tab_tables))
{
$Table_Size = $Tab_tables ["$Table"]['Table_Size'];
$Table_Size_len = strlen($Table_Size);
if ($Table_Size_len > 3 && $Table_Size_len <= 6) $Table_Size = substr($Table_Size, 0 , $Table_Size_len - 3 ) . " " . substr($Table_Size, $Table_Size_len - 3 , 3);
if ($Table_Size_len > 6 && $Table_Size_len <= 9) $Table_Size = substr($Table_Size, 0 , $Table_Size_len - 6 ) . " " . substr($Table_Size, $Table_Size_len - 6 , 3) . " " . substr($Table_Size, $Table_Size_len - 3 , 3);
$Total_Rows = $Tab_tables ["$Table"]['Total_Rows'];
$Total_Rows_len = strlen($Total_Rows);
if ($Total_Rows_len > 3 && $Total_Rows_len <= 6) $Total_Rows = substr($Total_Rows, 0 , $Total_Rows_len - 3 ) . " " . substr($Total_Rows, $Total_Rows_len - 3 , 3);
if ($Total_Rows_len > 6 && $Total_Rows_len <= 9) $Total_Rows = substr($Total_Rows, 0 , $Total_Rows_len - 6 ) . " " . substr($Total_Rows, $Total_Rows_len - 6 , 3) . " " . substr($Total_Rows, $Total_Rows_len - 3 , 3);
$Index_Size = $Tab_tables ["$Table"]['Index_Size'];
$Index_Size_len = strlen($Index_Size);
if ($Index_Size_len > 3 && $Index_Size_len <= 6) $Index_Size = substr($Index_Size, 0 , $Index_Size_len - 3 ) . " " . substr($Index_Size, $Index_Size_len - 3 , 3);
if ($Index_Size_len > 6 && $Index_Size_len <= 9) $Index_Size = substr($Index_Size, 0 , $Index_Size_len - 6 ) . " " . substr($Index_Size, $Index_Size_len - 6 , 3) . " " . substr($Index_Size, $Index_Size_len - 3 , 3);
$Aff_size .= "<tr> \n";
$Aff_size .= "<td align=left> " . $Table . "</td> \n";
$Aff_size .= "<td align=right> " . $Table_Size . "</td> \n";
$Aff_size .= "<td align=right> " . $Index_Size . "</td> \n";
$Aff_size .= "<td align=right> " . $Total_Rows . "</td> \n";
$Aff_size .= "</tr> \n";
}
$Aff_size .= "<tr><td colspan=4> </td></tr> \n";
$Total_data_len = strlen($Total_data);
if ($Total_data_len > 3 && $Total_data_len <= 6) $Total_data = substr($Total_data, 0 , $Total_data_len - 3 ) . " " . substr($Total_data, $Total_data_len - 3 , 3);
if ($Total_data_len > 6 && $Total_data_len <= 9) $Total_data = substr($Total_data, 0 , $Total_data_len - 6 ) . " " . substr($Total_data, $Total_data_len - 6 , 3) . " " . substr($Total_data, $Total_data_len - 3 , 3);
$Total_index_len = strlen($Total_index);
if ($Total_index_len > 3 && $Total_index_len <= 6) $Total_index = substr($Total_index, 0 , $Total_index_len - 3 ) . " " . substr($Total_index, $Total_index_len - 3 , 3);
if ($Total_index_len > 6 && $Total_index_len <= 9) $Total_index = substr($Total_index, 0 , $Total_index_len - 6 ) . " " . substr($Total_index, $Total_index_len - 6 , 3) . " " . substr($Total_index, $Total_index_len - 3 , 3);
$Aff_size .= "<tr> \n";
$Aff_size .= "<td align=left> <b> Totaux : </b> </td> \n";
$Aff_size .= "<td align=right> <b> " . $Total_data . " </b> </td> \n";
$Aff_size .= "<td align=right> <b> " . $Total_index . " </b> </td> \n";
$Aff_size .= "<td align=left> </td> \n";
$Aff_size .= "</tr> \n";
$Aff_size .= "</table> \n";
$Aff_size .= "</center> \n";
$Aff_size .= "<br> \n";
// Affichage des résultats
print $Aff_size;
?>
Dernière modification par Abogil 20/03/2007 à 09h52
|
19/03/2007, 18h23
