Mariadb my.cnf advice please - Centos8

Hi @colinshaun77Eel,

The 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 script’s 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.

Other than that, it’s not possible to suggest valid values as this depends on the traffic you get, on the queries you are executing and how often are they executed.

Regards, KDSys

Hello there,

In addition to what has already been mentioned, I wanted to point out that CentOS Linux 8 has reached End Of Life (EOL) on December 31st, 2021.

I would strongly recommend planning either migration to another OS like Debian or Ubuntu, or upgrading to CentOS Stream.

I personally believe that the safest way to do this is to follow these steps here:

• Spin up a new Droplet with the desired OS
• Install the necessary services (webserver, sql server, etc.)
• After that using rsync for example copy over your files and migrate your databases
• Then test your website and server fully to make sure that everything is working as expected
• Finally, change your DNS to point to the new Droplet

Regards, Alex

Heya,

I just wanted to provide more information on the topic.

Creating a customized my.cnf file for your MariaDB configuration is a great step towards optimizing your WordPress blog’s performance, especially when you’re planning to host multiple blogs on a single server. Since you’re using a $10 droplet, I assume you have limited resources, so the configuration should be optimized for a balance between performance and resource usage.

Here’s a basic my.cnf configuration to get you started. Remember, these settings are starting points and should be adjusted based on your server’s performance and the results from MySQLTuner after it’s been running for a while.

[mysqld]
# General Settings
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1

# MyISAM
key_buffer_size         = 32M
myisam-recover-options  = BACKUP

# SAFETY
max_allowed_packet      = 16M
max_connect_errors      = 1000000

# DATA STORAGE
datadir                 = /var/lib/mysql/

# CACHES AND LIMITS
tmp_table_size          = 32M
max_heap_table_size     = 32M
query_cache_type        = 0
query_cache_size        = 0
max_connections         = 75
thread_cache_size       = 50
open_files_limit        = 65535
table_definition_cache  = 1024
table_open_cache        = 2048

# INNODB
innodb_flush_method     = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size    = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table   = 1
innodb_buffer_pool_size = 256M

# LOGGING
log_error               = /var/log/mysql/error.log
slow_query_log_file     = /var/log/mysql/mysql-slow.log
slow_query_log          = 1
long_query_time         = 2
log_queries_not_using_indexes = 1

Important Notes:

1. Resource Allocation: The settings like innodb_buffer_pool_size, tmp_table_size, max_connections, etc., should be adjusted based on your server’s RAM and CPU. The provided values are conservative and suitable for a server with limited resources.

2. Logging: Slow query logging is enabled, which is useful for identifying queries that could be optimized. Adjust long_query_time as needed.

3. InnoDB Settings: These are crucial for performance, especially innodb_buffer_pool_size. It’s set to 256M here, but you may need to adjust it based on your available RAM.

4. Query Cache: It’s disabled in this configuration (query_cache_size = 0) as it’s often more beneficial for dynamic sites like WordPress. However, this can be adjusted based on your specific use case.

5. Security: bind-address is set to 127.0.0.1 to allow local connections only. Ensure proper firewall settings if remote access is required.

After applying these settings, monitor your server’s performance and adjust the configuration as needed. MySQLTuner will provide valuable insights after it has collected enough data. Always make small changes and monitor their impact before proceeding with further optimizations.