date:20090421

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

2009-04-21 Thread david


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

2009-04-21 Thread david


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

2009-04-21 Thread david


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

2009-04-21 Thread Stephen Frost

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

2009-04-21 Thread Stephen Frost

* 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

2009-04-21 Thread Stephen Frost

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

2009-04-21 Thread david


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

2009-04-21 Thread david


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] GiST index performance

2009-04-21 Thread Matthew Wakeling


On Mon, 20 Apr 2009, Teodor Sigaev wrote:

Looks like contrib/cube has the same error.  I don't see a similar code
pattern elsewhere though.  Oleg, Teodor, do you concur that this is a
correct patch?  Is it safe to back-patch (I think it should be)?
Yeah, good catch, and it doesn't touch any already-on-disk data. Although 
release notes should mention advice about REINDEX seg and cube opclasses.


Unfortunately, it seems there is another bug in the picksplit function. 
My patch fixes a bug that reveals this new bug. The whole picksplit 
algorithm is fundamentally broken, and needs to be rewritten completely, 
which is what I am doing.


If you apply my patch, then index sizes will go up by a factor of ten or 
so, because the picksplit function tends to split the set of 367 ranges 
into one set of 366 and another set of 1, leading to a horribly unbalanced 
tree. Before the patch, the different branches of the tree were 
unselective, so new entries would just get stuffed in anywhere, leading to 
a much more balanced tree.


I shall have a proper fix to this problem later today.

Matthew

--
It's one of those irregular verbs - I have an independent mind, You are
an eccentric, He is round the twist.
 -- Bernard Woolly, Yes Prime Minister

--
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,

[PERFORM] WHERE condition not being pushed down to union parts

2009-04-21 Thread John L. Clark

I have a database with two tables that relate similar data, and a view
which projects and combines the data from these two tables in order to
access them both in a consistent manner.  With enough information, the
application can specifically choose to query from one table or the
other, but in the more general case the data could come from either
table, so I need to query the view.  When I join against the view (or
an equivalent subselect), however, it looks like the joining condition
is not pushed down into the individual components of the union that
defines the view.  This leads to a significant performance degradation
when using the view; I ask the list for help in resolving this
problem.  The remainder of this email digs into this problem in
detail.

(If you were interested in background on this database, it implements
a backing store for a higher level RDF database, specifically for the
RDFLib project.  I would be happy to talk more about this application,
or the corresponding database design issues, with anyone who might be
interested, in whatever forum would be appropriate.)

I begin with the poorly performing query, which follows this
paragraph.  This query joins one of the tables to the view, and using
'explain' on this query gives the query plan listed below the query.
Note that in this query plan, the join filter happens after (above)
the collection of matching rows from each of the parts of the UNION.

query
select * from
  relations as component_0_statements
cross join
  URI_or_literal_object as component_1_statements
where
component_0_statements.predicate = -2875059751320018987 and
component_0_statements.object = -2827607394936393903 and
component_1_statements.subject = component_0_statements.subject and
component_1_statements.predicate = -2875059751320018987
/query

query-plan

QUERY PLAN
---
 Nested Loop  (cost=96.31..36201.57 rows=1 width=128)
  Join Filter: (component_0_statements.subject = literalproperties.subject)
  -  Index Scan using relations_poscindex on relations
component_0_statements  (cost=0.00..9.96 rows=1 width=40)
Index Cond: ((predicate = (-2875059751320018987)::bigint) AND
(object = (-2827607394936393903)::bigint))
  -  Append  (cost=96.31..36044.62 rows=11759 width=88)
-  Bitmap Heap Scan on literalproperties
(cost=96.31..16190.72 rows=5052 width=49)
  Recheck Cond: (literalproperties.predicate =
(-2875059751320018987)::bigint)
  -  Bitmap Index Scan on
literalproperties_predicateindex  (cost=0.00..95.04 rows=5052 width=0)
Index Cond: (literalproperties.predicate =
(-2875059751320018987)::bigint)
-  Bitmap Heap Scan on relations  (cost=128.99..19736.31
rows=6707 width=40)
  Recheck Cond: (relations.predicate =
(-2875059751320018987)::bigint)
  -  Bitmap Index Scan on relations_predicateindex
(cost=0.00..127.32 rows=6707 width=0)
Index Cond: (relations.predicate =
(-2875059751320018987)::bigint)
(13 rows)
/query-plan

As it turns out, all of the results are in fact from the 'relations'
table, so we get the same results if we query that table instead of
the more general view.  The corresponding query follows this
paragraph, and its query plan immediately follows it.  Note that in
this query plan, the join condition is pushed down to the leaf node as
an Index Condition, which seems to be the main source of the dramatic
performance difference.

query
select * from
  relations as component_0_statements
cross join
  relations as component_1_statements
where
component_0_statements.predicate = -2875059751320018987 and
component_0_statements.object = -2827607394936393903 and
component_1_statements.subject = component_0_statements.subject and
component_1_statements.predicate = -2875059751320018987
/query

query-plan

QUERY PLAN
---
 Nested Loop  (cost=0.00..26.11 rows=1 width=80)
  -  Index Scan using relations_poscindex on relations
component_0_statements  (cost=0.00..9.96 rows=1 width=40)
Index Cond: ((predicate = (-2875059751320018987)::bigint) AND
(object = (-2827607394936393903)::bigint))
  -  Index Scan using relations_subjectindex on relations
component_1_statements  (cost=0.00..16.13 rows=1 width=40)
Index Cond: (component_1_statements.subject =
component_0_statements.subject)
Filter: (component_1_statements.predicate =
(-2875059751320018987)::bigint)
(6 rows)
/query-plan

My research led me to a post by Tom Lane describing the conditions in
which the WHERE conditions cannot be pushed down to the UNION parts:
http://archives.postgresql.org/pgsql-performance/2007-11/msg00041.php.
I refactored the UNION definition slightly to attempt to bring all
the column types

Re: [PERFORM] WHERE condition not being pushed down to union parts

2009-04-21 Thread Tom Lane

John L. Clark j...@po.cwru.edu writes:
 I have a database with two tables that relate similar data, and a view
 which projects and combines the data from these two tables in order to
 access them both in a consistent manner.  With enough information, the
 application can specifically choose to query from one table or the
 other, but in the more general case the data could come from either
 table, so I need to query the view.  When I join against the view (or
 an equivalent subselect), however, it looks like the joining condition
 is not pushed down into the individual components of the union that
 defines the view.

You never mentioned what PG version you are using, but I'm betting
it's 8.1.x.  This should work the way you are expecting in 8.2 and up.

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] WHERE condition not being pushed down to union parts

2009-04-21 Thread John L. Clark

On Tue, Apr 21, 2009 at 10:35 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 You never mentioned what PG version you are using, but I'm betting
 it's 8.1.x.  This should work the way you are expecting in 8.2 and up.

Naturally, I would forget (at least) one critical piece of information:

$ pg_config --version
PostgreSQL 8.3.7

Other ideas?

Take care,

John L. Clark

-- 
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] WHERE condition not being pushed down to union parts

2009-04-21 Thread Tom Lane

John L. Clark j...@po.cwru.edu writes:
 On Tue, Apr 21, 2009 at 10:35 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 You never mentioned what PG version you are using, but I'm betting
 it's 8.1.x.  This should work the way you are expecting in 8.2 and up.

 Naturally, I would forget (at least) one critical piece of information:

 $ pg_config --version
 PostgreSQL 8.3.7

In that case you're going to need to provide a reproducible test case,
'cause it worksforme.

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-21 Thread david


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 will

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
be a

Re: [PERFORM] SQL With Dates

2009-04-21 Thread Robert Haas

On Mon, Apr 20, 2009 at 9:55 AM, Rafael Domiciano
rafael.domici...@gmail.com wrote:
 Hello People,

 I have initiated a work to review the sqls of our internal software.
 Lot of them he problem are about sql logic, or join with table unecessary,
 and so on.
 But software has lot of sql with date, doing thinks like:
 [..]
   date = '2009-04-01' AND
   date = '2009-04-15'
 [..]

 Redoing the SQL with fix date (date = '2009-04-01') o cost in explain always
 still about 200 or less. But with a period the cost is high, about 6000 or
 more.

 Select is using Index and the date is using index too.

 There is some way to use date period with less cost?

If you have an actual performance problem (as opposed to a big number
in EXPLAIN), then it's possible that the planner isn't estimating the
number of rows that will be in that range very accurately.  In that
case, you might need to increase the statistics target for that
column, or your default_statistics_target.

In 8.3, the default default_statistics_target = 10.  In 8.4, it will
be 100, so you might try that for a starting point.  But date columns
can sometimes have highly skewed data, so you might find that you need
an even higher value for that particular column.  I wouldn't recommend
raising the database-wide setting above 100 though (though I know some
people have used 200 or 400 without too much pain, especially on
Really Big Databases where longer planning time isn't a big deal
because the execution times are measured in minutes - it doesn't sound
like that's your situation though).

The first thing, to do, is see how fast the query actually runs.  Try
setting \timing in psql and running the query to see how long it
actually takes.  If it's fast enough, you're done.  If not, run
EXPLAIN ANALYZE and compare the estimated row counts to t he actual
row counts.  If they're pretty close, you're out of luck - as others
have already said, TANSTAAFL.  If they're way off, the try the above.

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

2009-04-21 Thread Stephen Frost

* 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

2009-04-21 Thread david


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

2009-04-21 Thread Stephen Frost

* 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

2009-04-21 Thread david


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] WHERE condition not being pushed down to union parts

2009-04-21 Thread John L. Clark

On Tue, Apr 21, 2009 at 12:05 PM, John L. Clark j...@po.cwru.edu wrote:
 On Tue, Apr 21, 2009 at 10:50 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 In that case you're going to need to provide a reproducible test case,
 'cause it worksforme.

 Ok.  I scaled back my example by just selecting 1000 random rows
 from each of the component tables.  The resulting database dump should
 be attached to this email.  I tried a very small subset (just 10
 rows), but the resulting tables were small enough that the query plans
 were changing to use scans.  Note that I haven't actually run sample
 queries with this smaller dataset.  I have only been inspecting the
 query plans of the two queries that I listed in my original message,
 and the results are the same, except that the magnitude of the costs
 are scaled down.  This scaling leads to a smaller performance penalty,
 but the query plan still shows that the join filter is still not being
 pushed down in the case of the view (built from a union).

I posted this earlier, but I haven't seen it come through the mailing
list, perhaps because of the attachment.  I have also posted the
attachment at 
http://infinitesque.net/temp/union_performance_2009-04-21.postgresql.dump.gz.
 The MD5 checksum is 3942fee39318aa5d9f18ac2ef3c298cf.  If the
original does end up coming through, I'm sorry about the redundant
post.

Take care,

John L. Clark

-- 
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

2009-04-21 Thread david


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

2009-04-21 Thread david


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

2009-04-21 Thread Stephen Frost

* 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

2009-04-21 Thread Stephen Frost

* 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

2009-04-21 Thread Stephen Frost

* 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] WHERE condition not being pushed down to union parts

2009-04-21 Thread Tom Lane

John L. Clark j...@po.cwru.edu writes:
 I posted this earlier, but I haven't seen it come through the mailing
 list, perhaps because of the attachment.  I have also posted the
 attachment at 
 http://infinitesque.net/temp/union_performance_2009-04-21.postgresql.dump.gz.

Ah.  The problem is that your view contains constants in the UNION arms:

CREATE VIEW uri_or_literal_object AS
SELECT literalproperties.subject, literalproperties.subject_term, 
literalproperties.predicate, literalproperties.predicate_term, 
literalproperties.object, 'L'::character(1) AS object_term, 
literalproperties.context, literalproperties.context_term, 
literalproperties.data_type, literalproperties.language FROM literalproperties
UNION ALL
SELECT relations.subject, relations.subject_term, relations.predicate, 
relations.predicate_term, relations.object, relations.object_term, 
relations.context, relations.context_term, NULL::bigint AS data_type, 
NULL::character varying(3) AS language FROM relations;

In 8.2 and 8.3, the planner is only smart enough to generate
inner-indexscan nestloop plans on UNIONs if all the elements of the
SELECT lists are simple variables (that is, table columns).
8.4 will be smarter about this.

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-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

2009-04-21 Thread david


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

37 matches

Mail list logo