Tweaking MySQL 4.1

[yacenty]

Hello all,
could You help me to tweak my mysql server?
I'm using a64 3700+ with 2GB ram and SATA disk,

currently I'm running:
rather big IPB+IPG forum (avg 150users online, peak 250)
smf forum avg 2 users online

few small wordpress with few visits weekly,
small joomla site - avg 50 visits weekly
oscommerce with very small visits - avg 10 daily

my current my.cnf file is:

[mysqld]
default-character-set=latin2
init-connect = "SET NAMES latin2"
language=polish
skip-bdb
#skip-innodb
key_buffer = 192M
#16MB
myisam_sort_buffer_size = 34M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1528
thread_cache_size = 286
tmp_table_size = 64M
max_allowed_packet = 16M
query_cache_limit = 2M
query_cache_size = 256M
#64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
tmpdir=/tmp
#myisam-recover


[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 10M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[client]
default-character-set=latin2

what should I correct to improve speed of my server?

Thanks in advance,
BR
YacentY

 

[ITDevil]

MySQL optimization request

Server details

CPU1: Intel(R) Xeon 2.80GHz ( Dual Core )
CPU2: Intel(R) Xeon 2.80GHz ( Dual Core )
2 GB DDR ECC RAM
160 GB HDD (WD1600BB-98DWA0, ATA DISK drive)

CentOS 5
Apache 1.3.39
PHP 4.4.7
mySQL 4.1.22

uname:

 Linux s1.xxxxxxxx.com 2.6.18-8.1.8.el5 #1 SMP Tue Jul 10 06:50:22 EDT 2007 i686 i686 i386 GNU/Linux

ulimit -aH

core file size          (blocks, -c) 1000000
data seg size           (kbytes, -d) unlimited
max nice                        (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 32767
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 4096
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
max rt priority                 (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 14335
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

mysqlreport

[root@s1 ~]# ./mysqlreport
MySQL 4.1.22-standard    uptime 5 15:53:37      Wed Sep 26 03:58:29 2007

__ Key _________________________________________________________________
Buffer used   113.26M of 128.00M  %Used:  88.49
  Current     128.00M            %Usage: 100.00
Write hit      29.84%
Read hit       99.48%

__ Questions ___________________________________________________________
Total          19.34M    39.5/s
Slow            2.06k     0.0/s  %Total:   0.01  %DMS:   0.01
DMS            14.01M    28.6/s           72.44

__ Table Locks _________________________________________________________
Waited        448.89k     0.9/s  %Total:   2.25
Immediate      19.50M    39.9/s

__ Tables ______________________________________________________________
Open             1024 of 1024    %Cache: 100.00
Opened        156.37k     0.3/s

__ Connections _________________________________________________________
Max used          174 of  500      %Max:  34.80
Total         659.37k     1.3/s

__ Created Temp ________________________________________________________
Disk table     30.84k     0.1/s
Table         135.76k     0.3/s
File              704     0.0/s

my.cnf

[mysqld]
datadir=/var/lib/mysql
tmpdir=/home/tmpmysql
skip-locking
skip-innodb
#skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=128M
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M  
thread_concurrency=8
myisam_sort_buffer_size=64M
server-id=1
collation-server=latin1_general_ci

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

any tips, as the mysql start eating ram and cpu after running for 24hrs.
Thank You!