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.
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.
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
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 : Controls the maximum size of the rows in user created memory tables.
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
You will see something like this and will be asked to enter your database root username and password.
>> MySQLTuner 1.0.1 - Major Hayden <firstname.lastname@example.org> >> 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)
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.