Help optimizing connections to My_SQL database?

software development

#1

From the support wiki:::
“Remember, it’s possible, through the use of peristent connections, to not have to make a new connection to your database even on a new page load!”

Right now, I’m reconnecting with every new page load… how do I do just what the wiki describes here
and optimize my connections?

Thanks


#2

There are a couple of ways to approach it … what applications are you using (WordPress?, Joomla!, your own code?)

–rlparker


#3

I’m using my own PHP code.


#4

Bear in mind, if you’re using PHP-CGI rather than mod_php, which will be the case for all relatively new installations, you won’t be able to use persistent connections.

That said, if you are using mod_php, replace any calls you make to mysql_connect() with calls to mysql_pconnect() and then you’ll have persistent connections.


#5

Can you explain…
(1) The difference between PHP-CGI and mod_php
(2) How exactly I use pconnect and what it does?
(3) How I know which I’m using, PHP-CGI or mod_php

Thanks again


#6

Yikes! That is a good point which I completely forgot about when I read the post.

IIRC you can use them, but they won’t do anything useful, as they won’t “persist” after the php-cgi process shuts down (I only mention this in the interests of portable code) - please correct me if I have misunderstood this or remembered it wrong!

–rlparker


#7

Alright… so how should I go about this?

Also I want to make sure I’m understanding the pconnect function right.

By using mysql_pconnect, the connection will carry over… if it hits another pconnect call, what happens? Does it reconnect, even if it already is connected?

And if it doesn’t, how do I ensure that a user still has access to the database, no matter what page he starts on?


#8

I can do my best to try! :slight_smile:

In a nutshell, PHP-CGI runs PHP scripts in a CGI environment, meaning that for every page request, Apache invokes the PHP interpreter on your script and your script runs once. This works like any other CGI, too; basically, Apache calls a designated program on your script (PHP, in this case) just like you would from the shell and sends its output to the user’s browser.

mod_php is an Apache module which tries to run PHP inside of Apache itself. This is good for speed because Apache keeps your script inside of its memory and doesn’t need to call any other programs to get the work done. And, because Apache doesn’t stop running after every connection, it can do things persistently (like connect to MySQL!). The problem is that because it does everything inside of Apache, Dreamhost has a hard time finding people who abuse their services and, as a result, they no longer offer this feature.

Normally, when your PHP script ends, PHP automatically closes all of its connections to MySQL. This even happens under mod_php as a matter of “housekeeping” where every script should clean up after itself, leaving a fresh environment for the next one. What pconnect does is tell mod_php to not close the connection, so that way when the next page request comes, there’s already a MySQL connection ready and waiting.

As for its use, you use it exactly like you would mysql_connect(). It’s a drop-in replacement; you can literally do a find and replace through all your code and everything will Just Work. I also think rlparker is correct that mysql_pconnect() will fall back to acting like mysql_connect() if it gets used in a CGI environment.

If you look in your web control panel under Domains -> Manage Domains and edit the settings for the domain you’re interested in, you should see a dropdown for “PHP Version.” I’m pretty sure it says in there if you’re using mod_php (if it doesn’t say anything about mod_php, you’re not using it).


#9

Nope, doesn’t say anything about mod_php.

So will this service become available at some point?

And I’m guessing that I’m just stuck using up coneuries? I’m a high school student working towards gaining experience building dynamic sites — so I’m not expecting that many visitors. So should this really be a problem for me?


#10

That was a great post! I really enjoyed reading it and think you did a great job of explaining. Thanks for taking the time to do that! :slight_smile:

–rlparker


#11

I thank you too!

There were two main reasons why I signed up for DH.

One was the awesome multi-domain package I got through a coupon code, and the second one was the great support - specifically this forum!

To me, almost all hosts offer a limitless amount of storage & BW… and thus their greatness is decided by their support, something I commend DH on.


#12

It’s hard to say for sure, but I don’t think it is likely to be offered as an option anytime soon. If you you search the forums you will find that, on most servers at DreamHost, there is still a way to run mod_php, but it is not publicized and should you avail yourself of that and your programming/site becomes a drain on your server’s resources, you can expect DreamHost to ask you to refrain from using it.

I only mention this because you indicated you are learning, and I doubt than any “light” usage, as you learn the programming techniques involved, will create a problem. It is also true that your server may not have this ability at all - you can always check by trying it! If you find mod_php is available to you and decide to experiment with it, remember that it is not officially supported as a DreamHost feature, and your ability to use it could be terminated at any time. In other words, don’t build a site that absolutely requires mod_php to function properly, or you may find yourself having to move it to another host should DreamHost remove your ability to access mod_php.

In addition to the information in the web panel about your PHP environment, careful study of the output of the phpinfo() function will indicate whether you are running mod_php or php-cgi. :wink:

There is no longer a “hard limit” to the number of conueries you can use on DreamHost; the only limitation is that your usage cannot negatively impact the server. There is more information about that in the Wiki, and there is also information on how you can monitor your resource usage.

If you are careful with your work, and your sites is not getting pounded by lots of visitors, I think you are unlikely to have a problem with conuery usage by your user.

As you are learning, you might consider focusing your attention on the efficient use of queries in your connections, and the judicious use of joins when necessary.

Persistent connections are not a general “magic fix” for inefficient MySQL programming - getting multiple queries efficiently from a single connection will probably produce better results in most circumstances than programming inefficiently with persistent connections. :slight_smile:

–rlparker