Optimize the database

DracoBlue

Below, I am sending the database performance results from Plesk and Enhance panels. The test was conducted on the same server. The results clearly show that Enhance should have a better-optimized database in terms of performance.
PLESK:

ENHANCE:

FabioP

DracoBlue Isaia-Arknet_PTY_LTD twest bocagua necrom Andreas JohnB josedieguez

Hi DracoBlue, Isaia-Arknet_PTY_LTD, twest, bocagua, necrom, deydod, Andreas, JohnB, josedieguez.

To avoid trying to optimize the database or to not go crazy reading ten thousand guides on the internet, my modest advice is to start by using this totally free, safe, tested and maintained open source script since February 22, 2014 which will give you up to immediately a very detailed overview of your database and all the advice to best optimize it based on many tests and checks that the script itself performs.

The script supports MySQL, MariaDB, Percona Server etc., etc.

The script runs in Perl, it is very easy to install, use and understand, and on the official project page on GitHub you will find all the necessary information, advice and suggestions (if you decide to use it, I recommend you spend 10 minutes or more of your time read everything, it will be minutes well spent, believe me).

The well-known and used script is MySQLTuner and this is the official GitHub link of the Project:

https://github.com/major/MySQLTuner-perl

Obviously this is an excellent tool for optimizing the performance of your databases and then you can always carry out more precise optimizations from time to time, but this is truly an excellent and valid starting point for all the optimizations of your databases.

Hoping to have been useful to you, as always I wish you a good day and a good job.

Adam

Try increasing innodb_buffer_pool_size in the my.cnf settings, I think that will improve things for you. Enhance doesn't set this by default as it's very much dependent on the amount of RAM you are willing to give over to MySQL vs PHP workers, web server, email server, etc.

On Plesk, are you using MariaDB or MySQL (5.7 or 8.x)?

DracoBlue

Adam i use MariaDB 10.11 in Plesk

DracoBlue

After some small configurations, the result improved. I look forward to supporting MariaDB.

Isaia-Arknet_PTY_LTD

DracoBlue, Would you be kind enough to share the optimisation done to achieve better DB performance?

The standard Enhance install, including changing innodb_buffer_pool_size to 1G, did not improve Performance.

Thank you

twest

Isaia-Arknet_PTY_LTD you should check the mysql8 guidebook and Google about these to learn what they mean/do, but here's a pretty good config I'm using on a 50 CPU/250gb server:

innodb_buffer_pool_instances 21
innodb_buffer_pool_size 20000M
innodb_file_per_table 1
innodb_log_file_size 5000M
join_buffer_size 1M
key_buffer_size 100M
max_connections 1000
max_heap_table_size 2000M
open_files_limit 20000
tmp_table_size 2000M
innodb_flush_log_at_trx_commit 2

Some of those things you'd want to scale down, some not. The inno log file size should be 1/4 of buffer pool size. Join buffer you can leave same. If you use a lot of MyISAM tables you may want to increase the key buffer appropriately (though you should really use all innodb since it's faster and corrupts less often). You can leave the flush log commit as it will vastly improve the number of queries you can run while the "risk" is negligible (loss of 2 seconds of data during unexpected shutdown).

There's tons of free tuning tools for databases you can run that will analyze your DB to give recommendations. I'm not sure what a good one is since I do everything by "feeling" nowadays lol... Once you read up on what those entries I put above mean then you can pretty easily figure out how best to manipulate them for your system.

You can test around too, add your config then restart the database container for the settings to take effect, then see how performance goes, tweak as needed. I usually shoot for dedicating around 50% of system resources to the database, sometimes more or less depending on what the server is hosting. A lot of times devs will suggest running as much as 80% resources for database if you host a lot of WordPress sites since it used so many DB resources - but realistically I find that a bad idea because people aren't in wp-admin all the time, for 99% of the time a website is only consuming resources to server static html pages to visitors from cache, not an enormous amount of DB activity (though WP and plugins always have so many actions scheduled that there's always a baseline of DB usage that's significant to account for). The little bit visitors use the DB is when using the search function on a site, or if the site has something like Woocommerce. So if you have a lot of Woocommerce sites then yeah maybe commit 60% of resources to the database. For a normal mix of sites it should be more like 30-50%, because you want there to be enough resources dedicated to your webserver or other services running on the server. You should of course adjust your config as the needs of the server grows/changes, a yearly checkup/tuning is a good idea.

Note the numbers I put above are consuming a relatively small amount of total available system resources due to the reasoning I explained - that server needs much more resources available for Apache due to the huge volume of web traffic it's getting. Not much DB activity (relatively) but a huge amount of static html pages for Apache to serve. That server is getting like 1.5 million visits/day, everything is well-cached so everything loads super fast and server load is super low.

DracoBlue

Isaia-Arknet_PTY_LTD

VPS - 6cores, 16GB RAM, 668GB NVMe

My setting:

innodb_flush_neighbors=0
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_io_capacity=450
innodb_random_read_ahead=ON
innodb_buffer_pool_size=134217728
table_open_cache=16013
innodb_log_file_size=33554432

Isaia-Arknet_PTY_LTD

twest Thank you for the above advice,

I have done some settings, and considering I am not a DB expert, i am sure others might come and offer better advice/ settings.
The outcome was considerably better 🙂
The server i set is a 6 core 32 Gb ram.

Before:
Database

Importing large amount of data to database 0.94 17.8 sec
Simple queries on single table 7.16 3.4 sec
Complex database queries on multiple tables 9.05 3 sec

After:
Database

Importing large amount of data to database 9.01 2.5 sec
Simple queries on single table 10 0.6 sec
Complex database queries on multiple tables 8.57 3.6 sec

Settings used:

innodb_buffer_pool_instances 4
innodb_buffer_pool_size 16G
innodb_file_per_table 1
innodb_flush_log_at_trx_commit 2
innodb_log_file_size 1G
join_buffer_size 1M
key_buffer_size 100M
max_connections 1000
max_heap_table_size 1G
open_files_limit 20000
tmp_table_size 2G

ivansalloum

twest I think the innodb_buffer_pool_instances directive is deprecated now (since 10.5.1). I use MariaDB.

twest

Isaia-Arknet_PTY_LTD Nice performance boost! Only things I would suggest is increase your innodb log file size to 4G. It should be 1/4 of your buffer pool. And you may increase buffer pool instances to 16 (so each one is about 1G of your 16G buffer pool).

Next to optimize php and the webserver 😉

Isaia-Arknet_PTY_LTD

twest I am not sure why, but the best numbers I managed to get were when innodb_buffer_pool_instances "2" without touching the rest of the settings.

Importing large amount of data to database 9.16 2.4 sec
Simple queries on single table 10 0.6 sec
Complex database queries on multiple tables 9.05 3 sec

Isaia-Arknet_PTY_LTD

I am using OLS on this server, I am not sure what can be optimised on OLS server side, same with PHP. If you have any suggestions, i am all ears 🙂

twest

Isaia-Arknet_PTY_LTD when you have more sites, more databases, bigger databases with bigger datasets to handle, then the optimal config will probably perform better. The testing tools are nice to help, but you have to also consider real world and future conditions and how the db server will behave when there's hundreds of databases, thousands of tables, millions of points of data to handle. The mysql8 guidebook will say 1 instance per G pool size for optimal performance, it's probably the safest route to go. But you can monitor and see, adjust later if you're having issues with load or something's runs slow.

There's some good discussions on ols config in other threads. Mostly about the issues with using it in production (no persistent config and needing to restart webserver anytime htaccess changes for a site). For those reasons, I won't be spending time to optimize ols for now. But you might want to look up those threads to learn more, like you need to setup an admin account for ols in terminal, then you can login to the config screen on port 7080 I believe it is... But again, that config will reset during updates... If we can get persistent configs some day then ols will be very powerful - until then it's a bottleneck waiting to happen.

My optimized Apache config is almost done and then I'll be posting that in the big ModSecurity thread (since the ModSecurity config is easy way to add Apache config, even if you don't use modsec rules).

I haven't optimized php-fpm yet either, but it's on my list after Apache.

bocagua

Does anyone have any recommendations for a good sysadmin to optimize my database? thank you so much

necrom

bocagua you might want to explore https://releem.com/ which is a front-end to mysqltuner that makes it a bit friendlier to constantly monitor, evolve and apply MySQL tuning (we've used it on several non-enhance systems and it works really well).

Roman

Hi guys, the founder of Releem is here.

necrom Thanks for shout-out.

FabioP Thanks for your feedback. MySQLTuner is a helpful tool we love, and we collaborate with the MySQLTuner team❤️ MySQLTuner was at the core of the earliest Releem Agent versions to simplify the collection of MySQL metrics. We moved to GoLang a year ago, which allowed Releem Agent collect more metrics and automate operations, and Releem Platform bring better suggestions.

@DracoBlue The Releem Free version allows the monitoring of the database and suggests configurations to improve database performance for lightly utilized databases.
Also, you could try configuration from our research on how MySQL Performance tuning impacts performance of Drupal, Laravel, and WordPress https://releem.com/blog/web-applications-performance

P.S. We built Releem as a database performance-tuning solution, which should keep the database server well-tuned in the all environments without additional monitoring tools and manual calculations.

DracoBlue

MySQL Test:

MariaDB Test:

MariaDB the best!

deydod

I am on MariaDB 10.11 and I've tried to optimize the database, but without any luck - I am still getting 0.95 score on importing large amount of data to database.

Draco can you share your settings for MariaDB?

deydod

Well I believe I've found the problem:
innodb_flush_log_at_trx_commit = 2

From MariaDB documentation:

Controls the durability/speed trade-off for commits.
Set to
0 (write and flush redo log to disk only once per second),
1 (flush to disk at each commit),
2 (write to log at commit but flush to disk only once per second) or
3 (flush to disk at prepare and at commit, slower and usually redundant).

1 and 3 guarantees that after a crash, committed transactions will not be lost and will be consistent with the binlog and other transactional engines. 2 can get inconsistent and lose transactions if there is a power failure or kernel crash but not if mysqld crashes. 0 has no guarantees in case of crash.

Not sure exactly why there is so big difference in performance, but my score went from 0.95 to 10 instantly.

Andreas

Hey Everyone

I wanted to thank everyone for sharing their tweaks here.
I appreciate them very much.
I was testing the tweaks shared and this is what I found.

With no tweaks, I'm getting

Importing large amount of data to database 2.97
Simple queries on single table 8.67
Complex database queries on multiple tables 9.14

If I add the tweaks I get 10, 10 and 9,15

The only line that gave me the 10's was using either one of these lines.
innodb_flush_log_at_trx_commit=0
innodb_flush_log_at_trx_commit=2

twest

Andreas that's one good one to help reduce IO and expedite DB operation. If you have servers larger than around 4c/8gb I would recommend looking up other threads for more optimizations as well. I put some stuff specifically for Apache optimizations in a couple threads that should be helpful for scaling to larger servers (I only use large dedis). I may start working on an optimization for nginx soon too - I really want to get a good litespeed config, but it's kind of cost prohibitive for my use case (I would need +4 workers, which is an expensive license, and unfortunately OLS on enhance doesn't have a persistent config where I could add an optimized config beyond the default).

JohnB

twest =0 probably shouldn't be used in production especially when =2 gets basically equal performance with less risk. Neither should be used if you can't afford to lose up to 2 seconds of data or need ACID compliance but if thats the case, the site probably shouldn't be using Wordpress lol.

Andreas

JohnB I have removed it myself. My sites are loading up fast already.