Category Archives: MySQL

mysqldumpslow

mysqldumpslow -s c -t 10 /var/log/mysql_slow_queries.log

NAME
mysqldumpslow – Summarize slow query log files

SYNOPSIS
mysqldumpslow [options] [log_file …]

DESCRIPTION
The MySQL slow query log contains information about queries that take a long time to execute (see Section 5.2.5, “The Slow Query Log”).
mysqldumpslow parses MySQL slow query log files and prints a summary of their contents.

Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It “abstracts” these values to N and ‘S’ when displaying summary output. The -a and -n options can be used to modify value abstracting behavior.

Invoke mysqldumpslow like this:

shell> mysqldumpslow [options] [log_file ...]

mysqldumpslow supports the following options.

· –help

Display a help message and exit.

· -a

Do not abstract all numbers to N and strings to ‘S’.

· –debug, -d

Run in debug mode.

· -g pattern

Consider only queries that match the (grep-style) pattern.

· -h host_name

Host name of MySQL server for *-slow.log file name. The value can contain a wildcard. The default is * (match all).

· -i name

Name of server instance (if using mysql.server startup script).

· -l

Do not subtract lock time from total time.

· -n N

Abstract numbers with at least N digits within names.

· -r

Reverse the sort order.

· -s sort_type

How to sort the output. The value of sort_type should be chosen from the following list:

· t, at: Sort by query time or average query time

· l, al: Sort by lock time or average lock time

· r, ar: Sort by rows sent or average rows sent

· c: Sort by count

By default, mysqldumpslow sorts by average query time (equivalent to -s at).

· -t N

Display only the first N queries in the output.

· –verbose, -v

Verbose mode. Print more information about what the program does.

Example of usage:

shell> mysqldumpslow
Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1
Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N
Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1 

Get the total MySQL data and index usage by storage engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Get MySQL tables size

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
*************************** 1. row ***************************
 Name: tbl_name
 Engine: MyISAM
 Version: 10
 Row_format: Dynamic
 Rows: 5262588
 Avg_row_length: 154
 Data_length: 40680708
 Max_data_length: 281474976710655
 Index_length: 22840320
 Data_free: 0
 Auto_increment: NULL
 Create_time: 2013-06-13 15:08:11
 Update_time: 2013-06-20 14:27:43
 Check_time: 2013-06-20 13:16:16
 Collation: utf8_general_ci
 Checksum: NULL
 Create_options:
 Comment:
 1 row in set (0.00 sec)

Get table sizes in MB for a whole database:

SELECT table_name AS "Tables",
 round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
 FROM information_schema.TABLES
 WHERE table_schema = "table_name";

Using perl MySQL Tuner

MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.

juancho@juancho-laptop:~$ perl < (GET https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl)

>> MySQLTuner 1.2.0 - Major Hayden
 >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >> Run with '--help' for additional options and output filtering
 Please enter your MySQL administrative login: root
 Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
 [--] Skipped version check for MySQLTuner script
 [OK] Currently running supported MySQL version 5.5.29-0ubuntu0.12.04.1
 [OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
 [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
 [--] Data in MyISAM tables: 1G (Tables: 763)
 [--] Data in InnoDB tables: 113M (Tables: 78)
 [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
 [!!] Total fragmented tables: 86
-------- Security Recommendations -------------------------------------------
 [OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
 [--] Up for: 16h 50m 51s (10K q [0.171 qps], 852 conn, TX: 15M, RX: 2M)
 [--] Reads / Writes: 98% / 2%
 [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
 [OK] Maximum possible memory usage: 597.8M (10% of installed RAM)
 [OK] Slow queries: 0% (0/10K)
 [OK] Highest usage of available connections: 8% (13/151)
 [OK] Key buffer size / total MyISAM indexes: 16.0M/572.6M
 [OK] Key buffer hit rate: 99.9% (1M cached / 971 reads)
 [OK] Query cache efficiency: 44.4% (3K cached / 8K selects)
 [OK] Query cache prunes per day: 0
 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
 [OK] Temporary tables created on disk: 21% (159 on disk / 734 total)
 [OK] Thread cache hit rate: 98% (15 created / 852 connections)
 [!!] Table cache hit rate: 9% (400 open / 4K opened)
 [OK] Open file limit used: 76% (786/1K)
 [OK] Table locks acquired immediately: 100% (7K immediate / 7K locks)
 [OK] InnoDB data size / buffer pool: 113.8M/128.0M
-------- Recommendations -----------------------------------------------------
 General recommendations:
 Run OPTIMIZE TABLE to defragment tables for better performance
 MySQL started within last 24 hours - recommendations may be inaccurate
 Enable the slow query log to troubleshoot bad queries
 Increase table_cache gradually to avoid file descriptor limits
 Variables to adjust:
 table_cache (> 400)