NYCPHP Meetup

[Hans Zaunere]

Ian Forsyth wrote:

> Hello,
> 
> I need some support.
> 
> There is a site I do sys-admin type stuff for, that consistently has Too 
> Many MySQL connections. Today, it happened 3 times already.
> 
> Here is some info.
> 
> Hardware:
> Dual Pentium 4, 1.8 GigHz, 40 Gigs SCSI Raid, 512 MB Ram.
> 
> Server Software:
> Apache 1. 3.27, PHP 4.3.1, MySQL 4.0.12 Max
> 
> Here is the my.cnf file.
> 
> [mysqld]
> bind-address=127.0.0.1
> set-variable = ft_min_word_len=2
> set-variable = max_connections=300
> 
> The developer uses a phplib based database class. On average each 'page' 
> is using 3 instances of the database class. The connect method is using 
> mysql_connect();. The developer is re-working the db class so a maximum 
> of instance of the class will be initialized on any page load. The site 
> is used book dealer, and relies heavily on the full_text indexing of 
> about 100,00 records.

Mostly as an FYI, keep in mind mysql_connect()'s behavior.  If a connection already exists with the same host, user and password information, the connection will be reused by default.  This means that even if 3 objects are created, they should be using a single MySQL connection (assuming they have the same connection information).

> The site gets a lot of traffic. According to webalizer, it gets been 
> getting about 20,000 'Visits' a month. There are not any other sites 
> running on the computer. It is just this site.
> 
> MyQuestions.
> What are your opinions regarding 1. Hardware, is there enough ram on the 
> box to handle increasing the 'max_connections' to 500. 2. Which my-*.cnf 

I would most likely say "yes."  Of course, use the standard utilities to determine system state (top,free,memfree,mem,vmstate,/proc,etc, depending on the platform).  Also keep in mind the limit on file descriptions.  I don't remember off hand what default values are, but MySQL is generally file descriptor heavy.

> should we be using. Everything is basically the defaults.. Here is a 
> link to the results of the show variables.
> 
> http://plusfour.org/mysqlVariables.txt

I'm not seeing anything shockingly wrong here.  There are some formulas to dig up and tweaks you could make, but since the issue seems to be connections now, this should probably wait until the box is humming along in a reliable state.

> 2. At what point do i give the my-large.cnf a try.. how can I test it.. 
> 3. What are the pear classes for load testing?

512mb is not a large RAM system, and I'd hesitate making a change like that without first seeing how it responds to an increase in the max connections.  Personally, I'd write a quick script for load testing so I can't be much help there either.

H