Changing My.cnf Values For My Server

[SecondSight_merged]

Hello !

I'm trying to learn how to configure a server.

I don't understand very well max_user_connections and max_connections :
- What are they for exactly ?
- What is a user exactly ?

In my my.cnf file, I see :
max_user_connections=1000
max_connections=2000

Since my board is a slow at the moment (too many users online) I was advised to change it to :
max_user_connections=50
max_connections=850

The difference seems very large to me...

What is special with my board it that :
- At the moment, I'm having about 800/900 users online.
- Many files are being downloaded.
- Sphinx has been installed as the search engine.
- xcache has been installed.
- I have a Dual Xeon 2.4 Mhz with 4 Giga Ram.

I put below my my.cnf and after the =>, in bold characters the values I was advised to use, and also in bold the values added :

skip-innodb => was removed ?
back_log = 75
max_user_connections=1000 =>50
max_connections=2000 =>850
max_heap_table_size = 64M
max_connect_errors = 10
max_allowed_packet=1M =>64
key_buffer_size=768M =>512
sort_buffer_size=8M =>2M
join_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=4M =>524288
bulk_insert_buffer_size = 8M
table_cache=2000 => deleted
thread_cache_size=256 =>512
tmp_table_size=48M =>64
myisam_sort_buffer_size=512M =>64M
query_cache_size=144M
query_cache_limit=6M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
interactive_timeout=60 => deleted
wait_timeout=100 => 60
connect_timeout = 10

My understanding of all this is not very good. It seems to me the advised values are meant to make the server work less ? Aren't my members going to receive errors messages then ?
Do you think this my.cnf changes will make things better for my server ?

Also, I'm going to change server :
- I now have a Dual Xeon 2.4 Mhz with 4 Giga Ram.
- I intend to use this server :
Dual Processor Quad Core Xeon 5345 - 2.33GHz
Second Processor Quad Core Xeon 5345 - 2.33GHz
Ram 6 GB
2 Hard Drive 73GB SCSI RAID 1
Will that be enought in your opinion, for about 1000/1200 users online ?

I can't rent two servers because what was suggested to me is much too expensive for me.

Thank you for your help ! :)

[SecondSight_merged]

With the configuration I have at the moment (I've not applied the changes mentionned above), I have a server load of 8 (average, sometimes it's 10, sometimes it's 4) with 800 users online.
But at very few moments the load rises high (about 40). Most of the time, it goes down, but sometimes it doesn't, and it ends with an IPS Driver error (at this moment memory used and swap figures are high => 89% and 64%).
What can be the reason for this ?

Thank you ! :)

[bfcrew]

Don't use Apache :)
No matter how high your hardware is, it will blow your server load out if you have a really busy forum.

Read here

http://www.ipsbeyond.com/forums/index.php?showtopic=26404

That is the best my.cnf settings for a server with 4GB RAM

[bfarber]

Just one question - who recommended the values? Was it Blake? If so, I wouldn't second guess him, myself, since he's being configuring and managing servers much longer than myself.

[SecondSight_merged]

I read what you said about Apache and LiteSpeed. That's interesting. I will have a look at it.

I made a top :

 11:54:33  up 15 days, 11:32,  1 user,  load average: 20.88, 20.85, 22.96

 590 processes: 587 sleeping, 2 running, 1 zombie, 0 stopped

 CPU states:  cpu	user	nice  system	irq  softirq  iowait	idle

			total   55.3%	0.0%	9.9%   0.2%	 0.4%   26.9%	7.1%

			cpu00   59.5%	0.0%	9.6%   0.1%	 1.7%   21.4%	7.4%

			cpu01   50.2%	0.0%   13.1%   0.0%	 0.0%   26.3%   10.2%

			cpu02   62.1%	0.0%	7.6%   0.0%	 0.0%   24.9%	5.2%

			cpu03   49.4%	0.0%	9.2%   0.7%	 0.0%   35.0%	5.4%

 Mem:  4050936k av, 3717828k used,  333108k free,	   0k shrd,   20936k buff

					2797552k actv,  573632k in_d,   65296k in_c

 Swap: 2048276k av,  561044k used, 1487232k free				 2103916k cached


   PID USER	 PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND

 18451 nobody	25   0 19340  15M 12060 R	16.6  0.4  34:03   2 httpd

 13314 nobody	16   0 27992  26M 20168 S	 3.2  0.6   2:53   0 httpd

 23124 mysql	 15   0  374M 263M  2216 D	 2.6  6.6   0:07   2 mysqld

 13010 nobody	15   0 25212  23M 18300 S	 2.5  0.6   3:05   0 httpd

 13288 nobody	15   0 25444  24M 18408 S	 2.2  0.6   3:14   2 httpd

 26254 nobody	15   0 19644  18M 12380 S	 2.0  0.4   0:40   1 httpd

 27751 nobody	15   0 16868  15M  9488 S	 1.9  0.3   0:15   0 httpd

 27874 nobody	15   0 17532  16M 10152 S	 1.6  0.4   0:08   1 httpd

 13424 nobody	16   0 26836  25M 17692 S	 1.5  0.6   2:54   0 httpd

 28222 nobody	15   0 14880  13M  8488 S	 1.4  0.3   0:03   2 httpd

 13693 mysql	 15   0  374M 263M  2216 S	 1.2  6.6   0:18   3 mysqld

 27753 nobody	15   0 18732  17M 11500 S	 1.2  0.4   0:13   2 httpd

 13017 nobody	15   0 24784  23M 16664 S	 1.0  0.5   2:58   2 httpd

 27738 nobody	15   0 17748  16M 10388 S	 1.0  0.4   0:15   1 httpd

What do you think of it ?
Are the figures in this line normal ?
18451 nobody 25 0 19340 15M 12060 R 16.6 0.4 34:03 2 httpd
It doesn't disappear and remains visible.

Thank you ! :)

[SecondSight_merged]

Just one question - who recommended the values? Was it Blake? If so, I wouldn't second guess him, myself, since he's being configuring and managing servers much longer than myself.

Hello !

No, it wasn't Blake but I'm getting more and more interested in server configuration.
These values were given to me by another board's members who said they should increase performance.
I would like to understand how these values could do it, and if they could really.

[bfarber]

The following is general recommendations, mostly based on the expected 6GB setup.

skip-innodb => was removed ?
I'd remove this as well - there are a couple of tables that can be switched to innodb and gain minor performance benefits from

back_log = 75
I don't use this, no idea what this is really off the top of my head

max_user_connections=1000 =>50
I'd lower this to maybe 500, but no lower - this is a dedicated server, if you lower this too much you'll get sql errors "max_user_connections exceeded"

max_connections=2000 =>850
max_connections should be somewhat higher than max_user_connections, though unless you run multiple users/databases it won't matter (you'd always hit the user limit first)

max_heap_table_size = 64M
That should be fine

max_connect_errors = 10
Don't think this would change your performance any

max_allowed_packet=1M =>64
64 or 64M (big difference)? In any event, unless you are actually sending packages larger than 1M (you're probably not) this won't affect anything

key_buffer_size=768M =>512
No....you want your key_buffer_size set to 25-50% of your total available memory. With 6GB, I'd recommend setting it to about 1 GB (1024M) to 1.5GB (1536M). You may want to start lower, and if MySQL seems to be a problem increase it down the road.

sort_buffer_size=8M =>2M
Again, no. With 4-6GB memory I'd set this somewhere between 8M and 12M, but no higher. Be careful setting this TOO high though. On a dedicated mysql server, this can be put to about 10M with just 3GB memory in the box.

join_buffer_size=1M
2M

read_buffer_size=1M
6M

read_rnd_buffer_size=4M =>524288
6M

bulk_insert_buffer_size = 8M
Not really going to make a difference since we don't do many/any bulk inserts

table_cache=2000 => deleted
You should look at your system during peak time, but something like 512 is probably good

thread_cache_size=256 =>512
Sure

tmp_table_size=48M =>64
64M, I assume that's what you/they meant - this should effectively be the same as max_heap_size, as the lower of the two is always used

myisam_sort_buffer_size=512M =>64M
As I recall this is only really used when running things like myisamchk from command line. Someone correct me if I'm wrong - I could be. If so, this won't affect much during normal usage.

query_cache_size=144M
Heck, with 6GB I'd go for 512M

query_cache_limit=6M
6M is higher than normal, but then again with 6GB and 512M sized cache I think you can get away with it

query_cache_type = 1
Yes

query_prealloc_size = 163840
query_alloc_block_size = 32768
I generally don't change these much, so I won't comment on this. According to all docs I've read on this, you should really monitor how your query cache behaves after it has been in use for a while before changing block sizes

interactive_timeout=60 => deleted
wait_timeout=100 => 60
Set both to 60

connect_timeout = 10
Yes

[SecondSight_merged]

Hello ! :)

Thanks a lot for your pieces of advice and explanations.

As for back_log, I read that it "determines the size of the queue that MySQL creates for incoming connections". I found this here : http://www.unix.org.ua/orelly/linux/sql/ch04_06.htm

I was told that a lower figure than the one I have for max-connection (2000) would avoid persistent connections.

Thank you ! :)