Optimize /etc/my.cnf Requests

G

grniyce

Well-Known Member
#1
#1
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

Code: 
[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
 
E

Elfz

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

Code: 
[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
 
Last edited:
B

brrr

Well-Known Member
#7
#7
Elfz said:
I personally use the following on my 4GB Ram Server which keeps mysql and everything running fast:

Code: 
[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
Click to expand...
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.
 
M

melinite

Member
#8
#8
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.;)
 
G

grniyce

Well-Known Member
#12
#12
brrr said:
Lrn2google.

This all has hardly anything to do with LSWS.
Click to expand...
You are entirely incorrect, as many people (in fact ALL I know) switch to LSWS to ensure the stability of their forums which are high in SQL queries and usage.

For this reason, the ability to effectively quantify the settings which are most compatible for the hardware, software, and hosting environment in combination with LSWS DOES MAKE it a valuable resource for all users.

Someone with a blank "my.cnf" that runs a server hosting 20 high usage vBulletin sites will NOT experience the true capabilities of LSWS unless they understand that their MySQL configuration should also be optimized.

I would suggest you use your knowledge to help others rather than make pointless posts that berate the community and posts which can and DO help a large majority of unknowing clients.

Regards,

Ant
 
You must log in or register to reply here.
Top