MySQLd optimization for system administrators

[DW]

Server optimization of MySQL is key to your ListMail lists running smoothly.  I highly recommend the following articles to get you going:

http://www.databasejournal.com/features/mysql/article.php/1402311
http://www.databasejournal.com/features/mysql/article.php/3367871
http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/

Some recent changes I made to my own server's MySQL settings based on the articles are as follows:

Code: [Select]

# Intended for MySQL 3.23.x
# Place this under the heading [mysqld] in the file /etc/my.cnf

# added due to a warning in /var/log/mysqld.log when starting mysqld, default was 1M
set-variable = innodb_additional_mem_pool_size=2M

# larger than *.MYI, calculate with shell command:
# X=0; for f in `du -k /var/lib/mysql/*/*.MYI | awk '{print $1}' | sed s/k//`; do X=`expr $X + $f`; echo $X kb; done;
# i chose 3x the size to allow for expansion
# http://www.databasejournal.com/features/mysql/article.php/3367871
# this was way too low (8MB) and was the major cause of my slowdowns!
# set to a maximum of 25% total RAM available
set-variable = key_buffer_size=128M

# 1k per 1M ram
# http://www.databasejournal.com/features/mysql/article.php/3367871
# named read_rnd_buffer_size in MySQL 4
# default was way too low
set-variable = record_rnd_buffer=512k

# seems to not matter if it's a bit higher and i needed it
# http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3
# default was 64
set-variable = table_cache=128

# default was 50
# http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3
set-variable = back_log=300

# default was 2MB!
# http://www.databasejournal.com/features/mysql/article.php/10897_1402311_4
set-variable = sort_buffer=16M
These changes resulted in a great performance improvement for all of my hosted clients.

Don't forget to restart MySQL.  Check /var/log/mysqld.log if you have problems.

Regards

[Stefaans]

# 1k per 1M ram
# http://www.databasejournal.com/features/mysql/article.php/3367871
# named read_rnd_buffer_size in MySQL 4
# default was way too low
set-variable = record_rnd_buffer=512k

Minor typo: I think record_rnd_buffer should be read_rnd_buffer_size

[auto]

I just installed MySQL 5.0, any suggestions on improving performance specifically related to sending out emails?

I have a large list of >300k and I send out 2-3 emails per day to this list.

thanks
Tara

[DW]

Tara,

With the MySQL optimizations mentioned in the URLs contained in my original post, along with email server config optimization, you should be able to achieve maximum output from your server.

Regards