Tuning your mysql db

Jon12345

Active member
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

Well-known member
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

Well-known member
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

1582904311035.png

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/ :D :cool:
 
Last edited:

Nirjonadda

Well-known member
@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.

Code:
# 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

Well-known member
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

Well-known member
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

Well-known member
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.
  • Code:
    # 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.
 
Top