huge performance gap with nodejs and postgresql benchmark

Question

I am creating an application with nodejs, it is using Hapi for web framework and knex for sql builder, The primary code is following:

server.route({
  method: 'POST',
  path: '/location',
  config: {
    tags: ['api'],
    validate: {
      payload: {
        longitude: Joi.string().regex(/^\d+\.\d+$/).required(),
        latitude: Joi.string().regex(/^\d+\.\d+$/).required(),
        date: Joi.string().regex(/^\d{4}-\d{1,2}-\d{1,2}\s\d{1,2}:\d{1,2}.+$/).required(),
        phone: Joi.string().required(/^1\d{10}$/)
      }
    }
  },
  handler: createLocation
})


async function createLocation(request, reply){
  try{
    const data = await knex('locations').insert(request.payload)
    reply(data)
  }catch(error){
    reply(error)
  }
}

It simply insert some date to postgresql. I am using Wrk to benchmark it Concurrent throughput in Google Compute Engine(cheapest machine), Result:

$ wrk -c 100 -t 12 http://localhost/api/location -s wrk.lua

Running 10s test @ http://panpan.tuols.com/api/location
  12 threads and 100 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   542.22ms  102.93ms   1.02s    88.32%
    Req/Sec    21.95     18.74    70.00     78.71%
  1730 requests in 10.02s, 0.94MB read
Requests/sec:    172.65
Transfer/sec:     96.44KB

Then I using pgbench to test postgresql insert performance:

$ pgbench  location -U postgres -h localhost -r -t 1000 -f index.sql -c 10

transaction type: index.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
latency average = 1.663 ms
tps = 6014.610692 (including connections establishing)
tps = 6029.973067 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         1.595  INSERT INTO "public"."locations"("phone", "longitude", "latitude", "date", "createdAt", "updatedAt") VALUES('18382383428', '123,33', '123.33', 'now()', 'now()', 'now()') RETURNING "id", "phone", "longitude", "latitude", "date", "createdAt", "updatedAt";

The nodejs is 172.65 req/s, and the postgresql internal native is 6000 req/s, The are actually do some thing, if ignore the http overhead, the difference should not so much big, Why the performance is so much hug different? It is nodejs or node-pg package problem?

Am I wrong or are you comparing the whole HTTP request handling with just the SQL execution ? Not saying that there aren't performance problems related to node-pg (there are in my opinion), but this doesn't look like a precise enough benchmark. — Denys Séguret, Mar 18 '17 at 09:16
So what *is* the difference if you ignore the HTTP overhead? — JJJ, Mar 18 '17 at 09:37
Knex is a known performance hog. If you want to evaluate properly how much data you can insert in a single run, you should use this approach: http://stackoverflow.com/questions/37300997/multi-row-insert-with-pg-promise — vitaly-t, Mar 18 '17 at 13:41

huge performance gap with nodejs and postgresql benchmark

0 Answers0