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

tdsii Reviewed by tdsii on 25th Jun 2011. Most recommended mysql Configuration for 8GB RAM 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 Rating: 5

[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.