DEV Community

loading...
Cover image for Techniques to scale your Relational Databases - Part 3

Techniques to scale your Relational Databases - Part 3

vishnuchilamakuru profile image Vishnu Chilamakuru Originally published at vishnuch.tech ・3 min read

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
Partition tables
  • Break up a table by putting hot spots in a separate table to help keep it in memory.
Tune the query cache

References :

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

Buy Me A Coffee

Discussion (0)

Forem Open with the Forem app