Server Optimization

mat_ · September 17, 2007

Hello,

When I have +320 online users (in 5 minutes) then the server loads goes more to 8 and then I have IPS Driver Error. In cache I see error: Too many connections. Currently I have no mods on my board.

Statistics:
1.591.956 posts
92.813 registered members

Here are my dedicated server specifications:
# Dual Processor Dual Core Intel Woodcrest 5130
# 4 Total 2.00 GHz Cores
# Dual 1333 MHz Front-side Bus (FSB)
# 4 MB Shared L2 Cache
# 2 GB 667 MHz FB-DIMM Memory (Fully Buffered)
# 2 x 73 GB SAS HD in Hardware RAID 1
# 100 Mbps Uplink Port
with CentOS 4.x

Do you think I should upgrade the server? More RAM? Or is enough just to optimize my.cnf and httpd.conf and what changes can I do?

Here is my my.cnf file:

# 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 = 256M

max_allowed_packet = 1M

table_cache = 256

sort_buffer_size = 4M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 128M

thread_cache_size = 8

query_cache_size= 16M

max_connections = 1000

max_connect_errors = 5

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

mat_ · September 17, 2007

More info:

Now with 130 users online:

top - 20:38:41 up  2:02,  0 users,  load average: 1.12, 8.57, 33.74

Tasks: 247 total,   1 running, 245 sleeping,   0 stopped,   1 zombie

Cpu(s):  4.8% us,  8.8% sy,  0.0% ni, 35.1% id, 51.2% wa,  0.0% hi,  0.0% si

Mem:   2074640k total,  1279628k used,   795012k free,	14872k buffers

Swap:  4192956k total,   695432k used,  3497524k free,   525900k cached


  PID USER	  PR  NI  VIRT  RES  SHR S %CPU %MEM	TIME+  COMMAND			

16689 nobody	16   0 36460 7868 5048 S   20  0.4   0:02.81 httpd			  

 7602 nobody	16   0 43232  13m 4368 S   18  0.7   0:04.84 httpd			  

 3154 mysql	 15   0  601m 144m 2108 S   10  7.1   8:08.17 mysqld			 

 9117 nobody	15   0 36744 8796 5728 S	2  0.4   0:02.60 httpd			  

 9125 nobody	16   0	 0	0	0 Z	2  0.0   0:03.45 httpd	 

16727 nobody	15   0 36464 7528 4684 S	2  0.4   0:01.29 httpd			  

18872 nobody	15   0 36452 6740 3596 S	2  0.3   0:00.21 httpd			  

18875 nobody	15   0  2156  956  672 R	2  0.0   0:00.01 top				

	1 root	  16   0  1996  384  352 S	0  0.0   0:02.92 init			   

	2 root	  RT   0	 0	0	0 S	0  0.0   0:00.00 migration/0		

	3 root	  34  19	 0	0	0 S	0  0.0   0:00.00 ksoftirqd/0		

	4 root	  RT   0	 0	0	0 S	0  0.0   0:00.00 migration/1		

	5 root	  34  19	 0	0	0 S	0  0.0   0:00.00 ksoftirqd/1		

	6 root	  RT   0	 0	0	0 S	0  0.0   0:00.00 migration/2		

	7 root	  34  19	 0	0	0 S	0  0.0   0:00.00 ksoftirqd/2		

	8 root	  RT   0	 0	0	0 S	0  0.0   0:00.00 migration/3		

	9 root	  34  19	 0	0	0 S	0  0.0   0:00.00 ksoftirqd/3		

   10 root	   5 -10	 0	0	0 S	0  0.0   0:00.00 events/0		   

   11 root	   5 -10	 0	0	0 S	0  0.0   0:00.00 events/1		   

   12 root	   5 -10	 0	0	0 S	0  0.0   0:00.00 events/2		   

   13 root	   5 -10	 0	0	0 S	0  0.0   0:00.00 events/3		   

   14 root	   5 -10	 0	0	0 S	0  0.0   0:00.00 khelper			

   15 root	  15 -10	 0	0	0 S	0  0.0   0:00.00 kacpid			 

   67 root	   5 -10	 0	0	0 S	0  0.0   0:00.00 kblockd/0		  

   68 root	   5 -10	 0	0	0 S	0  0.0   0:00.00 kblockd/1		  

   69 root	   5 -10	 0	0	0 S	0  0.0   0:00.00 kblockd/2		  

   70 root	   5 -10	 0	0	0 S	0  0.0   0:00.01 kblockd/3		  

   71 root	  15   0	 0	0	0 S	0  0.0   0:00.00 khubd			  

   91 root	   6 -10	 0	0	0 S	0  0.0   0:00.00 aio/0			  

   92 root	   6 -10	 0	0	0 S	0  0.0   0:00.00 aio/1			  

   93 root	   6 -10	 0	0	0 S	0  0.0   0:00.00 aio/2			  

   94 root	   6 -10	 0	0	0 S	0  0.0   0:00.00 aio/3			  

   90 root	  16   0	 0	0	0 S	0  0.0  12:30.98 kswapd0			

  238 root	  25   0	 0	0	0 S	0  0.0   0:00.00 kseriod			

  353 root	  20   0	 0	0	0 S	0  0.0   0:00.00 scsi_eh_0		  

  379 root	  15   0	 0	0	0 S	0  0.0   0:01.61 kjournald		  

 1586 root	   6 -10  3032  284  280 S	0  0.0   0:00.01 udevd			  

 2010 root	   6 -10	 0	0	0 S	0  0.0   0:00.00 kauditd			

 2122 root	   7 -10	 0	0	0 S	0  0.0   0:00.00 kmirrord		   

 2143 root	  15   0	 0	0	0 S	0  0.0   0:01.45 kjournald		  

 2144 root	  15   0	 0	0	0 S	0  0.0   0:00.14 kjournald

...

...

...

...

...

...

...

bfarber · September 17, 2007

Honestly I think your BEST option would be to switch to sphinx search engine.

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

320 online users isn't that many, but 1.5 million posts is a lot of posts. If just 1 or 2 users do a complex search it can lock everything up with that much data. sphinx would alleviate a lot of pressure on the mysql database.

Bono · September 18, 2007

I would add at least 2gb of ram, maybe even 6 if you can afford it. And it would be good idea to install xcache and of course switch to sphinx. With extra ram you would eliminate disk swaping, but try to execute command free -m and top when load is at 8.

Darkside_RG_merged · September 18, 2007

:unsure: your using less swap than you have free RAM :unsure: so you can probably get better results by optimizing my.cnf, from limited experience I would say your key_buffer size is on the small size.

Undoubtedly that spinx mod would be the way to go though.... looking forward to giving that a go when time / hardware and circumstances require it.

bfarber · September 18, 2007

I've set up sphinx a few times and it always provides drastic improvements on sites with a lot of posts.

More memory will ALWAYS help. If you have 8GB of memory and add another 2GB, it would never hurt. :D

The key_buffer_size is somewhat low for the amount of data in the database, however keep in mind this isn't a dedicated mysql server (it's mysql + web) and he only has 2GB right now. You might be able to up the value to like 768MB at the most, but be careful - if you give too much memory to mysql, apache will get mad, go on strike, and sue you.

mat_ · September 18, 2007

Thanks a lot for your answers, I will try to install sphinx search engine and I hope everything to be ok!

mat_ · September 18, 2007

I just decide to upgrade to 4GB of RAM. What changes can I do the my.cnf file?

Thanks

bfarber · September 18, 2007

With 4 GB RAM I'd set key_buffer to at least 1 GB (set it to 1024M in the my.cnf file). That will make the biggest difference.

Also, search this forum for maxrequests and find the topic where rct explains a good rule of thumb for configuring Apache. You can play with the apache config file to boost performance dramatically too.

Bono · September 19, 2007

I've set up sphinx a few times and it always provides drastic improvements on sites with a lot of posts.

More memory will ALWAYS help. If you have 8GB of memory and add another 2GB, it would never hurt. :D

Usually when system starts to swap it means that he needs more memory. Thats why i said he could add additional 2GB of memory.

When You add memory maybe it would be good thing to set read buffer to 256 or maybe 512mb and check how high mysql usage of buffer would be.

Get this and execute it from shell: wget hackmysql.com/scripts/mysqlreport

Like on my system, i didn't set buffer so high and usage is never over 90%

__ Key _________________________________________________________________
Buffer used 85.18M of 96.00M %Used: 88.73
Current 62.68M %Usage: 65.29
Write hit 44.94%
Read hit 99.99%

So my recommendation is to try with lower buffer and if it fills up you can raise it higher.

bfarber · September 20, 2007

Usually when system starts to swap it means that he needs more memory. Thats why i said he could add additional 2GB of memory.

When You add memory maybe it would be good thing to set read buffer to 256 or maybe 512mb and check how high mysql usage of buffer would be.

Get this and execute it from shell: wget hackmysql.com/scripts/mysqlreport

Like on my system, i didn't set buffer so high and usage is never over 90%

__ Key _________________________________________________________________

Buffer used 85.18M of 96.00M %Used: 88.73

Current 62.68M %Usage: 65.29

Write hit 44.94%

Read hit 99.99%

So my recommendation is to try with lower buffer and if it fills up you can raise it higher.

That's true, but he's also got 1.5 million posts or so, so the index would likely take up quite a bit of space.

Rule of thumb is usually to set your key_buffer large enough to hold all myi files in memory.

BTW, Blake loves mysqlreport - he recommends it to pretty much all people looking to optimize their server. I haven't played with it a lot, but from what I've seen it looks nice.

mat_ · October 7, 2007

I added 2GB of RAM and configured a little my.cnf. Now everything is ok, but every night between 8 and 9 I have more than 500 users online (5minutes) and then the forum goes slow or sometimes the server restarts...

When 2.3.2 comes out I will install sphinx but is this enough?

Also is there any mod which change some configuration settings depending the amount of online users?
Example: When there are more than 400 online users - raise flood time for search function and/or remove some features that require a query like "Remove 'Users Browsing this forum' feature?"

bfarber · October 8, 2007

I don't know of any mods like that, no.

Is it possible for you to find out WHY the server is getting slow between that time? Run top. Run the mysql show processlist query. Find out exactly what the problem is, otherwise it's difficult to tell you what you need to look at to fix it.

MarcusInMd · October 18, 2007

I added 2GB of RAM and configured a little my.cnf. Now everything is ok, but every night between 8 and 9 I have more than 500 users online (5minutes) and then the forum goes slow or sometimes the server restarts...

When 2.3.2 comes out I will install sphinx but is this enough?

Also is there any mod which change some configuration settings depending the amount of online users?

Example: When there are more than 400 online users - raise flood time for search function and/or remove some features that require a query like "Remove 'Users Browsing this forum' feature?"

Your problem *could* be apache. I am running tests now on our very busy server with a replacement web server called litespeed. It replaces apache on your server. So far I have noticed decreases of about 50% in server load over apache.

mat_ · March 16, 2008

Hi, I just moved to an other server with the following specs:

Processor: Dual Xeon E5420 Quad Core 2.5GHz
Memory: 6GB DDR2 PC5300
Hd1: Dual 73GB SCSI / Hardware Raid 1 with 15.000 RPM

(Before I had 8GB of RAM but I saw that I had not problem with RAM so I will start with 6GB and if necessary I will upgrade to 8GB)

What could I change?

Thanks

Speed Racer · March 16, 2008

The biggest help I have seen is going to faster HD's like yours. Makes searches take less time and drastically reduces server load as a result of this and other factors).

bfarber · March 19, 2008

I use this site for general assistance configuring my.cnf

http://www.databasejournal.com/features/my...cle.php/3367871

Server Optimization

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Recently Browsing 0 members