Mysql_Pconnect And General Bad Practice...

[Drongo_III]

Hi Guys

 

I have recently taken up a new job. I am noticing a lot of bad practices, at least that's how they occur to me, and i was hoping to get your opinion on the following:

1. Is it worth using mysql_pconnect ? I know php.net doesnt advise using it. The site gets a lot of visitors - typically around 700-800 hits a week - so would this justify using mysql_pconnect? And should you therefore use it on sites where you get a lot of traffic to reduce overhead? Or is it just pointless and would it be ok ot just use mysqli connect?
    
   
2. A lot of the code is very procedural. One of the include files connects to the database using the method above and then there are mutliple lines lines of mysqli_query, mysql_fetch_assoc (30 plus lines of this). But none of these are surrounded by any logic so it would appear that everytime the include is called all of these statements are executed and all result sets retrieved. Would I be correct in thinking this is a tragically bad approach? OR is this something that works ok because they'ved used mysql_pconnect?
   

 

Thanks...

 

drongo

[KevinM1]

That sounds pretty bad. AFAIK, query results aren't cached with a persistent connection. I also wouldn't think that the overhead for opening/closing connections would be bad enough to necessitate a persistent connection, either. I mean, 700-800 hits a week is ~100 hits a day, or just over 4 hits an hour (4.1blahblahblah).

 

On the surface, it sounds like a bad approach, but without being able to see the actual code, it's hard to say with 100% certainty. That said, the code should at least be modernized to use either MySQLi or PDO. The classic mysql_* functions aren't recommended any longer. There are better/safer way to handle the db.

[Drongo_III]

Thanks for your reply Kevin

 

Sorry I got the site visitors bit wrong - it's 700-800,000 per month - brain was elsewhere as i was writing :/ .

 

I've never really seen mysql_pconect before - hence why I felt the need to get a second opinion. Do you think with those kind of visitor figures that mysql_pconnect is worth using? Or should I really just drop mysql for mysqli going forward?

 

And the multiple query/fetch statements - where most of them aren't even used on every page - seemed plain wrong too.

 

 

 

 

That sounds pretty bad. AFAIK, query results aren't cached with a persistent connection. I also wouldn't think that the overhead for opening/closing connections would be bad enough to necessitate a persistent connection, either. I mean, 700-800 hits a week is ~100 hits a day, or just over 4 hits an hour (4.1blahblahblah).

 

On the surface, it sounds like a bad approach, but without being able to see the actual code, it's hard to say with 100% certainty. That said, the code should at least be modernized to use either MySQLi or PDO. The classic mysql_* functions aren't recommended any longer. There are better/safer way to handle the db.

[KevinM1]

(DISCLAIMER: I suck when it comes to db scalability, so someone else will likely know more)

 

Those numbers sound reasonable for a persistent connection.

 

Keep in mind - having a persistent connection and using MySQLi or PDO addresses two different concerns. MySQLi/PDO is just how we can interact with the underlying database, just like the classic mysql_* functions. They just represent a more modern access layer. It's entierly possible to create/use a persistent connection through them.

 

A persistent connection makes sense because you can pool connections. Instead of creating/destroying a new connection every time a script is executed (which, given the nature of PHP, means with every HTTP request), connections can be swapped around. Instead of a create/destroy cycle, it largely becomes a use/free cycle, which saves on overhead.

 

Now, regarding the actual queries themselves, it's hard to determine if 30+ are too many because we don't know the data you're working with. That said, even a forum like this one tends to use multiple queries per action. If you look at the bottom of the screen, you can see it. Actually, looks like only staff can see it. Suffice it to say, actions on this forum take ~15-20 queries each. And that includes reading a topic, posting, etc. So, it's not a matter of "after X number of queries you're screwed," but whether or not the queries themselves are necessary and written/executed efficiently.

Edited September 26, 2012 by KevinM1

[Jessica]

I was taught to always use pconnect. *shrug*

[PFMaBiSmAd]

Are you sure the persistent connection is actually 'working' in your case.

 

Unless php is running as a server module on a multiprocess/multithreaded web server, all you end up with is a regular connection anyway.

Edited September 26, 2012 by PFMaBiSmAd

[darkfreaks]

here is your answer @ OP

 

 

Mysql_pconnect will also open a new connection when a PHP page is called up (at any rate, it will the first time after a server reboot), but it will NOT close the connection at the end of the request - instead, it will save it in a connection pool so that a subsequent request can continue to use the same connection. It's intended for pages that do have a heavy usage - where the resources burn up by opening and closing connections every time might have a severe effect on performance. If your local supermarket had a door that was opened each time someone went in and out, there would be a lot of needless opening and closing going on - better to leave it open and let a whole lot of people in and out at the same time.

 

 

 

Mysql_connect opens a new connection each time a PHP page is called up, and closes the connection down again at the end of the request. It's ideal for pages that don't have a heavy usage - doesn't need tuning, is straightforward internally. If you compare MySQL to a shop, this is the connection that you would use for a small shop where the door is opened each time a new customer wishes to enter.

[Drongo_III]

Are you sure the persistent connection is actually 'working' in your case.

 

Unless php is running as a server module on a multiprocess/multithreaded web server, all you end up with is a regular connection anyway.

 

Not entirely sure about this as I have only just taken over from the last person. It's definitely the only connection being made but whether it's being utilised properly is another matter.

 

 

here is your answer @ OP

 

 

Mysql_pconnect will also open a new connection when a PHP page is called up (at any rate, it will the first time after a server reboot), but it will NOT close the connection at the end of the request - instead, it will save it in a connection pool so that a subsequent request can continue to use the same connection. It's intended for pages that do have a heavy usage - where the resources burn up by opening and closing connections every time might have a severe effect on performance. If your local supermarket had a door that was opened each time someone went in and out, there would be a lot of needless opening and closing going on - better to leave it open and let a whole lot of people in and out at the same time.

 

 

 

Mysql_connect opens a new connection each time a PHP page is called up, and closes the connection down again at the end of the request. It's ideal for pages that don't have a heavy usage - doesn't need tuning, is straightforward internally. If you compare MySQL to a shop, this is the connection that you would use for a small shop where the door is opened each time a new customer wishes to enter.

 

So the upshot from that would suggest that it's the way to go - given the site volume.

[darkfreaks]

i would not consider 800 a week high volume traffic more like Normal. i would probably switch to mysql_connect until you hit a good 10,000+ visitors a day/week/month.

Edited September 26, 2012 by darkfreaks

[xylex]

One of the include files connects to the database using the method above and then there are mutliple lines lines of mysqli_query, mysql_fetch_assoc (30 plus lines of this). But none of these are surrounded by any logic so it would appear that everytime the include is called all of these statements are executed and all result sets retrieved.

 

 

This is going to be a far bigger performance issue than any mysql/mysqli/pconnect issue. Why are there so many calls executed every time? Is this something that should be joining across tables and combining results? Are there queries in there that don't really need to pull from the db with every page load and could be cached?

Edited September 26, 2012 by xylex

[Drongo_III]

Hi

 

Yes there are queries that aren;t even used on every page. It's clearly some sort of 'catch all' solution that doesn't have much regard for efficiency. I think I have my work cut out for me...there is a LOT to fix.

 

This is going to be a far bigger performance issue than any mysql/mysqli/pconnect issue. Why are there so many calls executed every time? Is this something that should be joining across tables and combining results? Are there queries in there that don't really need to pull from the db with every page load and could be cached?