Re: [PERFORM] Update table performance
On 8/9/07, Michael Stone <[EMAIL PROTECTED]> wrote: > On Thu, Aug 09, 2007 at 06:04:09PM +0530, Merlin Moncure wrote: > >keep an eye for the HOT feature which will hopefully make 8.3 that > >will highly reduce the penalty for (small) updates in many cases. > > Is there an overview somewhere about how this feature works and what it > is expected to do? There have been a lot of references to it over time, > and it's possible to understand it if you follow list traffic over time, > but starting cold it's hard to know what it is. The name was poorly > chosen as far as google is concerned. :) This is what I found when I went looking for info earlier: http://archives.postgresql.org/pgsql-patches/2007-07/msg00142.php http://archives.postgresql.org/pgsql-patches/2007-07/msg00360.php ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On 9/5/07, Carlo Stonebanks <[EMAIL PROTECTED]> wrote: > > > Right, additionally NTFS is really nothing to use on any serious disc > > > array. > > > > Do you mean that I will not see any big improvement if I upgrade the disk > > subsystem because the client is using NTFS (i.e. Windows) > > No, I think he's referring more to the lack of reliability of NTFS > compared to UFS / ZFS / JFS / XFS on unixen. Lack of reliability compared to _UFS_? Can you elaborate on this? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On 9/5/07, Trevor Talbot <[EMAIL PROTECTED]> wrote: > > On 9/5/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > On 9/5/07, Carlo Stonebanks <[EMAIL PROTECTED]> wrote: > > > > > Right, additionally NTFS is really nothing to use on any serious disc > > > > > array. > > > > > > > > Do you mean that I will not see any big improvement if I upgrade the > > > > disk > > > > subsystem because the client is using NTFS (i.e. Windows) > > > > > > No, I think he's referring more to the lack of reliability of NTFS > > > compared to UFS / ZFS / JFS / XFS on unixen. > > > > Lack of reliability compared to _UFS_? Can you elaborate on this? > Not a lot. Back when I was an NT 4.0 sysadmin, I had many many > occasions where NTFS simply corrupted for no apparent reason. No > system crash, no obvious problems with the drive, and bang suddenly a > file goes corrupted. About that time I gave up on Windows and started > supporting Linux and Solaris. Neither is perfect, but I've never had > either of them just corrupt a file on good hardware for no reason. Anecdotal then. That's fine, but needs to be qualified as such, not presented as a general case that everyone with experience agrees is true. I mean, I've got a box running OpenBSD UFS that's lost files on me, while my NTFS boxes have been fine other than catastrophic drive failure. But that anecdote doesn't actually mean anything, since it's useless in the general case. (The issues on that one UFS box have a known cause anyway, related to power failures.) > With the newer journalling file systems on linux, solaris and BSD, you > get both good performance and very reliable behaviour. Maybe NTFS has > gotten better since then, but I don't personally know. The thing is, most UFS implementations I'm familiar with don't journal; that's what prompted my question in the first place, since I figured you were thinking along those lines. NTFS is metadata-journaling, like most of the others, and has continued to improve over time. I took the original comment to be about performance, actually. NTFS's journaling method tends to get bashed in that department compared to some of the more modern filesystems. I don't have any experience with intensive I/O on large arrays to know. Hopefully he'll clarify what he meant. > Oh, the other issue that NTFS still seems to suffer from that most > unix file systems have overcome is fragmentation. Since you can't > defrag a live system, you have to plan time to take down the db should > the NTFS partition for your db get overly fragmented. Live defragmentation has been supported since NT4, although Microsoft never included tools or publicly documented it until 2000. The NTFS implementation in Windows doesn't make much effort to avoid fragmentation, but that varies among implementations of the other filesystems too. Modern ones tend to be better at it. > And there's the issue that with windows / NTFS that when one process > opens a file for read, it locks it for all other users. This means > that things like virus scanners can cause odd, unpredictable failures > of your database. It's simply a Windows platform default for file I/O; there's no hard limitation there, and it's not about a particular filesystem. In the case of antivirus vs database, it's more of an administrative issue: configure the AV to ignore the database files, harass the AV vendor to get programmers with clue, find another AV vendor, or just don't run AV on your dedicated database server. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] utilising multi-cpu/core machines?
On 9/5/07, Thomas Finneid <[EMAIL PROTECTED]> wrote: > how does pg utilise multi cpus/cores, i.e. does it use more than one > core? and possibly, how, are there any documentation about this. PostgreSQL creates a new process to handle each connection to the database. Multiple sessions can therefore spread across multiple cores, but a single session will never use more than one. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Curious about dead rows.
On 11/13/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Jean-David Beyer wrote: > > Andrew Sullivan wrote: > > > I'm not a private support organisation; please send your replies to the > > > list, not me. > > > > Sorry. Most of the lists I send to have ReplyTo set, but a few do not. > > And then I forget. > > If you use "reply to all", it works wonderfully in both cases. Then it upsets the people who don't want to get private copies, only list copies, on most of the Reply-To lists. There's no winning :( ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] TB-sized databases
On 11/29/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > On Wed, 2007-11-28 at 14:48 +0100, Csaba Nagy wrote: > >> In fact an even more useful option would be to ask the planner to throw > >> error if the expected cost exceeds a certain threshold... > > Tom's previous concerns were along the lines of "How would know what to > > set it to?", given that the planner costs are mostly arbitrary numbers. > Hm, that's only kind of true. > Obviously few people know how long such a page read takes but surely you would > just run a few sequential reads of large tables and set the limit to some > multiple of whatever you find. > > This isn't going to precise to the level of being able to avoid executing any > query which will take over 1000ms. But it is going to be able to catch > unconstrained cross joins or large sequential scans or such. Isn't that what statement_timeout is for? Since this is entirely based on estimates, using arbitrary fuzzy numbers for this seems fine to me; precision isn't really the goal. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
On 12/18/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > On Tue, 18 Dec 2007 10:05:46 -0600 > "Roberts, Jon" <[EMAIL PROTECTED]> wrote: > > If we are talking about enhancement requests, I would propose we > > create a role that can be granted/revoked that enables a user to see > > dictionary objects like source code. Secondly, users should be able > > to see their own code they write but not others unless they have been > > granted this dictionary role. > You are likely not going to get any support on an obfuscation front. > This is an Open Source project :P Wait, what? This is a DBMS, with some existing security controls regarding the data users are able to access, and the proposal is about increasing the granularity of that control. Arbitrary function bodies are just as much data as anything else in the system. Obfuscation would be something like encrypting the function bodies so that even the owner or administrator cannot view or modify the code without significant reverse engineering. I mean, some people do want that sort of thing, but this proposal isn't even close. Where on earth did "obfuscation" come from? ---(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 12/20/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Roberts, Jon wrote: > > This really is a needed feature to make PostgreSQL more attractive to > > businesses. A more robust security model that better follows commercial > > products is needed for adoption. > I would argue that commercial products need to get a clue and stop > playing bondage with their users to help stop their imminent and frankly > obvious downfall from the Open Source competition. I'm still not seeing where your comments are actually coming from, and I can't decipher your argument as a result. Exactly what is it about fine-grained security controls that is "playing bondage with their users"? > This "feature" as it is called can be developed externally and has zero > reason to exist within PostgreSQL. If the feature has the level of > demand that people think that it does, then the external project will be > very successful and that's cool. I'm unsure of what you consider "external" here. Is SE-PostgreSQL the type of thing you mean? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] viewing source code
It seems like a lot of people only saw "hide source code" in the original message, and then went off on tangents that don't have anything to do with the request. Again: On 12/14/07, Roberts, Jon <[EMAIL PROTECTED]> wrote: > Is it possible yet in PostgreSQL to hide the source code of functions from > users based on role membership? I would like to avoid converting the code > to C to secure the source code and I don't want it obfuscated either. > > In an ideal world, if a user can't modify a function, he/she shouldn't be > able to see the source code. If the user can execute the function, then the > user should be able to see the signature of the function but not the body. As a Role under PostgreSQL, I can create tables, views, functions, etc. As the owner of those objects, I can control what other roles can view data through them, and what roles can modify them. However, unlike tables, I cannot control what roles can view the data contained within my PL functions (body). That's it. A very simple problem. One that has absolutely nothing whatsoever to do with encrypted storage on disk or hiding things from DBAs or superusers. I'm surprised this group ended up so far off point. It's not as if objecting to this requires a bunch of abstract hyperbole, just a simple "it's not worth the effort and it's considered a bad idea to put security-senstive data inside PL function bodies". On 12/20/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > On Thu, 20 Dec 2007 10:47:53 -0800 > "Trevor Talbot" <[EMAIL PROTECTED]> wrote: > > > This "feature" as it is called can be developed externally and has > > > zero reason to exist within PostgreSQL. If the feature has the > > > level of demand that people think that it does, then the external > > > project will be very successful and that's cool. > > I'm unsure of what you consider "external" here. Is SE-PostgreSQL the > > type of thing you mean? > I don't know that it needs to be that extensive. I noted elsewhere in > the thread the idea of a plpgsql_s. I think that is an interesting > idea. I just don't think it needs to be incorporated into > postgresql-core. I was trying to get a handle on whether you meant external as in middleware, or external as in third-party patches to PostgreSQL. The OP's request doesn't necessarily need something as extensive as SE-PostgreSQL, but it needs to be on the same level: something that affects the database surface clients see, not apps behind middleware. On 12/20/07, Tom Lane <[EMAIL PROTECTED]> wrote: > "Merlin Moncure" <[EMAIL PROTECTED]> writes: > > I don't really agree that wrapping pl/pgsql with encryptor/decryptor > > is a bad idea. > It's quite a good idea, because it has more than zero chance of > succeeding politically in the community. Something that looks a lot like encryption of the entire database is more likely to succeed politically than a simple addition to PostgreSQL's existing role-based security model? Really? It's not like I can claim otherwise, I'm just wondering if I woke up in an alternate universe this morning... > The fundamental reason why preventing access to pg_proc.prosrc won't > happen is this: all the pain (and there will be plenty) will be > inflicted on people who get none of the benefit (because they don't give > a damn about hiding their own functions' code). The folks who want > function hiding can shout all they want, but as long as there is a very > sizable fraction of the community who flat out *don't* want it, it's > not going to get applied. I don't understand. Can you give an example of pain you see coming? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
I wrote: > That's it. A very simple problem. It was hinted to me off-list that my mail was fanning the flames, so to clarify: when I say things like the above, I mean conceptually. I think there might be a shared pool of knowledge that says it's anything but simple in practical terms, but that hasn't been communicated clearly in this thread. That's what I was getting at. ---(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
