In this document we will cover some recommended optimizations which may speed up dramatically the performance and the response time perceived by the user.

The recommendations shown here assume that you installed MySQL following the procedure from in this guide.

WARNING: Although problems in this procedure are very rare, check the MySQL official documentation to know more and verify each of the recommendations shown here. We have put here only recommendations which have appeared to be safe during time, but apply them at your own risk as any loss of data, issue or problem in the edinn Platform related to changes applied here, will not be covered by the edinn basic support.

Make a complete backup copy of all the databases which are inside this MySQL.

Locate the file my.ini, which should (depending on the path that you specified in the MySQL Installer step) be found in the path: C:\Program Files\edinn\edinnM2\Server\MySQL

Make a backup copy of this file. You can copy and paste this file in the same folder and then rename it with the current date (for example my_YYYYMMDD.ini) and leave it in the same folder.

Open, with a text editor, the file my.ini

These are the recommended changes to apply inside this file:

  • In certain occasions, usually after restarting or updating the edinn M2 server, a MySQL error could be shown when the terminals try to login into the system. Add this line to avoid this from happening:

    bind-address = 0.0.0.0
  • In order to avoid that MySQL registers slow queries in disk, comment these lines by adding the # prefix, as shown:

    #slow-query-log=0
    #slow_query_log_file="EU-1-slow.log"
    #long_query_time=10
  • Increase the number of maximum connections:

    max_connections=2000
  • Change these memory use parameters, typing as many megabytes as the RAM Gigabytes of the server executing MySQL. For example, we show below the memory parameters for MySQL running on a server with 16 Gb of RAM:

    key_buffer_size=16M
    read_buffer_size=16M
    read_rnd_buffer_size=16M
    join_buffer_size=16M
    max_allowed_packet=16M
    sort_buffer_size=16M
  • Increase the maximum number of open files:

    open_files_limit=4161
  • This is probably the most important change. The objective of this change is that, most of the time, MySQL does not use the disk for reading, but the RAM memory instead. Type the number of RAM gigabytes that you want to assign to the MySQL process. There is no fixed rule on what should be the number, as the server executing MySQL might be running other applications. The general approach is to check, at a certain moment, the available RAM memory (for example using the Windows Task Administrator) in the server executing MySQL, add the RAM memory used in that moment by the mysqld.exe and substract the number of RAM gigabytes that you are assigning to the following parameter. The result will be the memory that will be available when MySQL will load all data into the memory space what we are assigning with the parameter below, and that value should be approximately no more than 80% of the total available physical memory of the server executing MySQL:

    innodb_buffer_pool_size=12G
  • This parameter must be in accordance with the previous. MySQL recommends this parameter to range from 25% to 100% of the previous. It will consume, from your hard disk, normally 2 times the number of gigabytes indicated in this following parameter. Therefore, if you have enough disk space, type the same value that you have typed in the previous parameter.

    innodb_log_file_size=6G

Save the changes to the my.ini file. Now:

  1. From the Windows menu, the task administrator (menu File, Execute) or a DOS console, type services.msc
  2. Locate and restart the service edinnM2_MySQL. It may take a while to restart as it needs to create the new files on your disk. Try to not interrupt this process nor kill the mysqld.exe process during this boot, or you may corrupt the database.
  3. Once restarted, MySQL should be working correctly.

If you experience any problem, you can recover the my.ini file that you backuped before in this procedure and restart the service again. In the worst case, you might need to uninstall MySQL and reinstall it following this procedure and then restore the databases from the databases files that you backuped before in this procedure.