I have a 4 GB database (disk usage on var/lib/mysql/dbname), all MyISAM tables.
I purchased a new Nehalem server with 12 GB RAM, 300GB HD, WHM / cPanel, MySQL 5.1.48 server. This server will handle both Apache / Perl scripts and MySQL but I only need 2-6 GB RAM for Apache and Perl, the rest could be allocated to MySQL. The ideal solution would be to setup the MySQL server to mainly use RAM to eliminate too many reads and writes to the HD and reduce io wait.

The question is, if I want to allocate 6 GB RAM to MySQL what variables to use in my.cnf to reduce disk load as much as possible and use RAM instead?

This environment works on another server with 4GB RAM only and slower / fewer processors. I will move everything to this new server.

Here is what I see when I run mysqltuner on the OLD server:

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.48
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 528)
[--] Data in InnoDB tables: 48K (Tables: 3)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 13h 56m 18s (40M q [83.478 qps], 1M conn, TX: 1B, RX: 4B)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 442.0M global + 12.3M per thread (151 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.2G (58% of installed RAM)
[OK] Slow queries: 0% (1K/40M)
[!!] Highest connection usage: 100% (152/151)
[OK] Key buffer size / total MyISAM indexes: 384.0M/2.2G
[OK] Key buffer hit rate: 100.0% (9B cached / 1M reads)
[OK] Query cache efficiency: 66.8% (21M cached / 32M selects)
[!!] Query cache prunes per day: 63758
[OK] Sorts requiring temporary tables: 0% (227 temp sorts / 2M sorts)
[OK] Temporary tables created on disk: 17% (74K on disk / 431K total)
[OK] Thread cache hit rate: 99% (4K created / 1M connections)
[!!] Table cache hit rate: 1% (512 open / 29K opened)
[OK] Open file limit used: 80% (953/1K)
[OK] Table locks acquired immediately: 98% (18M immediate / 18M locks)
[OK] InnoDB data size / buffer pool: 48.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 32M)
table_cache (> 512)


I would like to setup the new server very well (optimize my.cnf) before I move the data. Any help on my.cnf setup is much appreciated.

Thank you.