MySQL

From Glee
Jump to: navigation, search

Variables

SHOW GLOBAL VARIABLES LIKE '%log%';
SET GLOBAL log_slow_queries = 'ON';

Status

SHOW GLOBAL STATUS LIKE '%log%';

When tweaking setting, much more useful than a grand total since startup is looking at the current evolution :

mysqladmin extended-status --relative --sleep=60 | grep log

Logging

show variables like '%slow%';
set global log_slow_queries=ON;
set global slow_query_log=ON;
show variables like '%log%';
set global log_queries_not_using_indexes=ON;

Percona specific :

set global log_slow_filter = 'full_scan,tmp_table_on_disk,filesort_on_disk';

Indexes

Show existing and add new :

SHOW INDEXES FROM tbl_name;
CREATE INDEX index_name ON tbl_name (index_col_name,...);

Find out what SELECT aren't using indexes :

SHOW VARIABLES LIKE 'log%index%';
SET GLOBAL log_queries_not_using_indexes='ON';
SHOW VARIABLES LIKE '%slow%';
SET GLOBAL slow_query_log='ON';

Tuning

The magic setting for non-critical heavy writes :

innodb_flush_log_at_trx_commit = 2

Slaving

Don't replicate `OPTIMIZE` to avoid slave lag with large tables :

OPTIMIZE NO_WRITE_TO_BINLOG TABLE foo;