php developer blog

DNS Cache 1.5 released

by Dominik Jungowski

13. Juli 2009 Comments (10)

Today the latest version 1.5 of my Firefox plugin "DNS Cache" finally went public. Apart from Firefox 3.5 compatibility the following things have changed:

• Explicitly flush DNS cache when deactivating
• Toolbar Icon now has a context menu (right click): "Enable / Disable DNS Cache" and new:
• "Flush DNS Cache": Flushes the DNS Cache no matter if the cache is disabled or not
• Toolbar Icon now has to be double clicked in order to change the dns cache state

You can get the Firefox addon at the Firefox Add-ons page. If you've already installed it from there you should get the update automatically.

If you should experience any problems, please let me know!

MySQL: Pagination - SQL_CALC_FOUND_ROWS vs COUNT()-Query

by Dominik Jungowski

17. Juni 2009 Comments (13)

When using pagination for let's say a list of offers that where never clicked, you have to know the exact amount of offers (which were never clicked) in order to know how many pages you have. Now there are 2 possible ways of calculating the exact amount of offers: You can use either SQL_CALC_FOUND_ROWS or you can setup a second query with a COUNT() in it. I did the tests with SQL_NO_CACHE in order to get the best results possible. The clicks table has about 18.000.000 rows, the offer table about 800.000. Let's start with some time results.

Using SQL_CALC_FOUND_ROWS:

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 50,50;
50 rows in set (30.84 sec)

mysql> SELECT FOUND_ROWS();
1 row in set (0.00 sec)

Using a second query with COUNT():

mysql> SELECT SQL_NO_CACHE o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 50,50;
50 rows in set (0.03 sec)

mysql> SELECT SQL_NO_CACHE COUNT(o.offer_id)
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL;
1 row in set (30.97 sec)

At a first glance they look equally fast, both taking about 30 seconds. But: They are only equally fast, when query caching is turned off. Let's assume we're on a high-traffic website where performance matters, so we turn the query cache on. MySQL Query caching is like a key-value cache with the key being the EXACT query and the resultset being the value. Once we turn on the cache, the pagination is way faster with the second query using COUNT().

Why?

When using SQL_CALC_FOUND_ROWS the application has to calculate the found rows every single time we turn the page, because the query changes, while the COUNT()-Query always remains the same, meaning that its result comes from the query cache from the second time on. Let's emulate:

Using SQL_CALC_FOUND_ROWS:

mysql> SELECT SQL_CALC_FOUND_ROWS o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 50,50;
50 rows in set (31.13 sec)

mysql> SELECT FOUND_ROWS();
1 row in set (0.00 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 100,50;
50 rows in set (30.71 sec)

mysql> SELECT FOUND_ROWS();
1 row in set (0.00 sec)

Using a second query with COUNT():

mysql> SELECT o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 50,50;
50 rows in set (0.03 sec)

mysql> SELECT COUNT(o.offer_id)
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL;
1 row in set (31.11 sec)

mysql> SELECT o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 100,50;
50 rows in set (0.04 sec)

mysql> SELECT COUNT(o.offer_id)
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL;
1 row in set (0.00 sec)

Et Voilà! While the SQL_CALC_FOUND_ROWS Queries took more than one minute together, the queries with the second COUNT() query only took a bit more than 30 seconds together, meaning they are twice as fast.

I'm pretty sure there are situations where SQL_CALC_FOUND_ROWS is the way to go, but in cases like this one you definately wanna go for the COUNT()-Query.

Canonicals: What they are and how to use them

by Dominik Jungowski

10. Juni 2009 One Comment

Today I wanna give a short insight on canonicals. A canonical is a (fairly new) SEO item in the head section of a HTML document, which contains the parent URL. Let's say you have a page which contains a sortable list whose URL is www.chip.de/sortable-list. Besides AJAX-sorting you also have sorting with URLs like www.chip.de/sortable-list/order/price/dir/desc, which reloads the page showing the sortable list ordered by the price descending. Usually you either 2 would have pages in the Google index with the almost the same content or 1 page since you put noindex,nofollow in one of them. This is where canonical comes into play:

First off you leave the noindex, nofollow in the page with the sorted list. Second you put a canonical into the HTML head section telling search engines where it can find the original list!

<link rel="canonical" href="/sortable-list">

That way the sorted list URL will not only not be in the Google index but the original URL gets more "attention".

The latest Firefox versions already show if a canonical is defined:

P.S. It does no harm to put the canonical tag in the original page itself.

Scrumfails Part 2

by Dominik Jungowski

10. Juni 2009 No Comment

Sprint Planning I: The product owner presents an item with 8 storypoints. Not one team member can remember to have ever estimated that item and everyone agrees that it's way bigger than an 8. How the hell could that happen? We don't know. What did we do? We excluded the item from the sprint and set up a meeting to discuss the item.

Job offer at CHIP Online as PHP Developer

by Dominik Jungowski

5. Juni 2009 No Comment

If you speak german and you're looking for a job as PHP Developer, we are looking for one at CHIP Online in the price comparison team.

In case you are interested, click here.

Scrumfails Part 1

by Dominik Jungowski

4. Juni 2009 No Comment

Estimation meeting. Cards are layed off. One 5, two 8s, one 13. Question from the one with the 5: "Why 8?"

CLI: Catching Ctrl+C, kill commands and fatal errors

by Dominik Jungowski

31. Mai 2009 Comments (5)

Sometimes, when you write a Command Line (CLI) script, you want to catch Ctrl+C. Simple Example: Your script does some stuff on the database and you don't want to leave it in the state it had in the moment the script was killed, Ctrl+C'd or quit by a fatal error.

First off, we have to tell PHP to use ticks, in order for the catches to work (for more information on ticks check the pcntl_signal() documentation):

declare(ticks = 1);

Then, we add shutdown functions to the classes that have to rollback the state:

/**
 * Cleanup if process has been killed unexpectedly
 *
 */
private function shutdown()
{
    $this->getTargetDb()->rollBack();
    print('Script quit unexpectedly. Doing rollback' . PHP_EOL);
    exit();
}

/**
 * Method that is executed, when a Fatal Error occurs
 *
 */
public function fatalErrorShutdown()
{
    $lastError = error_get_last();
    if (!is_null($lastError) && $lastError['type'] === E_ERROR) {
        $this->shutdown();
    }
}

/**
 * Method, that is executed, if script has been killed by
 * SIGINT: Ctrl+C
 * SIGTERM: kill
 *
 * @param int $signal
 *
*/
public function sigintShutdown($signal)
{
    if ($signal === SIGINT || $signal === SIGTERM) {
        $this->shutdown();
    }
}

You shouldn't forget to include an exit() at some point in the shutdown methods or your script won't quit at all. In the example the shutdown() method (which is called by both shutdown methods, that will be registered afterwards) quits the script as soon as it's done.

As a last step, you only have to register your shutdown methods and you're done:

$sync = new Process_Sync();
// Catch Fatal Error (Rollback)
register_shutdown_function(array($sync, 'fatalErrorShutdown'));
// Catch Ctrl+C, kill and SIGTERM (Rollback)
pcntl_signal(SIGTERM, array($sync, 'sigintShutdown'));
pcntl_signal(SIGINT, array($sync, 'sigintShutdown'));

International PHP Conference Spring Edition Day 2

by Dominik Jungowski

27. Mai 2009 No Comment

Some words on sessions I attended on day 2.

OOP … aber richtig (Stefan Priebsch)

Some known basics about OOP. It was ok, but it really was nothing new.

Organized serendipity: Inside report from the core of PHP development (Lukas Smith)

Finally I know why the namespace separator discussion took so long ;-)

Multi-Master MySQL (Arne Blankerts)

While the session itself was rather disappointing, it got me thinking a lot about how it would be possible to set up a multi master architecture. If I ever have any good idea, I'll let you know ;-)

International PHP Conference Spring Edition Day 1

by Dominik Jungowski

26. Mai 2009 No Comment

Some words on the sessions I attended today, but first off: Since I only got here yesterday, I could not attend the workshop day.

MySQL Performance Tuning & Optimization Capsule (Sonali Minocha)

Some known stuff, a lot of new tweaks, optimizations and dos/don'ts I didn't know yet. For example PROCEDURE ANALYSE() was totally new to me, you would use it like this: select * from mysql.user procedure analyse(); Basically all this does is to check the results the query produces and it then tells you the optimal type you should use for each column, based on the result data. For more information check the documentation linked above. Furthermore the talk was about optimizing query cache, InnoDB specific optimizations, Index optimiazations and many more.

Sichere Applikationen auf Basis des Zend-Frameworks (Stefan Esser)

I was really looking forward to this session. Although most of the shown, like the proper use of Validators, Filters or how to access $_GET/$_POST/etc. variables correctly, there were things that were new to me, like Zend Frameworks build-in possibility against Cross-Site Request Forgery (CSRF). On one hand, I had hoped to learn a bit more, but on the other hand it maybe tells me that I'm on the right course… ;-)

Why Architecture in Web Development matters (Lars Jankowfsky)

Lars showed some really interesting basics for building a good architecture, spiced up with some examples of how they did it at Swoodoo. I already enjoyed Lars' article on architecture in the PHP magazin a while ago and this talk was a good extension to it. Generally it seems that SOA (Service Oriented Archicture) is a good way for abstraction in architecture, even though it makes integration testing harder, since you have to write more mock-up objects.

The Present and Future of PHP (Andrei Zmievski)

Everytime you think about coding in Ruby, Domo-Kun kills a kitten!

DTrace für AMP-Entwickler und Administratoren (Johannes Schlüter)

Johannes showed really nice ways to trace (and debug) processes (and with focus on php) not using strace, but Suns dtrace. It's possibilites really impressed me and there's not really anything I can say about it right now, apart from the fact that you need PHP 5.3 or MySQL 5.4 (there are backports on Solaris up to 5.1) to be able to use it for those two.

By the way: That was fun (in German) ;D