Secrets Of MySQL Optimization

Secrets of Best MySQL Optimization : 

Secrets of Best MySQL Optimization Presented by – Sonali Minocha OSSCube

Who Am I? : 

Who Am I?

Why Tune a Database? : 

Why Tune a Database?

Who Tunes? : 

Who Tunes?

What is Tuned? : 

What is Tuned?

How much tuning is enough? : 

How much tuning is enough?

Application Development(Optimizing Queries) : 

Application Development(Optimizing Queries)

Index Optimizations : 

Index Optimizations

Slide 14: 

MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

EXPLAIN Types : 

EXPLAIN Types

EXPLAIN Extra : 

EXPLAIN Extra

Optimizer Hints : 

Optimizer Hints

Selecting Queries to Optimize : 

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

Slide 19: 

The general query log can be use to analyze: Reads vs. writes Simple queries vs. complex queries etc

Database Designing(Optimizing Schemas) : 

Database Designing(Optimizing Schemas)

Normalization : 

Normalization

Table Optimizations : 

Table Optimizations

Choosing Best Suited Storage Engine : 

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

MyISAM-Specific Optimizations : 

MyISAM-Specific Optimizations

InnoDB-Specific Optimizations : 

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

Monitoring Threads in MySQL : 

Monitoring Threads in MySQL

MEMORY-Specific Optimizations : 

MEMORY-Specific Optimizations

Optimizing the Server : 

Optimizing the Server

Performance Monitoring : 

Performance Monitoring

Tuning MySQL Parameters : 

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

Slide 31: 

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

Status Variables : 

Status Variables

SQL/Parser Model : 

SQL/Parser Model Client2 ClientN Client1 MySQL Server

Query Cache : 

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

Slide 35: 

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

Some Thread Specific Options : 

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

Some Global Options : 

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

Slide 38: 

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

MyISAM Global Options : 

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

MyISAM Thread-Specific Options : 

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

InnoDB-Specific Optimization : 

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)

Slide 42: 

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

InnoDB-Specific Optimization : 

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

Slide 44: 

Thank you for your time and attention For more information, please feel free to drop in a line to sonali@osscube.com or visit http://www.osscube.com www.osscube.com

Slide 45: 

Q n A