I promised that I will get back to the group with the reason. Well, of course was a query :). I do use a search engine file system based(lucene) that will take any desired entity saved into the database and find the primary keys and then do a
select * from entity where id is in (:ids)
If I get too many matches(3000-4000)... that will delay my postmaster and that postmaster associated with the query would take 10-15 minutes to process that query. So, now I limit that to 500, anything bigger than that will ask user to refine the query.
However this whole investigation made me observe some things. On my server box 7.4.7 I have some queries that are executing pretty slow 1.2-1.5secs it's a lot for a query that goes thru 5000 records. On my local environment 8.1 the same queries on similar table size executes much faster like 200-400ms. Do you know if this is a known issue or my dev box is better than my server box? I do have indexes on those fields I have criteria and order by on, I did run the reindex and I did a full vacuum. Anything else I need to do or I just need to go ahead an do an upgrade to 8.2 ?
Another thing I try to figure it out is the postgresql.conf setting. I wanted only to log the statements that are taking more than 500ms, I enabled that in my conf file but that alone won't track the statements. It looks I need pt make log_statement=true but that will track all the statements. Is there anything I miss here ?
Please let me know what you think.
MC
Ps.
I heard people complaining about my posting format. I use the hotmail web interface and the way they send the message is beyond my control ;-|
select * from entity where id is in (:ids)
If I get too many matches(3000-4000)... that will delay my postmaster and that postmaster associated with the query would take 10-15 minutes to process that query. So, now I limit that to 500, anything bigger than that will ask user to refine the query.
However this whole investigation made me observe some things. On my server box 7.4.7 I have some queries that are executing pretty slow 1.2-1.5secs it's a lot for a query that goes thru 5000 records. On my local environment 8.1 the same queries on similar table size executes much faster like 200-400ms. Do you know if this is a known issue or my dev box is better than my server box? I do have indexes on those fields I have criteria and order by on, I did run the reindex and I did a full vacuum. Anything else I need to do or I just need to go ahead an do an upgrade to 8.2 ?
Another thing I try to figure it out is the postgresql.conf setting. I wanted only to log the statements that are taking more than 500ms, I enabled that in my conf file but that alone won't track the statements. It looks I need pt make log_statement=true but that will track all the statements. Is there anything I miss here ?
Please let me know what you think.
MC
Ps.
I heard people complaining about my posting format. I use the hotmail web interface and the way they send the message is beyond my control ;-|
> Date: Fri, 8 Jun 2007 18:13:02 -0400
> From: ajs@xxxxxxxxxxxxxxx
> To: pgsql-general@xxxxxxxxxxxxxx
> Subject: Re: [GENERAL] Postmaster processes taking all the CPU
>
> On Fri, Jun 08, 2007 at 05:08:26PM -0500, MC Moisei wrote:
> > Yes all the connection are coming from within the box so no network
> > latency.Well, isn't the swap can be because too many process
> > postmaster are requiring more memory.
>
> But why are they requring more memory? Do you maybe have (e.g.)
> work_mem set too high, and that's what is causing your problem? Or
> shared buffers too big? This is a common error, and on a smaller set
> of data, it won't hurt; but when the data gets to a point, you lose.
>
> A
>
> --
> Andrew Sullivan | ajs@xxxxxxxxxxxxxxx
> 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
