How To Configure & Optimise MariaDB 5.5.X / MySQL 5.6 on Ubuntu 14.04 LTS Production Server ( Part IV )

If you are running a PHP based website, most probably you would have used MySQL as your relational database management system.The default settings of such database management systems would get your up and running but it does not always process every query effectively until we configure and optimise the system. In this tutorial we will go through system variables and techniques needed to optimise MySQL and MariaDB.

MariaDB

It’s a high performance drop-In MySQL replacement relational database management system, in simple terms it can be used in place of MySQL. Well atleast upto MariaDB 5.5, from version 10.0 MariaDB has decided abandon backward compatibility to MySQL.

my.cnf

The main configuration file for both MySQL and MariaDB are the same and is located at /etc/mysql/my.cnf in Ubuntu 14.04. To edit the following file use the command

sudo nano /etc/mysql/my.cnf

Backup

Before making any changes make sure that you backup your settings file and databases.

To backup adatabase

mysqldump -u root -p[root_password] [database_name_to_backup] > backupfilename.sql

To backup all databases

mysqldump -u root -p[root_password] --all-databases > backupfilename.sql

To backup configuration file

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf-backup

Optimise MySQL & MariaDB Configuration

There is no perfect “one size fits for all” configuration that can optimise a database system. There are several system variables unique to each environment and website that needs to be taken into account to get the best database performance. So, this is a guide for configuring system variables that help make best use of resources for optimal performance.

General Database Tuning

max_connections         = 1000
connect_timeout         = 60
wait_timeout            = 60
max_allowed_packet      = 8M
tmp_table_size          = 64M
max_heap_table_size     = 64M

• max_connections : Maximum number of client connections at any point of time.
• connect_timeout : Number of seconds to wait before sending ‘bad handshake’ response to connection packet. Increase value of connection is lost intermittently.
• wait_timeout : Number of seconds before inactive connections are closed
• _max_allowed_packet__ : The size of packets or string or any parameter send by API functions. 1GB is the max value limit and values must be multiples of 1024.
• Tmp table size : Size of temporary memory tables and is also limited by max_heap_table_size
• max_heap_table_size : Controls the maximum size of the rows in user created memory tables.

InnoDB

One of the main reasons to use MariaDB is its scalability and XtraDB engine. Similarly InnoDB specific variables could be configured to gain scalability & performance in MySQL without the need to migrate or use of XtraDB plugins.

innodb_log_file_size   = 500M
innodb_buffer_pool_size = 2G
innodb_log_buffer_size  = 256M
innodb_file_per_table   = 1
innodb_open_files       = 4000
innodb_io_capacity      = 4000
innodb_flush_method     = O_DIRECT
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_thread_concurrency = 32
innodb_lru_scan_depth=2000

Automated MySQL Tuning
The process of optimising configuration can be automated using MySQL Tuner. This is a read-only perl script which gives recommendations for better performance based on existing settings, current performance, and other system parameters.

This can be downloaded and run like any other perl script but the easiest way is to use the following command to install using ubuntu package manager.

sudo apt-get install mysqltuner

Once, installed it is recommended to wait up-to at-least 24 hours for mysqltuner to give you the best recommendations. To run mysqltuner use the following command

sudo mysqltuner

You will see something like this and will be asked to enter your database root username and password.

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login:

The final report varies according to your configuration.

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    innodb_buffer_pool_size (>= 364M)

Conclusion

These techniques would give you an insight into configuring and optimising MySQL / MariaDB. It is important to go through the learning curve and know the database systems well before you configure and optimise. Have log_slow_queries enabled , no amount of database tuning can improve performance if your software sends bad queries that eat up resources.

This brings to conclusion the LAMP / LEMP part of the tutorial series but we will dwell deeper into server side processing tools and security in future.

Subscribe and stay connected.