Techniques to scale your Relational Databases - Part 3
Tags: database, devops, performance, sql
Published: 2021-05-11 07:08:47

{% link https://dev.to/vishnuchilamakuru/techniques-to-scale-your-relational-databases-part-1-3lj3 %}

{% link https://dev.to/vishnuchilamakuru/techniques-to-scale-your-relational-databases-part-2-5be %}

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](http://httpd.apache.org/docs/2.2/programs/ab.html).
* **Profile** - Enable tools such as the [slow query log](http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html) 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](http://stackoverflow.com/questions/1017239/how-do-null-values-affect-performance-in-a-database-search).

##### **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](https://en.wikipedia.org/wiki/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](https://dev.to/vishnuchilamakuru/techniques-to-scale-your-relational-databases-part-2-5be#denormalization) 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](https://dev.mysql.com/doc/refman/5.7/en/query-cache.html) could lead to [performance issues](https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/).


## **References** :
* [Scalability, availability, stability, patterns](http://www.slideshare.net/jboner/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.

<hr/>

## 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](https://twitter.com/vishnuchi).
- Subscribe to my [Newsletter here](https://www.getrevue.co/profile/vishnuch).

<a href="https://www.buymeacoffee.com/vishnuchi" target="_blank"><img src="https://cdn.buymeacoffee.com/buttons/v2/default-yellow.png" alt="Buy Me A Coffee" style="height: 60px !important;width: 150px !important;" ></a>