Monitoring and Optimizing the Database Performance for Drupal Hosting

You’ve read countless website optimization guides and followed all the steps to make your Drupal project faster. You’ve converted your images to the latest and best formats and minified all CSS, JavaScript, and HTML files. You’ve enabled caching, picked a server with more than enough hardware resources, and put your site behind a content delivery network (CDN).

Yet, your website still won’t perform as well as expected. As frustrating as it may be, your priority is to find the bottleneck, and the first place to look is the database.

Although it sounds daunting, optimizing your database performance is actually not that tricky. However, you must first learn how it works and what you can use it for.

So, without further ado, let’s get stuck in.

Database Performance: Why Is It So Important?

Content Management Systems (CMS) like Drupal wouldn’t exist if it weren’t for databases. In fact, the entire internet would be a very different place without them. Pretty much every mouse click on a modern website either generates a new database record or requires the retrieval of an existing one.

For example, when setting up a new online account, your personal details, including your login credentials, are sent to the website database. Later, when you try to log in, the browser retrieves your username and password from the database and compares them to what you just entered on the login page.

As a website owner, you have an administrator account, meaning the articles you and other contributors publish also go to the database. So do the comments under them, the Drupal core settings, and the vast volumes of information your modules generate.

That data is in use all the time. When a visitor loads your website, Drupal needs to know which of the installed themes is the correct one so it can load the page with your design. It must also know which modules are installed and enabled and how they’re configured to work. So, many of the elements you see on a typical web page wouldn’t be there if it wasn’t for the database.

But how does it all work?

The interaction is between Drupal and the database, and it’s based on the so-called queries.

Let’s say a user wants to read one of the articles you’ve published on your site. Drupal acknowledges the request and sends a query to the database management system (DBMS), sometimes called the database engine. The DBMS is a software system that manages and organizes data in a database. It locates the requested content, and Drupal injects it into the HTML code the web server sends back to the user.

We’ll assume the visitor wants to leave a comment. After they click Submit, Drupal packages the text they’ve entered into another query, instructing the DBMS to store the command in the database. Every time someone loads the article, yet another query will retrieve the comment and make sure it’s visible.

This is one of the simplest examples of how information in the database is stored and used. Every page load could generate dozens or even hundreds of queries.

Now imagine a busy site generating thousands of clicks every second. You can see how much pressure the DBMS and the database are under.

So, how can you make them work as quickly as possible?

Let’s take a closer look at some of the things you may want to consider. 

Choosing the Right Database Management System

Before you start optimizing your Drupal website, some “Database Management 101” may be in order. 

Let’s start with the types of databases.

Types of databases

Over the years, software engineers have faced countless data management problems, which have led to the development of dozens of technological solutions.

Each database engine has its own approach, but depending on the way they store information, we can divide them into two distinct categories:

• Relational databases

Relational databases are also called SQL databases. SQL stands for Structured Query Language, and it’s the language used in the queries sent to these specific databases.

These databases store data in tables, with multiple tables often containing data about a single entity. To illustrate, let’s see how Drupal stores information related to its user accounts by default.

One table contains the user IDs, the usernames, a hashed and salted representation of the passwords, and the account creation dates. A second table has the user roles and access levels. In a third one, you’ll find the individual user IDs connected to their respective roles. Depending on the configuration, you may have two additional tables – one for personal details and one for session data and authentication tokens.

Storing all these details in multiple tables instead of just putting them in a single one sounds a bit counterintuitive. However, in an SQL database, the relations between the tables and the individual fields are clearly defined in something called a schema.

The schema allows your DBMS to locate the requested data more efficiently.

• Non-relational databases

Non-relational databases are often called NoSQL databases, short for “Not only SQL.” SQL can sometimes be used, but non-relational databases also support other query languages.

The crucial difference is that, unlike their relational counterparts, NoSQL databases don’t store information in traditional tables. Instead, there are four separate storage models.

• Key-value storage

In a key-value model, you have a key associated with a value. You can think of it as a word and its definition in a dictionary. This is the most straightforward storage model in NoSQL databases.

• Document-oriented storage

Once again, you have a key, but this time, it’s not connected to a single value but to a more complex data structure called a document.

• Graph storage

This complex model relies on nodes, edges, and various data properties. Information is recorded and retrieved with the help of complicated semantic queries.

• Wide-column storage

In this case, data is stored in rows, columns, and tables. However, you can have different names and data formats within the same table, which is impossible with regular SQL databases.

Databases supported by Drupal

By default, Drupal works with relational databases. Out of the box, it supports the following DBMSs:

• MySQL

Owned by Oracle, MySQL is one of the most widely used DBMSs out there. It’s a part of the so-called LAMP stack, so it’s available on most web hosting packages. Drupal works well with MySQL out of the box, and if you have an automatic installer included in your control panel, it will set up the database and the MySQL user associated with it for you. The integration is seamless.

• MariaDB

When Oracle bought the project in 2009, MySQL’s original developers created a fork named MariaDB. They’re adamant that MariaDB will remain free and open-source, and right now, over a decade after it was first rolled out, many people prefer it because it offers more features than MySQL.

• Percona

Percona is a software company specializing in free, open-source database solutions. Over the years, it has created a range of DBMSs designed to work with both relational and non-relational databases. Percona’s MySQL system will work easily with Drupal.

• PostgreSQL

PostgreSQL (or Postgres) is a relational database management system that provides improved flexibility thanks to better scalability, concurrency, and data integrity. Drupal does support PostgreSQL databases, but to use one, you need to ensure the pg_trgm extension is enabled.

Another thing to remember is that many of the modules you use may contain MySQL-specific code. If so, they won’t work with a Postgres database.

• SQLite

SQLite is yet another relational database management system. It’s a powerful engine used by many large organizations. Like Postgres, support is available out of the box, but you have to ensure your modules are compatible.

If the database technology you want to use isn’t on the list above, you can look for Drupal modules that can enable support for it. Even some non-relational databases, like MongoDB, can be utilized with the help of add-ons.

How to pick the right Database Management System

Most web hosting accounts are equipped with either MySQL or MariaDB. Some control panels also let you enable PostgreSQL with a couple of clicks. There’s a good reason for this – most standard websites, whether built with Drupal or not, use SQL databases and can work well with the aforementioned engines.

That said, Drupal is one of the world’s most versatile website-building applications. It can support a broad range of websites and help you turn your ideas into reality. However, if that is to happen, you need to ensure the hosting environment allows it to work with the technologies required by your specific project.

You need to have a clear idea of the type of data your site is going to handle. If this is a new venture for you, be sure to research and see which database engine will be the most suitable choice.

It’s essential to explore the differences in how data is handled and understand the advantages and disadvantages of different DBMSs. Last but not least, don’t forget to consult your hosting provider’s technical specialists to see if they can give you the environment you’re after.

Whatever your requirements, if you want to take advantage of a managed hosting service, you have to make sure the database engine you need is available by default. On the other hand, if you work with a self-managed solution, you have root access, so you can install the most suitable engine yourself. Bear in mind, however, that this will require certain technical skills.

Query Optimization

Database performance depends entirely on the speed with which your queries are executed. So, how do you ensure they’re quick and efficient?

The correct database engine is an obvious prerequisite, as is the suitable hosting environment. However, even the best DBMS on the most powerful server will struggle if the queries are too cumbersome and the data is not organized well enough.

Fortunately, several techniques can help prevent this, especially if you use an SQL database. Since most Drupal websites employ relational DBMSs, we’ll focus on these today.

• Indexing

Organizing data is not exactly straightforward, especially on a larger website that handles a lot of information. This means that finding what you’re looking for isn’t always easy.

Let’s explore an example. Your Drupal website has a total of 2,000 user accounts and the same number of lines in the Users table in the database. The entries are added as users sign up, i.e., the people who signed up the last are at the bottom of the table.

You want to find John Doe, who created his account yesterday and is under line 1,989. The DBMS has to go through a total of 1,988 lines before it finds the correct one. It would be much easier if you could arrange the entries alphabetically, but unfortunately, this is not something you can do on the main table. The good news is you can create a separate data structure and fill it with pointers that guide the DBMS to the requested data. This structure is called an index.

Think of it as a collection of shortcuts pointing to the entries in the main table. These shortcuts can be arranged alphabetically or according to other criteria so the DBMS can find the requested data more quickly and efficiently.

Indexing is one of the most popular database optimization techniques. This is partly because it’s easy to implement through tools like phpMyAdmin but mostly because it’s highly efficient in speeding up a regular SQL database.

• Utilize JOIN operations

Use them correctly, and the so-called Joins can optimize complex queries immensely. A JOIN clause combines rows from multiple tables based on a related column between them.

For example, if you’ve turned your Drupal project into an ecommerce website, you most likely have one table for the orders and a separate one for the user accounts. The Orders table contains the submitted purchases, each associated with a customer ID. The customer IDs are in the table of User Accounts, along with the users’ names, addresses, and other personal information. Using a JOIN clause, you can link an order to a specific name or address with a single query.

The idea is to minimize the number of simultaneous queries hitting your database.

• Use suitable data types

If a column contains the dates of particular events, configure the database to use the DATE data format for the entries under it. The benefit is two-fold.

On the one hand, you can prevent data entry errors, as records using a different data format can’t be entered into the wrong column. On the other, the number of characters is limited, leading to faster query execution.

• Minimize the use of SELECT queries

SELECT queries view all fields in a dataset rather than just the relevant ones, which makes them inefficient. When you’re writing your queries, be sure to include only the columns you’re interested in. On the one hand, this will speed up execution and improve performance; on the other, it will make the output cleaner and easier to read.

• Use the LIMIT clause when needed

When you have a large table with thousands of rows and records, a query can often return a large number of results. Usually, you don’t need such a large volume of data, meaning the DBMS wastes precious time retrieving information you won’t use. In such cases, the LIMIT clause can help you optimize the data collection process and speed up the execution.

• Partition large tables

The larger the table, the more time your DBMS needs to find the relevant information. That’s why if you have millions of records stored in the same table, it may not be a bad idea to partition it into smaller subsets. This way, the database engine can find the relevant data more quickly.

Database Maintenance

After your website is up and running, you’ll need to stay on top of a few maintenance tasks if you want to ensure it performs at its best at all times. Quite a few of them have to do with the database. Let’s take a closer look.

• Backing up and restoring data

Backups are the best way to minimize data loss if the entire site goes down. However, this isn’t the only scenario where they might be useful.

Imagine, for example, that you’re making some changes to your database, which don’t work as expected and slow your site down. Your best course of action is to restore the database from a recent backup and ensure the loading speeds are up to par while trying to figure out what went wrong.

Your hosting account likely has a facility that can automatically back up your files and databases, and, being an open-source content management system, Drupal also has some modules you can implement into your backup strategy. Whatever your approach, be sure you regularly test your restore procedures. That way, you can guarantee that when you need to use your backups, they will work as planned.

• Index maintenance

Indexes are indispensable when it comes to improving database performance. However, over time, they inevitably become fragmented. At some point, the order of the pointers will stray too far from the logical order of the index keys, leading to increased disk usage and suboptimal performance. Regularly defragmenting and rebuilding your indexes will help you avoid this.

• Statistics maintenance

There’s another data structure within your database that contains statistics vital for efficiently executing your queries. Through it, your DBMS gets information about data distribution in tables and indexes, and thanks to it, it can retrieve the requested information much more quickly. However, over time, the stats become outdated and insufficient to guarantee fast query execution. You can use various commands and tools to regularly update database statistics and ensure you’ll get the performance you’re after.

• Data integrity checks

When you have a large number of records, errors and inconsistencies are pretty much inevitable. The least they can lead to is increased storage space usage. In a much worse-case scenario, they can result in poor performance and data loss. Thankfully, SQL databases come with quite a few tools that allow you to perform regular data integrity checks, fix corrupted or invalid data, remove broken records, etc.

• Performance tuning

Your Drupal website database is a complex system, and there are many aspects of its operation you can look into in an attempt to improve its speed. For example, something as simple as updating the DBMS to its latest version could help. You can use performance analysis tools to learn more about how the database works. Based on that information, for example, you can adjust the memory allocated to it.

• The DB Maintenance module

On Drupal’s official module directory, you’ll find an add-on called DB Maintenance. It’s completely free to use, and its installation procedure is standard. Despite its name, it’s not an all-in-one solution that streamlines your database with a couple of clicks.

Essentially, the module uses a cron job to run the OPTIMIZE TABLE command at predefined intervals. From Drupal’s backend, you can decide which tables to include in the command and how often you want the module to run. The exact effects depend on the storage engine, but in essence, what the module does is defragment the tables you’ve selected.

How effective it is depends on the size and state of your database. Bear in mind that sometimes, the speed improvement is barely visible. Still, regularly defragmenting data can do no harm, and the module is known to work, so installing it is definitely a good call.

Databases and Caching

Caching is traditionally associated with static data, such as multimedia files, CSS stylesheets, JavaScript scripts, etc. However, it is also possible to cache data generated by your database.

In fact, before producing the required output, relational databases load it into the buffer pool—a facility located in the server’s RAM that stores data for faster delivery. That’s not the only way to cache database responses, though.

Dedicated caching solutions can also store database-generated data in the server’s RAM. They are equipped with better storage and retrieval mechanisms and are more easily scalable, so you can be sure they’ll work well even as your website grows.

The two most popular caching solutions are Memcached and Redis.

Memcached stands out with its distributed architecture and ability to serve data from multiple nodes. In addition to faster data delivery, this leads to lower server load, further improving performance.

Redis supports more data types than Memcached, and the data it writes is persistent, meaning it can be used as a standalone database. However, when you use a relational DBMS, it’s an effective caching system that significantly speeds up your site.

Drupal doesn’t work with Memcached and Redis out of the box, but there are modules for both, and tutorials showing you how to set everything up are never more than a Google search away. You just need to ensure your hosting provider supports the technology you’ve chosen. Speaking of which, let’s see what benefits you can enjoy if you use ScalaHosting for launching and running your Drupal website.

ScalaHosting and Drupal Performance

We want to make sure your website performs to the best of its ability when it’s hosted with us, which is why we’ve created a range of Drupal hosting plans configured specifically for the CMS.

Both our affordable shared solutions and our powerful VPS packages come with server-level customizations that create a perfect environment for your Drupal project. The technologies required to build your project are fully supported, and our managed virtual private servers give you the freedom to easily implement a variety of speed and database optimization techniques.

IMPORTANT: Keep in mind that while Drupal can run on a shared environment, optimal performance and security can be achieved on a VPS service plan. Resource consumption and limitations on the shared packages will quickly catch up to any growing Drupal project.

They come with SPanel – a proprietary web hosting control panel we’ve created to make server management a breeze, which supports both MariaDB and PostgreSQL

Switching between the two DBMSs is as easy as clicking a couple of buttons in the Admin Interface, and you can use phpMyAdmin or phpPgAdmin to avoid spending too much time trying to find the right command.

Our technical support specialists can guide you in the right direction if you ever get stuck, and if you’re a new customer with an existing website, they can transfer it for you for free.

Conclusion

Database performance optimization doesn’t sound like something anyone can tackle. In reality, there’s nothing too complicated about it. You certainly don’t need to be a technical wizard to speed up your database.

The fact that you’re using Drupal further simplifies the process thanks to a few modules and some built-in features.

You will need to put in some time and effort, but when you see the performance benefits, you’ll realize that the “investment” is well worth it.

FAQ

Q: How can I improve the database performance of my Drupal website?

A: You can start by better understanding how your database works and how it contributes to the overall loading speeds of your Drupal site. Make sure you explore opportunities for implementing various performance-optimization techniques that work on all websites. Last, but not least, look at the modules that provide Drupal-specific solutions.

Q: How can I improve the performance of a Drupal website?

A: Website optimization is a never-ending process that requires a deep understanding of how individual components work and a clear idea of how to improve their performance. Some techniques you can implement involve caching, image optimization, and static and dynamic data delivery.

Q: How do I make my Drupal site more responsive?

A: If you want your site to be responsive, you can focus on two aspects. First, carefully consider the layout, menus, widgets, and all other design elements people interact with and ensure they offer the best possible user experience on all screens. Then, implement as many performance optimization techniques as possible to boost the overall speed of the Drupal website.