Barry,
I have made a similar experience, moving a big Oracle data base to
Postgres 8.03 on linux.
The first impact was similar, huge performance problems.
The main problem was bad planner choices. The cause in our case: bad
parameter types in the jdbc set methods (I guess you use Java). For
oracle
Hi all,
After a long time of reading the general list it's time to subscribe to
this one...
We have adapted our application (originally written for oracle) to
postgres, and switched part of our business to a postgres data base.
The data base has in the main tables around 150 million rows, the wh
[snip]
> Have you tried reindexing your active tables?
>
Not yet, the db is in production use and I have to plan for a down-time
for that... or is it not impacting the activity on the table ?
> Emil
>
> ---(end of broadcast)---
> TIP 9: In versions
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
updates of one column, qualified by the primary key condition.
This part is OK, the qu
[snip]
> 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.
OK, this sounds interesting, but I don't understand: why would an u
ba.
On Thu, 2005-10-13 at 14:40, Andrew Sullivan wrote:
> 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 so
table is one of which has frequently updated rows.
TIA,
Csaba.
On Thu, 2005-10-13 at 14:40, Andrew Sullivan wrote:
> 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
> > "c
First of all thanks all for the input.
I probably can't afford even the reindex till Christmas, when we have
about 2 weeks of company holiday... but I guess I'll have to do
something until Christmas.
The system should at least look like working all the time. I can have
downtime, but only for shor
Christian,
Do you have foreign keys pointing to your table with ON CASCADE... ?
Cause in that case you're not only deleting your 22000 records, but the
whole tree of cascades. And if you don't have an index on one of those
foreign keys, then you might have a sequential scan of the child table
on e
Hi all,
I wonder what is the main driving factor for vacuum's duration: the size
of the table, or the number of dead tuples it has to clean ?
We have a few big tables which are also heavily updated, and I couldn't
figure out a way to properly vacuum them. Vacuuming any of those took
very long amo
Alex,
I suppose the table is a kind of 'queue' table, where you
insert/get/delete continuously, and the life of the records is short.
Considering that in postgres a delete will still leave you the record in
the table's file and in the indexes, just mark it as dead, your table's
actual size can gro
ght. Full vacuum
> fixes the problem. Thank you very much!
>
> I expect there will be less than 1000 records in the table. The index does
> obvous improvement on "SELECT task_id, username FROM download_queue WHERE
> username > '%s'" even there are only 100 r
Joost,
Why do you use an offset here ? I guess you're traversing the table
somehow, in this case it would be better to remember the last zipcode +
housenumber and put an additional condition to get the next bigger than
the last one you've got... that would go for the index on
zipcode+housenumber a
On Tue, 2005-12-06 at 13:20, Joost Kraaijeveld wrote:
[snip]
> Ah, a misunderstanding: I only need to calculate an index if the user
> wants a record that is not in or adjacent to the cache (in which case I
> can do a "select values > last value in the cache". So I must always
> materialize all ro
Moritz,
Is it possible that you use lots of temporary tables, and you don't
vacuum the system tables ? That would cause such symptoms I guess...
Try to make a "vacuum analyze" connected as the postgres super user,
that will vacuum all your system tables too. Note that if you have a
really big bloa
On Tue, 2007-02-06 at 01:35, Karen Hill wrote:
> [snip] So far I've been sitting here for about 2 million ms
> waiting for it to complete, and I'm not sure how many inserts postgres
> is doing per second.
One way is to run analyze verbose on the target table and see how many
pages it has, and then
On Thu, 2007-02-22 at 22:53, Mark Stosberg wrote:
> Thanks to everyone for the feedback about vacuuming. It's been very
> useful. The pointers to the pgstattuple and Pgfouine tools were also
> helpful.
>
> I'm now considering the following plan for trying Autovacuuming again
> with 8.1. I'd like a
I only know to answer your no. 2:
> 2) What about the issue with excessive locking for foreign keys when
> inside a transaction? Has that issue disappeared in 8.2? And if not,
> would it affect similarly in the case of multiple-row inserts?
The exclusive lock is gone already starting with 8.0 II
> Or are these Flash disks so slow that they compare to the HD's latency
> figures?
On sequential read speed HDs outperform flash disks... only on random
access the flash disks are better. So if your application is a DW one,
you're very likely better off using HDs.
Cheers,
Csaba.
> Can someone confirm that I've identified the right fix?
I'm pretty sure that won't help you... see:
http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php
The deadlock will be there if you update/insert the child table and
update/insert the parent table in the same transaction (even
> A frequently mentioned approach to avoid the point of contention is to
> have a "totals" record and have the triggers insert "deltas" records; to
> get the sum, add them all. Periodically, take the deltas and apply them
> to the totals.
This is what we do here too. There is only one exception t
On Wed, 2007-07-18 at 15:36, Michael Dengler wrote:
> Row X is inserted into TableX in DB1 on server1TableX trigger
> function fires and contacts DB2 on server2 and inserts the row into
> TableY on server2.
This kind of problem is usually solved more robustly by inserting the
"change" into a
On Fri, 2007-09-21 at 09:03 +0100, Gregory Stark wrote:
> >> Mem: 32945280k total, 32871832k used,73448k free, 247432k buffers
> >> Swap: 1951888k total,42308k used, 1909580k free, 30294300k cached
> >
> It seems to imply Linux is paging out sysV shared memory. In fact some of
> Heikki
On Fri, 2007-09-21 at 10:43 +0100, Gregory Stark wrote:
> The other possibility is that Postgres just hasn't even touched a large part
> of its shared buffers.
>
But then how do you explain the example I gave, with a 5.5GB table
seq-scanned 3 times, shared buffers set to 12 GB, and top still sho
Hi all,
Postgres version: 8.2.4
Tables:
table_a(a bigint, b bigint, primary key(a, b) );
table_b1(b bigint primary key, more columns...);
table_b2(b bigint primary key references table_b1(b), more columns...);
table_b1:
~ 27M rows;
~25 more columns;
width=309 (as reported by explain selec
On Fri, 2007-09-21 at 11:59 +0100, Simon Riggs wrote:
> Please re-run everything on clean tables without frigging the stats. We
> need to be able to trust what is happening is normal.
I did, the plan fiddling happened after getting the plans after a fresh
analyze, and I did run the plan again with
On Fri, 2007-09-21 at 11:34 +0100, Heikki Linnakangas wrote:
> Which version of Postgres is this? In 8.3, a scan like that really won't
> suck it all into the shared buffer cache. For seq scans on tables larger
> than shared_buffers/4, it switches to the bulk read strategy, using only
> a few buff
On Fri, 2007-09-21 at 12:34 +0100, Simon Riggs wrote:
> On Fri, 2007-09-21 at 13:29 +0200, Csaba Nagy wrote:
>
> > > Can you plans with/without LIMIT and with/without cursor, for both b1
> > > and b2?
> >
> > The limit is unfortunately absolutely needed part o
[snip]
Ok, I was not able to follow your explanation, it's too deep for me into
what the planner does...
> Incidentally, the way out of this is to improve the stats by setting
> stats target = 1000 on column a of ta. That will allow the optimizer to
> have a better estimate of the tail of the dis
> OK, I can confirm that. I set the statistics target for column "a" on
> table_a to 1000, analyzed, and got the plan below. The only downside is
> that analyze became quite expensive on table_a, it took 15 minutes and
> touched half of the pages... I will experiment with lower settings,
> maybe it
On Mon, 2007-09-24 at 14:27 +0100, Simon Riggs wrote:
> Csaba, please can you copy that data into fresh tables, re-ANALYZE and
> then re-post the EXPLAINs, with stats data.
Well, I can of course. I actually tried to generate some random data
with similar record count and relations between the tabl
On Mon, 2007-09-24 at 17:14 +0200, Gábor Farkas wrote:
> will i achieve the same thing by simply dropping that table and
> re-creating it?
If you have an index/PK on that table, the fastest and most useful way
to rebuild it is to do CLUSTER on that index. That will be a lot faster
than VACUUM FUL
> Csaba, please can you copy that data into fresh tables, re-ANALYZE and
> then re-post the EXPLAINs, with stats data.
Here you go, fresh experiment attached.
Cheers,
Csaba.
db=# \d temp_table_a
Table "public.temp_table_a"
Column | Type | Modifiers
++---
a |
On Wed, 2007-09-26 at 11:22 -0400, Tom Lane wrote:
> ... how
> many values of "a" are there really, and what's the true distribution of
> counts?
table_a has 23366 distinct values. Some statistics (using R):
> summary(table_a_histogram)
a count
Min. : 7857
On Thu, 2007-09-27 at 10:40 -0400, Tom Lane wrote:
> And yet there's another trap here: if the parameter you passed in
> chanced to be one of the very common values, a plan that was optimized
> for a small number of matches would perform terribly.
>
> We've speculated about trying to deal with the
On Thu, 2007-09-27 at 11:07 -0700, Ron Mayer wrote:
> Csaba Nagy wrote:
> >
> > Well, my problem was actually solved by rising the statistics target,
>
> Would it do more benefit than harm if postgres increased the
> default_statistics_target?
>
> I see a fair num
> Just an idea, but with the 8.3 concurrent scan support would it be
> possible to hang a more in depth analyze over exisiting sequential
> scans. Then it would be a lower cost to have higher resolution in
> the statistics because the I/O component would be hidden.
The biggest problem with that is
On Tue, 2007-10-23 at 08:53 -0700, Ron St-Pierre wrote:
> [snip] We were running autovacuum but it interfered with
> the updates to we shut it off.
This is not directly related to your question, but it might be good for
your DB: you don't need to turn off autovacuum, you can exclude tables
indivi
Hi all,
Short story:
I have a quite big table (about 200 million records, and ~2-3 million
updates/~1 million inserts/few thousand deletes per day). I started a
vacuum on it on friday evening, and it still runs now (monday
afternoon). I used "vacuum verbose", and the output looks like:
INFO: va
> So one very effective way of speeding this process up is giving the
> vacuum process lots of memory, because it will have to do fewer passes
> at each index. How much do you have?
OK, this is my problem... it is left at default (16 megabyte ?). This
must be a mistake in configuration, on other
Mikael,
I've just recently passed such an experience, i.e. migrating from
another vendor to postgres of a DB about the same size category you
have.
I think you got it right with the fsync turned off during migration
(just don't forget to turn it back after finishing ;-), and using tables
without
> Did you try mounting ext3 whith data=writeback by chance? People have
> found that makes a big difference in performance.
I'm not sure, there's other people here doing the OS stuff - I'm pretty
much ignorant about what "data=writeback" could mean :-D
They knew however that for the data partitio
> For the record, that's the wrong way round. For the data partitioning
> metadata journaling is enough, and for the WAL partition you don't need any
> FS journaling at all.
Yes, you're right: the data partition shouldn't loose file creation,
deletion, etc., which is not important for the WAL part
On Wed, 2006-03-22 at 16:35, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Oh, so in other words, SELECT * INTO temp FROM table is inherently
> > non-deterministic at the physical level, so the only way to be able to
> > allow PITR to work is to duplicate all the physical changes
You very likely forgot to run ANALYZE on your laptop after copying the
data. Observe the different row count estimates in the 2 plans...
HTH,
Csaba.
> QUERY PLAN
>
OK, I marked the wrong row counts, but the conclusion is the same.
Cheers,
Csaba.
> > QUERY PLAN
> > ---
> > Index Scan using ticketing_codes_uq_value_
[snip]
> It would be interesting to know what the bottleneck is for temp tables
> for you. They do not go via the buffer-cache, they are stored in
[snip]
Is it possible that the temp table creation is the bottleneck ? Would
that write into system catalogs ? If yes, maybe the system catalogs are
no
> So, it appears my autovacuum is just NOT working... I must have screwed
> something up, but I cannot see what.
Is it possible that you have long running transactions ? If yes, VACUUM
is simply not efficient, as it won't eliminate the dead space
accumulated during the long running transaction.
On Wed, 2006-06-21 at 17:27, jody brownell wrote:
> Our application is broken down quite well. We have two main writing processes
> writing to two separate sets of tables. No crossing over, nothign to prohibit
> the
> vacuuming in the nature which you describe.
It really doesn't matter what tab
On Wed, 2006-06-21 at 18:21, jody brownell wrote:
> That is interesting.
>
> There is one thread keeping a transaction open it appears from ps
>
> postgres: app app xxx(42644) idle in transaction
That shouldn't be a problem on itself, "idle in transaction" happens all
the time between 2 commands
On Wed, 2006-06-21 at 18:39, jody brownell wrote:
> that is exactly what I am seeing, one process, no change, always in idle
> while the others are constantly
> changing their state.
>
> looks like someone opened a tx then is blocking on a queue lock or something.
> dang.
Don't forget to check
> I've just fired off a "DELETE FROM table" command (i.e. unfiltered
> DELETE) on a trivially small table but with many foreign key references
> (on similar-sized tables), and I'm waiting for it to finish. It's been
> 10 minutes now, which seems very excessive for a table of 9000 rows on a
> 3
On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote:
> How can I configure the vacuum to run after the daily batch insert/update?
Check out this:
http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html
By inserting the right row you can disable autovacuum to vacuum your big
tables,
On Thu, 2006-09-28 at 09:36, Tobias Brox wrote:
> [Tobias Brox - Thu at 08:56:31AM +0200]
> > It really seems like some transaction is still viewing the queue, since
> > it found 38k of non-removable rows ... but how do I find the pid of the
> > transaction viewing the queue? As said, the pg_locks
> If we didn't want to add it for each list we could just add a link here:
>
> http://www.postgresql.org/community/lists/subscribe
+1
When I want to unsubscribe from a list (very rare in my case, I don't
subscribe in the first place if I'm not sure I want to get it), I start
by looking where I s
On Wed, 2006-10-04 at 18:02, Csaba Nagy wrote:
> > If we didn't want to add it for each list we could just add a link here:
> >
> > http://www.postgresql.org/community/lists/subscribe
OK, now that I had a second look on that page, it does contain
unsubscription info... b
OK, I just have to comment...
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > These hints would outright force the planner to do things a certain way.
> > ... FROM table /* ACCESS {SEQSCAN | [[NO] BITMAP] INDEX index_name} */
>
> This proposal seems to deliberately ignore every point that has been
> Hmmm, if you already understand Visual Basic syntax, should we support
> that too? Or maybe we should support MySQL's use of '-00-00' as the
> "zero" date because people "understand" that?
You completely misunderstood me... I have no idea about oracle hints,
never used Oracle in fact. My
> I'm not suggesting that we do that, but it seems better then embedding
> the hints in the queries themselves.
OK, what about this: if I execute the same query from a web client, I
want the not-so-optimal-but-safe plan, if I execute it asynchronously, I
let the planner choose the
best-overall-per
> 2d) Hints will damage the ongoing development of the optimizer by
> reducing or eliminating test cases for its improvement.
You have no evidence for this. The mindset of the postgres community you
cite further below usually mandates that you say things if you have
evidence for them... and this
On Thu, 2006-11-09 at 13:35, Hilary Forbes wrote:
> [snip] Is there a way that we can achieve this in Postgres? We have a
> situation whereby we have lots of web based users doing short quick
> queries and obviously the start up time for a process must add to
> their perceived response time.
Yes:
On Fri, 2007-11-16 at 12:13 +0100, Tobias Brox wrote:
> [snip] should i use both auto-vacuum and
> > manual-vacuum?
I would say for 8.2 that's the best strategy (which might change with
8.3 and it's multiple vacuum workers thingy).
> That being said, we have some huge tables in our database and
On Wed, 2007-11-28 at 08:27 -0500, Bill Moran wrote:
> Is there something wrong with:
> set enable_seqscan = off
> ?
Nothing wrong with enable_seqscan = off except it is all or nothing type
of thing... if you want the big table to never use seqscan, but a medium
table which is joined in should use
On Wed, 2007-11-28 at 08:54 -0500, Bill Moran wrote:
> > Nothing wrong with enable_seqscan = off except it is all or nothing type
> > of thing...
>
> If that's true, then I have a bug report to file:
[snip]
> It looks to me to be session-alterable.
I didn't mean that it can't be set per session,
On Thu, 2007-11-29 at 10:45 -0500, Tom Lane wrote:
> Given that this list spends all day every day discussing cases where the
> planner is wrong, I'd have to think that that's a bet I wouldn't take.
>
> You could probably avoid this risk by setting the cutoff at something
> like 100 or 1000 times
> Isn't that what statement_timeout is for? Since this is entirely based
> on estimates, using arbitrary fuzzy numbers for this seems fine to me;
> precision isn't really the goal.
There's an important difference to statement_timeout: this proposal
would avoid completely taking any resources if it
Hi Jean-David,
On Mon, 2009-10-05 at 15:37 +0200, Jean-David Beyer wrote:
> Robert Haas wrote (in part):
>
> > Also, I'd just like to mention that vi is a much better editor than
> > emacs.
> >
> That is not my impression. I have used vi from when it first came out (I
> used ed before that) unti
67 matches
Mail list logo
