Most recommended mysql Configuration for 8GB RAM

[tdsii]

Hello,

like everyone here, i host all my warez forum sites under the same server(around 7) . Everything is fine and smooth until one query takes time and then i have over 100 locked queries.

What is the best mysql configuration for the following server specs:
Phenom II Quad Core
8GB Ram
500GB HD
Centos 64bit
Mysql 5.5

looking forward for your recommendations

 

[Lock Down]

Depends on a lot of variables but you could always start here using the mysql configuration wizard.

 

[tdsii]

well i just want a recommended my.cnf variables some hosting uses.

edit: link provided is only for windows

 

[Lock Down]

Here is one used for 8 gig:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
lower-case-table-name =1
key_buffer = 384M
query_cache_size = 128M
sort_buffer_size = 32M
myisam_sort_buffer_size = 32M
query_cache_limit = 4M
tmp_table_size = 128M
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security
risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

[Cory]

I host forums as well (Not specifically 'warez' related) but they're a resource hog causing MySQL to spike.

I've even tried switching to alternative Web Servers to help with the load.

Apache to LiteSpeed - Works Ok, just a lot scripting issues, but helped with load significantly.

Went back to Apache and tried, Nginx Web Server - It was awesomely fast, but until I've had a lot of load and decided to switch back to Apache and aparently the load has subsided, but I don't know if that was due to making other changes from Apache Configuration, MySQL Configuration or PHP Configuration (Most likely).

Basically, I have a total of 16 GB of RAM, I was using 16 GB RAM and No Swap, I flush the cache and it goes down to like 5 GB of usage, therefore hence why their is no swap needed, just still seems a lot usage on the server.

Currently, Running Apache with lower PHP Configuration with MySQL Configuration and Apache Configuration and runs pretty fast for almost a week.

 

[tdsii]

i dont think i am having issue with apache mainly i am having over 40 locked queries which is killing my server, if you want Cory u can access it and check the reason that will be much appreciated.