Author Topic: MySQLd optimization for system administrators  (Read 7601 times)

DW

  • Administrator
  • Posts: 3782
    • View Profile
    • http://listmailpro.com
MySQLd optimization for system administrators
« on: October 11, 2005, 07:13:47 am »
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
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

Stefaans

  • Posts: 11
    • View Profile
    • http://www.anno.com
Re: MySQLd optimization for system administrators
« Reply #1 on: August 13, 2006, 01:43:22 pm »
Quote from: "DW"
# 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 ;)
Stephen at ANNO Internet

auto

  • Posts: 49
    • View Profile
MySQLd optimization for system administrators
« Reply #2 on: September 12, 2006, 03:16:21 pm »
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
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

DW

  • Administrator
  • Posts: 3782
    • View Profile
    • http://listmailpro.com
MySQLd optimization for system administrators
« Reply #3 on: September 12, 2006, 04:03:16 pm »
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
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting