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

On Tue, 21 Apr 2009, Stephen Frost wrote: * da...@lang.hm (da...@lang.hm) wrote: while I fully understand the 'benchmark your situation' need, this isn't that simple. It really is. You know your application, you know it's primary use cases, and probably have some data to play with. You're c

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

On Tue, 21 Apr 2009, Stephen Frost wrote: David, * da...@lang.hm (da...@lang.hm) wrote: I thought that part of the 'efficiancy' and 'performance' to be gained from binary modes were avoiding the need to parse commands, if it's only the savings in converting column contents from text to specifi

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

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 t

Re: [PERFORM] GiST index performance

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 alrea

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 prepa

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

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 m

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

"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

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

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

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

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

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

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

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

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 rat

Re: [PERFORM] SQL With Dates

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

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

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

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

* Ben Chobot (be...@silentmedia.com) wrote: > On Mon, 20 Apr 2009, da...@lang.hm wrote: >> one huge advantage of putting the sql into the configuration is the >> ability to work around other users of the database. > > +1 on this. We've always found tools much easier to work with when they > coul

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

On Tue, 21 Apr 2009, Stephen Frost wrote: * Ben Chobot (be...@silentmedia.com) wrote: On Mon, 20 Apr 2009, da...@lang.hm wrote: one huge advantage of putting the sql into the configuration is the ability to work around other users of the database. +1 on this. We've always found tools much ea

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

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 packa

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

* da...@lang.hm (da...@lang.hm) wrote: > I think the key thing is that rsyslog today doesn't know anything about > SQL variables, it just creates a string that the user and the database > say looks like a SQL statement. err, what SQL variables? You mean the $NUM stuff? They're just placeholde

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

On Tue, 21 Apr 2009, Greg Smith wrote: On Mon, 20 Apr 2009, da...@lang.hm wrote: while I fully understand the 'benchmark your situation' need, this isn't that simple. in this case we are trying to decide what API/interface to use in a infrastructure tool that will be distributed in common di

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

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 c

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

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

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

On Tue, 21 Apr 2009, Stephen Frost wrote: * da...@lang.hm (da...@lang.hm) wrote: I think the key thing is that rsyslog today doesn't know anything about SQL variables, it just creates a string that the user and the database say looks like a SQL statement. err, what SQL variables? You mean th

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

On Tue, 21 Apr 2009, da...@lang.hm wrote: I see that you use %blah% to define variables inside your string. That's fine. There's no reason why you can't use this exact syntax to build a prepared query. No user-impact changes are necessary. Here's what you do: for some reason I was stuck o

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

* da...@lang.hm (da...@lang.hm) wrote: >> Ignoring the fact that this is horrible, horrible non-SQL, > > that example is for MySQL, nuff said ;-) indeed. > for some reason I was stuck on the idea of the config specifying the > statement and variables seperatly, so I wasn't thinking this way, ho

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

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

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

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 ha

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

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

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

"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

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

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

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

On Tue, 21 Apr 2009, Stephen Frost wrote: * James Mansion (ja...@mansionfamily.plus.com) wrote: da...@lang.hm wrote: on the other hand, when you have a full queue (lots of stuff to insert) is when you need the performance the most. if it's enough of a win on the database side, it could be wort

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

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 performan

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

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 compl