Welcome, Guest. [ Log In ]
Question   Why do connections count so much towards conueries?
Search KBase


Top 4 in this Area:
1. Why do connections count so much towards conueries?
2. How many conueries am I allowed per month?
3. What does that "ratio" mean on the mysql usage page?
4. How do I check how many conueries I've used?

 
Why do connections count so much towards conueries?
Database connections use far more system resources than the average database query does. Because of this, we want to encourage people to make as few connections as possible with their code. So we "penalize" each connection by making it worth so many conueries. You can do a lot of things to cut down the number of connections you make with your code - it all depends on your particular application.

One of our customers averages over 1300 queries per connection! Thanks to the design of his code, his "conueries" are basically just queries.

Another customer averages just one query per connection. What they've done is basically turn their "conueries" into 25 times their queries. This uses up the monthly allotment of conueries a lot faster than necessary, so you should try to keep those connections open as long as possible. It'll also make your site faster in the process.

Remember, it's possible, through the use of peristant connections, to not have to make a new connection to your database even on a new page load!

Last updated: May 28, 2002.

User Post (2005-01-03 20:28:48 by veganarian)
Is it possible to save a resource somewhere in PHP? If so, maybe one could save the database connection resource to be able to grab it later and check if it is still valid.
User Post (2004-08-04 13:49:45 by mabi)
If you use PHP as a CGI "wrapper" instead of an Apache module, when the page load is completed the instance of the PHP interpreter used to load your page is destroyed and all associated resources are released to the system.

In otherwords, if you use PHP in CGI mode, you will gain nothing by using persistent connections. They are only applicable when run as an Apache module.
User Post (2003-11-17 11:24:34 by rauldelauralii)
I am somewhat new to MySQL... From what I have been able to figure out, the "wait_timeout" variable determines how many seconds a persistent connection will remain inactive before closed (i.e. the user has left your site and isn't coming back for some time, this tells how long the connection will wait for them), and that there is a session version of this variable which is set from a globally set default. I noticed from the mysql> prompt that this session variable is coming up initially set to 14400 (4 hours) in my session (which could be a problem if you get lots of quick short hits to a site, right?). Is there a way to set the session value for this when the session starts up (or even the default for all sessions you create for your database?)? Also, am I correct to assume that every web user might get a different persistent connection? (I'm starting to doubt this, and if not, then there's no problem I guess except for the connection hanging around possibly when no-one is using the site)
User Post (2002-11-16 14:55:14 by jkv_spt)
People who are complaining about persistent connections and inefficient SQL miss the point. The point is to use persistent connections across page views so that you can serve a user's entire session and not just the current page's data. mySql timeouts should help w/ resource cleanup due to inactivity.<p>

The major caveat to keep in mind is that subsequent users may not obtain the <strong>same</strong> connection they used last time. mySql regulates this according to the host and username; so subsequent pconnect requests will get you a physical link, but not necessarily the last one you used. This means you need to avoid anything that assumes a stateful connection, e.g., temporary tables that you plan to use beyond the lifetime of a page. There are also some problems with persistent connections and transactions - if your page fails to commit or rollback a transaction properly, you can prevent your connection from ever being recycled, and possibly exhaust your persistent connection limit with mySql. Caveat programmor.<p>

See <a href="http://www.php.net/manual/en/features.persistent-connections.php">the PHP Manual entry on Persistent Database Connections</a> for more.<p>
User Post (2002-06-20 16:54:43 by jsasitorn)
It must be noted that this does NOT work if your site is loaded as php-cgi.. Each cgi object is destroyed after the page is completed.
This is documented:
http://www.php.net/manual/en/features.persistent-connections.php
User Post (2002-05-22 00:25:45 by endquote)
For PHP, using mysql_pconnect instead of mysql_connect should help a lot.
User Post (2002-04-19 14:47:32 by jvanasco)
i'll agree with kfox -- an extremely efficient sql join could get the same data as dozens of queries with just one.

this advice seems to suggest that everyone should operate w/a persistent connection -- which i dont think is the best idea in the world
User Post (2002-04-02 14:46:25 by jondream)
Info on connection efficiency would be particularly helpful as Dreamhost recommends phpBB, a bulletin board which uses many connections and queries. I have extrapolated the numbers from just two people using it on one day over a 30 day period with a hundred users a day and have calculated that I will need well over 30 million conuries, 20 more than I have available to me in my plan. In addition, Dreamhost may shut down persistent connections. Some help would be nice.
User Post (2002-03-30 21:10:27 by pharcoff)
For PHP users, here's a link on persistant connections:

http://www.php.net/manual/en/function.mysql-pconnect.php
User Post (2002-03-26 15:38:47 by kfox)
1300 queries for a single conection smacks of inefficient SQL, looping over what could be handled by an inner join. Unless more queries actually saves you later connections, this doesn't really help.

Can anyone point us to application-specific (Perl, PHP, what have you) tips for reducing connections? Can we have persistant connections that last across multiple page views? Help the users help Dreamhost!