Forum Optimization

[mat_]

I upgraded my server so I need your help to optimize my.cnf

Server Specs:
Dual Processor Dual Core Intel Woodcrest 5130 (2.00 GHz Cores),
8 GB 667 MHz FB-DIMM Memory (Fully Buffered),
2 x 73 GB SAS HD in Hardware RAID 1

I have 600-900 users online.

my.cnf

# The following options will be passed to all MySQL clients

[client]

#password	   = you can put your password here or use -p

port			= 3306



# Here follows entries for some specific programs


# The MySQL server

[mysqld]

port			= 3306

skip-locking

key_buffer = 786M

max_allowed_packet = 8M

table_cache = 1024M

sort_buffer_size = 24M

read_buffer_size = 16M

read_rnd_buffer_size = 16M

myisam_sort_buffer_size = 128M

thread_cache_size = 128

query_cache_size= 128M

max_connections = 1500

max_connect_errors = 2

skip-name-resolve

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

skip-networking

# log-bin=mysql-bin

server-id	   = 1


# Uncomment the following if you are using InnoDB tables which i believe your not

#innodb_data_home_dir = /var/db/mysql/

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /var/db/mysql/

#innodb_log_arch_dir = /var/db/mysql/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 256M

#innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 64M

#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


[isamchk]

key_buffer = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M


[myisamchk]

key_buffer = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M


[mysqlhotcopy]

interactive-timeout

Thanks a lot

[bfarber]

Nice, 8GB B) That should go pretty far.

One consideration, of course, is what else is on this server? Is it Apache and MySQL? Anything else (i.e. cpanel, sphinx, etc.)? MySQL only?

Assuming it's a typical Apache/MySQL installation, I'd recommend something like

table_cache = 1024 # this isn't a memory size value - no 'M' required sort_buffer_size = 24M read_buffer_size = 16M read_rnd_buffer_size = 16M join_buffer_size = 8M thread_cache_size = 128 query_cache_size= 256M query_cache_limit = 2M

key_buffer = 2048M

I mean, you'll probably have the best results trying some of this out, seeing how it performs, and then tweaking areas until it's hitting max performance. You have a lot of leg room with 8GB of memory though. You could lower settings here, and increase memory allowed in other areas of the server, for example.

[Lindsey_]

8 Gigabyte of RAM nice. :)
Nice forum btw and it loads real fast :)