Hide Notes 

Slide 1: www.osscube.com       Secrets of Best MySQL Optimization Presented by – Sonali Minocha           OSSCube
Slide 2: www.osscube.com Who Am I?
Slide 3: www.osscube.com Why Tune a  Database?
Slide 4: Who Tunes? www.osscube.com
Slide 5: www.osscube.com What is Tuned?
Slide 6: How much tuning is  enough? www.osscube.com
Slide 7: www.osscube.com
Slide 8: www.osscube.com
Slide 9: www.osscube.com Application Development (Optimizing Queries)
Slide 10: www.osscube.com
Slide 11: www.osscube.com Index Optimizations
Slide 12: www.osscube.com
Slide 13: www.osscube.com
Slide 14: www.osscube.com MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube
Slide 15: www.osscube.com EXPLAIN Types system const eq_ref ref ref_or_null index_merge unique_subquery index_subquery range index ALL The table has only one row At the most one matching row, treated as a constant One row per row from previous tables Several rows with matching index value Like ref, plus NULL values Several index searches are merged Same as ref for some subqueries As above for non-unique indexes A range index scan The whole index is scanned A full table scan
Slide 16: www.osscube.com EXPLAIN Extra Using index Using where Distinct Not exists Using filesort Using temporary Range checked for each record The result is created straight from the index Not all rows are used in the result Only a single row is read per row combination A LEFT JOIN missing rows optimization is used An extra row sorting step is done A temporary table is used The read type is optimized individually for each combination of rows from the previous tables
Slide 17: www.osscube.com Optimizer Hints STRAIGHT_JOIN SQL_BIG_RESULTS Forces the optimizer to join the tables in the given order 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 FORCE INDEX IGNORE INDEX Hints to the optimizer to use the given index Forces the optimizer to use the index (if possible) Forces the optimizer not the use the index
Slide 18: www.osscube.com 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: www.osscube.com • The general query log can be use to  analyze: – Reads vs. writes – Simple queries vs. complex queries – etc
Slide 20: www.osscube.com Database Designing (Optimizing Schemas)
Slide 21: www.osscube.com Normalization
Slide 22: www.osscube.com Table Optimizations
Slide 23: www.osscube.com 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
Slide 24: www.osscube.com MyISAM­Specific  Optimizations
Slide 25: www.osscube.com InnoDB­Specific Optimizations • InnoDB uses clustered indexes • The rows are always dynamic – The length of the PRIMARY KEY is extremely  important – Using VARCHAR instead of CHAR is almost  always better – Many UPDATE/DELETE operations  • The pages can become underfilled • Maintenance operations needed after 
Slide 26: www.osscube.com Monitoring Threads in MySQL
Slide 27: www.osscube.com MEMORY­Specific Optimizations
Slide 28: www.osscube.com Optimizing the Server
Slide 29: www.osscube.com Performance Monitoring
Slide 30: www.osscube.com Tuning MySQL Parameters • Some MySQL options can be changed  online  • The dynamic options are either – SESSION specific – GLOBAL – Both • Changing the value will only affect the current  connection • Changing the value will affect the whole server  • When changing the value SESSION/GLOBAL 
Slide 31: www.osscube.com • Online changes are not persistant over a  server restart • The current values of all options can be  found with  SHOW SESSION/GLOBAL VARIABLES – The configuration files have to be changed as  well
Slide 32: www.osscube.com Status Variables
Slide 33: www.osscube.com SQL/Parser Model Client1 Client2 ClientN MySQL Server Connection Thread Pool Query Cache Parser Query 101101 Storage Engines Optimizer  InnoDB  MyISAM  MERGE  MEMORY  Federated  ARCHIVE  NDBCluster
Slide 34: www.osscube.com 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: www.osscube.com • The Qcache_% status variables help  monitoring the cache • The query cache can be emptied with  RESET QUERY CACHE – The utilisation ratio: Qcache_hits vs.  Com_select 
Slide 36: Some Thread Specific  Options www.osscube.com • 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 
Slide 37: www.osscube.com Some Global Options • table_cache (default 64) – Cache for storing open table handlers – Increase this if Opened_tables is high – Number of threads to keep for reuse – Increase if threads_created is high – Not useful if the client uses connection  pooling • thread_cache (default 0)
Slide 38: www.osscube.com • 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
Slide 39: www.osscube.com 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
Slide 40: www.osscube.com 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 
Slide 41: www.osscube.com 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: www.osscube.com • 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
Slide 43: InnoDB­Specific  Optimization • innodb_log_buffer_size (default  1Mb) www.osscube.com • innodb_log_file_size (default 5Mb) – Size of each InnoDB redo log file – Can be set up to  buffer_pool_size – Larger values allows for larger transactions to  be logged in memory – Sensible values range from 1M to 8M
Slide 44: www.osscube.com Thank you for your time and attention www.osscube.com For more information, please feel free to drop in a line to sonali@osscube.com or visit http://www.osscube.com
Slide 45: www.osscube.com QnA