weisong44@gmail.com | 1 Sep 23:56 2006
Picon

Hanging queries on Windows 2003 SP1

Hi,

We are seeing hanging queries on Windows 2003 Server SP1 with dual CPU,
looks like one of the process is blocked. In a lot of cases, the whole
DB is blocked if this process is holding important locks.

Looks like this issue was discussed in the following thread a few month
ago, but didn't seem to have a solution mention. I would liek to know
if there is a patch for this already?

http://archives.postgresql.org/pgsql-performance/2006-03/msg00129.php

I would appreciate your feedbaek,

Thanks,
Wei

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly

Magnus Hagander | 4 Sep 13:39 2006
Picon

Re: Hanging queries on Windows 2003 SP1

> Hi,
> 
> We are seeing hanging queries on Windows 2003 Server SP1 with dual
> CPU, looks like one of the process is blocked. In a lot of cases,
> the whole DB is blocked if this process is holding important locks.
> 
> Looks like this issue was discussed in the following thread a few
> month ago, but didn't seem to have a solution mention. I would liek
> to know if there is a patch for this already?
> 
> http://archives.postgresql.org/pgsql-performance/2006-
> 03/msg00129.php
> 

There have been some fairly extensive changes in the semaphore code for
8.2. Any chance you can try the cvs snapshot version and see if the
problem exists there as well?

//Magnus

---------------------------(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

Nimesh Satam | 7 Sep 12:31 2006
Picon

Template0 age is increasing speedily.

Hi!..
 
I noticed that the age of  template0 is increasing very rapidly..Can you please let me know how we can control this ....and what causes such problems.
 
We also noticed that the database slow downs heavily at a particular time..Can you suggest any tools which will help in diagnosing the root cause behiond the data load.
 
 
 
Regards,
Nimesh.
Merlin Moncure | 7 Sep 15:04 2006
Picon

Re: [HACKERS] Template0 age is increasing speedily.

On 9/7/06, Nimesh Satam <nimesh.zedo <at> gmail.com> wrote:
> We also noticed that the database slow downs heavily at a particular
> time..Can you suggest any tools which will help in diagnosing the root cause
> behiond the data load.

possible checkpoint?  poorly formulated query?  it could be any number
of things.  use standard tools to diagnose the problem, including:

unix tools: top, vmstat, etc
postgresql query logging, including min_statement_duration
explain analyze

---------------------------(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

Wei Song | 5 Sep 08:31 2006
Picon

Re: Hanging queries on Windows 2003 SP1

Hi Magnus,

Sure, I could try that out. Is there a place to download a 8.2 image to bypass setting up the tool chain?

-Wei


On 9/4/06, Magnus Hagander <mha <at> sollentuna.net> wrote:
> Hi,
>
> We are seeing hanging queries on Windows 2003 Server SP1 with dual
> CPU, looks like one of the process is blocked. In a lot of cases,
> the whole DB is blocked if this process is holding important locks.
>
> Looks like this issue was discussed in the following thread a few
> month ago, but didn't seem to have a solution mention. I would liek
> to know if there is a patch for this already?
>
> http://archives.postgresql.org/pgsql-performance/2006-
> 03/msg00129.php
>

There have been some fairly extensive changes in the semaphore code for
8.2. Any chance you can try the cvs snapshot version and see if the
problem exists there as well?

//Magnus

Edoardo Ceccarelli | 27 Sep 18:08 2006
Picon

autovacuum on a -mostly- r/o table

Hello,

we are running a 7.3 postgres db with only a big table (avg 
500.000records) and 7 indexes for a search engine.
we have 2 of this databases and we can switch from one to another.
Last week we decided to give a try to 8.1 on one of them and everything 
went fine, db is faster (about 2 or 3 times in our case) and the server 
load is higher - which should mean that faster response time is achieved 
by taking a better use of the server.

We also activated the autovacuum feature to give it a try and that's 
were our problems started.
I left the standard autovacuum configuration just to wait and see, pg 
decided to start a vacuum on the table just midday when users were 
launching search queries on the table and server load reached a very 
high value so that in a couple of minutes the db was unusable

With pg7.3 we use to vacuum the db night time, mostly because the insert 
and updates in this table is made in a batch way: a single task that 
puts 100.000 records in the db in 10/20minutes, so the best time to 
actually vacuum the db would be after this batch.

I have read that autovacuum cannot check to see pg load before launching 
vacuum but is there any patch about it? that would sort out the problem 
in a good and simple way.
Otherwise, which kind of set of parameters I should put in autovacuum 
configuration? I am stuck because in our case the table gets mostly read 
and if I set up things as to vacuum the table after a specific amount of 
insert/updates, I cannot foresee whether this could happen during 
daytime when server is under high load.
How can I configure the vacuum to run after the daily batch insert/update?

Any help appreciated
Thank you very much
Edoardo

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tobias Brox | 27 Sep 18:13 2006

Re: [PERFORM] autovacuum on a -mostly- r/o table

[Edoardo Ceccarelli - Wed at 06:08:30PM +0200]
> We also activated the autovacuum feature to give it a try and that's 
> were our problems started.
(...)
> How can I configure the vacuum to run after the daily batch insert/update?

I think you shouldn't use autovacuum in your case.

We haven't dared testing out autovacuum yet even though we probably
should, so we're running vacuum at fixed times of the day.  We have a
very simple script to do this, the most important part of it reads:

echo "vacuum verbose analyze;" | psql $DB_NAME > $logdir/$filename 2>&1

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Csaba Nagy | 27 Sep 18:13 2006

Re: [PERFORM] autovacuum on a -mostly- r/o table

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, and then you can schedule vacuum nightly for those just as
before. There's still a benefit in that you don't need to care about
vacuuming the rest of the tables, which will be done just in time.

Cheers,
Csaba.

---------------------------(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

Bill Moran | 27 Sep 18:14 2006

Re: [PERFORM] autovacuum on a -mostly- r/o table

In response to Edoardo Ceccarelli <eddy <at> axa.it>:

> Hello,
> 
> we are running a 7.3 postgres db with only a big table (avg 
> 500.000records) and 7 indexes for a search engine.
> we have 2 of this databases and we can switch from one to another.
> Last week we decided to give a try to 8.1 on one of them and everything 
> went fine, db is faster (about 2 or 3 times in our case) and the server 
> load is higher - which should mean that faster response time is achieved 
> by taking a better use of the server.
> 
> We also activated the autovacuum feature to give it a try and that's 
> were our problems started.
> I left the standard autovacuum configuration just to wait and see, pg 
> decided to start a vacuum on the table just midday when users were 
> launching search queries on the table and server load reached a very 
> high value so that in a couple of minutes the db was unusable
> 
> With pg7.3 we use to vacuum the db night time, mostly because the insert 
> and updates in this table is made in a batch way: a single task that 
> puts 100.000 records in the db in 10/20minutes, so the best time to 
> actually vacuum the db would be after this batch.
> 
> I have read that autovacuum cannot check to see pg load before launching 
> vacuum but is there any patch about it? that would sort out the problem 
> in a good and simple way.
> Otherwise, which kind of set of parameters I should put in autovacuum 
> configuration? I am stuck because in our case the table gets mostly read 
> and if I set up things as to vacuum the table after a specific amount of 
> insert/updates, I cannot foresee whether this could happen during 
> daytime when server is under high load.
> How can I configure the vacuum to run after the daily batch insert/update?

It doesn't sound as if your setup is a good match for autovacuum.  You
might be better off going back to the cron vacuums.  That's the
beauty of Postgres -- it gives you the choice.

If you want to continue with autovac, you may want to experiment with
vacuum_cost_delay and associated parameters, which can lessen the
impact of vacuuming.

--

-- 
Bill Moran
Collaborative Fusion Inc.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly

Rod Taylor | 27 Sep 18:31 2006
Picon

Re: [PERFORM] autovacuum on a -mostly- r/o table

On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote:
> 
> I have read that autovacuum cannot check to see pg load before
> launching 
> vacuum but is there any patch about it? that would sort out the
> problem 
> in a good and simple way. 

In some cases the solution to high load is to vacuum the tables being
hit the heaviest -- meaning that simply checking machine load isn't
enough to make that decision.

In fact, that high load problem is exactly why autovacuum was created in
the first place.
--

-- 

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


Gmane