Are your reports running slowly? MySQL performance tuning for large databases

If you find RGP reports starting to run slowly and you have a large database - it may be time to adjust your MySQL settings.

MySQL is wickedly fast... but once certain thresholds are surpassed it can bog down very quickly. You may notice reports that previously took a few seconds to run are suddenly taking 60 seconds to load. That's a tell-tale sign that it is time to tweak your settings.

The following video demonstrates how to modify the my.ini file in your MySQL Server. This file contains the various configuration parameters for MySQL.

IMPORTANT! if you follow the steps in the video, be absolutely sure to follow them EXACTLY. You can easily stop MySQL from launching if you change settings incorrectly.

And be smart.... don't adjust the parameters on a Saturday at 11am... do these steps on a slow weekday :)

Video: http://www.screencast.com/t/tm4RBN0Wvv

Key points in video -

* This must be performed on your MySQL server
* The ini file is typically located at c:\program files (x86)\mysql\mysql server 5.0\my.ini
* You must launch Notepad as administrator (find Notepad, right-mouse click, and Run as Administrator)
* Change the settings as shown in the video
* Restart your MySQL server as shown in the video

The parameters to change... these ALL currently exist in my.ini file and you should change them to the values below.

max_connections=1000
tmp_table_size=205M
innodb_additional_mem_pool_size=15M
innodb_log_buffer_size=7M
innodb_buffer_pool_size=686M
`