MySQL where to start performance tuning

Recently I needed to setup an instance of MySQL on CentOS and I found myself once again wonder where to start tweaking to get initial performance of the server beyond defaults. I'm a firm believer that early optimization can lead to problems but I figured there must be some basic common sense things to do with MySQL that would tune it to the environment where it lives. My initial search turned up this page:

http://library.linode.com/databases/mysql/centos-5

I often start with documentation on the linode.com site since I use there VPS services. Rather then just blindly make the changes recommended for a "low" memory environment I decided to do a bit more reading in the MySQL documentation. I was trying to discover what (if any) difference there was between "key_buffer" (shown on this linode page) and "key_buffer_size" (as outlined in the MySQL documentation) I never did find an adequate explanation although one post I found suggested that key_buffer_size was depreciated. (apparently they both will work) In any event, I didn't dig any further because in digging around I found a script that seems to be quite helpful with my original quest to tune MySQL to my server environment.

You can locate the page here:

http://www.day32.com/MySQL/

There are several scripts on this page that may be useful for future stuff but the script MySQL Performance Tuning Primer Script located at the top of the page is the one I started with. I downloaded it using:

wget http://www.day32.com/MySQL/tuning-primer.sh

I reviewed the script (just stating the obvious and if you don't know why then you shouldn't ever download anything) before marking it as execute and running it. This script ran without any problems on CentOS. It dumps a bunch of recommendations to the console and upon the initial review it the recommendations appear to be solid.

Of course, I didn't stop there. I wanted to run the script on a VPS (linode.com) where I have another instance of MySQL. This server is setup with Ubuntu 10.04 (Lucid) and unfortunately the script did not run immediately. I received a nice error message from the script:

"Error: Command line calculator 'bc' not found!"

The script has a dependency on BC, a free command line calculator. I installed this with:

sudo apt-get install bc

to provide the necessary dependency and this got things going on Ubuntu for me.

Overall the script will be most useful if you have been running your MySQL instance for a while and have some useful data for it to review.

REFERENCES
MySQL Scripts by Matthew Montgomery
Reference to key_buffer_size being deprecated
MySQL documentation

Setting LDAP parameter to invalid value caused strange error

I was working on a web based application using C# and ran into a bit of a strange error.  I'm using an LDAP to try and set the option "Require user to change password at next login"  for an Active Directory account.  A small typo resulted in a strange error that lead me to waste some time today.  Thought I'd share to hopefully prevent others from having the same issue.  The error I received was:


A device attached to the system is not functioning. (Exception from HRESULT: 0x8007001F)

The snippet of relevant code:

    public bool RequireUserToChangePwd(bool force)
    {
        bool success = false;

        try
        {
            DirectoryEntry user = loadSpecificEntry();
                
            if (user != null)
            {
                if (force)
                    user.Properties["pwdLastSet"].Value = 0;
                else
                    user.Properties["pwdLastSet"].Value = 1;
                        
                user.CommitChanges();
                user.Close();
                success = true;
            }
        }
        catch (Exception ex)
        {
            tossFormattedExceptionMessage("Unhandled error requiring force password change",ex);
        }

        return success;
    }

The exception was happening on the call to CommitChanges();   It was happening because the "pwdLastSet" property only has two valid values  0  and  -1  so when I called this method with force = false the exception was being tossed.   Needless to say the error message "A device attached to the system is not functioning" is a bit misleading.   Changing the code to supply the proper value of -1 resolved the issue.  I imagine this error could potentially show up in when using invalid values for other properties being modified using LDAP.  If you're reading this, hopefully I've saved you some time.

References
https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.adsi.general/qUfqhn0qb6M