Closing connections

[martinh]

Hi
I'm running a popular site which hasn't had any problems up to a year ago. Pages can take an age to load and the site is often down. Although we're assuming this is down to the hosting company (and are in the process of moving host) I am concerned that it may be something I've done in the programming. In particular we have had a "multiple connection" error occasionally. Each php page connects to the database but I don't close the connection as I understood that happened automatically. Is that correct? When I'm testing the site I sometimes open it in several browsers - could that cause a problem?
Any advice gratefully appreciated.

[coolsti]

Just some info that may help you out.

When you open a page in a browser that addresses a PHP script, that script is carried out, sends whatever it is supposed to send back to the user's browser, and then the script ends. Your connection to the database should then end when the script ends (although here I cannot speak for the use of pconnect as I haven't used this in my work). So even though you have many browsers open at one time, this does not necessarilly mean you have many connections to the database open.

The fact that the browser is still open showing a page does not mean that the script in the background is still running, unless you have some sort of loop in the script which causes it to continue. But then the script would probably time out relatively quickly unless you played around with the configurations to allow a script to run for a long time before timing out. But since you are using an external host, I would not suppose that this is the case.

One thing to consider is whether your database design is such that the queries have suddenly become extremely long to perform. This can happen as the tables become larger. And this can quite often be fixed by adding some table indexes, which may slow up slightly the update and inserts to tables, but can increase the speed of queries enormously, like factor of 100 or more.

[Atli]

Hi.

When you say "popular", are we talking a couple of hits per second or some hundreds or thousands of hits per second?

If it is only a few hits per second, then any half-decent server should be able to handle the load. It usually only takes a fraction of a second to render a page and by default Apache allows 150 concurrent connections.

There is also a limit to how many concurrent connections your MySQL server can handle.
If I remember correctly, the default value is 100.

It's possible if you are using shared hosting that another site on the same machine is causing the problem. If that is the case you should call the hosting company.

[chaosprime]

A not-uncommon cause of slowdowns like this as a small site becomes more established is database design that works well when there are 20 rows in the table but not so well when there are 2 million. It may be worthwhile to look at what queries you're doing on slow-running pages and make sure that fields you're doing lookups on are indexed.

[coolsti]

I don't know if you have access to this since you are using an external hoster, but perhaps they could help you with this. There is an option to log slow queries. If this was enabled, and if you could be given access to this log, you can see what queries, if any, are taking relatively long time to be performed.

[martinh]

Hi
Many thanks for the replies. No, the site is not that popular! And the database is not that large (and indexed). It certainly looks like it's down to the hosting company (we're on a shared server). My own smaller site is with a host offering a virtual server, VDS, which seems a better option, particularly since I can restart the server. Is it worth setting up our own server? I imagine that has its own problems - installation, security issues, etc.
Thanks for the tip about logging slow queries - I'll follow that up as well.

[coolsti]

You certainly can check to see if it is the database design or query design or your part of the site itself that is causing a slowdown, if you can copy your database to your own machine to test it. If it is your database at fault, it will also run slowly on your machine. If it is the fault of the hoster (busy server, or misconfiguratio n of something) then it will work well on your own machine.

Another thing: did your hoster change anything recently? Like for example going from MySQL 4.x to MySQL 5.x? The 5.x version of MySQL optimizes queries differently, and I have experienced a few queries with many table joins in my work which worked well with MySQL 4.x but ran terribly slowly on MySQL 5.x. The solution here for me was to modify the query, in particular the ordering of joins.

[martinh]

Thanks. My MySQL programming is pretty simple - straightforward SELECTs.
We've set the site up on another host which appears to run fine (very occasional slowdowns). I guess it must be the current host - we'll be switching hosts soon.
I didn't realise you could run the database on a standalone machine. I'm using a Mac - is it straightforward to do?
Thanks again for all the advice.

[coolsti]

You can certainly run the database on a standalone machine, as long as you can install a version of MySQL on it that somewhat matches what your host is using. That is, if you install for example a very old MySQL that does not allow subqueries and your site uses subqueries, then you of course won't get anywhere.

But assuming you install MySQL on your own PC (it is open source and so free to use under the GPL license agreement) you certainly can open a console window and type in (copy and paste in) various queries from your site and test them for speed and using the EXPLAIN keyword to see what is going on.

I don't know about a Mac, but I would imagine there is a MySQL port to it. Just install MySQL (assuming it is available for a Mac) and get it running (it runs as a service normally) and then copy your database over to it. Easiest way I find to do this is to make a mysqldump of your database and then use this as input file in a command line mysql command (for Mac you would need to check the syntax for this). You may first need to create the database. But fiddle around a bit with this. It can certainly pay to have a locally installed version of your database, even if your only way to access the database is via command line calls, because this will let you test queries and modifications to queries and tables (like adding or dropping indexes).

[Atli]

MacOSX, being a Unix based system, can run MySQL fine.
You can download it here. I believe there is a some proprietary Mac package there, as well as a tar package.

You should be able to control it just as you would on a Linux machine, from the kernel. Type mysql and you should get the mysql command line.

[martinh]

Thanks to both! I'll give it a try.