Re: PERFORMANCE meeting summary & next meeting topic!

Hello!

Last night we had our presentation on performance issues. I showed an
example where I used PL/PgSQL to mimic the behavior of a GUI
application, and the small code change that reduced the number of calls
to a particularly hairy date-range calculation function. When I first
looked at it, I thought that a SUM() call was the performance-killer,
but after examining the pg_stat & pg_statio tables, it was clear that
the calendar function was the culprit. I showed the code I used to
demonstrate the problem, and the before/after EXPLAIN output.

For Gab's part, she brought in her schema and we had a look at the
query that was running so slow. She explained how some optimizations
we'd tried actually made the query slower. The test database we used
in the meeting was running on version 8.1, Gab's is running 7.4. The
query was about 400% faster in the newer version. The general
consensus was that she should upgrade. We didn't have a clear answer
to why the query was so slow in the older version, but it was clear
that it was faster in the newer version of postgresql.

We also chatted about the planner and optimizing queries. The
importance of knowing what the planner is doing was emphasized, but
that for most general cases, the planner will do the right thing. For
example, the order of joins should probably not matter because the
planner will most likely pick the best one.

Then we had beer at the Lucky Lab! Two pitchers of porter were
consumed.

Thanks Gabrielle, Randal, David and Mark for attending!

Next meeting: October 17, 2006 - Postgresql 8.2 (wahoo!) and MySQL
caveats.

David Wheeler offered to give a rundown of 8.2 and to talk about some
recent bugaboos he's run into with MySQL. Other MySQL horror stories,
or PostgreSQL success stories welcome.

Look how organized we are.

-selena

On Sep 20, 2006, at 09:03, Selena Deckelmann wrote:

> The general consensus was that she should upgrade. We didn't have
> a clear answer to why the query was so slow in the older version,
> but it was clear that it was faster in the newer version of
> postgresql.

Actually, I think that the consensus was that she should try
increasing the sort_mem on the 7.4 database to see if that helps. But
upgrading is a no-brainer, too. :-)

Thanks for a fun meeting, all!

Best,

David

>>>>> "David" == David E Wheeler <david(at)kineticode(dot)com> writes:

David> On Sep 20, 2006, at 09:03, Selena Deckelmann wrote:
>> The general consensus was that she should upgrade. We didn't have a clear
>> answer to why the query was so slow in the older version, but it was clear
>> that it was faster in the newer version of postgresql.

David> Actually, I think that the consensus was that she should try increasing the
David> sort_mem on the 7.4 database to see if that helps. But upgrading is a
David> no-brainer, too. :-)

Does that mean there's no consensus on what the consensus was? Eeek!

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

On Sep 20, 2006, at 10:29 AM, David E. Wheeler wrote:

> On Sep 20, 2006, at 09:03, Selena Deckelmann wrote:
>
>> The general consensus was that she should upgrade. We didn't have a
>> clear answer to why the query was so slow in the older version, but
>> it was clear that it was faster in the newer version of postgresql.
>
> Actually, I think that the consensus was that she should try
> increasing the sort_mem on the 7.4 database to see if that helps. But
> upgrading is a no-brainer, too. :-)

Oh, right! I remember that now. Thanks!

happy to be in consensus for real this time,
-selena

On Sep 20, 2006, at 10:33 AM, Randal L. Schwartz wrote:

>>>>>> "David" == David E Wheeler <david(at)kineticode(dot)com> writes:
>
> David> On Sep 20, 2006, at 09:03, Selena Deckelmann wrote:
>>> The general consensus was that she should upgrade. We didn't have
>>> a clear
>>> answer to why the query was so slow in the older version, but it
>>> was clear
>>> that it was faster in the newer version of postgresql.
>
> David> Actually, I think that the consensus was that she should try
> increasing the
> David> sort_mem on the 7.4 database to see if that helps. But
> upgrading is a
> David> no-brainer, too. :-)
>
> Does that mean there's no consensus on what the consensus was? Eeek!

I think it means that I have a hard time remembering things after two
beers.

-selena

Mr Wheeler wrote:

> Actually, I think that the consensus was that she should try
> increasing the sort_mem on the 7.4 database to see if that
> helps. But upgrading is a no-brainer, too. :-)

Based on my tests today, upgrading is going to be the solution for sure.
On 8.1, the runtime for the problem query is ~5msec.

In the meantime, while I wait to schedule the prod upgrade *cough*, I
tried two things:
1. VACUUM ANALYZE. Duh. This should have been the first thing I
checked. (Forgot to put this test db in the maintenance queue. :$
*slaps self*) This improved things significantly (~2800 msec runtime as
calculated by the planner down to ~50msec. Yay.) This would be why my
indexes weren't being used.

2. I tried doubling sort_mem on my production machine to 2048K. It
made a noticable difference (we're down to ~40msec now, yee-haw). This
had no [visible] detrimental effect on the machine, and since I only
have 14 users & most of my queries are pretty straightforward, I figured
I could keep pushing it. However, further increases of sort_mem didn't
make any difference (I tried up to 16384), so I left it at 2048.

> Thanks for a fun meeting, all!
\e! \e! I used it today!

- gabrielle -
"5 out of 4 people have a problem with math."

On Sep 20, 2006, at 14:40, Roth, Gabrielle wrote:

> In the meantime, while I wait to schedule the prod upgrade *cough*, I
> tried two things:
> 1. VACUUM ANALYZE. Duh. This should have been the first thing I
> checked. (Forgot to put this test db in the maintenance queue. :$
> *slaps self*) This improved things significantly (~2800 msec
> runtime as
> calculated by the planner down to ~50msec. Yay.) This would be
> why my
> indexes weren't being used.

Oh, right. Duh. That's almost always the first thing I do when I have
query problems, actually.

> 2. I tried doubling sort_mem on my production machine to 2048K. It
> made a noticable difference (we're down to ~40msec now, yee-haw).
> This
> had no [visible] detrimental effect on the machine, and since I only
> have 14 users & most of my queries are pretty straightforward, I
> figured
> I could keep pushing it. However, further increases of sort_mem
> didn't
> make any difference (I tried up to 16384), so I left it at 2048.

Good analyzing the issue! 40ms must be quite respectable after
2800ms, eh?

>> Thanks for a fun meeting, all!
> \e! \e! I used it today!

:-)

Best,

David