Optimizing MySQL for a new server setup

[bauhsoj]

We are currently switching over to a newer more powerful server. I am wondering if anyone could give me their input on what would be the optimal configuration of MySQL for the following conditions....

Dual Intel Woodcrest Dual Core
4 GB RAM
NCQ SCSI SAS RAID drive
MySQL 5.0.45
PHP 5.2.3

There are 2 databases on the server which combined total approximately 4 million records across 120 tables with a disk footprint of about 1GB.

Traffic fluctuates from between 3,000-6,500 page views per day. After we finish the upgrade we expect traffic to grow by 1.5 to 2 times that since the server will not be overwhelmed as much.

Here is the current configuration:
[mysqld]
ft_min_word_len=3
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_user_connections=2000
max_connections=2000
interactive_timeout=10
wait_timeout=10
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=8
myisam_sort_buffer_size=64M
#log-binserver-id=1
[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]
[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
root@server [/etc]#

I appreciate any input! :)

[Daemonspyre]

Since you are using MyISAM to keep these tables -- you need to add two lines to your [mysqld] section:

myisam-recover=FORCE,BACKUP
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


Basically, the first line is there in case your server crashes, you will have some kind of safety need underneath you.

To really see how your MySQL server is using your indexes, enable these lines:

log-slow-queries
long_query_time=2
log-queries-not-using-indexes

That should help you figure out what queries are optimized and how you server uses the indexes.

Everything looks OK, but may need more tuning based on tmp_table_size and tmp_table usage.

[bauhsoj]

myisam-recover=FORCE,BACKUP
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


Basically, the first line is there in case your server crashes, you will have some kind of safety need underneath you.

What does the 2nd line tell MySQL to do?

[Daemonspyre]

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/faqs-sql-modes.html

Server SQL modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform.

[bauhsoj]

Will that 2nd line do anything to my existing queries? I read over the docs on it and the STRICT_TRANS_TABLES option is what concerns me the most.

[Daemonspyre]

It shouldn't do anything to the queries, but it does do data verification.

Since you are using MyISAM, you want to make sure that someone is not trying to input bad data into you database.

[bauhsoj]

Do you know if "myisam-recover=FORCE,BACKUP" will cause any performance hits?

[Daemonspyre]

It should not cause any performance hits, as it forces MyISAM tables to auto-recover in the event of a server crash.

Here's more on that line:
http://dev.mysql.com/doc/refman/5.0/en/myisam-start.html