Jump to content

Mysql_Pconnect And General Bad Practice...


Drongo_III

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

(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 by KevinM1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by xylex
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.