Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TGrzT-0007aX-6r for pgsql-performance@postgresql.org; Wed, 26 Sep 2012 13:49:27 +0000 Received: from mxin.ulb.ac.be ([164.15.128.112]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TGrzP-0001Zd-SH for pgsql-performance@postgresql.org; Wed, 26 Sep 2012 13:49:26 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ApoBAAEHY1CkD30E/2dsb2JhbAANOIJLg0CFX7YXAQEBAwEjKyAKBgsJAhEDAQIBCQQSCwICAgcDAgECAR8eCA4FCAEBBRQEh1IDCRGmKG4riQAXQokIixiEd4ESA45ugSCGcIQ6jUWBWgc Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by smtp.ulb.ac.be with ESMTP; 26 Sep 2012 15:49:23 +0200 Message-ID: <506307E2.4060504@ulb.ac.be> Date: Wed, 26 Sep 2012 15:49:22 +0200 From: Julien Cigar Organization: Belgian Biodiversity Platform User-Agent: Mozilla/5.0 (X11; FreeBSD i386; rv:12.0) Gecko/20120503 Thunderbird/12.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Same query doing slow then quick References: <1348662446741-5725486.post@n5.nabble.com> <1348664629662-5725491.post@n5.nabble.com> <50630162.2090603@ulb.ac.be> In-Reply-To: Content-Type: multipart/mixed; boundary="------------010403040301050809090307" X-Pg-Spam-Score: -3.9 (---) X-Archive-Number: 201209/128 X-Sequence-Number: 47874 This is a multi-part message in MIME format. --------------010403040301050809090307 Content-Type: multipart/alternative; boundary="------------080800030705020301070204" --------------080800030705020301070204 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 09/26/2012 15:36, FFW_Rude wrote: > Hi, > > Thank you for your answer. > > It was already at 16MB and i upped it just this morning to 64MB. Still > no change > that's normal, please configure shared_buffers and effective_cache_size properly > Rude - Last Territory > > *Ou écouter ?* > http://www.deezer.com/fr/music/last-territory/the-last-hope-3617781 > (Post-apocalyptic Metal) > http://www.deezer.com/fr/music/rude-undertaker (Pop-Rock) > > *Ou acheter ?* > /La Fnac/ > http://recherche.fnac.com/fmia14622213/Last-Territory > http://recherche.fnac.com/fmia14770622/Rude-Undertaker > > /iTunes/ > http://itunes.apple.com/us/artist/last-territory/id533857009?ign-mpt=uo%3D4 > > > > ------------------------------------------------------------------------ > Date: Wed, 26 Sep 2012 06:22:35 -0700 > From: [hidden email] > To: [hidden email] > Subject: Re: Same query doing slow then quick > > On 09/26/2012 15:03, FFW_Rude wrote: > > > Here is the answer to Ray Stell who send me the wiki page of Slow > Query. I > > hope i detailed all you wanted (i basicly pasted the page and add my > > answers). > > > > Full Table and Index Schema: > > > > schema tables_adresses > > "Tables" > > tables_adresses.adresses_XX (id (serial), X(Double precision),Y (Double > > precision)). > > "Indexes" > > adresses_XX_pkey (Primary key, btree) > > calcul_XX (non unique, Btree on X,Y) > > > > schema tables_gps > > "Tables" > > tables_gps.gps_XX (id (int),x_max(numeric(10,5)), y_max > > (numeric(10,5)),x_min(numeric(10,5)),y_min(numeric(10,5))) > > "Indexes" > > calculs_XX (non unique Btree x_min,x_max,y_min,y_max) > > gps_10_pkey (Primary key on id btree) > > > > Approximate rows 250000. > > No large objects in it (just data) > > No NULL > > receives a large number of UPDATEs or DELETEs regularly > > is growing daily > > > > I can't post an EXPLAIN ANALYZE because of the 6hour query time. > > > > Postgres version: 9.1 > > > > History: was this query always slow, : "YES" > > > > Hardware: Ubuntu server last version 32bits > > > > Daily VACUUM FULL ANALYZE, REINDEX TABLE on all the tables. > > > > WAL Configuration: Whats a WAL ? > > > > GUC Settings: i didn't change anything. All is standard. > > > > shared_buffers should be 10% to 25% of available RAM (it's on 24MB > and can't > > go higher. The server has 4Gb) > > > > effective_cache_size should be 75% of available RAM => I don't now > what this > > is. > before looking further, please configure shared_buffers and > effective_cache_size properly, it's fundamental > you'll probably need to raise SHMALL/SHMMAX, take a look at: > http://www.postgresql.org/docs/current/static/kernel-resources.html > for 4GB of RAM I start with shared_buffers to 512MB and > effective_cache_size to 2GB > > > Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does > it make a > > difference? "No" > > default work_mem is very small, set it to something like 16MB > > > > > > > > > -- > > View this message in context: > http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725491.html > > Sent from the PostgreSQL - performance mailing list archive at > Nabble.com. > > > > > > > -- > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > > > -- > Sent via pgsql-performance mailing list ([hidden email] > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > > *jcigar.vcf* (304 bytes) Download Attachment > > > > ------------------------------------------------------------------------ > If you reply to this email, your message will be added to the > discussion below: > http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725493.html > > To unsubscribe from Same query doing slow then quick, click here. > NAML > > > > ------------------------------------------------------------------------ > View this message in context: RE: Same query doing slow then quick > > Sent from the PostgreSQL - performance mailing list archive > > at Nabble.com. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. --------------080800030705020301070204 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 09/26/2012 15:36, FFW_Rude wrote:
Hi,

Thank you for your answer.

It was already at 16MB and i upped it just this morning to 64MB. Still no change


that's normal, please configure shared_buffers and effective_cache_size properly

Rude - Last Territory

Ou écouter ?

Ou acheter ?
La Fnac

iTunes


Date: Wed, 26 Sep 2012 06:22:35 -0700
From: [hidden email]
To: [hidden email]
Subject: Re: Same query doing slow then quick

On 09/26/2012 15:03, FFW_Rude wrote:

> Here is the answer to Ray Stell who send me the wiki page of Slow Query. I
> hope i detailed all you wanted (i basicly pasted the page and add my
> answers).
>
> Full Table and Index Schema:
>
> schema tables_adresses
> "Tables"
> tables_adresses.adresses_XX (id (serial), X(Double precision),Y (Double
> precision)).
> "Indexes"
> adresses_XX_pkey (Primary key, btree)
> calcul_XX (non unique, Btree on X,Y)
>
> schema tables_gps
> "Tables"
> tables_gps.gps_XX (id (int),x_max(numeric(10,5)), y_max
> (numeric(10,5)),x_min(numeric(10,5)),y_min(numeric(10,5)))
> "Indexes"
> calculs_XX (non unique Btree x_min,x_max,y_min,y_max)
> gps_10_pkey (Primary key on id btree)
>
> Approximate rows 250000.
> No large objects in it (just data)
> No NULL
> receives a large number of UPDATEs or DELETEs regularly
> is growing daily
>
> I can't post an EXPLAIN ANALYZE because of the 6hour query time.
>
> Postgres version: 9.1
>
> History: was this query always slow, : "YES"
>
> Hardware: Ubuntu server last version 32bits
>
> Daily VACUUM FULL ANALYZE, REINDEX TABLE on all the tables.
>
> WAL Configuration: Whats a WAL ?
>
> GUC Settings: i didn't change anything. All is standard.
>
> shared_buffers should be 10% to 25% of available RAM (it's on 24MB and can't
> go higher. The server has 4Gb)
>
> effective_cache_size should be 75% of available RAM =>  I don't now what this
> is.
before looking further, please configure shared_buffers and
effective_cache_size properly, it's fundamental
you'll probably need to raise SHMALL/SHMMAX, take a look at:
http://www.postgresql.org/docs/current/static/kernel-resources.html
for 4GB of RAM I start with shared_buffers to 512MB and
effective_cache_size to 2GB

> Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does it make a
> difference? "No"

default work_mem is very small, set it to something like 16MB

>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725491.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>


--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.



--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

jcigar.vcf (304 bytes) Download Attachment


If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486p5725493.html
To unsubscribe from Same query doing slow then quick, click here.
NAML

View this message in context: RE: Same query doing slow then quick
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--------------080800030705020301070204-- --------------010403040301050809090307 Content-Type: text/x-vcard; charset=utf-8; name="jcigar.vcf" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="jcigar.vcf" YmVnaW46dmNhcmQNCmZuOkp1bGllbiBDaWdhcg0KbjpDaWdhcjtKdWxpZW4NCm9yZztxdW90 ZWQtcHJpbnRhYmxlOkJlbGdpYW4gQmlvZGl2ZXJzaXR5IFBsYXRmb3JtO1VMQiAoVW5pdmVy c2l0PUMzPUE5IExpYnJlIGRlIEJydXhlbGxlcykNCmFkcjo7OztCcnVzc2Vsczs7O0JlbGdp dW0NCmVtYWlsO2ludGVybmV0OmpjaWdhckB1bGIuYWMuYmUNCnRlbDt3b3JrOiszMigwKTI2 NTA1NzUyDQp4LW1vemlsbGEtaHRtbDpGQUxTRQ0KdXJsOmh0dHA6Ly93d3cuYmlvZGl2ZXJz aXR5LmJlDQp2ZXJzaW9uOjIuMQ0KZW5kOnZjYXJkDQoNCg== --------------010403040301050809090307--