PGSQL 7.4 -> 8.1 migration & performance problem

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

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

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:

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

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

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

"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

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

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&#65533;: mercredi, 31. mai 2006 16:39
&#65533;: 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

"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=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.036 rows=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=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.083 rows=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=1 loops=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=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=1714 loops=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

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

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 with Linux, compared to the workstation with freebsd... Maybe my query is so CPU-bound that the most important thing is the CPU 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.024 rows=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.036 rows=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=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.083 rows=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=1 loops=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=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=1714 loops=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

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,

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

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: