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:
Optimize the database
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)?
After some small configurations, the result improved. I look forward to supporting MariaDB.
5 days later
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
- Edited
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.
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
- Edited
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 
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 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 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.
- Edited
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=33554432Does anyone have any recommendations for a good sysadmin to optimize my database? thank you so much
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).
10 days later
MySQL Test:
MariaDB Test:
MariaDB the best!
15 days later
- Edited
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?
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.
14 days later
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
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).
- Edited
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.