Counting MySQL connections on a page

[123468743867143]

Good morning everyone,

Is there a way to find out how many MySQL connections are on a particular page?
I am limited to 50 db connections (MySQL+Linux shared server).

It seems that my site cannot handle more than two people at once. I have been told by my hosting provider that I have 36 connections on one page. That sounds crazy and the tech could explain more (?). I have been analyzing the code and can't figure out the problem.

The site works great if only person is on but it is becoming popular.

Any suggestions, input or links would be HIGHLY appreciated. I have been cruising the Internet for two weeks looking for a solution.

Thank you.

Natasha

[dickersonka]

how are you accessing the database, through java php etc?

[123468743867143]

Hello!

Simple php

One sample extracted from one page:

<?
$sql="select * from fsbo_properties  where parent_id=0 order by property_name";
$rs=mysql_query($sql);
while($rec=mysql_fetch_array($rs)){
if($rec[cat_id]==$mainid){
?>

I am wondering if there is a software out there that can calculate the number of queries a page makes to the database.

Just like there are sites where you can validate your html, css, etc …

Or possibly something that tells me if the connections are staying open too long ???

Thank you

[dickersonka]

not sure what kind of front end you have to look at the mysql side with

you could either set up a counter with a static variable, to increment and decrement connections, queries etc

http://webmaster-forums.code-head.com/showthread.php?t=202

you can also use mysql administrator to view current connections

there is a program called heidi sql that shows some detailed info as well
http://www.heidisql.com/screenshots.php?which=host_commandstats

and one last thing, you could try running show processlist
http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html

[123468743867143]

I access MySQL through my host provider GoDaddy (Was that your question?).

I will take a look at the links (Thank you).

I just a thought that possibly the functions are creating the problem not necessarily the coding on the page itself … so I will be looking at this too.

Question (if I may):

Does seeing the code "$sql= ..." necessarily mean that a connection is going to be open? Or does it depends if function/action is called by the visitor on the page?

[dickersonka]

$sql doesn't necessarily mean for sure it will be executed, it means that a query is being constructed, as long as someone named a variable called that for a query

but also, gotta remember if there are checks to run the query and any includes

[123468743867143]

Your first comment confirms what I thought. This would mean that anyone opening a page (without doing any searches or registration) should not connect to the database.

The second comment is something new to me. I have limited knowledge about MySQL and four bible-size books I borrowed from the library.

Something else I will be investigating … "checks to run query".

Thank you.

[dickersonka]

lol maybe i overcomplicated that statement

lets see you are setting a variable for an admin

if($is_admin){
    $sql = "select * from myadmintable";
}

if the user isn't an admin, the statement won't be executed, but could still be on the page

[123468743867143]

I think I "get" what you are saying but this does not seem to be the case.

The site functions quite well but if 1 or 2 users are on, no one else can get in. The site becomes too slow and does not display at all.

The reason I am searching for a MySQL answer is that I was told by a phone agent at GoDaddy it was because there were more than 50 connections open. Of course I can't find a proof of that.

[dickersonka]

are you for sure you are calling ?

mysql_close($conn);

[123468743867143]

I do not have any mysql_close anywhere in my code.
Does it mean the connections are staying open eventhough they could be safely close without bothering the user?

(PS: I will gone for 10 mn)

[123468743867143]

I thought php automatically closed db connections after a script was finished.

But I guess it might still be better to close the connections after they are run.

What do you think?

If yes, how do you safely close connections to make sure that it does not hurt the proper functioning of the page? (I own a real estate directory where users create their own account and post their own listings)

And do you close each db connection individually? Or do you close all connections on one page with one single piece or coding?

Your help has been great. Thanks again.

Natasha

[dickersonka]

yes they are closed at the end of the script, just wondering if for some reason a script doesn't get completed and isn't closed

lol this one is starting to make me think

[dickersonka]

what did your "SHOW PROCESSLIST" command show?

[dickersonka]

ahhhh, one thing i just thought of

what does allow_persistent show?
its in the php.ini file

[123468743867143]

I am sorry: What do you mean by SHOW PROCESSLIST?

I cannot any code showing persistant db connections. My php.ini is as follow:
register_globals = off
allow_url_fopen = off

expose_php = Off
max_input_time = 60
variables_order = "EGPCS"
extension_dir = ./
upload_tmp_dir = /tmp
precision = 12
SMTP = relay-hosting.secureserver.net
url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=,fieldset="

[Zend]
zend_extension=/usr/local/zo/ZendExtensionManager.so
zend_extension=/usr/local/zo/4_3/ZendOptimizer.so

No idea what the zend is for.

I have been looking to see if any scripts command a persistant connection instead of a normal one but nothing seems to point to that.

I read that multiple db queries in one single script count as multiple database connection (one for each query).

If this is true, there could be my problem. I have multiple queries in some script (if executed).

Let's say someone is log into their account (username, password, viewing their listings, etc) and he does a search with specific country, state ... property type, price, etc ... Could each peice of info count as one connection?

[dickersonka]

you can run the show processlist as a query in mysql

also, are you reusing your connections or creating new ones on that page?

[123468743867143]

I can see the qurey SHOWPROCESS LIST in MySQL. Does not seem to provide any help. What should I be looking for exactly?

Process list

Your SQL query has been executed successfully

About your second question ... good one ... I just don't know yet. I had someone from India set up the database and first pages. I have taken it over a while back. So the whole MySQL is problematic to me. Let me take a closer a look and the connections being reused.

[dickersonka]

here's a sample of what it will look like
http://free.netartmedia.net/Databases/Databases4.html

you can tell what the connections are doing with themselves :-)

[123468743867143]

It only shows one row. I wonder if it is only showing my connection to the db since I am log into the db or that only one person is on at this moment.

I read that sometimes one does not have Process priviledge. I wonder since I am on a shared server (but the db should not be shared though).

Not sure how to answer your question about "connections with themselves". I had to smile at that one.

They retrieve listings stored in the database based on criteria selected by user. They also retreive account with username and so forth. They entered number of visits and enquiries for each individual listing into the db. They do many different things if called upon.

I hope it makes sense ... English not being my first language.

[123468743867143]

Hey, you have been awesome. Thank you so much.
I must go to a late meeting … yerk.

I will analyze all the data, links, questions and suggestions you have been kind enough to share with me.

I thank you a million times.

Your French Connection in California,

Natasha

(I'll be back … with our governor's voice)

[dickersonka]

perfect english :-), lol well at least typing

maybe when the users are on the system tomorrow, run show processlist again, and you can see what may be causing the problem