MySQL Server Variable Tuning

Embed Size (px)

Citation preview

  • 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

    [email protected]

  • 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