Optimisation MySQL

From Tuxunix
Jump to: navigation, search
  • Télécharger le script permettant d'ajuster les paramètres pour optimiser MySQL : File:Tuning-primer.sh

Analyse requete lente

Ex :

#> tail -f mysql-slow.log | mysql_slow_log_filter -T 1.5 -R 1000


#!/usr/bin/perl 
 
#
# Originally wrriten by
# Nathanial Hendler
# http://retards.org/
#
#
# 2001-06-26 v1.0
#
#  Modified by Vadim Tkachenko apachephp@gmail.com
#
#
# This perl script parses a MySQL slow_queries log file
# ignoring all queries less than $min_time and prints
# out how many times a query was greater than $min_time
# with the seconds it took each time to run.
#
# Usage: msql_slow_log_filter -T timesec -R numrows < logfile
#
 
use Getopt::Std;
 
 
getopt ('TR');
 
$min_time       = $opt_T;	# Skip queries less than $min_time
$max_display    = 0;	# Truncate display if more than $max_display occurances of a query
$max_rows	= $opt_R;    #Skip queries less than $max_rows
 
print "\n Starting... \n";
 
$query_string   = '';
$time           = 0;
$new_sql        = 0;
 
 
##############################################
# Loop Through The Logfile
##############################################
 
while (<>) {
 
	# Skip Bogus Lines
	# /home/vadim/mysql/bin/mysqld, Version: 5.0.22-standard-log. started with:
	next if ( m|/.*mysqld, Version:.+ started with:| );
	next if ( m|Tcp port: \d+  Unix socket: .*mysql.sock| );
	next if ( m|Time\s+Id\s+Command\s+Argument| );
	next if ( m|User@Host:| );
 
	# # Query_time: 790  Lock_time: 0  Rows_sent: 3400617  Rows_examined: 3400617
	if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s+Rows_examined:\s+(.*)/ ) {
	# if ( /Query_time:\s+(\d+)\s+Lock_time:\s+(\d+).*Rows_examined:\s+(\d+)/ ) {
		$time    = $1;
		$rows	 = $3;
		if ( ( defined ($min_time) &&  $time >= $min_time) || ( defined ($max_rows) && $rows >= $max_rows ) ) {
			$passed_test = 1;
			print $_;
		} else {
			$passed_test = 0;
		}
		next;
 
	}
 
	print $_ if ($passed_test);
	next;
}
 
 
exit(0);

Trouvé les tables qui occupe le plus d'espace

SELECT CONCAT(table_schema, '.', table_name),
      CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
      CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
      CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
      CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
      ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

Commandes utiles

  • Forcer l'utilisation d'index lors d'une requête :
SELECT * FROM some_table USE INDEX (index1,index2);
  • Export du resulat d'une requête au format CSV :
SELECT * INTO OUTFILE '/tmp/export.csv' from some_table;

Pour personnaliser le format de sortie on peut ajouter des options telles que le séparateur de champs "FIELDS TERMINATED BY", la mise entre quote (ou autre) "OPTIONALLY ENCLOSED BY", le type fin de ligne "LINES TERMINATED BY", Exemple :

SELECT * INTO OUTFILE '/tmp/export.csv' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' from some_table;