db optimization?

[FrankR678]

i am on a virtual server and installed vbulletin but then the mysql db kept shutting down. I dont get that much traffic at my board, so far in july the forum has gotten approx 145000 page views...probably about 50 to 60 posts a day i would guess.

the db shutting down got so bad that i had to stop using vb until i can somehow get the mysql to stay up (had to revert back to ubb :( for now...so you know how desperate i am to get figure out a way)

i dont have any info on the server as i am completely ignorant on most of this stuff. I have, based on past posts, gotten some info as noted below:

# This will be passed to all mysql clients
[client]
password =
user =
port = 4384
socket = /vs2/vs2/forumvil/tmp/mysql.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
user =
port = 4384
socket = /vs2/vs2/forumvil/tmp/mysql.sock
tmpdir = /vs2/vs2/forumvil/tmp
basedir = /vs2/vs2/forumvil/usr/local/mysql/

skip-locking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
# Start logging
# log

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=16M

Http processes currently running = 0
Mysql processes currently running = 0

Netstat information summary


+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| Aborted_clients | 0 |
| Aborted_connects | 2 |
| Created_tmp_tables | 70 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 127 |
| Handler_read_first | 59 |
| Handler_read_key | 2896 |
| Handler_read_next | 5599 |
| Handler_read_rnd | 46828 |
| Handler_update | 85 |
| Handler_write | 508 |
| Key_blocks_used | 218 | Approx. 1% of key_buffer in use
| Key_read_requests | 9799 |
| Key_reads | 159 |
| Key_write_requests | 752 |
| Key_writes | 372 |
| Max_used_connections | 2 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 48 | 75% of table_cache in use
| Open_files | 96 |
| Open_streams | 1 |
| Opened_tables | 179 |
| Questions | 1534 |
| Running_threads | 1 |
| Slow_queries | 6 | (execution time > 10 secs)
| Uptime | 47888 | 13 hrs 18 mins 8 secs
+---------------------------+-----------------+


Key Reads/Key Read Requests = 0.016226 (Cache hit = 99.983774%)
Key Writes/Key Write Requests = 0.494681
Temporary Tables Created/second = 0.001 (/hour = 5.262)
Opened Tables/second = 0.004 (/hour = 13.456)
Slow Queries/second = 0.000 (/hour = 0.451)
% of slow queries = 0.391%
Queries/second = 0.032 (/hour = 115.319)



any help would be greatly appreciated.

[eva2000]

without knowing the specifics of your server's hardware it's hard to say.. but i'd recommend

a configuration along the lines of


# This will be passed to all mysql clients
[client]
password =
user =
port = 4384
socket = /vs2/vs2/forumvil/tmp/mysql.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
user =
port = 4384
socket = /vs2/vs2/forumvil/tmp/mysql.sock
tmpdir = /vs2/vs2/forumvil/tmp
basedir = /vs2/vs2/forumvil/usr/local/mysql/

skip-locking
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=3M
set-variable = table_cache=512
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
set-variable = thread_stack=128K

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[safe_mysqld]
open_files_limit=8192

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=16M


restart mysql after changing to take affect