Improve My-SQL performance

Problem

If you have many "Waiting for free connection" in your ErrorLog here are some tips to speed up MySQL significantly.

In an average case you can have 50% more devices on the same server without getting "Waiting for free connection" with those changes. However the exact improvements will depend on your specific combination of reports, event rules, accumulators and update rate from the devices.

Fix

Backup and then make the following changes to my.ini (You typically find this file under C:\Program Files\MySQL\MySQL Server 5.5)

innodb_flush_log_at_trx_commit=2

Change innodb_flush_log_at_trx_commit from 1 to 2. This will flush DB changes once per second to disk. This means that you might lose 1 second of data if MySQL crashes, but you get a significantly faster database.

innodb_buffer_pool_size=2G

Set innodb_buffer_pool_size to 2G. If you have less than 4 GB memory on your server dedicated for GpsGate usage, set it to half the memory you have.

After you have done the changes restart MySQL.

1. Open Services under Administrative Tools in the Windows Control Panel.
2. Right click on MySQL and select Restart.