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] 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] 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] 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] Help tuning postgres
On Thu, Oct 13, 2005 at 10:15:03AM +0200, Csaba Nagy wrote: > > OK, this sounds interesting, but I don't understand: why would an update > "chase down a lot of dead tuples" ? Should I read up on some docs, cause > I obviously don't know enough about how updates work on postgres... Right. Here's the issue: MVCC does not replace rows when you update. Instead, it marks the old row as expired, and sets the new values. The old row is still there, and it's available for other transactions who need to see it. As the docs say (see <http://www.postgresql.org/docs/8.0/interactive/transaction-iso.html>), "In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run." And that can be true because the original data is still there, although marked as expired for subsequent transactions. UPDATE works the same was as SELECT in terms of searching for rows (so does any command that searches for data). Now, when you select data, you actually have to traverse all the existing versions of the tuple in order to get the one that's live for you. This is normally not a problem: VACUUM goes around and cleans out old, expired data that is not live for _anyone_. It does this by looking for the oldest transaction that is open. (As far as I understand it, this is actually the oldest transaction in the entire back end; but I've never understood why that should the the case, and I'm too incompetent/dumb to understand the code, so I may be wrong on this point.) If you have very long-running transactions, then, you can end up with a lot of versions of dead tuples on the table, and so reading the few records you want can turn out actually to be a very expensive operation, even though it ought to be cheap. You can see this by using the VERBOSE option to VACUUM: test=# VACUUM VERBOSE eval1 ; INFO: vacuuming "public.eval1" INFO: "eval1": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "pg_toast.pg_toast_18831" INFO: index "pg_toast_18831_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_18831": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Note those "removable" and "nonremovable" row versions. It's the unremovable ones that can hurt. WARNING: doing VACUUM on a big table on a disk that's already pegged is going to cause you performance pain, because it scans the whole table. In some cases, though, you have no choice: if the winds are already out of your sails, and you're effectively stopped, anything that might get you moving again is an improvement. > And how would the analyze help in finding this out ? I thought it would > only show me additionally the actual timings, not more detail in what > was done... Yes, it shows the actual timings, and the actual number of rows. But if the estimates that the planner makes are wildly different than the actual results, then you know your statistics are wrong, and that the planner is going about things the wrong way. ANALYSE is a big help. There's also a verbose option to it, but it's usually less useful in production situations. A -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Server misconfiguration???
On Mon, Oct 10, 2005 at 05:31:10PM +0300, Andy wrote: > I read some tuning things, I made the things that are written there, but I > think that there improvements can be made. Have you tried the suggestions people made? Because if I were you, I'd be listing very carefully to what Chris and Tom were telling me about how to tune my database. 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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 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] 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] 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] 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] 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] 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] CPU Intensive query
On Fri, May 18, 2007 at 03:26:08PM -0700, Abu Mushayeed wrote: > Also, this query ran today and it already finished. Today it was > IO intensive. Are you entirely sure that it's not a coincidence, and something _else_ in the system is causing the CPU issues? A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(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] 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] 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] 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] 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] [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] 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: [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] 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: [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] 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] WALL on controller without battery?
On Wed, Jul 11, 2007 at 10:48:04AM -0400, Francisco Reyes wrote: > The question is, would I be better off putting WAL on the second, OS, > controller or in the 8 port controller? Specially since the 2 port will not > have battery (3ware does not have 2 ports with battery). Put the WAL where the battery is. Even if it's slower (and I don't know whether it will be), I assume that having the right data more slowly is better than maybe not having the data at all, quickly. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(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 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] 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] 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] 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 03:05:29PM +0100, Michael Riess wrote: > There must be a way to implement a daemon which frees up space of a > relation without blocking it too long. Define "too long". If I have a table that needs to respond to a SELECT in 50ms, I don't have time for you to lock my table. If this were such an easy thing to do, don't you think the folks who came up wit the ingenious lazy vacuum system would have done it? Remember, a vacuum full must completely lock the table, because it is physically moving bits around on the disk. So a SELECT can't happen at the same time, because the bits might move out from under the SELECT while it's running. Concurrency is hard, and race conditions are easy, to implement. 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 4: Have you searched our list archives? http://archives.postgresql.org
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 , 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] 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] 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] 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] 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] Some queries starting to hang
On Mon, Jun 05, 2006 at 04:07:19PM -0400, Tom Lane wrote: > > broke: > >-> Nested Loop (cost=30150.77..129334.04 rows=1 width=305) > > work: > >-> Hash Join (cost=30904.77..125395.89 rows=1810 width=306) > > I'm wondering about out-of-date or nonexistent ANALYZE stats, missing > custom adjustments of statistics target settings, etc. But even the nested loop shouldn't be a "never returns" case, should it? For 1800 rows? (I've _had_ bad plans that picked nestloop, for sure, but they're usually for tens of thousands of rows when they take forever). A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] vacuuming problems continued
On Thu, Jun 01, 2006 at 01:54:08PM +0200, Antoine wrote: > Hi, > We just don't seem to be getting much benefit from autovacuum. Running > a manual vacuum seems to still be doing a LOT, which suggests to me > that I should either run a cron job and disable autovacuum, or just > run a cron job on top of autovacuum. What the others said; but also, which version of autovacuum (== which version of the database) is this? Because the early versions had a number of missing bits to them that tended to mean the whole thing didn't hang together very well. > I have been thinking about strategies and am still a bit lost. Our > apps are up 24/7 and we didn't code for the eventuality of having the > db going offline for maintenance... we live and learn! You shouldn't need to, with anything after 7.4, if your vacuum regimen is right. There's something of a black art to it, though. 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 6: explain analyze is your friend
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 Tue, Jun 06, 2006 at 11:37:46AM -0400, Greg Stark wrote: > An alternate approach would be to implement a SIGINFO handler that > prints out the explain analyze output for the data built up so far. > You would be able to keep hitting C-t and keep getting updates > until the query completes or you decided to hit C-c. This is even better, and pretty much along the lines I was thinking in my other mail. If you can see the _first_ spot you break, you can start working. We all know (or I hope so, anyway) that it would be better to get the full result, and know everything that needs attention before starting. As nearly as I can tell, however, they don't teach Mill's methods to MBAs of a certain stripe, so changes start getting made without all the data being available. It'd be nice to be able to bump the set of available data to something higher than "none". (That said, I appreciate that there's precious little reason to spend a lot of work optimising a feature that is mostly there to counteract bad management practices.) 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 6: explain analyze is your friend
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] slow queue-like empty table
On Thu, Sep 28, 2006 at 08:56:31AM +0200, Tobias Brox wrote: > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: "my_queue": found 0 removable, 34058 nonremovable row versions in 185 > pages ^^^ You have a lot of dead rows that can't be removed. You must have a lot of other transactions in process. Note that nobody needs to be _looking_ at those rows for them to be unremovable. The transactions just have to be old enough. > --- > Limit (cost=0.00..0.04 rows=1 width=4) (actual time=402.525..402.525 rows=0 > loops=1) >-> Index Scan using my_queue_pkey on stats_bet_queue (cost=0.00..1314.71 > rows=34058 width=4) (actual time=402.518..402.518 rows=0 loops=1) I'm amazed this does an indexscan on an empty table. If this table is "hot", my bet is that you have attempted to optimise in an area that actually isn't an optimisation under PostgreSQL. That is, if you're putting data in there, a daemon is constantly deleting from it, but all your other transactions depend on knowing the value of the "unprocessed queue", the design just doesn't work under PostgreSQL. It turns out to be impossible to keep the table vacuumed well enough for high performance. 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 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: [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] 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: [PERFORM] VACUUMs take twice as long across all nodes
Ok, I see Tom has diagnosed your problem. Here are more hints anyway: On Fri, Oct 27, 2006 at 10:20:25AM +0100, Gavin Hamill wrote: > > table bloat in various slony-related tables. > > I know it takes longer, I know it blocks. It's never been a problem The problem from a VACUUM FULL is that its taking longer causes the vacuums on (especially) pg_listen and sl_log_[n] to be unable to recover as many rows (because there's an older transaction around). This is a significant area of vulnerability in Slony. You really have to readjust your vacuum assumptions when using Slony. > > 3. Your backups "from the slave" aren't done with pg_dump, > > right? > > Em, they are indeed. I assumed that MVCC would ensure I got a > consistent snapshot from the instant when pg_dump began. Am I wrong? That's not the problem. The problem is that when you restore the dump of the slave, you'll have garbage. Slony fools with the catalogs on the replicas. This is documented in the Slony docs, but probably not in sufficiently large-type bold italics in red with the tag set as would be appropriate for such a huge gotcha. Anyway, don't use pg_dump on a replica. There's a tool that comes with slony that will allow you to take consistent, restorable dumps from replicas if you like. (And you might as well throw away the dumpfiles from the replicas that you have. They won't work when you restore them.) 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 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] 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] Drop Tables Very Slow in Postgresql 7.2.1
On Wed, Mar 10, 2004 at 12:33:01PM +0530, Maneesha Nunes wrote: > Hello there !!! > > I am using postgresql7.2.1 as the backend for an E.R.P system running > on Linux Redhat 7.2(Enigma) You should upgrade, to at the _very least_ the last release of 7.2. There were bugs in earlier releases fixed in later releases; that's why there's a 7.2.4. (I'll also point out that the 7.2 series is missing plenty of performance enhancements which came later. I'd get to work on upgrading, because 7.2 is now basically unmaintained.) But in any case, you likely have issues on your system tables. I'd do a VACUUM FULL and a complete REINDEX of the system tables next. 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: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [ADMIN] syslog slowing the database?
On Thu, Mar 11, 2004 at 09:34:54AM +0800, Christopher Kings-Lynne wrote: > >You could also consider not using syslog at all: let the postmaster > >output to its stderr, and pipe that into a log-rotation program. > > Not an option I'm afraid. PostgreSQL just jams and stops logging after > the first rotation... Actually, this is what we do. Last year we offered an (admittedly expensive) bespoke log rotator written in Perl for just this purpose. It was rejected on the grounds that it didn't do anything Apache's rotator didn't do, so I didn't pursue it. I'm willing to put it up on gborg, though, if anyone thinks it'll be worth having around. FWIW, we use ours in production. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Scaling further up
On Fri, Mar 12, 2004 at 06:25:48PM -0500, Anjan Dave wrote: > We upgraded from 8GB to 12GB RAM a month or so ago, but even in the > past, I've never seen the system exhaust on it's system cache (~6GB, in > 'top'), while it's swapping. > > Some one had mentioned why not have the entire DB in memory? How do I > configure that, for knowledge? You don't. It'll automatically be in memory if (a) you have enough memory, (b) you don't have anything else on the machine using the memory, and (c) it's been read at least one time. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] rapid degradation after postmaster restart
Sorry I haven't had a chance to reply to this sooner. On Fri, Mar 12, 2004 at 05:38:37PM -0800, Joe Conway wrote: > The problem is this: the application runs an insert, that fires off a > trigger, that cascades into a fairly complex series of functions, that > do a bunch of calculations, inserts, updates, and deletes. Immediately > after a postmaster restart, the first insert or two take about 1.5 > minutes (undoubtedly this could be improved, but it isn't the main > issue). However by the second or third insert, the time increases to 7 - > 9 minutes. Restarting the postmaster causes the cycle to repeat, i.e. > the first one or two inserts are back to the 1.5 minute range. The vacuum delay stuff that you're working on may help, but I can't really believe it's your salvation if this is happening after only a few minutes. No matter how much you're doing inside those functions, you surely can't be causing so many dead tuples that a vacuum is necessary that soon. Did you try not vacuuming for a little while to see if it helps? I didn't see it anywhere in this thread, but are you quite sure that you're not swapping? Note that vmstat on multiprocessor Solaris machines is not notoriously useful. You may want to have a look at what the example stuff in the SE Toolkit tells you, or what you get from sar. I believe you have to use a special kernel setting on Solaris to mark shared memory as being ineligible for swap. 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [ADMIN] Benchmarking postgres on Solaris/Linux
On Mon, Mar 22, 2004 at 04:05:45PM -0800, Subbiah, Stalin wrote: > being the key performance booster for postgres. what is the preferred OS > for postgres deployment if given an option between linux and solaris. As One thing this very much depends on is what you're trying to do. Suns have a reputation for greater reliability. While my own experience with Sun hardware has been rather shy of sterling, I _can_ say that it stands head and shoulders above a lot of the x86 gear you can get. If you're planning to use Solaris on x86, don't bother. Solaris is a slow, bloated pig compared to Linux, at least when it comes to managing the largish number of processes that Postgres requires. If pure speed is what you're after, I have found that 2-way, 32 bit Linux on P-IIIs compares very favourably to 4 way 64 bit Ultra SPARC IIs. 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [ADMIN] Benchmarking postgres on Solaris/Linux
On Tue, Mar 23, 2004 at 08:53:42PM -, [EMAIL PROTECTED] wrote: > is way down the priority list compared with IO throughput, stability, > manageability, support, etc etc. Indeed, if our Suns actually diabled the broken hardware when they died, fell over, and rebooted themselves, I'd certainly praise them to heaven. But I have to say that the really very good reporting of failing memory has saved me some headaches. > environment, I'd take the Sun every day of the week, assuming that those > compile option changes have sorted out the oddly slow PG performance at > last. I seem to have hit a bad batch of Dell hardware recently, which makes me second this opinion. I should say, also, that my initial experience of AIX has been extremely good. I can't comment on the fun it might involve in the long haul, of course. 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
IBM P-series machines (was: [PERFORM] Excessive context switching on SMP Xeons)
On Tue, Oct 05, 2004 at 09:47:36AM -0700, Josh Berkus wrote: > As long as you're on x86, scaling outward is the way to go. If you want to > continue to scale upwards, ask Andrew Sullivan about his experiences running > PostgreSQL on big IBM boxes. But if you consider an quad-Opteron server > expensive, I don't think that's an option for you. Well, they're not that big, and both Chris Browne and Andrew Hammond are at least as qualified to talk about this as I. But since Josh mentioned it, I'll put some anecdotal rablings here just in case anyone is interested. We used to run our systems on Solaris 7, then 8, on Sun E4500s. We found the performance on those boxes surprisingly bad under certain pathological loads. I ultimately traced this to some remarkably poor shared memory handling by Solaris: during relatively heavy load (in particular, thousands of selects per second on the same set of tuples) we'd see an incredible number of semaphore operations, and concluded that the buffer handling was killing us. I think we could have tuned this away, but for independent reasons we decided to dump Sun gear (the hardware had become unreliable, and we were not satisfied with the service we were getting). We ended up choosing IBM P650s as a replacement. The 650s are not cheap, but boy are they fast. I don't have any numbers I can share, but I can tell you that we recently had a few days in which our write load was as large as the entire write load for last year, and you couldn't tell. It is too early for us to say whether the P series lives up to its billing in terms of relibility: the real reason we use these machines is reliability, so if approaching 100% uptime isn't important to you, the speed may not be worth it. We're also, for the record, doing experiments with Opterons. So far, we're impressed, and you can buy a lot of Opteron for the cost of one P650. 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: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Why isn't this index being used?
On Tue, Oct 19, 2004 at 11:33:50AM -0400, Knutsen, Mark wrote: > (Why don't replies automatically go to the list?) Because sometimes you don't want them to. There's been dozens of discussions about this. BTW, mutt has a nice feature which allows you to reply to lists -- I imagine other MUAs have such a feature too. > Sure enough, quoting the constants fixes the problem. > > Is it a best practice to always quote constants? No, but it's very useful in these cases. The problem is I believe this is fixed in 8.0, BTW. See the FAQ, question 4.8 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: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Performance Anomalies in 7.4.5
> Probably the most severe objection to doing things this way is that the > selected plan could change unexpectedly as a result of the physical > table size changing. Right now the DBA can keep tight rein on actions > that might affect plan selection (ie, VACUUM and ANALYZE), but that > would go by the board with this. OTOH, we seem to be moving towards > autovacuum, which also takes away any guarantees in this department. But aren't we requiring that we can disable autovacuum on some tables? I've actually used, more than once, the finger-on-the-scale method of thumping values in pg_class when I had a pretty good idea of how the table was changing, particularly when it would change in such a way as to confuse the planner. There are still enough cases where the planner doesn't quite get things right that I'd really prefer the ability to give it clues, at least indirectly. I can't imagine that there's going to be a lot of enthusiasm for hints, so anything that isn't a sure-fire planner helper is a potential loss, at least to me. 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: 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 Anomalies in 7.4.5
On Fri, Oct 22, 2004 at 05:13:18PM -0400, Matthew T. O'Connor wrote: > Yes that is the long term goal, but the autovac in 8.0 is still all or > nothing. Yes, which is why I couldn't use the current iteration for production: the cost is too high. I think this re-inforces my original point, which is that taking away the ability of DBAs to thump the planner for certain tables -- even indirectly -- under certain pathological conditions is crucial for production work. In the ideal world, the wizards and genius planners and such like would work perfectly, and the DBA would never have to intervene. In practice, there are cases when you need to haul on a knob or two. While this doesn't mean that we should adopt the Oracle approach of having knobs which adjust the sensitivity of the knob that tunes the main knob-tuner, I'm still pretty leery of anything which smacks of completely locking the DBA's knowledge out of the system. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Restricting Postgres
On Tue, Nov 02, 2004 at 11:52:12PM +, Martin Foster wrote: > Is there a way to restrict how much load a PostgreSQL server can take > before dropping queries in order to safeguard the server?I was Well, you could limit the number of concurrent connections, and set the query timeout to a relatively low level. What that ought to mean is that, under heavy load, some queries will abort. 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] preloading indexes
On Wed, Nov 03, 2004 at 12:12:43PM -0700, [EMAIL PROTECTED] wrote: > That's correct - I'd like to be able to keep particular indexes in RAM > available all the time If these are queries that run frequently, then the relevant cache will probably remain populated[1]. If they _don't_ run frequently, why do you want to force the memory to be used to optimise something that is uncommon? But in any case, there's no mechanism to do this. A [1] there are in fact limits on the caching: if your data set is larger than memory, for instance, there's no way it will all stay cached. Also, VACUUM does nasty things to the cache. It is hoped that nastiness is fixed in 8.0. -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] preloading indexes
On Wed, Nov 03, 2004 at 01:19:43PM -0700, [EMAIL PROTECTED] wrote: > The caching appears to disappear overnight. The environment is not in > production yet so I'm the only one on it. Are you vacuuming at night? It grovels through the entire database, and may bust your query out of the cache. Also, we'd need some more info about how you've tuned this thing. Maybe check out the archives first for some tuning pointers to help you. 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 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] preloading indexes
On Wed, Nov 03, 2004 at 03:53:16PM -0500, Andrew Sullivan wrote: > and may bust your query out of the cache. Also, we'd need some more Uh, the data you're querying, of course. Queries themselves aren't cached. 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: 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] Alternatives to Dell?
On Fri, Dec 03, 2004 at 07:19:37AM -0700, Cott Lang wrote: > Consider Sun's new line of Opterons. They've been around for a couple of I wouldn't buy a ray of sunshine from Sun in the middle of January at the north pole, given the customer experience I had with them. They had consistent failures in some critical hardware, and it was like asking them to donate a kidney when we tried to get the things fixed. Finally, they told us that they'd sell us the new line of hardware instead. In other words, "The last version was broken, but _this_ one works! We promise!" We told them to take a long walk off a short pier. Their service people sure _try_ hard in the field, but some machines required three and four visits to fix. I also find the Sun Opteron offering to be way overpriced compared to the competition. In case it's not obvious, I don't speak for my employer. 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Config review
On Tue, Dec 07, 2004 at 10:07:54AM -0500, Tom Lane wrote: > If you are using a RAID configuration it might just be that you need > to adjust the configuration (IIRC, there are some RAID setups that > are not very write-friendly). Otherwise you may have little alternative > but to buy faster disks. It might be that altering the Clariion array from RAID 5 to RAID 1+0 would make a difference; but I'd be very surprised to learn that you could get that array to go a whole lot faster. One thing that might also be worth investigating is whether performance actually goes up by moveing the WAL into the array. We've had some remarkably good experiences with our recently-acquired EMC. 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: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
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
Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
On Thu, Jan 20, 2005 at 04:02:39PM +0100, Hervé Piedvache wrote: > > I don't insist about have data in RAM but when you use PostgreSQL with > big database you know that for quick access just for reading the index file > for example it's better to have many RAM as possible ... I just want to be > able to get a quick access with a growing and growind database ... Well, in any case, you need much better hardware than you're looking at. I mean, dual Xeon with 2 Gig isn't hardly big iron. Why don't you try benchmarking on a honking big box -- IBM P690 or a big Sun (I'd counsel against that, though) or something like that? Or even some Opterons. Dual Xeon is probablt your very worst choice at the moment. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
On Thu, Jan 20, 2005 at 10:40:02PM -0200, Bruno Almeida do Lago wrote: > > I was thinking the same! I'd like to know how other databases such as Oracle > do it. You mean "how Oracle does it". They're the only ones in the market that really have this technology. 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 7: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
On Thu, Jan 20, 2005 at 03:54:23PM +0100, Hervé Piedvache wrote: > Slony do not use RAM ... but PostgreSQL will need RAM for accessing a > database > of 50 Gb ... so having two servers with the same configuration replicated by > slony do not slove the problem of the scalability of the database ... You could use SSD for your storage. That'd make it go rather quickly even if it had to seek on disk. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
On Thu, Jan 20, 2005 at 04:07:51PM +0100, Hervé Piedvache wrote: > Yes seems to be the only solution ... but I'm a little disapointed about > this ... could you explain me why there is not this kind of > functionnality ... it seems to be a real need for big applications no ? I hate to be snarky, but the reason there isn't this kind of system just hanging around is that it's a Very Hard Problem. I spent 2 days last week in a room with some of the smartest people I know, and there was widespread agreement that what you want is a very tough problem. 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: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
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 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 on Solaris 8 and ufs
On Wed, Mar 23, 2005 at 09:32:07AM -0800, Tom Arthurs wrote: > found that high context switching seems to be more a symptom, Yes, that's a good point. It usually _is_ a symptom; but it might be a symptom that you've got an expensive query, and Solaris's foot-gun approach to handling such cases is a little painful. (We didn't give up on Solaris because of cs problems, BTW; but I have to say that AIX seems to be a little less prone to self-DOS on this front than Solaris was. If you really want to hear me rant, ask me some time about ecache and Sun support.) A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Curious about dead rows.
On Sat, Nov 10, 2007 at 09:22:58PM -0500, Jean-David Beyer wrote: > > > > So, there are NO failed inserts, and no updates? Cause that's what > > I'd expect to create the dead rows. > > > So would I. Hence the original question. Foreign keys with cascading deletes or updates? A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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] 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] 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
