This post details efforts to boost database performance, from hardware upgrades to code optimizations. While we show mixed results, all in all, positive enhancements ensuring better performance.

This month we finished three of those projects that nobody can see and that consume a lot of time and effort. All three are oriented towards making the database access faster.

Any optimization we do concerning database access is important because, at this point, almost all our profiling efforts end with the same bottleneck: the database. All the operations that Evolutivo.FW does end up touching the database in one way or another. It is the center of our application and the core of our business. That means that any optimization we make there will make the whole application go faster.

Thus, the first, most obvious, limitation is going to be the hard disk, where the information is ultimately saved or read from. The faster those disks are the faster this is going to move. I recently bought a 1Tb NVME hard disk. The difference is incredible. Something to keep in mind.

That said, the effort we put into making our code go to the database less or making it easier for the database to organize and retrieve information faster is also going to pay off positively.

With that in mind, we started three projects which I present to you today.

This project stems from recommendations given by Google Cloud Platform. When installing our application there, GCP informs us that there is a set of tables that do not have primary keys. Most of those tables are sequence tables, but there are some others. So we decide to study each table and add primary keys and autoincrement columns to them.

Sadly, once we were done with this one, we found that it didn't have a big impact on performance.

We had over 120 tables without a primary key defined and probably another 100 or so sequence tables. Sequence tables are database tables that contain only one counter field. They hold the ID of the next row to insert into a table. This can be done automatically by the database management system so we save time if we delegate that task to the database instead of manually doing it ourselves. With that, we make the sequence table unnecessary and they can be deleted.

That sounds like a good idea. We avoid having to do an extra database query to get an ID, increment it, and then use it in the insert of the new record. With the autoincrement we directly insert and get the last autoincrement ID returned. A lot faster. We also reduce the size of the database by eliminating a bunch of useless sequence tables.

What happened in reality was that except for two tables, all the other tables we fixed were small settings tables. For example, the payment module and the mobile module have a database table where some default settings can be configured. This table is just one row, so it didn't have a primary key. We added it, but there is no gain at all.

Other tables are the same. Currencies, workflow tasks, or hour format. All tables that have small usage and a small set of records.

So the project turned out to be a waste of time. We should have approached it in another way.

There are two tables where we did end up winning something: Audit Trail and Mod Tracker. In those two we optimized the insertion time, so IF you have those active you will get a small benefit that you most probably won't notice, but, every little bit helps.

I was expecting to optimize on the main CRMEntity table but for that one, we need to keep using the separate sequence table due to the denormalization project. Since we can have modules that do not use the CRMEntity table but still need to get a CRMID that increments consecutively inside the application we had to keep it. Similarly, for the picklist tables we had to keep a shared sequence table also.

About the space we save. We have deleted around 100 tables, give or take, but all those tables contained one row with one integer. So, irrelevant already, but you have to consider that we added primary indexes and some new columns in other tables, so we probably ended up the same or worse.

My last comment is that we had to review a lot of the application and touch a lot of the code. When it was clear that we weren't going to get much out of this we lost enthusiasm and let the project drag. We also, did NOT update the migration scripts. So be warned, the next migration we do from an install that is very behind will need to dedicate time to updating those migration scripts.

Live and learn.

This next project was significantly more positive.

Our data abstraction layer gives us a set of functionality to make working with the database easier. One of these functions is query_result. We can give that method a database result set of rows and ask for any column in any row. Many times we launch a query and retrieve columns from the first row found so we use query_result to get the data values, but our code has a nice functionality whereas when we launch a query it will automatically retrieve the first row from the result along with the metadata of the call. In the case of just needing the first row, it is a waste of time to use query_result to get the first row, when you already have it. Note that query_result has to move the pointer to the indicated row, ask the database engine for that row, and, on top of that, it does a conversion to HTML entities which we usually do not need or end up undoing now that we live in the UTF-8 world.

Let's see what that looks like.

function getQuoteName($quote_id) {
    global $adb;
    $result = $adb->pquery('select subject from vtiger_quotes where quoteid=?', array($quote_id));
    $quote_name = $adb->query_result($result, 0, 'subject');
    return $quote_name;
}

That query_result will move the pointer of the database cursor to row 0, retrieve the column subject, and apply HTML encoding to it. Now this function looks like this:

function getQuoteName($quote_id) {
    global $adb;
    $result = $adb->pquery('select subject from vtiger_quotes where quoteid=?', array($quote_id));
    $quote_name = $result->fields['subject'] ?? '';
    return $quote_name;
}

We launch the query and directly access the value if it is available.

We reviewed 4 files in our code base, substituted query_result for direct access, and reduced the execution time of our unit tests by 2 seconds!

There are currently over 3000 lines of code where we use query_result. Not all of them can be switched, but many of them can be, so there is still a lot of work to do in this project. What we have done up to now is proof that it is worth investing in this and enforcing this programming pattern instead of using query_result.

We dedicated time to benchmarking MySQL in general and specifically for Evolutivo.FW. Creating a procedure with steps and recommendations on how to optimally configure the database management system.

In line with the coding efforts to optimize database access, it makes sense to study, tune, and tweak the database engine itself. If we manage to make MySQL work faster by adapting it to the resources in the server, all the installs in the server will benefit. This makes a lot of sense because most of our servers are exclusively dedicated to hosting Evolutivo installs. If we build a server with a lot of RAM we can configure MySQL to use that RAM for storing metadata or query cache results, for example.

This study brought our attention to some missing indexes in our tables, an incorrect configuration in one table, and the recommendation of modifying the settings:

• skip-name-resolve: no need to keep names, the IPs are good enough
• key_buffer_size=0 we don't need this anymore as we don't have any MyISAM tables
• table_definition_cache: table metadata cache holds information about the structure of the tables, the bigger the cache the less we will have to go to the database to get the metadata. By default, this has a value of 2000 tables. A normal Evolutivo install has about 500 tables, so if you have more than 4 Evolutivo installs or a mix with other MySQL-based applications then this one should be adapted to your server. As a side note, I have had to increment this variable to avoid an error while inserting records in the database. It is a rare edge case, but it happens. You should dedicate some time to studying the right value for your server. Definitely, 2000 is way too low for our application.
• innodb_buffer_pool_size: this one is important as we use it a lot
• innodb_redo_log_capacity: important when your server crashes
• innodb_log_buffer_size

With some basic tweaking in one of our test servers, we achieved these results:

• Transactions per second increased significantly from 48.93 to 157.77.
• Queries per second also saw a substantial increase from 1392.29 to 4517.80.
• Latency improved notably with the average latency decreasing from 80.46 ms to 25.24 ms.
• The maximum latency also reduced significantly from 919.94 ms to 254.59 ms.
• The 95th percentile latency decreased from 303.33 ms to 90.78 ms, indicating better overall performance. This decrease in minimum latency indicates that even the fastest queries experienced a slight improvement in response time after the configuration adjustments.

We still have some work to finish this project but it is mostly wrapping up and documenting the guidelines and procedures to be applied in each server depending on its resources. Great work!

Getting better every day!

Photo by Tobias Fischer on Unsplash