Optimize MySQL Database Insert


In order to make the data insertion faster, you need to append ?useServerPrepStmts=false&rewriteBatchedStatements=true in the url property that you specify in JDBC configuration file. 


e.g. url=jdbc:mysql://localhost:<portNumber>/<dbName>?useServerPrepStmts=false&rewriteBatchedStatements=true 

Optmize MySQL Database Update


In order to make the data updation faster, you need to make 2 changes in your MySQL configuration file (mysqld.cnf) under [mysqld] section.


[mysqld]

key_buffer_size = 1024

innodb_flush_log_at_trx_commit = 2 


Steps to follow on Mac/Linux to make changes in mysqld.cnf file

  • Open the mysqld.cnf file in any text editor. You can also use vim editor to open the file: sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
  • Search for the key_buffer_size option and update its value to 1024M
  • Search for the innodb_flush_log_at_trx_commit option and update its value to 2


(Note: if the innodb_flush_log_at_trx_commit option is not there in the file, please add this entry and assign the value to it).


  • After making the changes mentioned above, you need to restart the MySQL service: sudo service mysql restart



Steps to follow on Windows to make changes in mysqld.cnf file


  • On Windows, the file my.cnf might be called my.ini. MySQL looks for it in the following locations (in the order mentioned below):
    • %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini or, %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf
    • %WINDIR%\my.ini, %WINDIR%\my.cnf
    • C:\my.ini, C:\my.cnf
    • INSTALLDIR\my.ini, INSTALLDIR\my.cnf


  • Open my.cnf or, my.inifile and, under [mysqld] section,
    • Search for the key_buffer_size option and update its value to 1024M
    • Search for the innodb_flush_log_at_trx_commit option and update its value to 2


(Note: if the innodb_flush_log_at_trx_commit option is not there in the file, please add this entry and assign the value to it).


  • After making above changes, open the command prompt or PowerShell with administrator privilege and stop/start the MySQL service: 

net stop MySQL57

net start MySQL57



In order to know more about the innodb_flush_log_at_trx_commit property, click here.