Blog | Dec 11, 2013

MySQL - Master of small things

Due to its ability to get installed in a snap, MySQL database is the most sought after databases now-a-days by the coders and the developers in the small and medium scale business environment.
However maintenance of the same is a different game all together. A default install may work initially, however, with an exponential growth in the data it may face bottlenecks and thus slowdown is inevitable with the same code that worked earlier.
Though coding practices continue to play a larger role, it is crippled when faced with a badly configured or tuned database. A right start goes far in tuning anything, so we come to the MySQL configuration file.

Knowing the Starting Point:

The following observations and suggestions are being given keeping the Linux based production environment.
After MySQL is installed via repository; default configuration files may be found at '/usr/share/MySQL/'.  They are named as 'my-*.cnf'(* as wildcard).
It is safer to lean towards the 'my-huge.cnf' as a starting point for tweaks.
This needs to be copied to '/etc/' and renamed to 'my.cnf'.

Tweaking the Engine Before the Race:

Now before starting MySQL, make modifications to the following variables in the file in an effort to maximize the MySQL database performance:

1) skip-name-resolve -Enable this to restrict client connects to IP Addresses. It helps in preventing "unauthorized user" connections.
2) max_allowed_packet - Set this to 16M, which is sufficient.
3) max_connections - Set this to 1000, which typically takes care of immediate needs.
4) thread_cache_size - Set this to 128 to enable cache thread use instead of creating new threads.
5) thread_concurrency - Set to number of CPU*2
6) default-storage-engine - With MySQL 5.5 InnoDB becomes the default storage engine which is good for high transactional business applications.  A developer should evaluate their business applications, if it’s a high read type then it makes sense to override default engine to MyISAM.
7) binlog_format - Set to mixed
8) innodb_buffer_pool_size - Set to 1/2 total RAM
9) innodb_additional_mem_pool_size - Set to 64MB, however this depends on number of tables.
10) innodb_log_file_size - Set to 200M ,this helps save disk I/O as checkpoint flush is decreased.
11) innodb_log_buffer_size - Set to 8M, however increase if you doing a  larger transaction as this would save disk I/O and enhance performance.
12) innodb_file_per_table - Enable this as this helps reclaim space when innodb tables are dropped.

Working the Net - Rinse and Repeat:

Other than the above the following two scripts are essential in handling any issues pertaining to performance:

You can easily find directions to use and download links via a google search. These should be used when the database server has been running for greater than 48 hours at least and the results from them should be taken as a starting point in resolving issues and not to be followed blindly.

Remember that tweaking is a process which should always be repeated and improved upon over time as initial vectors change.  You may want to consider leveraging a managed service provider that can leverage their breadth of experience to make sure your MySQL environment is always running at its optimal level.