know  more  about  MySQL  open  source  database   1   Salaria  Software  Services   

—  Storage  engines   —  Schema   —  Normalization   —  Data  types   —  Indexes   —  Know  more  about  your  SQL  queries  -­‐  Using  EXPLAIN     —  Character  set  and  Collation   —  Resources   2   Salaria  Software  Services   

—  Generics  are  inefficient   •  If  you  have  chosen  MySQL,  take  advantage  of  its   strengths   •  Having  an  understanding  of  the  database  helps  you   develop  better-­‐performing  applications   ü better  to  design  a  well-­‐performing  database-­‐driven   application  from  the  start   ü than  try  to  fix  a  slow  one  after  the  fact!   3   Salaria  Software  Services   

Salaria  Software  Services   4   —  MySQL  supports  several   storage  engines  that  act   as  handlers  for  different   table  types   ü No  other  database   vendor  offers  this   capability   

—  Dynamically  add  and  remove  storage  engines.   —  Change  the  storage  engine  on  a  table  with                “ALTER  TABLE    ENGINE=InnoDB;”   5   Salaria  Software  Services   

—  Concurrency:  some  have  more  granular  locks  than   others   ü right  locking  can  improve  performance.   •  Storage:  how  the  data  is  stored  on  disk   ü page  size  for  tables,  indexes,  format  used   •  Indexes:  b-­‐trees,  hash   •  Memory  usage   ü caching  strategy   •  Transactions:   ü not  every  application  table  needs  transactions   6   Salaria  Software  Services   

“As  a  developer,  what  do  I  need  to  know   about  storage  engines,  without  being  a   MySQL  expert?”   •  Keep  in  mind  the  following  questions:   ü What  type  of  data  will  you  be  storing?   ü Is  the  data  constantly  changing?   ü Is  the  data  mostly  logs  (INSERTs)?   ü requirements  for  reports?   ü need  for  transaction  control?   7   Salaria  Software  Services   

—  Default  MySQL  engine   •  high-­‐speed  Query  and  Insert  capability   ü insert  uses  shared  read  lock   ü updates,  deletes  use  table-­‐level  locking,  slower   •  full-­‐text  indexing   ü Good  for  text  search   •  Non-­‐transactional,  No  foreign  key  support   •  good  choice  for  :   ü read-­‐only  or  read-­‐mostly  application  tables  that  don't   require  transactions   ü Web,  data  warehousing,  logging,  auditing   8   Salaria  Software  Services   

—  Transaction-­‐safe  and  ACID  (atomicity,  consistency,   isolation,  durability)  compliant   ü  crash  recovery,  foreign  key  constraints   •  good  query  performance,  depending  on  indexes   •  row-­‐level  locking,  Multi  Version  Concurrency  Control   (MVCC)   ü  allows  fewer  row  locks  by  keeping  data  snapshots   –  Depending  on  the  isolation  level,  no  locking  for  SELECT   –  high  concurrency  possible   •  uses  more  disk  space  and  memory  than  ISAM   •  Good  for  Online  transaction  processing  (OLTP)   ü  Lots  of  users:  eBay,  Google,  Yahoo!,  Facebook,  etc.   9   Salaria  Software  Services   

—  Entirely  in-­‐memory  engine   ü stores  all  data  in  RAM  for  extremely  fast  access   ü Updated  Data  is  not  persisted   –    table  loaded  on  restart     •  Hash  index  used  by  default   •  Good  for   ü Summary  and  transient  data   ü "lookup"  tables,   ü calculated  table  counts,   ü for  caching,  temporary  tables   10   Salaria  Software  Services   

—  Incredible  insert  speeds   •  Great  compression  rates  (zlib)?   ü Typically  6-­‐8x  smaller  than  MyISAM   •  No  UPDATEs   •  Ideal  for  storing  and  retrieving  large  amounts  of   historical  data   ü audit  data,  log  files,Web  traffic  records   ü Data  that  can  never  be  updated   11   Salaria  Software  Services   

Salaria  Software  Services   12   —  You  can  use  multiple   storage  engines  in  a   single  application   ü  A  storage  engine  for  the   same  table  on  a  slave  can   be  different  than  that  of   the  master   •  Choose  storage  engine   that's  best  for  your   applications  requirements   ü  can  greatly  improve   performance   

—  Creating  a  table  with  a  specified  engine   ü CREATE  TABLE  t1  (...)          ENGINE=InnoDB;   •  Changing  existing  tables   ü ALTER  TABLE  t1  ENGINE=MyISAM;   •  Finding  all  your  available  engines   ü SHOW  STORAGE  ENGINES;   13   Salaria  Software  Services   

Basic  foundation  of  performance   •  Normalization   •  Data  Types   ü Smaller,  smaller,  smaller   -­‐  Smaller  tables  use  less  disk,  less  memory,  can  give  better   performance   •  Indexing   ü Speeds  up  retrieval   14   Salaria  Software  Services   

—  Eliminate  redundant  data:   ü   Don't  store  the  same  data  in  more  than  one  table   ü   Only  store  related  data  in  a  table     ü   reduces  database  size  and  errors   15   Salaria  Software  Services   

—  updates  are  usually  faster.   ü there's  less  data  to  change.   •  tables  are  usually  smaller,  use  less  memory,  which   can  give  better  performance.   •  better  performance  for  distinct  or  group  by  queries   16   Salaria  Software  Services   

—  However  Normalized  database  causes  joins  for   queries   •    excessively  normalized  database:   ü queries  take  more  time  to  complete,  as  data  has  to  be   retrieved  from  more  tables.   •  Normalized  better  for  writes  OLTP   •  De-­‐normalized  better  for  reads  ,  reporting   •  Real  World  Mixture:   ü normalized  schema   ü Cache  selected  columns  in  memory  table   17   Salaria  Software  Services   

Smaller  =>  less  disk  =>  less  memory  =>  better   performance   —  Use  the  smallest  data  type  possible   •  The  smaller  your  data  types,  The  more  index  (and   data)  can  fit  into  a  block  of  memory,  the  faster  your     queries  will  be.   ü Period.   ü Especially  for  indexed  fields   18   Salaria  Software  Services   

—  MySQL  has  9  numeric  data  types   ü  Compared  to  Oracle's  1   •    Integer:   ü  TINYINT  ,  SMALLINT,  MEDIUMINT,  INT,  BIGINT   ü  Require  8,  16,  24,  32,  and  64  bits  of  space.   •  Use  UNSIGNED  when  you  don't  need  negative  numbers  –  one  more  level  of   data  integrity   •  BIGINT  is  not  needed  for  AUTO_INCREMENT   ü  INT  UNSIGNED  stores  4.3  billion  values!   ü  Summation  of  values...  yes,  use  BIGINT   —  Floating  Point:  FLOAT,  DOUBLE   ü  Approximate  calculations   •  Fixed  Point:  DECIMAL   ü  Always  use  DECIMAL  for  monetary/currency  fields,  never  use  FLOAT  or   DOUBLE!   •  Other:  BIT     ü  Store  0,1  values   19   Salaria  Software  Services   

—  VARCHAR(n)  variable  length   ü uses  only  space  it  needs   –  Can  save  disk  space  =  better  performance   ü   Use  :   –  Max  column  length  >  avg   –  when  updates  rare  (updates  fragment)   •  CHAR(n)  fixed  length   ü Use:   –  short  strings,  Mostly  same  length,  or  changed  frequently   20   Salaria  Software  Services   

—  Always  define  columns  as  NOT  NULL   –  unless  there  is  a  good  reason  not  to   ü Can  save  a  byte  per  column   ü nullable  columns  make  indexes,  index  statistics,  and   value  comparisons  more  complicated.   •  Use  the  same  data  types  for  columns  that  will  be   compared  in  JOINs   ü Otherwise  converted  for  comparison   •  Use  BLOBs  very  sparingly   ü Use  the  filesystem  for  what  it  was  intended   21   Salaria  Software  Services   

“The  more  records  you  can  fit  into  a  single  page  of   memory/disk,  the  faster  your  seeks  and  scans  will  be.”   •  Use  appropriate  data  types   •  Keep  primary  keys  small   •  Use  TEXT  sparingly   ü Consider  separate  tables   •  Use  BLOBs  very  sparingly   ü Use  the  filesystem  for  what  it  was  intended   22   Salaria  Software  Services   

—  Indexes  Speed  up  Queries,   ü SELECT...WHERE  name  =  'carol'   ü only  if  there  is  good  selectivity:   –  %  of  distinct  values  in  a  column   •  But...  each  index  will  slow  down  INSERT,  UPDATE,   and  DELETE  operations   23   Salaria  Software  Services   

—  Always  have  an  index  on  join  conditions   •  Look  to  add  indexes  on  columns  used  in  WHERE  and   GROUP  BY  expressions   •  PRIMARY  KEY,  UNIQUE  ,  and  Foreign  key  Constraint   columns  are  automatically  indexed.   ü other  columns  can  be  indexed  (CREATE  INDEX..)   24   Salaria  Software  Services   

—  use  the  MySQL  slow  query  log  and  use  Explain   •  Append  EXPLAIN  to  your  SELECT  statement   ü shows  how  the  MySQL  optimizer  has  chosen  to  execute   the  query   •  You  Want  to  make  your  queries  access  less  data:   ü are  queries  accessing  too  many  rows  or  columns?   –  select  only  the  columns  that  you  need   •  Use  to  see  where  you  should  add  indexes   ü Consider  adding  an  index  for  slow  queries  or  cause  a  lot   of  load.   –  ensures  that  missing  indexes  are  picked  up  early  in  the   development  process   25   Salaria  Software  Services   

—  Find  and  fix  problem  SQL:   •  how  long  a  query  took   •  how  the  optimizer  handled  it   ü Drill  downs,  results  of  EXPLAIN  statements   •  Historical  and  real-­‐time  analysis   ü query  execution  counts,  run  time      “Its  not  just  slow  running  queries  that  are  a  problem,   Sometimes  its  SQL  that  executes  a  lot  that  kills  your   system”   26   Salaria  Software  Services   

Salaria  Software  Services   27   —  Just  append  EXPLAIN  to   your  SELECT  statement   •  Provides  the  execution  plan   chosen  by  the  MySQL   optimizer  for  a  specific   SELECT  statement   ü  Shows  how  the  MySQL   optimizer  executes  the   query   •  Use  to  see  where  you   should  add  indexes   ü  ensures  that  missing   indexes  are  picked  up   early  in  the  development   process   

—  A  character  set  is  a  set  of  symbols  and  encodings.     —  A  collation  is  a  set  of  rules  for  comparing  characters  in  a   character  set.     —  MySQL  can  do  these  things  for  you:     ü  Store  strings  using  a  variety  of  character  sets     ü  Compare  strings  using  a  variety  of  collations     ü  Mix  strings  with  different  character  sets  or  collations  in  the   same  server,  the  same  database,  or  even  the  same  table     ü  Allow  specification  of  character  set  and  collation  at  any  level      -­‐  Mysql  >  SET  NAMES  'utf8';      -­‐  Mysql  >  SHOW  CHARACTER  SET   28   Salaria  Software  Services   

—  Two  different  character  sets  cannot  have  the  same   collation.     —  Each  character  set  has  one  collation  that  is  the  default   collation.  For  example,  the  default  collation  for  latin1  is   latin1_swedish_ci.  The  output  for  “SHOW  CHARACTER   SET”  indicates  which  collation  is  the  default  for  each   displayed  character  set.     —  There  is  a  convention  for  collation  names:  They  start  with   the  name  of  the  character  set  with  which  they  are   associated,  they  usually  include  a  language  name,  and  they   end  with  _ci  (case  insensitive),  _cs  (case  sensitive),  or  _bin   (binary).     29   Salaria  Software  Services   

—    —    —  CREATE  DATABASE  mydatabase  DEFAULT  CHARACTER   SET  utf8  COLLATE  utf8_general_ci;   —  CREATE  TABLE  mytable  (      mydata  VARCHAR(128)  NOT  NULL   )  ENGINE=MyISAM  DEFAULT  CHARACTER  SET  utf8   COLLATE  utf8_general_ci;   30   Salaria  Software  Services   

—  MySQL  Forge  and  the  Forge  Wiki   http://forge.mysql.com/   —  Planet  MySQL    http://planetmysql.org/   —  MySQL  DevZone   http://dev.mysql.com/   —  High  Performance  MySQL  book   31   Salaria  Software  Services   

?   32   Salaria  Software  Services   

Salaria  Software  Services   33   Mahesh  Salaria   [email protected]   Salaria  Software  Services   http://salariasoft.com