Had a database issue - dashboard took ages to load due to 13 second query 😱

I've got a project which recently got its first customer and they signed straight up to the pro plan. Awesome news got my first paying customer.

But I don't just want to show the good things that have happened here, I want to be as transparent as possible and also show where things may not have gone to plan and unfortunately this is one of those stories.

They've been on the platform on and off for a couple of months (due to banking issues which are now resolved) but during that time Crash Catch receives around 200 project initialisations a minute. For those that don't know, when you create a project within Crash Catch, you are provided instructions to add to your own development project, such as ReactJS, Android, PHP etc. On load, an initialisation request is sent to Crash Catch with a project ID and API key to confirm a valid account and enable the crash monitoring and submission from the library to the Crash Catch API.

In less than a month Crash Catch has also received over 100,000 crash submissions successfully processed and stored in the database without a hitch.

I use Crash Catch myself, of course, it would be a bit odd if I didn't use my own crash reporting service for my own projects and I slowly started to notice a delay in the dashboard opening once you've logged in. I presumed it was slowing down because the database was growing, and I was right but the problem was too subtle to try and identify where the problem was.

I did code checks and database checks to try and identify anything that was missing or may have been misconfigured in the database or design but couldn't put my finger on anything.

So enabled the MySQL Slow Query Log and identified it was a stored procedure call.

The stored procedure call, not that complex but it does a number of queries. The idea is it get a list of available projects to the user, along with some stats, such as new crashes in the past 24 hours, how many are unresolved etc.

This is a combination of a SELECT INTO type query to insert a filtered set of data into a temporary table, with looping over a cursor running a query with 3 separate sub queries to get the required data out with an UPDATE query on each loop to increment counters within the temporary table and finally a SELECT statement on the temporary table.

When running this stored procedure the database took around 11-13 seconds to complete hence why the dashboard was taking so long to load.

I managed to break the individual sub queries up and run them one by one, on their own they were fairly quick, around 0.003 seconds, but the problem is that it was running in a loop so over several iterations, this can add up.

I then used the stored procedure profiling to see what the timing was on each query the stored proc was doing.

This can be done by doing the following SQL statements

set profiles=1
call proc_name
show profiles;

This list a row for each query that the stored procedure run and how long it took to complete, some of which were taking 1 or 2 seconds to finish.

Breaking up each query within the stored procedure I then used the EXPLAIN command. For example if you have a query such as:

SELECT * FROM table WHERE col=value;

You can run instead:

EXPAIN SELECT * FROM table WHERE col=value;

This doesn't execute the query but outputs how the query will be executed, such as what index are available, and what index were used. There was a particular query where it showed it had possible indexes, but there was no actual index used, it turned out I was missing one of the columns as an index as part of the WHERE clause, so added the index ran the query again, and we'd gone from 13 seconds down to 9, well its an improvement but nowhere near good enough.

Looking at the stored procedure again I twigged that on every loop of the cursor within the stored proc, it was doing an UPDATE command. I figured this might have been the issue, as every UPDATE command by default will cause a COMMIT and then the database has to perform some task such as indexing to facilitate whatever that update was.

So in the stored procedure I started a START TRANSACTION at the very beginning, and just before it does the SELECT I then do a COMMIT. Ran the stored procedure again and the stored procedure call went from 13 seconds down to 0.0, don't think you can get much better than that and now the dashboard loads instantly.

So there you have it, remember your database indexes and transactions, but remember, don't go overboard with index, too many indexes can have a similar performance hit.

So slightly embarrassing mistake, not sure how long this delay has been going on for as it was never mentioned by anyone, just discovered it myself when I was using it, but on the plus side I've learnt a bit more how to debug slow database queries and stored procedures.