secrets of mysql optimization & performance tuning at osspac 2009
TRANSCRIPT
![Page 1: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/1.jpg)
Secrets of Best MySQL Optimization
Presented by – Sonali Minocha OSSCube
![Page 2: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/2.jpg)
Who Am I?
![Page 3: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/3.jpg)
Why Tune a Database?
![Page 4: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/4.jpg)
Who Tunes?
![Page 5: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/5.jpg)
What is Tuned?
![Page 6: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/6.jpg)
How much tuning is enough?
![Page 7: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/7.jpg)
![Page 8: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/8.jpg)
![Page 9: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/9.jpg)
Application Development(Optimizing Queries)
![Page 10: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/10.jpg)
![Page 11: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/11.jpg)
Index Optimizations
![Page 12: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/12.jpg)
![Page 13: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/13.jpg)
![Page 14: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/14.jpg)
MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube
![Page 15: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/15.jpg)
EXPLAIN Typessystem The table has only one rowconst At the most one matching row, treated as a
constant
eq_ref One row per row from previous tablesref Several rows with matching index valueref_or_null Like ref, plus NULL valuesindex_merge Several index searches are mergedunique_subquery Same as ref for some subqueriesindex_subquery As above for non-unique indexesrange A range index scanindex The whole index is scannedALL A full table scan
![Page 16: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/16.jpg)
EXPLAIN ExtraUsing index The result is created straight from the index
Using where Not all rows are used in the result
Distinct Only a single row is read per row combination
Not exists A LEFT JOIN missing rows optimization is used
Using filesort An extra row sorting step is done
Using temporary A temporary table is used
Range checked for each record
The read type is optimized individually for each combination of rows from the previous tables
![Page 17: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/17.jpg)
Optimizer HintsSTRAIGHT_JOIN Forces the optimizer to join the tables in the
given order
SQL_BIG_RESULTS Together with GROUP BY or DISTINCT tells the server to use disk-based temp tables
SQL_BUFFER_RESULTS Tells the server to use a temp table, thus releasing locks early (for table-locks)
USE INDEX Hints to the optimizer to use the given index
FORCE INDEX Forces the optimizer to use the index (if possible)
IGNORE INDEX Forces the optimizer not the use the index
![Page 18: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/18.jpg)
Selecting Queries to Optimize• The slow query log
– Logs all queries that take longer than long_query_time
– Can also log all queries that don’t use indexes with --log-queries-not-using-indexes
– To log slow administrative commands use --log-slow-admin-statements
– To analyze the contents of the slow log use mysqldumpslow
![Page 19: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/19.jpg)
• The general query log can be use to analyze:– Reads vs. writes– Simple queries vs. complex queries– etc
![Page 20: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/20.jpg)
Database Designing(Optimizing Schemas)
![Page 21: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/21.jpg)
Normalization
![Page 22: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/22.jpg)
Table Optimizations
![Page 23: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/23.jpg)
Choosing Best Suited Storage Engine
• Understanding benefits and drawbacks of each storage engine is very important while designing application.
• Different storage engine has different index capability ,application need should be kept in mind while choosing storage engine
![Page 24: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/24.jpg)
MyISAM-Specific Optimizations
![Page 25: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/25.jpg)
InnoDB-Specific Optimizations
• InnoDB uses clustered indexes– The length of the PRIMARY KEY is extremely
important
• The rows are always dynamic– Using VARCHAR instead of CHAR is almost always
better
• Maintenance operations needed after – Many UPDATE/DELETE operations
• The pages can become underfilled
![Page 26: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/26.jpg)
Monitoring Threads in MySQL
![Page 27: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/27.jpg)
MEMORY-Specific Optimizations
![Page 28: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/28.jpg)
Optimizing the Server
![Page 29: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/29.jpg)
Performance Monitoring
![Page 30: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/30.jpg)
Tuning MySQL Parameters
• Some MySQL options can be changed online • The dynamic options are either
– SESSION specific• Changing the value will only affect the current
connection
– GLOBAL• Changing the value will affect the whole server
– Both• When changing the value SESSION/GLOBAL
should be specified
![Page 31: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/31.jpg)
• Online changes are not persistant over a server restart– The configuration files have to be changed as well
• The current values of all options can be found with SHOW SESSION/GLOBAL VARIABLES
![Page 32: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/32.jpg)
Status Variables
![Page 33: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/33.jpg)
SQL/Parser Model
Client2 ClientN
Connection Thread Pool
Parser Query 101101
Client1
Optimizer
Storage Engines InnoDB MyISAM MERGE MEMORY Federated ARCHIVE NDBCluster
Query Cache
MySQL Server
![Page 34: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/34.jpg)
Query Cache
• Stores SELECT queries and their results • Purpose: improve performance for
frequently requested data• The data in the query cache is invalidated as
soon as a modification is done in the table• Controlled with the query_cache_size
variable
![Page 35: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/35.jpg)
• The Qcache_% status variables help monitoring the cache– The utilisation ratio: Qcache_hits vs. Com_select
• The query cache can be emptied with RESET QUERY CACHE
![Page 36: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/36.jpg)
Some Thread Specific Options
• read_buffer_size (default 128Kb) and read_rnd_buffer_size (default 256Kb)– Size of cache used for table scanning– Not equivalent to block size
• The database is not divided into blocks but directly into records
– Increase if you do many sequential scans• sort_buffer_size (default 2Mb)
– Size of the GROUP BY / ORDER BY cache– If more memory is needed it will be taken from the disk
• tmp_table_size (default 32Mb)– Limit after which temporary tables will not be MEMORYs
anymore, but MyISAM tables
![Page 37: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/37.jpg)
Some Global Options
• table_cache (default 64)– Cache for storing open table handlers– Increase this if Opened_tables is high
• thread_cache (default 0)– Number of threads to keep for reuse– Increase if threads_created is high– Not useful if the client uses connection pooling
![Page 38: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/38.jpg)
• max_connections (default 100)– The maximum allowed number of simultaneous
connections– Very important for tuning thread specific memory
areas– Each connection uses at least thread_stack of
memory
![Page 39: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/39.jpg)
MyISAM Global Options
• key_buffer_size (default 8Mb)– Cache for storing indices– Increase this to get better index handling– Miss ratio
(key_reads/key_read_requests) should be very low, at least < 0.03 (often < 0.01 is desirable)
• Row caching is handled by the OS
![Page 40: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/40.jpg)
MyISAM Thread-Specific Options
• myisam_sort_buffer_size (default 8Mb)– Used when sorting indexes during REPAIR/ALTER TABLE
• myisam_repair_threads (default 1)– Used for bulk import and repairing– Allows for repairing indexes in multiple threads
• myisam_max_sort_file_size– The max size of the file used while re-creating
indexes
![Page 41: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/41.jpg)
InnoDB-Specific Optimization
• innodb_buffer_pool_size (default 8Mb)– The memory buffer InnoDB uses to cache both
data and indexes– The bigger you set this the less disk i/o is
needed– Can be set very high (up to 80% on a dedicated
system)
![Page 42: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/42.jpg)
• innodb_flush_log_at_trx_commit (default 1)– 0 writes and sync’s once per second (not ACID)– 1 forces sync to disk after every commit– 2 write to disk every commit but only sync’s about
once per second
![Page 43: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/43.jpg)
InnoDB-Specific Optimization
• innodb_log_buffer_size (default 1Mb)– Larger values allows for larger transactions to be
logged in memory– Sensible values range from 1M to 8M
• innodb_log_file_size (default 5Mb)– Size of each InnoDB redo log file– Can be set up to buffer_pool_size
![Page 44: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/44.jpg)
Thank you for your time and attention
For more information, please feel free to drop in a line to [email protected] or visit http://www.osscube.com
www.osscube.com
![Page 45: Secrets Of MySQL Optimization & Performance Tuning At OSSPAC 2009](https://reader034.vdocuments.us/reader034/viewer/2022050613/543f6d538d7f72a9518b5b7d/html5/thumbnails/45.jpg)
Q n A