Re: Bad plan by Planner (Already resolved?)

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: Bad plan by Planner (Already resolved?)
Дата
Msg-id 4E9D2ECA0200002500042131@gw.wicourts.gov
обсуждение исходный текст
Ответ на Bad plan by Planner (Already resolved?)  (Robins Tharakan <robins.tharakan@comodo.com>)
Ответы Re: Bad plan by Planner (Already resolved?)  (Robins Tharakan <robins.tharakan@comodo.com>)
Список pgsql-performance
Robins Tharakan  wrote:

> I'll try to answer in-line.

Thanks; that's the preferred style on PostgreSQL lists.

> On 10/17/2011 09:32 PM, Kevin Grittner wrote:
>> First off, did you use pg_upgrade from an earlier major release?
>> If so, be sure you've dealt with this issue:
> Although I joined recently, I doubt whether pg_upgrade was used
> here. And this doesn't look like the issue either. There are no
> data loss issues and this seems primarily a planner specific bug.

The data loss doesn't happen until transaction ID wraparound -- so if
you had used pg_upgrade to get to where you are, and not used the
recovery techniques I pointed to, you could suddenly start losing
data at a time long after the conversion.  Since you're on a version
which came out before that was discovered I thought it would be
friendly to try to save you that trouble; but if you're sure you're
not in a vulnerable state, that's great.

>> Since it's arguably in your best interest to update at least to
>> 8.4.9 anyway, the easiest way to get your answer might be to do so
>> and test it.

> Frankly, its slightly difficult to just try out versions. DB>1Tb
> and getting that kind of resources to just try out versions for a
> query is not that simple. Hope you would understand.

That I don't understand.  We have found that it takes no longer to
upgrade to a new minor release on a 2.5 TB database cluster than on a
tiny 300 MB cluster.  (With pg_upgrade, it only takes five or ten
minutes of down time to upgrade a new *major* release on a multi-TB
database, but that's not what we're talking about to get to 9.4.9.)

We build from source, and we include the minor release number in the
prefix for the build, so we can have both old and new software
installed side-by-side.  The path for the client-side executables we
do through a symbolic link, so we can switch that painlessly.  And we
assign the prefix used for the server to an environment variable in
our services script.  So here is our process:

 - Build and install the new minor release.
 - Change the symlink to use it for clients (like pg_dump and psql).
 - Change the service script line that sets the prefix to point to
   the new minor release.
 - Run the service script with "stop" and then run the service script
   with "start".  (Unless your service script does a restart by using
   stop and start, don't run it with "restart", because a PostgreSQL
   restart won't pick up the new executables.)

There is literally no more down time than it takes to stop and start
the database service.  Our client software retries on a broken
connection, so we can even do this while users are running and they
just get a clock for a few seconds; but we usually prefer not to
cause even that much disruption, at least during normal business
hours.  We have enough hardware to load balance off of one machine at
a time to do this without interruption of service.

There are sometimes bugs fixed in a minor release which require
cleanup of possibly damaged data, like what I mentioned above.  You
may need to vacuum or reindex something to recover from the damage
caused by the now-fixed bug, but the alternative is to continue to
run with the damage.  I don't understand why someone would knowingly
choose that.

Really, it is worthwhile to keep up on minor releases.

http://www.postgresql.org/support/versioning

Perhaps the difference is that you feel I'm suggesting that you
upgrade in order to see if performance improves.  I'm not.  I'm
suggesting that you upgrade to get the bug fixes and security fixes.
After the upgrade, it would make sense to see if it also fixed your
performance problem.

> I have the workaround implemented, but just wanted to be sure that
> this is accommodated in a newer version.

You've already gotten feedback on that; I don't have anything to add
there.

-Kevin

В списке pgsql-performance по дате отправления:

Предыдущее
От: Mark Kirkwood
Дата:
Сообщение: Re: Bad plan by Planner (Already resolved?)
Следующее
От: kzsolt
Дата:
Сообщение: Heavy contgnous load