performance my.cnf setting

[Tymsah]

Hello,

how can i set my.cnf for MySQL performance.
(2 gb ram Amd 3500+)

Regards,
Tymsah

[sawbuck]

You can use the tuning-primer script to help setup my.cnf parameters.

http://www.day32.com/MySQL/

[Tymsah]

Thank you.

[mr2jzgte]

This is our setup

Dedicated machine with 2 x quad core 1.86ghz processors - 4gb ram
mysql data partition is san attached

So far with 5000 clients - 2000 active databases - 500 databases over 500mb in size, approx 50 2gb+ databases with our testing the server hasn't seen a load over over .9

db server is connected via GB ethernet with failover (not load balanced network connection - active/active bonding in linux is crap) the interconnects to web servers are on their own seperate vlan with dedicated nics in the webservers - read 2 nics in web servers 1 for external access, 1 for internal use (due to multiple machines we don't worry about bonded nics for the webservers)

In addition, the OS is heavily tweaked and mysql is compiled from source

Hope this gives you a few ideas.

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
# get as much as you can in memory
key_buffer = 2048M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 128M
# Try number of CPU's*2 for thread_concurrency
#8 cores = 16 cpus
thread_concurrency = 16
max_connections = 8192
max_user_connections = 8192
#stop mysql from dying due to simple errors
max_connect_errors=999999
#Old password table
old_password=1
# Replication Master Server (default)
# binary logging is required for replication
#we do snap shots of the data partition on the san rather than rely on transaction logging
#log-bin



#defaults from mysql large.cnf below

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M