Related

Product Now Available: Managed MySQL Databases Product
How to solve Mysql database connection error frequently after wordpress installation? Question Question
MySQL job will not start - database list could not be retrieved? Question Question

Question

How to find out the best MySQL 8 settings should I set in my.cnf

Posted May 9, 2020 4.9k views
MySQLWordPress

I’m using the WordPress One-Click droplet (https://marketplace.digitalocean.com/apps/wordpress) with 2 cores and 4GB of RAM, and it’s running MySQL version 8.0.29, but unlike the previous setup found with Ubuntu 16.04 and MySQL 5.7, the my.cnf is pretty much empty.

This is the only thing in the my.cnf of mysql 8:

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log

I’ve been having some mysql high cpu usage, and I want to make sure that I’m not missing any configuration.

So, how do I find out which are the right settings that I should be using for WordPress site?

Add a comment
mhweb
By:
mhweb

Related

Product Now Available: Managed MySQL Databases Product
How to solve Mysql database connection error frequently after wordpress installation? Question Question
MySQL job will not start - database list could not be retrieved? Question Question

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
1 answer
KFSys May 9, 2020

Hi @mhweb,

We’ll the optimization of MySQL is usually a trial and error kind of stuff however there are different tools that can help you out. Me personally, I’ll recommend checking out the ‘MySQL Tunner’ one. The MySQLTunerscript assesses your MySQL installation, and then outputs suggestions for increasing your server’s performance and stability.

1.Download the MySQLTuner script:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Change the scripts permissions to be executable:

chmod +x mysqltuner.pl

Run the mysqltuner.pl script. You will be prompted to enter in your MySQL administrative login and password:

./mysqltuner.pl

The script will return results/suggestions which you can follow the optimize your MySQL.

Regards,
KDSys

Reply Report
  • mhweb May 9, 2020

    When you say trial and error, does this means that settings that I’d configure in the my.cnf won’t damage the data inside the database if I use the wrong parameter?

    What’s the worse it could happen when configuring the my.cnf file?

    Thanks,

    Reply Report
    • KFSys May 12, 2020

      Hi @mhweb,

      Yes, most of the configurations you put in your my.cnf won’t damage the data. The only exception is the innodb_force_recovery however you won’t need to use it unless your MySQL is broken and can’t start.

      The best solution would be using the mysqltunner.pl script.

      Regards,
      KDSys

      Reply Report

Related

Looking for something else?

Ask a new question Search for more help