Check your database server
Ensuring that the host of your database processes has sufficient resources is an essential step to solving performance issues. Start with a check-up of your CPU, memory, and disk space to help identify potential problems.
If your database routinely underperforms, it may be necessary to upgrade to a higher class CPU unit. Evaluate the various aspects of CPU performance, such as CPU ready times, which can alert you to instances that your system was unable to use the CPU due to insufficient resources. Due to the continuous base load database servers induce, a minimum of two CPU cores may be necessary to keep the server responsive. Upgrading to a more powerful CPU can reduce the strain introduced by multiple applications and requests, improving database speed and efficiency.
A lack of available memory is another potential culprit of database performance problems. An effective evaluation of your memory needs involves assessing two different metrics: memory usage and page faults per second. A page faults number in the thousands signals that your hosts are running out of available memory space, and an increase is necessary. Having more available memory can improve the efficiency and performance of the system.
Increasing the amount of memory used by MySQL to allocate 70 percent of the total memory is another option, as long as the database is the only application on that server.
Having a lot of storage available for your database server is crucial due to the fact that indexes and other performance improvements cause databases to consume more disk space than is actually necessary. Running your database on its own hard drives can minimize the disk fragmentation that occurs as a result of other processes. Also, dedicating a set of drives for data files, log files, backup files, and tempdb not only improves performance, but also serves as a convenient back-up in the event of a recovery disaster.
A prevalent issue that results in decreased database performance is an increase in disk latency. Monitor metrics related to disk latency closely. The quickest and most cost effective way to mitigate latency issues involves utilizing the caching mechanisms available to you.
The type of disks in your server is another important factor in improving performance. The amount of data the query needs to access or return can require millions of i/o operations for simply retrieving the results of even a single query. Upgrading to solid-state disks can impact the performance of your SQL queries, which can result in better performance from your database. Choose an SSD model designed for database usage for optimal results.