CODEWITHSUNDEEP
node.jspostgresql
Fernando Basso
Fernando Basso

Reputation: 708

nodejs + postgresql way too slow

I have this piece of code:

var pg = require('pg');
var QueryStream = require('pg-query-stream');
var constr = 'postgres://devel:[email protected]/tcc';
var JSONStream = require('JSONStream');
var http = require('http');

pg.connect(constr, function(err, client, done) {
    if (err) {
        console.log('Erro ao conectar cliente.', err);
        process.exit(1);
    }

    sql = 'SELECT \
          pessoa.cod, \
          pessoa.nome, \
          pessoa.nasc, \
          cidade.nome AS cidade \
          FROM pessoa, cidade \
          WHERE cidade.cod IN (1, 2, 3);';

    http.createServer(function (req, resp) {
        resp.writeHead(200, { 'Content-Type': 'text/html; Charset=UTF-8' });
        var query = new QueryStream(sql);
        var stream = client.query(query);

        //stream.on('data', console.log);
        stream.on('end', function() {
            //done();
            resp.end()
        });
        stream.pipe(JSONStream.stringify()).pipe(resp);
    }).listen(8080, 'localhost');
});

When I run apache bench on it, it get only about four requests per second. If I run the same query in php/apache or java/tomcat I get ten times faster results. The database has 1000 rows. If I limit the query to about ten rows, then node is double faster than php/java.

What am I doing wrong?

EDIT: Some time ago I opened an issue here: https://github.com/brianc/node-postgres/issues/653

I'm providing this link because I posted there some other variations on the code I have tried. Even with comments and hints so far, I have not been able to get a descent speed.

Upvotes: 3

Views: 7170

Answers (4)

dnozay
dnozay

Reputation: 24304

  • pg-query-stream uses cursors.
  • it uses cursors (bold for emphasis).
  • you can read the code and change batchSize to better fit your needs.

For those who don't know what cursors are, in short they are a trade-off for keeping memory footprint small and not reading a whole table in memory. But if you get 100 rows at a time when you have 1000 results, that's 1000 / 100 round-trips; so probably 10x slower than a solution not using cursors.

If you know how many rows you need, add a limit to your query, and change the number of rows returned each time to minimize number of roundtrips.

Upvotes: 6

Witold Szczerba
Witold Szczerba

Reputation: 121

As far as I can tell from this code, you create a single one connection to the PostgreSQL and everything gets queued through it.

The pg module allows for this, it's described here: https://github.com/brianc/node-postgres/wiki/Queryqueue

If you want a real performance, the for each HTTP request you should fetch the connection from the pool, use it, release it and make 101% sure you always release (e.g. proper exception handling) or your server will die once the pool gets completely exhausted.

Once you are there you can tweak the connection pool parameters and measure performance.

Upvotes: 1

huwence
huwence

Reputation: 326

Maybe you should set http.agent.maxSockets value, try this:

var http = require('http');
http.agent.maxSockets = {{number}};

default maxSockets is 5

Upvotes: 0

Stephen Punwasi
Stephen Punwasi

Reputation: 476

Looks like you're waiting for the server to be created before the request gets relayed. Try moving http.createServer outside of the call. If you only want to use the http server in the request, you should try making the calls async.

Upvotes: 0

Related Questions