Optimize MySQL (4.1.12)

Take a look of my.cnf

[iProgram]

I plan to optimize my server (Apache\PHP\MySQL) before move some php/db sites to this new server. I found that mysql is the most important factor.

I have 3G ram and but I assume there is only 2G ram (for whole server).

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
log-bin
old_passwords=1

max_allowed_packet = 16M

max_connections = 500
max_connect_errors = 10

wait_timeout=100
connect_timeout = 10
interactive_timeout=100

query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

table_cache = 1024
thread_concurrency=2

key_buffer=256M
join_buffer_size = 1M
record_buffer=1M
read_buffer_size = 2M
sort_buffer_size = 3M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size=64M

#thread_cache_size = 256
thread_cache_size = 128

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

Take a look of this cnf and I want to hear your advice

[webdoctor]

Take a look of this cnf and I want to hear your advice

What kinds of queries are you going to be running against the DB?

I would suggest checking the basic stuff first before you worry about my.cnf - are you using appropriate indices and the right engine (e.g. MyISAM vs InnoDB)?

Other questions you should consider: What kind of hardware does your server have? Are you using binary logging? Are your log files stored on a different physical drive to the database files? How you planning to back up?

In any case, MySQL ships with preconfigured my.cnf files - for small, medium and large systems - why don't you simply take the medium as a starting point?

[iProgram]

What kinds of queries are you going to be running against the DB?

SELECT - 90%
INSERT/UPDATE/DELETE - 10%;

I would suggest checking the basic stuff first before you worry about my.cnf - are you using appropriate indices and the right engine (e.g. MyISAM vs InnoDB)?

I use MyISAM but the control panel Plesk requires Inno, so I have to remove "skip-innodb" from my.cnf.

Other questions you should consider: What kind of hardware does your server have?

Actually that's why I ordered this new server, to replace my old 1G RAM/IDE/Celeron box. Now it's Dual Xeon 3.0 with 3GB RAM.

Are you using binary logging? Are your log files stored on a different physical drive to the database files? How you planning to back up?

Yes, log-bin is on. I have only one drive and I use mysqldump to perform weekly backup.

In any case, MySQL ships with preconfigured my.cnf files - for small, medium and large systems - why don't you simply take the medium as a starting point?

Becuase I am sure the the my.medium.cnf is not suit for my server.

[webdoctor]

Becuase I am sure the the my.medium.cnf is not suit for my server.

Why? What makes you think it's my.cnf that's wrong, not the database schema?

I found that mysql is the most important factor.

How did you deduce this?

Can you tell us a little more about the type of queries?

Are you using a standard product (e.g. commercial forum software) or a custom application?

FWIW (and I don't mean to be critical) badly written queries (or a badly designed db) will bring even the fastest server to its knees...

Have you tried MySQL's slow query log [dev.mysql.com] to see what queries are taking the time?

I assume you're pretty unhappy with the performance to upgraded your box.

If you're so convinced it's MySQL at fault, why not dedicate the new box to MySQL, and leave the Apache/PHP stuff on the original box?