In this tutorial, we will cover improving database performance on a Linux VPS. Under the database, performance refers to how efficiently a database system can handle queries, data retrieval, and transactions. These are all essential components of a website running on a Linux server. You can enhance database performance by optimizing queries, applying indexing, using caching, and tuning the database. In addition to adjusting web server configuration, utilizing a CDN, upgrading server resources, and updating server packages. Database performance can sometimes impact the website’s content and the web application’s code.
In the following paragraphs, we will provide a brief overview of database systems. We will explore ways to optimize them and clarify why database performance matters.
Table of Contents
What is a Database system?
A database system actively organizes related data. A database management system (DBMS) manages this data for specific applications. It is a structure of organized data with relations between elements. This includes text, numbers, images, and many other formats. Database management systems are actively classified into three distinct categories. A relational database management system (RDBMS), a document database management system (DoDBMS), and a columnar database management system. In the following paragraph, we will explain the importance of optimizing a database system.
What is Database Optimization, and why is it important?
Database performance optimization involves a systematic improvement in how efficiently a database manages server resources. This optimizes query processing and efficiently manages concurrent connections or users. Such a process utilizes various techniques to minimize response times, resource usage, and enhance overall system performance. Notably, this is particularly important in high-traffic environments. Better database performance can improve the system’s efficiency, reduce costs, enhance user experience, and provide a competitive advantage.
Database performance optimization is crucial for ensuring the system’s efficiency and responsiveness. Without it, slow queries and bottlenecks can lead to frustration among users. This, in turn, increases website abandonment rates and decreases search engine rankings.
How to Optimize Database Performance on a Linux VPS?
There are several steps for improving database performance on a Linux VPS. Many server applications’ default settings fail to optimize for peak performance. There are key points for optimization. Updating software on the VPS and optimizing database performance by removing unused indexes are some of the most common tasks. Utilizing database cache solutions, such as Memcached or Varnish, implementing a CDN, and optimizing web application code are also beneficial.
The steps for optimizing database performance are theoretical in nature. Practical and real-time examples, accompanied by numbers, actively demonstrate their derivation. Let’s dive in!
1. Update the Software on the VPS
Updating the software and system packages on the VPS is the first step we can take to optimize database performance. This operation is crucial because outdated software can miss recent performance improvements and security patches. Delaying software updates may also expose vulnerabilities on the server, which could lead to data loss and make the website inaccessible.
MySQL Update: Updating the MySQL database service will significantly increase performance by up to 40 percent. This includes features such as better indexing, query optimization, improved concurrency, and many more. At the time we wrote the blog post, the latest MySQL version was MySQL 8.4, which provides a stable and reliable version with long-term support.
PHP Update: Updating the PHP version can significantly impact MySQL performance in several ways, including faster script execution, reduced CPU usage, and more efficient RAM utilization. It will also enhance the MySQL libraries, such as MySQLi and PDO, resulting in improved connection pooling and support for new MySQL features.
Web Server Update: Updating the web server (Apache, Nginx, or LiteSpeed) can indirectly affect MySQL performance, particularly in how the web application interacts with it. It can handle more concurrent requests efficiently, reduce timeouts, minimize query queuing, and lower wait times on MySQL, among other benefits.
IMPORTANT: Before upgrading MySQL to a higher version, it is essential to make a backup of the MySQL files, or even better, of the entire VPS. Once upgraded, it is impossible to downgrade the MySQL version. If the web application is not working with a higher version, we cannot roll back to the previous version, and we will lose the files. That is why it is essential to take a backup before taking any action.
2. Database Indexing
Database Indexing is a technique for enhancing database performance by optimizing the speed of data retrieval operations within the database system. The database indexing creates an index, a separate data structure that stores a sorted list of key values along with pointers to the corresponding data rows. With database indexes, we can set the database to quickly locate data without having to scan the entire table. This will significantly reduce the query execution time. It works simply, and we can compare it with a table of contents in a book. If there is no table of contents, you need to read the entire book to find some specific information about a chapter. The table of contents (index) provides a faster way to find information in the database, rather than wasting time searching through the entire database.
There are four different types of Indexes: B-Tree, Bitmap, Hash, and Gist Indexes.
B-Tree Indexes are the most common type of index, used for efficient searching, range, and sorting queries.
Bitmap Indexes are used for columns with low distinct values.
Hash Indexes are used for equality lookups.
Gist Indexes are used for handling complex data types and spatial queries.
When indexing, the system uses more storage on your VPS, so carefully select what to index. During indexing, the database slows write queries as it actively updates the index in conjunction with the data. Schedule indexing operations during minimal or no website traffic to ensure data consistency.
There are numerous benefits to using database indexing, including faster query performance, reduced disk operations, and improved system responsiveness.
3. Using the Right Storage Engine
The storage engine is a component responsible for handling the data storage, its retrieval, and database management. This important for improving database performance on a Linux VPS. The storage engine, a software component, actively manages physical storage and dictates how the database stores and retrieves data. They actively split into transactional and non-transactional engines. We will give a detailed explanation about these two types of storage engines.
The Transactional Storage Engines are the following:
InnoDB is the default storage engine for MySQL. The most modern applications, such as e-commerce sites, blog sites, and applications with frequent updates like WordPress, Magento, Drupal, etc, use the InnoDB storage engine. The InnoDB storage engine supports crash recovery, row-level locking, foreign keys, and transactions.
XtraDB is an enhanced version of InnoDB used in MariaDB with similar transactional features and performance optimizations.
NDB is a clustered storage engine designed for high-availability applications that require robust data replication and failover.
The Non-Transactional Storage Engines are the following:
MyISAM is a non-transactional storage engine used for read-intensive operations, such as full-text searching, but with limitations on data integrity and concurrency. MyISAM was the default storage engine for MySQL versions before 5.5
A memory non-transactional storage engine is used in exceptional cases, such as temporary data or session storage on a VPS. The data is stored in RAM, offering high-speed read and write operations for temporary or frequently accessed data. It will all be deleted from RAM upon server restart.
4. Implement Query Caching
The MySQL query cache is a feature that stores the results of SELECT queries in memory. This significantly helps, as the system serves identical queries directly from the cache, thereby avoiding repeated query execution. Actively implementing query caching sharply boosts database performance, particularly when executing identical queries hundreds of times in a short span.
When executing a MySQL query, MySQL actively checks if the query exists in the cache. If MySQL finds a match, it returns the results to the client without accessing the database, known as a cache hit. When MySQL finds no match, it labels it a cache miss, but still executes the query against the database and stores it in the cache for future use. When UPDATE, INSERT, or DELETE actions modify a table, MySQL actively invalidates the query cache to ensure cached results reflect the latest data.
These are the recommended caching tools for your MySQL database:
Memcached is a high-performance in-memory caching system used to speed up websites with heavy database usage.
Varnish cache also improves the performance of dynamic and busy websites.
5. Optimize MySQL and tune the MySQL configuration
There are a few settings that can be adjusted in the MySQL configuration file (/etc/my.cnf) to optimize the performance of your database service. We will explain them in more detail in this section.
query_cache_size is a MySQL configuration value that specifies the amount of memory allocated to the query cache. The query cache stores the results of the queries that are executed too often, and its values are 128M-256M for MySQL 5.7 or MariaDB. The latest MySQL 8.0 release removed the query cache entirely because it was found to be inefficient in high-concurrency environments with frequent data modifications.
max_connections is a configuration parameter that specifies the maximum number of concurrent connections a database server will allow at any given time. The max_connections value can be estimated with the following formula:
max_connections = (Available RAM - Global Buffers) / Thread Buffers. # Example: # Available RAM is the RAM on your serve # Global Buffers represent memory allocated for global buffers like the key buffer, query cache, etc. # Thread Buffers represent memory allocated for each connection, such as sort_buffer_size, read_buffer_size, etc.
Innodb_buffer_pool_size is a configuration parameter that determines the amount of memory allocated to the InnoDB storage engine’s buffer pool. It is generally set to 70-80% of the available RAM. For a server with 32GB of RAM, the innodb_buffer_pool_size will be set to approximately 26GB.
innodb_redo_log_capacity defines the total size of the InnoDB redo log. The redo log is a crucial component of the InnoDB storage engine, acting as a transaction log that records all changes made to the database.
thread_cache_size is the number of threads that the server maintains in a cache for reuse, rather than creating new ones for each connection. This parameter can significantly improve the database server’s performance by reducing the time required for thread creation and destruction.
Innodb_buffer_pool_instances is a MySQL configuration parameter that determines how many instances the InnoDB buffer pool is divided into. This parameter is relevant when the innodb_buffer_pool_size is larger than 1 GB. This means that we need a server with at least 2 GB of RAM to implement the innodb_buffer_pool_instances parameter.
Innodb_log_buffer_size determines the size of the memory buffer used by the InnoDB storage engine to hold redo log data before it’s written to the physical log files on disk. Innodb_log_buffer_size is a temporary storage for transactional information that will eventually be written to the redo logs.
6. Upgrade the VPS
It is essential to note that upgrading the VPS and server resources does not necessarily guarantee continuous improvement in database performance. Upgrading the VPS is necessary only if there are not enough resources after the server has been optimized. If the RAM and CPU usage exceed 80% of the server limits, then a server update is needed. If you choose hosting, it is essential to select a hosting company that utilizes the latest CPU generations, NVMe disks, and the most recent DDRX RAM generation.
That’s it. You learned some basic steps on improving database performance on a Linux VPS. Of course, you do not have to do this on your own. If you experience difficulties with MySQL and Linux, simply sign up for one of our NVMe VPS plans and submit a support ticket. Our admins will help you with any aspect of optimizing the database performance. Feel free to contact us. We are available 24/7.
If you liked this post on improving database performance on a Linux VPS, please share it with your friends or leave a comment below.