Re: [GENERAL] Support for idempotent schema changes?
Peter Eisentraut wrote: You just ignore the error if the object already exists. I'd advice against that or at least make sure that only "the thing already exists" errors get ignored. Because otherwise it's 100% impossible to discover any real problems with the scripts. -- Regards Flemming Frandsen - YAPH - http://dion.swamp.dk ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Performance of the listen command
I cache a lot of data in the application, to invalidate that cache I have triggers that use notify to let new transactions eject the objects from the cache. My problem with listen is that when I start a new connection I run a listen command for each of my tables, that loop takes around 10 seconds to complete and I have around 150 tables. The strange thing is that just about any query runs in 1-4ms, but the listen command, which shouldn't even have to touch the disk takes around 60ms. Is it normal for listen to be so slow? ... and what can be done to help the situation? -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performance of the listen command
Michael Fuhr wrote: Also, based on a 60ms-per-listen time I suspect you're not doing the listens in a transaction, so each listen is its own transaction that has to be committed, resulting in a disk hit. Try doing them all in one transaction. I think I am doing the listens in a transaction, as I connect via DBI with AutoCommit=>0, unless there is some bug that causes the listens to not start a new transaction when using DBI. I also see a problem with the first query I run in a transaction, it takes a very long time, even if it's simply a "select 6*7", I'm guessing this is because a new transaction is started, is there any way to improve performance of that? Alvaro Herrera wrote: > Do you regularly vacuum the pg_listener table? No, but this is on a system that has been running for a couple of days. -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Performance of the listen command
Michael Fuhr wrote: How long is "a very long time"? More than 2ms, typically 50-150ms. The other queries (that actually fetch data from tables and do real work) execute in 1-4ms. The funny thing is that if I run the select 6*7 after ping (which does a select 'somestring') then the select 6*7 runs in normal time and the ping is slow, so it seems to me that it's the first query that's being hit. Maybe it's the overhead of starting a new transaction? Does the first query's time include the time to connect or do you start timing after the connection has been made? This is on a used connection that I just pulled out of my connection pool, so it's simply the first query in the new transaction. How often are you doing the listens? Every time an apache process connects to the DB. > I just did a test in which I connected to a database 100 times and issued 150 listens in each connection. By the 100th connection the time to execute the listens had increased by an order of magnitude due to bloat in pg_listener. Vacuuming pg_listener brought the times down again. Thank you *so* much for confirming that I'm not out of my mind, I just did the vacuum and it did indeed fix the problem, listens are now done in less than 1ms. I just looked at the pg_listener table: zepong-> \d+ pg_listener Table "pg_catalog.pg_listener" Column| Type | Modifiers | Description --+-+---+- relname | name| not null | listenerpid | integer | not null | notification | integer | not null | Has OIDs: no ... and noticed the complete lack of indexen, surely this must be a bug? When trying to create the index I get told off by pg: create unique index pg_listeners on pg_listener (relname, listenerpid); ERROR: permission denied: "pg_listener" is a system catalog Any ideas, other than run VACUUM pg_listener every 10 minutes? What's the output of "VACUUM VERBOSE pg_listener"? If you vacuum pg_listener do the listens run faster? zepong=> VACUUM VERBOSE pg_listener; INFO: vacuuming "pg_catalog.pg_listener" INFO: "pg_listener": removed 243467 row versions in 3290 pages DETAIL: CPU 0.24s/0.38u sec elapsed 8.61 sec. INFO: "pg_listener": found 243467 removable, 113 nonremovable row versions in 3290 pages DETAIL: 5 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.27s/0.40u sec elapsed 8.65 sec. VACUUM zepong=> VACUUM VERBOSE pg_listener; INFO: vacuuming "pg_catalog.pg_listener" INFO: "pg_listener": removed 1207 row versions in 17 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_listener": found 1207 removable, 108 nonremovable row versions in 3290 pages DETAIL: 0 dead row versions cannot be removed yet. There were 242413 unused item pointers. 0 pages are entirely empty. CPU 0.02s/0.00u sec elapsed 0.03 sec. VACUUM -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Performance of the listen command
Michael Fuhr wrote: What's the output of "VACUUM VERBOSE pg_listener"? If you vacuum pg_listener do the listens run faster? Actually now that You mentioned vacuming, I tried doing a VACUUM and after it was done request time was down to around a tenth of what it was before. iow. requests that took 500ms before are now down to around 25ms. I will now hang my head in shame, please forgive me. -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance of the listen command
Christopher Browne wrote: There's a demerit: c) If there are a LOT of events, that might not fit in memory nicely. If you have that many events then the current implementation is going to suck hard as well:) Keeping the listener structure in memory wouldn't be too bad. I'd guess that each connection that listens only needs a single transaction pointer (64bit int or somesuch?) to point to the transaction that it has caught up with. Each transaction will need some way of recording what events were seen, like: * a bitmap of the events seen during that transaction (to be useful you'd need a global string->bit table, this needs some way of sharing/locking that global datastructure though). * an ordered list of events seen to allow a simple binary search. * a more complicated datastructure like a JudySL array If you really want to save space then a lump of the pr-transaction bitmaps can be merged if there are no listeners that point to those transactions. Any ideas, other than run VACUUM pg_listener every 10 minutes? Remarkably enough, that is exactly the solution that the Slony-I, which makes use of LISTEN/NOTIFY, uses. The Slony-I cleanup thread vacuums pg_listener (and some other tables) every 10 minutes. Damnit, I thought I was kidding:) -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] LISTEN considered dangerous
I have an application that does aggresive caching of data pulled from the database, it even keeps the objects cached between transactions. Normally this works very well and when the cache is warmed up about 90% of the database time is saved. However that leaves the problem of how to notice that my cached objects have turned stale, luckily pg has the listen/notify feature so I have triggers on all tables that do a notify, as you do. However that just doesn't work, because listen is broken, allow me to illustrate, here A and B are two clients: A: BEGIN A: SELECT * FROM foo and cache the result. A: LISTEN foochange B: BEGIN B: update foo B: NOTIFY foochange B: COMMIT A: COMMIT When A continues with an other transaction it will never get the event from B and thus will keep using the cached foo data, clearly this is not what you'd want. The workaround is to commit after the listen, but that too is broken because then you'd commit all the changes up to that point, also not a desirable situation. The only real way to work around the problem is to LISTEN to every single object that could ever be interesting to cache and commit right after connecting the first time. The reason for this is that LISTEN is implemented by inserting into a table that's under transaction control (well naturally), so the actual listening doesn't start until the transaction has been committed. I'm quite lucky I didn't get any corrupted data from this gotcha, but I did get som annoyed users, so let this be a warning to other pg users. The correct behaviour would be to start listening at the begining of the transaction, when committed, IMHO. To allow this the entire implementation needs to change so all events from all transactions are stored until all connections with earlier started transactions have started new transactions. This way we could even have wildcard listens, imagine doing a listen % and getting all the generated events:) -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] LISTEN considered dangerous
Ian Harding wrote: NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY. But it can be disconcerting if one is expecting the notification events to be delivered immediately. Yes, that's very nice, but it doesn't have *anything* to do with what I posted about. I'm bothered by listen listening from the end of the transaction in stead of the start of the transaction. -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] LISTEN considered dangerous
Martijn van Oosterhout wrote: A: BEGIN A: SELECT * FROM foo and cache the result. A: LISTEN foochange B: BEGIN B: update foo B: NOTIFY foochange B: COMMIT A: COMMIT > Eh? At the point the LISTEN is run, the NOTIFY hasn't committed, so a row is inserted. At the time the NOTIFY is committed, the LISTEN hasn't committed yet so won't be visible. Only LISTEN is stored, not NOTIFY so there's nothing wrong with the read-committed semantics. No, that's all well and good it behaves as you'd expect when you know how it's implemented, that doesn't make it more usable though. It's slightly surprising though. I havn't seen anyone else complain about this before though. I'm likely to do that:) The only way to fix this is to make the LISTEN completely atransactional, so NOTIFY can see uncomitted LISTENs also. Not at all. The listen should simply listen for events issued at the start of the transaction it's executed in. To do that without timetravel we'd need to store all events from all transactions, but that's not too bad if it's implemented right. -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] LISTEN considered dangerous
Tom Lane wrote: However, I'm unconvinced that the OP's complaint is valid. I'm unconvinced that I've stated the problem clearly enough. I would still expect any reimplementation of notify messaging to honor the principle that a LISTEN doesn't take effect till you commit. Naturally, the listen should not do anything at all when followed by a rollback. However if you start listening in a transaction then you should get all events that have happened after the snapshot that the transaction represents (the start of the transaction). That means that events that happen after the start of the transaction have to be returned in the next transaction. -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] LISTEN considered dangerous
On 2 Aug 2006, Harald Fuchs wrote: > > all events that have happened after the snapshot that the transaction > > represents (the start of the transaction). > > Here you're contradicting yourself. In your second paragraph you > state that LISTEN should get events unless later cancelled by a > ROLLBACK. How should LISTEN know if its transaction will commit or > abort? No, I'm not contradicting myself. What I'm saying is that if you listen for an event then that event should be delivered in the next transaction, if it happens after the start of the transaction that you listen in. The reason for this is that while you are in a transaction you see the world as it existed at the time the transaction was started. So if you decide that you want to be told about something then that wish has to be in effect from the time of the start of the transaction, because otherwise the state of the delivered events is not consistent with the state of the rest of the data. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] LISTEN considered dangerous
On Wed, 2 Aug 2006, Tom Lane wrote: > Flemming Frandsen <[EMAIL PROTECTED]> writes: > > The listen should simply listen for events issued at the start of the > > transaction it's executed in. > > BEGIN; > SELECT sleep(10); > LISTEN foo; > > No, I don't think so. And why would that be a problem? There is no reason to assume that there would be any overhead in storing a list of outstanding events for your connection compared to today. What would happen in your example is that all the other connections leave this slow transaction behind, but in stead of storing all the events for all the transactions you can simply merge them all into one lump of events that are waiting to be delivered to that slow client. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] LISTEN considered dangerous
On Wed, 2 Aug 2006, Roman Neuhauser wrote: > > I'm bothered by listen listening from the end of the transaction in > > stead of the start of the transaction. > > Sorry if this isn't what you're after, instead just a question: > > Why don't you issue the LISTEN in a separate transaction before > going on? > > LISTEN foo; > BEGIN; > SELECT ... Well that's exactly what I do, but in stead of doing LISTEN foo I do 155x LISTEN foo before committing. It's not too bad as it only has to happen once pr. connection, but it creates a huge amount of churn in the pg_listeners table and there is an extra delay, simply because of the 155 roundtrips and the extra commit. I think I might put the listen stuff in a stored procedure to cut down on the roundtrips, but it's still not ideal. Many of my connections don't need the full 155 listens, so what I'd really like to be able to do is: select * from foo; listen foochanges; and actually get events if foo is updated compared to the state that the select read. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] LISTEN considered dangerous
On Thu, 3 Aug 2006, Martijn van Oosterhout wrote: > > There is no reason to assume that there would be any overhead in storing a > > list of outstanding events for your connection compared to today. > > Err, yes there would. Think about it: for that example to work, the > server would have to store every notify that happened until your > transaction completed. That's exactly the same problem as exists today, although one transaction earlier. If someone listens to a bunch of events, does a commit and THEN waits around forever then you still have to store all the events from that point on. The only difference is that it would have to happen from the start of the transaction in stead of from the end of the transaction. > That could be thousands, considering you can defer indefinitly. Well, if the system still discards duplicate events then there is a limit on how many events you can get. Neighbouring transactions don't need to be kept around so they can be merged into one. In the end you end up with storing just one instance of the unique events destined for each connection along with one instance of the unique events generated during each transaction. > And at the end of your transaction it has to go through the list and > throw away 99% of them because they're not for "foo" but for something else. Well, my application would probably be insterested in every single event, but filtering for interesting events is really not a problem. > Currently NOTIFY requires no storage at all, so what you're suggesting > is fairly expensive, since the cost would be applied for every transaction, > even ones that don't use LISTEN. Well, in my experience all transactions use listen, or none of them do. You rarely have a situation where different clients access the same database and even more rarely different clients that use different methods to access it. Either you need events when working with a given database instance or you don't. > The solution is to do the LISTEN first, outside the transaction. Yes, this is the workaround I use. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] LISTEN considered dangerous
On Fri, 4 Aug 2006, Martijn van Oosterhout wrote: > Really? Even pg_dump cares? Or your maintainence scripts > (VACUUM/ANALYZE)? Ok, those clients don't, but you rarely have many vacuum/pg_dump processes going on at the same time, so storing the events for them and throwing them away is not that big of a deal imho. > I'd have to disagree though. In most of the systems I've worked with > the database is in the center of the system. It'd be access by CGI > scripts, cron job, batch jobs started by a person, load triggered by > emails, etc. These may all use very different methods of accessing the > database. Even if an application used LISTEN/NOTIFY, I can't imagine > any bulk load/store being interested. Hmm, maybe you are right:) Maybe a new implementation should be able to do both. That way you could set the timetravel option on the begin statement: begin listen now So transactions that like to get all events they listen for during the transaction can and everybody else will get only events that happen after they commit. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
