PHP / mysql help

**Stuart** · 28-09-08

Hi,

Please could anyone offer any help. I keep getting this error message on my site pricefilterdotcodotuk
----------------------------------
Warning: mysql_connect() [function.mysql-connect]: Too many connections in /home/reedsd/public_html/index.php on line 109
Unable to Connect to Database
----------------------------------
There are conversations happening about the above issue, is it coding or a hosting issue?

Seperatly: Prior to the above error the pricing results coming back in the XML feed witin the shopping section, sometimes displayed and sometimes did not, if we did a forced refresh when they were not displayed then after a few attempts the results appeared.

The feed provider said knowother partners are having problems, again we cant figure out if the 2nd issue is hosting related, cosding or wheer the results are pullled from the feed provider (Pricerunner)

Anyone help please - thanks

Stuart

**shyguy0507** · 28-09-08

I'm not sure if this will help but you could try using the mysql_pconnect() function instead of mysql_connect(), it will setup a persistent connection so if another user tries to use the script, the script will look for an existing already open connection.

Originally Posted by php.net

when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

More details can be found here: PHP: mysql_pconnect - Manual

**affiliatemarketingltd** · 28-09-08

here is a quick connection script

<?php
$dbu = 'USERNAME';
$dbp = 'PASSWORD';
$dbh = 'HOSTNAME NORMALLY LOCALHOST';
$dbn = 'DATABASE NAME';
$dbc = mysql_pconnect($dbh, $dbu, $dbp);
mysql_select_db($dbn, $dbc);
$query = "SELECT * FROM TABLENAME";
$doquery = mysql_query($query, $dbc);
$result = mysql_fetch_assoc($doquery);
?>

hope that helps

thats just a connection scriopt and a basic query

**Stuart** · 28-09-08

Thanks affiliatemarketingltd and shyguy0507. I'll let you know if this solves the problem

Br
Stuart

**tbp** · 28-09-08

The error is exactly as it says, too many open MySQL connections.

There are 2 reasons why this happens:

1) You have some rogue code somewhere thats opening MySQL connections and not closing them again afterwards, meaning they build up.

2) You're site has become so busy that its opening more connections than have been allowed in the my.cnf (MySQL configuration file).

The solution to 1 is to do a global search looking for code that opens MySQL connections, and then make sure that each one is closed again later in the script.

The solution for 2 is to get the host to edit their my.cnf file and allow more connections to be opened.

**Stuart** · 29-09-08

tbp

I have asked my developer to take a look at the options

Thanks a million

Br
Stuart

**Stuart** · 29-09-08

**D-Mac** · 29-09-08

SELECT * FROM user_reviews WHERE product_id =
'1002827' AND approved > '' ORDER BY RAND() LIMIT 1

Do you have multiple products with the same product_id ? E.g. multiple retailers selling the same product.If so, presumably you're using RAND() to select a random single occurrence of this product?
Take a look here: MySQL Select Random Row Fast
There are also other ways that you could select a random row, but before going into detail it's best to clarify the actual requirement of the scripts.

**tbp** · 29-09-08

Basically, what they are saying is that it's the speed of the SQL queries that's too slow, and consequently it leads to a large number of connections open at once.

I do get the feeling that its not the whole problem, and its excuse to pass the buck back onto you, but you should do everything you can to make the queries as quick as possible to give your site the best performance.

Ordering using RAND() is ok for a small number of records, say up to 500, but with a larger dataset its very slow ,as a random number has to be generated for each record, and then these random numbers placed in ascending order.

It's better to use your PHP code to pick a random record id, based on the number of records in the table, and then to call up that record. It does use more SQL queries, but even so it's still faster than the single one containing RAND().

They are correct in saying that you should avoid using SELECT * where possible, and just select the fields that you actually want to use.

The indexes are also very important, and can speed things up by a huge amount. I`ve seen an SQL query that previously took 5 - 10 minutes brought down to a few seconds, simply by adding an index. If you use phpMyAdmin it makes it very easy to add indexes, basically you need to make sure that there is an index on each of the major fields you search on. So if you search a dataset by record id, name and email address, then you would make sure these fields are indexed (although record id should be the primary key and as such is already indexed). Don't add an index to everything, as then things start slowing down again, just add them to fields which are regularly searched on.

How much this is going to affect your site I don't know, as it depends on the size of your data. If you have 1000 records its not going to make a noticeable difference, if you have 100,000 records it will.

The above should be implemented anyway, as its the best practice for working with databases, so get your developers to do the above and then see what happens from there. It may be that your host should still raise their connection limit, but when you've done the above you will be in a better bargaining position.

If the developers haven't ventured into this area before, there are huge numbers of tutorials available on the net on indexes and MySQL optimisation which will guide them.

**Stuart** · 29-09-08

Superb, thanks tbp and D-mac...

We take XML feeds from pricerunner so its 1000's or products across multiple categories (pricefilter dot co dot uk) don't know the exact number.

I'll pass this post to my developer.

I was shocked receiving the email from my host, as the strange thing is the sites been liver for nearly 2 years and this only just started happening, although traffic is up slightly (well was before these errors).

The host said they won't raise their connection limit

Hopefully we can get it sorted ASAP

Br
Stuart

**affiliatemarketingltd** · 29-09-08

Originally Posted by Stuart

The host said they won't raise their connection limit

To be hojnest stuart your host sounds like an arsehole who is running a slow ass server and wants to try and blame the clients for any problems
agree about teh select * comment .. although i had no idea aboutthe size etc when i posted

hope you get this sorted as my host threw a hissy fit once .. although my script did lock up their server lol

**Stuart** · 01-10-08

A public thank you to everyone that replied and gave advice as to how we could solve our problem.

Without wanting to offend anyone - speacial thanks to "tbp" for the detailed response, crossing fingers we have solved the issue, only time will tell...

Br
Stuart