Home » Categories » Multiple Categories

MySQL Performance Tuning

There are a couple of things that can be done to ensure that your MySQL database is running as fast as it can. Besides making sure that your server is in good shape and that it has the resources necessary for supplying data to your web site/server, you can fine tune it's performance a little by reviewing the following items.

Customizing Antivirus Scanning Rules

MySQL maintains the database data in a data folder It is possible that your antivirus software is scanning the data folders or the temp folders and slowing down the data transfer for the database. To make sure this isn't a problem you must first determine where your data and temp folders are located. MySQl often creates temporary tables to handle more complicated queries. These temp tables are normally created in the Windows temp folder by default. But to be sure you need to look through the "my.ini" file to determine where these folders are located.

The "my.ini" file is usually located under the main installation file for MySQL. Typically "c:\MySQL5\". For older version of MySQL it could also be located in the "system" folder or on the root drive. This is also the order that MySQL looks for the "my.ini" file so try to locate the first "my.ini" file you can find in that order (Install Path first, System path next then the Root path).

Open the "my.ini" file in Notepad or some other simple text editor. Now locate the "datadir=" value. This is the primary storage location path for the database files. Remember this path for later.

Now locate the "tmpdir=" value. It may not exist. If it doesn't then your system is using one of the Windows temp folders. You can add it under the "datadir" value by adding the following line:

tmpdir="C:/MySQL5/Data" 

You can add it anywhere, but it would make sense to add it to the same location as your database folders so that you need only specify a single directory when setting up the antivirus settings to bypass this folder.

Now, using these 2 directories, edit your antivirus program to not include these folders in the normal real-time scan. This will cause your antivirus software to stop scanning these directories in real-time and should speed up the data transfer to your web site/server.

Ensuring database default consistancy

The other item in the 'my.ini" file that is commonly configured improperly is the default temp table format. This is also found in the "my.ini" file that was mentioned above.

Locate the "default-storage-engine=" value in the "my.ini" file. This should be set to:

default-storage-engine=MYISAM

It is common for it to be set to another value. This causes a slow temporary table creation because the format is different thatn the normal tabels that are created with our applications. Changing this value to be consistant should have a significant impact on performance.

Custom Fields
  • Applicable To: All Users
  • Attachments: No
  • Summary: MySQL Performance Tuning
3.75 (4)
Article Rating (4 Votes)
Rate this article
  • Icon PDFExport to PDF
  • Icon MS-WordExport to MS Word
Attachments Attachments
There are no attachments for this article.
Comments Comments
There are no comments for this article. Be the first to post a comment.
Related Articles
Everything You Need to Configure Email in Events and Room Reserve
Viewed 12788 times since Tue, Apr 3, 2012
Server Move
Viewed 13594 times since Wed, Sep 28, 2011
Room Reserve - Reservation Limits
Viewed 3904 times since Wed, Sep 28, 2011
Upgrading MySQL Database from 3 to 5
Viewed 5646 times since Tue, Sep 6, 2011
How can patrons see what programs they have registered for?
Viewed 8032 times since Thu, Oct 27, 2011
How do I print an attendance sheet for a recurring event?
Viewed 9791 times since Thu, Oct 6, 2011
Events Mobile
Viewed 4184 times since Tue, Feb 5, 2013
What is a Featured Event?
Viewed 4724 times since Thu, Sep 15, 2011
Is There A Way To Track No Shows in Room Reserve?
Viewed 3491 times since Tue, Dec 13, 2011
Before Transferring Your Evanced Site to Your New Server
Viewed 11144 times since Fri, Apr 20, 2012