Re: performance for high-volume log insertion

On Wed, Apr 22, 2009 at 4:53 PM, James Mansion
<james(at)mansionfamily(dot)plus(dot)com> wrote:
> And I'm disagreeing with that.  Single row is a given, but I think you'll
> find it pays to have one
> round trip if at all possible and invoking multiple prepared statements can
> work against this.

You're talking about round-trips to a *local* server, on the same
system, not a dedicated server with network round-trips, right?

Blocking round trips to another process on the same server should be
fairly cheap--that is, writing to a socket (or pipe, or localhost TCP
connection) where the other side is listening for it; and then
blocking in return for the response. The act of writing to an FD that
another process is waiting for will make the kernel mark the process
as "ready to wake up" immediately, and the act of blocking for the
response will kick the scheduler to some waiting process, so as long
as there isn't something else to compete for CPU for, each write/read
will wake up the other process instantly. There's a task switching
cost, but that's too small to be relevant here.

Doing 1000000 local round trips, over a pipe: 5.25s (5 *microseconds*
each), code attached. The cost *should* be essentially identical for
any local transport (pipes, named pipes, local TCP connections), since
the underlying scheduler mechanisms are the same.

That's not to say that batching inserts doesn't make a difference--it
clearly does, and it would probably be a much larger difference with
actual network round-trips--but round-trips to a local server aren't
inherently slow. I'd be (casually) interested in knowing what the
actual costs are behind an SQL command round-trip (where the command
isn't blocking on I/O, eg. an INSERT inside a transaction, with no I/O
for things like constraint checks that need to be done before the
command can return success).

--
Glenn Maynard