MySQL Database Design and Tuning

Index

[SYMBOL] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] large-scale data    importation problems

         diagnosing (High-Hat Airways scenario) 2nd 3rd 4th         High-Hat Airways scenario 2nd 3rd         resolving (High-Hat Airways scenario) 2nd 3rd 4th leading wildcards (indexes) 2nd

leaf nodes

     B-tree indexeslength of transactions     dangers

         deadlocks

         degraded concurrency         resource consumption

         rollback issues         rollback segments    problems

         prevention of

         user interface communication

LIMIT statement

    queries         limiting 2nd 3rd 4th

Linux

     performance tips         hdparm command 2nd

         mount command 2nd    performance tools         free

         IBM Performance Monitoring (rmfpms) 2nd

         iostat

         netstat 2nd

         ps 2nd 3rd 4th

         Red Hat System Monitor

         top

         vmstat

     tuning tips 2nd

         disabling unnecessary services 2nd

         recent kernel upgrades 2nd

local checkpoints

lock concurrency (InnoDB)

locking

     granularity levels

         page locks

         row locks         table locks

         table locks, explicit requests 2nd

     InnoDB 2nd 3rd         exclusive next-key locks 2nd 3rd         exclusive non-next-key locks 2nd         lock concurrency         monitoring (SHOW INNODB STATUS command) 2nd

         row-level locking scope 2nd         shared next-key locks         shared same-record locks 2nd    tables

         data reload preparations 2nd

    types         READ 2nd         WRITE

log files

    InnoDB         configuring 2nd 3rd 4th

         crash recovery         function of         innodb flush log at trx commit setting

         innodb log buffer setting

         innodb log buffer size setting

         innodb log file size setting

         innodb log files in group setting         innodb log waits setting

         innodb log write requests setting

         innodb log writes setting         innodb os log pending writes setting

         monitoring 2nd 3rd 4th         rollbacks         tuning 2nd 3rd 4th

logging

    indexes

         --log-queries-not-using indexes parameter 2nd

    management nodes

         clustering issues (MySQL Cluster) 2nd

    master servers

         overhead costs 2nd

logging tools (performance monitoring)

     binary log 2nd

     error log 2nd

     general query log 2nd

     slow query log 2nd

lookup tables

     design mistakes 2nd