subject:"\[PERFORM\] performance for high\-volume log insertion"

David,

* da...@lang.hm (da...@lang.hm) wrote:
 in a recent thread about prepared statements, where it was identified 
 that since the planning took place at the time of the prepare you 
 sometimes have worse plans than for non-prepared statements, a proposal 
 was made to have a 'pre-parsed, but not pre-planned' version of a 
 prepared statement. This was dismissed as a waste of time (IIRC by Tom L) 
 as the parsing time was negligable.

 was that just because it was a more complex query to plan?

Yes, as I beleive was mentioned already, planning time for inserts is
really small.  Parsing time for inserts when there's little parsing that
has to happen also isn't all *that* expensive and the same goes for
conversions from textual representations of data to binary.

We're starting to re-hash things, in my view.  The low-hanging fruit is
doing multiple things in a single transaction, either by using COPY,
multi-value INSERTs, or just multiple INSERTs in a single transaction.
That's absolutely step one.

Adding in other things, where they make sense (prepared statements,
binary format for things you have as binary, etc) is a good idea if it
can be done along the way.

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

2009-04-22 Thread Simon Riggs


On Mon, 2009-04-20 at 14:53 -0700, da...@lang.hm wrote:

 the big win is going to be in changing the core of rsyslog so that it can 
 process multiple messages at a time (bundling them into a single 
 transaction)

That isn't necessarily true as a single big win.

The reason there is an overhead per transaction is because of commit
delays, which can be removed by executing

  SET synchronous_commit = off; 

after connecting to PostgreSQL 8.3+

You won't need to do much else. This can also be enabled for a
PostgreSQL user without even changing the rsyslog source code, so it
should be easy enough to test.

And this type of application is *exactly* what it was designed for.

Some other speedups should also be possible, but this is easiest. 

I would guess that batching inserts will be a bigger win than simply
using prepared statements because it will reduce network roundtrips to a
centralised log server. Preparing statements might show up well on tests
because people will do tests against a local database, most likely.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

On Wed, Apr 22, 2009 at 8:19 AM, Stephen Frost sfr...@snowman.net wrote:
 Yes, as I beleive was mentioned already, planning time for inserts is
 really small.  Parsing time for inserts when there's little parsing that
 has to happen also isn't all *that* expensive and the same goes for
 conversions from textual representations of data to binary.

 We're starting to re-hash things, in my view.  The low-hanging fruit is
 doing multiple things in a single transaction, either by using COPY,
 multi-value INSERTs, or just multiple INSERTs in a single transaction.
 That's absolutely step one.

This is all well-known, covered information, but perhaps some numbers
will help drive this home.  4 inserts into a single-column,
unindexed table; with predictable results:

separate inserts, no transaction: 21.21s
separate inserts, same transaction: 1.89s
40 inserts, 100 rows/insert: 0.18s
one 4-value insert: 0.16s
40 prepared inserts, 100 rows/insert: 0.15s
COPY (text): 0.10s
COPY (binary): 0.10s

Of course, real workloads will change the weights, but this is more or
less the magnitude of difference I always see--batch your inserts into
single statements, and if that's not enough, skip to COPY.

-- 
Glenn Maynard

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-22 Thread david


On Wed, 22 Apr 2009, Glenn Maynard wrote:


On Wed, Apr 22, 2009 at 8:19 AM, Stephen Frost sfr...@snowman.net wrote:

Yes, as I beleive was mentioned already, planning time for inserts is
really small.  Parsing time for inserts when there's little parsing that
has to happen also isn't all *that* expensive and the same goes for
conversions from textual representations of data to binary.

We're starting to re-hash things, in my view.  The low-hanging fruit is
doing multiple things in a single transaction, either by using COPY,
multi-value INSERTs, or just multiple INSERTs in a single transaction.
That's absolutely step one.


This is all well-known, covered information, but perhaps some numbers
will help drive this home.  4 inserts into a single-column,
unindexed table; with predictable results:

separate inserts, no transaction: 21.21s
separate inserts, same transaction: 1.89s


are these done as seperate round trips?

i.e.
begin send
insert send
insert send
..
end send

or as one round trip?

i.e.
begin;insert;insert..;end


40 inserts, 100 rows/insert: 0.18s
one 4-value insert: 0.16s
40 prepared inserts, 100 rows/insert: 0.15s


are one of these missing a 0?


COPY (text): 0.10s
COPY (binary): 0.10s

Of course, real workloads will change the weights, but this is more or
less the magnitude of difference I always see--batch your inserts into
single statements, and if that's not enough, skip to COPY.


thanks for this information, this is exactly what I was looking for.

can this get stored somewhere for reference?

David Lang
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

Glenn,

* Glenn Maynard (glennfmayn...@gmail.com) wrote:
 This is all well-known, covered information, but perhaps some numbers
 will help drive this home.  4 inserts into a single-column,
 unindexed table; with predictable results:

Thanks for doing the work.  I had been intending to but hadn't gotten to
it yet.

 separate inserts, no transaction: 21.21s
 separate inserts, same transaction: 1.89s
 40 inserts, 100 rows/insert: 0.18s
 one 4-value insert: 0.16s
 40 prepared inserts, 100 rows/insert: 0.15s
 COPY (text): 0.10s
 COPY (binary): 0.10s

What about 4 individual prepared inserts?  Just curious about it.

Also, kind of suprised about COPY text vs. binary.  What was the data
type in the table..?  If text, that makes sense, if it was an integer or
something else, I'm kind of suprised.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

2009-04-22 Thread Tom Lane

Stephen Frost sfr...@snowman.net writes:
 * Glenn Maynard (glennfmayn...@gmail.com) wrote:
 separate inserts, no transaction: 21.21s
 separate inserts, same transaction: 1.89s
 40 inserts, 100 rows/insert: 0.18s
 one 4-value insert: 0.16s
 40 prepared inserts, 100 rows/insert: 0.15s
 COPY (text): 0.10s
 COPY (binary): 0.10s

 What about 4 individual prepared inserts?  Just curious about it.

Also, just to be clear: were the 40 insert cases 40 separate
transactions or one transaction?  I think the latter was meant but
it's not 100% clear.

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-22 Thread James Mansion


Stephen Frost wrote:

You're re-hashing things I've already said.  The big win is batching the
inserts, however that's done, into fewer transactions.  Sure, multi-row
inserts could be used to do that, but so could dropping begin/commits in
right now which probably takes even less effort.
  
Well, I think you are seriously underestimating the cost of the 
round-trip compared
to all the other effects (possibly bar the commits).  When I tested the 
union insert

technique on SQLServer and Sybase I got measurable improvements going from
100 row statements to 200 row statements, though I suspect in that case the
per-statement overheads are quite high.  I expected improvements from 10 
to 20
row batches, but it carried on getting better for a long time after 
that.  The

Sybase parser runs out of workspace first.



No, as was pointed out previously already, you really just need 2.  A
  
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.



see if there's really much of a performance difference between a
50-insert prepared statement, and 50 1-insert prepared statements.  If
they're both done in larger transactions, I don't know that there's
really alot of performance difference.
  
I think you'll be surprised, but the only way is to test it.  And also 
the simple 50 row single
insert as text.  See if you can measure the difference between that and 
the prepared

statement.

storage overhead?  indexing overhead?  We're talking about prepared
statements here, what additional storage requirement do you think those
would impose?  What additional indexing overhead?  I don't believe we
actually do anything differently between prepared statements and
multi-row inserts that would change either of those.
  
That's my point.  You will brickwall on the actual database operations 
for execution
early enough that the efficiency difference between parse-and-execute 
and prepared
statements will be hard to measure - at least if you have multi-row 
statements.


But this really needs testing and timing.


Ah, latency is a reasonable thing to bring up.  Of course, if you want
to talk about latency then you get to consider that multi-insert SQL
will inherently have larger packet sizes which could cause them to be
delayed in some QoS arrangements.
  
No, I mean latency from round trips from the client to the server 
process.  I don't know why

you think I'd mean that.

As I said, most of this is a re-hash of things already said.  The
low-hanging fruit here is doing multiple inserts inside of a
transaction, rather than 1 insert per transaction.  Regardless of how
that's done, it's going to give the best bang-for-buck.  It will
complicate the client code some, regardless of how it's implemented, so
that failures are handled gracefully (if that's something you care about
anyway), but as there exists some queueing mechanisms in rsyslog
already, hopefully it won't be too bad.
  

I think you have largely missed the point. There are two things here:
1) how many rows per commit
2) how many rows per logical RPC (ie round trip) between the client
  and server processes

We are agreed that the first is a Very Big Deal, but you seem resistant to
the idea that the second of these is a big deal once you've dealt with 
the former.


My experience has been that its much more important than any benefits of
preparing statements etc, particularly if the use of a prepared 
statement can

make it harder to do multi-row RPCs because the protocol doesn't
allow pipelining (at least without things getting very hairy).

Clearly 'copy' is your friend for this too, at least potentially (even 
if it means

streaming to a staging table).


James


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

On Wed, Apr 22, 2009 at 4:07 PM,  da...@lang.hm wrote:
 are these done as seperate round trips?

 i.e.
 begin send
 insert send
 insert send
 ..
 end send

 or as one round trip?

All tests were done by constructing a file and using time psql -f 

 40 inserts, 100 rows/insert: 0.18s
 one 4-value insert: 0.16s
 40 prepared inserts, 100 rows/insert: 0.15s

 are one of these missing a 0?

Sorry, 400 * 100.  All cases inserted 4 rows, and I deleted all
rows between tests (but did not recreate the table).

-- 
Glenn Maynard

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-22 Thread Joshua D. Drake

On Wed, 2009-04-22 at 21:53 +0100, James Mansion wrote:
 Stephen Frost wrote:
  You're re-hashing things I've already said.  The big win is batching the
  inserts, however that's done, into fewer transactions.  Sure, multi-row
  inserts could be used to do that, but so could dropping begin/commits in
  right now which probably takes even less effort.

 Well, I think you are seriously underestimating the cost of the 
 round-trip compared

The breakdown is this:

1. Eliminate single inserts
2. Eliminate round trips

Yes round trips are hugely expensive. 

 
  No, as was pointed out previously already, you really just need 2.  A

 And I'm disagreeing with that.  Single row is a given, but I think 
 you'll find it pays to have one

My experience shows that you are correct. Even if you do a single BEGIN;
with 1000 inserts you are still getting a round trip for every insert
until you commit. Based on 20ms round trip time, you are talking
20seconds additional overhead.

Joshua D. Drake


-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

On Wed, Apr 22, 2009 at 4:37 PM, Stephen Frost sfr...@snowman.net wrote:
 Thanks for doing the work.  I had been intending to but hadn't gotten to
 it yet.

I'd done similar tests recently, for some batch import code, so it was
just a matter of recreating it.

 separate inserts, no transaction: 21.21s
 separate inserts, same transaction: 1.89s
 40 inserts, 100 rows/insert: 0.18s
 one 4-value insert: 0.16s
 40 prepared inserts, 100 rows/insert: 0.15s
 COPY (text): 0.10s
 COPY (binary): 0.10s

 What about 4 individual prepared inserts?  Just curious about it.

4 inserts, one prepared statement each (constructing the prepared
statement only once), in a single transaction: 1.68s

I'm surprised that there's any win here at all.

 Also, kind of suprised about COPY text vs. binary.  What was the data
 type in the table..?  If text, that makes sense, if it was an integer or
 something else, I'm kind of suprised.

Each row had one integer column.  I expect strings to be harder to
parse, since it's allocating buffers and parsing escapes, which is
usually more expensive than parsing an integer out of a string.  I'd
expect the difference to be negligible either way, though, and I'd be
interested in hearing about use cases where binary copying is enough
of a win to be worth the development cost of maintaining the feature.

On Wed, Apr 22, 2009 at 4:49 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Also, just to be clear: were the 40 insert cases 40 separate
 transactions or one transaction?  I think the latter was meant but
 it's not 100% clear.

One transaction--the only case where I ran more than one transaction
was the first.

-- 
Glenn Maynard

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

On Wed, Apr 22, 2009 at 4:53 PM, James Mansion
ja...@mansionfamily.plus.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 100 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
#include stdio.h
#include unistd.h
#include assert.h

main()
{
int parent[2];
int ret = pipe(parent);
assert(ret != -1);

int child[2];
ret = pipe(child);
assert(ret != -1);

int pid = fork();
assert(pid != -1);

if(pid != 0)
{
// parent
close(parent[0]);
close(child[1]);
int wfd = parent[1];
int rfd = child[0];

printf(go\n);
int i = 100;
while(i--)
{
char c = 1;
ret = write(wfd, c, 1);
assert(ret == 1);
ret = read(rfd, c, 1);
assert(ret == 1);
}
printf(done\n);
}
else
{
// child
close(parent[1]);
close(child[0]);
int wfd = child[1];
int rfd = parent[0];
int i = 100;
while(i--)
{
char c = 1;
ret = read(rfd, c, 1);
assert(ret == 1);
ret = write(wfd, c, 1);
assert(ret == 1);
}
}
}


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

* Glenn Maynard (glennfmayn...@gmail.com) wrote:
  separate inserts, no transaction: 21.21s
  separate inserts, same transaction: 1.89s
  40 inserts, 100 rows/insert: 0.18s
  one 4-value insert: 0.16s
  40 prepared inserts, 100 rows/insert: 0.15s
  COPY (text): 0.10s
  COPY (binary): 0.10s
 
  What about 4 individual prepared inserts?  Just curious about it.
 
 4 inserts, one prepared statement each (constructing the prepared
 statement only once), in a single transaction: 1.68s
 
 I'm surprised that there's any win here at all.

For a single column table, I wouldn't expect much either.  With more
columns I think it would be a larger improvement.

 Each row had one integer column.  I expect strings to be harder to
 parse, since it's allocating buffers and parsing escapes, which is
 usually more expensive than parsing an integer out of a string.  I'd
 expect the difference to be negligible either way, though, and I'd be
 interested in hearing about use cases where binary copying is enough
 of a win to be worth the development cost of maintaining the feature.

I've seen it help, but I was sending everything as binary (I figured,
once I'm doing it, might as well do it all), which included dates,
timestamps, IP addresses, integers, and some text.  It may have more of
an impact on dates and timestamps than on simple integers.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

On Wed, Apr 22, 2009 at 5:51 PM, Stephen Frost sfr...@snowman.net wrote:
 For a single column table, I wouldn't expect much either.  With more
 columns I think it would be a larger improvement.

Maybe.  I'm not sure why parsing (1,2,3,4,5) in an EXECUTE parameter
should be faster than parsing the exact same thing in an INSERT,
though.

 I've seen it help, but I was sending everything as binary (I figured,
 once I'm doing it, might as well do it all), which included dates,
 timestamps, IP addresses, integers, and some text.  It may have more of
 an impact on dates and timestamps than on simple integers.

Of course, you still need to get it in that format.  Be careful to
include any parsing you're doing to create the binary date in the
benchmarks.  Inevitably, at least part of the difference will be costs
simply moving from the psql process to your own.

-- 
Glenn Maynard

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-22 Thread david


On Wed, 22 Apr 2009, Glenn Maynard wrote:


On Wed, Apr 22, 2009 at 4:53 PM, James Mansion
ja...@mansionfamily.plus.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?


the use-case for a production setup for logging servers would probably 
include a network hop.


David Lang


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 100 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).



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

* Glenn Maynard (glennfmayn...@gmail.com) wrote:
 On Wed, Apr 22, 2009 at 5:51 PM, Stephen Frost sfr...@snowman.net wrote:
  For a single column table, I wouldn't expect much either.  With more
  columns I think it would be a larger improvement.
 
 Maybe.  I'm not sure why parsing (1,2,3,4,5) in an EXECUTE parameter
 should be faster than parsing the exact same thing in an INSERT,
 though.

Erm..  Prepared queries is about using PQexecPrepared(), not about
sending a text string as an SQL EXECUTE().  PQexecPrepared takes an
array of arguments.  That gets translated into a Bind command in the
protocol with a defined number of parameters and a length for each
parameter being passed.  That removes any need for scanning/parsing the
string sent to the backend.  That's the savings I'm referring to.

If you weren't using PQexecPrepared() (and using psql, you wouldn't
be..), then the difference you saw was more likely planning cost.

 Of course, you still need to get it in that format.  Be careful to
 include any parsing you're doing to create the binary date in the
 benchmarks.  Inevitably, at least part of the difference will be costs
 simply moving from the psql process to your own.

Sure.  What I recall from when I was working on this is that it wasn't
terribly hard to go from unix timestamps (epoch from 1970) to a PG
timestamp format (and there was nice example code in the backend) in
terms of CPU time.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

* da...@lang.hm (da...@lang.hm) wrote:
 On Wed, 22 Apr 2009, Glenn Maynard wrote:
 You're talking about round-trips to a *local* server, on the same
 system, not a dedicated server with network round-trips, right?

 the use-case for a production setup for logging servers would probably  
 include a network hop.

Sure, but there's a big difference between a rtt of 0.041ms (my dinky
home network) and 20ms (from my home network in Virginia to Boston).  I
wasn't intending to discount latency, it can be a concen, but I doubt
it'll be 20ms for most people..

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

2009-04-22 Thread david


On Wed, 22 Apr 2009, Stephen Frost wrote:


* Glenn Maynard (glennfmayn...@gmail.com) wrote:

On Wed, Apr 22, 2009 at 5:51 PM, Stephen Frost sfr...@snowman.net wrote:

For a single column table, I wouldn't expect much either.  With more
columns I think it would be a larger improvement.


Maybe.  I'm not sure why parsing (1,2,3,4,5) in an EXECUTE parameter
should be faster than parsing the exact same thing in an INSERT,
though.


Erm..  Prepared queries is about using PQexecPrepared(), not about
sending a text string as an SQL EXECUTE().  PQexecPrepared takes an
array of arguments.  That gets translated into a Bind command in the
protocol with a defined number of parameters and a length for each
parameter being passed.  That removes any need for scanning/parsing the
string sent to the backend.  That's the savings I'm referring to.


are you sure? I thought that what goes out over the wire is always text.

David Lang


If you weren't using PQexecPrepared() (and using psql, you wouldn't
be..), then the difference you saw was more likely planning cost.


Of course, you still need to get it in that format.  Be careful to
include any parsing you're doing to create the binary date in the
benchmarks.  Inevitably, at least part of the difference will be costs
simply moving from the psql process to your own.


Sure.  What I recall from when I was working on this is that it wasn't
terribly hard to go from unix timestamps (epoch from 1970) to a PG
timestamp format (and there was nice example code in the backend) in
terms of CPU time.

Thanks,

Stephen


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion


On Tue, 21 Apr 2009, Stephen Frost wrote:


David,

* da...@lang.hm (da...@lang.hm) wrote:

the database structure is not being defined by (or specificly for)
rsyslog. so at compile time we have _no_ idea how many variables of what
type there are going to be. my example of ($timestamp,$msg) was intended
to just be a sample (avoiding typing out some elaberate set of
parameters)


That's fine.  I don't see any reason that the API I suggested be a
compile-time option.  Certainly, libpq has no idea how many, or what
kind, of types are being passed to it by the caller, that's why it *has*
the API that it does.  You just need to work out what each prepared
queries' parameters are and construct the necessary arrays.


I misunderstood what you were saying (more below)


rsyslog provides the following items, which can be sliced and diced with
substatutions, substrings, and additional inserted text.


[...]

Looks like mainly text fields still, so you might want to just stick
with text for now.


yes, almost exclusivly text fields, and the fields that could be numbers 
(or dates) are in text formats when we get them anyway.



rsyslog message formatting provides tools for doing the nessasary
escaping (and is using it for the single insert messages today)

prepared statements in text mode have similar problems (although they
_are_ better in defending against sql injection attacks, so a bit safer).


Uhh, if you use prepared statements with PQexecPrepared, there *is no*
escaping necessary.  I'm not sure what you mean by 'similar problems'.
Can you elaborate on that?  If you mean doing 'prepared queries' by
using creating a string and then using PQexec with
'EXECUTE blah (1,2,3);' then you really need to go read the
documentation I suggested.  That's *not* what I'm getting at when I say
'prepared queries', I'm talking about a protocol-level well-defined
format for passing arguments independently of commands.  A call to
PQexecPrepared looks like this:

PQprepare(conn, myquery, INSERT INTO TAB1 VALUES ($1, $2);, 0, NULL);

values[0] = a;
values[1] = b;
PQexecPrepared(conn, myquery, 2, values, NULL, NULL, 0);

Note that we don't ever send an 'EXECUTE myquery (1,2,3);' type of thing
to libpq.  libpq will handle the execute and the parameters and whatnot
as part of the PG 3.0 protocol.


when you said to stick with text mode, I thought you were meaning that we 
would create a string with EXECUTE in it and send that. it would have 
similar escaping issues (although with fewer vunerabilities if they mess 
up)




so the binary mode only makes a difference on things like timestamps and
numbers? (i.e. no significant added efficiancy in processing the command
itself?)


I'm slightly confused by what you mean by this?  Binary mode is for
parameters only, commands are never 'binary'.  Binary mode just means
that the application gives the value to the database in the format which
it expects, and so the database doesn't have to translate a textual
representation of a value into the binary format the database needs.


I thought that part of the 'efficiancy' and 'performance' to be gained 
from binary modes were avoiding the need to parse commands, if it's only 
the savings in converting column contents from text to specific types, 
it's much less important.



I think the huge complication is that when RedHat compiles rsyslog to
ship it in the distro, they have no idea how it is going to be used (if
it will go to a database, what database engine it will interface with, or
what the schema of that database would look like). Only the
sysadmin(s)/dba(s) know that and they need to be able to tell rsyslog
what to do to get the data where they want it to be, and in the format
they want it to be in.


That's really all fine.  You just need to get from the user, at runtime,
what they want their commands to look like.  Once you have that, it
should be entirely possible to dynamically construct the prepared
queries, most likely without the user having to know anything about COPY
or prepared statements or anything.  For my part, I'd want something
like:

table = mytable;
data = $Y, $m, $d, $H, $msg;


if the user creates the data this way, you just reintroduced the escaping 
problem. they would have to do something like


data = $Y
data = $m
data = $d
data = $H
data = $msg

one key thing is that it's very probable that the user will want to 
manipulate the string, not just send a single variable as-is



I'd avoid having the user provide actual SQL, because that becomes
difficult to deal with unless you embed an SQL parser in rsyslog, and
I don't really see the value in that.


there's no need for rsyslog to parse the SQL, just to be able to escape it 
appropriately and then pass it to the database for execution




If the user wants to do
something fancy with the data in the database, I would encourage them
to put an 'ON INSERT' trigger on 'mytable' to do whatever they want with
the data that's coming in.  This gives you the freedom

Re: [PERFORM] performance for high-volume log insertion


On Mon, 20 Apr 2009, Greg Smith wrote:


On Mon, 20 Apr 2009, da...@lang.hm wrote:


any idea what sort of difference binary mode would result in?


The win from switching from INSERT to COPY can be pretty big, further 
optimizing to BINARY you'd really need to profile to justify.  I haven't 
found any significant difference in binary mode compared to overhead of the 
commit itself in most cases.  The only thing I consistently run into is that 
timestamps can bog things down considerably in text mode, but you have to be 
pretty efficient in your app to do any better generating those those in the 
PostgreSQL binary format yourself.  If you had a lot of difficult to parse 
data types like that, binary might be a plus, but it doesn't sound like that 
will be the case for what you're doing.


But you don't have to believe me, it's easy to generate a test case here 
yourself.  Copy some typical data into the database, export it both ways:


COPY t to 'f';
COPY t to 'f' WITH BINARY;

And then compare copying them both in again with \timing.  That should let 
you definitively answer whether it's really worth the trouble.


while I fully understand the 'benchmark your situation' need, this isn't 
that simple.


in this case we are trying to decide what API/interface to use in a 
infrastructure tool that will be distributed in common distros (it's now 
the default syslog package of debian and fedora), there are so many 
variables in hardware, software, and load that trying to benchmark it 
becomes effectivly impossible.


based on Stephan's explination of where binary could help, I think the 
easy answer is to decide not to bother with it (the postgres text to X 
converters get far more optimization attention than anything rsyslog could 
deploy)


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion


On Mon, 20 Apr 2009, Stephen Frost wrote:


Greg,

* Greg Smith (gsm...@gregsmith.com) wrote:

The win from switching from INSERT to COPY can be pretty big, further
optimizing to BINARY you'd really need to profile to justify.


Have you done any testing to compare COPY vs. INSERT using prepared
statements?  I'd be curious to know how those compare and against
multi-value INSERTS, prepared and unprepared.


and this is the rest of the question that I was trying (unsucessfully) to 
ask.


is this as simple as creating a database and doing an explain on each of 
these? or do I need to actually measure the time (at which point the 
specific hardware and tuning settings become an issue again)


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

David,

* da...@lang.hm (da...@lang.hm) wrote:
 I thought that part of the 'efficiancy' and 'performance' to be gained  
 from binary modes were avoiding the need to parse commands, if it's only  
 the savings in converting column contents from text to specific types,  
 it's much less important.

No, binary mode is about the column contents.  Prepared queries is about
avoiding having to parse commands (which is why the command and the data
elements are seperately done).

 table = mytable;
 data = $Y, $m, $d, $H, $msg;

 if the user creates the data this way, you just reintroduced the escaping 
 problem. they would have to do something like

 data = $Y
 data = $m
 data = $d
 data = $H
 data = $msg

Yes, there is a bit of escaping that the admins will have to deal with
in the config file.  There's no way around that though, regardless of
what you do.  If you let them put SQL in, then they may have to escape
stuff there too.  In the end, an escape problem in the config file is
something which you should really catch when you read the file in, and
even if you don't catch it there, it's less problematic, not really a
performance problem, and much less of a security risk, than having the
escaping done on data from an untrusted source.

 one key thing is that it's very probable that the user will want to  
 manipulate the string, not just send a single variable as-is

You could probably let them do some manipulation, add extra
non-escape-code fields, maybe tack something on the beginning and end,
basically, anything that can be done in the application prior to it
hitting the database should be fine.

 I'd avoid having the user provide actual SQL, because that becomes
 difficult to deal with unless you embed an SQL parser in rsyslog, and
 I don't really see the value in that.

 there's no need for rsyslog to parse the SQL, just to be able to escape 
 it appropriately and then pass it to the database for execution

If the user is providing SQL, then you need to be able to parse that SQL
if you're going to do prepared queries.  It might not require you to be
able to fully parse SQL the way the back-end does, but anything you do
that's not a full SQL parser is going to end up having limitations that
won't be easy to find or document.

For example, you could ask users to provide the prepared statement the
way the database wants it, and then list the data elements seperately
from it somehow, eg:

myquery = INSERT INTO blah (col1, col2, col3) VALUES ($1, $2, $3);
myvals[1] = $Y
myvals[2] = $M
myvals[3] = $msg

The user could then do:

myquery = INSERT INTO blah (col1, col2) SELECT substring($1), $2;
myvals[1] = $M
myvals[2] = $msg

Both of these will work just fine as prepared queries.  

You can then parse that string by just looking for the $'s, but of
course, if the user wants to put an *actual* dollar sign in, then you
have to support that somehow ($$?).  Then you have to deal with whatever
other quoting requirements you have in your config file (how do you deal
with double quotes?  What about single quotes? etc, etc).

You could possibly even put your escape codes into myquery and just try
to figure out how to do the substitutions with the $NUMs and build your
prepared query string.  It gets uglier and uglier if you ask me though.

In the end, I'd still worry about users coming up with new and different
ways to break your sql 'parser'.

 one huge advantage of putting the sql into the configuration is the  
 ability to work around other users of the database.

See, I just don't see that.

 for example, what if the database has additional columns that you don't  
 want to touch (say an item# sequence), if the SQL is in the config this 
 is easy to work around, if it's seperate (or created by the module), this 
 is very hard to do.

You can do that with a trigger trivially..  That could also be supported
through other mechanisms (for instance, let the user provide a list of
columns to fill with DEFAULT in the prepared query).

 I guess you could give examples of the SQL in the documentation for how 
 to create the prepared statement etc in the databases, but how is that 
 much better than having it in the config file?

 for many users it's easier to do middlein -fancy stuff in the SQL than  
 loading things into the database (can you pre-load prepared statements in 
 the database? or are they a per-connection thing?)

Prepared statements, at least under PG, are a per-connection thing.
Triggers aren't the same, those are attached to tables and get called
whenever a particular action is done on those tables (defined in the
trigger definition).  The trigger is then called with the row which is
being inserted, etc, and can do whatever it wants with that row (put it
in a different table, ignore it, etc).

 so back to the main questions of the advantages

 prepared statements avoid needing to escape things, but at the  
 complication of a more complex API.

 there's still the question of the performance difference. I have

Re: [PERFORM] performance for high-volume log insertion

* da...@lang.hm (da...@lang.hm) wrote:
 while I fully understand the 'benchmark your situation' need, this isn't  
 that simple.

It really is.  You know your application, you know it's primary use
cases, and probably have some data to play with.  You're certainly in a
much better situation to at least *try* and benchmark it than we are.

 in this case we are trying to decide what API/interface to use in a  
 infrastructure tool that will be distributed in common distros (it's now  
 the default syslog package of debian and fedora), there are so many  
 variables in hardware, software, and load that trying to benchmark it  
 becomes effectivly impossible.

You don't need to know how it will perform in every situation.  The main
question you have is if using prepared queries is faster or not, so pick
a common structure, create a table, get some data, and test.  I can say
that prepared queries will be more likely to give you a performance
boost with wider tables (more columns).

 based on Stephan's explination of where binary could help, I think the  
 easy answer is to decide not to bother with it (the postgres text to X  
 converters get far more optimization attention than anything rsyslog 
 could deploy)

While that's true, there's no substitute for not having to do a
conversion at all.  After all, it's alot cheaper to do a bit of
byte-swapping on an integer value that's already an integer in memory
than to sprintf and atoi it.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

David,

* da...@lang.hm (da...@lang.hm) wrote:
 is this as simple as creating a database and doing an explain on each of  
 these? or do I need to actually measure the time (at which point the  
 specific hardware and tuning settings become an issue again)

No, you need to measure the time.  An explain isn't going to tell you
much.  However, I think the point here is that if you see a 10%
performance improvment on some given hardware for a particular test,
then chances are pretty good most people will see a performance
benefit.  Some more, some less, but it's unlikely anyone will have worse
performance for it.  There are some edge cases where a prepared
statement can reduce performance, but that's almost always on SELECT
queries, I can't think of a reason off-hand why it'd ever be slower for
INSERTs unless you're already doing things you shouldn't be if you care
about performance (like doing a join against some other table with each
insert..).

Additionally, there's really no way for us to know what an acceptable
performance improvment is for you to justify the added code maintenance
and whatnot for your project.  If you're really just looking for the
low-hanging fruit, then batch your inserts into transactions and go from
there.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion


On Tue, 21 Apr 2009, Stephen Frost wrote:


* da...@lang.hm (da...@lang.hm) wrote:

while I fully understand the 'benchmark your situation' need, this isn't
that simple.


It really is.  You know your application, you know it's primary use
cases, and probably have some data to play with.  You're certainly in a
much better situation to at least *try* and benchmark it than we are.


rsyslog is a syslog server. it replaces (or for debian and fedora, has 
replaced) your standard syslog daemon. it recieves log messages from every 
app on your system (and possibly others), filters, maniulates them, and 
then stores them somewhere. among the places that it can store the logs 
are database servers (native support for MySQL, PostgreSQL, and Oracle. 
plus libdbi for others)


other apps then search and report on the data after it is stored. what 
apps?, I don't know either. pick your favorite reporting tool and you'll 
be a step ahead of me (I don't know a really good reporting tool)


as for sample data, you have syslog messages, just like I do. so you have 
the same access to data that I have.


how would you want to query them? how would people far less experianced 
that you want to query them?



I can speculate that some people would do two columns (time, everything 
else), others will do three (time, server, everything else), and others 
will go further (I know some who would like to extract IP addresses 
embedded in a message into their own column). some people will index on 
the time and host, others will want to do full-text searches of 
everything.



I can talk about the particular use case I have at work, but that would be 
far from normal (full text searches on 10s of TB of data, plus reports, 
etc) but we don't (currently) use postgres to do that, and I'm not sure 
how I would configure postgres for that sort of load. so I don't think 
that my personal situation is a good fit. I looked at bizgres a few years 
ago, but I didn't know enough about what I was trying to do or how much 
data I was trying to deal with to go forward with it at that time.



do I do the benchmark on the type of hardware that I use for the system 
above (after spending how much time experimenting to find corret tuning)? 
or on a stock, untuned postgres running on a desktop-type system (we all 
know how horrible the defaults are), how am I supposed to know if the 
differences that I will see in my 'benchmarks' are the result of the 
differences between the commands, and not that I missed some critical knob 
to turn?


benchmarking is absolutly the right answer for some cases, especially when 
someone is asking exactly how something will work for them. but in this 
case I don't have the specific use case. I am trying to find out where the 
throretical advantages are for these things that 'everybody knows you 
should do with a database' to understand the probability that they will 
make a difference in this case.



in this case we are trying to decide what API/interface to use in a
infrastructure tool that will be distributed in common distros (it's now
the default syslog package of debian and fedora), there are so many
variables in hardware, software, and load that trying to benchmark it
becomes effectivly impossible.


You don't need to know how it will perform in every situation.  The main
question you have is if using prepared queries is faster or not, so pick
a common structure, create a table, get some data, and test.  I can say
that prepared queries will be more likely to give you a performance
boost with wider tables (more columns).


this is very helpful, I can't say what the schema would look like, but I 
would guess that it will tend towards the narrow side (or at least not 
update very many columns explicitly)



based on Stephan's explination of where binary could help, I think the
easy answer is to decide not to bother with it (the postgres text to X
converters get far more optimization attention than anything rsyslog
could deploy)


While that's true, there's no substitute for not having to do a
conversion at all.  After all, it's alot cheaper to do a bit of
byte-swapping on an integer value that's already an integer in memory
than to sprintf and atoi it.


but it's not a integer in memory, it's text that arrived over the network 
or through a socket as a log message from another application.


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion


On Tue, 21 Apr 2009, Stephen Frost wrote:


David,

* da...@lang.hm (da...@lang.hm) wrote:

I thought that part of the 'efficiancy' and 'performance' to be gained
from binary modes were avoiding the need to parse commands, if it's only
the savings in converting column contents from text to specific types,
it's much less important.


No, binary mode is about the column contents.  Prepared queries is about
avoiding having to parse commands (which is why the command and the data
elements are seperately done).


thanks for the clarification.


I'd avoid having the user provide actual SQL, because that becomes
difficult to deal with unless you embed an SQL parser in rsyslog, and
I don't really see the value in that.


there's no need for rsyslog to parse the SQL, just to be able to escape
it appropriately and then pass it to the database for execution


If the user is providing SQL, then you need to be able to parse that SQL
if you're going to do prepared queries.  It might not require you to be
able to fully parse SQL the way the back-end does, but anything you do
that's not a full SQL parser is going to end up having limitations that
won't be easy to find or document.


the current situation is that rsyslog never parses the SQL (other than as 
text for a template, just like if you were going to write the log message 
to disk)


if we stick with the string based API we never need to

the user gives us one string 'prepare...' that we send to the database. 
the user then gives us another string 'execute...' that we send to the 
database. at no point do we ever need to parse the SQL, or even really 
know that it is SQL (the one exception is an escapeing routine that 
replace ' with '' in the strings comeing from the outside world), it's 
just strings assembled using the same string assembly logic that is used 
for writing files to disk, crafting the payload of network packets to 
other servers, etc.


I do agree that there is a reduction in security risk. but since rsyslog 
is rather draconian about forcing the escaping, I'm not sure this is 
enough to tip the scales.




one huge advantage of putting the sql into the configuration is the
ability to work around other users of the database.


See, I just don't see that.


moving a bit away from the traditional syslog use case for a moment. with 
the ability to accept messages from many different types of sources (some 
unreliable like UDP syslog, others very reliably with full 
application-level acknowledgements), the ability to filter messages to 
different destination, and the ability to configure it to craft arbatrary 
SQL statements, rsyslog can be useful as an 'impedance match' between 
different applications. you can coherse just about any app to write some 
sort of message to a file/pipe, and rsyslog can take that and get it into 
a database elsewhere. yes, you or I could write a quick program that would 
reformat the message and submit it (in perl/python/etc, but extending that 
to handle outages, high-volume bursts of traffic, etc starts to be hard.


this is very much _not_ a common use case, but it's a useful side-use of 
rsyslog today.



I guess you could give examples of the SQL in the documentation for how
to create the prepared statement etc in the databases, but how is that
much better than having it in the config file?

for many users it's easier to do middlein -fancy stuff in the SQL than
loading things into the database (can you pre-load prepared statements in
the database? or are they a per-connection thing?)


Prepared statements, at least under PG, are a per-connection thing.
Triggers aren't the same, those are attached to tables and get called
whenever a particular action is done on those tables (defined in the
trigger definition).  The trigger is then called with the row which is
being inserted, etc, and can do whatever it wants with that row (put it
in a different table, ignore it, etc).


that sounds like a lot of work at the database level to avoid some 
complexity on the app side (and it seems that the need to fire a trigger 
probably cost more than the prepared statement ever hoped to gain.)



so back to the main questions of the advantages

prepared statements avoid needing to escape things, but at the
complication of a more complex API.

there's still the question of the performance difference. I have been
thinking that the overhead of doing the work itself would overwelm the
performance benifits of prepared statements.


What work is it that you're referring to here?


doing the inserts themselves (putting the data in the tables, updating 
indexes, issuing a fsync)



Based on what you've
said about your application so far, I would expect that the run-time
cost to prepare the statement (which you do only once) to be a bit of a
cost, but not much, and that the actual inserts would be almost free
from the application side, and much easier for the database to
parse/use.


the inserts are far from free ;-)

but I agree that with

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread Richard Huxton

da...@lang.hm wrote:

On Tue, 21 Apr 2009, Stephen Frost wrote:

* da...@lang.hm (da...@lang.hm) wrote:

while I fully understand the 'benchmark your situation' need, this isn't
that simple.

rsyslog is a syslog server. it replaces (or for debian and fedora, has
replaced) your standard syslog daemon. it recieves log messages from
every app on your system (and possibly others), filters, maniulates
them, and then stores them somewhere. among the places that it can store
the logs are database servers (native support for MySQL, PostgreSQL, and
Oracle. plus libdbi for others)

Well, from a performance standpoint the obvious things to do are:
1. Keep a connection open, do NOT reconnect for each log-statement
2. Batch log statements together where possible
3. Use prepared statements
4. Partition the tables by day/week/month/year (configurable I suppose)

The first two are vital, the third takes you a step further. The fourth
is a long-term admin thing.

And possibly
5. Have two connections, one for fatal/error etc and one for info/debug
level log statements (configurable split?). Then you can use the
synchronous_commit setting on the less important ones. Might buy you
some performance on a busy system.

http://www.postgresql.org/docs/8.3/interactive/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS

as for sample data, you have syslog messages, just like I do. so you
have the same access to data that I have.

how would you want to query them? how would people far less experianced
that you want to query them?

I can speculate that some people would do two columns (time, everything
else), others will do three (time, server, everything else), and others
will go further (I know some who would like to extract IP addresses
embedded in a message into their own column). some people will index on
the time and host, others will want to do full-text searches of everything.

Well, assuming it looks much like traditional syslog, I would do
something like: (timestamp, host, facility, priority, message). It's
easy enough to stitch back together if people want that.

PostgreSQL's full-text indexing is quite well suited to logfiles I'd
have thought, since it knows about filenames, urls etc already.

If you want to get fancy, add a msg_type column and one subsidiary table
for each msg_type. So - you might have smtp_connect_from (hostname,
ip_addr). A set of perl regexps can match and extract the fields for
these extra tables, or you could do it with triggers inside the
database. I think it makes sense to do it in the application. Easier for
users to contribute new patterns/extractions. Meanwhile, the core table
is untouched so you don't *need* to know about these extra tables.

If you have subsidiary tables, you'll want to partition those too and
perhaps stick them in their own schema (logs200901, logs200902 etc).

--
Richard Huxton
Archonet Ltd

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread Kenneth Marshall

Hi,

I just finished reading this thread. We are currently working on
setting up a central log system using rsyslog and PostgreSQL. It
works well once we patched the memory leak. We also looked at what
could be done to improve the efficiency of the DB interface. On the
rsyslog side, moving to prepared queries allows you to remove the
escaping that needs to be done currently before attempting to
insert the data into the SQL backend as well as removing the parsing
and planning time from the insert. This is a big win for high insert
rates, which is what we are talking about. The escaping process is
also a big CPU user in rsyslog which then hands the escaped string
to the backend which then has to undo everything that had been done
and parse/plan the resulting query. This can use a surprising amount
of additional CPU. Even if you cannot support a general prepared
query interface, by specifying what the query should look like you
can handle much of the low-hanging fruit query-wise.

We are currently using a date based trigger to use a new partition
each day and keep 2 months of logs currently. This can be usefully
managed on the backend database, but if rsyslog supported changing
the insert to the new table on a time basis, the CPU used by the
trigger to support this on the backend could be reclaimed. This
would be a win for any DB backend. As you move to the new partition,
issuing a truncate to clear the table would simplify the DB interfaces.

Another performance enhancement already mentioned, would be to
allow certain extra fields in the DB to be automatically populated
as a function of the log messages. For example, logging the mail queue
id for messages from mail systems would make it much easier to locate
particular mail transactions in large amounts of data.

To sum up, eliminating the escaping in rsyslog through the use of
prepared queries would reduce the CPU load on the DB backend. Batching
the inserts will also net you a big performance increase. Some DB-based
applications allow for the specification of several types of queries,
one for single inserts and then a second to support multiple inserts
(copy). Rsyslog already supports the queuing pieces to allow you to
batch inserts. Just some ideas.

Regards,
Ken

On Tue, Apr 21, 2009 at 09:56:23AM +0100, Richard Huxton wrote:
da...@lang.hm wrote:
On Tue, 21 Apr 2009, Stephen Frost wrote:
* da...@lang.hm (da...@lang.hm) wrote:
while I fully understand the 'benchmark your situation' need, this isn't
that simple.

It really is. You know your application, you know it's primary use
cases, and probably have some data to play with. You're certainly in a
much better situation to at least *try* and benchmark it than we are.
rsyslog is a syslog server. it replaces (or for debian and fedora, has
replaced) your standard syslog daemon. it recieves log messages from every
app on your system (and possibly others), filters, maniulates them, and
then stores them somewhere. among the places that it can store the logs
are database servers (native support for MySQL, PostgreSQL, and Oracle.
plus libdbi for others)

The first two are vital, the third takes you a step further. The fourth is
a long-term admin thing.

http://www.postgresql.org/docs/8.3/interactive/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS

other apps then search and report on the data after it is stored. what
apps?, I don't know either. pick your favorite reporting tool and you'll
be a step ahead of me (I don't know a really good reporting tool)
as for sample data, you have syslog messages, just like I do. so you have
the same access to data that I have.
how would you want to query them? how would people far less experianced
that you want to query them?
I can speculate that some people would do two columns (time, everything
else), others will do three (time, server, everything else), and others
will go further (I know some who would like to extract IP addresses
embedded in a message into their own column). some people will index on
the time and host, others will want to do full-text searches of
everything.

Well, assuming it looks much like traditional syslog, I would do something
like: (timestamp, host, facility, priority, message). It's easy enough to
stitch back together if people want that.

PostgreSQL's full-text indexing is quite well suited to logfiles I'd have
thought, since it knows about filenames,

Re: [PERFORM] performance for high-volume log insertion


Kenneth,
  could you join the discussion on the rsyslog mailing list?
rsyslog-users rsys...@lists.adiscon.com

I'm surprised to hear you say that rsyslog can already do batch inserts 
and am interested in how you did that.


what sort of insert rate did you mange to get?

David Lang

On Tue, 21 Apr 2009, Kenneth Marshall wrote:


Date: Tue, 21 Apr 2009 08:33:30 -0500
From: Kenneth Marshall k...@rice.edu
To: Richard Huxton d...@archonet.com
Cc: da...@lang.hm, Stephen Frost sfr...@snowman.net,
Greg Smith gsm...@gregsmith.com, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] performance for high-volume log insertion

Hi,

I just finished reading this thread. We are currently working on
setting up a central log system using rsyslog and PostgreSQL. It
works well once we patched the memory leak. We also looked at what
could be done to improve the efficiency of the DB interface. On the
rsyslog side, moving to prepared queries allows you to remove the
escaping that needs to be done currently before attempting to
insert the data into the SQL backend as well as removing the parsing
and planning time from the insert. This is a big win for high insert
rates, which is what we are talking about. The escaping process is
also a big CPU user in rsyslog which then hands the escaped string
to the backend which then has to undo everything that had been done
and parse/plan the resulting query. This can use a surprising amount
of additional CPU. Even if you cannot support a general prepared
query interface, by specifying what the query should look like you
can handle much of the low-hanging fruit query-wise.

We are currently using a date based trigger to use a new partition
each day and keep 2 months of logs currently. This can be usefully
managed on the backend database, but if rsyslog supported changing
the insert to the new table on a time basis, the CPU used by the
trigger to support this on the backend could be reclaimed. This
would be a win for any DB backend. As you move to the new partition,
issuing a truncate to clear the table would simplify the DB interfaces.

Another performance enhancement already mentioned, would be to
allow certain extra fields in the DB to be automatically populated
as a function of the log messages. For example, logging the mail queue
id for messages from mail systems would make it much easier to locate
particular mail transactions in large amounts of data.

To sum up, eliminating the escaping in rsyslog through the use of
prepared queries would reduce the CPU load on the DB backend. Batching
the inserts will also net you a big performance increase. Some DB-based
applications allow for the specification of several types of queries,
one for single inserts and then a second to support multiple inserts
(copy). Rsyslog already supports the queuing pieces to allow you to
batch inserts. Just some ideas.

Regards,
Ken


On Tue, Apr 21, 2009 at 09:56:23AM +0100, Richard Huxton wrote:

da...@lang.hm wrote:

On Tue, 21 Apr 2009, Stephen Frost wrote:

* da...@lang.hm (da...@lang.hm) wrote:

while I fully understand the 'benchmark your situation' need, this isn't
that simple.


It really is.  You know your application, you know it's primary use
cases, and probably have some data to play with.  You're certainly in a
much better situation to at least *try* and benchmark it than we are.

rsyslog is a syslog server. it replaces (or for debian and fedora, has
replaced) your standard syslog daemon. it recieves log messages from every
app on your system (and possibly others), filters, maniulates them, and
then stores them somewhere. among the places that it can store the logs
are database servers (native support for MySQL, PostgreSQL, and Oracle.
plus libdbi for others)


Well, from a performance standpoint the obvious things to do are:
1. Keep a connection open, do NOT reconnect for each log-statement
2. Batch log statements together where possible
3. Use prepared statements
4. Partition the tables by day/week/month/year (configurable I suppose)

The first two are vital, the third takes you a step further. The fourth is
a long-term admin thing.

And possibly
5. Have two connections, one for fatal/error etc and one for info/debug
level log statements (configurable split?). Then you can use the
synchronous_commit setting on the less important ones. Might buy you some
performance on a busy system.

http://www.postgresql.org/docs/8.3/interactive/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS


other apps then search and report on the data after it is stored. what
apps?, I don't know either. pick your favorite reporting tool and you'll
be a step ahead of me (I don't know a really good reporting tool)
as for sample data, you have syslog messages, just like I do. so you have
the same access to data that I have.
how would you want to query them? how would people far less experianced
that you want to query them?
I can speculate that some people would do two columns (time, everything
else), others

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread Kenneth Marshall

On Tue, Apr 21, 2009 at 08:37:54AM -0700, da...@lang.hm wrote:
Kenneth,
could you join the discussion on the rsyslog mailing list?
rsyslog-users rsys...@lists.adiscon.com

I'm surprised to hear you say that rsyslog can already do batch inserts and
am interested in how you did that.

what sort of insert rate did you mange to get?

David Lang

David,

I would be happy to join the discussion. I did not mean to say
that rsyslog currently supported batch inserts, just that the
pieces that provide stand-by queuing could be used to manage
batching inserts.

Cheers,
Ken

On Tue, 21 Apr 2009, Kenneth Marshall wrote:

Date: Tue, 21 Apr 2009 08:33:30 -0500
From: Kenneth Marshall k...@rice.edu
To: Richard Huxton d...@archonet.com
Cc: da...@lang.hm, Stephen Frost sfr...@snowman.net,
Greg Smith gsm...@gregsmith.com, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] performance for high-volume log insertion
Hi,

Regards,
Ken

It really is. You know your application, you know it's primary use
cases, and probably have some data to play with. You're certainly in a
much better situation to at least *try* and benchmark it than we are.
rsyslog is a syslog server. it replaces (or for debian and fedora, has
replaced) your standard syslog daemon. it recieves log messages from
every
app on your system (and possibly others), filters, maniulates them, and
then stores them somewhere. among the places that it can store the logs
are database servers (native support for MySQL, PostgreSQL, and Oracle.
plus libdbi for others)

The first two are vital, the third takes you a step further. The fourth
is
a long-term admin thing.

http://www.postgresql.org/docs/8.3/interactive/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS

other apps then search and report on the data after it is stored. what
apps?, I don't know either. pick your favorite reporting tool and you'll

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread Ben Chobot


On Mon, 20 Apr 2009, da...@lang.hm wrote:

one huge advantage of putting the sql into the configuration is the ability 
to work around other users of the database.


+1 on this. We've always found tools much easier to work with when they 
could be adapted to our schema, as opposed to changing our process for the 
tool.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

* Ben Chobot (be...@silentmedia.com) wrote:
 On Mon, 20 Apr 2009, da...@lang.hm wrote:
 one huge advantage of putting the sql into the configuration is the 
 ability to work around other users of the database.

 +1 on this. We've always found tools much easier to work with when they  
 could be adapted to our schema, as opposed to changing our process for 
 the tool.

I think we're all in agreement that we should allow the user to define
their schema and support loading the data into it.  The question has
been if the user really needs the flexibility to define arbitrary SQL to
be used to do the inserts.

Something I'm a bit confused about, still, is if this is really even a
problem.  It sounds like rsyslog already allows arbitrary SQL in the
config file with some kind of escape syntax for the variables.  Why not
just keep that, but split it into a prepared query (where you change the
variables to $NUM vars for the prepared statement) and an array of
values (to pass to PQexecPrepared)?

If you already know how to figure out what the variables in the
arbitrary SQL statement are, this shouldn't be any more limiting than
today, except where a prepared query can't have a variable argument but
a non-prepared query can (eg, table name).  You could deal with that
with some kind of configuration variable that lets the user choose to
use prepared queries or not though, or some additional syntax that
indicates certain variables shouldn't be translated to $NUM vars (eg:
$*blah instead of $blah).

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion


On Tue, 21 Apr 2009, Stephen Frost wrote:


* Ben Chobot (be...@silentmedia.com) wrote:

On Mon, 20 Apr 2009, da...@lang.hm wrote:

one huge advantage of putting the sql into the configuration is the
ability to work around other users of the database.


+1 on this. We've always found tools much easier to work with when they
could be adapted to our schema, as opposed to changing our process for
the tool.


I think we're all in agreement that we should allow the user to define
their schema and support loading the data into it.  The question has
been if the user really needs the flexibility to define arbitrary SQL to
be used to do the inserts.

Something I'm a bit confused about, still, is if this is really even a
problem.  It sounds like rsyslog already allows arbitrary SQL in the
config file with some kind of escape syntax for the variables.  Why not
just keep that, but split it into a prepared query (where you change the
variables to $NUM vars for the prepared statement) and an array of
values (to pass to PQexecPrepared)?

If you already know how to figure out what the variables in the
arbitrary SQL statement are, this shouldn't be any more limiting than
today, except where a prepared query can't have a variable argument but
a non-prepared query can (eg, table name).  You could deal with that
with some kind of configuration variable that lets the user choose to
use prepared queries or not though, or some additional syntax that
indicates certain variables shouldn't be translated to $NUM vars (eg:
$*blah instead of $blah).


I think the key thing is that rsyslog today doesn't know anything about 
SQL variables, it just creates a string that the user and the database say 
looks like a SQL statement.


an added headache is that the rsyslog config does not have the concept of 
arrays (the closest that it has is one special-case hack to let you 
specify one variable multiple times)


if the performance win of the prepared statement is significant, then it's 
probably worth the complication of changing these things.


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread Greg Smith


On Mon, 20 Apr 2009, da...@lang.hm wrote:

while I fully understand the 'benchmark your situation' need, this isn't 
that simple.  in this case we are trying to decide what API/interface to 
use in a infrastructure tool that will be distributed in common distros 
(it's now the default syslog package of debian and fedora), there are so 
many variables in hardware, software, and load that trying to benchmark 
it becomes effectivly impossible.


From your later comments, you're wandering a bit outside of what you were 
asking about here.  Benchmarking the *query* side of things can be 
extremely complicated.  You have to worry about memory allocation, cold 
vs. warm cache, scale of database relative to RAM, etc.


You were asking specifically about *insert* performance, which isn't 
nearly as complicated.  There are basically three setups:


1) Disk/controller has a proper write cache.  Writes and fsync will be 
fast.  You can insert a few thousand individual transactions per second.


2) Disk/controller has a lying write cache.  Writes and fsync will be 
fast, but it's not safe for database use.  But since (1) is expensive and 
this one you can get for free jut by using a regular SATA drive with its 
write cache enabled, you can use this case as a proxy for approximately 
how (1) would act.  You'll still get a few thousand transactions per 
second, sustained writes may slow down relative to (1) if you insert 
enough that you hit a checkpoint (triggering lots of random I/O).


3) All write caches have been disabled because they were not 
battery-backed.  This is the case if you have a regular SATA drive and you 
disable its write cache because you care about write durability.  You'll 
get a bit less than RPM/60 writes/second, so 120 inserts/second with a 
typical 7200RPM drive.  Here batching multiple INSERTs together is 
critical to get any sort of reasonable performance.


In (3), I'd expect that trivia like INSERT vs. COPY and COPY BINARY vs. 
COPY TEXT would be overwhelmed by the overhead of the commit itself. 
Therefore you probably want to test with case (2) instead, as it doesn't 
require any additional hardware but has similar performance to a 
production-worthy (1).  All of the other things you're worried about 
really don't matter here; you can get an approximate measure of what the 
performance of the various INSERT/COPY schemes are that is somewhat 
platform dependant, but the results should be good enough to give you some 
rule of thumb suggestions for whether optimizations are significant enough 
to justify the coding effort to implement them or not.


I'm not sure whether you're familiar with all the fsync trivia here.  In 
normal syslog use, there's an fsync call after every write.  You can 
disable that by placing a - before the file name in /etc/syslog.conf The 
thing that is going to make database-based writes very different is that 
syslog's fsync'd writes are unlikely to leave you in a bad state if the 
drive lies about them, while database writes can.  So someone using syslog 
on a standard SATA drive isn't getting the write guarantee they think they 
are, but the downside on a crash is minimal.  If you've got a high-volume 
syslog environment (100 lines/second), you can't support those as 
individual database writes unless you've got a battery-backed write 
controller.  A regular disk just can't process genuine fsync calls any 
faster than that.  A serious syslog deployment that turns fsync on and 
expects it to really do its thing is already exposed to this issue though. 
I think it may be a the case that a lot of people think they have durable 
writes in their configuration but really don't.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

* da...@lang.hm (da...@lang.hm) wrote:
 I think the key thing is that rsyslog today doesn't know anything about  
 SQL variables, it just creates a string that the user and the database 
 say looks like a SQL statement.

err, what SQL variables?  You mean the $NUM stuff?  They're just
placeholders..  You don't really need to *do* anything with them..  Or
are you worried that users would provide something that would break as a
prepared query?  If so, you just need to figure out how to handle that
cleanly..

 an added headache is that the rsyslog config does not have the concept of 
 arrays (the closest that it has is one special-case hack to let you  
 specify one variable multiple times)

Argh.  The array I'm talking about is a C array, and has nothing to do
with the actual config syntax..  I swear, I think you're making this
more difficult by half.

Alright, looking at the documentation on rsyslog.com, I see something
like:

$template MySQLInsert,insert iut, message, receivedat values
('%iut%', '%msg:::UPPERCASE%', '%timegenerated:::date-mysql%')
into systemevents\r\n, SQL

Ignoring the fact that this is horrible, horrible non-SQL, I see that
you use %blah% to define variables inside your string.  That's fine.
There's no reason why you can't use this exact syntax to build a
prepared query.  No user-impact changes are necessary.  Here's what you
do:

build your prepared query by doing:
copy user string
newstring = replace all %blah% strings with $1, $2, $3, etc.
myvars = dynamically created C array with the %blah%'s in it
call PQprepare(newstring)

when a record comes in:
allocate a myvalues array of pointers
loop through myvars
  for each myvar
  set the corresponding pointer in myvalues to the string which
  it corresponds to from the record
call PQexecPrepared(myvalues)

That's pretty much it.  I assume you already deal with escaping %'s
somehow during the config load so that the prepared statement will be
what the user expects.  As I mentioned before, the only obvious issue I
see with doing this implicitly is that the user might want to put
variables in places that you can't have variables in prepared queries.
You could deal with that by having the user indicate per template, using
another template option, if the query can be prepared or not.  Another
options is adding to your syntax something like '%*blah%' which would
tell the system to pre-populate that variable before issuing PQprepare
on the resultant string.  Of course, you might just use PQexecParams
there, unless you want to be gung-ho and actually keep a hash around of
prepared queries on the assumption that the variable the user gave you
doesn't change very often (eg, '%*month%') and it's cheap to keep a
small list of them around to use when they do match up.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion


On Tue, 21 Apr 2009, Greg Smith wrote:


On Mon, 20 Apr 2009, da...@lang.hm wrote:

while I fully understand the 'benchmark your situation' need, this isn't 
that simple.  in this case we are trying to decide what API/interface to 
use in a infrastructure tool that will be distributed in common distros 
(it's now the default syslog package of debian and fedora), there are so 
many variables in hardware, software, and load that trying to benchmark it 
becomes effectivly impossible.


From your later comments, you're wandering a bit outside of what you were 
asking about here.  Benchmarking the *query* side of things can be extremely 
complicated.  You have to worry about memory allocation, cold vs. warm cache, 
scale of database relative to RAM, etc.


You were asking specifically about *insert* performance, which isn't nearly 
as complicated.  There are basically three setups:


1) Disk/controller has a proper write cache.  Writes and fsync will be fast. 
You can insert a few thousand individual transactions per second.


2) Disk/controller has a lying write cache.  Writes and fsync will be fast, 
but it's not safe for database use.  But since (1) is expensive and this one 
you can get for free jut by using a regular SATA drive with its write cache 
enabled, you can use this case as a proxy for approximately how (1) would 
act.  You'll still get a few thousand transactions per second, sustained 
writes may slow down relative to (1) if you insert enough that you hit a 
checkpoint (triggering lots of random I/O).


3) All write caches have been disabled because they were not battery-backed. 
This is the case if you have a regular SATA drive and you disable its write 
cache because you care about write durability.  You'll get a bit less than 
RPM/60 writes/second, so 120 inserts/second with a typical 7200RPM drive. 
Here batching multiple INSERTs together is critical to get any sort of 
reasonable performance.


in case #1 would you expect to get significant gains from batching? 
doesn't it suffer from problems similar to #2 when checkpoints hit?


In (3), I'd expect that trivia like INSERT vs. COPY and COPY BINARY vs. COPY 
TEXT would be overwhelmed by the overhead of the commit itself. Therefore you 
probably want to test with case (2) instead, as it doesn't require any 
additional hardware but has similar performance to a production-worthy (1). 
All of the other things you're worried about really don't matter here; you 
can get an approximate measure of what the performance of the various 
INSERT/COPY schemes are that is somewhat platform dependant, but the results 
should be good enough to give you some rule of thumb suggestions for whether 
optimizations are significant enough to justify the coding effort to 
implement them or not.


I'll see about setting up a test in the next day or so. should I be able 
to script this through psql? or do I need to write a C program to test 
this?


I'm not sure whether you're familiar with all the fsync trivia here.  In 
normal syslog use, there's an fsync call after every write.  You can disable 
that by placing a - before the file name in /etc/syslog.conf The thing that 
is going to make database-based writes very different is that syslog's 
fsync'd writes are unlikely to leave you in a bad state if the drive lies 
about them, while database writes can.  So someone using syslog on a standard 
SATA drive isn't getting the write guarantee they think they are, but the 
downside on a crash is minimal.  If you've got a high-volume syslog 
environment (100 lines/second), you can't support those as individual 
database writes unless you've got a battery-backed write controller.  A 
regular disk just can't process genuine fsync calls any faster than that.  A 
serious syslog deployment that turns fsync on and expects it to really do its 
thing is already exposed to this issue though. I think it may be a the case 
that a lot of people think they have durable writes in their configuration 
but really don't.


rsyslog is a little different, instead of just input - disk it does input 
- queue - output (where output can be many things, including disk or 
database)


it's default is to use memory-based queues (and no fsync), but has config 
options to do disk based queues with a fsync after each update


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread Kenneth Marshall

On Tue, Apr 21, 2009 at 11:09:18AM -0700, da...@lang.hm wrote:
 On Tue, 21 Apr 2009, Greg Smith wrote:

 On Mon, 20 Apr 2009, da...@lang.hm wrote:

 while I fully understand the 'benchmark your situation' need, this isn't 
 that simple.  in this case we are trying to decide what API/interface to 
 use in a infrastructure tool that will be distributed in common distros 
 (it's now the default syslog package of debian and fedora), there are so 
 many variables in hardware, software, and load that trying to benchmark 
 it becomes effectivly impossible.

 From your later comments, you're wandering a bit outside of what you were 
 asking about here.  Benchmarking the *query* side of things can be 
 extremely complicated.  You have to worry about memory allocation, cold 
 vs. warm cache, scale of database relative to RAM, etc.

 You were asking specifically about *insert* performance, which isn't 
 nearly as complicated.  There are basically three setups:

 1) Disk/controller has a proper write cache.  Writes and fsync will be 
 fast. You can insert a few thousand individual transactions per second.

 2) Disk/controller has a lying write cache.  Writes and fsync will be 
 fast, but it's not safe for database use.  But since (1) is expensive and 
 this one you can get for free jut by using a regular SATA drive with its 
 write cache enabled, you can use this case as a proxy for approximately 
 how (1) would act.  You'll still get a few thousand transactions per 
 second, sustained writes may slow down relative to (1) if you insert 
 enough that you hit a checkpoint (triggering lots of random I/O).

 3) All write caches have been disabled because they were not 
 battery-backed. This is the case if you have a regular SATA drive and you 
 disable its write cache because you care about write durability.  You'll 
 get a bit less than RPM/60 writes/second, so 120 inserts/second with a 
 typical 7200RPM drive. Here batching multiple INSERTs together is critical 
 to get any sort of reasonable performance.

 in case #1 would you expect to get significant gains from batching? doesn't 
 it suffer from problems similar to #2 when checkpoints hit?

Even with a disk controller with a proper write cache, the latency for
single-insert-at-a-time will keep the number of updates to the low
thousands per second (on the controllers I have used). If you can batch
them, it would not be unreasonable to increase performance by an order
of magnitude or more. At the high end, other issues like CPU usage can
restrict performance.

Ken
 In (3), I'd expect that trivia like INSERT vs. COPY and COPY BINARY vs. 
 COPY TEXT would be overwhelmed by the overhead of the commit itself. 
 Therefore you probably want to test with case (2) instead, as it doesn't 
 require any additional hardware but has similar performance to a 
 production-worthy (1). All of the other things you're worried about really 
 don't matter here; you can get an approximate measure of what the 
 performance of the various INSERT/COPY schemes are that is somewhat 
 platform dependant, but the results should be good enough to give you some 
 rule of thumb suggestions for whether optimizations are significant enough 
 to justify the coding effort to implement them or not.

 I'll see about setting up a test in the next day or so. should I be able to 
 script this through psql? or do I need to write a C program to test this?

 I'm not sure whether you're familiar with all the fsync trivia here.  In 
 normal syslog use, there's an fsync call after every write.  You can 
 disable that by placing a - before the file name in /etc/syslog.conf The 
 thing that is going to make database-based writes very different is that 
 syslog's fsync'd writes are unlikely to leave you in a bad state if the 
 drive lies about them, while database writes can.  So someone using syslog 
 on a standard SATA drive isn't getting the write guarantee they think they 
 are, but the downside on a crash is minimal.  If you've got a high-volume 
 syslog environment (100 lines/second), you can't support those as 
 individual database writes unless you've got a battery-backed write 
 controller.  A regular disk just can't process genuine fsync calls any 
 faster than that.  A serious syslog deployment that turns fsync on and 
 expects it to really do its thing is already exposed to this issue though. 
 I think it may be a the case that a lot of people think they have durable 
 writes in their configuration but really don't.

 rsyslog is a little different, instead of just input - disk it does input 
 - queue - output (where output can be many things, including disk or 
 database)

 it's default is to use memory-based queues (and no fsync), but has config 
 options to do disk based queues with a fsync after each update

 David Lang

 -- 
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance


-- 
Sent

Re: [PERFORM] performance for high-volume log insertion


On Tue, 21 Apr 2009, Stephen Frost wrote:


* da...@lang.hm (da...@lang.hm) wrote:

I think the key thing is that rsyslog today doesn't know anything about
SQL variables, it just creates a string that the user and the database
say looks like a SQL statement.


err, what SQL variables?  You mean the $NUM stuff?  They're just
placeholders..  You don't really need to *do* anything with them..  Or
are you worried that users would provide something that would break as a
prepared query?  If so, you just need to figure out how to handle that
cleanly..


an added headache is that the rsyslog config does not have the concept of
arrays (the closest that it has is one special-case hack to let you
specify one variable multiple times)


Argh.  The array I'm talking about is a C array, and has nothing to do
with the actual config syntax..  I swear, I think you're making this
more difficult by half.


not intentinally, but you may be right.


Alright, looking at the documentation on rsyslog.com, I see something
like:

$template MySQLInsert,insert iut, message, receivedat values
('%iut%', '%msg:::UPPERCASE%', '%timegenerated:::date-mysql%')
into systemevents\r\n, SQL

Ignoring the fact that this is horrible, horrible non-SQL,


that example is for MySQL, nuff said ;-) or are you referring to the 
modifiers that rsyslog has to manipulate the strings before inserting 
them? (as opposed to using sql to manipulate the strings)



I see that
you use %blah% to define variables inside your string.  That's fine.
There's no reason why you can't use this exact syntax to build a
prepared query.  No user-impact changes are necessary.  Here's what you
do:


snip psudocode to replace %blah% with $num

for some reason I was stuck on the idea of the config specifying the 
statement and variables seperatly, so I wasn't thinking this way, however 
there are headaches


doing this will require changes to the structure of rsyslog, today the 
string manipulation is done before calling the output (database) module, 
so all the database module currently gets is a string. in a (IMHO 
misguided) attempt at security in a multi-threaded program, the output 
modules are not given access to the full data, only to the distiled 
result.


also, this approach won't work if the user wants to combine fixed text 
with the variable into a column. an example of doing that would be to have 
a filter to match specific lines, and then use a slightly different 
template for those lines. I guess that could be done in SQL instead of in 
the rsyslog string manipulation (i.e. instead of 'blah-%host%' do 
'blah-'||'%host')



As I mentioned before, the only obvious issue I
see with doing this implicitly is that the user might want to put
variables in places that you can't have variables in prepared queries.


this problem space would be anywhere except the column contents, right?


You could deal with that by having the user indicate per template, using
another template option, if the query can be prepared or not.  Another
options is adding to your syntax something like '%*blah%' which would
tell the system to pre-populate that variable before issuing PQprepare
on the resultant string.  Of course, you might just use PQexecParams
there, unless you want to be gung-ho and actually keep a hash around of
prepared queries on the assumption that the variable the user gave you
doesn't change very often (eg, '%*month%') and it's cheap to keep a
small list of them around to use when they do match up.


rsyslog supports something similar for writing to disk where you can use 
variables as part of the filename/path (referred to as 'dynafiles' in the 
documentation). that's a little easier to deal with as the filename is 
specified seperatly from the format of the data to write. If we end up 
doing prepared statements I suspect they initially won't support variables 
outside of the columns.


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion


On Tue, 21 Apr 2009, da...@lang.hm wrote:


I see that
you use %blah% to define variables inside your string.  That's fine.
There's no reason why you can't use this exact syntax to build a
prepared query.  No user-impact changes are necessary.  Here's what you
do:


snip psudocode to replace %blah% with $num

for some reason I was stuck on the idea of the config specifying the 
statement and variables seperatly, so I wasn't thinking this way, however 
there are headaches


doing this will require changes to the structure of rsyslog, today the string 
manipulation is done before calling the output (database) module, so all the 
database module currently gets is a string. in a (IMHO misguided) attempt at 
security in a multi-threaded program, the output modules are not given access 
to the full data, only to the distiled result.


also, this approach won't work if the user wants to combine fixed text with 
the variable into a column. an example of doing that would be to have a 
filter to match specific lines, and then use a slightly different template 
for those lines. I guess that could be done in SQL instead of in the rsyslog 
string manipulation (i.e. instead of 'blah-%host%' do 'blah-'||'%host')


by the way, now that I understand how you were viewing this, I see why you 
were saying that there would need to be a SQL parser. I was missing that 
headache, by going the direction of having the user specify the individual 
components (which has it's own headache)


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

* da...@lang.hm (da...@lang.hm) wrote:
 Ignoring the fact that this is horrible, horrible non-SQL,

 that example is for MySQL, nuff said ;-)

indeed.

 for some reason I was stuck on the idea of the config specifying the  
 statement and variables seperatly, so I wasn't thinking this way, however 
 there are headaches

Nothing worth doing is ever completely without complications. :)

 doing this will require changes to the structure of rsyslog, today the  
 string manipulation is done before calling the output (database) module,  
 so all the database module currently gets is a string. in a (IMHO  
 misguided) attempt at security in a multi-threaded program, the output  
 modules are not given access to the full data, only to the distiled  
 result.

Ah, yes, that's definitely a problem and I agree- a very misguided
approach to doing things.  Certainly, to use prepared queries, you will
have to pass the data to whatever is talking to the database in some
kind of structured way.  There's not much advantage if you're getting it
as a string and having to parse it out yourself before using a prepared
query with the database.

In a multi-threaded program, I think it would at least be reasonably
easy/cheap to provide the output modules with the full data?  Of course,
you would need to teach the string manipulation logic to not do its
escaping and other related work for prepared queries which are just
going to use the full data anyway.

 also, this approach won't work if the user wants to combine fixed text  
 with the variable into a column. an example of doing that would be to 
 have a filter to match specific lines, and then use a slightly different  
 template for those lines. I guess that could be done in SQL instead of in 
 the rsyslog string manipulation (i.e. instead of 'blah-%host%' do  
 'blah-'||'%host')

It would be more like: 'blah-' || %host%

Just to be clear (if you put the %host% in quotes, and then convert that
to '$1', it won't be considered a variable, at least in PG).  That might
be an issue going forward, but on the flip side, I can see some reasons
for supporting both prepared and unprepared queries, so if you implement
that through an additional template option, you can document that the
user needs to ensure the prepared query is structured correctly with the
correct quoting.  This gives you the flexibility of the unprepared query
for users who don't care about performance, and the benefits of prepared
queries, where they can be used, for users who do need that performance.

Or you could just force your users to move everything to prepared
queries but it's probably too late for that. :)  Maybe if things had
started out that way..

 As I mentioned before, the only obvious issue I
 see with doing this implicitly is that the user might want to put
 variables in places that you can't have variables in prepared queries.

 this problem space would be anywhere except the column contents, right?

Well, it depends on the query..  You can have variables in the column
contents, sure, but you can also have them in where clauses if you're
doing something like:

insert into blah select $1,$2,$3,b from mytable where $2 = c;

I believe, in PG at least, you can use them pretty much anywhere you can
use a constant.

 rsyslog supports something similar for writing to disk where you can use  
 variables as part of the filename/path (referred to as 'dynafiles' in the 
 documentation). that's a little easier to deal with as the filename is  
 specified seperatly from the format of the data to write. If we end up  
 doing prepared statements I suspect they initially won't support 
 variables outside of the columns.

That sounds reasonable, to me at least.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

* da...@lang.hm (da...@lang.hm) wrote:
 by the way, now that I understand how you were viewing this, I see why 
 you were saying that there would need to be a SQL parser. I was missing 
 that headache, by going the direction of having the user specify the 
 individual components (which has it's own headache)

Right, but really, you're already parsing the SQL to the extent that you
need to, and whatever limitations and headaches that causes you've
already had to deal with through proper escaping and whatnot of your
variables..  So I'm not sure that it'll be all that bad in the end.

If you add this as a new feature that users essentially have to opt-in
to, then I think you can offload alot of the work on to the users for
doing things like fixing quoting (so the $NUM vars aren't quoted).

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread James Mansion


da...@lang.hm wrote:

2. insert into table values (),(),(),()


Using this structure would be more database agnostic, but won't perform
as well as the COPY options I don't believe.  It might be interesting to
do a large insert into table values (),(),() as a prepared statement,
but then you'd have to have different sizes for each different number of
items you want inserted.


on the other hand, when you have a full queue (lots of stuff to 
insert) is when you need the performance the most. if it's enough of a 
win on the database side, it could be worth more effort on the 
applicaiton side.

Are you sure preparing a simple insert is really worthwhile?

I'd check if I were you.  It shouldn't take long to plan.

Note that this structure (above) is handy but not universal.

You might want to try:

insert into table
select (...)
union
select (...)
union
select (...)
...

as well, since its more univeral.  Works on Sybase and SQLServer for 
example (and v.quickly too - much more so than a TSQL batch with lots of 
inserts or execs of stored procs)


James



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

* James Mansion (ja...@mansionfamily.plus.com) wrote:
 da...@lang.hm wrote:
 on the other hand, when you have a full queue (lots of stuff to  
 insert) is when you need the performance the most. if it's enough of a  
 win on the database side, it could be worth more effort on the  
 applicaiton side.
 Are you sure preparing a simple insert is really worthwhile?

 I'd check if I were you.  It shouldn't take long to plan.

Using prepared queries, at least if you use PQexecPrepared or
PQexecParams, also reduces the work required on the client to build the
whole string, and the parsing overhead on the database side to pull it
apart again.  That's where the performance is going to be improved by
going that route, not so much in eliminating the planning.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread Greg Smith


On Tue, 21 Apr 2009, da...@lang.hm wrote:

1) Disk/controller has a proper write cache.  Writes and fsync will be 
fast. You can insert a few thousand individual transactions per second.


in case #1 would you expect to get significant gains from batching? doesn't 
it suffer from problems similar to #2 when checkpoints hit?


Typically controllers with a write cache are doing elevator sorting across 
a much larger chunk of working memory (typically =256MB instead of 32MB 
on the disk itself) which means a mix of random writes will average better 
performance--on top of being able to aborb a larger chunk of them before 
blocking on writes.  You get some useful sorting in the OS itself, but 
every layer of useful additional cache helps significantly here.


Batching is always a win because even a write-cached commit is still 
pretty expensive, from the server on down the chain.


I'll see about setting up a test in the next day or so. should I be able to 
script this through psql? or do I need to write a C program to test this?


You can easily compare things with psql, like in the COPY BINARY vs. TEXT 
example I gave earlier, that's why I was suggesting you run your own tests 
here just to get a feel for things on your data set.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion


On Tue, 21 Apr 2009, Stephen Frost wrote:


* James Mansion (ja...@mansionfamily.plus.com) wrote:

da...@lang.hm wrote:

on the other hand, when you have a full queue (lots of stuff to
insert) is when you need the performance the most. if it's enough of a
win on the database side, it could be worth more effort on the
applicaiton side.

Are you sure preparing a simple insert is really worthwhile?

I'd check if I were you.  It shouldn't take long to plan.


Using prepared queries, at least if you use PQexecPrepared or
PQexecParams, also reduces the work required on the client to build the
whole string, and the parsing overhead on the database side to pull it
apart again.  That's where the performance is going to be improved by
going that route, not so much in eliminating the planning.


in a recent thread about prepared statements, where it was identified that 
since the planning took place at the time of the prepare you sometimes 
have worse plans than for non-prepared statements, a proposal was made to 
have a 'pre-parsed, but not pre-planned' version of a prepared statement. 
This was dismissed as a waste of time (IIRC by Tom L) as the parsing time 
was negligable.


was that just because it was a more complex query to plan?

David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread Robert Haas

On Tue, Apr 21, 2009 at 8:12 PM,  da...@lang.hm wrote:
 Using prepared queries, at least if you use PQexecPrepared or
 PQexecParams, also reduces the work required on the client to build the
 whole string, and the parsing overhead on the database side to pull it
 apart again.  That's where the performance is going to be improved by
 going that route, not so much in eliminating the planning.

 in a recent thread about prepared statements, where it was identified that
 since the planning took place at the time of the prepare you sometimes have
 worse plans than for non-prepared statements, a proposal was made to have a
 'pre-parsed, but not pre-planned' version of a prepared statement. This was
 dismissed as a waste of time (IIRC by Tom L) as the parsing time was
 negligable.

 was that just because it was a more complex query to plan?

Joins are expensive to plan; a simple insert is not.  I also disagree
that pre-parsed but not pre-planned is a waste of time, whoever said
it.  Sometimes it's what you want, especially in PL/pgsql.

...Robert

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-21 Thread James Mansion


Stephen Frost wrote:

apart again.  That's where the performance is going to be improved by
going that route, not so much in eliminating the planning.
  
Fine.  But like I said, I'd suggest measuring the fractional improvement 
for this
when sending multi-row inserts before writing something complex.  I 
think the
big will will be doing multi-row inserts at all.  If you are going to 
prepare then
you'll need a collection of different prepared statements for different 
batch sizes
(say 1,2,3,4,5,10,20,50) and things will get complicated.  A multi-row 
insert

with unions and dynamic SQL is actually rather universal.

Personally I'd implement that first (and it should be easy to do across 
multiple

dbms types) and then return to it to have a more complex client side with
prepared statements etc if (and only if) necessary AND the performance
improvement were measurably worthwhile, given the indexing and storage
overheads.

There is no point optimising away the CPU of the simple parse if you are
just going to get hit with a lot of latency from round trips, and forming a
generic multi-insert SQL string is much, much easier to get working as a 
first

step. Server CPU isn't a bottleneck all that often - and with something as
simple as this you'll hit IO performance bottlenecks rather easily.

James



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] performance for high-volume log insertion

2009-04-20 Thread david

I am working with the rsyslog developers to improve it's performance in 
inserting log messages to databases.


currently they have a postgres interface that works like all the other 
ones, where rsyslog formats an insert statement, passes that the the 
interface module, that sends it to postgres (yes, each log as a seperate 
transaction)


the big win is going to be in changing the core of rsyslog so that it can 
process multiple messages at a time (bundling them into a single 
transaction)


but then we run into confusion.

off the top of my head I know of several different ways to get the data 
into postgres


1. begin; insert; insert;...;end

2. insert into table values (),(),(),()

3. copy from stdin
  (how do you tell it how many records to read from stdin, or that you 
have given it everything without disconnecting)


4. copy from stdin in binary mode

and each of the options above can be done with prepared statements, stored 
procedures, or functions.


I know that using procedures or functions can let you do fancy things like 
inserting the row(s) into the appropriate section of a partitioned table


other than this sort of capability, what sort of differences should be 
expected between the various approaches (including prepared statements vs 
unprepared)


since the changes that rsyslog is making will affect all the other 
database interfaces as well, any comments about big wins or things to 
avoid for other databases would be appriciated.


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

David,

* da...@lang.hm (da...@lang.hm) wrote:
 I am working with the rsyslog developers to improve it's performance in  
 inserting log messages to databases.

Great!

 currently they have a postgres interface that works like all the other  
 ones, where rsyslog formats an insert statement, passes that the the  
 interface module, that sends it to postgres (yes, each log as a seperate  
 transaction)

Ouch.

 the big win is going to be in changing the core of rsyslog so that it can 
 process multiple messages at a time (bundling them into a single  
 transaction)

Yup.

 1. begin; insert; insert;...;end

Doing the insert in a transaction should definitely improve your
performance.  Doing them as prepared statements would be good too, and
using binary mode would very likely help.

 2. insert into table values (),(),(),()

Using this structure would be more database agnostic, but won't perform
as well as the COPY options I don't believe.  It might be interesting to
do a large insert into table values (),(),() as a prepared statement,
but then you'd have to have different sizes for each different number of
items you want inserted.

 3. copy from stdin
   (how do you tell it how many records to read from stdin, or that you  
 have given it everything without disconnecting)

Assuming you're using libpq, you just call PQputCopyEnd().  Then you
call PQgetResult() to check that everything worked ok.

 4. copy from stdin in binary mode

Binary mode, in general, should be faster.  You should consider what
format the data is inside your application though (it's less useful to
use binary copy if you're having to convert from text to binary in your
application).

 and each of the options above can be done with prepared statements, 
 stored procedures, or functions.

 I know that using procedures or functions can let you do fancy things 
 like inserting the row(s) into the appropriate section of a partitioned 
 table

We would normally recommend having the database handle the partitioning
by using a trigger on the base table to call a stored procedure.  The
application really doesn't need to know about this.

 other than this sort of capability, what sort of differences should be  
 expected between the various approaches (including prepared statements vs 
 unprepared)

 since the changes that rsyslog is making will affect all the other  
 database interfaces as well, any comments about big wins or things to  
 avoid for other databases would be appriciated.

Hope this helps.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

2009-04-20 Thread david


On Mon, 20 Apr 2009, Stephen Frost wrote:


David,

* da...@lang.hm (da...@lang.hm) wrote:

I am working with the rsyslog developers to improve it's performance in
inserting log messages to databases.


Great!


currently they have a postgres interface that works like all the other
ones, where rsyslog formats an insert statement, passes that the the
interface module, that sends it to postgres (yes, each log as a seperate
transaction)


Ouch.


yep


the big win is going to be in changing the core of rsyslog so that it can
process multiple messages at a time (bundling them into a single
transaction)


Yup.


1. begin; insert; insert;...;end


Doing the insert in a transaction should definitely improve your
performance.  Doing them as prepared statements would be good too, and
using binary mode would very likely help.


2. insert into table values (),(),(),()


Using this structure would be more database agnostic, but won't perform
as well as the COPY options I don't believe.  It might be interesting to
do a large insert into table values (),(),() as a prepared statement,
but then you'd have to have different sizes for each different number of
items you want inserted.


on the other hand, when you have a full queue (lots of stuff to insert) is 
when you need the performance the most. if it's enough of a win on the 
database side, it could be worth more effort on the applicaiton side.



3. copy from stdin
  (how do you tell it how many records to read from stdin, or that you
have given it everything without disconnecting)


Assuming you're using libpq, you just call PQputCopyEnd().  Then you
call PQgetResult() to check that everything worked ok.


one of the big questions is what value we will get by making things 
database specififc (more below)



4. copy from stdin in binary mode


Binary mode, in general, should be faster.  You should consider what
format the data is inside your application though (it's less useful to
use binary copy if you're having to convert from text to binary in your
application).


any idea what sort of difference binary mode would result in?


and each of the options above can be done with prepared statements,
stored procedures, or functions.

I know that using procedures or functions can let you do fancy things
like inserting the row(s) into the appropriate section of a partitioned
table


We would normally recommend having the database handle the partitioning
by using a trigger on the base table to call a stored procedure.  The
application really doesn't need to know about this.


well, the trigger or stored procedure/funcion can be part of the database 
config, or loaded by the app when it starts.




one very big question is how much of a gain there is in moving from a 
database agnostic approach to a database specific approach.


currently rsyslog makes use of it's extensive formatting capabilities to 
format a string along the lines of

$DBformat=insert into table X values ('$timestamp','$msg');
then it hands the resulting string to the database interface module. This 
is a bit of a pain to setup, and not especially efficiant, but it has the 
ability to insert data into whatever schema you want to use (unlike a lot 
of apps that try to force you to use their schema)


I proposed a 5 variable replacement for this to allow for N log entries to 
be combined into one string to be sent to the database:


DBinit (one-time things like initialinzing prepared statements, etc)
DBstart (string for the start of a transaction)
DBjoin (tring to use to join multiple DBitems togeather)
DBend (string for the end of a transaction)
DBitem (formatting of a single action )

so you could do something like

DBstart = insert into table X values
DBjoin = ,
DBend = ;
DBitem = ('$timestampe','$msg')

and it would create a string like #2

this is extremely flexible. I think it can do everything except binary 
mode operations, including copy. It is also pretty database agnostic.


but people are asking about how to do binary mode, and some were thinking 
that you couldn't do prepared statements in Oracle with a string-based 
interface.


so I decided to post here to try and get an idea of (1) how much 
performance would be lost by sticking with strings, and (2) of all the 
various ways of inserting the data, what sort of performance differences 
are we talking about


David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

2009-04-20 Thread Greg Smith


On Mon, 20 Apr 2009, da...@lang.hm wrote:


any idea what sort of difference binary mode would result in?


The win from switching from INSERT to COPY can be pretty big, further 
optimizing to BINARY you'd really need to profile to justify.  I haven't 
found any significant difference in binary mode compared to overhead of 
the commit itself in most cases.  The only thing I consistently run into 
is that timestamps can bog things down considerably in text mode, but you 
have to be pretty efficient in your app to do any better generating those 
those in the PostgreSQL binary format yourself.  If you had a lot of 
difficult to parse data types like that, binary might be a plus, but it 
doesn't sound like that will be the case for what you're doing.


But you don't have to believe me, it's easy to generate a test case here 
yourself.  Copy some typical data into the database, export it both ways:


COPY t to 'f';
COPY t to 'f' WITH BINARY;

And then compare copying them both in again with \timing.  That should 
let you definitively answer whether it's really worth the trouble.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

Greg,

* Greg Smith (gsm...@gregsmith.com) wrote:
 The win from switching from INSERT to COPY can be pretty big, further  
 optimizing to BINARY you'd really need to profile to justify.  

Have you done any testing to compare COPY vs. INSERT using prepared
statements?  I'd be curious to know how those compare and against
multi-value INSERTS, prepared and unprepared.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] performance for high-volume log insertion

2009-04-20 Thread david


On Mon, 20 Apr 2009, Stephen Frost wrote:


David,

* da...@lang.hm (da...@lang.hm) wrote:

any idea what sort of difference binary mode would result in?


It depends a great deal on your application..


currently rsyslog makes use of it's extensive formatting capabilities to
format a string along the lines of
$DBformat=insert into table X values ('$timestamp','$msg');


Is this primairly the commands sent to the database?  If so, I don't
think you'll get much by going to binary-mode.  The text '$msg' isn't
going to be any different in binary.  The '$timestamp' would be, but I'm
guessing you'd have to restructure it some to match the PG binary
timestamp format and while that *would* be a win, I don't think it would
end up being all that much of a win.


the applicaiton is the log server pulling apart messages, reformatting 
them to whatever is appropriate for the database schema, and then 
inserting them into the database (for other applications to access, ones 
that rsyslog knows nothing about)


I used the example of a trivial table with timestamp and log message, but 
in most cases you will break out sending host and application as well, and 
in some cases may parse apart the log message itself. I have a use case 
where the message itself if pipe delimited, and I will want to do make use 
of the first four fields of the message (probably as seperate columns) 
before dumping the rest of the message into a text field.



I proposed a 5 variable replacement for this to allow for N log entries
to be combined into one string to be sent to the database:

DBinit (one-time things like initialinzing prepared statements, etc)
DBstart (string for the start of a transaction)
DBjoin (tring to use to join multiple DBitems togeather)
DBend (string for the end of a transaction)
DBitem (formatting of a single action )

so you could do something like

DBstart = insert into table X values
DBjoin = ,
DBend = ;
DBitem = ('$timestampe','$msg')

and it would create a string like #2


Using this textual representation for the DBitem would cause difficulty
for any kind of prepared statement usage (Oracle or PG), and so I would
really recommend getting away from it if possible.


that example would be, but the same mechanism would let you do


DBinit=PREPARE rsyslog_insert(date, text) AS\nINSERT INTO foo VALUES(\$1, 
\$2);

DBstart = begini;B\n
DBjoin = 
DBend = end;
DBitem = EXECUTE rsyslog_insert('$timestamp','$msg');\n

which would become

PREPARE rsyslog_insert(date, text) AS
INSERT INTO foo VALUES($1, $2);
begin;
EXECUTE rsyslog_insert('20090420-06:00', log1);
EXECUTE rsyslog_insert('20090420-06:00', log2);
EXECUTE rsyslog_insert('20090420-06:00', log3);
end;

which I think makes good use of prepared statements.


Instead, I would
encourage going with the PG (and Oracle, as I recall) structure of
having an array of pointers to the values.

Take a look at the documentation for PQexecParams here:
http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html

(note that you'll want to use PQprepare and PQexecPrepared in the end,
but the detailed documentation is under PQexecParams)

Basically, you would have:

DBnParams = 2;
DBparamValues[0] = ptr to $timestamp
DBparamValues[1] = ptr to $msg

If you just use the text format, you don't actually need anything else
for PG, just pass in NULL for paramTypes, paramLengths, and
paramFormats, and 0 for resultFormat.

Of course, if that's your only structure, then you can just make a C
struct that has those two pointers in it and simplify your API by
passing the struct around.


the database structure is not being defined by (or specificly for) 
rsyslog. so at compile time we have _no_ idea how many variables of what 
type there are going to be. my example of ($timestamp,$msg) was intended 
to just be a sample (avoiding typing out some elaberate set of parameters)


rsyslog provides the following items, which can be sliced and diced with 
substatutions, substrings, and additional inserted text.


msg the MSG part of the message (aka the message ;))

rawmsg 	the message excactly as it was received from the socket. Should be 
useful for debugging.


uxtradmsg   will disappear soon - do NOT use!

hostnamehostname from the message

source  alias for HOSTNAME

fromhost 	hostname of the system the message was received from (in a 
relay chain, this is the system immediately in front of us and not 
necessarily the original sender). This is a DNS-resolved name, except if 
that is not possible or DNS resolution has been disabled.


fromhost-ip 	The same as fromhost, but alsways as an IP address. Local 
inputs (like imklog) use 127.0.0.1 in this property.


syslogtag   TAG from the message

programname 	the static part of the tag, as defined by BSD syslogd. 
For example, when TAG is named[12345], programname is named.


pri PRI part of the message - undecoded (single value)

pri-text 	the PRI part of the message in a textual form (e.g. 
syslog.info)


iut the

Re: [PERFORM] performance for high-volume log insertion

David,

* da...@lang.hm (da...@lang.hm) wrote:
 the database structure is not being defined by (or specificly for)  
 rsyslog. so at compile time we have _no_ idea how many variables of what  
 type there are going to be. my example of ($timestamp,$msg) was intended  
 to just be a sample (avoiding typing out some elaberate set of 
 parameters)

That's fine.  I don't see any reason that the API I suggested be a
compile-time option.  Certainly, libpq has no idea how many, or what
kind, of types are being passed to it by the caller, that's why it *has*
the API that it does.  You just need to work out what each prepared
queries' parameters are and construct the necessary arrays.

 rsyslog provides the following items, which can be sliced and diced with  
 substatutions, substrings, and additional inserted text.

[...]

Looks like mainly text fields still, so you might want to just stick
with text for now.

 rsyslog message formatting provides tools for doing the nessasary 
 escaping (and is using it for the single insert messages today)

 prepared statements in text mode have similar problems (although they  
 _are_ better in defending against sql injection attacks, so a bit safer). 

Uhh, if you use prepared statements with PQexecPrepared, there *is no*
escaping necessary.  I'm not sure what you mean by 'similar problems'.
Can you elaborate on that?  If you mean doing 'prepared queries' by
using creating a string and then using PQexec with
'EXECUTE blah (1,2,3);' then you really need to go read the
documentation I suggested.  That's *not* what I'm getting at when I say
'prepared queries', I'm talking about a protocol-level well-defined
format for passing arguments independently of commands.  A call to
PQexecPrepared looks like this:

PQprepare(conn, myquery, INSERT INTO TAB1 VALUES ($1, $2);, 0, NULL);

values[0] = a;
values[1] = b;
PQexecPrepared(conn, myquery, 2, values, NULL, NULL, 0);

Note that we don't ever send an 'EXECUTE myquery (1,2,3);' type of thing
to libpq.  libpq will handle the execute and the parameters and whatnot
as part of the PG 3.0 protocol.

 I don't see how you would easily use the API that you pointed me at above 
 without having to know the database layout at compile time.

The arrays don't have to be of fixed length..  You can malloc() them at
runtime based on the number of parameters which are being used in a
particular message.  Perhaps what I'm missing here is exactly what
you're expecting the user to provide you with versus what you're going
to be giving to libpq.  I have been assuming that you have a format
definition system already in place that looks something like:

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Which you then parse, figure out what the escape codes mean, and build
some structure which understands the whole thing at an abstract level.
For example, you should know that '%Y' is the first variable, is an
integer, etc.

From this, you can determine that there are 6 parameters, at runtime.
You can then malloc() an array with 6 char* pointers.  Then, when you
have those 6 strings somewhere, you just set each of your array
parameters to the appropriate in-memory string address, eg:

array = malloc(sizeof(char*) * num_of_params);
for (int i = 0; i  num_of_params; i++) {
switch(params[i]) {
case 'Y':   array[i] = my_year_string; break;
case 'm':   array[i] = my_month_string; break;
}
}

etc, until you eventually have your array of pointers, with a valid
in-memory string somewhere for each pointer, that you can then turn
around and pass to PQexecPrepared.  Obviously, you don't have to
malloc() every time, if you keep track of each type of message.

 I agree that defining a fixed table layout and compiling that knowledge  
 into rsyslog is the safest (and probably most efficiant) way to do 
 things, but there is no standard for log messages in a database, and 
 different people will want to do different things with the logs, so I 
 don't see how a fixed definition could work.

I didn't intend to imply that you have to use a fixed definition, just
that if you currently only have 1 then you might as well.  It's entirely
possible to support any definition using the API I suggested.

 so the binary mode only makes a difference on things like timestamps and  
 numbers? (i.e. no significant added efficiancy in processing the command  
 itself?)

I'm slightly confused by what you mean by this?  Binary mode is for
parameters only, commands are never 'binary'.  Binary mode just means
that the application gives the value to the database in the format which
it expects, and so the database doesn't have to translate a textual
representation of a value into the binary format the database needs.

 thanks for taking the time to answer, I was trying to keep the problem  
 definition small and simple, and from your reply it looks like I made it  
 too simple.

Yes, probably.  You likely assumed that I knew something about how

Re: [PERFORM] performance for high-volume log insertion