• MySQL Performance Tuning

Developer Session Take-Away  ~ http://www.percona.com/webinars/

a.  Composite pkey is better than secondary index . It avoids secondary B+Tree traversal

b.  If an index need to be created on text column, better create another column text_crc32 = crc32(text) and place both the columns in where condition => emulates Hash Index

c.  In 5.5 – do not ever use dependent subquery (execution plan ignores index in sub query – fixed in 5.6 )

d.  For dynamically growing set of indices, maintain a separate read slave of datawarehouse schema (with all indices )

e.  Patched percona server / maria db -> provides solutions to lock contentions , better log statistics

f.  Everything in InnoiDB is index — so everything gets stored in a clustered index against the primary_key

g.  inserting data : in-order very fast , out-of-order generates fragments ….

h.  mysql starts picking up index from left-most conditions and stops at range-scan

Follow the ‘Effective MySQL’ slides – http://j.mp/EM-Explain                                                              i.      http://effectivemysql.com/downloads/ExplainingTheMySQLEXPLAIN.pdf                                                             ii.      http://effectivemysql.com/downloads/ImprovingPerformanceWithBetterIndexes.pdf                                                                 http://effectivemysql.com/downloads/MySQLIdiosyncrasiesThatBITE.pdf

Tune the parameters

query-cache => disable unless the table is super-static

query-cache-size =

log-slow-query =

max-connections =

log-query-time =


log-file-size =  set it high for write consistency (remember – higher the size higher the crash-recovery time !!!

innodb-io-capacity =

Birds of Feather session on Performance Optimization :

i. http://mysql.rjweb.org/

Character Set and Collation problem solver
Memory Allocation (caching, etc)
Big DELETEs – how to optimize
Alter Huge
Indexing, take 1

ii.      http://dimitrik.free.fr/blog/archives/2010/04/mysql-performance-post-notes-after-uc2010.html

iii.      Drop partition faster than – deleting records.

Create lots of logical shards

iv.      Vertical partitions are useful for – separating metadata from bulky data (blobs ) in the same table

v.      Compound index – should specify the most specific column first , followed by less specific

vi.      Where clause should consider – equality on indexed column, followed by between on other columns

vii.      Be aware of the pitfalls of Query Cache – even varchar 256 will quickly fill up the query cache –  Turn it on – only for super-static tables and set the size as double the required space !

  1. Query cache – can be turned off by –  query_cache_size = 0   ++ /* no_cache/  ++  Set no_cache flag

viii.      Be  mindful – Mysql Partitioning is the least-tested buggy feature !  — Not much parallelism and problems of table locking !

ix.      MySQL Replication will stabilize in 5.6

  1. Percona  presentations : http://www.percona.com/webinars/2012-02-22-explain-demystified/
  2. http://www.percona.com/webinars/2012-03-14-optimizing-mysql-configuration/
  • Hibernate / Connector4J Tuning :
  • Set the parameters and follow the advice of the tutorial :


  1. Calling list() on query –> generates select * ->  that further ignores index and degrades performance
  2. Always convert sub-select to join
  3. Always use à setProjection (Projection.property (“city” ) ) while fetching city
  4. Use optimistic  locking with version number for -> long running transaction
  5. Analyze Hibernate Queries :

i.      Turn on slow query logs

ii.      Use pt-query-digest tool

  • ETL Profiling tool :

n  Data Profiling, Data Cleansing, Data De-duplication : http://wiki.pentaho.com/display/EAI/Human+Inference

  •    Handy MySQL Tools :
  • Best MySQL Monitoring Tool :

i.      WebYog :



ii.      Cacti MySQL Templates : Historical  Trend on MySQL performance

iii.      Nagios : Alerts  (slide)

iv.      http://www.percona.com/webinars/2012-03-28-monitoring-mysql-with-percona-monitoring-plugins/

  1. pt-stalk :   shows events which slowed down the queries ….
  2. Tool to auto-configure my.cnf (to understand the settings ) : https://tools.percona.com/wizard
  3. Pt-query-digest  is best friend for slow query analysis
  4. oprofile – time consumption per resource
  5. GDB backtrace- shows who waiting (lock contentions )
  6. Process analysis :

i.      Innotop

ii.      Mytop

iii.      Pt-mext

iv.      Show processlist\G

v. Mysqladmin extended –r –i1  —       Every 1 second

  1. Online Schema Change :

i.      http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

ii.      Oracle-MySQL 5.6 will include a major improvement on – ad-hoc fast alter-schema – commands


  • Parallelizing MySQL queries :

Shard-query plugin

  • Scale MySQL for Web :



  • Using MySql as NoSql
  1. Memcached plugin, Handler Socket, NDB API etc.
  2. http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
  3. http://www.percona.tv/percona-webinars/understanding-handlersocket-a-nosql-plugin-for-mysql
  4. http://dev.mysql.com/tech-resources/articles/nosql-to-mysql-with-memcached.html
  5. http://www.slideshare.net/daniel_b_austin/a-global-inmemory-data-system-for-mysql
  • MySQL Replication :

 Tungsten Filter can be used as Extract-Filter-Apply (EFA)  process

i.      Facilitates prefetch,  parallel apply

ii.      Works nicely with Xtra Bkup

iii.      Slides : http://www.slideshare.net/datacharmer

iv.      http://www.slideshare.net/jpmalek/building-a-highvolume-reporting-system-on-amazon-aws-with-mysql-tungsten-and

  • Lessons learnt from  Architecture Walk-Through

 Pinterest  : http://speakerdeck.com/u/yashh/p/scaling-pinterest

Instagram : http://www.slideshare.net/iammutex/scaling-instagram

Spill Games :  Use a Global Shared Id  (that includes Data Center Territory Id )  for  sharding the database

  1. Memcached – for –  object  lookup storage
  2. Redis – for –  storing growing list  (feeds, comments, ranks, followers, stream of activities, ordered events, alerts etc. )
  3. Ajax Long Poll + Redis Even Publisher  =>  very fast real time alerts/notifications to browser
  4. Application Router -> should divert the traffic to core services
  •    MySQL  5.6 Features 
  1. http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html
  2. http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html
  3. http://www.mysqlperformanceblog.com/2012/04/04/join-optimizations-in-mysql-5-6-and-mariadb-5-5/
  4. http://www.mysqlperformanceblog.com/2012/03/21/multi-range-read-mrr-in-mysql-5-6-and-mariadb-5-5/