CA BD NY

  •  Subscribe in a reader

    Add to Google Reader or Homepage

  • Categories

  • Recent Posts

  • User Services

  • RSS Apache Hacker

    • An error has occurred; the feed is probably down. Try again later.

  • RSS CentOS Hacker

  • RSS Editor's Lists

    • An error has occurred; the feed is probably down. Try again later.

  • Spam Blocked

  •  Subscribe in a reader

    Add to Google Reader or Homepage

    Enter your email address:

  • Our Tweets

    • Performance Best Practices

    To have great performance, you must have:

    • an efficient database scheme
    • an efficient indexing strategy
    • an efficient SQL coding practices
    • an ongoing query profiling/ analysis practice
    • a good understanding of server configuration
    • fast hardware, lots of RAM, and network setup

    Typical Sources of Performance Problem

    • Poor indexing
    • Bad schema design - don’t denormalize from the start
    • Bad SQL coding - sub-queries is slower than JOIN
    • Server misconfiguration or leaving the defaults on in production
    • Hardware/Network Issues - do not use Internet routing for DB connections, use LAN

    Query Profiling

    • Use EXPLAIN religiously
    • Use Slow Query Log with mysqldumpslow
    • Use mytop to see queries that lock and/or takes too long

    Indexing Best Practices

    • Poor indexing kills apps faster than anything
    • Covering indexing - the slimmer the index record is the more you can fit in the index block which makes MySQL does not go to the data records
    • Ensure good selectivity - unique indexes are great but not always possible. If your index field is too common (or same data), say 30% or more, then indexing the field is no good. Uniqueness of the value of index field is very important.  Make composite index, which might be faster than not-so-unique index fields. Order of the fields in multi-column (composite) index matter.
    • Index on  field slows down right
    • Redundant indexes (on the same field) are bad so remove them! Happens in multi-developer projects
    • Index partial fields:  field first name can be indexed for only first 10 characters
    • InnoDB small clustering key since it is appended to every secondary index record

    Database Design Schema Best Practices

    • Use smallest data types - don’t use BIG INT when you do not plan to have billions of records
    • Horizontally split multi-column tables:   table_x, table_x_details
    • Don’t use surrogate keys when you have real primary keys in the table. For example, don’t create an auto increment record_id/id field when you have other id field that can be your primary key. Surrogate keys waste spaces. Remove them
    • Use numeric primary keys instead of CHAR/VAR CHAR keys

    SQL Coding Best Practices

    • Use SET based programming instead of procedure-based (for loop) programming
    • Use stored procedures
    • For InnoDB do not use SELECT count(*) from tables. Instead use counter table.  Increment/decrement counts in a counter table as you add/remove rows
    • MYISAM can get count from index
    • Isolate indexing fields in one side
    • Use Joins instead of sub-queries

    Server Configuration Best Practices

    • Beaware of GLOBAL vs THREAD specific settings - example: key_buffer vs sort_buffer
    • Query caching - use for for read-intensive apps; stores the result set
    • MySQL internal table is MYISAM - leave MYISAM defaults
    • InnoDB buffer pool size - how much memory is allocated - 50 to 80% of your RAM should be set here
    • InnoDB log buffer size - make it bigger
    • More RAM is always the cheapest performance boast - MySQL loves RAM

    If you found this post useful, please subscribe to our RSS feed. Thanks for visiting!