Performance problem with SG-clusteren and postgres 8.0.7

Just translating a message from our DBA Rafael Martinez,

We have a huge problem ahead of us which may delay our Service Guard
Cluster project.

Our last performance test with our new cluster and RT shows that we
have a SQL statement that 'planner" in Postgres 8.0.7 doesn’t manage to
find a good ‘plan’ for and takes to long to execute.

I have sent an e-mail to pgsql-performance and got a reply back, and a
solution that implies to upgrade to 8.1.4 when it’s shipped.

The e-mail I sent (including the SQL) to pgsql-performance:

Answers from Tom Lane (one of the major developers in the PostgreSQL project):

Rafael Martinez, r.m.guerrero@usit.uio.no
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

Tomas, RT management

Tomas A. P. Olaj, email: tomas.olaj@usit.uio.no, web: folk.uio.no/tomaso
University of Oslo / USIT (Center for Information Technology Services)
System- and Application Management / Applications Management Group

This is one of the most problematic query in RT. I only can confirm
that the first plan (on Pg 7.x) is almost optimal one (there is still
way to optimize it with planner) and this is plan we wanted to achive
when were changing this query in RT-3.4.5.

I hope Pg 8.1.4 would be available soon and we could recommend that
instead of Pg 8.0.x.

Thanks to you and your team for the report and help.On 4/10/06, Tomas Olaj tomas.olaj@usit.uio.no wrote:

Just translating a message from our DBA Rafael Martinez,

We have a huge problem ahead of us which may delay our Service Guard
Cluster project.

Our last performance test with our new cluster and RT shows that we
have a SQL statement that 'planner" in Postgres 8.0.7 doesn’t manage to
find a good ‘plan’ for and takes to long to execute.

I have sent an e-mail to pgsql-performance and got a reply back, and a
solution that implies to upgrade to 8.1.4 when it’s shipped.

The e-mail I sent (including the SQL) to pgsql-performance:
PostgreSQL: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7

Answers from Tom Lane (one of the major developers in the PostgreSQL project):
PostgreSQL: Re: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7
PostgreSQL: Re: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7


Rafael Martinez, r.m.guerrero@usit.uio.no
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

Tomas, RT management


Tomas A. P. Olaj, email: tomas.olaj@usit.uio.no, web: folk.uio.no/tomaso
University of Oslo / USIT (Center for Information Technology Services)
System- and Application Management / Applications Management Group

Best regards, Ruslan.

On the marvelous Mon, 10 Apr 2006, Ruslan Zakirov wrote kindly to me …

This is one of the most problematic query in RT. I only can confirm
that the first plan (on Pg 7.x) is almost optimal one (there is still
way to optimize it with planner) and this is plan we wanted to achive
when were changing this query in RT-3.4.5.

I hope Pg 8.1.4 would be available soon and we could recommend that
instead of Pg 8.0.x.

Best regards, Ruslan.

Our production environment is still on RT 3.4.2. We’re waiting on BP to
approve our spec to include mailadministration in RT that our perl
developer Petter Reinholdtsen sent earlier to BP to continue our upgrade, since it’s a
feature many of our users want, and a new upgrade would imply that we have
to rewrite the add-on code once more. We would rather pay BP to include it
in the source code as a regular feature of RT. Currently we run RT and
PostgreSQL on the same production server, but this is just temporarly.

Our new Pg hotel on a SG cluster combined with the appserver will be
our future production platform.

We will use a 64 bit amd Linux app-server to run RT/Perl/webserver on.
Probably we will have two 64 bits machines in an app-cluster for failover
with LVS in front. I guess this won’t be any problem? We run Apache 2, but
I was curious if lighttpd (“lighty”) was another alternative?

Another thing is the MTA. We run exim, and the best solution would be to
outsource this to Postmaster? Today, exim is also run on the same
production server.

“Chtob vse byli zdorovy”, i.e. “Let everybody be healthy”
Tomas, RT management

Tomas A. P. Olaj, email: tomas.olaj@usit.uio.no, web: folk.uio.no/tomaso
University of Oslo / USIT (Center for Information Technology Services)
System- and Application Management / Applications Management Group

On the marvelous Mon, 10 Apr 2006, Ruslan Zakirov wrote kindly to me …

This is one of the most problematic query in RT. I only can confirm
that the first plan (on Pg 7.x) is almost optimal one (there is still
way to optimize it with planner) and this is plan we wanted to achive
when were changing this query in RT-3.4.5.

I hope Pg 8.1.4 would be available soon and we could recommend that
instead of Pg 8.0.x.

A proposal from the PostgreSQL developers how this SQL can be improved.

Tomas

Tomas A. P. Olaj, email: tomas.olaj@usit.uio.no, web: folk.uio.no/tomaso
University of Oslo / USIT (Center for Information Technology Services)
System- and Application Management / Applications Management Group

On the marvelous Mon, 10 Apr 2006, Ruslan Zakirov wrote kindly to me …

PostgreSQL: Re: Help with optimizing a sql statement

A proposal from the PostgreSQL developers how this SQL can be improved.

I believe this is something that’s already fixed in RT 3.4.5. It would
be very useful if you could test with the current release to see if the
behaviour more suits you.

Best,
Jesse