Improve database performance
Databases are sophisticated applications, and database access is a core feature of many applications. To avoid failures or poor performance, it's important that your databases be hosted securely and resourced well enough to perform at their best.
You can optimize your databases with:
- Server data that supports host health monitoring
- Hypervisor and virtual machine metrics that support monitoring of your virtualization layer
- Application data that optimizes database access
- Network data that provides insight into the network impact of database communications
With the following few steps for simple database performance tuning, you can significantly speed up most applications.
Check the health of your database
Understand how your database is accessed
Understand the load and individual response time of each service instance
Check the number of database connections
Check your network
Check the health of your database
The first step is to ensure that the host serving your database process has sufficient resources such as CPU, memory, and disk space.
CPU
-
Equip each host with a minimum of two CPU cores. Matching the CPU count of your host helps with:
- Ensuring host responsiveness, because database servers induce a continuous base load on machines.
- Preventing overspending or hardware limitations, because database-server licensing is affected by the number of CPUs.
-
When monitoring virtual machines, monitor the host that the virtual machines run on as well. This provides a more complete picture than the CPU metrics of individual virtual machines, which generate insights only on the respective CPU time availability.
Memory
-
In addition to monitoring the Memory usage metric, monitor Page faults per second to learn how much additional memory is required. Having thousands of page faults per second indicates that your host is out of memory.
Disk space
-
Ensure that storage availability for your database server is higher than the disk space required for the data.
Because of indices and other performance improvements, databases might use more disk space than required by the data itself. For example, NoSQL databases (such as Cassandra and MongoDB) consume a lot more disk space than expected. Compared to common SQL databases, MongoDB databases might consume less RAM but more disk space.
-
Ensure that your database runs on dedicated hard drives to reduce disk fragmentation caused by other processes.
-
Check Disk latency.
Depending on the hard drive load, disk latency can increase, leading to a reduction in database performance. You can prevent high disk latency by leveraging the caching mechanisms of your application and database as much as possible.
-
If the results of the measures above aren't satisfactory, consider the following.
-
Add additional hard drives.
Read performance can be multiplied by simply mirroring hard drives. Write performance benefits from using RAID 1 or RAID 10 instead of RAID 6.
-
Try solid-state drives.
Ensure that you select a model designed for database usage, because databases apply more read/write cycles to storage than most common applications. Solid-state drives are more expensive than traditional hard disks but offer a substantial boost in performance.
-
Understand how your database is accessed
Once your database resides on healthy hardware, take a look at the applications that access it. If you know of an application or service that has bad database performance, don’t assume that it's the application that's affecting the performance of your database—it may be another application or service entirely.
Reduction in database performance can affect the entire database or a single client.
- If all clients experience bad performance, check if the host is healthy. In most cases, the cause is hardware that isn't capable of handling the work.
- If only a single service suffers from poor response times, dig deeper into the service’s metrics to find the root cause of the problem.
Understand the load and individual response time of each service instance
When a service has poor database performance, you can analyze its communication with the database via database statements. You can gain insights into the number of executed queries, the query execution frequency per request, the number of rows each query returns, and so on.
- If you're running multiple service instances, check if all the instances are affected rather than a single service instance.
- Check how often the queries are called per request. You might be able to reduce the number of database queries by improving the database cache of your service. If a single query is executed more than once per request, you can unlock potential performance by applying smart caching strategies.
Check the number of database connections
You might continue to face poor database performance even when database queries are correctly configured. In such cases, check that the application’s database connection pool is correctly sized.
When configuring a connection pool, consider the following:
- The maximum number of connections the database can handle
- The correct size connection pool required for the application
Because your application may not be the only client connected to the database, ensure that the connection pool size isn't set to the maximum. If the application takes up all the connections, the database server won’t perform as expected.
The maximum number of connections to the database is a function of the resources in the database. To find the maximum number of connections, gradually increase the load and the number of allowed connections to your database.
While doing this, keep an eye on your database server’s metrics: CPU, memory, and disk performance. Once any of these maxes out, you’ve reached the limit. If the number of available connections isn't enough for your application, consider upgrading your hardware.
To learn more about the database connection pool size, see About Pool Sizing.
The number of allowed concurrent connections to your database is equivalent to the amount of parallel load that your application applies to the database server. There are certain tools that you can use to determine the right number.
Increasing the load leads to higher transaction response times, even if your database server is healthy. Measure the transaction response time from end-to-end to see if Connection acquisition time increases under heavy load. If that's the case, your connection pool may be exhausted. If not, review your database server metrics to determine the maximum number of connections that your database can handle.
A connection pool’s size should be constant. Therefore, set the minimum and maximum pool sizes to the same value.
Check your network
Physical constraints of your virtualized infrastructure can affect database performance; cables can fail and routers can break. Network metrics generate insights into non-virtual problems. For example, if problems appear after months or even years of flawless operation, your infrastructure might be suffering physical problems. Check your routers, cables, and network interfaces.
Most often, over-stressed processes start dropping packets when resources are depleted. If your network issue isn't hardware based, process-level visibility can help you identify any failing component.