Re: [PERFORM] 12 hour table vacuums

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Ron St-Pierre <[EMAIL PROTECTED]> writes:

>> For what it's worth NUMERIC columns take more space than you might expect.
>> Figure a minimum of 12 bytes your rows are at about 1.5k each even if the
>> non-numeric columns aren't large themselves. What are the other columns?

> The NUMERIC columns hold currency related values, with values ranging
> from a few cents to the billions, as well as a few negative numbers.

What's the required precision? If it's just cents (or maybe tenths
thereof), you could use BIGINT to store the amount in this precision.
This would give you exact values with much less space.


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

[PERFORM] Finalizing commit taking very long

[Giulio Cesare Solaroli]

Hello,

I am having a strange latency problem on my instance of Postgres that
I don't know how to investigate.

I am accessing the db instance using a Java application and the
Cayenne mapping framework. Everything works fine, except when it is
time to delete a user account (that is a user of the application, not
of Postgres).

Deleting an account trigger a (sort-of) cascade delete to remove also
all the dependent records stored on the db. The cascade constraints
are managed by the Cayenne library, and the db receives a list of
delete statements for all the rows of the different tables that should
be deleted; this is probably not optimal from a db point of view, but
is extremely convenient from an application point of view.

Anyway, the deletion of all the records is executed without much
problems (I had a huge slowdown here a few weeks ago, but I was
missing an index on a constraint).

Obviously, all the delete statements are grouped into a single
transaction; and when it is time to commit this transaction, the db
instance takes "forever".

Here are some logs taken from the db server:

[ lot of delete statements skipped]
2007-10-24 12:13:17 CEST LOG:  statement: EXECUTE   [PREPARE:
 DELETE FROM connection.USRCNN WHERE ID_USRCNN = $1]
2007-10-24 12:13:17 CEST LOG:  duration: 0.206 ms
2007-10-24 12:13:17 CEST LOG:  duration: 0.206 ms  statement: EXECUTE
  [PREPARE:  DELETE FROM connection.USRCNN WHERE ID_USRCNN =
$1]
2007-10-24 12:13:17 CEST LOG:  statement: PREPARE  AS DELETE
FROM clipperz.USR WHERE ID_USR = $1
2007-10-24 12:13:17 CEST LOG:  statement: 
2007-10-24 12:13:17 CEST LOG:  statement: EXECUTE   [PREPARE:
 DELETE FROM clipperz.USR WHERE ID_USR = $1]
2007-10-24 12:13:17 CEST LOG:  duration: 0.761 ms
2007-10-24 12:13:17 CEST LOG:  duration: 0.761 ms  statement: EXECUTE
  [PREPARE:  DELETE FROM clipperz.USR WHERE ID_USR = $1]
2007-10-24 12:13:17 CEST LOG:  statement: 
2007-10-24 12:13:17 CEST LOG:  statement: EXECUTE   [PREPARE:  COMMIT]
2007-10-24 12:13:51 CEST LOG:  autovacuum: processing database "clipperz_beta"
2007-10-24 12:14:51 CEST LOG:  autovacuum: processing database "clipperz_beta"
2007-10-24 12:15:10 CEST LOG:  duration: 113300.147 ms
2007-10-24 12:15:10 CEST LOG:  duration: 113300.147 ms  statement:
EXECUTE   [PREPARE:  COMMIT]


As you may notice, the commit phase takes almost 2 full minutes. :-(

How can I understand what is going on on that timeframe in order to
try to fix it?

Thanks for your attention.

Best regards,

Giulio Cesare

PS: I run my development machine on a MacOS, with Postgres running on
a Parallels virtual machine. I don't think this really matters for the
above problem, but in case ...

---(end of broadcast)---
TIP 6: explain analyze is your friend

[PERFORM] multiple apaches against single postgres database

[Honza Novak]

Hi all,
i'm looking for correct or at least good enough solution for use of 
multiple apaches with single postgres database. (apaches are 2.0.x, and 
postgres is 8.1.x)


At this moment i'm involved in management of a website where we have 
large user load on our web servers.  Apaches are set up to be able to 
answer 300 requests at the same time and at the moment we have 4 
apaches.  Eaxh of these apaches handles about 100 requests 
simultaneously at average.We have no connection pooling setup between 
apaches and postgresql. Postgres accepts up to 200 connections and 
normaly there is about 20 used connections (although, there is quite a 
lot of traffic between postgres and apaches, queries are simple enough, 
so postgres handles it nicely)


But sometimes (i don't know exactly for what reason) some queries gets 
stuck (mostly they are inserts or updates, but realy simple) and 
postgres is unable to answer in time,  which starts a "wave" because 
queries from apaches are delayed, which means that there is bigger 
number of user request in process, which means more connections to 
postgres, until we reach connection limit. But there is something even 
worse and that is, that i don't know why postmaster process probably 
forks itself ending with lots of (some hunreds) of postmasters running. 
When we kill all these postmasters and start postgres again, it ends the 
same because apaches probably overloads database server with their 
waiting requests. In this case we first need to stop apaches, start 
postgres, and then apaches and everything works fine .. until next 
problem, which can occur in hours, days or weeks.


And my questions:
1. Does someone hes similar experience? or clue what to do with it?

2. What is correct setup of postgresql backend serving data for many 
(4+) apaches? i know that there are connection pooling solutions 
(pgPool, pgBouncer, or apache 2.2) and i'm thinking about them, but it 
seems that we have other problem beside that we didn't implement any 
pooling solution yet.


3. is there a way to somehow log what happened to the postgres server 
before accident? do you think that logging of all sql statements would 
help me? if i enable it, what will be the performance overhead?


I might be asking too much, but i appreciate any help, hint, or 
direction what to explore.


Thanks, i'm looking forward for answers.

Honza


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PERFORM] Finalizing commit taking very long

[Tom Lane]

"Giulio Cesare Solaroli" <[EMAIL PROTECTED]> writes:
> As you may notice, the commit phase takes almost 2 full minutes. :-(

Yow.  It's hard to believe that the actual commit (ie, flushing the
commit record to WAL) could take more than a fraction of a second.
I'm thinking there must be a pile of pre-commit work to do, like a
lot of deferred triggers.  Do you use deferred foreign keys?
If so, the most likely bet is that the DELETE is triggering a lot
of deferred FK checks, and these are slow for some reason (maybe
another missing index).

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] multiple apaches against single postgres database

[Michal Taborsky - Internet Mall]

Honza Novak napsal(a):

And my questions:
1. Does someone hes similar experience? or clue what to do with it?


Sure, this is considered "normal" behavior for web applications. The 
solution is to use connection pooling.


2. What is correct setup of postgresql backend serving data for many 
(4+) apaches? i know that there are connection pooling solutions 
(pgPool, pgBouncer, or apache 2.2) and i'm thinking about them, but it 
seems that we have other problem beside that we didn't implement any 
pooling solution yet.


We use pgpool running on each web server. You can have also the pgpool 
running on the database server or even a separate server just for that. 
You'll have to test to see what's best for you.


3. is there a way to somehow log what happened to the postgres server 
before accident? do you think that logging of all sql statements would 
help me? if i enable it, what will be the performance overhead?


What you are seeing is called "positive feedback". Once the server 
reaches a certain performance threshold, it starts to delay the queries, 
which causes more load, which causes further delay, until everything 
comes to a halt. Sometimes the system can recover from this, if you have 
properly setup limits (it will just refuse the requests until it can 
cool off), sometimes it doesn't. The point is never get over the threshold.


Also, maybe you need better hardware for that kind of load, but since 
you didn't provide more detail, we can't tell you.


It's quite meaningless to analyze performance once the system is 
overloaded. You have to analyze before that happens and identify the 
longest running queries under normal load and try to optimize them. 
Under heavy load, even the simplest query may seem to be taking long 
time, but it doesn't necessarily mean there is something wrong with it.


--
Michal Táborský
chief systems architect
Internet Mall, a.s.


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

Re: [PERFORM] multiple apaches against single postgres database

[Gregory Stark]

"Honza Novak" <[EMAIL PROTECTED]> writes:

> Hi all,
> i'm looking for correct or at least good enough solution for use of multiple
> apaches with single postgres database. (apaches are 2.0.x, and postgres is
> 8.1.x)
>
> At this moment i'm involved in management of a website where we have large 
> user
> load on our web servers.  Apaches are set up to be able to answer 300 requests
> at the same time and at the moment we have 4 apaches.  

Do you have 300 processors? Are your requests particularly i/o-bound? Why
would running 300 processes simultaneously be faster than running a smaller
number sequentially? It doesn't sound like your systems are capable of
handling such a large number of requests simultaneously.

The traditional answer is to separate static content such as images which are
more i/o-bound onto a separate apache configuration which has a larger number
of connections, limit the number of connections for the cpu-bound dynamic
content server, and have a 1-1 ratio between apache dynamic content
connections and postgres backends. The alternative is to use connection
pooling. Often a combination of the two is best.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com

---(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] 12 hour table vacuums

[Jean-David Beyer]

Ron St-Pierre wrote:
> We vacuum only a few of our tables nightly, this one is the last one
> because it takes longer to run. I'll probably re-index it soon, but I
> would appreciate any advice on how to speed up the vacuum process (and
> the db in general).

I am a novice to postgreSQL, so I have no answers for you. But for my own
education, I am confused by some of your post.
> 
> Okay, here's our system:
>   postgres 8.1.4

I have postgresql-8.1.9-1.el5

>   Linux version 2.4.21

I imagine you mean Linux kernel version; I have 2.6.18-8.1.15.el5PAE

>   Red Hat Linux 3.2.3

I have no clue what this means. Red Hat Linux 3 must have been in the early
1990s. RHL 5 came out about 1998 IIRC.

Red Hat Enterprise Linux 3, on the other hand, was not numbered like that,
as I recall. I no longer run that, but my current RHEL5 is named like this:

Red Hat Enterprise Linux Server release 5 (Tikanga)

and for my CentOS 4 system, it is

CentOS release 4.5 (Final)

Did RHEL3 go with the second dot in their release numbers? I do not remember
that.

>   8 GB ram
>   Intel(R) Xeon(TM) CPU 3.20GHz
>   Raid 5
>   autovacuum=off

Why would you not have that on?

>   serves as the application server and database server
>   server is co-located in another city, hardware upgrade is not
> currently an option
> 
> Here's the table information:
> The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 indexes.

I have designed databases, infrequently, but since the late 1970s. In my
experience, my tables had relatively few columns, rarely over 10. Are you
sure this table needs so many? Why not, e.g., 13 tables averaging 10 columns
each?

OTOH, 140,000 rows is not all that many. I have a 6,000,000 row table in my
little database on my desktop, and I do not even consider that large.
Imagine the size of a database belonging to the IRS, for example. Surely it
would have at least one row for each taxpayer and each employer (possibly in
two tables, or two databases).

Here are the last few lines of a VACUUM VERBOSE; command for that little
database. The 6,000,000 row table is not in the database at the moment, nor
are some of the other tables, but two relatively (for me) large tables are.

CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  free space map contains 166 pages in 76 relations
DETAIL:  A total of 1280 page slots are in use (including overhead).
1280 page slots are required to track all free space.
Current limits are:  4 page slots, 1000 relations, using 299 KB.
VACUUM
stock=> select count(*) from ranks; [table has 10 columns]
 count

 981030
(1 row)

stock=> select count(*) from ibd;   [table has 8 columns]
  count
-
 1099789
(1 row)

And this is the time for running that psql process, most of which was
consumed by slow typing on my part.

real1m40.206s
user0m0.027s
sys 0m0.019s

My non-default settings for this are

# - Memory -

shared_buffers = 251000
work_mem  = 32768
max_fsm_pages  = 4

I have 8GBytes RAM on this machine, and postgreSQL is the biggest memory
user. I set shared_buffers high to try to get some entire (small) tables in
RAM and to be sure there is room for indices.

-- 
  .~.  Jean-David Beyer  Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A Registered Machine   241939.
 /( )\ Shrewsbury, New Jerseyhttp://counter.li.org
 ^^-^^ 08:40:01 up 1 day, 58 min, 1 user, load average: 4.08, 4.13, 4.17

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Finalizing commit taking very long

[Giulio Cesare Solaroli]

Hello Tom,

On 10/24/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Giulio Cesare Solaroli" <[EMAIL PROTECTED]> writes:
> > As you may notice, the commit phase takes almost 2 full minutes. :-(
>
> Yow.  It's hard to believe that the actual commit (ie, flushing the
> commit record to WAL) could take more than a fraction of a second.
> I'm thinking there must be a pile of pre-commit work to do, like a
> lot of deferred triggers.  Do you use deferred foreign keys?
> If so, the most likely bet is that the DELETE is triggering a lot
> of deferred FK checks, and these are slow for some reason (maybe
> another missing index).

I have most (if not all) of my constraint defined with the DEFERRABLE
INITIALLY DEFERRED clause.

I have done this as I have not a direct control on the order of the
SQL statements that the Cayenne library sends to the server, and this
will avoid all the constraint violations inside a single transaction.

How can I try to isolate the trigger taking so long, in oder to
understand which is/are the missing index(es)?

Best regards,

Giulio Cesare

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [PERFORM] Finalizing commit taking very long

[Tom Lane]

"Giulio Cesare Solaroli" <[EMAIL PROTECTED]> writes:
> How can I try to isolate the trigger taking so long, in oder to
> understand which is/are the missing index(es)?

Try SET CONSTRAINTS ALL IMMEDIATE and then EXPLAIN ANALYZE the
delete.  This should cause all the triggers to run within the
scope of the EXPLAIN ANALYZE, and you'll be able to see which
one(s) are slow.  (This assumes you're running a recent release
of PG; I think EXPLAIN shows trigger times since 8.1 or so.)

regards, tom lane

---(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] Finalizing commit taking very long

[Giulio Cesare Solaroli]

On 10/24/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Giulio Cesare Solaroli" <[EMAIL PROTECTED]> writes:
> > How can I try to isolate the trigger taking so long, in oder to
> > understand which is/are the missing index(es)?
>
> Try SET CONSTRAINTS ALL IMMEDIATE and then EXPLAIN ANALYZE the
> delete.  This should cause all the triggers to run within the
> scope of the EXPLAIN ANALYZE, and you'll be able to see which
> one(s) are slow.  (This assumes you're running a recent release
> of PG; I think EXPLAIN shows trigger times since 8.1 or so.)

I was thinking about something similar after writing the last message.

Thank you very much for your attention!!

Giulio Cesare

---(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] multiple apaches against single postgres database

[Sven Geisler]

Hi Honza,

as Gregory wrote, let apache do the job.
The apache does queue a request if all running workers are busy.

1. Split static content.
We have an apache as frontend which serves all static content and
forwards (reverse-proxy) dynamic content to the "backends"

2. Split different types of dynamic content.
We have an apache for all interactive requests - where the user expects
quick responses. We have another apache for non-interactive content such
as downloads and uploads. Whose request, which doesn't do much cpu work
at all (and don't fit to 1.).

3. Limit each apache to achieve a good work load
It is to late if the operation systems tries to schedule the simultan
work load because you have more processes in ready state than free CPUs.
Set MaxClients of the apache for interactive requests that your
server(s) doesn't get overloaded.
You can set MaxClients just to limit the parallel downloads/uploads.
Set MaxClients of the frontend higher. A good settings is when the
interactive requests are queued at the backend apache without reaching
the limit of the request queue.

4. Set max_connection that you don't reach this limit.
Maximum number of connection from interactive backend + maximum number
of connections from non-interactive backend + reserve for the database
admin.

5. Check all limits that you never reach a memory limit and you box
starts to swap.

6. Monitor you application well
- Count number of open connections to each apache
- Check the load of the server.
- Check context switches on the PostgreSQL box.

I understand an apache process group as one apache.

Here is a example from our web application

Two frontends - each MaxClients = 1024.
Interactive backend - MaxClients = 35.
non-Interactive backend - MaxClients = 65.
max_connections = 120 (assuming each backend child process has one
connections)
With this setting we have even under load normally not more queries
running at the PostgreSQL server as cores are available.

Please note that example should give you only experience for the scale.
We need a long time to find this values for our environment (application
and hardware).

BTW: This can also be setup on a single box. We have customers where
different apache are running on the same server.

There are a number of papers in the web which describe such setups.
Checkout  for
example.

Sven.

Gregory Stark schrieb:
> "Honza Novak" <[EMAIL PROTECTED]> writes:
> 
>> Hi all,
>> i'm looking for correct or at least good enough solution for use of multiple
>> apaches with single postgres database. (apaches are 2.0.x, and postgres is
>> 8.1.x)
>>
>> At this moment i'm involved in management of a website where we have large 
>> user
>> load on our web servers.  Apaches are set up to be able to answer 300 
>> requests
>> at the same time and at the moment we have 4 apaches.  
> 
> Do you have 300 processors? Are your requests particularly i/o-bound? Why
> would running 300 processes simultaneously be faster than running a smaller
> number sequentially? It doesn't sound like your systems are capable of
> handling such a large number of requests simultaneously.
> 
> The traditional answer is to separate static content such as images which are
> more i/o-bound onto a separate apache configuration which has a larger number
> of connections, limit the number of connections for the cpu-bound dynamic
> content server, and have a 1-1 ratio between apache dynamic content
> connections and postgres backends. The alternative is to use connection
> pooling. Often a combination of the two is best.
> 

-- 
Sven Geisler <[EMAIL PROTECTED]>   Tel +49.30.921017.81  Fax .50
Senior Developer, AEC/communications GmbH & Co. KG Berlin, Germany

---(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] multiple apaches against single postgres database

[Kevin Grittner]

>>> On Wed, Oct 24, 2007 at  7:15 AM, in message
<[EMAIL PROTECTED]>, Honza Novak
<[EMAIL PROTECTED]> wrote: 
 
> But sometimes (i don't know exactly for what reason) some queries gets 
> stuck (mostly they are inserts or updates, but realy simple) and 
> postgres is unable to answer in time
 
In addition to the points made by others, there is a chance that a
contributing factor is the tendency of PostgreSQL (prior to the
upcoming 8.3 release) to hold onto dirty pages for as long as
possible and throw them all at the disk drives in at checkpoint
time.  In some such cases the advice from previous emails may not
be enough -- you may have to use very aggressive background writer
settings, a smaller shared buffers setting, and/or reduce or
eliminate the OS write delays.
 
If you find this to be your problem, you may want to be an early
adopter of the 8.3 release, once it is out.
 
-Kevin
 



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] multiple apaches against single postgres database

[Tatsuo Ishii]

Sorry for an off topic posting...

Michal,

> Honza Novak napsal(a):
> > And my questions:
> > 1. Does someone hes similar experience? or clue what to do with it?
> 
> Sure, this is considered "normal" behavior for web applications. The 
> solution is to use connection pooling.
> 
> > 2. What is correct setup of postgresql backend serving data for many 
> > (4+) apaches? i know that there are connection pooling solutions 
> > (pgPool, pgBouncer, or apache 2.2) and i'm thinking about them, but it 
> > seems that we have other problem beside that we didn't implement any 
> > pooling solution yet.
> 
> We use pgpool running on each web server. You can have also the pgpool 
> running on the database server or even a separate server just for that. 
> You'll have to test to see what's best for you.

As a member of pgpool development team, I am always looking for pgpool
examples in the real world which could be open to public. Can you
plese tell me more details the pgpool usage if possible?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> > 3. is there a way to somehow log what happened to the postgres server 
> > before accident? do you think that logging of all sql statements would 
> > help me? if i enable it, what will be the performance overhead?
> 
> What you are seeing is called "positive feedback". Once the server 
> reaches a certain performance threshold, it starts to delay the queries, 
> which causes more load, which causes further delay, until everything 
> comes to a halt. Sometimes the system can recover from this, if you have 
> properly setup limits (it will just refuse the requests until it can 
> cool off), sometimes it doesn't. The point is never get over the threshold.
> 
> Also, maybe you need better hardware for that kind of load, but since 
> you didn't provide more detail, we can't tell you.
> 
> It's quite meaningless to analyze performance once the system is 
> overloaded. You have to analyze before that happens and identify the 
> longest running queries under normal load and try to optimize them. 
> Under heavy load, even the simplest query may seem to be taking long 
> time, but it doesn't necessarily mean there is something wrong with it.
> 
> -- 
> Michal Táborský
> chief systems architect
> Internet Mall, a.s.
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq