Re: random slow query

On 6/30/09 2:39 PM, "Mike Ivanov" <mikei <at> activestate.com> wrote:

> Scott Carey wrote:
>>> 222 / 8 cores = ridiculous 27 processes per core, while the OP has 239
>> That's not rediculous at all.  Modern OS's handle thousands of idle
>> processes just fine.
>> 
>>  
> I meant that 27 was a ridiculously small number.
> 
>> Or you can control the behavior with the following kenrnel params:
>> vm.swappiness
>> vm.dirty_ratio
>> vm.dirty_background ratio
>>  
> Thanks for pointing that out!
> 
>> Actually, no.  When a process wakes up only the pages that are needed are
>> accessed.  For most idle processes that wake up from time to time, a small
>> bit of work is done, then they go back to sleep.  This initial allocation
>> does NOT come from the page cache, but from the "buffers" line in top.  The
>> os tries to keep some ammount of free buffers not allocated to processes or
>> pages available, so that allocation demands can be met without having to
>> synchronously decide which buffers from page cache to eject.
>>  
> Wait a second, I'm trying to understand that 
> Did you mean that FS cache pages are first allocated from the buffer
> pages or that process memory being paged out to swap is first written to
> buffers? Could you clarify please?

Re: random slow query

Scott Carey wrote:
> the OS can either quickly allocate to the process or
> the page cache from the free buffers, or more slowly take from the page
> cache, or even more slowly page out a process page.
>   

Aha, now it all makes sense.

> I like to use the '5 second rule'.  dirty_background_ratio should be sized
> so that it takes about 5 seconds to flush to disk in optimal conditions.
> dirty_ratio should be 2x to 5x this depending on your application's needs --
> for a system with well tuned postgres checkpoints, smaller tends to be
> better to limit stalls while waiting for the checkpoint fsync to finish.
>   

Thanks a lot, this is invaluable information.

Regards,
Mike

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

- Slow Query

Re: - Slow Query

Hi Rui,
> i have this query (i think is a simple one)

Could you EXPLAIN ANALYZE the query and show the results please?

Thanks,
Mike

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: - Slow Query

Rui Carvalho wrote:
> SELECT distinct on (bien.uid) bien.uid , bien.date_creation , 
> bien.date_modification , bien.nom ,  bien.numero_voie , bien.mer , 
> bien.proximite ,  bien.nom_voie , bien.type_voie , bien.lieudit ,  
> bien.arrondissement , bien.montagne , bien.complement_adresse , 
> bien.xy_geo , bien.ref_type_avancement ,   bien.ref_agence , 
> bien.acces_handicape , bien.surface_totale , bien.ref_type_transaction 
> ,  bien.reference_bien ,
>                     bien.ref_type_bien ,  bien.bien_exception , 
> bien.video_online , bien.geom , habitation.nombre_de_chambres, 
> habitation.nombre_de_wc ,
>                     prix.montant , ville.nom ,ville.abreviation , 
> ville.code_insee , ville.code_postal ,
>                     freguesia_ville.code_insee , 
> freguesia_ville.code_postal , freguesia_ville.ref_freguesia , 
> freguesia_ville.ref_ville ,
>                     freguesia.nom , freguesia.numero , departement.nom 
> , departement.numero , region.nom ,region.numero , zone.zone_public , 
> type_transaction.nom, mandat.numero_mandat_pt
> FROM bien
> LEFT outer JOIN prix ON prix.ref_bien = bien.uid  AND 
> prix.ref_type_prix in (2,9) and prix.montant !=0  LEFT outer JOIN 
> habitation on habitation.uid = bien.uid
> LEFT outer JOIN ville ON ville.uid = bien.ref_ville LEFT outer JOIN 
> freguesia_ville ON freguesia_ville.ref_ville =ville.uid
> LEFT outer JOIN freguesia ON freguesia.uid = freguesia_ville.ref_freguesia
> LEFT outer JOIN departement ON departement.uid =ville.ref_departement 
> LEFT outer JOIN region ON region.uid = departement.ref_region
> LEFT outer JOIN zone ON zone.ref_bien = bien.uid JOIN imagebien ON 
> imagebien.ref_bien = bien.uid left outer join mandat on 
> mandat.ref_bien=bien.uid
> LEFT outer JOIN type_transaction ON type_transaction.uid = 
> bien.ref_type_transaction
> LEFT OUTER JOIN agence on agence.uid = bien.ref_agence
> WHERE imagebien.uid IS NOT NULL AND bien.statut = 0 and 
> bien.visible_internet = 1 and bien.ref_agence = XXXXXXX
>

You need to run explain analyze on the query, and post the results   
This will tell us where the time is getting eaten up and other problems 
that might be in the query. 
Also need to know the version of Postgresql???

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: - Slow Query

 >  Merge Join (cost=111885.70..319492.88 rows=13016048 width=620)

The outermost merge join has to go through 13 million rows. If you 
remove "distinct on (bien.uid)", you'll see that.

 > LEFT outer JOIN ville ON ville.uid = bien.ref_ville
 > LEFT outer JOIN freguesia_ville ON freguesia_ville.ref_ville =ville.uid

This is not enough. You have to add this condition as well:

AND bien.ref_ville = freguesia_ville.ref_ville

In other words, when you link three tables by a common field, all three 
relationships should be explicitly expressed, otherwise you'll have this 
type of explosive row multiplication.

Although I don't quite understand the purpose of the query, I don't 
think you need all those OUTER joins.

Regards,
Mike

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: - Slow Query

Re: - Slow Query

On Wed, Jul 1, 2009 at 11:37 AM, Rui Carvalho<rui.hmcarvalho <at> gmail.com> wrote:
> hum thanks a lot for the quick answer,
>
> if is not abuse of your patience
>
> what is the best alternative to the LEFT OUTER JOINS?

Hard to say.  Generally, when you really do need a left, right, or
full outer join, you need it, and there's not a lot of alternatives.
Sometimes putting a where clause portion into the on clause helps.
like:

select * from a left join b on (a.id=b.id) where a.somefield=2

might run faster with

select * from a left join b on (a.id=bid. and a.somefield=2);

but it's hard to say.  I'd definitely post it to the list and see who
knows what.

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: - Slow Query

Scott Marlowe <scott.marlowe <at> gmail.com> writes:
> Sometimes putting a where clause portion into the on clause helps.
> like:
> select * from a left join b on (a.id=b.id) where a.somefield=2
> might run faster with
> select * from a left join b on (a.id=bid. and a.somefield=2);
> but it's hard to say.

Uh, those are not the same query ... they will give different results
for rows with a.somefield different from 2.

			regards, tom lane

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: - Slow Query

Rui Carvalho wrote:
> hum thanks a lot for the quick answer,
>
> if is not abuse of your patience
>
> what is the best alternative to the LEFT OUTER JOINS?
I meant I wasn't sure whether you really meant *outer* joins. Too many 
of them looked kinda suspicious 

If you *do* need them, then there is no alternative, as Scott said.

Mike

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance