my.cnf advice needed

MySQL & MariaDB Other MySQL® Questions
1

Hello,
I’ve already made some changes based on other threads I’ve found here (increasing interactive_timeout from 10 to 600 and wait_timeout from 10 to 30 sec), however I’m looking for any additional suggestions for improving performance. I’m already tracking down some of the slow queries from the log.

This server is dedicated for MySQL only. The servers connecting to them are mainly connecting via ColdFusion 9, but some PHP.

Dell PowerEdge 185
Red Hat Enterprise Linux ES release 3 (Taroon)
MySQL Version: 5.0.22-standard-log
4GB of RAM
Dual 2.8GHz/2MB Cache, Xeon, 800MHz Front Side Bus
36GB SCSI 15K HD

my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=250
set-variable = interactive_timeout=600
set-variable = wait_timeout=30
set-variable = max_allowed_packet=1M
set-variable = max_connect_errors=999999

skip-innodb
skip-bdb
skip-locking
key_buffer_size = 1024M
max_allowed_packet = 2M
table_cache = 768
sort_buffer_size = 8M
record_buffer=1M
read_buffer_size = 2M
#read_rnd_buffer_size should be 1M per GB of RAM
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_type=1
query_cache_size= 64M
query_cache_limit=2M
thread_concurrency = 4
#tmp_table_size should equal max_heap_table_size as mySQL uses the smaller of the two
tmp_table_size = 1024MB
max_heap_table_size = 1024MB
join_buffer_size = 2M

log-slow-queries
long_query_time=1
log-error= /var/lib/mysql/usint02-slow.log
#log-queries-not-using-indexes

[mysqldump]
quick
max_allowed_packet = 16M

[mysql.server]
user=mysql
#basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

I’d be happy to provide any additional information if it would be helpful. We are using phpMyAdmin, so I have provide reports from it as well.

Thank you,
Jeff

2

5.0.22 is a steaming pile of… your first action should be to upgrade to the latest 5.0.x series. And why are you disabling InnoDB?

3

Thanks for your reply xaprb. Obviously, I’m not a DBA and have been learning things on my own as I go. This system has been in place for several years, I wasn’t the installer, and hasn’t been updated. I appreciate your assistance.

At the risk of sounding naive, shouldn’t I upgrade to the latest version (5.1.51) instead of 5.0.x? Also, since I’ve never performed one, how complicated is the upgrade process?

Regarding InnoDB, our tables have been MyISAM since they were set up originally (as the default I believe). I believe that skip-innodb came from when we had MySQL running on the web server and had limited RAM. Should I consider changing to InnoDB? I read this article ( http://www.mysqlperformanceblog.com/2009/01/12/should-you-mo ve-from-myisam-to-innodb/). All our tables together are 133MB and use mysqldump to make nightly backups. I run a website that displays information, there is some user interactivity, but we’re mostly doing selects, some of which are pretty complex; very few inserts and deletes except for a couple tables.

One thing is painfully clear, I think I need to pick up a copy of your book.

Thanks again,
Jeff