Tuning your mysql db

[Jon12345]

I've heard you can use my.cnf to help you tune your mysql database. Does it make much difference to speed? Anyone got any before and after experiences?

Also, mariadb vs mysql...any before and after speed differences?

 

[djbaxter]

I switched to MariaDB a few years ago. I did notice it was faster and more stable for me but it was too long ago for me to be able to report any specific data.

 

[eva2000]

Also, mariadb vs mysql...any before and after speed differences?

It's not what you use that matters, but how you use it. MariaDB, Percona or Oracle MySQL all have their strengths and out of the box they may differ with their defaults, but you switching from one version to another won't magically make things better usually. It's a matter of properly tuning based on your specific MySQL usage load/activity you monitor over time.

One of my Centmin Mod users who runs a Xenforo forum clearly highlighted that point when he accidentally removed Centmin Mod's auto tuned MariaDB MySQL settings which is done on initial Centmin Mod installs automatically based on detected server processor, memory and disk resources available and based on my 20+ years of optimising vBulletin and now xenforo forum's MySQL performance. On removing the optimised MariaDB configuration settings, his forum and MySQL performance measurably dropped from his monitoring and charts he posted at https://community.centminmod.com/threads/mariadb-10-1-slower-after-upgrading-from-5-5.14254/ saw 14x times slower response times!

His chart



You can see the difference after he restored the automatically tuned MariaDB config settings here https://community.centminmod.com/th...er-after-upgrading-from-5-5.14254/#post-61092.

• MariaDB 5.5 preupgrade with auto optimised config = 5ms response times
• MariaDB 10.1 upgraded removed auto optimised config = 25-70ms response times
• MariaDB 10.1 restored auto optimised config = 5-10ms response times

Yes we're measuring in milliseconds but that could be all in takes for MySQL response times under high concurrent activity load. The user did say he noticed the slowness browsing his forums though as forums aren't made up just a single MySQL query.

Probably why ~10% of all largest Xenforo forums are now powered by my Centmin Mod LEMP stack https://community.centminmod.com/th...p-powers-10-of-xenforos-largest-forums.16435/

 

[Nirjonadda]

@eva2000 Please do you have any recommendation optimization for MySQL 5.7? We are not going to use MariaDB 10.3 but we will use MySQL 8 after cPanel support update. We us CentOS 7.7 kvm with 10 CPUs and 32GB Ram Memory.

This is my current my.cnf setting.

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

performance-schema=0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

local-infile=0
bind-address=127.0.0.1

performance-schema=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_file_per_table=1
default-storage-engine=InnoDB
max_allowed_packet=268435456
open_files_limit=10000

max_connections=800
max_user_connections=1000

 

[HWS]

This is my current my.cnf setting.

There is nothing in it which optimises anything. ;-)
Also 3(!) times the same config for disabling performance scheme.

You really would need to hire someone to optimise your database. Start with using mysqltuner.

 

[eva2000]

You really would need to hire someone to optimise your database. Start with using mysqltuner.

+1

Yes optimising mysql settings is partly optimising for your server resources available (i.e. Centmin Mod auto tuning) + optimising for your specific MySQL usage load activity. The latter is key for large active forums. Ignoring the monitoring/analysis part won't give your the most optimal configuration. Hence, why my paid consults involves days of work and even in some cases months of work, analysis and monitoring.

mysqltuner is okay to start with but recent changes in it's calculations can provide inaccurate recommendations if you try to follow those recommendations to a the letter. What would separate more optimal configs from other configs, would be fully understanding what recommendations mysqltuner gives and what they mean for your specific MySQL usage load activity.

 

[Xon]

You probably want innodb_buffer_pool_instances to be as low as possible, it doesn't give much actual wins above ~4 or so with modern versions of MySQL & MariaDB.

Some of the MySQL/MariaDB defaults are really bad for forums.

Key defaults to change;

• innodb_buffer_pool_size=50%-75% ram (manual number)
  • Single most important default, should be fairly large
• innodb_flush_log_at_trx_commit=2 or innodb_flush_log_at_trx_commit=0
  • the default innodb_flush_log_at_trx_commit=1 causes very poor performance when doing installs (XF or add-ons) due to all the extra fsyncs

Defaults you really should change;

• default_storage_engine=InnoDB
  • default table format of MyISAM is bad
• innodb_file_per_table=1
  • This makes individual table restores possible and compacting tables sane.

• # CHARACTER SET
  collation-server = utf8mb4_general_ci
  init-connect='SET NAMES utf8mb4'
  character-set-server = utf8mb4

  Ensure utf8mb4 is consistently used, especially important for mysqldump-based backups!!!

There are many other options, but like @eva2000 said performance tuning requires data gathering and just as importantly; testing those changes after they have been implemented.

 

[rdn]

• Ensure utf8mb4 is consistently used, especially important for mysqldump-based backups!!!

By the way, what is the proper (- or _)?
If you declare it on the config file like /etc/mysql/mariadb.conf.d/50-server.cnf

collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

or

collation_server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
character_set_server = utf8mb4

 

[Xon]

By the way, what is the proper (- or _)?

Either work. Just pick one style to be consistent and aid when greping the config for information.