Description

Additional steps can be taken to optimize the insertion or update of data within a MySQL database. 


Optimize MySQL Database Insert

To speed up data insertion, you need to append ?useServerPrepStmts=false&rewriteBatchedStatements=true to the URL property that you specify in the JDBC Config File. 


e.g. url=jdbc:mysql://localhost:<portNumber>/<dbName>

?useServerPrepStmts=false&rewriteBatchedStatements=true


Optimize 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 


Note: To learn more about the innodb_flush_log_at_trx_commit property, click here. 


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 the 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 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.ini file 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 in the file, please add this entry and assign the value to it.


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

net stop MySQL57

net start MySQL57