Improve your Database issues
In the environment of micro services and high throughput, if you don’t plan and monitor your database status, you may start getting bad performance and poor results (high response time, and many slow queries).
Here at iFood, we have a database which receives data of area and location of merchants/partners. At each every operation, we update so many rows, which lead to the increase of disk saturation and IOPS throughput. Also, slow queries started to pop up, and response time of the micro service raised.
Below there are some simple things we did that helped the database and micro service.
Review indices for your search
First, we did make sure that application (micro service) was using the correct indices. There were some specific operations that should have been using other indices. We created new better indices for most common operations.
Remove indices you are not using
Second, the database and application we were dealing were built some years ago. There were many old and not used indices, which were updated in every ‘update’ sql query. These helped increasing disk saturation and IOPS. Removing indices you are not using will probably have a good impact at your database performance. In our case, we remove around 40 indices in all our entities/database, which lead already to ~20–30% drop of iops and disk saturation.
Increase database instance type
Third, you may review your database instance type and disk size. We upgraded instance we were using, which helped also keeping the disk saturation not too high at peak times.
Cache your data
For last but not least, we reviewed our flows and endpoints which we could cache data, so we remove calls to our database. These will help database performance, by not being triggered with same queries in seconds.
Those actions were taken multiple times (during 2–3 weeks) for multiple flows/entities. They were simple and direct actions, and with a high level of detail and accuracy.
If you like what I am writing, and want to support me, consider buying me a coffe!