Zoomtutorials
  • Cloud
    • Cloud Hosting
      • AWS
      • Azure
      • GCP
      • Oracle Cloud
      • Alibaba Cloud
      • IBM Cloud
      • DigitalOcean
      • Linode
  • DevOps
    • DevOps Tools
      • Terraform
      • Git
      • GitHub
      • Jenkins
      • Docker
      • Kubernetes
      • Ansible
      • Python
      • Monitoring
    • Azure DevOps
      • Azure Boards
      • Azure Repos
      • Azure Pipelines
      • Azure Test Plans
      • Azure Artifacts
      • Extensions Marketplace
    • AWS DevOps
      • CodeStar
      • CodeCommit
      • CodeBuild
      • CodeDeploy
      • CodePipeline
      • CodeArtifact
  • Programming
    • Application
      • Magento 2
      • Magento
      • WordPress
      • PHP
      • .Net
      • .Net Core
      • NodeJS
      • JAVA
    • Database
      • Microsoft SQL Server
      • MySQL
      • MariaDB
      • Amazon Aurora
      • SQL Proxy
    • Web Server
      • IIS
      • Apache
      • NGINX
      • Tomcat
      • NodeJS
    • Microsoft Dynamics ERP
      • Dynamics 365
        • D365 FnO
        • D365 BC
      • Dynamics AX
      • Dynamics NAV
      • Dynamics GP
      • Dynamics CRM
  • HowTos
    • Linux OS
      • RHEL
      • Ubuntu
      • CentOS
      • Fedora
      • Debian
    • Linux Tips
      • Linux Commands
      • Linux Tricks
      • Linux Cheatsheet
    • Windows OS
      • Server 2019
      • Server 2016
      • Server 2012
      • Server 2008
      • Windows 11
      • Windows 10
      • Windows 8
      • Windows 7
    • Windows Tips
      • Windows Commands
      • Windows Shortcuts
    • Microsoft
      • Office 365
      • Microsoft 365
      • SharePoint
      • MS Dynamics ERP
  • Tools
    • Monitoring
      • New Relic
      • ELK Stack
      • Loggly
      • DataDog
      • Papertrial
      • Graylog
      • UptimeRobot
    • Security
      • Firewall
      • Internet Security
      • Antivirus
    • Backup
      • SQL Backup
      • Iperius Backup
  • Hire Us
  • Courses
  • Submit Tutorials
  • More…
    • …
      • Services
      • News
      • Write For Us
      • Community
      • Donate ❤️
      • Contact Us
Facebook Twitter Instagram
Zoomtutorials
  • Cloud
    • Cloud Hosting
      • AWS
      • Azure
      • GCP
      • Oracle Cloud
      • Alibaba Cloud
      • IBM Cloud
      • DigitalOcean
      • Linode
  • DevOps
    • DevOps Tools
      • Terraform
      • Git
      • GitHub
      • Jenkins
      • Docker
      • Kubernetes
      • Ansible
      • Python
      • Monitoring
    • Azure DevOps
      • Azure Boards
      • Azure Repos
      • Azure Pipelines
      • Azure Test Plans
      • Azure Artifacts
      • Extensions Marketplace
    • AWS DevOps
      • CodeStar
      • CodeCommit
      • CodeBuild
      • CodeDeploy
      • CodePipeline
      • CodeArtifact
  • Programming
    • Application
      • Magento 2
      • Magento
      • WordPress
      • PHP
      • .Net
      • .Net Core
      • NodeJS
      • JAVA
    • Database
      • Microsoft SQL Server
      • MySQL
      • MariaDB
      • Amazon Aurora
      • SQL Proxy
    • Web Server
      • IIS
      • Apache
      • NGINX
      • Tomcat
      • NodeJS
    • Microsoft Dynamics ERP
      • Dynamics 365
        • D365 FnO
        • D365 BC
      • Dynamics AX
      • Dynamics NAV
      • Dynamics GP
      • Dynamics CRM
  • HowTos
    • Linux OS
      • RHEL
      • Ubuntu
      • CentOS
      • Fedora
      • Debian
    • Linux Tips
      • Linux Commands
      • Linux Tricks
      • Linux Cheatsheet
    • Windows OS
      • Server 2019
      • Server 2016
      • Server 2012
      • Server 2008
      • Windows 11
      • Windows 10
      • Windows 8
      • Windows 7
    • Windows Tips
      • Windows Commands
      • Windows Shortcuts
    • Microsoft
      • Office 365
      • Microsoft 365
      • SharePoint
      • MS Dynamics ERP
  • Tools
    • Monitoring
      • New Relic
      • ELK Stack
      • Loggly
      • DataDog
      • Papertrial
      • Graylog
      • UptimeRobot
    • Security
      • Firewall
      • Internet Security
      • Antivirus
    • Backup
      • SQL Backup
      • Iperius Backup
  • Hire Us
  • Courses
  • Submit Tutorials
  • More…
    • …
      • Services
      • News
      • Write For Us
      • Community
      • Donate ❤️
      • Contact Us
Facebook Twitter Instagram YouTube LinkedIn
Zoomtutorials
MySQL

MySQL and MariaDB Performance Tuning and Optimization

ZT Senior EditorBy ZT Senior EditorAugust 4, 2021Updated:August 10, 2021No Comments7 Mins Read
Facebook Twitter Pinterest LinkedIn Tumblr Email
Share
Facebook Twitter LinkedIn Pinterest Email

MySQL or MariaDB is vast and has lots of areas to optimize and tweak to get the optimal performance. Some changes can be done dynamically, while others require a server restart. It is common to find a MySQL or MariaDB installation with a default configuration, however the later may not be appropriate based on your workload and setup.

Here are the key areas in MySQL and MariaDB which I have taken from different expert sources in the MySQL world, as well as my experiences. This blog would serve as your knowledgebase to tune performance and make your MySQL great again.

For those who are just looking for a quick way to optimize the performance, below is the optimized configuration file.
Note: The configuration file should be tweaked here and there based on your actual server configuration.

# === Optimized my.cnf configuration for MySQL/MariaSQL (on Ubuntu, CentOS etc. servers) ===
#
# ~ Updated July 2021 ~
#
# The settings provided below are a starting point for a 8-16 GB RAM server with 
# 4-8 CPU cores.
# If you have different resources available you should adjust accordingly to 
# save CPU, RAM & disk I/O usage.
#
# The settings marked with a specific comment or the word "UPD" (after the value)
# should be adjusted for your system by using database diagnostics tools like:
#
# https://github.com/major/MySQLTuner-perl
# or
# https://github.com/RootService/tuning-primer (supports MySQL up to v5.7)
#
# Run either of these scripts before optimizing your database, at least 1 hr after
# the optimization & finally
# at least once a day for 3 days (without restarting the database) to see how your 
# server performs and if you need
# to re-adjust anything. The more MySQL/MariaDB runs without restarting, the more
# usage data it gathers, so these diagnostics scripts will report in mode
# detail how MySQL/MariaDB performs.
#
#
# IMPORTANT NOTE: If there is NO comment after a setting value, then 99,9% of the
# times you won't need to adjust it.
#
#
# --- THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING ---
#
# If any terminal commands are mentioned, make sure you execute them as "root" user.
#
# If MySQL or MariaDB cannot start (or restart), then perform the following actions.
#
# 1. If the server had the stock database configuration and you added or updated any
# "innodb_log_*" settings (as suggested below), then execute these commands ONLY
# the first time you apply this configuration:
#
# $ rm -rvf /var/lib/mysql/ib_logfile*
# $ chown -R mysql:mysql /var/lib/mysql
# $ service mysql restart
#
# or use the shorthand command:
# $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service
# mysql restart
#
# 2. If the setting "bind-address" is not commented out, then make sure the file
# /etc/hosts is
#properly configured. A good example of a "clean" /etc/hosts file is something
# like this:
#
# 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
# ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
# 1.2.3.4 hostname.domain.tld hostname # <-- Replace accordingly!
#
# Finally restart the database service:
#
# $ service mysql restart
#
# 3. If the database service cannot restart even after the first 2 steps, make
# sure the database data folder (common for either MySQL or MariaDB) "/var/lib/mysql"
# is owned by the "mysql" user AND group.
# Additionally, the folder itself can have 0751 or 0755 file permissions.
# To fix it, simply do this:
# $ chown -R mysql:mysql /var/lib/mysql
# $ chmod 0755 /var/lib/mysql
#
# Finally restart the database service:
#
# $ service mysql restart
#
#
# ~ FIN ~


[mysql]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
# === Required Settings ===
basedir = /usr
bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
datadir = /var/lib/mysql
max_allowed_packet = 256M
max_connect_errors = 1000000
pid_file = /var/run/mysqld/mysqld.pid
port = 3306
skip_external_locking
skip_name_resolve
socket = /var/run/mysqld/mysqld.sock
tmpdir = /tmp
user = mysql

# === SQL Compatibility Mode ===
# Enable for b/c with databases created in older MySQL/MariaDB versions
# (e.g. when using null dates)
# sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,
# ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

# === InnoDB Settings ===
default_storage_engine = InnoDB
innodb_buffer_pool_instances = 4 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 4G # Use up to 70-80% of RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_stats_on_metadata = 0

#innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum
# size for the ibtmp1 file
#innodb_thread_concurrency = 4 # Optional: Set to the number of CPUs on your system
# (minus 1 or 2) to better
# contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
# the overall load produced by MySQL/MariaDB.
innodb_read_io_threads = 64
innodb_write_io_threads = 64
#innodb_io_capacity = 1000 # Max is 2000

# === MyISAM Settings ===
# The following 3 options are ONLY supported by MariaDB & up to MySQL 5.7
# Do NOT un-comment on MySQL 8.x+
#query_cache_limit = 4M # UPD
#query_cache_size = 64M # UPD
#query_cache_type = 1 # Enabled by default

key_buffer_size = 32M # UPD

low_priority_updates = 1
concurrent_insert = 2

# === Connection Settings ===
max_connections = 100 # UPD - Important: high no. of connections = high RAM consumption

back_log = 512
thread_cache_size = 100
thread_stack = 192K

interactive_timeout = 180
wait_timeout = 180

# For MySQL 5.7+ only (disabled by default)
#max_execution_time = 30000 # Set a timeout limit for SELECT statements
# (value in milliseconds).
# This option may be useful to address aggressive crawling on large sites,
# but it can also cause issues (e.g. with backups). So use with extreme caution and test!
# More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
# sysvar_max_execution_time

# For MariaDB 10.1.1+ only (disabled by default)
# max_statement_time = 30 # The equivalent of "max_execution_time" in
# MySQL 5.7+ (set above)
# The variable is of type double, thus you can use subsecond timeout.
# For example you can use value 0.01 for 10 milliseconds timeout.
# More info at: https://mariadb.com/kb/en/aborting-statements/

# === Buffer Settings ===
innodb_sort_buffer_size = 2M # UPD
join_buffer_size = 4M # UPD
read_buffer_size = 3M # UPD
read_rnd_buffer_size = 4M # UPD
sort_buffer_size = 4M # UPD

# === Table Settings ===
# In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform 
#   an extra action for table_open_cache & open_files_limit
# to be overriden (also see comment next to open_files_limit).
# E.g. for MySQL 5.7, please check:
# https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
table_definition_cache = 40000 # UPD
table_open_cache = 40000 # UPD
open_files_limit = 60000 # UPD - This can be 2x to 3x the table_open_cache value
 or match the system's
# open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
# In systemd managed systems this limit must also be set in:
# /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
# /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)

max_heap_table_size = 128M
tmp_table_size = 128M

# === Search Settings ===
ft_min_word_len = 3 # Minimum length of words to be indexed for search results

# === Logging ===
log_error = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes = 1
long_query_time = 5
slow_query_log = 0 # Disabled for production
slow_query_log_file = /var/lib/mysql/mysql_slow.log

[mysqldump]
# Variable reference
# For MySQL 5.7+: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB: https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet = 512M

Let’s now jump into the key areas for optimizing performance in MySQL and MariaDB.

 

For more info on MySQL performance tuning, you can read the official MySQL Performance Tuning and Optimization Resources here.

 

 

 

MySQL Optimization
Share. Facebook Twitter Pinterest LinkedIn Tumblr Email
Avatar photo
ZT Senior Editor
  • Website

The editor is the founder and chief editor of ZoomTutorials Blog, a leading tutorials and technology blogging site specializing in DevOps, SysAdmin and Cloud Technologies to help IT professionals in their day to day work. He is a Senior Cloud and DevOps Solutions Engineer at a leading eCommerce development Company and has more than 11+ years of Cloud, DevOps and SysAdmin experience working with Fortune 500 companies to solve their most important IT backbones. He lives in Hyderabad with his wife, a son and a daughter.

Related Posts

How to install Percona MySQL 5.7 on CentOS 7

July 20, 2019

Leave A Reply Cancel Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Buy Me a Coffee Plz...

Get our latest tutorials
Recent Posts
  • Install Latest Git 2.x on CentOS/RHEL
  • How to Use the slmgr Command in Windows
  • How To Extend Windows Server Evaluation after Trial Period Expiry
  • List of Useful Magento 2 Commands
  • Magento 2 Commands and the Use Cases
  • MySQL and MariaDB Performance Tuning and Optimization
  • Microsoft Dynamics AX End of Support/Life
  • Magento Community read/write splitting with Database Proxy
  • Git Commands for developers and sysadmins
  • Install the git credential-oskeychain
December 2022
M T W T F S S
 1234
567891011
12131415161718
19202122232425
262728293031  
« Aug    
Archives
Facebook Twitter Instagram YouTube LinkedIn
  • Donation ❤️
  • About
  • Services
  • Submit Tutorials
  • Contact Us
  • Privacy Policy
  • Terms of Use
Copyright © 2023 by ZT Consulting. Designed with ❤️ by CloudSols.com. Hosting Partner Contabo.com. SSL Partner SSL.com. Monitoring Partner UptimeRobot.com

Type above and press Enter to search. Press Esc to cancel.