Thread: PGSQL 7.4 -> 8.1 migration & performance problem

PGSQL 7.4 -> 8.1 migration & performance problem

From
"Philippe Lang"
Date:
Hi,

For an unknown reason, I cannot post this message to the mailing-list!

Here it is:

http://www.attiksystem.ch/postgresql-general.txt

Cheers,

Philippe Lang

Attachment

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Ludwig Isaac Lim
Date:
Hi :

  Maybe you forget to run the "ANALYZE" command afterwards.

ludwig.

--- Philippe Lang <philippe.lang@attiksystem.ch> wrote:

> Hi,
>
> For an unknown reason, I cannot post this message to the
> mailing-list!
>
> Here it is:
>
> http://www.attiksystem.ch/postgresql-general.txt
>
> Cheers,
>
> Philippe Lang
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
"Philippe Lang"
Date:
Hi,

I did not mention it in my first email, but I did run "ANALYSE" before
running the query... So statistics are just fine for the execution plan
engine.

Philippe

-----Message d'origine-----
De : Ludwig Isaac Lim [mailto:ludz_lim@yahoo.com]
Envoyé : mercredi, 31. mai 2006 15:52
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem

Hi :

  Maybe you forget to run the "ANALYZE" command afterwards.

ludwig.

--- Philippe Lang <philippe.lang@attiksystem.ch> wrote:

> Hi,
>
> For an unknown reason, I cannot post this message to the mailing-list!
>
> Here it is:
>
> http://www.attiksystem.ch/postgresql-general.txt
>
> Cheers,
>
> Philippe Lang


Attachment

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Ludwig Isaac Lim
Date:
Hi:

  How about the postgresql.conf settings? Did you changed
them?

ludwig.

--- Philippe Lang <philippe.lang@attiksystem.ch> wrote:

> Hi,
>
> I did not mention it in my first email, but I did run
> "ANALYSE" before
> running the query... So statistics are just fine for the
> execution plan
> engine.
>
> Philippe
>
> -----Message d'origine-----
> De : Ludwig Isaac Lim [mailto:ludz_lim@yahoo.com]
> Envoy�: mercredi, 31. mai 2006 15:52
> �: Philippe Lang
> Cc : pgsql-general@postgresql.org
> Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration &
> performance problem
>
> Hi :
>
>   Maybe you forget to run the "ANALYZE" command
> afterwards.
>
> ludwig.
>
> --- Philippe Lang <philippe.lang@attiksystem.ch> wrote:
>
> > Hi,
> >
> > For an unknown reason, I cannot post this message to
> the mailing-list!
> >
> > Here it is:
> >
> > http://www.attiksystem.ch/postgresql-general.txt
> >
> > Cheers,
> >
> > Philippe Lang
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
"Philippe Lang"
Date:
Hi,

No, the only change I made in postgresql.conf is in order to have the
postmaster listen on the network, and change the default datestyle, just
like I did with all the other installations.

Philippe

-----Message d'origine-----
De : Ludwig Isaac Lim [mailto:ludz_lim@yahoo.com]
Envoyé : mercredi, 31. mai 2006 16:13
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem


Hi:

  How about the postgresql.conf settings? Did you changed them?

ludwig.

--- Philippe Lang <philippe.lang@attiksystem.ch> wrote:

> Hi,
>
> I did not mention it in my first email, but I did run "ANALYSE" before
> running the query... So statistics are just fine for the execution
> plan engine.
>
> Philippe
>
> -----Message d'origine-----
> De : Ludwig Isaac Lim [mailto:ludz_lim@yahoo.com]
> Envoy�: mercredi, 31. mai 2006 15:52
> �: Philippe Lang
> Cc : pgsql-general@postgresql.org
> Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem
>
> Hi :
>
>   Maybe you forget to run the "ANALYZE" command afterwards.
>
> ludwig.
>
> --- Philippe Lang <philippe.lang@attiksystem.ch> wrote:
>
> > Hi,
> >
> > For an unknown reason, I cannot post this message to
> the mailing-list!
> >
> > Here it is:
> >
> > http://www.attiksystem.ch/postgresql-general.txt
> >
> > Cheers,
> >
> > Philippe Lang
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


Attachment

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Tom Lane
Date:
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> http://www.attiksystem.ch/postgresql-general.txt

Please provide EXPLAIN ANALYZE, not just EXPLAIN, output ... and try to
post it in an un-line-wrapped form.

Also, as Ludwig mentioned, a common gotcha is to forget to ANALYZE your
data after moving it to a new database.

            regards, tom lane

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Nis Jorgensen
Date:
Philippe Lang wrote:
> Hi,
>
> For an unknown reason, I cannot post this message to the mailing-list!
>
> Here it is:
>
> http://www.attiksystem.ch/postgresql-general.txt

Please post query and table structure as well. And since you are using a
medium with no limitation on line lenght, unwrap the query plan.

EXPLAIN ANALYZE output would more useful. Indeed, PG 8 believes it has
found a "better" plan than 7.4, costing only ~1.1 million units
(although these are probably not directly comparable).

Memory settings might be relevant - you may be comparing a vanilla
installation of 8.1 with a "tuned" 7.4.

/Nis



Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
"Philippe Lang"
Date:
Hi,

I have now disabled hyperthreading in /etc/grub.conf (added noht at the end
of kernel lines), rebooted the server, run ANALYSE on the database again,
and launched the query: things are even a little bit worse, it completes in
540 seconds now. So this is no ANALYSE or hyperthreading problem.

Here are both EXPLAIN ANALYSE results, plus the query itself:

Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt

Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt

Query is here: http://www.attiksystem.ch/big_query.txt

The freebsd box, as far as I can remember, has not been specifically tuned.
I did recompile a kernel with a few things inside, but really, I did not
spend hours on that two years ago.

Thanks,

Philippe


-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
Envoyé : mercredi, 31. mai 2006 16:39
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem

"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> http://www.attiksystem.ch/postgresql-general.txt

Please provide EXPLAIN ANALYZE, not just EXPLAIN, output ... and try to post
it in an un-line-wrapped form.

Also, as Ludwig mentioned, a common gotcha is to forget to ANALYZE your data
after moving it to a new database.

            regards, tom lane


Attachment

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Ludwig Isaac Lim
Date:
Hi:

  Just a hunch:

  What about adding an index to the field
etapes_lignes_commandes(code_etape). Assuming your database
 uses C locales. This might work for your particular query
since your filter is something like code_etape~~'COMP%'.
What about increasing the settings of work_mem, etc (check
http://www.powerpostgresql.com/PerfList) .

ludwig.

--- Philippe Lang <philippe.lang@attiksystem.ch> wrote:

> Hi,
>
> I have now disabled hyperthreading in /etc/grub.conf
> (added noht at the end
> of kernel lines), rebooted the server, run ANALYSE on the
> database again,
> and launched the query: things are even a little bit
> worse, it completes in
> 540 seconds now. So this is no ANALYSE or hyperthreading
> problem.
>
> Here are both EXPLAIN ANALYSE results, plus the query
> itself:
>
> Postgresql 7.4.5:
> http://www.attiksystem.ch/explain_analyze_74.txt
>
> Postgresql 8.1.4:
> http://www.attiksystem.ch/explain_analyze_81.txt
>
> Query is here: http://www.attiksystem.ch/big_query.txt
>
> The freebsd box, as far as I can remember, has not been
> specifically tuned.
> I did recompile a kernel with a few things inside, but
> really, I did not
> spend hours on that two years ago.
>
> Thanks,
>
> Philippe
>
>
> -----Message d'origine-----
> De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Envoy�: mercredi, 31. mai 2006 16:39
> �: Philippe Lang
> Cc : pgsql-general@postgresql.org
> Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration &
> performance problem
>
> "Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> > http://www.attiksystem.ch/postgresql-general.txt
>
> Please provide EXPLAIN ANALYZE, not just EXPLAIN, output
> ... and try to post
> it in an un-line-wrapped form.
>
> Also, as Ludwig mentioned, a common gotcha is to forget
> to ANALYZE your data
> after moving it to a new database.
>
>             regards, tom lane
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Tom Lane
Date:
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> Here are both EXPLAIN ANALYSE results, plus the query itself:
> Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
> Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt
> Query is here: http://www.attiksystem.ch/big_query.txt

My goodness, that is one big ugly query :-(

Trawling through the EXPLAIN ANALYZE results, it seems that the lossage
comes from the repeated occurrences of this pattern:

    SELECT travaux_en_cours_mvu FROM lignes_workflows AS lw
    INNER JOIN workflows AS w
    ON lw.id_workflow = w.id
    WHERE w.code_article = lignes_commandes.code_article
    AND w.code_type_workflow = commandes.code_type_workflow
    AND SUBSTRING(lw.code_etape FROM 1 FOR 3) = SUBSTRING(etapes_lignes_commandes.code_etape FROM 1 FOR 3)
    AND lw.ordre = etapes_lignes_commandes.ordre

7.4 is doing this as

  ->  Nested Loop  (cost=0.00..37.28 rows=1 width=8) (actual time=0.056..0.087 rows=1 loops=13653)
        ->  Index Scan using w_code_article on workflows w  (cost=0.00..15.76 rows=1 width=4) (actual time=0.016..0.024
rows=1loops=13653) 
              Index Cond: (code_article = $1)
              Filter: (code_type_workflow = $2)
        ->  Index Scan using lw_id_workflow on lignes_workflows lw  (cost=0.00..21.51 rows=1 width=12) (actual
time=0.023..0.036rows=1 loops=13651) 
              Index Cond: (lw.id_workflow = "outer".id)
              Filter: (("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3)) AND (ordre = $4))

8.1 is doing

  ->  Nested Loop  (cost=18.93..26.84 rows=1 width=8) (actual time=0.431..0.434 rows=1 loops=13630)
        ->  Bitmap Heap Scan on workflows w  (cost=6.63..10.51 rows=1 width=4) (actual time=0.107..0.107 rows=1
loops=13630)
              Recheck Cond: ((code_article = $1) AND (code_type_workflow = $2))
              ->  BitmapAnd  (cost=6.63..6.63 rows=1 width=0) (actual time=0.104..0.104 rows=0 loops=13630)
                    ->  Bitmap Index Scan on w_code_article  (cost=0.00..2.02 rows=5 width=0) (actual time=0.017..0.017
rows=5loops=13630) 
                          Index Cond: (code_article = $1)
                    ->  Bitmap Index Scan on w_code_type_workflow  (cost=0.00..4.36 rows=389 width=0) (actual
time=0.083..0.083rows=390 loops=13628) 
                          Index Cond: (code_type_workflow = $2)
        ->  Bitmap Heap Scan on lignes_workflows lw  (cost=12.30..16.31 rows=1 width=12) (actual time=0.312..0.313
rows=1loops=13628) 
              Recheck Cond: ((lw.id_workflow = "outer".id) AND (lw.ordre = $4))
              Filter: ("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3))
              ->  BitmapAnd  (cost=12.30..12.30 rows=1 width=0) (actual time=0.306..0.306 rows=0 loops=13628)
                    ->  Bitmap Index Scan on lw_id_workflow  (cost=0.00..2.02 rows=7 width=0) (actual time=0.009..0.009
rows=7loops=13628) 
                          Index Cond: (lw.id_workflow = "outer".id)
                    ->  Bitmap Index Scan on lw_ordre  (cost=0.00..10.03 rows=1437 width=0) (actual time=0.293..0.293
rows=1714loops=13628) 
                          Index Cond: (ordre = $4)

In the parts of the plan that do not depend on workflows/lignes_workflows
joins, 8.1 is consistently beating 7.4.

So basically 8.1 is being too optimistic about the value of ANDing
multiple indexes.  If you try setting enable_bitmapscan off, you'll
probably find 8.1 beating 7.4 handily for this query.  That's a really
blunt-instrument solution of course, and I wouldn't recommend it for
production because it'll probably kill performance elsewhere.  What
might work better is to get rid of the indexes w_code_type_workflow and
lw_ordre --- do you have any queries where those are actually useful?

Meanwhile, I think I'm going to have to take another look at the bitmap
cost estimates ... it shouldn't be bothering to AND a 7-row result
with a 1437-row result, even if that does save six trips to the heap.

            regards, tom lane

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Tom Lane
Date:
I wrote:
> So basically 8.1 is being too optimistic about the value of ANDing
> multiple indexes.  If you try setting enable_bitmapscan off, you'll
> probably find 8.1 beating 7.4 handily for this query.  That's a really
> blunt-instrument solution of course, and I wouldn't recommend it for
> production because it'll probably kill performance elsewhere.

A less brute-force way to fix this would be to adjust the planner cost
parameters.  Am I right in guessing that your database is small enough
to fit into RAM on the new server?  If so, it would be reasonable to
reduce random_page_cost, perhaps all the way to 1.0, and this would
probably improve the quality of the planner's choices for you.  Another
thing you should look at is increasing the cpu-cost parameters.  The
numbers in your EXPLAIN ANALYZE results suggest that on your new machine
the cost of processing an index tuple is about 1/50th of the cost of
touching an index page; that is, you ought to have cpu_index_tuple_cost
plus cpu_operator_cost around 0.02.  I'd try setting each of them to
0.01 and increasing cpu_tuple_cost a little bit, maybe to 0.02.

            regards, tom lane

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
"Philippe Lang"
Date:
Hi Tom, hi Ludwig,

Thanks for your support. Yes, this query has grown very big with time, and I was always asked to add exceptions in it,
sothe result is quite frightening! 


>>> TOM: If you try setting enable_bitmapscan off,
>>> you'll probably find 8.1 beating 7.4 handily for this query.

Correct. I had 239 seconds on the old 7.4 server, and I get 189 seconds with "enable_bitmapscan = off".


>>> LUDWIG: What about adding an index to the field
>>> etapes_lignes_commandes(code_etape)

I have this index already.


>>> LUDWIG: What about increasing the settings of work_mem?

default work_mem = 1024  ==>  511 seconds
work_mem         = 2048  ==>  531 seconds


>>> TOM: Am I right in guessing that your database is small
>>> enough to fit into RAM on the new server?

select pg_database_size('groupefpdb');

returns "360428168"

That's 360 MB. It fits in RAM, correct!

>>> TOM: If so, it would be reasonable to reduce random_page_cost,
>>> perhaps all the way to 1.0, and this would probably improve
>>> the quality of the planner's choices for you.

With that change I get results in 193 seconds.


>>> TOM: What might work better is to get rid of the indexes
>>> w_code_type_workflow and lw_ordre --- do you have any
>>> queries where those are actually useful?

Yes, I think I do, but let's try:

Drop both indexes
VACUUM FREEZE ANALYZE
ANALYZE

I get the results in 199 seconds.


>>> TOM: Another thing you should look at is increasing the
>>> cpu-cost parameters.  The numbers in your EXPLAIN ANALYZE
>>> results suggest that on your new machine the cost of
>>> processing an index tuple is about 1/50th of the cost of
>>> touching an index page; that is, you ought to have
>>> cpu_index_tuple_cost plus cpu_operator_cost around 0.02.
>>> I'd try setting each of them to 0.01 and increasing
>>> cpu_tuple_cost a little bit, maybe to 0.02.

cpu_index_tuple_cost = 0.01
cpu_operator_cost = 0.01
cpu_tuple_cost = 0.02

With these change, plus random_page_cost = 1, I get results in 195 seconds.

(Yes, I did restart the server!)

The new EXPLAIN ANALYSE at this point is here:

Postgresql 8.1.4 with tuning: http://www.attiksystem.ch/explain_analyze_81_2.txt

The old EXPLAIN ANALYZE are still here:

Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
Postgresql 8.1.4 without tuning: http://www.attiksystem.ch/explain_analyze_81.txt






Is there maybe something I could tune further on the kernel side? I get only 20 % improvements with the new server with
Linux,compared to the workstation with freebsd... Maybe my query is so CPU-bound that the most important thing is the
CPUclock speed, and in both cases I have a single 3Ghz CPU to run the query? What do you think? 


Philippe


-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
Envoyé : mercredi, 31. mai 2006 18:21
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem

"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> Here are both EXPLAIN ANALYSE results, plus the query itself:
> Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
> Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt
> Query is here: http://www.attiksystem.ch/big_query.txt

My goodness, that is one big ugly query :-(

Trawling through the EXPLAIN ANALYZE results, it seems that the lossage comes from the repeated occurrences of this
pattern:

    SELECT travaux_en_cours_mvu FROM lignes_workflows AS lw
    INNER JOIN workflows AS w
    ON lw.id_workflow = w.id
    WHERE w.code_article = lignes_commandes.code_article
    AND w.code_type_workflow = commandes.code_type_workflow
    AND SUBSTRING(lw.code_etape FROM 1 FOR 3) = SUBSTRING(etapes_lignes_commandes.code_etape FROM 1 FOR 3)
    AND lw.ordre = etapes_lignes_commandes.ordre

7.4 is doing this as

  ->  Nested Loop  (cost=0.00..37.28 rows=1 width=8) (actual time=0.056..0.087 rows=1 loops=13653)
        ->  Index Scan using w_code_article on workflows w  (cost=0.00..15.76 rows=1 width=4) (actual time=0.016..0.024
rows=1loops=13653) 
              Index Cond: (code_article = $1)
              Filter: (code_type_workflow = $2)
        ->  Index Scan using lw_id_workflow on lignes_workflows lw  (cost=0.00..21.51 rows=1 width=12) (actual
time=0.023..0.036rows=1 loops=13651) 
              Index Cond: (lw.id_workflow = "outer".id)
              Filter: (("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3)) AND (ordre = $4))

8.1 is doing

  ->  Nested Loop  (cost=18.93..26.84 rows=1 width=8) (actual time=0.431..0.434 rows=1 loops=13630)
        ->  Bitmap Heap Scan on workflows w  (cost=6.63..10.51 rows=1 width=4) (actual time=0.107..0.107 rows=1
loops=13630)
              Recheck Cond: ((code_article = $1) AND (code_type_workflow = $2))
              ->  BitmapAnd  (cost=6.63..6.63 rows=1 width=0) (actual time=0.104..0.104 rows=0 loops=13630)
                    ->  Bitmap Index Scan on w_code_article  (cost=0.00..2.02 rows=5 width=0) (actual time=0.017..0.017
rows=5loops=13630) 
                          Index Cond: (code_article = $1)
                    ->  Bitmap Index Scan on w_code_type_workflow  (cost=0.00..4.36 rows=389 width=0) (actual
time=0.083..0.083rows=390 loops=13628) 
                          Index Cond: (code_type_workflow = $2)
        ->  Bitmap Heap Scan on lignes_workflows lw  (cost=12.30..16.31 rows=1 width=12) (actual time=0.312..0.313
rows=1loops=13628) 
              Recheck Cond: ((lw.id_workflow = "outer".id) AND (lw.ordre = $4))
              Filter: ("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3))
              ->  BitmapAnd  (cost=12.30..12.30 rows=1 width=0) (actual time=0.306..0.306 rows=0 loops=13628)
                    ->  Bitmap Index Scan on lw_id_workflow  (cost=0.00..2.02 rows=7 width=0) (actual time=0.009..0.009
rows=7loops=13628) 
                          Index Cond: (lw.id_workflow = "outer".id)
                    ->  Bitmap Index Scan on lw_ordre  (cost=0.00..10.03 rows=1437 width=0) (actual time=0.293..0.293
rows=1714loops=13628) 
                          Index Cond: (ordre = $4)

In the parts of the plan that do not depend on workflows/lignes_workflows joins, 8.1 is consistently beating 7.4.

So basically 8.1 is being too optimistic about the value of ANDing multiple indexes.  If you try setting
enable_bitmapscanoff, you'll probably find 8.1 beating 7.4 handily for this query.  That's a really blunt-instrument
solutionof course, and I wouldn't recommend it for production because it'll probably kill performance elsewhere.  What
mightwork better is to get rid of the indexes w_code_type_workflow and lw_ordre --- do you have any queries where those
areactually useful? 

Meanwhile, I think I'm going to have to take another look at the bitmap cost estimates ... it shouldn't be bothering to
ANDa 7-row result with a 1437-row result, even if that does save six trips to the heap. 

            regards, tom lane


Attachment

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Peter Kovacs
Date:
About three years ago I did a test to compare the performance of an
Apache-PHP-PostgreSQL web application on Linux vs. FreeBSD. I used the
same machine with the then current versions of the said software. The
results were better by 30-35% on FreeBSD. Since then I have been having
the sneaking feeling that FreeBSD generally performs better than Linux.

(Actually, my personal experience had always been that FreeBSD was much
more responsive on very slow machines (Intel 486) when used
interactively. But this could have been a result of the rougher
scheduler in earlier Linuxes and despite better responsiveness, overall
performance could still have been better with Linux.)

Peter

Philippe Lang wrote:
> Hi Tom, hi Ludwig,
>
> Thanks for your support. Yes, this query has grown very big with time, and I was always asked to add exceptions in
it,so the result is quite frightening! 
>
>
>
>>>> TOM: If you try setting enable_bitmapscan off,
>>>> you'll probably find 8.1 beating 7.4 handily for this query.
>>>>
>
> Correct. I had 239 seconds on the old 7.4 server, and I get 189 seconds with "enable_bitmapscan = off".
>
>
>
>>>> LUDWIG: What about adding an index to the field
>>>> etapes_lignes_commandes(code_etape)
>>>>
>
> I have this index already.
>
>
>
>>>> LUDWIG: What about increasing the settings of work_mem?
>>>>
>
> default work_mem = 1024  ==>  511 seconds
> work_mem         = 2048  ==>  531 seconds
>
>
>
>>>> TOM: Am I right in guessing that your database is small
>>>> enough to fit into RAM on the new server?
>>>>
>
> select pg_database_size('groupefpdb');
>
> returns "360428168"
>
> That's 360 MB. It fits in RAM, correct!
>
>
>>>> TOM: If so, it would be reasonable to reduce random_page_cost,
>>>> perhaps all the way to 1.0, and this would probably improve
>>>> the quality of the planner's choices for you.
>>>>
>
> With that change I get results in 193 seconds.
>
>
>
>>>> TOM: What might work better is to get rid of the indexes
>>>> w_code_type_workflow and lw_ordre --- do you have any
>>>> queries where those are actually useful?
>>>>
>
> Yes, I think I do, but let's try:
>
> Drop both indexes
> VACUUM FREEZE ANALYZE
> ANALYZE
>
> I get the results in 199 seconds.
>
>
>
>>>> TOM: Another thing you should look at is increasing the
>>>> cpu-cost parameters.  The numbers in your EXPLAIN ANALYZE
>>>> results suggest that on your new machine the cost of
>>>> processing an index tuple is about 1/50th of the cost of
>>>> touching an index page; that is, you ought to have
>>>> cpu_index_tuple_cost plus cpu_operator_cost around 0.02.
>>>> I'd try setting each of them to 0.01 and increasing
>>>> cpu_tuple_cost a little bit, maybe to 0.02.
>>>>
>
> cpu_index_tuple_cost = 0.01
> cpu_operator_cost = 0.01
> cpu_tuple_cost = 0.02
>
> With these change, plus random_page_cost = 1, I get results in 195 seconds.
>
> (Yes, I did restart the server!)
>
> The new EXPLAIN ANALYSE at this point is here:
>
> Postgresql 8.1.4 with tuning: http://www.attiksystem.ch/explain_analyze_81_2.txt
>
> The old EXPLAIN ANALYZE are still here:
>
> Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
> Postgresql 8.1.4 without tuning: http://www.attiksystem.ch/explain_analyze_81.txt
>
>
>
>
>
>
> Is there maybe something I could tune further on the kernel side? I get only 20 % improvements with the new server
withLinux, compared to the workstation with freebsd... Maybe my query is so CPU-bound that the most important thing is
theCPU clock speed, and in both cases I have a single 3Ghz CPU to run the query? What do you think? 
>
>
> Philippe
>
>
> -----Message d'origine-----
> De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Envoyé : mercredi, 31. mai 2006 18:21
> À : Philippe Lang
> Cc : pgsql-general@postgresql.org
> Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem
>
> "Philippe Lang" <philippe.lang@attiksystem.ch> writes:
>
>> Here are both EXPLAIN ANALYSE results, plus the query itself:
>> Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
>> Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt
>> Query is here: http://www.attiksystem.ch/big_query.txt
>>
>
> My goodness, that is one big ugly query :-(
>
> Trawling through the EXPLAIN ANALYZE results, it seems that the lossage comes from the repeated occurrences of this
pattern:
>
>     SELECT travaux_en_cours_mvu FROM lignes_workflows AS lw
>     INNER JOIN workflows AS w
>     ON lw.id_workflow = w.id
>     WHERE w.code_article = lignes_commandes.code_article
>     AND w.code_type_workflow = commandes.code_type_workflow
>     AND SUBSTRING(lw.code_etape FROM 1 FOR 3) = SUBSTRING(etapes_lignes_commandes.code_etape FROM 1 FOR 3)
>     AND lw.ordre = etapes_lignes_commandes.ordre
>
> 7.4 is doing this as
>
>   ->  Nested Loop  (cost=0.00..37.28 rows=1 width=8) (actual time=0.056..0.087 rows=1 loops=13653)
>         ->  Index Scan using w_code_article on workflows w  (cost=0.00..15.76 rows=1 width=4) (actual
time=0.016..0.024rows=1 loops=13653) 
>               Index Cond: (code_article = $1)
>               Filter: (code_type_workflow = $2)
>         ->  Index Scan using lw_id_workflow on lignes_workflows lw  (cost=0.00..21.51 rows=1 width=12) (actual
time=0.023..0.036rows=1 loops=13651) 
>               Index Cond: (lw.id_workflow = "outer".id)
>               Filter: (("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3)) AND (ordre = $4))
>
> 8.1 is doing
>
>   ->  Nested Loop  (cost=18.93..26.84 rows=1 width=8) (actual time=0.431..0.434 rows=1 loops=13630)
>         ->  Bitmap Heap Scan on workflows w  (cost=6.63..10.51 rows=1 width=4) (actual time=0.107..0.107 rows=1
loops=13630)
>               Recheck Cond: ((code_article = $1) AND (code_type_workflow = $2))
>               ->  BitmapAnd  (cost=6.63..6.63 rows=1 width=0) (actual time=0.104..0.104 rows=0 loops=13630)
>                     ->  Bitmap Index Scan on w_code_article  (cost=0.00..2.02 rows=5 width=0) (actual
time=0.017..0.017rows=5 loops=13630) 
>                           Index Cond: (code_article = $1)
>                     ->  Bitmap Index Scan on w_code_type_workflow  (cost=0.00..4.36 rows=389 width=0) (actual
time=0.083..0.083rows=390 loops=13628) 
>                           Index Cond: (code_type_workflow = $2)
>         ->  Bitmap Heap Scan on lignes_workflows lw  (cost=12.30..16.31 rows=1 width=12) (actual time=0.312..0.313
rows=1loops=13628) 
>               Recheck Cond: ((lw.id_workflow = "outer".id) AND (lw.ordre = $4))
>               Filter: ("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3))
>               ->  BitmapAnd  (cost=12.30..12.30 rows=1 width=0) (actual time=0.306..0.306 rows=0 loops=13628)
>                     ->  Bitmap Index Scan on lw_id_workflow  (cost=0.00..2.02 rows=7 width=0) (actual
time=0.009..0.009rows=7 loops=13628) 
>                           Index Cond: (lw.id_workflow = "outer".id)
>                     ->  Bitmap Index Scan on lw_ordre  (cost=0.00..10.03 rows=1437 width=0) (actual time=0.293..0.293
rows=1714loops=13628) 
>                           Index Cond: (ordre = $4)
>
> In the parts of the plan that do not depend on workflows/lignes_workflows joins, 8.1 is consistently beating 7.4.
>
> So basically 8.1 is being too optimistic about the value of ANDing multiple indexes.  If you try setting
enable_bitmapscanoff, you'll probably find 8.1 beating 7.4 handily for this query.  That's a really blunt-instrument
solutionof course, and I wouldn't recommend it for production because it'll probably kill performance elsewhere.  What
mightwork better is to get rid of the indexes w_code_type_workflow and lw_ordre --- do you have any queries where those
areactually useful? 
>
> Meanwhile, I think I'm going to have to take another look at the bitmap cost estimates ... it shouldn't be bothering
toAND a 7-row result with a 1437-row result, even if that does save six trips to the heap. 
>
>             regards, tom lane
>
>

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Arnaud Lesauvage
Date:
Philippe Lang a écrit :
> Hardware is much more powerful: intel server motherboard, dual-xeon
> 3GHz, SCSI disks (raid 1), 4GB RAM.

Do you need the hyperthreading ?
Depending on your case, you might have better results with 2x3GHz
thant 4x1.5GHz.

--
Arnaud




Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
"Philippe Lang"
Date:
Hi,

I made some tests, with and without Hyperthreading: with hyperthreading, on
a dual-processor, top mentions a 25% load, and without, 50%, but computing
time is exactly the same.

Philippe

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] De la part de Arnaud Lesauvage
Envoyé : mercredi, 31. mai 2006 16:20
À : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem

Philippe Lang a écrit :
> Hardware is much more powerful: intel server motherboard, dual-xeon
> 3GHz, SCSI disks (raid 1), 4GB RAM.

Do you need the hyperthreading ?
Depending on your case, you might have better results with 2x3GHz thant
4x1.5GHz.

--
Arnaud




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

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


Attachment

Re: PGSQL 7.4 -> 8.1 migration & performance problem

From
Lincoln Yeoh
Date:
You are getting 190+ secs on the new hardware and 235 secs on the old?

Is the CPU usage maxed out? Assuming linux run top and then press 1.

If in both cases the CPU's are maxed out, then that explains why they are
about the same speed = both are 3GHz Intel CPUs, and your DB fits in RAM.

I've really no idea how to improve your query or indexing...

Try EXPLAIN ANALYZE <your query here> and then look for the longest running
bits.

Link.


At 03:32 PM 6/1/2006 +0200, Philippe Lang wrote:

>Hi,
>
>I made some tests, with and without Hyperthreading: with hyperthreading, on
>a dual-processor, top mentions a 25% load, and without, 50%, but computing
>time is exactly the same.
>
>Philippe
>
>-----Message d'origine-----
>De : pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] De la part de Arnaud Lesauvage
>Envoyé : mercredi, 31. mai 2006 16:20
>À : pgsql-general@postgresql.org
>Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem
>
>Philippe Lang a écrit :
> > Hardware is much more powerful: intel server motherboard, dual-xeon
> > 3GHz, SCSI disks (raid 1), 4GB RAM.
>
>Do you need the hyperthreading ?
>Depending on your case, you might have better results with 2x3GHz thant
>4x1.5GHz.
>
>--
>Arnaud
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>