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!
Apache Hacker