When querying the db is it plausible to feel extremely paranoid? I go as far as opening and closing mysql connection every time a new query has to be done. I am afraid that (especially with the ajax enabled pages) this would cause great performance downgrading.
Should I continue in this method or at least open and close connections once in everypage (instead of per-query)? (i'm writing in php btw)
thank you.
I'd recommend a connection pool if it's possible with PHP. It's a way to simultaneously maximize performance and minimize connection time.
Especially if you host your own server, close those connections as soon as you're done. An unclosed connection is essentially a memory leak. The server will clean them up eventually, but it shouldn't need to, and can cause some "bad things".
Yes... I've learned this the hard way.
Yes, the overhead of connecting every time will be considerable. I suggest you just close it when you're done, it's very unlikely that simply having an open connection without running queries on it will open you to vulnerabilities.
You should not close MySQL connections immediately. It's better to use a single connection for the entire PHP script. PHP will automatically close the connection if you don't explicitly.
Opening a new connection incurs some small time penalty, particularly if MySQL lives on another server on the network. New TCP connections require a three-way handshake, and each TCP connection consumes kernel resources for at least two minutes.
Although PHP doesn't support full-fledged connection pooling, the procedural MySQL API does support persistent connections. See mysql_pconnect() for more details. At my office we use pconnect to avoid crashing the TCP stacks on our high-traffic PHP site.
Consider PHP Data Objects, which can keep a persistent connection for you. You really only connect with credentials once and then cache the connection. Create the connection with something along these lines:
$dbh = new PDO('mysql:host=HOSTNAME;dbname=DBNAME',
'USER', 'PASSWORD',
array(PDO::ATTR_PERSISTENT => true));
For more information, see this page in the php manual.
Can't speak for PHP, but when I write this kind of thing in Apache/Perl I generally do two things to boost performance:
a) allow MySQL handles to stay open as long as the Apache daemon does. b) keep cached statement handles around (using an LRU cache).
On production servers, some of those MySQL handles have been around for days, serving many thousands of quite complicated SQL queries. It isn't a problem.