Note: for an updated version of this and other articles, we recommend you to check our knowledge base here
GUIDE: Make MySQL run faster!
Before you use the tips in this guide make sure Patch.v4 and WatchDog.Client (if installed) are up to date (green)
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.
Backup and then make the following changes to my.ini (You typically find this file under C:\Program Files\MySQL\MySQL Server 5.5)
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.
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.
Don't forget to update Patch.v4 and WatchDog.Client (if installed)! If you don't the performance improvement will not be realized in the same way!