ErikHoogeboom

In the previous part I explained how we used an iterator to improve the Time to First Byte (TTFB) for our search result pages which we generate using Server-Side Rendering (SSR). The way this iterator is used enables a type of lazy loading. It improves the Time to First Byte by executing an expensive call to load the result products while rendering the page instead of before rendering it. Now that the products are loaded halfway the rendering of the html, this introduces a new phenomenon: a small yet very much noticeable pause as soon as the iterator starts loading the 40 products to show on the search result page. 

This pause happens when the page looks somewhat like this:

After the short pause, rendering continues and the products are shown on the result page:

Even though the pause is relatively short (somewhere between 100-400ms), the pause hurts an important performance metric: the largest contentful paint (LCP).

What is the ‘Largest Contentful Paint’?

According to web.dev, the Largest Contentful Paint ”marks the point in the page load timeline when the page’s main content has likely loaded—a fast LCP helps reassure the user that the page is useful”. In other words: the Largest Contentful Paint is the moment in time where the page seems finished loading. You will want this to be as quickly as possible to avoid annoying the user with a seemingly slow website. 

This means it is crucial to get as many pixels of the first screen in their final state as quickly as possible. By the ‘first screen’ I mean what the user sees without scrolling, also known as ‘above the fold’. Of course, whatever happens below the fold is also important, but not as important as what happens above it.

If the search result page pauses loading for a bit when a large part of the screen is still empty, this will negatively affect the LCP. After all; the LCP counts when most of the screen seems to have reached its final state.

This specific problem with our search result page only applies to desktop browsers. That’s because on a mobile browser the first search result is just outside the first view. Or, if you will, just below the fold. That is why the LCP on mobile devices is already much better than on desktop browsers. But since a significant part of our traffic comes from desktop browsers, this problem needed a solution.

To improve the LCP we modified the iterator that is returned when the templates start looping over the 40 products in the ProductList. 

Before the changes this is how the Renderer interacts with the ProductList:

As you can see, the first time an iterator is created for the ProductList is the moment when the 40 products are loaded using getProducts(). After that, the products can be easily iterated over by getting an iterator from an ArrayList that holds the 40 loaded products. But loading the 40 products takes up a large part of the time spent before the LCP.

Batched loading

In order to improve the LCP we want to load the first two rows of products (8 in total) as soon as possible. The remaining 32 products are less important as they are only needed below the fold. To accomplish this we want to load the products in batches of 8 products at a time.

If we want to have more control over which of those 40 products are loaded – and when to load them, we have to implement a custom iterator. 

In our case (using Java with Freemarker), when a template starts to loop over a list of items, the template engine sees that the ProductList implements the Iterable interface, so it uses the iterator() method to create a new iterator for the list. Earlier, this is where we used to load all 40 products (if not done already) and then call iterator() on the filled-up ArrayList holding the 40 products. If we want the products to be loaded in batches of 8 products at a time, we have to implement an actual iterator to do so. 

In the final solution, a custom ProductListIterator is created when the iterator() method on the ProductList is called. Upon creation, the following parameters are passed to the iterator: the ArrayList holding the 40 product keys, a reference to the ProductService where to load products and a reference to a Map holding already-loaded products. This last parameter prevents products being loaded twice when the ProductList is iterated over more than once.

The following diagram shows the final solution:

When iterating over a list, the hasNext() and next() are called for each iteration. The call to hasNext() only peeks if there is another item to iterate over and the call to next() makes the next item ‘current’. In our custom iterator, we use an internal index to keep track of the current product. It is a zero-based integer so we can use it to get the current product’s key from the ArrayList holding the 40 product keys. 

A call to next() first increments the internal pointer. After that, the current product key is retrieved from the list of product keys. If the product for the current key is not already loaded, a batch of to-be-loaded product keys is being collected. This batch contains the current product key and the next 7 keys. Then, the products are retrieved from the ProductService and stored into the Map holding already loaded products. Finally, the current product is taken from the map to be used as return value. 

In the following 7 calls to next() the then current product will have been loaded already so its key will already exist in the Map holding already loaded products. These calls to next() will not cause any products to be loaded and will return a product immediately. 

When the next() method is called for the 9th time, another 8 products will be loaded using getProducts(). And so forth, until all 40 products have been iterated over. 

When the ProductList is iterated over for a second time, all products will have been loaded already so they will not be loaded again.

In summary, we were able to dramatically improve both the Time to First Byte (TTFB) and Largest Contentful Paint (LCP) of our search result pages by utilizing a custom iterator. And we did not have to change a single template to accomplish this.

In this post I will explain how we were able to improve the performance of our website by using iterators. In the first part I will show how we used an iterator to improve the time to first byte (TTFB) for some specific pages. In the second part I will show how we modified our iterator to improve the largest contentful paint (LCP) of those pages.

Part 1: improving TTFB

The time to first byte (TTFB) is an important metric to determine a website’s responsiveness. In general, web.dev recommends a TTFB of 0.8 seconds or less.
Of course we followed the general recommendations for improving TTFB but even after that we noticed that especially the search engine result pages (SERP) had a relatively bad TTFB. In order to find out what was causing this poor performance, we added logging in several places and were quickly able to find out exactly where most of the processing time was spent.

Like most dynamic web sites, we use a template engine to render the html. In the following diagram (which is of course a very much simplified version of what is actually going on) you can see which methods are being executed in order to render a search engine result page:

As you can see, the RequestController is being called to serve the request. It figures out which page is being requested and collects all data required to render that page. As soon as all data has been collected the execution is passed to the Renderer which is when the first bytes are sent back to the client.

When a product search page is being requested the SolrClient is called to get the first 40 results to be shown on the requested search result page. Since our Solr instance does not actually store the complete product objects, only the product ids are used from the search result. The next step is to have the ProductService load the 40 product objects using those 40 product ids. 

This is where most of the time is spent. In our case, loading those 40 products typically takes anywhere between 100ms and 400ms. Not only does this vary (by a lot), but it is obviously too much processing time before the first byte. In comparison, fetching search results from Solr takes only about 20ms. 

Loading those 40 product objects takes that much time because they are complex objects. Retrieving a single product from the database takes a few queries in different database tables. On top of that, there are 2 layers of object cache. But with a product catalog of over a million products, a search query can easily yield results that are not in any cache and have to be retrieved from the database. 

Since the process of loading products from the database is already very much optimized using combined queries, there was no way to further optimize this. The only way forward… was to load nothing at all. At least, not before TTFB. After all, the actual products are not yet needed before the template can start rendering and the first bytes of HTML can be sent to the user. 

The first idea to optimize the TTFB was to change two things: 

1. Pass only a list of product ids to the template
2. Have the template load the products at the time they are needed

This approach had a number of drawbacks:

• This required building a custom template function and explaining template developers how and when to use this
• The whole operation requires coordination of when to change the templates and when to stop loading products before TTFB
• We did not have a clear view which templates had to be changed

Especially that last one was a deal breaker. Our website uses search results on many places. For instance on the home page or on category pages showing the first 10 results of a search request for those categories. Those inline search results use the same data structure as the normal search result page. So in order to eliminate loading products before TTFB, we would have to change a lot of templates. This would have required even more coordination of when to have templates changed and when to stop loading products before TTFB.

Ideally the solution would not require to change anything in any template. This is why using a custom iterator is such a valuable solution. No template has to be changed and the application developer can control the entire change.

So what exactly is an iterator? 

In simple terms: it is an object that implements a predefined way of iterating over it. The actual implementation differs from one programming language to another, but in pretty much any modern language you can work with custom iterators. I say custom because all of these languages already come shipped with standard iterators. Those standard iterators are used when iterating over a simple list or array. These are so common that you probably never realized you are actually already using iterators all the time. 

Template engines in Java (JSP, Velocity, Freemarker), PHP (Twig, Blade), Python (DTL, Jinja) all support working with iterators in their loop or foreach directives. All of these template engines will keep working if you drop-in replace an existing list or array with an iterator. Or, depending on the language, an iterable object.

In our case, the actual solution was to change the existing list of products by a custom ProductList. This is a custom class that implements the Collection interface so it can be used in a loop directive in our templates. The list initially only holds 40 product ids and a reference to the ProductService where it can retrieve the actual product objects from. As soon as any template starts iterating over the list, it will check if the 40 products have been loaded already and it will load them if necessary. This allows the ProductList to be iterated over multiple times. The products will be loaded only once, but no sooner than actually required. This is also known as ‘lazy loading’.

When loading the 40 products is no longer needed before TTFB but done implicitly by the templates when they start iterating over the ProductList, the process now looks more like the following diagram:

As you can see, the time-consuming getProducts() method is no longer called before TTFB. It is now being called from inside the rendering process whenever a template starts iterating over the ProductList for the first time. This will cause the call to render() to take more time than before, but the TTFB will be a lot better than before.

In the next part I will explain how we were able to improve the Largest Contentful Paint (LCP) metric for the same search result page by improving the inner workings of this ProductList. 

Gisteren was ik bij een meetup van Recommendation Systems Amsterdam (http://www.recommenders.nl/). Dit was de eerste die ze via meetup.com hadden georganiseerd (via die site werd ik er op geattendeerd) en dat heeft er voor gezorgd dat deze editie drukker was bezocht dan voorgaande en dat er ook meer developers aanwezig waren. Bij eerdere edities waren het met name mensen vanuit de academische wereld dus men was blij dat het nu diverser publiek was.

De eerste spreker (Nava Tintarev) hield een pleidooi voor het correct ‘uitleggen’ van recommendations. Door uit te leggen waarom je een bepaalde aanbeveling doet kun je de verwachtingen beter managen en voorkom je teleurstellingen. Het blijkt dat hoe ‘persoonlijker’ je de aanbeveling uitlegt (bijvoorbeeld: ‘… omdat je een man van 36 bent die graag Grolsch drinkt tijdens een actiefilm’) hoe groter de kans op teleurstelling. Het is dus nuttig om na te denken of en welke uitleg nodig is. Bij het gebruik van ‘anderen bekeken ook’ aanbevelingen bestaat de kans op filter-bubbels. Dit valt te doorbreken door te variëren en meer ‘novel’ aanbevelingen te doen. Met name bij novel recommendations is uitleg belangrijk. Maak gebruik van feedback-loops om beslismodellen te verbeteren. Evalueren werkt het best door mensen persoonlijk te vragen naar hun mening over de voorgestelde items. Hou bij het ontwerpen van je interfaces rekening met ruimte voor de uitleg. Deze vullen namelijk de ‘information gap’.

De tweede spreker (Daan Odijk) werkt als lead data-scientist bij Blendle. Men maakt daar uiteraard ook gebruik van recommendations om gebruikers dagelijks in de mailing te attenderen op mogelijk interessante artikelen. Het probleem daarbij is dat die artikelen op het moment van versturen (8:00) nog door bijna niemand zijn bekeken, dus het is vrijwel onmogelijk om aanbevelingen te doen op basis van views door anderen. Om toch selecties te kunnen maken gebruikt men diverse tools om de inhoud van artikelen te classificeren op basis van auteur, lengte, taalgebruik, trefwoorden etc etc. Deze data wordt realtime verwerkt op het moment dat de artikelen beschikbaar komen. Daarnaast wordt dagelijks de gebruikersprofielen verrijkt met allerhande data die men over gebruikers verzamelt; welk soort (lengte, auteur, medium, etc etc) artikelen lees je, voor welk soort recommendations ben je gevoelig, hoe lang zit je op de site etc etc. Vervolgens kan men bij het versturen razendsnel voor elke gebruiker de persoonlijke aanbevelingen voor de nieuwsbrief bepalen. Er vindt dagelijks een (automatische) update van het selectiemechanisme plaats zodat het bij elke variatie kan leren (welke variant converteert beter) en zo kan optimaliseren.

De laatste sprekers waren Barend Linders and Robbert van Waardhuizen van NPO. Ze gaven een indruk van het recommendation-systeem dat in de app en op de site wordt gebruikt om aanbevelingen te doen zodra je een aflevering hebt bekeken of pauzeert. Er worden op dit moment alleen aanbevelingen gedaan op ‘serie’ niveau en niet op ‘aflevering’ omdat je anders natuurlijk veel afleveringen zult voorschotelen (uit dezelfde serie) die men natuurlijk al had gezien. Daarnaast probeert men te voldoen aan de missie van de NPO om mensen een zo breed mogelijk aanbod aan programma’s voor te schotelen. Door aanbevelingen te doen die zo goed mogelijk passen bij wat ze zojuist hebben bekeken creëer je echter een filter bubbel en zal men weinig nieuws ontdekken. Op dit moment worden nog geen aanbevelingen gedaan op user-niveau (‘aanbevolen voor jou’) maar puur item-based recommendations (‘anderen bekeken ook’). Een van de redenen hiervoor is dat het lastig blijkt om gebruikers te ‘volgen’ over de site en over meerdere sessies. In de nabije toekomst komt er een verfijnder systeem voor het toestemming geven van tracking cookies en de verwachting is dat men dan gebruikers beter kan volgen en dus betere aanbevelingen kan doen.

Elke programmeertaal heeft wel een functie om een random getal te genereren. Maar de uitkomst van zo’n functie is minder random dan je misschien zou verwachten. Sterker nog; de uitkomst is zelfs goed voorspelbaar. Dat betekent dat als je random-functies gebruikt voor bijvoorbeeld sessie-cookies of andere security-gerelateerde zaken je daar rekening mee zou moeten houden.

Het probleem zit hem er in dat een computer (of eigenlijk een CPU) geen functie kent voor een compleet willekeurig getal. Dus wordt er gebruik gemaakt van ‘pseudo-random’ functies (die term zul je ook tegen komen in de handleiding van random-functies in elke programmeertaal). Met pseudo-random wordt bedoeld dat het op het oog willekeurig lijkt, maar het in werkelijkheid niet is. Zulke functies maken gebruik van een ‘teller’ (ook wel ‘seed’ genoemd) op basis waarvan het volgende getal wordt berekend. Dat betekent dat als je 2x achter elkaar een random getal opvraagt bij dezelfde ‘seed’ je ook 2x het zelfde ‘willekeurige’ getal krijgt.

Het volgende script laat dat zien:

<?php
srand(12345);

for ($i = 0; $i < 10; $i++) {
    echo rand(0, 9);
}

echo " ";

srand(12345);

for ($i = 0; $i < 10; $i++) {
    echo rand(0, 9);
}

De output hiervan is (hoe vaak je het ook draait):

1312002032 1312002032

Waarom lijkt een random-functie bij normaal gebruik dan toch random? Dat komt omdat de meeste random-implementaties automatisch een ‘seed’ instellen zodat de kans dat je 2x een aanroep met dezelfde seed doet niet zo heel groot is. Vaak wordt de huidige tijd (in milliseconden) gebruikt als seed.

Als je sessie-ids (of andere ‘random’ strings) genereert met een standaard random-functie werkt dat dus beperkend in het aantal unieke strings dat je zou kunnen genereren, ongeacht de lengte van je unieke string.

Bijvoorbeeld: als je strings van 10 tekens samenstelt uit de reeks A t/m Z heb je theoretisch 26 ^ 10 unieke strings (141.167.095.653.376 mogelijkheden). Maar als een seed een 32-bits integer is, zijn er dus maar 2 ^ 32 unieke strings (4.294.967.296) als uitkomst mogelijk.

Is dat erg? Dat ligt er aan. Als van de buitenkant niet zichtbaar (of te achterhalen) is op welke manier de sessie-ids worden gegenereerd, zal een kwaadwillende alsnog alle combinaties moeten proberen om er eentje te kunnen overnemen. Maar als bekend is op welke manier de strings tot stand komen kun je (offline, dus met veel rekenkracht) een tabel aanleggen met alle mogelijke combinaties met elke mogelijke input-seed. En als je vervolgens de site bezoekt en je krijgt een bepaalde sessie-id toegewezen kun je in die tabel opzoeken welke sessie-sleutels er ‘in de buurt’ liggen op basis van de seed (die afhangt van het tijdstip). En dan wordt de kans op een gestolen sessie-sleutel ineens een stuk groter.

Een oplossing zit hem er in door voor security-gerelateerde zaken andere random-functies te gebruiken. Deze kun je vinden onder de noemer ‘cryptographically secure pseudo-random number generators’ voor bijvoorbeeld PHP (CSPRNG) of Java (SecureRandom).

Zo af en toe leer je iets over een ontwikkel-taal of -omgeving waarvan je je afvraagt waarom je dat niet een paar jaar eerder wist. Dat overkwam mij toen ik bezig was met een stuk javascript waarbij ik een class had waarvan ik een methode als callback handler wilde gebruiken.

Om het probleem (en de oplossing) te illustreren heb ik een simpel voorbeeld gemaakt van zo’n situatie. Een class die de totale oppervlakte (in pixels) kan berekenen van plaatjes.

function ImageAreaCalculator(counterElement) {
    this.totalArea = 0;
    this.counterElement = counterElement;

    this.addImage = function(src) {
        var img = document.createElement('img');
        img.src = src;
        img.onload = this.onloadImage;
        document.appendChild(img);
    };

    this.addDimensions(width, height) {
        this.totalArea += (width * height);
    };

    this.updateCounter = function(counterElement) {
        counterElement.innerHTML = this.totalArea;
    }; 

    this.onloadImage = function(e) {
        var img = e.target;
        this.addDimensions(img.width, img.height);
        this.updateCounter(this.counterElement);          
    };

}

var counterElement = document.createElement('div');
document.appendChild(counterElement);
var calc = new ImageAreaCalculator(counterElement);
calc.addImage('foo.gif');

Bovenstaande code werkt helaas niet. In de methode onloadImage() zit een aanroep naar ‘this’ en op het moment dat die regel wordt uitgevoerd is ‘this’ niet meer het object ‘calc’ maar het ingeladen plaatje ‘img’.

Een workaround is dat je het object waar je de callback iets mee wilt laten doen injecteert in het img-object. De callback handler haalt dat object vervolgens weer uit het target dat wordt meegegeven in het event-object:

this.addImage = function(src) {
    var img = document.createElement('img');
    img.calc = this; 
    img.src = src;
    img.onload = this.onloadImage;
    document.appendChild(img);
};
this.onloadImage = function(e) {
    var img = e.target;
    var calc = img.calc;
    calc.addDimensions(img.width, img.height);
    calc.updateCounter(calc.counterElement); 
};

Niet echt fraai. Deze opzet vereist namelijk een ongeschreven afspraak tussen de aanroeper en callback dat er een magisch ‘calc’ object wordt gezet in het event-target.

Er is echter een manier om de onload-callback te maken zoals in dit geval gewenst is; dat ‘this’ ook echt verwijst naar de juiste instantie. Daarvoor is de bind() methode die bestaat voor elke functie (ja; ik was ook verbaasd dat een functie automatisch van bepaalde functies wordt voorzien).

Door het aanroepen van bind() op een verwijzing naar een functie creëer je een nieuwe callback die de waarde voor ‘this’ instelt op de meegegeven variabele. Hier een voorbeeld van zo’n callback:

this.addImage = function(src) {
    var img = document.createElement('img');
    var callback = this.onloadImage.bind(this);
    img.src = src;
    img.onload = callback;
    document.appendChild(img);
};
this.onloadImage = function(e) {
    var img = e.target;
    this.addDimensions(img.width, img.height);
    this.updateCounter(this.counterElement);          
};

Op het moment dat onloadImage() wordt aangeroepen heeft ‘this’ de waarde die je zou verwachten en werkt de aanroep naar addDimensions() en updateCounter() dus ook zoals je zou willen.

Het heeft even geduurd sinds het laatste blog maar ik liep al wel een tijdje met een onderwerp om over te bloggen. Het was me namelijk een keer opgevallen dat MySQL een index niet gebruikt als het type van de kolom niet overeenkomt met de waarde waar je op zoekt.

Neem bijvoorbeeld een tabel ‘book’ met een kolom ‘isbn’ en ‘title’:

CREATE TABLE IF NOT EXISTS `book` (
 `isbn` char(13) NOT NULL,
 `title` varchar(255) NOT NULL,
 PRIMARY KEY (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `book` (`isbn`, `title`) VALUES
('9789021400730', 'De Amerikaanse prinses'),
('9789045028163', 'Dit kan niet waar zijn');

Als je in deze tabel een record opzoekt aan de hand van het ISBN doe je dat normaal gesproken met de waarde tussen aanhalingstekens, want je zoekt immers op een string. In dat geval gebruikt MySQL ook netjes de gedefineerde index (de primary key in dit geval):

EXPLAIN SELECT * FROM `book` WHERE isbn = '9789045028163'

Maar als je per ongeluk (of omdat je ORM-tool niet goed werkt) zoekt op een integer, wordt de index niet gebruikt:

EXPLAIN SELECT * FROM `book` WHERE isbn = 9789045028163

Het resultaat van beide queries is hetzelfde; er zal in beide gevallen 1 record worden gevonden (Dit kan niet waar zijn) maar als er gebruik wordt gemaakt van de index (primary key) is dat natuurlijk veel sneller. Zeker als er duizenden records in je tabel zitten.

Als je werkt met datum/tijd velden in MySQL ligt een DATETIME type voor de hand. Om een DATETIME veld te vullen vanuit PHP kun je eenvoudig een iso-formaat als string (‘YYYY-MM-DD HH:II:SS’) gebruiken. In de meeste gevallen voldoet dat prima. Het wordt alleen lastig als je informatie uit verschillende tijdzones komt. Of wat als alle informatie uit 1 tijdzone komt maar een periode overspant waarbinnen de klok werd verzet?

Binnen een code-omgeving (PHP/Java/JavaScript) heb je meestal wel de mogelijkheid om te rekenen met datum/tijd objecten die van zichzelf weten over welke tijdzone het gaat en of het zomertijd was of niet. Meestal werken zulke objecten onder water met een unix-timestamp en wat meta-informatie over tijdzone en zomertijd. Maar hoe zit dat in MySQL?

Je kunt natuurlijk een integer-veld in MySQL maken en daarin de unix timestamp opslaan. Dan weet je zeker dat het het juiste tijdstip is (als het wegvallen van de tijdzone-informatie acceptabel is) en sorteren op die kolom gaat ook goed. Maar een unix timestamp is niet echt lekker leesbaar. Dan liever een echte datum/tijd kolom. Maar gaat dat altijd goed?

MySQL kent 2 mogelijkheden voor het opslaan van een datum/tijd in een enkel veld. Het kan door middel van een DATETIME en een TIMESTAMP kolomtype. Een DATETIME kolom kun je zien als een tekstueel veld. De waarde wordt opgeslagen hoe je het er in stopt en wordt ook weer zo weergegeven als hoe je het er in stopte. Een TIMESTAMP kolom werkt iets anders; bij het invoegen wordt de waarde geconverteerd naar een unix timestamp en bij het weergeven wordt deze waarde weer naar een tekstuele representatie omgezet.

Het verschil in werking zie je als je een tabel aanmaakt met zowel een DATETIME en een TIMESTAMP kolom:

CREATE TABLE `test_date` (
`timestamp_column` timestamp NULL DEFAULT NULL,
`datetime_column` datetime NULL
);

Vervolgens voegen we 3 tijdstippen in die elk een uur na elkaar plaatsvinden. Niet geheel toevallig kiezen we die rond het moment dat de klok een uur terug gaat (in de nacht van 24 oktober op 25 oktober 2015) en wel om 01:30, 02:30 en weer een uur later om 02:30 als de klok dus een uur terug is. Om er zeker van te zijn dat we die tijdstippen aanduiden geven we MySQL alleen waarden in een timestamp-notatie:

INSERT INTO test_date VALUES (FROM_UNIXTIME(1445729400), FROM_UNIXTIME(1445729400));
INSERT INTO test_date VALUES (FROM_UNIXTIME(1445729400 + 3600), FROM_UNIXTIME(1445729400 + 3600));
INSERT INTO test_date VALUES (FROM_UNIXTIME(1445729400 + 7200), FROM_UNIXTIME(1445729400 + 7200));

Als we kijken hoe MySQL dit heeft opgeslagen halen we de waarden weer op:

SELECT timestamp_column, datetime_column FROM test_date;

2015-10-25 01:30:00 2015-10-25 01:30:00
2015-10-25 02:30:00 2015-10-25 02:30:00
2015-10-25 02:30:00 2015-10-25 02:30:00

Dat lijkt er prima uit te zien: de tijdstippen kloppen allemaal. Als we echter de waarden terugrekenen naar unix timestamps (zoals we ze er in gestopt hebben) blijkt dat het niet meer helemaal klopt. Door gebruik te maken van de UNIX_TIMESTAMP() bij een TIMESTAMP kolom wordt de feitelijk opgeslagen waarde gebruikt dus kun je precies zien hoe die is opgeslagen. Bij de DATETIME kolom vindt er opnieuw conversie plaats (maar daarvan wisten we al hoe hij was opgeslagen).

SELECT UNIX_TIMESTAMP(timestamp_column), UNIX_TIMESTAMP(datetime_column) FROM test_date;

1445729400 1445729400
1445736600 1445736600
1445736600 1445736600

Wat is er mis gegaan? Je zou verwachten dat in ieder geval de TIMESTAMP notatie toch wel de juiste unix timestamp zou moeten hebben? Die kan onder water immers met timestamps werken, dachten we.

Het is mis gegaan bij het invoegen; daar wordt gebruik gemaakt van de FROM_UNIXTIME() functie die de ingevoerde unix timestamp omzet naar een ‘tekstuele’ representatie en maakt daarbij gebruik van de huidige tijdzone van de server. Als die hetzelfde is als van het tijdstip dat je probeert in te voegen gaat het goed, maar aangezien we deze query tijdens de zomertijd uitvoeren, wordt bij het terugrekenen van de tekstuele representatie naar een timestamp gewerkt met de zomertijd. En dus met het moment van voor het omzetten van de klok. Vandaar dat de laatste 2 rijen hetzelfde worden opgeslagen.

Om het derde tijdstip (in ieder geval in de TIMESTAMP kolom) juist op te slaan moeten we MySQL tijdelijk een andere tijdzone laten gebruiken:

SET time_zone=’+02:00′;
INSERT INTO test_date (timestamp_column, datetime_column) VALUES (FROM_UNIXTIME(1445729400), FROM_UNIXTIME(1445729400));
INSERT INTO test_date (timestamp_column, datetime_column) VALUES (FROM_UNIXTIME(1445729400 + 3600), FROM_UNIXTIME(1445729400 + 3600));

Alsof de klok een uur terug is gezet (om 03:00):

SET time_zone=’+01:00′;
INSERT INTO test_date (timestamp_column, datetime_column) VALUES (FROM_UNIXTIME(1445729400 + 7200), FROM_UNIXTIME(1445729400 + 7200));

Als we nu de waarden ophalen als tekstuele versie is er niks veranderd en lijken de waarden ook weer gewoon te kloppen:

SELECT timestamp_column, datetime_column FROM test_date;

2015-10-25 01:30:00 2015-10-25 01:30:00
2015-10-25 02:30:00 2015-10-25 02:30:00
2015-10-25 02:30:00 2015-10-25 02:30:00

Maar als we nu de waarden ophalen als unix timestamps zie je ineens het verschil tussen de TIMESTAMP en de DATETIME kolom:

SELECT UNIX_TIMESTAMP(timestamp_column), UNIX_TIMESTAMP(datetime_column) FROM test_date;

1445729400 1445729400
1445733000 1445736600
1445736600 1445736600

Daarmee lijkt het nuttig om voor datum/tijd velden waar rekenwerk mee plaats moet vinden altijd een TIMESTAMP kolom te gebruiken. Er zijn echter situaties waar dat niet helemaal werkt; als je in 1 statement meerdere waarden wilt meegeven worden ze geacht allemaal in dezelfde tijdzone (en zomertijd of niet) te zitten anders werkt het alsnog niet. En vergeet het beperkte bereik van een TIMESTAMP kolom niet; deze accepteert alleen waarden tussen 1970 en 2038.

Hoewel niet echt goed leesbaar zou ik er dan toch voor kiezen om de unix timestamp in een INT kolom te gaan opslaan. Maak er dan wel een UNSIGNED INT van, anders krijg je al snel een overflow.

Gebruik maken van een INT zorgt er in ieder geval voor dat het voor 100% duidelijk is welke datum/tijd waarde het is. En je kunt er veilig op sorteren en mee rekenen, ook binnen MySQL. En voor de leesbaarheid kun je natuurlijk ook met FROM_UNIXTIME() spieken wat je hebt opgeslagen.

Een week geleden kreeg ik inspiratie voor een leuk dataproject. Ik kwam er achter dat de Nederlandse Databank Wegverkeersgegevens (NDW) een bestand publiceert met daarin de actuele open/dicht status van een aantal bruggen in Noord- en Zuid-Holland.

Meteen had ik het idee om hiervan een historische database aan te leggen om op basis daarvan statistieken te berekenen. Hoe vaak zijn bruggen open? Hoe lang zijn ze dan open? Zijn ze in de spits nu echt zo vaak open? Etc etc.

Het periodiek importeren van de actuele statussen en deze in een database opslaan was zo geregeld. Een redelijk verbeus bestand terwijl er eigenlijk maar weinig informatie in zit; de bruggen die op dit moment open zijn, sinds wannneer ze open zijn (en mogelijk tot wanneer) en de GPS-coördinaten. Geen naam of plaats van de brug. Jammer. Nu bleken er zelfs na een tijdje niet zo heel veel verschillende bruggen in het bestand te hebben gezeten dus het werd een kwestie van eenmalig opzoeken welke bruggen het precies waren. Leuk klusje ook nog. Zo leer je iets van bruggen in plaatsjes waar je nog nooit van had gehoord.

Vervolgens bleek ook dat de actuele status niet zo actueel was als ik dacht. Het bleek een statisch bestandje dat elke 5 minuten werd geactualiseerd. En hoewel dat in principe vaak genoeg zou kunnen zijn, bleek de inhoud niet te gaan over de afgelopen 5 minuten maar een momentopname op het tijdstip van genereren. En aangezien een brug binnen die 5 minuten open en dicht zou kunnen zijn gegaan kan het gebeuren dat dat onopgemerkt bleef in de omliggende momentopnamen.

Dat laatste mag de pret niet drukken. De data wordt geïmporteerd en een eerste versie van de website is klaar: brugopen.nl. Daar staat in ieder geval al een overzicht van de bruggen die op dit moment open zijn en vervolgens een lijstje met alle bruggen die ooit in het bestand hebben gezeten met daarbij vermeld wanneer (en hoe lang) de brug voor het laatst open was.

Een volgende stap was het automatisch publiceren van status­wijzigingen (brug open, brug dicht) via Twitter. Dat bleek ook niet eens zo heel moeilijk, er is veel documentatie en voorbeeldcode beschikbaar om dit binnen korte tijd voor elkaar te krijgen.

Al met al dus binnen een week van idee naar realisatie.

Volg @OpenBrug op Twitter of check brugopen.nl.

Het blijkt dat mijn blogje ook door de Rabobank wordt gelezen. Schreef ik eerder al dat het een probleem kon op leveren als je automaticResponseUrl op HTTPS staat die alleen werkt met SNI (Server Name Indication), dat probleem blijkt nu opgelost.

In de logs zie ik dat de automatische requests nu worden uitgevoerd door Java 8, waar dat voorheen door Java 6 werd gedaan:

XXX.XXX.XXX.XXX – – [18/Jun/2015:14:42:05 +0200] “POST /?module=SHOP&action=ProcessPayment&orderId=XXX HTTP/1.1” 302 – “-” “Java/1.8.0_25”

Mooi dat het is opgelost.

Als je een certificaat aanvraagt krijg je bij de oplevering daarvan vaak een zip-bestand met daarin het certificaat, één of meer intermediate certificaten en een root-certificaat.

Het is echter raadzaam om het root-certificaat niet in te regelen in de webserver. Dat scheelt namelijk bandbreedte bij het opzetten van de SSL handshake en het voegt niks toe.

Dat zit zo; een browser is standaard voorzien van een verzameling root-certificaten die door de browser worden vertrouwd. Certificaten die zijn uitgegeven met die root-certificaten worden dus ook vertrouwd. En certificaten die zijn uitgegeven door intermediate certificaten die zijn ondertekend met een van de bekende root-certificaten worden ook vertrouwd. Enzovoort. Een certificaat uit een keten waarvan het root-certificaat niet wordt vertrouwd wordt door de browser niet vertrouwd, ook niet als je het meestuurt. Dus waarom zou je het meesturen?

Sterker nog; door het wel mee te sturen zorg je voor extra traffic bij het opzetten van de SSL verbinding. In een setup waar ik aan werkte was er sprake van een keten van in totaal 4 certificaten; het domein-certificaat, 2 intermediate certificaten en 1 rootcertificaat.

Met het volgende commando kon ik zien hoe veel bytes er werden verzonden bij het opzetten van de SSL handshake:

$ echo | openssl s_client -connect www.domein.nl:443 \
2>/dev/null | grep handshake
SSL handshake has read 6107 bytes and written 512 bytes

Volgens OpenSSL waren er dus 6107 bytes ontvangen en 512 bytes verstuurd. Dat lijkt niet veel, maar dat zijn dus al de nodige roundtrips alvorens er ook maar 1 HTTP request kan worden uitgevoerd.

Door het root-certificaat weg te laten uit de certificate chain werd dat al een stuk minder:

$ echo | openssl s_client -connect www.domein.nl:443 \
2>/dev/null | grep handshake
SSL handshake has read 5022 bytes and written 512 bytes

Dat scheelt dus dik 1000 bytes wellicht een aantal roundtrips.

Om te zien hoe veel TCP-pakketjes er worden verstuurd bij het opzetten van een SSL handshake kun je het volgende commando uitvoeren:

# tcpdump -ttttt -i any 'port 443 and host www.domein.nl'

Als je 2 terminal-vensters open hebt op een testclient kun je in de ene de tcpdump starten en in een ander venster kun je een SSL-verbinding opzetten:

openssl s_client -connect www.domein.nl:443

Dit commando blijft ‘hangen’ omdat het na het opzetten van de SSL-connectie wacht op input. Dat geeft je even de tijd om de output van tcpdump te bekijken.

In mijn situatie scheelde dit precies 1 roundtrip en een verwaarloosbare hoeveelheid tijd. Hoewel het in dit geval niet veel oplevert laat ik voortaan de root-certificaten achterwege. Hebben toch geen nut.