Closing SQL connection

Dear Programmers,

recently as my sites have grown to have respectable traffic, I have been running into some resource shortage. Site is written in PHP, and I never used to close connections, but whenever there are ~750 visitors connected to the site at any one time I started to have to worry about it.

This is the way I currently use to connect to MySQL db(works for sure :-P) and how to close the connection (not sure if it works just implemented)

<? $link = mysql_connect($dbServer, $dbUser, $dbPass); ?>
content blah blah content
<? mysql_close($link); ?>

PHP:

Please let me know if this is the right way to do this!

I am also open to suggestions as to how save SQL resources!

Thanks in advance

 

mysql_close($link ) is the command to close the connection

 

so mine is right then

 

<?
$link = mysql_connect($dbServer, $dbUser, $dbPass);

content blah blah content


mysql_close(); ?>

also will work in this way

 

if there are more than one connection on the page, will it close all the SQL connections?

 

<? Php

it will close within the scope of the connection.
?>

 

As with most questions regarding a particular function, the official documentation/manual will be a lot more reliable than the second hand knowledge (or often lackof) from programming forums.

If for any reason you're opening multiple connections and want to explicitly close all, I guess you could use:

while(@mysql_close());

PHP:

 

PHP will automatically close the connection when it's done running the script.

From the same page

If your server is getting overloaded you need to work on optimizing queries and code.

Just closing the connection won't help at all.

 

So I have noticed, adding connection closings didnt change a thing.

I guess I will have to go to work on queries

 

Don't forget that setting up Indexes properly makes a huge difference to performance. Look into query caching too.

 

How could I cache queries? output for 90% of queries on the server would only change few times daily. Yet they are executed ~100,000 times houlry.

I would really appreciate it if somebody would inform me as to how cache those bad boys

 

You can store the results in files or in another database table.

I wrote a wrapper in PHP once that md5s the query and stores it along with the serialized result in a cache table. When a query is made it md5s the query and checks the cache table. If a result is returned it passes that back to the calling function otherwise, it runs the query, caches it and returns the result.

You could save the serialized array in a file using the md5 for the filename. You then just check to see if the file exists and the timestamp of the file to determine if you should use the file or (re)create it.

You could then have a field in the table with an expire so that the cache entries that are X seconds old are ignored when looking for an existing entry.

You could then do a cron that would populate/clear the cache but that's not entirely necessary.

Just make sure that in your wrapper you can tell it not to check the cache because not all queries should be cached.

This works best if you have all your queries wrapped in functions so you don't accidently have two queries that are functionally the same but have a different md5 signiture.

 

MySQL has some limited query caching - you could look into tweaking the config there or use some caching software. Personally I like memcached.

 

If you're going for inbuilt MySQL query caching, you can enable it in the config file using:
query_cache_type = ON
query_cache_size = 32M

Try enabling the caching then monitor MySQL's runtime information to see if you need a bigger cache - Qcache_lowmem_prunes tells you how many times queries have been removed due to the cache size being too small.