Latency is a term widely used in software engineering, especially in the context of performance. Latency, in simple terms, is the time delay in the cause and the effect in a system. For instance, database latency is the time taken to send a query, process it, and return the data. This is very important to evaluate the end user’s experience and the overall performance of the system. There are several factors that influence it. In this article, we go over database latency, its causes, and methods to measure and reduce it.
Let’s look at an example of a request from a web application. Imagine the following form. The first three fields are normal text inputs where you enter your title, name, and age. The first dropdown asks you to select a company. The second dropdown is not available at this point. Once you select a company, it displays a list of all positions available in that company. This may be a hundred positions or can even go up to a thousand.
There may be a brief moment where you see a loading spinner for the last dropdown. It then immediately gets populated with the list of positions. So, what just happened here?
For the sake of this example, let’s say we have a three-tier architecture for this application. The front end is on HTML+CSS+JS, the service layer on Spring Boot, and the database on MySQL. As soon as you select Company A on the dropdown, the front end sends a request to the service layer passing Company A as an argument. The service layer then performs the business logic. Here, it sends a database query to retrieve all positions from Company A. The entire list of positions is then returned from the database to the service layer and back to the front end. During this time, to ensure a good user experience, a loading spinner is used. Once the front end receives the results, the spinner disappears and the actual data takes its place.
Database latency here is the time taken for the server to send the query to the database and get back the results. The overall latency for the end user, however, is the time taken from selecting Company A to displaying all the positions in the Positions dropdown.
Several factors can influence database latency. We have categorized them into three main areas. If your application is suffering from high latency, the best suggestion would be to check these one by one:
- Hardware-related factors: The underlying hardware on which your application runs can impact latency. Older hard disks can introduce latency that you can reduce to an extent using solid-state drives which have significantly better read speeds.
- Software-related factors: The performance of queries can dramatically affect your database latency. Optimizing database queries can help lower the latency and reduce the database resources needed to return data. If you want to learn more about queries, see our Introduction to Queries in our MySQL for Developers course to learn how to write performant queries. You can also use indexes to increase the speed of read operations. Adding indexes that are sometimes missing, or removing those that are unused, also decreases latency. See our Introduction to Indexes Course to learn more about indexes work and when it is best to use them. PlanetScale Boost can also help reduce database latency for frequently used queries that you might want to cache.
- Network-related factors: This is something that cannot be avoided. In our example above, we talked about the overall latency being more than the actual database latency itself. The physical distance between your application servers and database matters. Based on the criticality of the application and the location of your end users, you must choose the hardware and its location carefully. In a real-world scenario, a request may need to travel through more than one network with different bandwidths and different mediums before being served. Using a data center close to your end user’s location can reduce this. PlanetScale has a very innovative feature called Portals that allows users to read data from locations closest to them irrespective of where their application is deployed.
Latency is typically measured in seconds or milliseconds. When measuring database latency, you must make sure you do a wide range of test cases to ensure that you’re not projecting unrealistic numbers. See how PlanetScale measured latency for their HTTP/3 API for MySQL using a wide range of scenarios.
PlanetScale Insights gives you a detailed look into the active queries running against your database. You can get detailed metrics right from the PlanetScale dashboard, no additional tools needed. Click on “Insights” from the top menu to view query latency, queries per second, rows read, and more. You have the option to choose the time frame, view events, and filter queries. This is extremely useful for troubleshooting and performance tuning.
So far, we’ve seen what database latency looks like and how it is caused. But how does increased database latency affect the performance of an application? If you’re on the support team of a software company, you might’ve heard users saying things like, “This used to take 10 seconds before, but now it takes a whole minute,” or, “I’m stuck on the loading screen after logging in.” Using PlanetScale Insights can help you see where queries have high latency before users even have to report it. This helps in narrowing down the part of the application that is causing delays. For instance, if a user is generating a report that used to take 10 seconds before and takes one minute now, you should probably look at the queries that generate those reports. Once that is done, you may start looking at the code. As mentioned in the section above, check if there are any nested loops, blocking processes, or multithreaded activities that are causing delays. Check the database queries to see if you can tune them. Adding an index, caching, or sharding may help but it totally depends on your use case. Please be very careful when making database changes and ensure you do it in a sandbox environment first before pushing it to production. If you’re on PlanetScale and are facing slow SQL queries that could benefit from caching, try PlanetScale Boost to increase the performance and throughput of your queries by up to 1000 times
Database latency is a very important factor in measuring the performance of a system. Several hardware-, software-, and network-related factors can affect database latency. By using a platform like PlanetScale, you can rest assured that the best infrastructure and engineering paradigms are used to ensure top-notch performance for your application. Does your database have built-in query performance metrics that help you track down database latency? Give your engineers the power they deserve with a PlanetScale database today. Get started for free.