I need a little help.

I'm trying to optimzie mysqld for this site as much as possible. Here is my current my.cnf. Any improvements?

Server: Dual P3 750mhz, 512 ECC RAM, 18GB SCSI.
WebServer: Apache 1.3.20 with PHP 4 (mod_php) persistant connection enabled.
Database: vBulletin, approx size when dumped 200mb.
Site: WebHostingTalk.com


[mysqld]
port = 1079
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=128M
set-variable = max_allowed_packet=4M
set-variable = table_cache=512
set-variable = sort_buffer=5048K
set-variable = net_buffer_length=32K
set-variable = myisam_sort_buffer_size=64M
log-bin
server-id = 1


And here is the extend status, this is a snapshot at 7PM New York time (pretty much peak traffic).


+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 7328892 |
| Bytes_sent | 105906351 |
| Connections | 412 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 1092 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 346 |
| Handler_read_first | 2692 |
| Handler_read_key | 331144 |
| Handler_read_next | 802740 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 312140 |
| Handler_read_rnd_next | 14065950 |
| Handler_update | 4304 |
| Handler_write | 43651 |
| Key_blocks_used | 4809 |
| Key_read_requests | 1443984 |
| Key_reads | 4795 |
| Key_write_requests | 2021 |
| Key_writes | 1895 |
| Max_used_connections | 41 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 43 |
| Open_files | 72 |
| Open_streams | 0 |
| Opened_tables | 49 |
| Questions | 44741 |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 6787 |
| Select_range_check | 0 |
| Select_scan | 7134 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 7014 |
| Sort_rows | 320952 |
| Sort_scan | 4675 |
| Table_locks_immediate | 50884 |
| Table_locks_waited | 16 |
| Threads_cached | 0 |
| Threads_created | 411 |
| Threads_connected | 23 |
| Threads_running | 1 |
| Uptime | 4104 |
+--------------------------+-----------+


Also taken at same time.


7:04pm up 1:11, 1 user, load average: 0.13, 0.13, 0.10
109 processes: 108 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 1.0% user, 2.0% system, 0.0% nice, 95.0% idle
CPU1 states: 0.0% user, 1.0% system, 0.0% nice, 98.0% idle
Mem: 517032K av, 256500K used, 260532K free, 80800K shrd, 21056K buff
Swap: 265032K av, 0K used, 265032K free 157316K cached

1. post your extended-status output and latest top memory and cpu usage during peak time

2. remove from my.cnf

log-bin
server-id = 1

since logging slows down the server unless you have mysql replication in place (not that i know much about that yet :o )

3. path to mysql.sock socket = /tmp/mysql.sock might want to move it if you have cronjobs which might delete mysql.sock and bring down your server

4. what's your max_connections used (shown in extended-status output) ?

might want to change my.cnf to with the appropriate path to mysql.sock on your server i.e. /tmp/mysql.sock


[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = join_buffer=4M
set-variable = record_buffer=4M
set-variable = sort_buffer=6M
set-variable = table_cache=1024
set-variable = myisam_sort_buffer_size=32M
set-variable = thread_cache_size=128

then restart mysql and monitor your mysql extended-status and top cpu/memory usage

useful articles

http://i4net.tv/marticle/get.php?action=getarticle&articleid=3

http://i4net.tv/marticle/get.php?action=getarticle&articleid=8

http://i4net.tv/marticle/get.php?action=getarticle&articleid=4