How to fine tune MySQL for Plastic SCM

Out of the box, MySQL installations have a pretty conservative default configuration. If you have server machine to be used with Plastic SCM, you may want to adjust these configuration settings in the my.cnf file (or my.ini, in Windows) to improve the performance of the server. These adjustments have a huge impact in the speed of Plastic SCM.

Hardware

The performance of MySQL and the Plastic SCM server depend largely on the amount of memory and CPU available. As usual with performance tuning, there is no one-size-fits-all answer, but these guidelines may be useful to squeeze more out of your hardware.

For this article, we are assuming that you have a decent machine, dedicated to the Plastic SCM server and MySQL exclusively, with 4GB of RAM.

Steps

Edit the my.cnf file, normally located in /etc or /etc/mysql in Linux, or the c:\Program Files\MySQL\my.ini file in Windows. Add or edit the lines below.

The innodb_buffer_pool_size should be around half of the memory of your server machine. This is the most important value. Note that due to a limitation in MySQL, this value should be maximum 4GB (more details here):

innodb_buffer_pool_size = 2G

innodb_additional_mem_pool_size should be a 5% of the innodb_buffer_pool_size:

innodb_additional_mem_pool_size = 100M

Set innodb_log_file_size to 25% of buffer pool size.

innodb_log_file_size = 500M

Keep in mind that changing this value renders your ib_logfileX files unusable (they are located in the data directory), and MySQL may refuse to start until the files are removed and MySQL can recreate them. Make sure that MySQL was shut down correctly before removing these files.

innodb_log_buffer_size can be around 2% of the buffer pool size or, at a minimum, 8MB.

innodb_log_buffer_size = 40M

Once these settings are in place, restart the MySQL service.

Also, don't forget to set the max_allowed_packet to 10MB. This is mandatory for correct Plastic SCM operation:

max_allowed_packet = 10M