MySQL slow query configuration and MySQL process view

A. Command Actions

a) database Query Current connection:showprocesslist;&&show status;

b) Database viewing status:status;

c) view CPU usage:top

d) Refresh report Memory usage:free–b–s5

Free command:

Total: the total amount of physical memory.

used: how large is used.

Free : how much is available.

Shared: total amount of memory shared by multiple processes.

buffers/cached: the size of the disk cache.

Third line (-/+ buffers/cached):

used: how large is used.

Free : how much is available.

two. Database

MySQL Slow Query configuration

1. ?

It can record all executions over long_query_ Time sql statement , help you find the slow sql, convenient for these sql optimize .

2. turn on slow query

View MYSQL whether the server's slow query state is turned on . execute the following command :

Show variables like '%slow% ';

Current log_slow_queries status is OFF, indicates that a slow query is not currently turned on .

turn on slow query =:

in the [Mysqld] Add the following information:

[Mysqld]

Log-slow-queries= "/data/mysqldata/mysql-slow.log"

long_query_time = 4 ( unit:s)

Log-queries-not-using-indexes

log-slow-queries:RepresentativeMYSQLLog store directory for slow queries, This directory file must have write permission;

Long_query_time:Maximum execution Time. (, MSYQLall execution times are recorded in excess of2Bar ofSQLStatement, test time is here, time should not be too small best in5-10Within seconds, Of course, according to their own standards.);

log-queries-not-using-indexes : Queries that do not use an index are also recorded in the log

configure it to restart one later. MYSQL Service

Or: Run the following command to run the slow query log

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.03 sec)

mysql> set global slow_launch_time=5;
Query OK, 0 rows Affected (0.00 sec)

mYsql 5.1.6version,Slow_query_logand theSlow_launch_timesupport to write a file or write a database table two ways, and the opening of the log, the output mode of modification, can be inGloballevel dynamic modification.
just a simple passset global slow_query_log=on;To turn on slow queries without restarting the database!


3.can be written directly into the configuration fileMy.cnf

Slow_query_log_file=/mysql/log/nagiosdb-slow.log
Slow_launch_time=5

can complete the configuration!!

MySQL slow query configuration and MySQL process view