Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Also if autovacuum is eating all your I/O you may want to look into
> > throttling it back a bit by setting autovacuum_vacuum_cost_delay to a
> > non-zero value.
>
> BTW, why is it that autovacuum_cost_delay isn't enabled by default?
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Also if autovacuum is eating all your I/O you may want to look into
> throttling it back a bit by setting autovacuum_vacuum_cost_delay to a
> non-zero value.
BTW, why is it that autovacuum_cost_delay isn't enabled by default?
I can hardly believe that a
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane escribió:
>> I was hoping that the auto plan invalidation code in CVS HEAD would get
>> it out of this problem, but it seems not to for the problem-as-given.
>> The trouble is that it won't change plans until autovacuum analyzes the
>> tables, a
Tom Lane escribió:
> I was hoping that the auto plan invalidation code in CVS HEAD would get
> it out of this problem, but it seems not to for the problem-as-given.
> The trouble is that it won't change plans until autovacuum analyzes the
> tables, and that won't happen until the transaction commi
"Steven Flatt" <[EMAIL PROTECTED]> writes:
> One instance of our problem goes like this, and I have included a
> self-contained example with which you can reproduce the problem.
This is fairly interesting, because if you run the query by hand after
the function finishes, it's pretty fast. What I
Mark Kirkwood wrote:
Kurt Overberg wrote:
work_mem = 100MB# when I ran the original
query, this was set to 1MB, increased on Mark Kirkwood's advice,
seemed to help a bit but not really
For future reference, be careful with this parameter, as *every*
connection will
Kurt Overberg wrote:
work_mem = 100MB# when I ran the original query,
this was set to 1MB, increased on Mark Kirkwood's advice, seemed to help
a bit but not really
For future reference, be careful with this parameter, as *every*
connection will use this much memory f
Tyrrill, Ed wrote:
I have a table, let's call it A, whose primary key, a_id, is referenced
in a second table, let's call it B. For each unique A.a_id there are
generally many rows in B with the same a_id. My problem is that I want
to delete a row in A when the last row in B that references it
Hey All,
I have a table, let's call it A, whose primary key, a_id, is referenced
in a second table, let's call it B. For each unique A.a_id there are
generally many rows in B with the same a_id. My problem is that I want
to delete a row in A when the last row in B that references it is
deleted.
On Thu, 7 Jun 2007, Gunther Mayer wrote:
wal checkpoint config is on pg defaults everywhere, all relevant config
options are commented out. I'm no expert in wal stuff but I don't see how
that could cause the problem?
Checkpoints are very resource intensive and can cause other processes
(incl
On Thu, 7 Jun 2007, Joe Lester wrote:
Memory: 4GB RAM
shared_buffers = 1
work_mem = 2048
effective_cache_size = 3
With these parameters, your server has 80MB dedicated to its internal
caching, is making query decisions assuming the operating system only has
240MB of memory available
Joe Lester wrote:
> max_fsm_pages = 15
This may be a bit too low -- it's just a little more than 1 GB, which
means it might fail to keep track of all your tables (or it may not, if
you don't have many updates).
> autovacuum_naptime = 60
> autovacuum_vacuum_threshold = 150
> autovacuum_vacuum
On Thu, Jun 07, 2007 at 01:48:43PM -0400, Joe Lester wrote:
> - The server log shows frequent "archived transaction log file"
> entries. Usually once every 10 minutes or so, but sometimes 2 or 3
> per minute.
Sounds like you've got a lot of writes going. You might want more power in
your I/O?
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 spi
About six months ago, our normally fast postgres server started
having performance issues. Queries that should have been instant were
taking up to 20 seconds to complete (like selects on the primary key
of a table). Running the same query 4 times in a row would yield
dramatically different
On 6/5/07, Tom Lane <[EMAIL PROTECTED]> wrote:
If you're feeling desperate you could revert this patch in your local
copy:
http://archives.postgresql.org/pgsql-committers/2006-11/msg00066.php
regards, tom lane
Reverting that patch has not appeared to solve our problem.
On a FreeBSD system, is page size for shared_buffers calculation 8K? And is
page size for shmall calculation 4K? The documentation hints at these
values. Anyone know?
--
Yudhvir Singh Sidhu
408 375 3134 cell
Gunther Mayer wrote:
> On another note, autovacuum couldn't cause such issues, could it? I do
> have autovacuum enabled (autovacuum=on as well as
> stats_start_collector=on, stats_block_level = on and stats_row_level =
> on), is there any possibility that autovacuum is not as resource
> friend
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 ca
Kristo Kaiv wrote:
could be that the checkpoints are done too seldom.
what is your wal checkpoint config?
wal checkpoint config is on pg defaults everywhere, all relevant config
options are commented out. I'm no expert in wal stuff but I don't see
how that could cause the problem?
Gunther
-
Scott Marlowe wrote:
Gunther Mayer wrote:
Hi there,
We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend
and 200+ users. Authentication happens via UAM/hotspot and I see a lot
of authorisation and accounting packets that are handled via PL/PGSQL
functions directly in the databa
Andrew Sullivan wrote:
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
thi
Le jeudi 07 juin 2007, Kurt Overberg a écrit :
> Is there a primer somewhere on how to read EXPLAIN output?
Those Robert Treat slides are a great reading:
http://www.postgresql.org/communityfiles/13.sxi
Regards,
--
dim
---(end of broadcast)---
T
Kurt Overberg <[EMAIL PROTECTED]> writes:
> ... Turning off bitmap scans definitely seems
> to help things,
I really seriously doubt that. On queries like this, where each inner
scan is fetching a couple hundred rows, the small extra overhead of a
bitmap scan should easily pay for itself. I th
On Thu, Jun 07, 2007 at 07:18:22AM -0400, Kurt Overberg wrote:
> - My production environment is running RedHat 2.6.9.ELsmp on a server
> with 16GB of memory
Seriously, this (the RAM amount) _is_ all the difference. (You don't say how
much RAM is in your Mac, but something tells me it's not 16GB.
Thank you everyone for the replies. I'll try to answer everyone's
questions in one post.
* Regarding production/mac memory and cache usage. This query HAS
been running on 8.0 on my Mac, I just got that particular query
explain from our production system because I had to nuke my local 8.0
Steinar H. Gunderson wrote:
On Thu, Jun 07, 2007 at 11:35:27AM +0200, Steinar H. Gunderson wrote:
If that table
doesn't fit in the cache on your Mac, you pretty much lose. From the EXPLAIN
output, it looks like it fits very nicely in cache on your server. Thus, I
don't think the difference is be
On Thu, Jun 07, 2007 at 11:35:27AM +0200, Steinar H. Gunderson wrote:
> How big did you say these tables were?
Sorry, you already said that -- 650k rows for one of them. If that table
doesn't fit in the cache on your Mac, you pretty much lose. From the EXPLAIN
output, it looks like it fits very ni
Hello,
Postgres: 8.2
os: Linux 4CPU, 4 GB RAM, Raid 1, 32 bit system
work_mem: 600 Mb
I have some tables which may become quite large (currently up to 6 Gb) .
I initially fill them using copy from (files) .
The import is fast enough as I only have a primary key on the table:
about 18 minutes
On Wed, Jun 06, 2007 at 07:27:27PM -0400, Kurt Overberg wrote:
> This query runs great on production under 8.0 (27ms), but under 8.2.4
> (on my mac) I'm seeing times in excess of 50,000ms. Note that on
> 8.2.4, if I run the query again, it gets successively faster
> (50,000ms->6000ms->27ms).
Mark Kirkwood wrote:
8.2 is deciding to use a bitmap index scan on taskid_taskinstance_key,
which seems to be slower (!) than a plain old index scan that 8.0 is
using. A dirty work around is to disable bitmap scans via:
I'm having difficulty figuring out why it's doing this at all. There's
could be that the checkpoints are done too seldom.
what is your wal checkpoint config?
Kristo
On 07.06.2007, at 0:27, Scott Marlowe wrote:
Gunther Mayer wrote:
Hi there,
We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend
and 200+ users. Authentication happens via UAM/hotspo
32 matches
Mail list logo
