Re: [PERFORM] Best replication solution?
On Tue, Apr 07, 2009 at 10:31:02PM +1200, Mark Kirkwood wrote: From my experience - gained from unwittingly being in the wrong place at the wrong time and so being volunteered into helping people with Slony failures - it seems to be quite possible to have nodes out of sync and not be entirely aware of it I should have stated that differently. First, you're right that if you don't know where to look or what to look for, you can easily be unaware of nodes being out of sync. What's not a problem with Slony is that the nodes can get out of internally consistent sync state: if you have a node that is badly lagged, at least it represents, for sure, an actual point in time of the origin set's history. Some of the replication systems aren't as careful about this, and it's possible to get the replica into a state that never happened on the origin. That's much worse, in my view. In addition, it is not possible that Slony's system tables report the replica as being up to date without them actually being so, because the system tables are updated in the same transaction as the data is sent. It's hard to read those tables, however, because you have to check every node and understand all the states. Complexity seems to be the major evil here. Yes. Slony is massively complex. simpler to administer. Currently it lacks a couple of features Slony has (chained slaves and partial DDL support), but I'll be following its development closely - because if these can be added - whilst keeping the operator overhead (and the foot-gun) small, then this looks like a winner. Well, those particular features -- which are indeed the source of much of the complexity in Slony -- were planned in from the beginning. Londiste aimed to be simpler, so it would be interesting to see whether those features could be incorporated without the same complication. A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Best replication solution?
On Sun, Apr 05, 2009 at 11:36:33AM -0700, Lists wrote: *Slony-I* - I've used this in the past, but it's a huge pain to work with, caused serious performance issues under heavy load due to long running transactions (may not be the case anymore, it's been a while since I used it on a large database with many writes), and doesn't seem very reliable (I've had replication break on me multiple times). It is indeed a pain to work with, but I find it hard to believe that it is the actual source of performance issues. What's more likely true is that it wasn't tuned to your write load -- that _will_ cause performance issues. Of course, tuning it is a major pain, as mentioned. I'm also somewhat puzzled by the claim of unreliability: most of the actual replication failures I've ever seen under Slony are due to operator error (these are trivial to induce, alas -- aforementioned pain to work with again). Slony is baroque and confusing, but it's specifically designed to fail in safe ways (which is not true of some of the other systems: several of them have modes in which it's possible to have systems out of sync with each other, but with no way to detect as much. IMO, that's much worse, so we designed Slony to fail noisily if it was going to fail at all). *Mammoth Replicator* - This is open source now, is it any good? It sounds like it's trigger based like Slony. Is it based on Slony, or simply use a similar solution? It's completely unrelated, and it doesn't use triggers. I think the people programming it are first-rate. Last I looked at it, I felt a little uncomfortable with certain design choices, which seemed to me to be a little hacky. They were all on the TODO list, though. *SkyTools/Londiste* - Don't know anything special about it. I've been quite impressed by the usability. It's not quite as flexible as Slony, but it has the same theory of operation. The documentation is not as voluminous, although it's also much handier as reference material than Slony's (which is, in my experience, a little hard to navigate if you don't already know the system pretty well). A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Occasional Slow Commit
On Mon, Oct 27, 2008 at 05:23:37PM -0700, David Rees wrote: However, occasionally, processing time will jump up significantly - the average processing time is around 20ms with the maximum processing time taking 2-4 seconds for a small percentage of transactions. Ouch! Turning on statement logging and analyzing the logs of the application itself shows that step #4 is the culprit of the vast majority of the slow transactions. My bet is that you're waiting on checkpoints. Given that you're on 8.3, start fiddling with the checkpoint_completion_target parameter. 0.7 might help. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Slow updates, poor IO
Hi, On Fri, Sep 26, 2008 at 07:24:55AM +1200, John Huttley wrote: I've just had an interesting encounter with the slow full table update problem that is inherent with MVCC Quite apart from the other excellent observations in this thread, what makes you think this is an MVCC issue exactly? A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Choosing a filesystem
On Thu, Sep 11, 2008 at 06:29:36PM +0200, Laszlo Nagy wrote: The expert told me to use RAID 5 but I'm hesitating. I think that RAID 1+0 would be much faster, and I/O performance is what I really need. I think you're right. I think it's a big mistake to use RAID 5 in a database server where you're hoping for reasonable write performance. In theory RAID 5 ought to be fast for reads, but I've never seen it work that way. I would like to put the WAL file on the SAS disks to improve performance, and create one big RAID 1+0 disk for the data directory. But maybe I'm completely wrong. Can you please advise how to create logical partitions? I would listen to yourself before you listen to the expert. You sound right to me :) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] select on 22 GB table causes An I/O error occured while sending to the backend. exception
On Wed, Aug 27, 2008 at 03:22:09PM -0700, [EMAIL PROTECTED] wrote: I disagree with you. I think goof Postgres operation is so highly dependant on caching as much data as possible that disabling overcommit (and throwing away a lot of memory that could be used for cache) is a solution that's as bad or worse than the problem it's trying to solve. Ok, but the danger is that the OOM killer kills your postmaster. To me, this is a cure way worse than the disease it's trying to treat. YMMD c. c. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] select on 22 GB table causes An I/O error occured while sending to the backend. exception
On Wed, Aug 27, 2008 at 02:45:47PM -0700, [EMAIL PROTECTED] wrote: with memory overcommit enabled (the default), the kernel recognises that most programs that fork don't write to all the memory they have allocated, It doesn't recognise it; it hopes it. It happens to hope correctly in many cases, because you're quite right that many programs don't actually need all the memory they allocate. But there's nothing about the allocation that hints, By the way, I'm not really planning to use this. Also. . . seperate copies for the seperate processes (and if at this time it runs of of memory it invokes the OOM killer to free some space), . . .it kills processes that are using a lot of memory. Those are not necessarily the processes that are allocating memory they don't need. The upshot of this is that postgres tends to be a big target for the OOM killer, with seriously bad effects to your database. So for good Postgres operation, you want to run on a machine with the OOM killer disabled. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Autovacuum does not stay turned off
On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote: Does anyone know what will cause this bahavior for autovacuum? You're probably approaching the wraparound limit in some database. If you think you can't afford the overhead when users are accessing the system, when are you vacuuming? A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] The state of PG replication in 2008/Q2?
On Thu, Aug 21, 2008 at 10:53:05PM +0200, Mathias Stjernström wrote: For Master-Slave replication i think that Slony http://www.slony.info/ is most up to date. But it does not support DDL changes. This isn't quite true. It supports DDL; it just doesn't support it in the normal way, and is broken by applications doing DDL as part of the regular operation. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Mailing list hacked by spammer?
On Fri, Jul 18, 2008 at 10:40:33AM -0700, Craig James wrote: Yes, hack is the correct term. The bad guys have hacked into the major email systems, including gmail, which was the origin of this spam: http://www.theregister.co.uk/2008/02/25/gmail_captcha_crack/ The simple fact is that, as long as we don't reject completely all mail from any unsubscribed user, some spam will occasionally get through. It's humans who have to do the moderation, and sometimes we hit the wrong button. Sorry. (Moreover, the trick of foiling captchas and using compromised machines all over the Internet to send spam is hardly hacking the list.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] ??: Postgresql update op is very very slow
On Thu, Jun 26, 2008 at 02:40:59PM +0200, Holger Hoffstaette wrote: large databases treat mass updates? AFAIK both DB2 and Oracle use MVCC (maybe a different kind?) as well, but I cannot believe that large updates still pose such big problems. DB2 does not use MVCC. This is why lock escalation is such a big problem for them. Oracle uses a kind of MVCC based on rollback segments: your work goes into the rollback segment, so that it can be undone, and the update happens in place. This causes a different kind of pain: you can run out of rollback segments (part way through a long-running transaction, even) and then have to undo everything in order to do any work at all. Every system involves trade-offs, and different systems make different ones. The bulk update problem is PostgreSQL's weak spot, and for that cost one gets huge other benefits. Are there no options (algorithms) for adaptively choosing different update strategies that do not incur the full MVCC overhead? How would you pick? But one thing you could do is create the table with a non-standard fill factor, which might allow HOT to work its magic. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] ??: Postgresql update op is very very slow
On Thu, Jun 26, 2008 at 09:16:25PM +0800, Craig Ringer wrote: I think Pg already does in place updates, or close, if the tuples being replaced aren't referenced by any in-flight transaction. I noticed a while ago that if I'm doing bulk load/update work, if there aren't any other transactions no MVCC bloat seems to occur and updates are faster. Are you on 8.3? That may be HOT working for you. MVCC doesn't get turned off if there are no other transactions (it can't: what if another transaction starts part way through yours?). A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Hardware vs Software RAID
On Wed, Jun 25, 2008 at 01:35:49PM -0400, Merlin Moncure wrote: experiences, i'm starting to be more partial to linux distributions with faster moving kernels, mainly because i trust the kernel drivers more than the vendor provided drivers. While I have some experience that agrees with this, I'll point out that I've had the opposite experience, too: upgrading the kernel made a perfectly stable system both unstable and prone to data loss. I think this is a blade that cuts both ways, and the key thing to do is to ensure you have good testing infrastructure in place to check that things will work before you deploy to production. (The other way to say that, of course, is Linux is only free if your time is worth nothing. Substitute your favourite free software for Linux, of course. ;-) ) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Hardware vs Software RAID
On Wed, Jun 25, 2008 at 01:07:25PM -0500, Kevin Grittner wrote: It doesn't have to be free software to cut that way. I've actually found the free software to waste less of my time. No question. But one of the unfortunate facts of the no-charge-for-licenses world is that many people expect the systems to be _really free_. It appears that some people think, because they've already paid $smallfortune for a license, it's therefore ok to pay another amount in operation costs and experts to run the system. Free systems, for some reason, are expected also magically to run themselves. This tendency is getting better, but hasn't gone away. It's partly because the budget for the administrators is often buried in the overall large system budget, so nobody balks when there's a big figure attached there. When you present a budget for free software that includes the cost of a few administrators, the accounting people want to know why the free software costs so much. If you depend on your systems, though, you should never deploy any change, no matter how innocuous it seems, without testing. I agree completely. -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Which hardware ?
On Tue, Jun 17, 2008 at 03:38:59PM +0200, Lionel wrote:
Hi,
I need to install a 8.3 database and was wondering which hardware would be
sufficient to have good performances (less than 30s for� slowest select).
Statements will mainly do sums on the main table, grouped by whatever column
of the database (3-5 joined tables, or join on join), with some criterions
that may vary, lots of joined varchar in ('a','b',...,'z').
It's almost impossible to predict what users will do via the webapplication
that queries this database: almost all select, join, group by, where...
possibilities are available.
I'm not sure that I have any specific recommendation to make in the
face of such sweeping requirements. But I'd say you need to make I/O
cheap, which means piles of memory and extremely fast disk
subsystems.
Also, there's another important question (which never gets asked in
these discussions), which is, How much is the performance worth to
you? If the last 10% of users get something longer than 30s, but
less than 40s, and they will pay no more to get the extra 10s response
time, then it's worth nothing to you, and you shouldn't fix it.
Up to 4 simultaneous users.
You won't need lots of processer, then.
I'm planning to host it on a quad xeon 2.66Ghz with 8Go of DDR2, and a dual
(RAID1) SATA2 750Go HD.
Perharps with another HD for indexes.
How big's the database? If you can have enough memory to hold the
whole thing, including all indexes, in memory, that's what you want.
Apart from that, dual SATA2 is probably underpowered. But. . .
Which OS would you use ? (knowing that there will be a JDK 1.6 installed
too)
. . .I think this is the real mistake. Get a separate database box.
It's approximately impossible to tune a box correctly for both your
application and your database, in my experience.
A
--
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/
--
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] Which hardware ?
On Tue, Jun 17, 2008 at 04:49:17PM +0200, Lionel wrote: My tomcat webapp is well coded and consumes nearly nothing. If I were ever inclined to say, Nonsense, about code I've never seen, this is probably the occasion on which I'd do it. A running JVM is necessarily going to use some memory, and that is memory use that you won't be able to factor out properly when developing models of your database system performance. I could eventually run it on a different server, much less powerfull, but it's not on the same network, I guess this would be an issue. The power of the system is hard to know about in the context (with only 8Go of memory, I don't consider this a powerful box at all, note). But why wouldn't it be on the same network? You're using the network stack anyway, note: JVMs can't go over domain sockets. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Replication Syatem
On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri Kanekar wrote: Peter, We are doing vacuum full every alternate day. We also do vacuum analyze very often. VACUUM FULL is making your problem worse, not better. Don't do that. We are currently using 8.1.3 version. You need immediately to upgrade to the latest 8.1 stability and security release, which is 8.1.11. This is a drop-in replacement. It's an urgent fix for your case. Auto vacuum is already on. But the table1 is so busy that auto vacuum don't get sufficient chance to vacuum it :(. You probably need to tune autovacuum not to do that table, and just vacuum that table in a constant loop or something. VACUUM should _never_ take the site down. If it does, you're doing it wrong. Have already tried all the option listed by you, thats y we reached to the decision of having a replication sytsem. So any suggestion on that :). I think you will find that no replication system will solve your underlying problems. That said, I happen to work for a company that will sell you a replication system to work with 8.1 if you really want it. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Replication Syatem
On Mon, Apr 28, 2008 at 07:48:48PM +0530, Gauri Kanekar wrote: Slony don't do automatic failover. And we would appreciate a system with automatic failover :( No responsible asynchronous system will give you automatic failover. You can lose data that way. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Oddly slow queries
On Wed, Apr 16, 2008 at 11:48:21PM +0200, Thomas Spreng wrote: What I meant is if there are no INSERT's or UPDATE's going on it shouldn't affect SELECT queries, or am I wrong? CHECKPOINTs also happen on a time basis. They should be short in that case, but they still have to happen. -- 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] count * performance issue
On Tue, Mar 11, 2008 at 02:19:09PM +, Matthew wrote: of rows with IS NULL, then someone changes a row, then you find the count of rows with IS NOT NULL. Add the two together, and there may be rows that were counted twice, or not at all. Only if you count in READ COMMITTED. A -- 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] Disable WAL completely
On Tue, Feb 19, 2008 at 02:48:55PM +, Matthew wrote: If there's not much write traffic, the WAL won't be used much anyway. You still have checkpoints. If you really don't care much about the integrity, then the best option is probably to put the WAL on ramfs. Um, that will cause the WAL to go away in the event of device crash. Surely that's a bad thing? A ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] analyze
On Tue, Jan 29, 2008 at 04:28:45PM +0200, Adrian Moisey wrote: Seriously though, how do I try measure this? Is autovacuum not going to work for your case? A ---(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: [PERFORM] Best way to index IP data?
On Fri, Jan 11, 2008 at 10:19:51AM -0500, Tom Lane wrote: Given that the world is going to IPv6 in a few years whether you like it or not, that seems pretty darn short-sighted to me. Indeed. Even ARIN has finally started to tell people that IPv4 is running out. There are currently significant deployments of IPv6 in the Asia-Pacific region. And it appears that Comcast is planning to move to IPv6 for its own network deployment, which may mean that many U.S. homes will have native v6 in the near future (the upshot of their plans aren't actually clear to me yet, but if you're interested in some of what they're telling people they're doing, look for Alain Durand's presentation to the v6ops working group at the last IETF meeting). What would make sense IMHO is to adapt the improved indexing support in ip4r to work on the native inet/cidr types. This seems like a good idea to me. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Best way to index IP data?
On Fri, Jan 11, 2008 at 05:02:36PM -0500, Michael Stone wrote: networks), but there's a conspicuous lack of a type for (hosts). I suppose if you really are sure that you want to store hosts and not networks Well, part of the trouble is that in the CIDR world, an IP without a netmask can be dangerously ambiguous. I can see why the design is as it is for that reason. (But I understand the problem.) A ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Best way to index IP data?
On Fri, Jan 11, 2008 at 02:38:27PM -0800, Steve Atkins wrote: I don't think there's ambiguity about what an dotted-quad without a netmask means, and hasn't been for a long time. Am I missing something? Well, maybe. The problem is actually that, without a netmask under CIDR, the address alone isn't really enough. You have to have a netmask to get the packets to the destination. As it happens, we have some nice conventions, defined in the RFCs, for how to interpret hosts with no netmask; note though that some of those are only for humans. Or, to put it another way, without context, a dotted-quad is insufficient on its own. What you're really arguing is that the context ought to be storable somewhere else (maybe in a human's brain). I'm not suggesting that's wrong, but I can see the correctness argument that someone might have made to get to the datatype as it exists. I think calling it needless bloat is just holding it to the wrong criteria. If you look at the binary wire data, that netmask is always represented in some sense. It can sometimes be more compact than the general-purpose data type, though, no question. This is why somewhere in this thread someone talked about optimisation: there certainly are ways to make these things more compact. A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] function body actors (was: [PERFORM] viewing source code)
On Fri, Dec 21, 2007 at 12:09:28AM -0500, Merlin Moncure wrote: Maybe a key management solution isn't required. If, instead of strictly wrapping a language with an encryption layer, we provide hooks (actors) that have the ability to operate on the function body when it arrives and leaves pg_proc, we may sidestep the key problem (leaving it to the user) and open up the doors to new functionality at the same time. I like this idea much better, because the same basic mechanism can be used for more than one thing, and it doesn't build in a system that is fundamentally weak. Of course, you _can_ build a weak system this way, but there's an important difference between building a fundamentally weak system and making weak systems possible. A ---(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: function body actors (was: [PERFORM] viewing source code)
On Fri, Dec 21, 2007 at 12:40:05AM -0500, Tom Lane wrote: whether there is a useful policy for it to implement. Andrew Sullivan argued upthread that we cannot get anywhere with both keys and encrypted function bodies stored in the same database (I hope that's an adequate summary of his point). It is. I'm not a security expert, but I've been spending some time listening to some of them lately. The fundamental problem with a system that stores the keys online in the same repository is not just its potential for compromise, but its brittle failure mode: once the key is recovered, you're hosed. And there's no outside check of key validity, which means attackers have a nicely-contained target to hit. I'm not convinced that he's right, but that has to be the first issue we think about. The whole thing is a dead end if there's no way to do meaningful encryption --- punting an insoluble problem to the user doesn't make it better. Well, one thing you could do with the proposal is build a PKCS#11 actor, that could talk to an HSM. Not everyone needs HSMs, of course, but they do make online key storage much less risky (because correctly designed ones make key recovery practically impossible). So the mechanism can be made effectively secure even for very strong cryptographic uses. Weaker cases might use a two-level key approach, with a data-signing key online all the time to do the basic encryption and validation, but a key-signing key that is always offline or otherwise unavailable from within the system. The key signing key only authenticates (and doesn't encrypt) the data signing key. You could use a different actor for this, to provide an interface to one-way functions or something. This gives you a way to revoke a data-signing key. You couldn't protect already compromised data this way, but at least you could prevent new disclosures. Yes, I'm being hand-wavy now, but I can at least see how these different approaches are possible under the suggestion, so it seems like a possibly fruitful avenue to explore. The more I think about it, actually, the more I like it. A ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] viewing source code
On Thu, Dec 20, 2007 at 03:35:42PM -0500, Merlin Moncure wrote: Key management is an issue but easily solved. Uber simple solution is to create a designated table holding the key(s) and use classic permissions to guard it. Any security expert worth the title would point and laugh at that suggestion. If the idea is that the contents have to be encrypted to protect them, then it is just not acceptable to have the encryption keys online. That's the sort of security that inevitably causes programs to get a reputation for ill-thought-out protections. A ---(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: [PERFORM] viewing source code
On Thu, Dec 20, 2007 at 01:45:08PM -0600, Roberts, Jon wrote: Businesses use databases like crazy. Non-technical people write their own code to analyze data. The stuff they write many times is as valuable as the data itself and should be protected like the data. They don't need or want many times to go through a middle tier to analyze data or through the hassle to obfuscate the code. I'm not opposed to this goal, I should note. I just think that any proposal that is going to go anywhere may need to be better than the one you seem to have made. I think column-level permissions is probably something that is needed. a ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
On Thu, Dec 20, 2007 at 03:24:34PM -0600, Roberts, Jon wrote: Actually, PostgreSQL already has column level security for pg_stat_activity. Not exactly. pg_stat_activity is a view. But I think someone suggested upthread experimenting with making pg_proc into a view, and making the real table pg_proc_real or something. This might work. A ---(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: [PERFORM] viewing source code
On Thu, Dec 20, 2007 at 05:04:33PM -0500, Merlin Moncure wrote: right, right, thanks for the lecture. I am aware of various issues with key management. Sorry to come off that way. It wasn't my intention to lecture, but rather to try to stop dead a cure that, in my opinion, is rather worse than the disease. I said 'simple' not 'good'. I think this is where we disagree. It's simple only because it's no security at all. It's not that it's not good for some purposes. I'm arguing that it's the sort of approach that shouldn't be used ever, period. We have learned, over and over again, that simple answers that might have been good enough for a very narrow purpose inevitably get used for a slightly wider case than that for which they're appropriate. Anything that involves storing the keys in the same repository as the encrypted data is just begging to be misused that way. I am not making a proposal here and you don't have to denigrate my broad suggestion on a technical detail which is quite distracting from the real issue at hand, btw. This isn't a technical detail that I'm talking about: it's a very serious mistake in the entire approach to which you alluded, and goes to the heart of why I think any talk of somehow encrypting or otherwise obfuscating the contents of pg_proc are a bad idea. Column controls based on user roles are another matter, because they'd be part of the access control system in the DBMS. Best, A ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 7.4 Checkpoint Question
On Thu, Nov 29, 2007 at 10:10:54AM -0500, Brad Nicholson wrote: This is a _really _low volume system, less than 500 writes/hour. Normal operation sees checkpoint related spikes of around 200-300 milliseconds. We always checkpoint at the checkpoint timeout (every 5 minutes). During this one checkpoint, I'm seeing transactions running 2-3 seconds. During this time, writes are 5/minute. What gives? pg_dump? Remember that it has special locks approximately equivalent (actually eq? I forget) with SERIALIZABLE mode, which makes things rather different. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Query only slow on first run
On Tue, Nov 27, 2007 at 05:33:36PM +0100, cluster wrote: I have a query that takes about 7000 ms in average to complete the first time it runs. Subsequent runs complete in only 50 ms. That is more than a factor 100 faster! How can I make the query perform good in the first run too? Probably by buying much faster disk hardware. You'll note that the query plans you posted are the same, except for the actual time it took to get the results back. That tells me you have slow storage. On subsequent runs, the data is cached, so it's fast. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Curious about dead rows.
On Wed, Nov 14, 2007 at 07:12:45AM -0500, Jean-David Beyer wrote: I know there have been rollbacks but I do a REINDEX, CLUSTER, and VACUUM ANALYZE before starting the inserts in question. Do I need to do a VACUUM FULL ANALYZE instead? I had another idea. As Alvaro says, CLUSTER will do everything you need. But are you sure there are _no other_ transactions open when you do that? This could cause problems, and CLUSTER's behaviour with other open transactions is not, um, friendly prior to the current beta. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(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: [PERFORM] Curious about dead rows.
On Wed, Nov 14, 2007 at 11:53:17AM -0500, Jean-David Beyer wrote: that I run only one at a time, or leaving psql running. But as I understand it, psql does not bother with transactions, and besides, I normally just do No, every statement in psql is a transaction. Even SELECT. Every statement under PostgreSQL runs in a transaction. When you type SELECT (1), the server implicitly adds the BEGIN; and END; around it. into my application so that the statistics counters will not count previous UPDATEs and ROLLBACKs when the main program that I intend and believe to do only INSERTs is running. It will make those statistics easier to read than having to subtract previous values to get the changes. Yes. Well, it will not work because I must be superuser (i.e., postgres) to execute that, and if I am, I cannot read the input files. I will do it You could grant superuser status to your user (or just connect as postgres user) for the time being, while debugging this. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Curious about dead rows.
On Wed, Nov 14, 2007 at 11:58:23AM -0500, Andrew Sullivan wrote: No, every statement in psql is a transaction. Even SELECT. Every statement Err, to be clearer, Every statement in psql is _somehow_ part of a transaction; if you don't start one explicitly, the statement runs on its own as a transaction. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Curious about dead rows.
Please don't drop the list, as someone else may see something. On Tue, Nov 13, 2007 at 10:06:13AM -0500, Jean-David Beyer wrote: OK. I turned logging from none to mod and got a gawdawful lot of stuff. Yes. Then I ran it and got all the inserts. Using grep -i delete file grep -i update file grep -i rollback file How about ERROR? 2007-11-13 08:11:20 EST DEBUG: vl_ranks: scanned 540 of 540 pages, containing 67945 live rows and 554 dead rows; 3000 rows in sample, 67945 estimated total rows If there are dead rows, something is producing them. Either INSERT is firing a trigger that is doing something there (you won't see an UPDATE in that case), or else something else is causing INSERTs to fail. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Curious about dead rows.
On Tue, Nov 13, 2007 at 02:50:59PM -0500, Jean-David Beyer wrote: How about ERROR? $ grep -i error Tue.log $ Well, without actually logging into the machine and looking at the application, I confess I am stumped. Oh, wait. You do have the log level high enough that you should see errors in the log, right? That's not controlled by the statement parameter. I have no triggers in that database. I do have two sequences. Sequences should not produce any dead rows on the table, unless they're used as keys and you're attempting inserts that conflict with used sequence values. That should cause errors that you'd get in the log, presuming that you have the log level set correctly. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(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: [PERFORM] Curious about dead rows.
I'm not a private support organisation; please send your replies to the list, not me. On Tue, Nov 13, 2007 at 04:57:23PM -0500, Jean-David Beyer wrote: What is it controlled by? The following are the non-default values in postgresql.conf: redirect_stderr = on log_directory = '/srv/dbms/dataB/pgsql/pg_log' log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1440 log_rotation_size = 0 log_min_messages = debug2 This will certainly include error messages, then. Or it ought to. You do see errors in the log when you create one, right? (Try causing an error in psql to make sure.) log_line_prefix = '%t ' log_statement = 'none' (this was 'mod', but it uses too much disk to leave it turned on -- only 4 GBytes in that partition) They are; they are the primary keys of two tables. But those are all done before the last VACUUM FULL ANALYZE runs, so the dead rows should have been eliminated. And the output of the sequence is the only way of generating a primary key, so it should be impossible anyhow. I thought you were doing INSERTs? It's not true that the output of the sequence is the only way -- if you insert directly, it will happily insert into that column. But it should cause an error to show in the log, which is what's puzzling me. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(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: [PERFORM] server performance issues - suggestions for tuning
On Tue, Aug 28, 2007 at 08:12:06AM -0500, Kevin Grittner wrote: Is there any way to queue up these queries and limit how many are running at a time? Sure: limit the number of connections to the database, and put a pool in front. It can indeed help. If you have a lot of bloat due to large numbers of failed vacuums, however, I suspect your problem is I/O. Vacuum churns through the disk very aggressively, and if you're close to your I/O limit, it can push you over the top. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] best use of an EMC SAN
On Wed, Jul 11, 2007 at 09:03:27AM -0400, Dave Cramer wrote: Problem with dedicating the spindles to each array is that we end up wasting space. Are the SAN's smart enough to do a better job if I create one large metalun and cut it up ? In my experience, this largely depends on your SAN and its hard- and firm-ware, as well as its ability to interact with the OS. I think the best answer is sometimes yes. A -- Andrew Sullivan | [EMAIL PROTECTED] However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] TIMING A QUERY ???
On Wed, Jul 11, 2007 at 08:21:40AM -0700, smiley2211 wrote: How can I get the time it takes a query to execute - explain analyze is taking over 5 hours to complete You can't get it any faster than what explain analyse does: it runs the query. How else would you get the answer? ...can I use \timing??? I don't get any time when using the \timing option... How so? It returns Time: N ms at the end of output for me. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Two questions.. shared_buffers and long reader issue
On Wed, Jul 11, 2007 at 05:35:33PM +0200, Patric de Waha wrote: Mainly updates on 1 tuple. Are you vacuuming that table enough? And more or less complex SELECT statements. I noticed that the overall performance of postgres is decreasing when one or more long readers are present. Where a long reader here is already a Select count(*) from table. SELECT count(*) is expensive in Postgres. Do you really need it? Unqualified count() in PostgreSQL is just a bad thing to do, so if you can work around it (by doing limited subselects, for instance, where you never scan more than 50 rows, or by keeping counts using triggers, or various other tricks), it's a good idea. Why do long readers influence the rest of the transactions in such a heavy way? It could be because of all those updated tuples not getting vacuumed (which results in a bad plan). Or it could be that your connection pool is exhausted: note that when someone hits reload, that doesn't mean your old query goes away. It is still crunching through whatever work it was doing. Second question. What is the right choice for the shared_buffers size? On a dedicated postgres server with 4 Giga RAM. Is there any rule of thumb? Actually I set it to +-256M. There has been Much Discussion of this lately on this list. I suggest you have a look through the recent archives on that topic. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] TIMING A QUERY ???
On Wed, Jul 11, 2007 at 12:10:55PM -0400, Tom Lane wrote: Well, on some platforms (ie consumer-grade PCs) explain analyze can be a lot slower than just running the query, Yes, I suppose I exaggerated when I said can't get any faster, but given that the OP was talking on the order of hours for the EXPLAIN ANALYSE to return, I assumed that the problem is one of impatience and not clock cycles. After all, the gettimeofday() additional overhead is still not going to come in on the order of minutes without a _bursting_ huge query plan. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] best use of an EMC SAN
On Wed, Jul 11, 2007 at 01:39:39PM -0400, Chris Browne wrote: load causes. A fallout of this is that those disks are likely to be worked harder than the disk used for storing plain old data, with the result that if you devote disk to WAL, you'll likely burn thru replacement drives faster there than you do for the POD disk. This is true, and in operation can really burn you when you start to blow out disks. In particular, remember to factor the cost of RAID re-build into your RAID plans. Because you're going to be doing it, and if your WAL is near to its I/O limits, the only way you're going to get your redundancy back is to go noticably slower :-( will lose a very little bit in comparison. Andrew Sullivan had a somewhat similar finding a few years ago on some old Solaris hardware that unfortunately isn't at all relevant today. He basically found that moving WAL off to separate disk didn't affect performance materially. Right, but it's not only the hardware that isn't relevant there. It was also using either 7.1 or 7.2, which means that the I/O pattern was completely different. More recently, ISTR, we did analysis for at least one workload that tod us to use separate LUNs for WAL, with separate I/O paths. This was with at least one kind of array supported by Awful Inda eXtreme. Other tests, IIRC, came out differently -- the experience with one largish EMC array was I think a dead heat between various strategies (so the additional flexibility of doing everything on the array was worth any cost we were able to measure). But the last time I had to be responsible for that sort of test was again a couple years ago. On the whole, though, my feeling is that you can't make general recommendations on this topic: the advances in storage are happening too fast to make generalisations, particularly in the top classes of hardware. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Volunteer to build a configuration tool
On Thu, Jun 21, 2007 at 03:14:48AM -0400, Greg Smith wrote: The Oracle Way presumes that you've got such a massive development staff that you can solve these problems better yourself than the community at large, and then support that solution on every platform. Not that Greg is suggesting otherwise, but to be fair to Oracle (and other large database vendors), the raw partitions approach was also a completely sensible design decision back when they made it. In the late 70s and early 80s, the capabilities of various filesystems were wildly uneven (read the _UNIX Hater's Handbook_ on filesystems, for instance, if you want an especially jaundiced view). Moreover, since it wasn't clear that UNIX and UNIX-like things were going to become the dominant standard -- VMS was an obvious contender for a long time, and for good reason -- it made sense to have a low-level structure that you could rely on. Once they had all that code and had made all those assumptions while relying on it, it made no sense to replace it all. It's now mostly mature and robust, and it is probably a better decision to focus on incremental improvements to it than to rip it all out and replace it with something likely to be buggy and surprising. The PostgreSQL developers' practice of sighing gently every time someone comes along insisting that threads are keen or that shared memory sucks relies on the same, perfectly sensible premise: why throw away a working low-level part of your design to get an undemonstrated benefit and probably a whole lot of new bugs? A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(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: [PERFORM] Performance query about large tables, lots of concurrent access
On Thu, Jun 21, 2007 at 12:29:49PM -0400, Karl Wright wrote: I checked the disk picture - this is a RAID disk array with 6 drives, with a bit more than 1Tbyte total storage. 15,000 RPM. It would be hard to get more/faster disk than that. What kind of RAID? It's _easy_ to get faster disk that 6 drives in RAID5, even if they're 15,000 RPM. The rotation speed is the least of your problems in many RAID implementations. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] vacuum a lot of data when insert only
On Thu, Jun 21, 2007 at 07:53:54PM +0300, Sabin Coanda wrote: Reading different references, I understand there is no need to vacuum a table where just insert actions perform. That's false. First, you must vacuum at least once every 2 billion transactions. Second, if a table is INSERTed to, but then the INSERTing transaction rolls back, it leaves a dead tuple in its wake. My guess, from your posted example, is that you have the latter case happening, because you have removable rows (that's assuming you aren't mistaken that there's never a delete or update to the table). A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(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: [PERFORM] Replication
On Mon, Jun 18, 2007 at 08:54:46PM +0200, Markus Schiltknecht wrote: Postgres-R has been the name of the research project by Bettina Kemme et al. Slony-II was the name Neil and Gavin gave their attempt to continue that project. This isn't quite true. Slony-II was originally conceived by Jan as an attempt to implement some of the Postgres-R ideas. For our uses, however, Postgres-R had built into it a rather knotty design problem: under high-contention workloads, it will automatically increase the number of ROLLBACKs users experience. Jan had some ideas on how to solve this by moving around the GC events and doing slightly different things with them. To that end, Afilias sponsored a small workshop in Toronto during one of the coldest weeks the city has ever seen. This should have been a clue, perhaps. ;-) Anyway, the upshot of this was that two or three different approaches were attempted in prototypes. AFAIK, Neil and Gavin got the farthest, but just about everyone who was involved in the original workshop all independently concluded that the approach we were attempting to get to work was doomed -- it might go, but the overhead was great enough that it wouldn't be any benefit. Part of the problem, as near as I could tell, was that we had no group communication protocol that would really work. Spread needed a _lot_ of work (where lot of work may mean rewrite), and I just didn't have the humans to put on that problem. Another part of the problem was that, for high-contention workloads like the ones we happened to be working on, an optimistic approach like Postgres-R is probably always going to be a loser. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance query about large tables, lots of concurrent access
On Wed, Jun 20, 2007 at 02:01:34PM -0400, Karl Wright wrote: (FWIW, ANALYZE operations are kicked off after every 30,000 inserts, updates, or deletes, by the application itself). I don't think you should do it that way. I suspect that automatic VACUUM ANALYSE way more often on each table -- like maybe in a loop -- would be better for your case. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Performance query about large tables, lots of concurrent access
On Wed, Jun 20, 2007 at 05:29:41PM -0400, Karl Wright wrote: A nice try, but I had just completed a VACUUM on this database three hours prior to starting the VACUUM that I gave up on after 27 hours. You keep putting it that way, but your problem is essentially that you have several tables that _all_ need to be vacuumed. VACUUM need not actually be a database-wide operation. earlier finished in six hours - but to accomplish that I had to shut down EVERYTHING else that machine was doing.) This suggests to me that you simply don't have enough machine for the job. You probably need more I/O, and actually more CPU wouldn't hurt, because then you could run three VACUUMs on three separate tables (on three separate disks, of course) and not have to switch them off and on the CPU. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
All, On Mon, Jun 18, 2007 at 07:50:22PM +0200, Andreas Kostyrka wrote: [something] It would appear that this was the flame-fest that was predicted. Particularly as this has been copied to five lists. If you all want to have an argument about what Oracle should or should not do, could you at least limit it to one list? A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On Mon, Jun 18, 2007 at 02:16:56PM -0400, Jonah H. Harris wrote: pgsql-advocacy... your thoughts? I've picked -advocacy. I think the Oracle discussion is over, David T. just needs URL references IMHO. I don't think we can speak about Oracle; if we were licenced, we'd be violating it, and since we're not, we can't possibly know about it, right ;-) But there are some materials about why to use Postgres on the website: http://www.postgresql.org/about/advantages A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On Mon, Jun 18, 2007 at 02:38:32PM -0400, Andrew Sullivan wrote: I've picked -advocacy. Actually, I _had_ picked advocacy, but had an itchy trigger finger. Apologies, all. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] How much ram is too much
On Mon, Jun 11, 2007 at 11:09:42AM -0400, Dave Cramer wrote: and set them to anything remotely close to 128GB. Well, we'd give 25% of it to postgres, and the rest to the OS. Are you quite sure that PostgreSQL's management of the buffers is efficient with such a large one? In the past, that wasn't the case for relatively small buffers; with the replacement of single-pass LRU, that has certainly changed, but I'd be surprised if anyone tested a buffer as large as 32G. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] [ADMIN] reclaiming disk space after major updates
On Fri, Jun 08, 2007 at 08:29:24AM -0600, Dan Harris wrote: One more point in my original post.. For my own education, why does VACUUM FULL prevent reads to a table when running (I'm sure there's a good reason)? I can certainly understand blocking writes, but if I could still read from it, I'd have no problems at all! It has to take an exclusive lock, because it actually moves the bits around on disk. Since your SELECT query could be asking for data that is actually in-flight, you lose. This is conceptually similar to the way defrag works on old FAT-type filesystems: if you used one, you'll remember that when you were defragging your disk, if you did anything else on that disk the defrag would keep restarting. This was because the OS was trying to move bits around, and when you did stuff, you screwed up its optimization. The database works differently, by taking an exclusive lock, but the basic conceptual problem is the same. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] VERY slow queries at random
On Thu, Jun 07, 2007 at 04:22:47PM +0200, Gunther Mayer wrote: There are a whole bunch of update queries that fire all the time but afaik none of them ever lock the entire table. To the best of my knowledge UPDATE ... WHERE ... only locks those rows that it actually operates on, in my case this is always a single row. Well that shouldn't be biting you, then (you're not in SERIALIZABLE mode, right?). The other obvious bit would be checkpoint storms. What's your bgwriter config like? Question is, how do I find out about locks at the time when I only get told about the slow query *after* it has completed and postgres has told me so by logging a slow query entry in my logs? You can't :( A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(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: [PERFORM] Getting Slow
On Thu, Jun 07, 2007 at 01:48:43PM -0400, Joe Lester wrote: of a table). Running the same query 4 times in a row would yield dramatically different results... 1.001 seconds, 5 seconds, 22 seconds, 0.01 seconds, to complete. - When queries are especially slow, the server shows a big spike in read/write activity. My bet is that you're maxing your disk subsystem somehow. The problem with being I/O bound is that it doesn't matter how great you do on average: if you have too much I/O traffic, it looks like you're stopped. Softraid can be expensive -- first thing I'd look at is to see whether you are in fact hitting 100% of your I/O capacity and, if so, what your options are for getting more room there. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Thousands of tables versus on table?
On Wed, Jun 06, 2007 at 12:06:09AM +0200, Steinar H. Gunderson wrote: Wasn't there also talk about adding the ability to mark individual partitions as read-only, thus bypassing MVCC and allowing queries to be satisfied using indexes only? I have a (different) problem that read-only data segments (maybe partitions, maybe something else) would help, so I know for sure that someone is working on a problem like this, but I don't think it's the sort of thing that's going to come any time soon. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Thousands of tables versus on table?
On Tue, Jun 05, 2007 at 03:31:55PM -0700, [EMAIL PROTECTED] wrote: various people (not database experts) are pushing to install Oracle cluster so that they can move all of these to one table with a customerID column. Well, you will always have to deal with the sort of people who will base their technical prescriptions on the shiny ads they read in SuperGlobalNetworkedExecutiveGoFast, or whatever rag they're reading these days. I usually encourage such people actually to perform the analysis of the license, salary, contingency, and migrations costs (and do a similar analysis myself, actually, so when they have overlooked the 30 things that individually cost $1million a piece, I can point them out). More than one jaw has had to be picked up off the floor when presented with the bill for RAC. Frequently, people discover that it is a good way to turn your tidy money-making enterprise into a giant money hole that produces a sucking sound on the other end of which is Oracle Corporation. All of that aside, I have pretty severe doubts that RAC would be a win for you. A big honkin' single database in Postgres ought to be able to do this too, if you throw enough hardware money at it. But it seems a waste to re-implement something that's already apparently working for you in favour of something more expensive that you don't seem to need. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
control of benchmarks (was: [PERFORM] Thousands of tables)
On Wed, Jun 06, 2007 at 02:01:59PM -0400, Jonah H. Harris wrote: They did this for the same reason as everyone else. They don't want non-experts tuning the database incorrectly, writing a benchmark paper about it, and making the software look bad. I agree that Oracle is a fine system, and I have my doubts about the likelihood Oracle will fall over under fairly heavy loads. But I think the above is giving Oracle Corp a little too much credit. Corporations exist to make money, and the reason they prohibit doing anything with their software and then publishing it without their approval is because they want to control all the public perception of their software, whether deserved or not. Every user of any large software system (Oracle or otherwise) has their favourite horror story about the grotty corners of that software; commercially-licensed people just aren't allowed to prove it in public. It's not only the clueless Oracle is protecting themselves against; it's also the smart, accurate, but expensive corner-case testers. I get to complain that PostgreSQL is mostly fast but has terrible outlier performance problems. I can think of another system that I've used that certainly had a similar issue, but I couldn't show you the data to prove it. Everyone who used it knew about it, though. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] VERY slow queries at random
On Wed, Jun 06, 2007 at 09:20:54PM +0200, Gunther Mayer wrote: What the heck could cause such erratic behaviour? I suspect some type of resource problem but what and how could I dig deeper? Is something (perhaps implicitly) locking the table? That will cause this. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Vacuum takes forever
On Tue, May 29, 2007 at 07:56:07PM +0200, Joost Kraaijeveld wrote: Thanks, I tried it and it worked. I did not know that changing this setting would result in such a performance drop ( I just followed an It's not a performance drop. It's an on-purpose delay of the functionality, introduced so that _other_ transactions don't get I/O starved. (Make vacuum fast isn't in most cases an interesting goal.) A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] ECC RAM really needed?
On Sat, May 26, 2007 at 10:52:14AM -0400, [EMAIL PROTECTED] wrote: Do you want to be the one person who does notice a problem? :-) Right, and notice that when you notice the problem _may not_ be when it happens. The problem with errors in memory (or on disk controllers, another place not to skimp in your hardware budget for database machines) is that the unnoticed failure could well write corrupted data out. It's some time later that you notice you have the problem, when you go to look at the data and discover you have garbage. If your data is worth storing, it's worth storing correctly, and so doing things to improve the chances of correct storage is a good idea. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 121+ million record table perf problems
On Fri, May 18, 2007 at 12:43:40PM -0500, [EMAIL PROTECTED] wrote: I've got a table with ~121 million records in it. Select count on it currently takes ~45 minutes, and an update to the table to set a value on one of the columns I finally killed after it ran 17 hours and had still not completed. Queries into the table are butt slow, and I don't think you've told us anything like enough to get started on solving your problem. But to start with, you know that in Postgres, an unrestricted count() on a table always results in reading the entire table, right? Standard questions: have you performed any vacuum or analyse? Your update statement is also a case where you have to touch every row. Note that, given that you seem to be setting the state field to the same value for everything, an index on there will do you not one jot of good until there's greater selectivity. How fast is the disk? Is it fast enough to read and touch every one of those rows on the table inside of 17 hours? Note also that your approach of updating all 121 million records in one statement is approximately the worst way to do this in Postgres, because it creates 121 million dead tuples on your table. (You've created some number of those by killing the query as well.) All of that said, 17 hours seems kinda long. As a test I am trying to do an update on state using the following queries: update res set state=5001; select count(resid) from res; What is this testing? The update query that started this all I had to kill after 17hours. Does that suggest that the update you're trying to make work well is _not_ update res set state = 5001? each) and is running on a single disk (guess I will likely have to at the minimum go to a RAID1). Workload will primarily be comprised of queries I bet that single disk is your problem. Iostat is your friend, I'd say. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(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: [PERFORM] Background vacuum
On Thu, May 10, 2007 at 05:10:56PM -0700, Ron Mayer wrote: One way is to write astored procedure that sets it's own priority. An example is here: http://weblog.bignerdranch.com/?p=11 Do you have evidence to show this will actually work consistently? The problem with doing this is that if your process is holding a lock that prevents some other process from doing something, then your lowered priority actually causes that _other_ process to go slower too. This is part of the reason people object to the suggestion that renicing a single back end will help anything. This paper studied both CPU and lock priorities on a variety of databases including PostgreSQL. http://www.cs.cmu.edu/~bianca/icde04.pdf By contrast, for PostgreSQL, lock scheduling is not as effective as CPU scheduling (see Figure 4(c)). It is likely that in _some_ cases, you can get this benefit, because you don't have contention issues. The explanation for the good lock performance by Postgres on the TPC-C tests they were using is PostgreSQL's MVCC: Postgres locks less. The problem comes when you have contention, and in that case, CPU scheduling will really hurt. This means that, to use CPU scheduling safely, you have to be really sure that you know what the other transactions are doing. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(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: [PERFORM] When to vacuum a table?
On Sun, Nov 26, 2006 at 09:24:29AM -0500, Rod Taylor wrote: attempt and fail a large number of insert transactions then you will still need to vacuum. And you still need to vacuum an insert-only table sometimes, because of the system-wide vacuum requirement. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] VACUUMs take twice as long across all nodes
On Sun, Oct 29, 2006 at 03:08:26PM +, Gavin Hamill wrote: This is interesting, but I don't understand.. We've done a full restore from one of these pg_dump backups before now and it worked just great. Sure I had to DROP SCHEMA _replication CASCADE to clear out all the slony-specific triggers etc., but the new-master ran fine, as did firing up new replication to the other nodes :) Was I just lucky? Yes. Slony alters data in the system catalog for a number of database objects on the replicas. It does this in order to prevent, for example, triggers from firing both on the origin and the replica. (That is the one that usually bites people hardest, but IIRC it's not the only such hack in there.) This was a bit of a dirty hack that was supposed to be cleaned up, but that hasn't been yet. In general, you can't rely on a pg_dump of a replica giving you a dump that, when restored, actually works. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(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: [PERFORM] VACUUMs take twice as long across all nodes
On Sun, Oct 29, 2006 at 05:24:33PM +0100, Andreas Kostyrka wrote: Actually, you need to get the schema from the master node, and can take the data from a slave. In mixing dumps like that, you must realize that there are two seperate parts in the schema dump: table definitions and constraints. Do get a restorable backup you need to put the table definitions stuff before your data, and the constraints after the data copy. This will work, yes, but you don't get a real point-in-time dump this way. (In any case, we're off the -performance charter now, so if anyone wants to pursue this, I urge you to take it to the Slony list.) A -- Andrew Sullivan | [EMAIL PROTECTED] Windows is a platform without soap, where rats run around in open sewers. --Daniel Eran ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] VACUUMs take twice as long across all nodes
On Thu, Oct 26, 2006 at 09:35:56PM +0100, Gavin Hamill wrote: I'm absolutely certain. The backups run from only one slave, given that it is a full copy of node 1. Our overnight traffic has not increased any, and the nightly backups show that the overall size of the DB has not increased more than usual growth. A couple things from your posts: 1. Don't do VACUUM FULL, please. It takes longer, and blocks other things while it's going on, which might mean you're having table bloat in various slony-related tables. 2. Are your slony logs showing increased time too? Are your targets getting further behind? 3. Your backups from the slave aren't done with pg_dump, right? But I suspect Slony has a role here, too. I'd look carefully at the slony tables -- especially the sl_log and pg_listen things, which both are implicated. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] Hints proposal
On Thu, Oct 12, 2006 at 02:21:55PM -0400, Merlin Moncure wrote: third way: to solve the problem of data (especially constants) not being available to the planner at the time the plan was generated. this happens most often with prepared statements and sql udfs. note that changes to the plan generation mechanism (i think proposed by peter e a few weeks back) might also solve this. You're right about this, but you also deliver the reason why we don't need hints for that: the plan generation mechanism is a better solution to that problem. It's this latter thing that I keep coming back to. As a user of PostgreSQL, the thing that I really like about it is its pragmatic emphasis on correctness. In my experience, it's a system that feels very UNIX-y: there's a willingness to accept 80/20 answers to a problem in the event you at least have a way to get the last 20, but the developers are opposed to anything that seems really kludgey. In the case you're talking about, it seems to me that addressing the problems where they come from is a better solution that trying to find some way to work around them. And most of the use-cases I hear for a statement-level hints system fall into this latter category. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Postgres locking up?
On Fri, Sep 29, 2006 at 03:24:14PM -0400, Brian Hurt wrote: I'm experiencing a problem with our postgres database. Queries that normally take seconds suddenly start taking hours, if they complete at all. The first thing I'd do is EXPLAIN and EXPLAIN ANALYSE on the queries in question. The next thing I'd look for is OS-level performance problems. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Update on high concurrency OLTP application and Postgres 8 tuning
On Wed, Sep 20, 2006 at 11:09:23AM +0200, Cosimo Streppone wrote: I scheduled a cron job every hour or so that runs an analyze on the 4/5 most intensive relations and sleeps 30 seconds between every analyze. This has optimized db response times when many clients run together. I wanted to report this, maybe it can be helpful for others out there... :-) This suggests to me that your statistics need a lot of updating. You _might_ find that setting the statistics to a higher number on some columns of some of your tables will allow you to analyse less frequently. That's a good thing just because ANALYSE will impose an I/O load. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(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: [PERFORM] Some queries starting to hang
On Tue, Jun 06, 2006 at 11:06:09AM -0400, Tom Lane wrote: it was properly instrumented. That way, the OP might have been able to discover the root cause himself... I don't think that helps, as it just replaces one uncertainty by another: how far did the EXPLAIN really get towards completion of the plan? You still don't have any hard data. Well, you _might_ get something useful, if you're trying to work on a maladjusted production system, because you get to the part that trips the limit, and then you know, Well, I gotta fix it that far, anyway. Often, when you're in real trouble, you can't or don't wait for the full plan to come back from EXPLAIN ANALYSE, because a manager is helpfully standing over your shoulder asking whether you're there yet. Being able to say, Aha, we have the first symptom, might be helpful to users. Because the impatient simply won't wait for the full report to come back, and therefore they'll end up flying blind instead. (Note that the impatient is not always the person logged in and executing the commands.) A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Some queries starting to hang
On Mon, Jun 05, 2006 at 12:05:08PM -0700, Chris Beecroft wrote: Our problem is that about a week and a half ago we started to get some queries that would (seemingly) never return (e.g., normally run in a couple minutes, but after 2.5 hours, they were still running, the process pushing the processor up to 99.9% active). Are there any locks preventing the query from completing? I can't recall how you check in 7.3, but if nothing else, you can check with ps for something WAITING. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(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: [PERFORM] Optimizing a huge_table/tiny_table join
On Thu, May 25, 2006 at 12:31:04PM -0400, [EMAIL PROTECTED] wrote: Well, they're not my statistics; they're explain's. You mean there's Explain doesn't get them from nowhere. How often is the table being ANALYSEd? More bewildering still (and infuriating as hell--because it means that all of my work for yesterday has been wasted) is that I can no longer reproduce the best query plan I posted earlier, even though the tables have not changed at all. (Hence I can't post the explain analyze for I find that very hard to believe. Didn't change _at all_? Are you sure no VACUUMs or anything are happening automatically? Anyway, I take it that there is no way to bypass the optimizer and instruct PostgreSQL exactly how one wants the search performed? No, there isn't. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Postgres 7.4 and vacuum_cost_delay.
On Tue, May 02, 2006 at 05:47:15PM -0400, Chris Mckenzie wrote: I've come to the conclusion I need to simply start tracking all transactions and determining a cost/performance for the larger and frequently updated tables without the benefit and penalty of pg_statio. I'll bet it won't help you. If you can't get off 7.4 on a busy machine, you're going to get hosed by I/O sometimes no matter what. My suggestion is to write a bunch of rule-of-thumb rules for your cron jobs, and start planning your upgrade. Jan back-patched the vacuum stuff to 7.4 for us (Afilias), and we tried playing with it; but it didn't really make the difference we'd hoped. The reason for this is that 7.4 also doesn't have the bg_writer. So you're still faced with I/O storms, no matter what you do. If I were in your shoes, I wouldn't waste a lot of time on trying to emulate the new features in 7.4. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Investigating IO Saturation
On Tue, Jan 24, 2006 at 02:43:59PM -0500, Chris Browne wrote: I believe it's 7.4 where the cost-based vacuum parameters entered in, so that would, in principle, already be an option. [rummaging around...] Hmm There was a patch for 7.4, but it's only standard as of 8.0... And it doesn't work very well without changes to buffering. You need both pieces to get it to work. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] new to postgres (and db management) and performance already a problem :-(
On Tue, Jan 17, 2006 at 09:14:27AM +0100, Antoine wrote: think about it - we do very little removing, pretty much only inserts and selects. I will give it a vacuum full and see what happens. UPDATES? Remember that, in Postgres, UPDATE is effectively DELETE + INSERT (from the point of view of storage, not the point of view of the user). A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Autovacuum / full vacuum
On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote: hi, I'm curious as to why autovacuum is not designed to do full vacuum. I Because nothing that runs automatically should ever take an exclusive lock on the entire database, which is what VACUUM FULL does. activity. Increasing the FSM so that even during these bursts most space would be reused would mean to reduce the available memory for all other database tasks. I don't believe the hit is enough that you should even notice it. You'd have to post some pretty incredible use cases to show that the tiny loss of memory to FSM is worth (a) an exclusive lock and (b) the loss of efficiency you get from having some preallocated pages in tables. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Autovacuum / full vacuum
On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote: always wondered why there are no examples for common postgresql configurations. You mean like this one? (for 8.0): http://www.powerpostgresql.com/Downloads/annotated_conf_80.html All I know is that the default configuration seems to be too low for production use. Define production use. It may be too low for you. chance to see if my FSM settings are too low other than to run vacuum full verbose in psql, pipe the result to a text file and grep for some Not true. You don't need a FULL on there to figure this out. about the FSM: You say that increasing the FSM is fairly cheap - how should I know that? Do the math. The docs say this: --snip--- max_fsm_pages (integer) Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. Six bytes of shared memory are consumed for each page slot. This setting must be more than 16 * max_fsm_relations. The default is 2. This option can only be set at server start. max_fsm_relations (integer) Sets the maximum number of relations (tables and indexes) for which free space will be tracked in the shared free-space map. Roughly seventy bytes of shared memory are consumed for each slot. The default is 1000. This option can only be set at server start. ---snip--- So by default, you have 6 B * 20,000 = 120,000 bytes for the FSM pages. By default, you have 70 B * 1,000 = 70,000 bytes for the FSM relations. Now, there are two knobs. One of them tracks the number of relations. How many relations do you have? Count the number of indexes and tables you have, and give yourself some headroom in case you add some more, and poof, you have your number for the relations. Now all you need to do is figure out what your churn rate is on tables, and count up how many disk pages that's likely to be. Give yourself a little headroom, and the number of FSM pages is done, too. This churn rate is often tough to estimate, though, so you may have to fiddle with it from time to time. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Autovacuum / full vacuum
On Tue, Jan 17, 2006 at 09:09:02AM -0500, Matthew T. O'Connor wrote: vacuum. As long as that percentage is small enough, the effect on performance is negligible. Have you measured to see if things are truly Actually, as long as the percentage is small enough and the pages are really empty, the performance effect is positive. If you have VACUUM FULLed table, inserts have to extend the table before inserting, whereas in a table with some space reclaimed, the I/O effect of having to allocate another disk page is already done. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Autovacuum / full vacuum
On Tue, Jan 17, 2006 at 04:04:41PM +0100, Michael Riess wrote: I thought that vacuum full only locks the table which it currently operates on? I'm pretty sure that once a table has been vacuumed, it can be accessed without any restrictions while the vacuum process works on the next table. Yes, I think the way I phrased it was unfortunate. But if you issue VACUUM FULL you'll get an exclusive lock on everything, although not all at the same time. But of course, if your query load is like this BEGIN; SELECT from t1, t2 where t1.col1 = t2.col2; [application logic] UPDATE t3 . . . COMMIT; you'll find yourself blocked in the first statement on both t1 and t2; and then on t3 as well. You sure don't want that to happen automagically, in the middle of your business day. I have 5000 tables and a workstation with 1 GB RAM which hosts an Apache Web Server, Tomcat Servlet Container and PostgreSQL. RAM is not something that I have plenty of ... and the hardware is fixed and cannot be changed. I see. Well, I humbly submit that your problem is not the design of the PostgreSQL server, then. The hardware is fixed and cannot be changed, is the first optimisation I'd make. Heck, I gave away a box to charity only two weeks ago that would solve your problem better than automatically issuing VACUUM FULL. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(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: [PERFORM] Autovacuum / full vacuum
On Tue, Jan 17, 2006 at 09:59:25AM -0600, Scott Marlowe wrote: I have to admit, looking at the documentation, that we really don't explain this all that well in the administration section, and I can see how easily led astray beginners are. I understand what you mean, but I suppose my reaction would be that what we really need is a place to keep these things, with a note in the docs that the best practice settings for these are documented at some url, and evolve over time as people gain expertise with the new features. I suspect, for instance, that nobody knows exactly the right settings for any generic workload yet under 8.1 (although probably people know them well enough for particular workloads). A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(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: [PERFORM] Autovacuum / full vacuum
On Tue, Jan 17, 2006 at 11:43:14AM -0500, Chris Browne wrote: [EMAIL PROTECTED] (Andrew Sullivan) writes: Because nothing that runs automatically should ever take an exclusive lock on the entire database, That's a bit more than what autovacuum would probably do... Or even VACUUM FULL, as I tried to make clearer in another message: the way I phrased it suggests that it's a simultaneous lock on the entire database (when it is most certainly not). I didn't intend to mislead; my apologies. Note, though, that the actual effect for a user might look worse than a lock on the entire database, though, if you conider statement_timeout and certain use patterns. Suppose you want to issue occasional VACCUM FULLs, but your application is prepared for this, and depends on statement_timeout to tell it sorry, too long, try again. Now, if the exclusive lock on any given table takes less than statement_timeout, so that each statement is able to continue in its time, the application looks like it's having an outage _even though_ it is actually blocked on vacuums. (Yes, it's poor application design. There's plenty of that in the world, and you can't always fix it.) There is *a* case for setting up full vacuums of *some* objects. If you have a table whose tuples all get modified in the course of some common query, that will lead to a pretty conspicuous bloating of *that table.* Sure. And depending on your use model, that might be good. In many cases, though, a rotor table + view + truncate approach would be better, and would allow improved uptime. If you don't care about uptime, and can take long outages every day, then the discussion is sort of moot anyway. And _all_ of this is moot, as near as I can tell, given the OP's claim that the hardware is adequate and immutable, even though the former claim is demonstrably false. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] new to postgres (and db management) and performance already a problem :-(
On Mon, Jan 16, 2006 at 11:07:52PM +0100, Antoine wrote: performance problems (a programme running 1.5x slower than two weeks ago) might not be coming from the db (or rather, my maintaining of it). I have turned on stats, so as to allow autovacuuming, but have no idea whether that could be related. Is it better to schedule a cron job to do it x times a day? I just left all the default values in postgres.conf... could I do some tweaking? The first thing you need to do is find out where your problem is. Are queries running slowly? You need to do some EXPLAIN ANALYSE queries to understand that. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 15,000 tables
On Thu, Dec 01, 2005 at 08:34:43PM +0100, Michael Riess wrote: Well, we tried switching to daily VACUUM ANALYZE and weekly VACUUM FULL, but the database got considerably slower near the end of the week. If you have your FSM configured correctly and you are vacuuming tables often enough for your turnover, than in regular operation you should _never_ need VACUUM FULL. So it sounds like your first problem is that. With the 15000 tables you were talking about, though, that doesn't surprise me. Are you sure more back ends wouldn't be a better answer, if you're really wedded to this design? (I have a feeling that something along the lines of what Tom Lane said would be a better answer -- I think you need to be more clever, because I don't think this will ever work well, on any system.) A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] weird performances problem
On Thu, Nov 17, 2005 at 06:47:09PM +0100, Guillaume Smet wrote: queries are executed fast even if they are complicated but sometimes and for half an hour, we have a general slow down. Is it exactly half an hour? What changes at the time that happens (i.e. what else happens on the machine?). Is this a time, for example, when logrotate is killing your I/O with file moves? A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(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: [PERFORM] Help tuning postgres
On Tue, Oct 18, 2005 at 05:21:37PM +0200, Csaba Nagy wrote: INFO: vacuuming public.some_table INFO: some_table: removed 29598 row versions in 452 pages DETAIL: CPU 0.01s/0.04u sec elapsed 18.77 sec. INFO: some_table: found 29598 removable, 39684 nonremovable row versions in 851 pages DETAIL: 0 dead row versions cannot be removed yet. Does that mean that 39684 nonremovable pages are actually the active live pages in the table (as it reports 0 dead) ? I'm sure I don't have any long running transaction, at least according to pg_stats_activity (backed by the linux ps too). Or I should run a vacuum full... This table is one of which has frequently updated rows. No, you should be ok there. What that should tell you is that you have about 40,000 rows in the table. But notice that your vacuum process just removed about 75% of the live table rows. Moreover, your 39684 rows are taking 851 pages. On a standard installation, that's usually 8Kb/page. So that's about 6,808 Kb of physical storage space you're using. Is that consistent with the size of your data? If it's very large compared to the data you have stored in there, you may want to ask if you're leaking space from the free space map (because of that table turnover, which seems pretty severe). A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Help tuning postgres
On Thu, Oct 13, 2005 at 03:14:44PM +0200, Csaba Nagy wrote: In any case, I suppose that those disk pages should be in OS cache pretty soon and stay there, so I still don't understand why the disk usage is 100% in this case (with very low CPU activity, the CPUs are mostly waiting/idle)... the amount of actively used data is not that big. Ah, but if the sum of all the dead rows is large enough that they start causing your shared memory (== Postgres buffers) to thrash, then you start causing the memory subsystem to thrash on the box, which means less RAM is available for disk buffers because the OS is doing more work; and the disk buffers are full of a lot of garbage _anyway_, so then you may find that you're ending up hitting the disk for some of these reads after all. Around the office I have called this the buffer death spiral. And note that once you've managed to get into a vacuum-starvation case, your free space map might be exceeded, at which point your database performance really won't recover until you've done VACUUM FULL (prior to 7.4 there's also an index problem that's even worse, and that needs occasional REINDEX to solve; I forget which version you said you were using). The painful part about tuning a production system is really that you have to keep about 50 variables juggling in your head, just so you can uncover the one thing that you have to put your finger on to make it all play nice. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(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: [PERFORM] Help tuning postgres
On Wed, Oct 12, 2005 at 06:55:30PM +0200, Csaba Nagy wrote: Ok, that was the first thing I've done, checking out the explain of the query. I don't really need the analyze part, as the plan is going for the index, which is the right decision. The updates are simple one-row How do you know? You _do_ need the ANALYSE, because it'll tell you what the query _actually did_ as opposed to what the planner thought it was going to do. Note that EXPLAIN ANALYSE actually performs the work, so you better do it in a transaction and ROLLBACK if it's a production system. Actually I've done an iostat run in the meantime (just learned how to use it), and looks like the disk is 100 saturated. So it clearly is a disk issue in this case. And it turns out the Oracle hardware has an Yes, but it could be a disk issue because you're doing more work than you need to. If your UPDATEs are chasing down a lot of dead tuples, for instance, you'll peg your I/O even though you ought to have I/O to burn. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Whence the Opterons?
On Sat, May 07, 2005 at 02:00:34PM -0700, Josh Berkus wrote: Question, though: is HP still using their proprietary RAID card? And, if so, have they fixed its performance problems? According to my folks here, we're using the CCISS controllers, so I guess they are. The systems are nevertheless performing very well -- we did a load test that was pretty impressive. Also, Chris Browne pointed me to this for the drivers: http://sourceforge.net/projects/cciss/ A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Whence the Opterons?
On Fri, May 06, 2005 at 02:39:11PM -0700, Mischa Sandberg wrote: IBM, Sun and HP have their fairly pricey Opteron systems. We've had some quite good experiences with the HP boxes. They're not cheap, it's true, but boy are they sweet. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_autovacuum not having enough suction ?
On Fri, Mar 25, 2005 at 06:21:24PM -0500, Bruce Momjian wrote: Can we issue a LOCK TABLE with a statement_timeout, and only do the VACUUM FULL if we can get a lock quickly? That seems like a plan. I think someone else's remark in this thread is important, though: autovacuum shouldn't ever block other transactions, and this approach will definitely run that risk. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
On Tue, Mar 22, 2005 at 03:23:18PM -0600, Brandon Metcalf wrote: s What are you using to measure s performance? Nothing too scientific other than the fact that since we have moved the DB, we consistenly see a large number of postmater processes (close to 100) where before we did not. What did you move from? The Solaris ps (not in ucb, which is the BSD-style ps) shows the parent process name, so everything shows up as postmaster rather than postgres. There's always one back end per connection. If you are in fact using more connections, by the way, I can tell you that Solaris 8, in my experience, is _very bad_ at managing context switches. So you may not be merely I/O bound (although your other reports seem to indicate that you are). A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
On Wed, Mar 23, 2005 at 11:16:29AM -0600, Brandon Metcalf wrote: We moved from an HP-UX 10.20 box where the pgsql installation and data were on a vxfs fileystem. My best guess, then, is that ufs tuning really is your issue. We always used vxfs for our Sun database servers (which was a nightmare all on its own, BTW, so I don't actually recommend this), so I don't have any real ufs tuning advice. The Packer Solaris database book (Packer, Allan N., _Configuring Tuning Databases on the Solaris Platform_. Palo Alto: Sun Microsystems P, 2002. ISBN 0-13-083417-3) does suggest mounting the filesystems with forcedirectio; I dimly recall using this for the wal partition on one test box, and STR that it helped. Also, you want to make sure you use the right fsync method; if it's still set to fsync in the config file, you'll want to change that. I remember finding that fsync was something like 3 times slower than everything else. I don't have any more Solaris boxes to check, but I believe we were using open_datasync as our method. You'll want to run some tests. You also should enable priority paging, but expect that this will give you really strange po numbers from vmstat and friends. Priority paging, I found, makes things look like you're swapping when you aren't. Procmem is useful, but if you really want the goods on what's going on, you need the SE toolkit. Just be careful using it as root -- in some cases it'll modify kernel parameters behind the scenes. In my case, I didn't have superuser access, so there wasn't a danger; but I've heard sysadmins complain about this. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
On Mon, Jan 24, 2005 at 01:28:29AM +0200, Hannu Krosing wrote: IIRC it hates pg_dump mainly on master. If you are able to run pg_dump from slave, it should be ok. For the sake of the archives, that's not really a good idea. There is some work afoot to solve it, but at the moment dumping from a slave gives you a useless database dump. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: 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: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
On Fri, Jan 21, 2005 at 02:00:03AM -0500, Tom Lane wrote: got absolutely zero flak about their use of Postgres in connection with the .mobi bid, after having endured very substantial bombardment Well, absolutely zero is probably overstating it, but Tom is right that PostgreSQL is not the sort of major, gee-it's-strange technology it once was. PostgreSQL is indeed established technology in the ICANN world now, and I don't think anyone has an argument that it can't run a registry without trouble. I certainly believe that PostgreSQL is a fine technology for this. And it scales just fine; we added a million domains to .info over a couple days in September, and the effect on performance was unmeasurable (we'd have added them faster, but the bottleneck was actually the client). A domain add in our case is on the order of 10 database write operations; that isn't a huge load, of course, compared to large real-time manufacturing data collection or other such applications. (Compared to those kinds of applications, the entire set of Internet registry systems, including all the registrars, is not that big.) Incidentally, someone in this thread was concerned about having to maintain a separate password for each .org domain. It's true that that is a registrar, rather than a registry, issue; but it may also be a case where the back end is getting exposed. The .org registry uses a new protocol, EPP, to manage objects. One of the features of EPP is that it gives a kind of password (it's called authInfo) to each domain. The idea is that the registrant knows this authInfo, and also the currently-sponsoring registrar. If the registrant wants to switch to another registrar, s/he can give the authInfo to the new registrar, who can then use the authInfo in validating a transfer request. This is intended to prevent the practice (relatively widespread, alas, under the old protocol) where an unscrupulous party requests transfers for a (substantial number of) domain(s) without any authorization. (This very thing has happened recently to a somewhat famous domain on the Internet. I'll leave it to the gentle reader to do the required googling. The word panix might be of assistance.) So the additional passwords actually do have a purpose; but different registrars handle this feature differently. My suggestion is either to talk to your registrar or change registrars (or both) to get the behaviour you like. There are hundreds of registrars for both .info and .org, so finding one which acts the way you want shouldn't be too tricky. Anyway, this is pretty far off topic. But in answer to the original question, Afilias does indeed use PostgreSQL for this, and is happy to talk on the record about it. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
On Fri, Jan 21, 2005 at 03:23:30PM -0800, Kevin Brown wrote: beefier CPU setup would be in order. But in my (limited) experience, the disk subsystem is likely to be a bottleneck long before the CPU is in the general case, especially these days as disk subsystems haven't improved in performance nearly as quickly as CPUs have. Indeed. And you can go through an awful lot of budget buying solid state storage ;-) A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Swapping on Solaris
On Wed, Jan 19, 2005 at 10:42:26AM -0500, Alan Stange wrote: I'm fairly sure that the pi and po numbers include file IO in Solaris, because of the unified VM and file systems. That's correct. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
