[Help]Need Best MySQL setting

[ifirstdoz]

Hello Guys,
I want to optimize my sql setting for optimum performance.i want you to suggest me a good configuration

My Server Specs

CPU - AMD Athlon II X4 605e, Quad-Core
RAM - 8 GB
HDD - 1 TB
Apache
Centos 5 64BIT
Plesk

What site do run my dedicated server?

Rapidleech
Its a custom script that uses mysql db to store details about users who register.
Its a free public rapidleech service
I won't run any other site on it for now

Suggest me some tweaks for my server . I would appreciate it of this format

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=8M
query_cache_size=256M
query_cache_type=1
max_connections=500
max_user_connections=10
interactive_timeout=20
wait_timeout=20
connect_timeout=6
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
#log-bin
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

Of course modify figures to make it suitable for my server
Thanks in advance

ifirstdoz Reviewed by ifirstdoz on 16th Dec 2011. [Help]Need Best MySQL setting Hello Guys, I want to optimize my sql setting for optimum performance.i want you to suggest me a good configuration My Server Specs What site do run my dedicated server? Suggest me some tweaks for my server . I would appreciate it of this format Rating: 5

[onel0ve]

wget mysqltuner.pl
chmod 755 mysqltuner.pl
./mysqltuner.pl

[ifirstdoz]

@ onel0ve could u be more precise???????????

[vgnheart]

you will face issue for placing both mysql and rapid leech on same disk. Once your site starts getting users, the disk will act busy on up/down of files. Which basically gonna slow down the sql

The command given by onel0ve will help you to get some advices to set values on my.cnf

[ifirstdoz]

THANKS but anyone can give me settings in the format i asked above????

[ifirstdoz]

I have around 2500 members right now..Just in case u need it

[ifirstdoz]

No one?????????

[Lock Down]

The reason no one one is giving you anything is because there is minimal usage of mysql according to what you say. Capture new member registration..

THis is what Mysql suggest for small usage.

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = MySQL

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket        = MySQL
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking
server-id    = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# binary logging format - mixed recommended
#binlog_format=mixed

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\\mysql\\data\\
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\\mysql\\data\\
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout