Reading Ernie’s post today inspired me to write about something I’ve been wanting to write about for a while: how much of a performance impact you should expect from network latency.

Hypothetical Numbers

I’m going to throw out some web-application ‘back of the envelope’ numbers which I will then use for examples:

The goal is to generate a page within 200-500ms .

. Network latency between the application server and DB server is 0.5-1ms.

There will be use-cases which have goals far more agressive than this, for example advertising server goals are closer to 50ms. I have also seen network latency well below 1ms. I encourage commenters to correct me where they disagree 😉

Analysis

So if we say that a typical web page requires 5-10 queries to render, you can see that the amount of time spent in network latency could actually be very low:

10 queries @ 1ms each = 10ms, or 5% of our 200ms goal. 10 queries @ 0.5ms each = 5ms, or 2.5% of our 200ms goal.

However, what I want to do is illustrate the effects of the N+1 query pattern. That is, some applications will naively repeat a query inside a looping structure. So using the numbers we have established, lets take a look at the difference between:

SELECT * FROM Country – 239 rows in 1 query.

– 239 rows in 1 query. SELECT * FROM Country WHERE code = '?' – 239 queries with one row each.

Using performance_schema + ps_helper , I can show the time that it takes to execute on the server:

mysql> select * from statement_analysis\G *************************** 4. row *************************** query: SELECT * FROM `country` full_scan: * exec_count: 3 err_count: 0 warn_count: 0 total_latency: 3.41 ms max_latency: 1.17 ms avg_latency: 1.14 ms rows_sent: 717 rows_sent_avg: 239 rows_scanned: 717 digest: 53567ecd08977b34a4532202a10871f4 *************************** 6. row *************************** query: SELECT * FROM `country` WHERE CODE = ? full_scan: exec_count: 5 err_count: 0 warn_count: 0 total_latency: 1.19 ms max_latency: 249.08 us avg_latency: 238.78 us rows_sent: 5 rows_sent_avg: 1 rows_scanned: 5 digest: cc32c7d6ec9e2803dea1ff95f458520a

Because the SELECT * FROM Country retrieves every row, it is going to be a table scan (which is quite efficient in this case). We can see that the query time is 1.14ms to retrieve all 239 rows versus 0.239ms to retrieve just a single row.

Retrieving all 239 rows from MySQL as primary key lookups takes 0.239ms * 239 = 57ms.

However, once you account for 0.5ms network latency:

* The single step retrieval is 1.14 + 0.5 = 1.64ms.

* Row-at-a-time retrieval is 119.5ms + 57ms = 176.5ms.

And with 1ms network latency:

* The single step retrieval is 1.14 + 1 = 2.14ms.

* Row-at-a-time retrieval is 239ms + 57ms = 296ms.

This example is largely the “worst-case” situation, which I am using to illustrate an example. 2.14ms versus 296ms isn’t even comparable 🙂 To apply context – this difference will basically double my page generation goal of 200-500ms.

Merging Queries

My next example to run through, is what happens when we merge queries together. In this case retrieving multiple rows via primary key at once:

SELECT * FROM Country WHERE code IN ('AUS', 'USA', 'CAN');

instead of:

SELECT * FROM Country WHERE code = 'AUS'; SELECT * FROM Country WHERE code = 'USA'; SELECT * FROM Country WHERE code = 'CAN';

We have the time for the single row query above. Here is the timing information from ps_helper for the IN list query:

*************************** 8. row *************************** query: SELECT * FROM `Country` WHERE CODE IN (...) full_scan: exec_count: 4 err_count: 0 warn_count: 0 total_latency: 1.19 ms max_latency: 318.66 us avg_latency: 296.59 us rows_sent: 12 rows_sent_avg: 3 rows_scanned: 12 digest: b19ca11697506fac486dd35535c37c32

With 0.5ms network latency total time is:

* (0.29659ms + 1 roundtrip) = 0.8 ms total

* (0.23878ms x 3 + 3 roundtrips) = 2.2ms total

With 1ms network latency:

* (0.29659ms + 1 roundtrip) = 1.3 ms total

* (0.23878ms x 3 + 3 roundtrips) = 3.7ms total

So by merging 3 queries together, I very nearly received a 3x reduction in query time.

Another was to look at this, is that for simple queries the execution cost can be near-free on the MySQL side. Network latency really can matter.