My Performance & Availability

(Some of this is a more focused rehash of info on the MySQL site and some is original. DKMc 5/23/05)
MySQL provides a configuration file called my.cnf for setting runtime parameters.

MySQL default parameters are very small so it can run on almost any box as an additional service without slowing things down.
MySQL provides the following files as templates for creating my.cnf in /usr/share/mysql:


• my-huge.cnf
• my-large.cnf
• my-medium.cnf
• my-small.cnf

The names should be pretty self explanatory.  If you have a lot of hits on a popular webserver, use the huge cnf file.
Afterwords you may want to set some additional values.  max connections and max_user_connections are very important for high availabilty!  I got burned on this one with www.pbsauctions.com

Format of the file:

Group Headers:
[client]
mysqld]
[mysqldump]
[mysql]
[isamchk]
[myisamchk]
[mysqlhotcopy]

After the group headers come parameters setting appropriate for the group.

Many of the optimal parameter values will depend a lot on the load you put on the server, as well as the storage engines(s) you use. But, here are some generic guidelines to optimize performance regardless of which storage engine you are using. We will take a look at storage engine specific tuning options in a future article so please, continue to watch this space.

Set max_connections to the number of concurrent connections you need. The default value is only 100 connections, which is very small.  Ex:

[mysqld]
set-variable = max_connections=500

Set table_cache to match the number of your open tables and concurrent connections. Watch the open_tables value and if it is growing quickly you will need to increase its size. Ex:

[mysqld]
set-variable = table_cache=1024

Note: The 2 previous parameters may require a lot of open files. 20+max_connections+table_cache*2 is a good estimate for what you need. MySQL on Linux has an open_file_limit option to set this limit.

If you have complex queries sort_buffer_size and tmp_table_size are likely to be very important. Values will depend on the query complexity and available resources, but 4Mb and 32Mb, respectively are recommended starting points.

Note: These are "per connection" values, among read_buffer_size, read_rnd_buffer_size and some others, meaning that this value might be needed for each connection. So, consider your load and available resource when setting these parameters. For example sort_buffer_size is allocated only if MySQL needs to do a sort. Note: be careful not to run out of memory.

If you have many connects established (i.e. a web site without persistent connections) you might improve performance by setting thread_cache to a non-zero value. 16 is good value to start with. Increase the value until your threads_created do not grow very quickly.

Backup

It is essential that you regularly put a backup of the database somewhere else!
Locate the backup in a seperate place both server wise and optimally geographically! 
(Buildings do burn on occasion.  and now we have terrorist IEDs - or worse EMP devices - to worry about.)

Though MySQL is based on file sytem files that you can simpy copy, my recommendation is to use the utility function 'mysqldump'.  The reason being you can be assured the backup generated is complete, whereas a file copy may yield a partially updated file.

The following example backs up a specific database (the metasite):
 mysqldump --opt --user='some_ol_user' --password='secret_password' metasite > mtback

If you leave off the db parameter, all databases are dumped.

You could also pipe this to gzip to compress it, or do that as a seperate step.

After that: ftp the file to a remote location.