MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:<sinisa Date:August 19 1999 3:29pm
Subject:Large scale performance of Mysql?
View as plain text  
Terry Brown writes:
 > Hi all,
 > 
 > I have a query for anyone running Mysql with large concurrency via 
 > CGI.  This is a follow up to a post I sent about 3 months ago.
 > 
 > The full specifications are listed below and you'll probably see, I've 
 > tried a lot of configurations.
 > 
 > We need mysql to accept ~200 concurrent connections (via Perl CGIs) and 
 > each of those connections runs through 4 perl CGI's and makes approximately 
 > 50selects, 20inserts over the 4 scripts (70x200=14000 interactions).  I can 
 > test everything with Apache benchmark with 400 concurrent connections to 
 > the first script (the login script) once.  This will 95% of the time 
 > succeed without any errors and send the system load to ~80-120.  If it is 
 > tried again (even after the system load drops back down) there are a number 
 > of failures reported by Apache benchmark and looking in the logs reveals a 
 > Can't connect to Mysql server.
 > 
 > I've tried optimising the code but we're down to the bare bones of what we 
 > can get away with now.  I'm just about to buy the Mysql O'Reilly book (hope 
 > Monty and the guys get some of the proceeds?) but other than that, I'm 
 > completely stuck.  Can anyone at all shed some light on this 
 > situation?  There would be an airmail pint in it for them I'm sure :-)
 > 
 > Many thanks for any help,
 > 
 > Terry Brown
 > 
 > 
 > ___ stats from here down ____
 > We have a Sun UltraSparc E450, 4x250MHz processors, 512MB ram, 1.2GB swap.
 > 
 > We're running Apache1.3.6 and Mysql 3.22.25.
 > 
 > We've run mysql with the following command lines:
 > 
 > 	-O back_log=			65000		1500		30000
 > 	-O max_connections=	65000		5000		30000
 > 	-O wait_timeout=		45000		35000		120
 > 	-O key_buffer=		85M		32M		40M
 > 	-O tmp_table_size=		3000000	-		2000000
 > 	-O thread_stack=		300000	-		131070
 > 	-O delayed_queue_size=	32000		1500		4096
 > 	-O table_cache=		85M		30000		40M
 > 	-O sort_buffer=		85M		4M		40M
 > 	-O record_buffer=		85M		16M		30M
 > 
 > +others
 > 
 > As you can see, I've tried a number of different settings to achieve my goal.
 > 
 > I have also tuned some of the kernel variables in solaris (2.6) as follows:
 > 
 > /etc/init.d/inet
 > tcp_conn_req_max_q	32768
 > tcp_conn_req_max_q0	65535
 > 
 > tcp_deferred_ack_interval	200
 > tcp_slow_start_initial	2	(most of our clients are Win)
 > tcp_close_wait_interval	60000
 > tcp_fin_wait_2_flush_interval	67500
 > tcp_cwnd_max	262144
 > tcp_recv_hiwat	65536
 > tcp_xmit_hiwat	65536
 > 
 > /etc/system
 > set rlim_fd_max = 65535	(hard limit fd's)
 > set rlim_fd_cur = 65535	(soft limit fd's)
 > ________________________________________________________
 > Terry Brown				http://numedsun.ncl.ac.uk/
 > 	C&IT Development Officer		
 > 	UNIX System Administrator
 > 
 > Faculty of Medicine Computing Centre, University of Newcastle,
 > NE2 4HH     Tel: +44 191 222 5116	Fax: +44 191 222 5016
 > 


Hi!

First of all, I truly do not understand those triple values for MySQL
variables.

Second, you have met some limits in Solaris, that is definite.

Some of the buffers are horendously great. Thread stack in
millions. Why ? Table cache too ...  

Do not forget that sort and record buffers are THAT large for EACH
thread.

I am now logged on Solaris 2.7, dual UltraSparc, running just
MySQL. It is connected to 6 WWW servers each bombarding it with PHP
mysql persistent connections.

I have set up very modest buffers, except keybuffer, because there are 
few sorts and groups.

Machine has 600 connections AT AVERAGE and it is 20 % loaded.


You are doing it wrong ...................


Sinisa

+----------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped|
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
|             /*/             \*\                Developers Team       |
+----------------------------------------------------------------------+






Thread
Large scale performance of Mysql?Terry Brown19 Aug
  • Large scale performance of Mysql?sinisa19 Aug
  • Re: Large scale performance of Mysql?Scott Hess19 Aug
  • Large scale performance of Mysql?Michael Widenius23 Aug
    • Re: Large scale performance of Mysql?Yan Zhang Chen23 Aug
      • Re: Large scale performance of Mysql?Michael Widenius27 Aug