7/30/2019 MySQL Server Variable Tuning

1/24

www.fromdual.com

1 / 24

MySQL Performance Tuning

DOAG 2013 Datenbank

14. Mai 2013, Dsseldorf

Oli SennhauserSenior MySQL Berater, FromDual GmbH

oli.sennhauser@fromdual.com

7/30/2019 MySQL Server Variable Tuning

2/24

www.fromdual.com

2 / 24

ber FromDual GmbH

FromDual bietet neutral und unabhngig: Beratung fr MySQL Support fr MySQL und Galera Cluster Remote-DBA Dienstleistungen fr MySQL MySQL Schulungen

Oracle Silver Partner (OPN)

www.fromdual.com

http://www.fromdual.com/http://www.fromdual.com/

7/30/2019 MySQL Server Variable Tuning

3/24

www.fromdual.com

3 / 24

Inhalt

HA Solutions

Read scale-out Replication set-up for HA Active/passive fail-over MySQL Cluster

Replication Cluster Storage-Engine-Replication

MySQL Performance Tuning mit System-Variablen

Performance Tuning Hebel MySQL System Variablen MySQL Status Variablen MySQL Architektur

Speicherbelegung Session Variablen Globale Variablen MyISAM Variablen InnoDB Variablen Monitoring Informationen

7/30/2019 MySQL Server Variable Tuning

4/24

www.fromdual.com

4 / 24

Performance Tuning Hebel

7/30/2019 MySQL Server Variable Tuning

5/24

www.fromdual.com

5 / 24

MySQL System Variablen

Datenbank-Konfiguration

my.cnf

mysqld --help verbose/etc/my.cnf /etc/mysql/my.cnf/usr/local/mysql/etc/my.cnf$basedir/my.cnf ~/.my.cnf

SHOW GLOBAL VARIABLES;

7/30/2019 MySQL Server Variable Tuning

6/24

www.fromdual.com

6 / 24

MySQL Status Variablen

Was wir damit anrichten sieht man mit:

SHOW /*!50001 GLOBAL */ STATUS;

SHOW ENGINE INNODB STATUS\G

7/30/2019 MySQL Server Variable Tuning

7/24

www.fromdual.com

7 / 24

MySQL Architektur

mysqld

Application / Client

ThreadCache

ConnectionManager

User Au-thentication

CommandDispatcher

Logging

Query Cache

Module

Query

Cache

Parser

Optimizer

Access Control

Table Manager

Table OpenCache (.frm, fh)

Table Definition

Cache (tbl def.)

Handler Interface

MyISAM Memory NDB PBXTInnoDB ...Aria XtraDB Tokutek

7/30/2019 MySQL Server Variable Tuning

8/24

www.fromdual.com

8 / 24

Speicherbelegung

Storage Engines

MySQL Server (mysqld)

Client 2 Client N

Local Memory Areas

Client 1

read_buffersort_buffertmp_table

read_bufferread_buffersort_buffer

table_open_cache

table_definition_cachequery_cache_sizeetc...

Global Memory Area

key_buffer_size

innodb_buffer_pool_sizeinnodb_log_buffer_sizeetc...

7/30/2019 MySQL Server Variable Tuning

9/24

www.fromdual.com

9 / 24

Thread Cache

Konifguration:thread_cache_size

Status:

threads_created

+----------------------+-------+| Variable_name | Value |+----------------------+-------+| Threads_cached | 8 |

| Threads_connected | 242 || Threads_created | 15355 || Threads_running | 16 |+----------------------+-------+

7/30/2019 MySQL Server Variable Tuning

10/24

www.fromdual.com

10 / 24

Query Cache

Konfiguration: query_cache_size

Status:

Com_select,Qcache_hits

Hit ratio:

Qache_hits/(Com_select + Qcache_hits)

+-------------------------+----------+| Variable_name | Value |+-------------------------+----------+| Com_select | 25712 |

| Qcache_free_memory | 19553200 || Qcache_hits | 65137 || Qcache_inserts | 172167 |+-------------------------+----------+

7/30/2019 MySQL Server Variable Tuning

11/24

www.fromdual.com

11 / 24

Per Session Parameter

Im Executor Alloziert/Dealloziert on Demand Per Session oder global einstellbar Meisst Effekt gering oder gar kontraproduktiv! Join Buffer

join_buffer_size

Read Buffer und Read Random Buffer read_buffer_size, read_rnd_buffer_size

7/30/2019 MySQL Server Variable Tuning

12/24

www.fromdual.com

12 / 24

Sort Buffer, temporre Tabellen

Temporre Tabellen(MEMORY oderMyISAM)

Konfiguration: tmp_table_size,(max_heap_table_size)

Status: created_tmp_tables,

created_tmp_disk_tables

Sort Buffer Konfiguration:

sort_buffer_size

Status: sort_merge_passes

7/30/2019 MySQL Server Variable Tuning

13/24

www.fromdual.com

13 / 24

Table Definition Cache

Ab v5.1 Enthlt Info vom .frm File

Konfiguration: table_definition_cache

Status: open_table_definitions,opened_table_defintions

+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| Open_table_definitions | 100 || Opened_table_definitions | 100 |+--------------------------+-------+

7/30/2019 MySQL Server Variable Tuning

14/24

www.fromdual.com

14 / 24

Table (Open) Cache

Ab v5.1 File-Descriptioren (1 pro Connection pro Table)

Frhertable_cache

Konfiguration: table_open_cache Status: open_tables, opened_tables

+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| Open_tables | 64 || Opened_tables | 11812 |+--------------------------+-------+

7/30/2019 MySQL Server Variable Tuning

15/24

www.fromdual.com

15 / 24

MyISAM

Bis MySQL 5.1 default Storage Engine

Konfiguration: key_buffer_size

Status:Key_blocks_used,Key_blocks_unused, Key_reads,

Key_read_requests

7/30/2019 MySQL Server Variable Tuning

16/24

www.fromdual.com

16 / 24

InnoDB Buffer Pool

InnoDB seit 5.5 default Storage Engine InnoDB Buffer Pool Konfiguration:

innodb_buffer_pool_size,innodb_buffer_pool_instances,innodb_io_capacity

mysqld

system table space: ibdata1

table table space table table spacetable table space

ib_

logfile1

ib_

logfile2

ib_

logfilen

undo

double write area

tables/indexes

dd

OS file cache

sync

async

buffer pool

secondary index buffer

trx

log

buffer

data pages

index pages

adaptive hash idx

undo records

lock info table

7/30/2019 MySQL Server Variable Tuning

17/24

www.fromdual.com

17 / 24

InnoDB Buffer Pool

Status:innodb_buffer_pool_pages_%,Innodb_buffer_bytes_%,

Innodb_data_%

7/30/2019 MySQL Server Variable Tuning

18/24

www.fromdual.com

18 / 24

InnoDB Log File

REDO Log, TransactionLog

Konfiguration:

innodb_log_file_size,innodb_flush_log_at_trx_commit

Status: innodb%log%

mysqld

system table space: ibdata1

table table spacetable table spacetable table space

ib_

logfile1

ib_

logfile2

ib_

logfilen

undo

double write area

tables/indexes

dd

OS file cache

sync

async

buffer pool

secondary index buffer

trx

log

buffer

data pages

index pages

adaptive hash idx

undo records

lock info table

7/30/2019 MySQL Server Variable Tuning

19/24

www.fromdual.com

19 / 24

Binary Log

Application ApplicationApplication

mysqld

bin-log.n

binlog_cache_sizebinlog_stmt_cache_size

sync_binlog

+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Binlog_cache_disk_use | 3456 || Binlog_cache_use | 12786 || Binlog_stmt_cache_disk_use | 0 || Binlog_stmt_cache_use | 0 |+----------------------------+-------+

7/30/2019 MySQL Server Variable Tuning

20/24

www.fromdual.com

20 / 24

Monitoring

Von Hand: SHOW GLOBAL STATUS;

SHOW ENGINE INNODB STATUS\G

Performance Schema (P_S, >= v5.5)

Irgend ein graphisches Monitoring Tool...

7/30/2019 MySQL Server Variable Tuning

21/24

www.fromdual.com

21 / 24

PERFORMANCE_SCHEMA

In Diskussion seit mindestens 2006 Eingefhrt in MySQL 5.5 (2010)

Einblick in die Datenbank In v5.5 disabled, in v5.6 enabled per default

performance_schema = 1

Overhead 5% - 200%! also Vorsicht!

7/30/2019 MySQL Server Variable Tuning

22/24

www.fromdual.com

22 / 24

Beispiel: Table I/O waits

SELECT OBJECT_SCHEMA, OBJECT_NAME, ROUND(SUM_TIMER_WAIT/1000000000, 3) AS SUM_TIMER_WAIT, ROUND(SUM_TIMER_READ/1000000000, 3) AS SUM_TIMER_READ, ROUND(SUM_TIMER_WRITE/1000000000, 3) AS SUM_TIMER_WRITE

FROM table_io_waits_summary_by_tableORDER BY SUM_TIMER_WAIT DESCLIMIT 10

;+---------------+--------------+----------------+----------------+-----------------+| OBJECT_SCHEMA | OBJECT_NAME | SUM_TIMER_WAIT | SUM_TIMER_READ | SUM_TIMER_WRITE |+---------------+--------------+----------------+----------------+-----------------+| zabbix | items | 1182496.258 | 616630.696 | 565865.562 || zabbix | history_uint | 889884.116 | 370528.991 | 519355.125 || zabbix | history_str | 460411.155 | 246159.458 | 214251.697 || zabbix | history | 389815.659 | 86285.980 | 303529.679 || zabbix | trends_uint | 133955.560 | 107365.626 | 26589.934 |

| zabbix | functions | 128657.654 | 128657.654 | 0.000 || zabbix | trends | 43683.159 | 36314.732 | 7368.428 || mpm | mpm_agent | 27837.056 | 27837.056 | 0.000 || zabbix | hosts | 23820.284 | 14044.672 | 9775.612 || zabbix | triggers | 21052.459 | 20970.604 | 81.855 |+---------------+--------------+----------------+----------------+-----------------+

7/30/2019 MySQL Server Variable Tuning

23/24

www.fromdual.com

23 / 24

Informationen

MySQL System Variablen:http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

MySQL Status Variablen:

http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html

MySQL DB Health Check:http://www.fromdual.com/mysql-database-health-check

MySQL Performance Monitor:

http://www.fromdual.com/mysql-performance-monitor

7/30/2019 MySQL Server Variable Tuning

24/24

www.fromdual.com

24 / 24

Q & A

Fragen ?

Diskussion?

Wir haben Zeit fr ein Performance Audit...!

FromDual bietet neutral und unabhngig: Beratung Remote-DBA

Support fr MySQL und Galera Cluster

Schulung

www.fromdual.com/presentations