Geek.ro

PHP makes lazy programmers. I don’t want to turn this into a bashing post but as a sysadmin I’ve come across a lot of misbehaving scripts that are slowing my servers down to a halt. The general acceptance is that not the language makes bad code but the programmer, which is true, but the wide spread of php snippets turned everyone with a browser into a coder. Unfortunately, or not, programming takes a lot more than gluing together some pieces of code.
While the vast information present on the web can help you a lot in improving your coding skills theres something that no paper will teach you and that is common sense.
This are two recent cases of awful programming I encountered lately and which made by head twist at the lack of thought from the supposed coder.
Case 1:
$query = “SELECT * FROM table WHERE cond=true”;
$res   = mysql_fetch_array($query);
$num   = mysql_num_rows($res);

I just looked up the manual page for mysql_num_rows and I see where most got their bad habits from.
Why this is bad:
You might not notice the performance problems of this query if you have a small table or if you’re running the mysql server on localhost. What this query does is generate a lot of useless traffic/IO and when you have this query running several times per minute or on a huge table it will have your server crawl no matter the hardware you have behind it. What you are doing is fetching all the rows from the database and then counting them, just imagine selecting 1mil records every time a page loads just to see how many of them you have and you’ll get a sense of whats wrong with this type of approach.
Solution:
$query = “SELECT count(*) FROM table WHERE cond=true”;
$num   = mysql_fetch_row($query);
Same result a lot less overhead.
Case 2:
$query = “SELECT * FROM table WHERE cont=true”;
while ($res = mysql_fetch_assoc($query)) {
array_push($ids,$res['id']);
}
Why this is bad:
Again you are selecting an entire dataset when you need only a fraction of it.
Solution:
$query = “SELECT id FROM table WHERE cond=true”;
while ($res = mysql_fetch_assoc($query)) {
array_push($ids,$res['id']);
}

Keep in mind that all snippets you find on the net are just examples and are made so that they apply for a wide variety of cases, this doesn’t mean that it is the best approach for your scenario. Keep all things minimal and work only with the data you really need.

I found this error cluttering my system logs:
May 26 06:39:16 zeus amavis[10981]: (10981-04) NOTICE: reconnecting in response to: err=2006, HY000, DBD::mysql::st execute failed: MySQL server has gone away at (eval 86) line 166, GEN54 line 5.

After a bit of debugging it turns out amavis keeps an open connection to mysql and depending on your wait_timeout setting it might lose the connection and need to reconnect.
In order to fix this you just need to increase your wait_timeout to a comfortable value.

mysql set global wait_timeout=28800;
Query OK, 0 rows affected (0.00 sec)

You also need to edit your my.cnf file to make sure the change is persistent across mysql restarts.