2 years ago

Category: Laravel, Laravel

A while back, I was facing some performance issues with an application in production and this post is a walkthrough of the problem I was facing, and the steps I took to solve the problem. I am by no means an expert with databases, but as you'll see later in the post, I read some great articles by people who clearly knew more about this subject than I did, and this post is more about painting the full picture of how I knew I had a problem, how I diagnosed it, and then how I applied the knowledge I learnt from the aforementioned articles to improve my database average query time by up to 3,600%, drop CPU usage from 80% to 11%, and increased throughput of my queued jobs from 9 jobs per minute to 900.

One key bit of advice I picked out of this post was: We advise to create one index per unique query for better performance.

Using this index in such a state is almost equivalent to running a sequential scan as the data is not in state that we can efficiently read it; as we are wanting to filter by item_id first and then lookup price.

Looking in Horizon, I saw that this queue was processing roughly 9 records per minute, and was pretty much grinding the database to a halt doing so.