MySQL performance improvement

MySQL 5.7 and 5.6 performance improvement

By Default MySQL performs a flush operation per commit which is inoffensive on not demanding environments but on medium and high demanding environment, it could induce to I/O Bottleneck issues, even when the storage could be a SSD or a most sophisticated storage solution like a raid.

AWS Database instances

For AWS MySQL Databases create a new parameter group based on the version that is running

Parameters Groups → Create Parameter Group

Finally on Instances choose the MySQL Database to use for UTP and modify it, assigning it the new parameter group and reboot the database.

Local databases

For local database parameters setup, just add these parameter to my*.ini configuration files on windows environment and my.cnf on Linux distros, procedure could be different depending on platform and MySQL version and release.

In windows the my.ini could be here

C:\ProgramData\MySQL\MySQL Server 5.6 or here

C:\ProgramData\MySQL\MySQL Server 5.7

It is not located here

C:\Program Files\MySQL\MySQL Server 5.6\my-default.ini. This is just a basic template

Parameters

set the following values, take care could be exceptions depending on the exact MySQL version, but in any case, this is intended only for 5.6 and 5.7 versions.

lower_case_table_names=2 #Windows

max_allowed_packet=524288000