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

[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

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

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

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


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

[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

[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)
>
> 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 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 indexi

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

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


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


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.


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


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:
.
I refactored the UNION definition slightly to attempt to bring all
the column types into alignment, as that seemed like it might be a
block

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

[Tom Lane]

"John L. Clark"  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

[John L. Clark]

On Tue, Apr 21, 2009 at 10:35 AM, Tom Lane  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

[Tom Lane]

"John L. Clark"  writes:
> On Tue, Apr 21, 2009 at 10:35 AM, Tom Lane  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

[david]

Kenneth,
  could you join the discussion on the rsyslog mailing list?
rsyslog-users 

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 
To: Richard Huxton 
Cc: da...@lang.hm, Stephen Frost ,
Greg Smith , 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 do three (time, server, everything else), and others
will go further (I know some who would li

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

[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 
>
> 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 
>> To: Richard Huxton 
>> Cc: da...@lang.hm, Stephen Frost ,
>> Greg Smith , 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-SETTIN

Re: [PERFORM] SQL With Dates

[Robert Haas]

On Mon, Apr 20, 2009 at 9:55 AM, Rafael Domiciano
 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

[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

[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

[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

[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

[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

[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

[John L. Clark]

On Tue, Apr 21, 2009 at 12:05 PM, John L. Clark  wrote:
> On Tue, Apr 21, 2009 at 10:50 AM, Tom Lane  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 
.
 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

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

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

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




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

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




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

[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

[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

[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

[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

[Tom Lane]

"John L. Clark"  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 
> .

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

[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

[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

[Robert Haas]

On Tue, Apr 21, 2009 at 8:12 PM,   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

[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