有連結嗎?

還沒開場耶. XD

ok

我睡過頭了 交給你啦

Tombear 現在來還來得及

7/22 18:00~20:00 oracle meeting room, Taiwan MySQL Group

Oracle

MySQL Performance Tuning

MySQL Global Business Unit Sales Consulting Senior Manager, JAPAC

MySQL 5.5, 5.6, MySQL Cluster 7.3, MySQL Utilities, MySQL Enterprise, MySQL Applier for Hadoop

Oracle 定位: Database Platform for Next Gen Web Apps

MySQL 5.5 -> 5.6: 234% Performance Gain

SysBench (Read Write)

Replication: Binary Log Group Commit

10% overhead when configuring: sync_binlog=0

Additional 5% when configuring: sync_binlog=1

MySQL 5.6 Replication Performance (Oracle's MySQL Blog)

Save option: sync=1, 危險方案 Sync=0

Master perforamance

MySQL5.6: InnoDB - NoSQL Key Value Access to InnoDB

實作 Memcached Protocol

Key-value access to InnoDB via familiar Memcached API

Performance Tuning

Defining Performance

Main objective: Users should be satisfied

定義 Performance

performnce metrics

Throughput、Latency / Response time、Scalability、Combined metrics

Main objective: Users (direct or indirect) should be satisfied

User response time = queueing delay + service time

Service Time: Key to the hotspot

Indirect measurements: CPU、Disk IO、Network traffic、Load Average

Benchmark Tests

DBT2: Database Test Suite

漢(オトコ)のコンピュータ道: DBT-2によるベンチマーク手順

SysBench: sysbench sourceforge

SQL 慢的問題: Is this the largest risk/bottleneck?

Check configuration:

system variables: show variables like 'auto%';

Check Status of Server

status variables: show status like 'innodb_buf%';

Flush status; <run query>; show status;

mysqladmin -u -p ... ex -i 15 -r | grep -v 0

MySQL Server Architecture 圖

Connection Thread Pool: Single process, multi thread

Storage Engines:InnoDB Thread engine 直接寫入 DB

Server connections & threads

max_connections (151): per connections memory usage, thread_cache_size(8): max_connections / 3

thread_cache_size(8): Keep up to this amount of thread "cached" after discounnect

mysql> show status;

Max_used_connections

Threads_created: thread_cache misses should be low.

Connection Thread work buffers

soft_buffer_size(2M): often fine at 512K or 1M

other buffers, read, read_rnd, etc...: smaller defaults often OK

mysql> show status; Sort_merge_passes - number of passes made during file merge sort.

check if file sort needs to the done at all

use index if possible

"Server Query Cache"

query_cache_size(0): Amount of memory to use for query cache

Typically 32M is fine, some database need 128M

query_cache_type (ON): Worst case performance overhead is aboue 15%-20%

Favor servers with higher SELECT/WRITE ratios

"Storage Engines": InnoDB Performace Tips

innodb_buffer_pool_size: 80% of memory on Innodb only system, caches data & indexes unlike MyISAM

innodb_log_file_size: A key parameter for wrtie performance

innodb_flush_log_at_trx_commit: 1 (slow) Truly ACID, 2(fase) OS cache on commit, sync to disk once/sec. 0 (fastest) will flush (fsync) log every second or so

innodb_file_per_table: always good choice to distribute i/o, Default ON from 5.6

show engine Innodb status;

Geat Way to see what is going on inside InnoDB, hard to parse: FILE IO / Buffer Pool / Log activity / Row activity

innodb_flush_method = O_DIRECT

innodb_buffer_pool_instances (5.5+)

innodb_io_capactity(5.5+)

innodb_read_io_threads / innodb_write_io_thrads(5.5+)

innodb_io_capactity: default 200 is good for 2 disks striped

innodb_read_io_threads / innodb_write_io_thrads: default 4 is usually good enough

Monitoring Queries: Slow Query Log

Query_time, 重點看 Lock_time.

"mysqldumpslow" helps

show full processlist

Fixing Problem Queries - EXPLAIN

Analyize: How indes are being used, required file sorts, what tables, columns are being queried

MySQL Workbench

MySQL Query Analyzer

MySQL Thread Pool

1024 之後就會往下調.

MySQL Enterprise Edition Whth Thread Pool 1024 以上可以正常運作, MySQL Community Server Without Thread Pool 就會往下調

MySQL 5.7 95% Faster than MySQL 5.6/

MySQL 5.6, 57 速度 > Percona 5.6 > Maria DB 5.6