MySQL パフォーマンスチューニング&ベンチマーク - MySQLカンファレンス2007
http://www.mysql-ucj2007.jp/details/e13.html
Agenda
- An introduction to Benchmarking
- data structures
- query optimisation and query cache
- etc..
Why Benchmark?
The Good Scientists Guide to Benchmarking
- The scientific method suggests changing only one variable at a time
- The scientific method suggests repetition, more than once to verify results. If results vary greatly, think about taking averages
- Repeat, rinse , repeat , rinse!
- 少なくとも3回は。
The Good Scientists Guide to Benchmarking II
- Isolate your environment
- Use a different MySQL instance
- Savle all configurations!
Benchmarking Tools
Benchmarking Tools II
Slow Query Log
- log_slow_queries=/var/lib/mysql/slow-queries.log
- long_query_time=2
- Then, use mysqldumpslow
- In 5.1, you can log these details ...
-
-
EXPLAIN columns
- select_type
- table
- ...
「オンラインで読んで」
Scans and seeks
- A seek, jumps into a random place to fetch needed data.
- A scan will jump to the start of the data...
When do you get a full table scan?
- No WHERE conditions
- No index on any field in WHERE condition
- When your range returns a large number of rows, i.e. too many records in WHERE condition
- Pre-MySQL 5, using OR in a WHERE clause
- SELECT * FROM
Subqueries
- Don't use them; replace with a JOIN
- unique_subquery: results are known to be distinct
- index_subquery: otherwise
- Co-related subqueries are worse
- ...
Indexes
Good Schema Practice
- Use small data types
- Is a BIGINT really required?
- Small data types allow more index and data recoreds to fit into a single block of memory
- Normalise first, de-normalise later
- Generally, 3NF works pretty well
正規化しすぎない。
Storing IP addresses
- IP addresses always become an INT UNSIGNED
- Each subnet corresponds to an 8-byte division of the underlying INT UNSIGNED
- From string to int? Use INET_ATON()
- From into to ...
Query Cache
- スライド
- たくさんのストレージエンジンがある
- Understand your applications read/write ratio for most effective use
- Compromise between CPU usage and read performance
- Remember that the bigger your query cache, you may not see better performance, even if your application is read heavy
Query Cache Invalidation
キャッシュの無効化
- Coarse invalidation designed to prevent CPU overuse
- ...
- Thusm any modification to any table referenced in the SELECT will invali...
Choosing a Storage Engine
- MySQLs strong point: many engines MySQLはたくさんエンジンがあるよ
- Use InnoDB for most operations (esp. OLTP), except:
- big, read only tables
- high volume streaming tables(logging)
- specialised needs (have special engines)
- Tune InnoDB wisely
PBXがブログとかにいい。
Quick InnoDB Tuning Tips
Real World MySQL Use (RWMU)
- Run many servers
- Your serious application cannot run on "the server"
- "Shared nothing" architecture
- make no single point of contention in the system
- Scales well, just by adding cheap nodes
- If it works for Google, it will work for you!
RWMU: State and Session Information
- Dont keep state within the application server
- Key to being stateless: session data
- Cookies are best validated by checksums and timestamps(encrypting is a waste of CPU cycles)
RWMU: Caching
RWMU : Data Partitioning
- replication is great for read heavy applications
- Write intensive applications should look at partitioning
- Partition with a global master server in mind
- Give out global PKs and cache heavily (memcached)
- ...
- Consider the notion of summary databases
RWMU: Blobs
- Large binary object storage is interesting
- Image data is best kept in the filesystem, just use metadata in DB to reference server and path to filename
- Try the Amazon S3 storage engine?
- ...
RWMU: Misc.tips
Resources
services
- http://www.mysql.com/training/
- 日本語における認定が今月末(2007年9月末)から始まる
