MySQL tuning with my.cnf settings optimisation

[marcusquinn]

I did a bunch of research on this a while back for MySQL my.cnf settings best-practice for optimal RAM & CPU usage, and wrote up here sample settings with typical VPS configs for reference here:

• https://gitlab.com/d2c.io/wordpress-scalable-lemp-single-server/-/blob/master/configs/my.cnf - Reference

There's a few things in there I think could be automated based on knowing the VPS RAM & CPU count.

And a few things that could be admin panel settings, like enabling SQL Profiling and accessing the slow query logs for review.

TBH, not looked into what you already do but maybe some ideas in here to test. Guessing similar applies to other DBs but I only looked at MySQL & InnoDB as our primary environment.

Further Reading

• https://www.wpintense.com/2017/06/12/installing-and-configuring-fastest-possible-wordpress-stack-digital-ocean/
• http://www.mysqlcalculator.com/
• https://bugs.mysql.com/bug.php?id=82577
• https://launchpad.net/mysql-tuning-primer
• https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/
• https://www.percona.com/resources/technical-presentations/optimizing-mysql-configuration-percona-mysql-university-montevideo
• https://www.wpintense.com/2016/07/26/enable-slow-query-log-and-identify-slow-queries-percona-db/

[Lonkle]

@marcusquinn Bookmarked this as a resource, thanks for collecting all of this!

[marcusquinn]

@Lonk Interesting isn't it. Often I get "received wisdom" saying defaults are usually best - but in this case, you might be scaling your VPS to 32 cores and still only using 1 without knowing it if you didn't sanity-check those defaults.

[fbartels]

@marcusquinn don't forget the good old http://mysqltuner.pl/

[robi]

About a decade ago, there was an idea for a project to do this app wide.

As in have a profiler be able to tune any App (via config files and runtime params) it was aware of dynamically during various user loads.

Initially meant for cluster Apps, but easily applies to single apps too.

Feedback loops handle all the parameters automagically.

[Lonkle]

@robi said in MySQL tuning with my.cnf settings optimisation:

About a decade ago, there was an idea for a project to do this app wide.

As in have a profiler be able to tune any App (via config files and runtime params) it was aware of dynamically during various user loads.

Initially meant for cluster Apps, but easily applies to single apps too.

Feedback loops handle all the parameters automagically.

Did anything come of it?

[Lonkle]

@fbartels said in MySQL tuning with my.cnf settings optimisation:

@marcusquinn don't forget the good old http://mysqltuner.pl/

Never saw this before, when do we run / apply it? Anytime or only during installation?

[robi]

@lonk It was architected but not implemented. Life happened.

Still a good idea.

[rmdes]

@marcusquinn In the cloudron context, where does my.cnf goes to be taken into account ?

[fbartels]

@lonk said in MySQL tuning with my.cnf settings optimisation:

when do we run / apply it?

Mysql tuning is best applied after a few days of database usage. Applications usually have different load patterns.

[Lonkle]

@fbartels said in MySQL tuning with my.cnf settings optimisation:

@lonk said in MySQL tuning with my.cnf settings optimisation:

when do we run / apply it?

Mysql tuning is best applied after a few days of database usage. Applications usually have different load patterns.

Gotcha, thanks for the tip! ️