I set up pg to replace a plain gdbm database for my application. But
even running to the same machine, via a unix socket
* the pg database ran 100 times slower
Across the net it was
* about 500 to 1000 times slower than local gdbm
with no cpu use to speak of.
I'd heard that networked databases are slow. I might have left it at
that if curiosity hadn't led me to write a network server for gdbm
databases, and talk to _that_ just to get a comparison.
Lo and behold and smack me with a corncob if it wasn't _slower_ than pg.
On a whim I mapped the network bandwidth per packet size with the NPtcp
suite, and got surprising answers .. at 1500B, naturally, the bandwidth
was the full 10Mb/s (minus overheads, say 8.5Mb/s) of my pathetic little
local net. At 100B the bandwidth available was only 25Kb/s. At 10B,
you might as well use tin cans and taut string instead.
I also mapped the network flows using ntop, and yes, the average packet
size for both gdbm and pg in one direction was only about 100B or
so. That's it! Clearly there are a lot of short queries going out and
the answers were none too big either ( I had a LIMIT 1 in all my PG
queries).
About 75% of traffic was in the 64-128B range while my application was
running, with the peak bandwidth in that range being about 75-125Kb/s
(and I do mean bits, not bytes).
Soooo ... I took a look at my implementation of remote gdbm, and did
a very little work to aggregate outgoing transmissions together into
lumps. Three lines added in two places. At the level of the protocol
where I could tell how long the immediate conversation segment would be,
I "corked" the tcp socket before starting the segment and "uncorked" it
after the segment (for "cork", see tcp(7), setsockopt(2) and TCP_CORK in
linux).
Surprise, ... I got a speed up of hundreds of times. The same application
that crawled under my original rgdbm implementation and under PG now
maxed out the network bandwidth at close to a full 10Mb/s and 1200
pkts/s, at 10% CPU on my 700MHz client, and a bit less on the 1GHz
server.
So
* Is that what is holding up postgres over the net too? Lots of tiny
packets?
And if so
* can one fix it the way I fixed it for remote gdbm?
The speedup was hundreds of times. Can someone point me at the relevant
bits of pg code? A quick look seems to say that fe-*.c is
interesting. I need to find where the actual read and write on the
conn->sock is done.
Very illuminating gnuplot outputs available on request.
Peter
