Optimize /etc/my.cnf Requests

[grniyce]

I need to know if these settings are appropriate for a high sql usage environment running multiple forums. Please let me know if there are any areas you would recommend changing or tweaking. My load averages are typically 1-4%, however some sites are spiking 40% cpu usage. It would help to know if there are any cache'ing tricks I can use, or other methods which will help in using more memory and less cpu in the environment(s) I am speaking of. Any assistance is much appreciated. Obviously I'm using the latest LSWS.

My server details are:
Centos 5.3 (32 bit)
Dual Xeon Quad Cores 2.66ghz
4gb Ram
10mbps with up to 100mbps burst

[mysqld]
tmpdir=/home/sqltemp
datadir=/var/lib/mysql
local-infile = 0
skip-locking
skip-innodb
skip-bdb
safe-show-database
max_connections = 800
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 1024
record_buffer = 1M
thread_cache_size = 128
wait_timeout = 30
connect_timeout = 10
interactive_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
thread_concurrency = 4
default-storage-engine = MyISAM

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 100M

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

[Elfz]

I personally use the following on my 4GB Ram Server which keeps mysql and everything running fast:

[mysqld]
max_connections = 2500
socket = /var/lib/mysql/mysql.sock
key_buffer = 4096M
table_cache = 36272
open_files_limit = 16344
join_buffer_size = 32M
read_buffer_size = 32M
sort_buffer_size = 32M
tmp_table_size = 1024M
read_rnd_buffer_size = 32M
max_heap_table_size = 512M
myisam_sort_buffer_size = 1024M
thread_cache_size = 1024
thread_cache = 8192
query_cache_type = 1
query_cache_limit = 512M
query_cache_size = 2048M
thread_concurrency = 16
wait_timeout = 3000
connect_timeout = 1200
interactive_timeout = 1200
long_query_time = 1
log-slow-queries = /var/log/mysqlslowqueries.log
max_allowed_packet = 256M

[h0tz]

Thanks Elfz, that was a nice config file.

[Elfz]

Originally Posted by h0tz

Thanks Elfz, that was a nice config file.

np mate glad i could help

[bhanuprasad1981]

my /etc/my.cnf misses a lot of options shown above ? anything wrong ?

[anewday]

^ Nothing to worry about, if it's missing then it's likely to use the default values.

[brrr]

Originally Posted by Elfz

I personally use the following on my 4GB Ram Server which keeps mysql and everything running fast:

[mysqld]
max_connections = 2500
socket = /var/lib/mysql/mysql.sock
key_buffer = 4096M
table_cache = 36272
open_files_limit = 16344
join_buffer_size = 32M
read_buffer_size = 32M
sort_buffer_size = 32M
tmp_table_size = 1024M
read_rnd_buffer_size = 32M
max_heap_table_size = 512M
myisam_sort_buffer_size = 1024M
thread_cache_size = 1024
thread_cache = 8192
query_cache_type = 1
query_cache_limit = 512M
query_cache_size = 2048M
thread_concurrency = 16
wait_timeout = 3000
connect_timeout = 1200
interactive_timeout = 1200
long_query_time = 1
log-slow-queries = /var/log/mysqlslowqueries.log
max_allowed_packet = 256M

Haha. Very funny.

You have a server with 4GB RAM in total, and yet you tell MySQL to allocate:

- 4GB for the key_buffer
- 2GB for the query cache
- 1GB for myisam_sort_buffer_size
+ setting aside huge amounts of RAM for other variables
+ setting hugely long wait and connect timeouts
+ setting hugely high max connections...

Enjoy your server choking up from too many open connections held open for too long, and from swapping itself extensively to disk.

[melinite]

Those settings apply to myisam tables, but who uses myisam these days? Innodb has evolved enough that the performance differences are negligable unless you have 1000+ shared users on same db, innodb supports transactions too.

And yes agreed with brr, WTF kind of configuration is that @Elfz LOL.
Hopefully no one actually tried it.

[grniyce]

can you all comment on how mine looks? the first post?

[brrr]

Lrn2google.

This all has hardly anything to do with LSWS.