Optimize the database: Page 2 - Enhance Control Panel

Optimize the database

DracoBlue

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

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).

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.

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.

josedieguez

A Small VM (4c-8gb ram)
had Database scores:
0.96 - 5.98 - 8.09

After adding
innodb_flush_log_at_trx_commit=2
And mariadb reboot

New Database scores:
8.64 - 10.0 - 8.21

Interesting the big impact.

JohnB

josedieguez It has a big impact because you are risking data integrity and data loss for better performance. With =2 setting you may lose or corrupt the last 2 seconds of changes in the database.

Personally I wouldn't have an issue with this risk unless its a busy woocommerce type site but you should definitely be aware of the side affect of such changes. Sometimes dataloss isn't noticed until much later when backups can't save you. Imagine end of year accounting and it comes to light that an order is missing.

josedieguez

JohnB
Yep, thanks for the information, we are using it for the VM that hosts our own website (i can totally tolerate that), but won't use it on servers with clients.

necrom

FabioP

As I said:

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).

FabioP

necrom

Hi necrom,

Thank you for the advice, I already knew Releem and the services it offers, but I think that the free version on Releem is too limited and that the paid versions on Releem are too limited and above all too, too expensive, offering the exact same things that offers the Open Source MySQL Tuner script for free and without any limits.

For this reason, I remain of my opinion that the Open Source MySQL Tuner script available on GitHub has always been the best solution, furthermore, even though it does not have a GUI (Graphical User Interface) in my humble opinion it is very understandable and above all, it has no limitations of use and is completely free and always updated, as well as safe and reliable.

However, while remaining of my opinion, I thank you again for the suggestion and wish you a good day and a good job.

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.

twest

FabioP yes MySQLTuner is badass, loved that tool back in the day when I was learning. Maybe I'll give it a run on one of my servers to see how it likes my config, for old times sake 😄

deydod

FabioP, I was using MySQL Tuner in the past 10 years and I can say for sure that you should NOT trust this tool.
Its good for calculating stuff how much ram you mysql will use, but NEVER blindly trust the suggestions that its giving for improving performance.

If you don't know what it is, then read the docs and then carefully decide weather you should apply the change or not.

FabioP

deydod

Hi deydod,

I also know MySQL Tuner very well and all the documentation that concerns it and I have used it and have been using it since its first release about 10 years ago and I believe that MySQL Tuner like any other optimizer including online ones is not the magic wand that solves problems with a simple touch.

In fact, in the hope of being useful, I have only limited myself to recommending it as a simple and good starting point for all those who are beginners or for those who are not yet very familiar with Databases and their complex management.

Obviously, serious and professional optimization and management of the Data Base requires many years of experience and management of the Databases and also of their resources and not just the single configuration and optimization of the parameters in a my.cnf file, however important that may be.

8Dweb

Roman I know there was an older post in the Enhance forums regarding setting up on an Enhance server, do you have an updated FAQ with specific instructions for Enhance servers?

FabioP

Roman

Hi Roman and welcome, from what you write I read that you collaborate with the MySQLTuner developers and this makes me very happy.

Thank you for the updates and information you have provided us especially regarding your move to GoLang which is a very interesting and promising thing.

I take this opportunity to greet you and wish you a good day and a good job.

« Previous Page