This is another section that is broader than one would first imagine. There’s a reason that many large organizations employ dedicated database administrators. That said, this doesn’t prevent the average sysadmin from making some changes to enhance performance on their database.
The easiest way to start on this is to utilize a script to automatically check your configuration options and make suggestions based on status variables MySQL sets. I’ve had good luck with a script called mysqltuner.pl. You can visit the project page at GitHub here: https://github.com/rackerhacker/MySQLTuner-perl. This script can be conveniently pulled with: wget mysqltuner.pl
In order to ensure accurate suggestions, it’s best to have MySQL running for at least 24 hours of normal usage prior to running the script. Once you run the script, it will output several information sections based on a few criteria – general statistics, storage engine statistics, security recommendations, and performance metrics. At the bottom, there will be a list of recommendations.
Please note that this script is far from flawless – I would advise that you visit the MySQL documentation site (http://dev.mysql.com/doc/refman/5.1/en/) to evaluate the changes it suggests before blindly making changes.
It’s also a very good idea to ensure that your tables are using the appropriate storage engine for the kind of data and access patterns you have. The two most common storage engine types are MyISAM and InnoDB. InnoDB can have more overhead associated with it but offers a good set of extra features that MyISAM does not. It’s difficult to impossible to make a blanket recommendation for what storage engine is right for you. You’ll need to take a look at how your application accesses the database to determine what route you should take.