epgsql/epgsql — pull request #15 by keynslug (2014-04-07 17:25:53+00:00)
Title: Boost selects performance through passive reading from socket

### Why

Essentially, because we store reasonably big blobs in postgres. Ran into this problem when I had exhausted myself waiting for epgsql to fetch 24 Mb out of a single field in a single table row. Though I believe this issue applies not only for my case which is considerably specific, to be honest.

### How

I had to use classic `{active, once}` strategy. Instead of collecting all the chunks of a reply which were easily flooding `pgsql_sock` message loop (since typical message chunk is 1.5k bytes thin) I managed to break into passive receive as soon as I received a single piece of reply, thus collecting whole reply in a tight `recv` loop.

### Then what

Hungering for assuring myself I've done everything right, first of all I had run all the tests. Since I'm on Mac OS X right now a part of test suite were failing constantly. Small part, to be fair. Lucky me, no previously succeeded tests appeared to be failing after my changes. Secondly, I wrote an incredibly simple performance test suite, like this:

```erlang
-module(pgsql_perf).
-include_lib("eunit/include/eunit.hrl").

%%

perf_test_() ->
[
{timeout, 60, prepare_data()},
{timeout, 60, get_data()}
].

drop_data_test_() ->
drop_data().

-define(noise_size, 10000000).

prepare_data() ->
{"insert blob", with_connection(fun (C) ->
Noise = crypto:rand_bytes(?noise_size),
{ok, [], []} = pgsql:squery(C, "create table test_big_blobs (id int4 primary key, noise bytea)"),
{ok, 1} = pgsql:equery(C, "insert into test_big_blobs (id, noise) values (1, $1)", [Noise])
end)}.

get_data() ->
{"get blob back", with_connection(fun (C) ->
{ok, _, [{Noise}]} = pgsql:equery(C, "select noise from test_big_blobs"),
?assertEqual(?noise_size, byte_size(Noise))
end)}.

drop_data() ->
{"cleanup", with_connection(fun (C) ->
{ok, [], []} = pgsql:squery(C, "drop table test_big_blobs")
end)}.

%%

with_connection(F) ->
with_connection(F, "epgsql_test", []).

with_connection(F, Username, Args) ->
Args2 = [{port, 5432}, {database, "epgsql_test_db1"} | Args],
fun () ->
{ok, C} = pgsql:connect("localhost", Username, Args2),
try
F(C)
after
pgsql:close(C)
end
end.
```

Clearly enough, the test inserts a 10 Mb blob of noise and selects it back right then, through another connection though. Ran it couple of times and got amazing results.

With current master:
```
==> epgsql (eunit)
======================== EUnit ========================
module 'pgsql_perf'
pgsql_perf: with_connection (insert blob)...[0.602 s] ok
pgsql_perf: with_connection (get blob back)...[20.614 s] ok
pgsql_perf: with_connection (cleanup)...[0.009 s] ok
[done in 21.235 s]
```

My branchie:
```
==> epgsql (eunit)
======================== EUnit ========================
module 'pgsql_perf'
pgsql_perf: with_connection (insert blob)...[0.637 s] ok
pgsql_perf: with_connection (get blob back)...[0.096 s] ok
pgsql_perf: with_connection (cleanup)...[0.009 s] ok
[done in 0.750 s]
```

### Summing up

Hopefully, I did not break anything other than tests covered up. Many times more frightening for me because such hand-patched `epgsql` lives in production for as long as three weeks now and feels itself healthy like never felt before. :sunny:

As usual I'm open up for discussion.