Techniques to scale your Relational Databases - Part 3

This blog post is a continuation of my previous blog posts mentioned above. In my previous posts, I mentioned Scaling Relational Databases using

• Replication
• Federation
• Sharding
• Denormalization

In this post, I will mention more about SQL Tuning. SQL tuning is a broad topic and many books have been written as reference.

It's important to benchmark and profile to simulate and uncover bottlenecks.

• Benchmark - Simulate high-load situations with tools such as ab.
• Profile - Enable tools such as the slow query log to help track performance issues.

Benchmarking and profiling might point you to the following optimizations.

Tighten up the schema

• MySQL dumps to disk in contiguous blocks for fast access.
• Use TEXT for large blocks of text such as blog posts. TEXT also allows for boolean searches. Using a TEXT field results in storing a pointer on the disk that is used to locate the text block.
• Use INT for larger numbers up to 2^32 or 4 billion.
• Use DECIMAL for currency to avoid floating-point representation errors.
• Avoid storing large BLOBS, store the location of where to get the object instead.
• VARCHAR(255) is the largest number of characters that can be counted in an 8-bit number, often maximizing the use of a byte in some RDBMS.
• Set the NOT NULL constraint where applicable to improve search performance.

Use good indices

• Columns that you are querying (SELECT, GROUP BY, ORDER BY, JOIN) could be faster with indices.
• Indices are usually represented as self-balancing B-tree that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.
• Placing an index can keep the data in memory, requiring more space.
• Writes could also be slower since the index also needs to be updated.
• When loading large amounts of data, it might be faster to disable indices, load the data, then rebuild the indices.

Avoid expensive joins

• Denormalize where performance demands it.

Partition tables

• Break up a table by putting hot spots in a separate table to help keep it in memory.

Tune the query cache

• In some cases, the query cache could lead to performance issues.

References :

• Scalability, availability, stability, patterns

This is the last post as part of Techniques to scale your Relational Databases series. Hope you enjoyed this 3 part series of blog posts.

---

Thank you for reading

Hope you find these resources useful. If you like what you read and want to see more about system design, microservices, and other technology-related stuff... You can follow me on

• Twitter here.
• Subscribe to my newsletter above.