Обсуждение: Uber migrated from Postgres to MySQL

Поиск
Список
Период
Сортировка

Uber migrated from Postgres to MySQL

От
Guyren Howe
Дата:
Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons.

https://eng.uber.com/mysql-migration/

Thoughts?

Re: Uber migrated from Postgres to MySQL

От
"Joshua D. Drake"
Дата:
On 07/26/2016 10:39 AM, Guyren Howe wrote:
> Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons.
>
> https://eng.uber.com/mysql-migration/
>
> Thoughts?

Hello,

I started a thread about hackers on this.

Sincerely,

JD


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


Re: Uber migrated from Postgres to MySQL

От
Dorian Hoxha
Дата:

On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons.

https://eng.uber.com/mysql-migration/

Thoughts?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Uber migrated from Postgres to MySQL

От
Rakesh Kumar
Дата:
This is an old news. They are using mysql as a nosql to store schemaless. Basically one giant blob col. And thats where
therole of mysql ends. The bulk of the processing will be in nosql.  



Re: Uber migrated from Postgres to MySQL

От
"Joshua D. Drake"
Дата:
On 07/26/2016 02:49 PM, Rakesh Kumar wrote:
> This is an old news. They are using mysql as a nosql to store schemaless. Basically one giant blob col. And thats
wherethe role of mysql ends. The bulk of the processing will be in nosql. 

That doesn't mean they didn't bring up some very good points.

JD


>
>
>


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


Re: Uber migrated from Postgres to MySQL

От
Rakesh Kumar
Дата:

--
Sent from mobile.

On Jul 26, 2016, at 5:56 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

> On 07/26/2016 02:49 PM, Rakesh Kumar wrote:
> This is an old news. They are using mysql as a nosql to store schemaless. Basically one giant blob col. And thats
wherethe role of mysql ends. The bulk of the processing will be in nosql. 

That doesn't mean they didn't bring up some very good points.

JD

Yes of course. But this is not one of those "product A sucked and we moved to product B and same workload and work is
now10 times better. " 



--
Command Prompt, Inc.                  http://the.postgres.company/
                       +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


Re: Uber migrated from Postgres to MySQL

От
Condor
Дата:
On 26-07-2016 21:04, Dorian Hoxha wrote:
> Many comments: https://news.ycombinator.com/item?id=12166585
> https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
>
> On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
>
>> Honestly, I've never heard of anyone doing that. But it sounds like
>> they had good reasons.
>>
>> https://eng.uber.com/mysql-migration/
>>
>> Thoughts?
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general


They are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade
to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary
and library, need to do full dump and restore that take time and disk
space.


Regards,
Hristo S.





Re: Uber migrated from Postgres to MySQL

От
Chris Travers
Дата:
Just a few points on reading this.

First, the timeline bugs regarding replication (particularly iirc in the 9.1 days).  I remember accidentally corrupting a (fortunately only demonstration!) database cluster in the process of demonstrating promotion at least once.  Iirc last time I tried to reproduce these problems, they had been fixed (by 9.3?).

The replication section made me wonder though if they were using the right replication solution for the job.  If you don't want an on-disk copy, don't use physical replication.  This being said there is one serious issue here that is worth mentioning, which is that since autovacuum on the master has no knowledge of autovacuum on the slave, it is easy to have longer-running queries on a slave that have rows they need to see removed by autovacuum and replication.  This can of course be easily fixed (if your query takes 30 sec to run, every 30 sec open a minute-long transaction on the master, which means that autovacuum can never clean rows that are older than 30 sec) but such is not a very robust solution and may cause more problems than it is worth (the real solution is going to a logical replication system where that is a problem).  As I usually put it, streaming replication is for cases where you need to guarantee an exact replica of everything, while logical replication is where you need a copy of data for use.

Finally, if I were trying to create something like schemaless, there is one major limitation of PostgreSQL that is not mentioned here, which is TOAST overhead.  I have seen people try to do things like this and TOAST overhead can be a real problem in these cases.  If your data for a row won't easily fit in significantly less than a page, then every read of that data and every write can effectively do an implicit nested loop join.  And if you want to talk about write amplification......  But this is also very well hidden and not easy to measure unless you know to look for it specifically so it is possible that they ran into it and didn't know it but I don't have any knowledge of what they did or tried so I could be totally off base here.  I would say I have seen more than one project run into this and because explain analyze select * does not detoast....

All of the above being said, there are solutions to all the major problems.  But you have to know about them, where to look, and what to do.  And with higher scale, one very important aspect is that attention to detail starts to matter a whole lot.  I agree that there are some good points raised but I wonder what the solutions are.  There is room for some improvement in the backend (it would really be nice to instrument and measure toasting/detoasting overhead in explain analyze) but for a lot of these  I wonder if that is secondary.   PostgreSQL is very well optimized for a certain series of tasks, and one can build well optimized solutions well outside that.  At a certain point (including a certain scale) therewill be no substitute for a teamof people who really know the db backend inside and out who can design around limitations and I think that is true for all databases I have worked with.

On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons.

https://eng.uber.com/mysql-migration/

Thoughts?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

Re: Uber migrated from Postgres to MySQL

От
Marc Fournier
Дата:

On Jul 27, 2016, at 00:15, Condor <condor@stz-bg.com> wrote:

On 26-07-2016 21:04, Dorian Hoxha wrote:
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like
they had good reasons.
https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


They are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary and library, need to do full dump and restore that take time and disk space.

Not having tried to use pg_upgrade on a 1TB database, this might not apply, but pg_upgrade has a —link option that should greatly minimize the time required to upgrade, since it eliminates the duplication of files … apparently even works on Windows:

“—link … use hard links instead of copying files to the new cluster (use junction points on Windows)"

In fact, the —link option should also mean removing the requirement for ‘double the disk space’ to do the upgrade …

I don’t have a 1TB database to try it on, mind you, so your ‘wait couple of days’ might be *with* the —link option?

--
Marc G Fournier                                               http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services

Re: Uber migrated from Postgres to MySQL

От
Achilleas Mantzios
Дата:
On 27/07/2016 10:15, Condor wrote:
> On 26-07-2016 21:04, Dorian Hoxha wrote:
>> Many comments: https://news.ycombinator.com/item?id=12166585
>> https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
>>
>> On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
>>
>>> Honestly, I've never heard of anyone doing that. But it sounds like
>>> they had good reasons.
>>>
>>> https://eng.uber.com/mysql-migration/
>>>
>>> Thoughts?
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>
>
> They are right for upgrades.
> It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade to finish upgrade and meanwhile database is
offline.
> In some distros after upgrade of PG version you don't have old binary and library, need to do full dump and restore
thattake time and disk space. 

Our last 1TB upgrade from 9.0 -> 9.3 went like a charm in something like seconds. (with the -k option)
However, be warned that the planing and testing took one full week.

>
>
> Regards,
> Hristo S.
>
>
>
>
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: Uber migrated from Postgres to MySQL

От
Scott Mead
Дата:
On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 27/07/2016 10:15, Condor wrote:
On 26-07-2016 21:04, Dorian Hoxha wrote:
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/

On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:

Honestly, I've never heard of anyone doing that. But it sounds like
they had good reasons.

https://eng.uber.com/mysql-migration/

Thoughts?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


They are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary and library, need to do full dump and restore that take time and disk space.

Our last 1TB upgrade from 9.0 -> 9.3 went like a charm in something like seconds. (with the -k option)
However, be warned that the planing and testing took one full week.

That being said, it doesn't really provide a back-out plan.  The beauty of replication is that you can halt the upgrade at any point if need be and cut your (hopefully small) losses. If you use -k, you are all in.  Sure, you could setup a new standby, stop traffic, upgrade whichever node you'd like (using -k) and still have the other ready in the event of total catastrophe.  More often than not, I see DBAs and sysads lead the conversation with "well, postgres can't replicate from one version to another, so instead.... " followed by a fast-glazing of management's eyes and a desire to buy a 'commercial database'. 

All in all, Evan's blog seemed to start out decently technical, it quickly took a turn with half-truths, outdated information and, in some cases, downright fud:

 "The bug we ran into only affected certain releases of Postgres 9.2 and has been fixed for a long time now. However, we still find it worrisome that this class of bug can happen at all. A new version of Postgres could be released at any time that has a bug of this nature, and because of the way replication works, this issue has the potential to spread into all of the databases in a replication hierarchy."


ISTM that they needed a tire swing and were using a dump truck.  Hopefully they vectored somewhere in the middle and got themselves a nice sandbox.

--Scott  
 



Regards,
Hristo S.







--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
--
Scott Mead
Sr. Architect
OpenSCG

Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 10:22:27AM -0400, Scott Mead wrote:
> That being said, it doesn't really provide a back-out plan.  The beauty of
> replication is that you can halt the upgrade at any point if need be and cut
> your (hopefully small) losses. If you use -k, you are all in.  Sure, you could
> setup a new standby, stop traffic, upgrade whichever node you'd like (using -k)
> and still have the other ready in the event of total catastrophe.  More often
> than not, I see DBAs and sysads lead the conversation with "well, postgres
> can't replicate from one version to another, so instead.... " followed by a
> fast-glazing of management's eyes and a desire to buy a 'commercial database'. 

I agree, but I am not sure how to improve it.  The big complaint I have
heard is that once you upgrade and open up writes on the upgraded
server, you can't re-apply those writes to the old server if you need to
fall back to the old server.  I also don't see how to improve that either.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Geoff Winkless
Дата:
On 27 July 2016 at 15:22, Scott Mead <scottm@openscg.com> wrote:
 "The bug we ran into only affected certain releases of Postgres 9.2 and has been fixed for a long time now. However, we still find it worrisome that this class of bug can happen at all. A new version of Postgres could be released at any time that has a bug of this nature, and because of the way replication works, this issue has the potential to spread into all of the databases in a replication hierarchy."


ISTM that they needed a tire swing and were using a dump truck.  Hopefully they vectored somewhere in the middle and got themselves a nice sandbox.
 
At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in an initial refusal to accept any problem existed, followed by (once that particular strategy had run out of steam) the developer simply ignoring the bug until it was closed automatically by their bug system. As far as I'm aware those bugs still exist in the most recent version.

Admittedly they weren't data-corruption bugs but they were of the "this will cause your application to unnecessarily come to a complete halt for a couple of hours once your dataset grows beyond a certain point" variety.

As others have pointed out, db changes very rarely happen because of technical reasons. Most developers will harp on at their boss about how terrible their current database is and how <preferred database> performs much better. Eventually one of two things happens: either a) those developers end up in a position where their direct boss is in a position to make the change and he or she doesn't understand how much time and money it will actually take to change; or b) commercial considerations dictate the change.

The amount of money and time they've wasted making this change (and whatever anyone tells you these things never come for free) would have been better invested in employing one of the commercial PG companies to improve the specific postgres problems they found. The fact that they decided to go the MySQL route suggests to me that this was a political, not technical, change, and they're now reverse-justifying.

For what it's worth, from what I've read uber are a company whose very business plan relies on them taking things that they don't deserve while they treat customers and employees with similar levels of arrogance. Frankly I'd rather there were as many levels of separation as possible between me and them: they and Oracle are welcome to each other, it seems like a marriage made in heaven.

Geoff​

Re: Uber migrated from Postgres to MySQL

От
Andrew Sullivan
Дата:
On Wed, Jul 27, 2016 at 04:51:42PM +0100, Geoff Winkless wrote:
> technical reasons. Most developers will harp on at their boss about how
> terrible their current database is and how <preferred database> performs
> much better. Eventually one of two things happens: either a) those
> developers end up in a position where their direct boss is in a position to
> make the change and he or she doesn't understand how much time and money it
> will actually take to change; or b) commercial considerations dictate the
> change.

In a different context, someone suggested to me that Postgres
advocates sounded to him too often like FreeBSD advocates complaining
about Linux, and I'm afraid there is a certain truth to that.  Given
the discussion in the post in question, the decision to use MySQL
appears to have been well-justified:

    1.  They'd decided to use a NoSQL database and ditch relational
    systems, because shards.

    2.  They wanted an MVCC engine behind the above.

    3.  They wanted SQL semantics to this MVCC-enabled filesystem layer.

Sounds just like MySQL+InnoDB to me.  Once you've already decided on
(1), the rest of it flows pretty naturally and Postgres is probably
not your choice.  You can dismiss any of 1-3 as commerical or
political advocacy, but while I happen to think they're a somewhat
questionable set of goals they're not obviously stupid, and
competent people of good will could disagree about them.

At the same time, there really are two serious problems with Postgres
under heavy write loads.  Postgres's focus on readers' speed and
convenience means you have to take the hit somewhere, so writers take
it instead.  (The other side of the disk-layout description in the
blog post is that, under MySQL, secondary index use is more expensive
for readers than it is in Postgres.  The post acknowledges that, but
of course most important secondary indexing is useless under sharding
anyway, since you have to select from shards; so they won't care.)
I/O storms on Postgres are a major source of pain for large operators,
and the tools for understanding are sort of primitive because many of
them depend on underlying OS features and tools.

The second is the upgrade-by-replica-and-fallback-plan problem.  It's
really an issue. There is a reason that, back in the cloudy past, we
designed Slony to be able to replicate to and from any supported
version of Postgres: Afilias needed to be able to upgrade without a
lot of down time and with the ability to roll back if we had to,
because that was our contractual obligation.  This has always been a
large gap, and when it was raised in the past the answer was, "Well,
Slony can already do that so use it."  It wasn't too satisfying then,
and it's not much more satisfying now. :)

> better invested in employing one of the commercial PG companies to improve
> the specific postgres problems they found.

I think the two big problems laid out above are deep architectural
ones.  I'm not sure these are the sort of improvement you can buy
without getting the community on board.

> For what it's worth, from what I've read uber are a company whose very
> business plan relies on them taking things that they don't deserve while
> they treat customers and employees with similar levels of arrogance.

Nothin' for nothin', but I don't think it helps Postgres to attack
others' business plans -- whatever one thinks of them -- as part of an
argument about why Postgres is the right tool for a given job.

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca


Re: Uber migrated from Postgres to MySQL

От
Rakesh Kumar
Дата:
On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <bruce@momjian.us> wrote:

> I agree, but I am not sure how to improve it.  The big complaint I have
> heard is that once you upgrade and open up writes on the upgraded
> server, you can't re-apply those writes to the old server if you need to
> fall back to the old server.  I also don't see how to improve that either.

doesn't and pg_logical solve this by logically replicating and allowing for
different architecture/version between the replication nodes ?


Re: Uber migrated from Postgres to MySQL

От
Geoff Winkless
Дата:
On 27 July 2016 at 17:11, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
Given
the discussion in the post in question, the decision to use MySQL
appears to have been well-justified:

​Well yes, but that's pretty-much the point of back-justification, isn't it?

[snip a whole bunch of good points]
 
> For what it's worth, from what I've read uber are a company whose very
> business plan relies on them taking things that they don't deserve while
> they treat customers and employees with similar levels of arrogance.

Nothin' for nothin', but I don't think it helps Postgres to attack
others' business plans -- whatever one thinks of them -- as part of an
argument about why Postgres is the right tool for a given job.

​Oh, I wasn't using as an argument about anything (hence "for what it's worth").​

G

On 27 July 2016 at 17:11, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
On Wed, Jul 27, 2016 at 04:51:42PM +0100, Geoff Winkless wrote:
> technical reasons. Most developers will harp on at their boss about how
> terrible their current database is and how <preferred database> performs
> much better. Eventually one of two things happens: either a) those
> developers end up in a position where their direct boss is in a position to
> make the change and he or she doesn't understand how much time and money it
> will actually take to change; or b) commercial considerations dictate the
> change.

In a different context, someone suggested to me that Postgres
advocates sounded to him too often like FreeBSD advocates complaining
about Linux, and I'm afraid there is a certain truth to that.  Given
the discussion in the post in question, the decision to use MySQL
appears to have been well-justified:

    1.  They'd decided to use a NoSQL database and ditch relational
    systems, because shards.

    2.  They wanted an MVCC engine behind the above.

    3.  They wanted SQL semantics to this MVCC-enabled filesystem layer.

Sounds just like MySQL+InnoDB to me.  Once you've already decided on
(1), the rest of it flows pretty naturally and Postgres is probably
not your choice.  You can dismiss any of 1-3 as commerical or
political advocacy, but while I happen to think they're a somewhat
questionable set of goals they're not obviously stupid, and
competent people of good will could disagree about them.

At the same time, there really are two serious problems with Postgres
under heavy write loads.  Postgres's focus on readers' speed and
convenience means you have to take the hit somewhere, so writers take
it instead.  (The other side of the disk-layout description in the
blog post is that, under MySQL, secondary index use is more expensive
for readers than it is in Postgres.  The post acknowledges that, but
of course most important secondary indexing is useless under sharding
anyway, since you have to select from shards; so they won't care.)
I/O storms on Postgres are a major source of pain for large operators,
and the tools for understanding are sort of primitive because many of
them depend on underlying OS features and tools.

The second is the upgrade-by-replica-and-fallback-plan problem.  It's
really an issue. There is a reason that, back in the cloudy past, we
designed Slony to be able to replicate to and from any supported
version of Postgres: Afilias needed to be able to upgrade without a
lot of down time and with the ability to roll back if we had to,
because that was our contractual obligation.  This has always been a
large gap, and when it was raised in the past the answer was, "Well,
Slony can already do that so use it."  It wasn't too satisfying then,
and it's not much more satisfying now. :)

> better invested in employing one of the commercial PG companies to improve
> the specific postgres problems they found.

I think the two big problems laid out above are deep architectural
ones.  I'm not sure these are the sort of improvement you can buy
without getting the community on board.

> For what it's worth, from what I've read uber are a company whose very
> business plan relies on them taking things that they don't deserve while
> they treat customers and employees with similar levels of arrogance.

Nothin' for nothin', but I don't think it helps Postgres to attack
others' business plans -- whatever one thinks of them -- as part of an
argument about why Postgres is the right tool for a given job.

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote:
> On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> > I agree, but I am not sure how to improve it.  The big complaint I have
> > heard is that once you upgrade and open up writes on the upgraded
> > server, you can't re-apply those writes to the old server if you need to
> > fall back to the old server.  I also don't see how to improve that either.
>
> doesn't and pg_logical solve this by logically replicating and allowing for
> different architecture/version between the replication nodes ?

Yes.  I was saying I don't know how to improve pg_upgrade to address it.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Rakesh Kumar
Дата:
On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian <bruce@momjian.us> wrote:

> Yes.  I was saying I don't know how to improve pg_upgrade to address it.

This problem is there even in oracle/db2/sqlserver. None of them allow
rollback to the lower version
unless it is a minor version upgrade. Major version upgrade almost
definitely involves change in transaction log
(WAL) structure and hence no rollback.


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 12:51:40PM -0400, Rakesh Kumar wrote:
> On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> > Yes.  I was saying I don't know how to improve pg_upgrade to address it.
>
> This problem is there even in oracle/db2/sqlserver. None of them allow
> rollback to the lower version
> unless it is a minor version upgrade. Major version upgrade almost
> definitely involves change in transaction log
> (WAL) structure and hence no rollback.

Oh, good to know.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Chris Travers
Дата:


On Wed, Jul 27, 2016 at 4:22 PM, Scott Mead <scottm@openscg.com> wrote:
On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 27/07/2016 10:15, Condor wrote:
On 26-07-2016 21:04, Dorian Hoxha wrote:
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/

On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:

Honestly, I've never heard of anyone doing that. But it sounds like
they had good reasons.

https://eng.uber.com/mysql-migration/

Thoughts?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


They are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary and library, need to do full dump and restore that take time and disk space.

Our last 1TB upgrade from 9.0 -> 9.3 went like a charm in something like seconds. (with the -k option)
However, be warned that the planing and testing took one full week.

That being said, it doesn't really provide a back-out plan.  The beauty of replication is that you can halt the upgrade at any point if need be and cut your (hopefully small) losses.

Replication though does have limits and one aspect of incremental backups is you cannot restore from one major version to the next.  Another one I think they obliquely referred to (in the subtle problems section) was the fact that if you have longer-running queries on the replica with a lot of updates, you can get funny auto-vacuum-induced errors (writes from autovacuum on the master can interrupt queries on the slave).  BTW if there is interest in what could be done for that, something which allows autovacuum to decide how long to wait before cleaning up dead tuples would be a great enhancement.

I was on a project once where I was told, "we use pg_dump for our upgrades" for a multi-TB database.  When asked why, the answer made a lot of sense.  Namely if something goes wrong you need to do a restore on the new version from a logical backup anyway, so you have to take a pg_dump backup before you start, and you might have to restore anyway.  So the thinking was  that it was better to keep expectations low than promise low downtime and have a two-week outage.
 
If you use -k, you are all in.  Sure, you could setup a new standby, stop traffic, upgrade whichever node you'd like (using -k) and still have the other ready in the event of total catastrophe.  More often than not, I see DBAs and sysads lead the conversation with "well, postgres can't replicate from one version to another, so instead.... " followed by a fast-glazing of management's eyes and a desire to buy a 'commercial database'. 

This is one area where we need better presentation of what we have and what it does.

Streaming replication works great for certain things, such as where you have lots of small queries against the replica, where they don't have to be absolutely up to date, or where what you are really after is guarantees that you can keep moving after one of your servers suffers a catastrophic failure.

Where the guarantee that the two systems are guaranteed identical on the filesystem level, it is great.  Where that is not what you want, it is a pretty bad solution.  But then there is Slony, Bucardo, and other logical replication solutions out there (plus the newer logical replication approaches in PostgreSQL) which handle the other situations very well (with a very different sort of added complexity).
 

All in all, Evan's blog seemed to start out decently technical, it quickly took a turn with half-truths, outdated information and, in some cases, downright fud:

 "The bug we ran into only affected certain releases of Postgres 9.2 and has been fixed for a long time now. However, we still find it worrisome that this class of bug can happen at all. A new version of Postgres could be released at any time that has a bug of this nature, and because of the way replication works, this issue has the potential to spread into all of the databases in a replication hierarchy."


ISTM that they needed a tire swing and were using a dump truck.  Hopefully they vectored somewhere in the middle and got themselves a nice sandbox.

My first thought was, "If they know the database that well, surely they could have built something that would work well!"

However, for what they seem to want to do specifically, MySQL might not actually be a bad choice.  In a case like what they are doing, nearly all of your lookups are probably simple, primary key lookups and there InnoDB's design helps more than it hurts.  If I were to think of one are that MySQL probably would do better, it would be looking up documents based on simple primary key searches (no joins, no relational math, no need for complex plans, just a single primary key index lookup).  But this is also a reason we might not want to worry about this sort of thing too much.  Of course NFS might be another alternative at that level of complexity....

So yeah, a sandbox ;-)
 

--Scott  
 



Regards,
Hristo S.







--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
--
Scott Mead
Sr. Architect
OpenSCG



--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 12:47:24PM -0400, Bruce Momjian wrote:
> On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote:
> > On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <bruce@momjian.us> wrote:
> >
> > > I agree, but I am not sure how to improve it.  The big complaint I have
> > > heard is that once you upgrade and open up writes on the upgraded
> > > server, you can't re-apply those writes to the old server if you need to
> > > fall back to the old server.  I also don't see how to improve that either.
> >
> > doesn't and pg_logical solve this by logically replicating and allowing for
> > different architecture/version between the replication nodes ?
>
> Yes.  I was saying I don't know how to improve pg_upgrade to address it.

I think long-term we are looking at pg_logical for zero-downtime
upgrades and _downgrades_, and pg_upgrade for less overhead (I don't
want to make a second copy of my data) upgrades (but not downgrades).

I think this is probably the best we are going to be able to do for a
long time.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 09:17:58AM +0200, Chris Travers wrote:
> The replication section made me wonder though if they were using the right
> replication solution for the job.  If you don't want an on-disk copy, don't use
> physical replication.  This being said there is one serious issue here that is
> worth mentioning, which is that since autovacuum on the master has no knowledge
> of autovacuum on the slave, it is easy to have longer-running queries on a
> slave that have rows they need to see removed by autovacuum and replication. 

Uh, see hot_standby_feedback:

    #hot_standby_feedback = off             # send info from standby to prevent
                                            # query conflicts

> All of the above being said, there are solutions to all the major problems. 
> But you have to know about them, where to look, and what to do.  And with
> higher scale, one very important aspect is that attention to detail starts to
> matter a whole lot.  I agree that there are some good points raised but I
> wonder what the solutions are.  There is room for some improvement in the
> backend (it would really be nice to instrument and measure toasting/detoasting
> overhead in explain analyze) but for a lot of these  I wonder if that is
> secondary.   PostgreSQL is very well optimized for a certain series of tasks,
> and one can build well optimized solutions well outside that.  At a certain
> point (including a certain scale) therewill be no substitute for a teamof
> people who really know the db backend inside and out who can design around
> limitations and I think that is true for all databases I have worked with.

Watching the video was helpful:

    https://vimeo.com/145842299

You can see the failover happened because of various user errors.  That
doesn't excuse our bug, but I am not sure exactly how much they
understood of Postgres behavior.  His talk near the end about the
replication infrastucture being exposed to them was also interesting.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 12:59:59PM -0400, Bruce Momjian wrote:
> On Wed, Jul 27, 2016 at 12:47:24PM -0400, Bruce Momjian wrote:
> > On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote:
> > > On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <bruce@momjian.us> wrote:
> > >
> > > > I agree, but I am not sure how to improve it.  The big complaint I have
> > > > heard is that once you upgrade and open up writes on the upgraded
> > > > server, you can't re-apply those writes to the old server if you need to
> > > > fall back to the old server.  I also don't see how to improve that either.
> > >
> > > doesn't and pg_logical solve this by logically replicating and allowing for
> > > different architecture/version between the replication nodes ?
> >
> > Yes.  I was saying I don't know how to improve pg_upgrade to address it.
>
> I think long-term we are looking at pg_logical for zero-downtime
> upgrades and _downgrades_, and pg_upgrade for less overhead (I don't
> want to make a second copy of my data) upgrades (but not downgrades).
>
> I think this is probably the best we are going to be able to do for a
> long time.

Oh, let me give credit to Simon, who has always seen pg_logical as
providing superior upgrade options where the logical replication setup
isn't a problem.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Vik Fearing
Дата:
On 27/07/16 18:54, Chris Travers wrote:
> Another one I think they obliquely referred to (in the subtle problems
> section) was the fact that if you have longer-running queries on the
> replica with a lot of updates, you can get funny auto-vacuum-induced
> errors (writes from autovacuum on the master can interrupt queries on
> the slave).  BTW if there is interest in what could be done for that,
> something which allows autovacuum to decide how long to wait before
> cleaning up dead tuples would be a great enhancement.

You mean something like hot_standby_feedback?

https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Uber migrated from Postgres to MySQL

От
Marc Fournier
Дата:

On Jul 27, 2016, at 09:59, Bruce Momjian <bruce@momjian.us> wrote:

I think long-term we are looking at pg_logical for zero-downtime
upgrades and _downgrades_, and pg_upgrade for less overhead (I don't
want to make a second copy of my data) upgrades (but not downgrades).

I think this is probably the best we are going to be able to do for a
long time.

Stupid question here, but do we provide any less then what MySQL does?   I’m reading:


==

       Unless otherwise documented, the following downgrade paths are supported:

  • Downgrading from a release series version to an older release series version is supported using all downgrade methods. For example, downgrading from 5.7.10 to 5.7.9 is supported. Skipping release series versions is also supported. For example, downgrading from 5.7.11 to 5.7.9 is supported.

  • Downgrading one release level is supported using the logical downgrade method. For example, downgrading from 5.7 to 5.6 is supported.

  • Downgrading more than one release level is supported using the logical downgrade method, but only if you downgrade one release level at a time. For example, you can downgrade from 5.7 to 5.6, and then to 5.5. 

==

So, downgrade minor releases can be done by just changing the binaries … downgrading an older ‘major release’ requires a dump/reload … 

Unless I’m missing something, whether on PostgreSQL or MySQL, if you want to go back a major release, you would need to dump./ reload that 1TB database …


--
Marc G Fournier                                               http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services

Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 10:54:25AM -0700, Marc Fournier wrote:
> http://dev.mysql.com/doc/refman/5.7/en/downgrading.html#downgrade-paths
>
> ==
>
>        Unless otherwise documented, the following downgrade paths are
> supported:
>
>   • Downgrading from a release series version to an older release series
>     version is supported using all downgrade methods. For example, downgrading
>     from 5.7.10 to 5.7.9 is supported. Skipping release series versions is also
>     supported. For example, downgrading from 5.7.11 to 5.7.9 is supported.
>
>   • Downgrading one release level is supported using the logical downgrade
>     method. For example, downgrading from 5.7 to 5.6 is supported.
>
>   • Downgrading more than one release level is supported using the logical
>     downgrade method, but only if you downgrade one release level at a time.
>     For example, you can downgrade from 5.7 to 5.6, and then to 5.5.
>
> ==
>
> So, downgrade minor releases can be done by just changing the binaries …
> downgrading an older ‘major release’ requires a dump/reload …
>
> Unless I’m missing something, whether on PostgreSQL or MySQL, if you want to go
> back a major release, you would need to dump./ reload that 1TB database …

What they wanted, and I think was mentioned in the document, was that
they wanted to upgrade the slaves independently, then the master.  I
think MySQL supports that, Postgres doesn't.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Rakesh Kumar
Дата:
On Wed, Jul 27, 2016 at 1:54 PM, Marc Fournier
<marc.fournier@2ndquadrant.com> wrote:

> Stupid question here, but do we provide any less then what MySQL does?   I’m
> reading:

mysql provides same functionality for rollback like oracle/db2
provides. That is,
rollback on a minor version upgrade possible, but not on major version upgrade.

I am surprised PG does not even allow minor version rollback.


Re: Uber migrated from Postgres to MySQL

От
Andrew Sullivan
Дата:
On Wed, Jul 27, 2016 at 01:58:25PM -0400, Rakesh Kumar wrote:
>
> I am surprised PG does not even allow minor version rollback.

It almost never happens that a minor version (N.M.x, x is minor)
requires an upgrade at all.  Change your binaries and you're done.
Catalogue incompatibility historically was the basis for something
becoming a major version upgrade.  (I can recall a couple bugs where
you had to tickle the catalogues, so it's not exactly true that
they're never incompatible, but it's incredibly rare.)

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 01:58:25PM -0400, Rakesh Kumar wrote:
> On Wed, Jul 27, 2016 at 1:54 PM, Marc Fournier
> <marc.fournier@2ndquadrant.com> wrote:
>
> > Stupid question here, but do we provide any less then what MySQL does?   I’m
> > reading:
>
> mysql provides same functionality for rollback like oracle/db2
> provides. That is,
> rollback on a minor version upgrade possible, but not on major version upgrade.
>
> I am surprised PG does not even allow minor version rollback.

Uh, I thought we did.  Perhaps there a few that didn't.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Rakesh Kumar
Дата:
On Wed, Jul 27, 2016 at 2:07 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:

> It almost never happens that a minor version (N.M.x, x is minor)
> requires an upgrade at all.  Change your binaries and you're done.
> Catalogue incompatibility historically was the basis for something
> becoming a major version upgrade.  (I can recall a couple bugs where
> you had to tickle the catalogues, so it's not exactly true that
> they're never incompatible, but it's incredibly rare.)

Yeah good enough.


Re: Uber migrated from Postgres to MySQL

От
Kevin Grittner
Дата:
On Wed, Jul 27, 2016 at 2:15 AM, Condor <condor@stz-bg.com> wrote:

> They are right for upgrades.
> It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade to
> finish upgrade and meanwhile database is offline.

What?  I had a cluster over 3TB and it was offline for only 10
minutes doing a major version upgrade using pg_upgrade's --link
option.  Of course, that was with minimal statistics -- just enough
to keep from getting truly stupid plans.  If it takes *days* to run
pg_upgrade on a 1TB cluster either you have an insane number of
database objects or you are not taking advantage of the available
features.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Uber migrated from Postgres to MySQL

От
Kevin Grittner
Дата:
On Wed, Jul 27, 2016 at 9:22 AM, Scott Mead <scottm@openscg.com> wrote:
> On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

>> Our last 1TB upgrade from 9.0 -> 9.3 went like a charm in something like
>> seconds. (with the -k option)
>> However, be warned that the planing and testing took one full week.

Agreed -- you should spend a lot more time on planning and testing
than the actual upgrade will take; but that will probably be true
with any product.

> That being said, it doesn't really provide a back-out plan.

Until you get to the end of the upgrade and *start the cluster
under the new version*  you can fall back to the old version.  I
remember a couple times that we saw something during a pg_upgrade
--link run that we weren't expecting, and did exactly that so we
could investigate and try again later.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Uber migrated from Postgres to MySQL

От
Andrew Sullivan
Дата:
On Wed, Jul 27, 2016 at 02:33:54PM -0500, Kevin Grittner wrote:
> Until you get to the end of the upgrade and *start the cluster
> under the new version*  you can fall back to the old version.

Yeah, but to be fair a lot of well-funded businesses (note what
started this discussion) are pico-managed by people way up the stack
who want a bite-sized answer.  If you do not have a happy story for,
"What if we're 48 hours into the upgrade and discover some critical
bug corner case that makes us need to roll back?" then you're going to
lose them.  Never mind that such cases literally never happen (if you
have a 48 hour old bug in an Internet system today, you have an
emergency bugfix, not a rollback).

A great deal of practical delivery of technology involves managing
expectations of management who do not understand what they are asking
for and basically want a glib happy answer.  As people delivering such
things, we must find a glib happy answer that does not get us fired if
it turns out to be false.  The poor story Postgres has about
downgrade, _even if it's a stupid problem_, is a problem.  It might
not be worth fixing because it's a stupid problem.  But one has to
face the critique in its own terms.

A

--
Andrew Sullivan
ajs@crankycanuck.ca


Re: Uber migrated from Postgres to MySQL

От
Patrick B
Дата:
We can't use the pg_upgrade in our 3TB database.... just does not work.. that's the main reason we're still using 9.2.

pg_upgrade and not working (was Re: Uber migrated from Postgres to MySQL)

От
Andrew Sullivan
Дата:
I bet there are some people around here who could help you
troubleshoot.  Just sayin'  (I'm not among them.)

A

On Thu, Jul 28, 2016 at 09:34:05AM +1200, Patrick B wrote:
> We can't use the pg_upgrade in our 3TB database.... just does not work..
> that's the main reason we're still using 9.2.

--
Andrew Sullivan
ajs@crankycanuck.ca


Re: Uber migrated from Postgres to MySQL

От
Kevin Grittner
Дата:
On Wed, Jul 27, 2016 at 4:34 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
> We can't use the pg_upgrade in our 3TB database.... just does not work..
> that's the main reason we're still using 9.2.

I think it's safe to say that that has absolutely nothing to do
with the size being 3TB.  They symptoms you report are a little
thin to diagnose the actual cause.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Uber migrated from Postgres to MySQL

От
Patrick B
Дата:
I think it's safe to say that that has absolutely nothing to do
with the size being 3TB.  They symptoms you report are a little
thin to diagnose the actual cause.

might be... we're using SATA disks... and that's a big problem. But still.. the size of the DB is indeed a problem. 

Re: Uber migrated from Postgres to MySQL

От
Alvaro Herrera
Дата:
Patrick B wrote:
> >
> > I think it's safe to say that that has absolutely nothing to do
> > with the size being 3TB.  They symptoms you report are a little
> > thin to diagnose the actual cause.
>
> might be... we're using SATA disks... and that's a big problem. But still..
> the size of the DB is indeed a problem.

Andrew is correct -- the size of the database is not a limitation for
pg_upgrade.  Disk tech is not relevant either.  You may run into the
problem that you don't have enough disk space, but then that is not a
database or pg_upgrade problem, is it?

Other things might cause issues, but since you haven't actually reported
the problem, we don't know what is or whether there is any possible fix.

Then again, if you want to report a pg_upgrade failure, I suggest you
open a thread of your own rather than hijack this one.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 07:02:52PM -0400, Alvaro Herrera wrote:
> Patrick B wrote:
> > >
> > > I think it's safe to say that that has absolutely nothing to do
> > > with the size being 3TB.  They symptoms you report are a little
> > > thin to diagnose the actual cause.
> >
> > might be... we're using SATA disks... and that's a big problem. But still..
> > the size of the DB is indeed a problem.
>
> Andrew is correct -- the size of the database is not a limitation for
> pg_upgrade.  Disk tech is not relevant either.  You may run into the
> problem that you don't have enough disk space, but then that is not a
> database or pg_upgrade problem, is it?
>
> Other things might cause issues, but since you haven't actually reported
> the problem, we don't know what is or whether there is any possible fix.
>
> Then again, if you want to report a pg_upgrade failure, I suggest you
> open a thread of your own rather than hijack this one.

You need only minimal disk space when using pg_upgrade --link.  I agree
we would like a full bug report so we can find a fix for you.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
"Greg Sabino Mullane"
Дата:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Marc wrote:
> I donât have a 1TB database to try it on, mind you, so your
> âwait couple of daysâ might be *with* the âlink option?

I think you mean *without*, but yeah, there is no way the --link
option is going to take that long. Hard links are  awesome. We've upgraded
some really big databases, and --link is really, really fast.
If you can't use --link (usually because you want to get over
the checksum hump), we use something like Bucardo to help out.
No need to ever wait a "couple of days" as OP claims. :)

What really bites is the analyze afterwards. That's the part
that takes too long (yes, --in-stages helps some). Would love
to see progress made there.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201607272220
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAleZbGEACgkQvJuQZxSWSsiAngCgmsP0O9rMMmu43RQ6LkQJwMtP
IZcAn25UJWNs03q75iIEOVAabKHaBrWV
=zdDO
-----END PGP SIGNATURE-----




Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Thu, Jul 28, 2016 at 02:23:18AM -0000, Greg Sabino Mullane wrote:
>
> Marc wrote:
> > I donât have a 1TB database to try it on, mind you, so your
> > âwait couple of daysâ might be *with* the âlink option?
>
> I think you mean *without*, but yeah, there is no way the --link
> option is going to take that long. Hard links are  awesome. We've upgraded
> some really big databases, and --link is really, really fast.
> If you can't use --link (usually because you want to get over
> the checksum hump), we use something like Bucardo to help out.
> No need to ever wait a "couple of days" as OP claims. :)
>
> What really bites is the analyze afterwards. That's the part
> that takes too long (yes, --in-stages helps some). Would love
> to see progress made there.

Agreed.  I will try to prioritize it.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Jeff Janes
Дата:
On Wed, Jul 27, 2016 at 7:23 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:

> Marc wrote:
>> I donât have a 1TB database to try it on, mind you, so your
>> âwait couple of daysâ might be *with* the âlink option?
>
> I think you mean *without*, but yeah, there is no way the --link
> option is going to take that long.

That depends on how how many objects there are consuming that 1 TB.
With millions of small objects, you will have problems.  Not as many
in 9.5 as there were in 9.1, but still it does not scale linearly in
the number of objects.  If you only have thousands of objects, then as
far as I know -k works like a charm.

> Hard links are  awesome. We've upgraded
> some really big databases, and --link is really, really fast.
> If you can't use --link (usually because you want to get over
> the checksum hump), we use something like Bucardo to help out.
> No need to ever wait a "couple of days" as OP claims. :)
>
> What really bites is the analyze afterwards. That's the part
> that takes too long (yes, --in-stages helps some). Would love
> to see progress made there.

I'm of two minds about --in-stages.  On the one hand, it is totally
unprincipled.  Only two stages are in principle plausible, the stage
before you open your database for general use, and stage after you do
so.  (you could say there is a third stage, the one in which you do
EOW/EOM/EOY processing.  So don't run pg_upgrade on the second to last
day of the week or month, or towards the end of the second to the last
week of the year).  The current implementation of --in-stages uses
four stages, not two, and offers no convenient way to pause between
stages to open your database for general use.  On the other hand,
--in-stages is supposed to be pragmatic, not principled.  It is
supposed to work adequately over a wide variety of scenarios, even if
don't know ahead of time which scenario applies to you.

Cheers,

Jeff


Re: Uber migrated from Postgres to MySQL

От
John R Pierce
Дата:
On 7/27/2016 9:39 PM, Jeff Janes wrote:
> That depends on how how many objects there are consuming that 1 TB.
> With millions of small objects, you will have problems.  Not as many
> in 9.5 as there were in 9.1, but still it does not scale linearly in
> the number of objects.  If you only have thousands of objects, then as
> far as I know -k works like a charm.

millions of tables?  thats akin to having millions of classes in an
object oriented program, seems a bit excessive.



--
john r pierce, recycling bits in santa cruz



Re: Uber migrated from Postgres to MySQL

От
James Keener
Дата:
So, millions is a lot, but it's not difficult to get to a place where
you have thousands or tables.

Image a case in which census data and the associated geometries.
https://github.com/censusreporter/census-postgres has 22 surveys, each
with 230+ tables. That's 5000+ tables right there.  Now, the TIGER
tables for all of that is another 50 tables per year, so another 350
tables.

If these were to be partitioned by state, instead of all records for
all states in a single table, then we're looking at 270,000.

Jim

On Thu, Jul 28, 2016 at 12:48 AM, John R Pierce <pierce@hogranch.com> wrote:
> On 7/27/2016 9:39 PM, Jeff Janes wrote:
>>
>> That depends on how how many objects there are consuming that 1 TB.
>> With millions of small objects, you will have problems.  Not as many
>> in 9.5 as there were in 9.1, but still it does not scale linearly in
>> the number of objects.  If you only have thousands of objects, then as
>> far as I know -k works like a charm.
>
>
> millions of tables?  thats akin to having millions of classes in an object
> oriented program, seems a bit excessive.
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: Uber migrated from Postgres to MySQL

От
Jason Dusek
Дата:
With regards to write amplification, it makes me think about about OIDs. Used to be, every row had an OID and that OID persisted across row versions.


Would reintroducing such a feature address some of Uber's concerns about multiple indexes? It could, and would do so without the implicit requirement of a foreign key; but it would also require a fast OID to CTID mapping.

On Tue, 26 Jul 2016 at 10:40 Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons.

https://eng.uber.com/mysql-migration/

Thoughts?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Uber migrated from Postgres to MySQL

От
Jeff Janes
Дата:
On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 7/27/2016 9:39 PM, Jeff Janes wrote:
>>
>> That depends on how how many objects there are consuming that 1 TB.
>> With millions of small objects, you will have problems.  Not as many
>> in 9.5 as there were in 9.1, but still it does not scale linearly in
>> the number of objects.  If you only have thousands of objects, then as
>> far as I know -k works like a charm.
>
>
> millions of tables?

Well, it was a problem at much smaller values, until we fixed many of
them.  But the perversity is, if you are stuck on a version before the
fixes, the problems prevent you from getting to a version on which it
is not a problem any more.

> thats akin to having millions of classes in an object
> oriented program, seems a bit excessive.

It is not outside the bounds of reason, in a multi-tenancy situation.
 Maybe you have a hundred tables and each table has two sequences and
7 indexes, on average.  Or 300 tables and fewer indices apiece.  But
then you have 1000 schemas each with the same, ah, schema.  I've
pursued these optimizations as an intellectual exercise, but I know
others have had more concrete motivations.

Cheers,

Jeff


Re: Uber migrated from Postgres to MySQL

От
Gavin Flower
Дата:
On 28/07/16 17:52, Jason Dusek wrote:
> With regards to write amplification, it makes me think about about
> OIDs. Used to be, every row had an OID and that OID persisted across
> row versions.
>
> https://www.postgresql.org/docs/9.5/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS
>
> Would reintroducing such a feature address some of Uber's concerns
> about multiple indexes? It could, and would do so without the implicit
> requirement of a foreign key; but it would also require a fast OID to
> CTID mapping.
>
> On Tue, 26 Jul 2016 at 10:40 Guyren Howe <guyren@gmail.com
> <mailto:guyren@gmail.com>> wrote:
>
>     Honestly, I've never heard of anyone doing that. But it sounds
>     like they had good reasons.
>
>     https://eng.uber.com/mysql-migration/
>
>     Thoughts?
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
Would it be best to increase OIDs to 64 bits?

Possibly a choice of 32/64 to be decided when the DB is created???


Cheers,
Gavin



Re: Uber migrated from Postgres to MySQL

От
Scott Marlowe
Дата:
On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> On 27 July 2016 at 15:22, Scott Mead <scottm@openscg.com> wrote:
>>
>>  "The bug we ran into only affected certain releases of Postgres 9.2 and
>> has been fixed for a long time now. However, we still find it worrisome that
>> this class of bug can happen at all. A new version of Postgres could be
>> released at any time that has a bug of this nature, and because of the way
>> replication works, this issue has the potential to spread into all of the
>> databases in a replication hierarchy."
>>
>>
>> ISTM that they needed a tire swing and were using a dump truck.  Hopefully
>> they vectored somewhere in the middle and got themselves a nice sandbox.
>
>
> At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in
> an initial refusal to accept any problem existed, followed by (once that
> particular strategy had run out of steam) the developer simply ignoring the
> bug until it was closed automatically by their bug system. As far as I'm
> aware those bugs still exist in the most recent version.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.

As someone who has gotten more than one bug fix from pgsql in less
than 48 hours, I feel sorry for anyone who finds a bug in a MySQL
version they are running in production.


Re: Uber migrated from Postgres to MySQL

От
James Keener
Дата:
If we're talking about favourite bug https://bugs.mysql.com/bug.php?id=21153 is mine

Join with many tables hangs mysql (and taking 100% cpu)
Description:
the following query hangs the mysql server taking 100% cpu. also an "explain" of the query hangs the server!

It's "not a bug" because you can change some of the default query planning parameters to avoid it:

Igor Babaev
This is not a bug.
The reported query is a 18-way join. For such queries we expect that the full search for the best execution plan will take a significant amount of time.
At the same due to a specific structure of the reported query we can hope to get a good execution plan with a limited search (see Manual 5.0: 7.5.3. Controlling Query Optimizer Performance).
Setting the value of the global variable 'optimizer_search_depth' to 4 or even to 2 we can get the same execution plan as with a full search. Yet it will take much less time:

To me that speaks volumes. Sure, you can tweak a db params to get better performance, but I shouldn't have to deviate from the default for it to simply work at all!

Jim

On Thu, Jul 28, 2016 at 9:38 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> On 27 July 2016 at 15:22, Scott Mead <scottm@openscg.com> wrote:
>>
>>  "The bug we ran into only affected certain releases of Postgres 9.2 and
>> has been fixed for a long time now. However, we still find it worrisome that
>> this class of bug can happen at all. A new version of Postgres could be
>> released at any time that has a bug of this nature, and because of the way
>> replication works, this issue has the potential to spread into all of the
>> databases in a replication hierarchy."
>>
>>
>> ISTM that they needed a tire swing and were using a dump truck.  Hopefully
>> they vectored somewhere in the middle and got themselves a nice sandbox.
>
>
> At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in
> an initial refusal to accept any problem existed, followed by (once that
> particular strategy had run out of steam) the developer simply ignoring the
> bug until it was closed automatically by their bug system. As far as I'm
> aware those bugs still exist in the most recent version.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.

As someone who has gotten more than one bug fix from pgsql in less
than 48 hours, I feel sorry for anyone who finds a bug in a MySQL
version they are running in production.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Uber migrated from Postgres to MySQL

От
Alex Ignatov
Дата:
On 27.07.2016 19:51, Rakesh Kumar wrote:
> On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
>> Yes.  I was saying I don't know how to improve pg_upgrade to address it.
> This problem is there even in oracle/db2/sqlserver. None of them allow
> rollback to the lower version
> unless it is a minor version upgrade. Major version upgrade almost
> definitely involves change in transaction log
> (WAL) structure and hence no rollback.
>
>
Sorry, what? You can rollback to previous version of software what you had.

https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company




Re: Uber migrated from Postgres to MySQL

От
Rakesh Kumar
Дата:
On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

> Sorry, what? You can rollback to previous version of software what you had.
>
> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007

Not so fast. This requires the db to be set in compatibility mode of
the lower version. Once that is removed, no downgrade is possible.

Also starting from Oracle 12 this downgrade option has been removed.
That is, you can't upgrade in-place from Or 11 to 12 and then
downgrade back. The only supported way is the restore from an old
backup (which is true  for all products). I don't work in Oracle, but
got this clarified by someone who is an Oracle dba.


Re: Uber migrated from Postgres to MySQL

От
Alex Ignatov
Дата:
On 28.07.2016 17:50, Rakesh Kumar wrote:
> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>
>> Sorry, what? You can rollback to previous version of software what you had.
>>
>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007
> Not so fast. This requires the db to be set in compatibility mode of
> the lower version. Once that is removed, no downgrade is possible.
>
> Also starting from Oracle 12 this downgrade option has been removed.
> That is, you can't upgrade in-place from Or 11 to 12 and then
> downgrade back. The only supported way is the restore from an old
> backup (which is true  for all products). I don't work in Oracle, but
> got this clarified by someone who is an Oracle dba.
>
>

Not true -

https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007

About compatible params you are right but downgrade is possible from 12c.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



Re: Uber migrated from Postgres to MySQL

От
Igor Neyman
Дата:
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
Sent: Thursday, July 28, 2016 10:59 AM
To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL


On 28.07.2016 17:50, Rakesh Kumar wrote:
> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>
>> Sorry, what? You can rollback to previous version of software what you had.
>>
>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#
>> UPGRD007
> Not so fast. This requires the db to be set in compatibility mode of 
> the lower version. Once that is removed, no downgrade is possible.
>
> Also starting from Oracle 12 this downgrade option has been removed.
> That is, you can't upgrade in-place from Or 11 to 12 and then 
> downgrade back. The only supported way is the restore from an old 
> backup (which is true  for all products). I don't work in Oracle, but 
> got this clarified by someone who is an Oracle dba.
>
>

Not true -

https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007

About compatible params you are right but downgrade is possible from 12c.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

_____________________________________________________________________________

Alex, documentation you point to just proves what Rakesh said:

"For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded.
Forexample, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4."
 

So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures.
 

Regards,
Igor

Re: Uber migrated from Postgres to MySQL

От
Alex Ignatov
Дата:
On 28.07.2016 18:09, Igor Neyman wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
> Sent: Thursday, July 28, 2016 10:59 AM
> To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
> Cc: PostgreSQL General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>
>
> On 28.07.2016 17:50, Rakesh Kumar wrote:
>> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>
>>> Sorry, what? You can rollback to previous version of software what you had.
>>>
>>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#
>>> UPGRD007
>> Not so fast. This requires the db to be set in compatibility mode of
>> the lower version. Once that is removed, no downgrade is possible.
>>
>> Also starting from Oracle 12 this downgrade option has been removed.
>> That is, you can't upgrade in-place from Or 11 to 12 and then
>> downgrade back. The only supported way is the restore from an old
>> backup (which is true  for all products). I don't work in Oracle, but
>> got this clarified by someone who is an Oracle dba.
>>
>>
> Not true -
>
> https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007
>
> About compatible params you are right but downgrade is possible from 12c.
>
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
>
> _____________________________________________________________________________
>
> Alex, documentation you point to just proves what Rakesh said:
>
> "For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded.
Forexample, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4." 
>
> So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
> But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures. 
>
> Regards,
> Igor
Not true again.

 From Oracle docs
https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section
"How the COMPATIBLE Initialization Parameter Operates in Oracle Database"
-
If you run an Oracle Database 12c database with the COMPATIBLE
initialization parameter set to 11.0.0, then it generates database
structures on disk that are compatible with Oracle Database 11g.
Therefore, the COMPATIBLE initialization parameter enables or disables
the use of features. If you try to use any new features that make the
database incompatible with the COMPATIBLE initialization parameter, then
an error occurs. However, any new features that do not make incompatible
changes on disk are enabled.

So if feature do not change disk in incompatible way this features is
enable.

Also from this
https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
:

"This parameter specifies the release with which Oracle must maintain
compatibility. It enables you to take advantage of the maintenance
improvements of a new release immediately in your production systems
without testing the new functionality in your environment. Some features
of the release may be restricted."

So this parameter is made with rollback in mind and you have some new
feature from newer software release.

Alex Ignatov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company





Re: Uber migrated from Postgres to MySQL

От
Igor Neyman
Дата:
-----Original Message-----
From: Alex Ignatov [mailto:a.ignatov@postgrespro.ru] 
Sent: Thursday, July 28, 2016 11:26 AM
To: Igor Neyman <ineyman@perceptron.com>; Rakesh Kumar <rakeshkumar464a3@gmail.com>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL


On 28.07.2016 18:09, Igor Neyman wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org 
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
> Sent: Thursday, July 28, 2016 10:59 AM
> To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
> Cc: PostgreSQL General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>
>
> On 28.07.2016 17:50, Rakesh Kumar wrote:
>> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>
>>> Sorry, what? You can rollback to previous version of software what you had.
>>>
>>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm
>>> #
>>> UPGRD007
>> Not so fast. This requires the db to be set in compatibility mode of 
>> the lower version. Once that is removed, no downgrade is possible.
>>
>> Also starting from Oracle 12 this downgrade option has been removed.
>> That is, you can't upgrade in-place from Or 11 to 12 and then 
>> downgrade back. The only supported way is the restore from an old 
>> backup (which is true  for all products). I don't work in Oracle, but 
>> got this clarified by someone who is an Oracle dba.
>>
>>
> Not true -
>
> https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007
>
> About compatible params you are right but downgrade is possible from 12c.
>
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com The Russian Postgres 
> Company
>
> ______________________________________________________________________
> _______
>
> Alex, documentation you point to just proves what Rakesh said:
>
> "For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded.
Forexample, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4."
 
>
> So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
> But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures.
 
>
> Regards,
> Igor
Not true again.

 From Oracle docs
https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section "How the COMPATIBLE Initialization Parameter
Operatesin Oracle Database"
 
-
If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.0.0, then it
generatesdatabase structures on disk that are compatible with Oracle Database 11g. 
 
Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new
featuresthat make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs.
However,any new features that do not make incompatible changes on disk are enabled.
 

So if feature do not change disk in incompatible way this features is enable.

Also from this
https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
:

"This parameter specifies the release with which Oracle must maintain compatibility. It enables you to take advantage
ofthe maintenance improvements of a new release immediately in your production systems without testing the new
functionalityin your environment. Some features of the release may be restricted."
 

So this parameter is made with rollback in mind and you have some new feature from newer software release.

Alex Ignatov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

___________________________________________________________________________________________________________________

^^^
Which means that you can make use of some new feature, but definitely not all.
That makes "downgrade" feature very, very limited, if useful at all.

Regards,
Igor


Re: Uber migrated from Postgres to MySQL

От
Geoff Winkless
Дата:
On 28 July 2016 at 16:34, Igor Neyman <ineyman@perceptron.com> wrote:
Which means that you can make use of some new feature, but definitely not all.
That makes "downgrade" feature very, very limited, if useful at all.

Sufficient to allow you to run the upgrade, find that there's a catastrophic bug in the new version that causes your live system to fall over but
​which ​
didn't appear on your test system, and then revert to a working version?

I'd say that's a fairly useful feature, limited or not.

Geoff​
 

Re: Uber migrated from Postgres to MySQL

От
Igor Neyman
Дата:
-----Original Message-----
From: Alex Ignatov [mailto:a.ignatov@postgrespro.ru] 
Sent: Thursday, July 28, 2016 11:26 AM
To: Igor Neyman <ineyman@perceptron.com>; Rakesh Kumar <rakeshkumar464a3@gmail.com>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL


On 28.07.2016 18:09, Igor Neyman wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org 
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
> Sent: Thursday, July 28, 2016 10:59 AM
> To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
> Cc: PostgreSQL General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>
>
> On 28.07.2016 17:50, Rakesh Kumar wrote:
>> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>
>>> Sorry, what? You can rollback to previous version of software what you had.
>>>
>>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm
>>> #
>>> UPGRD007
>> Not so fast. This requires the db to be set in compatibility mode of 
>> the lower version. Once that is removed, no downgrade is possible.
>>
>> Also starting from Oracle 12 this downgrade option has been removed.
>> That is, you can't upgrade in-place from Or 11 to 12 and then 
>> downgrade back. The only supported way is the restore from an old 
>> backup (which is true  for all products). I don't work in Oracle, but 
>> got this clarified by someone who is an Oracle dba.
>>
>>
> Not true -
>
> https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007
>
> About compatible params you are right but downgrade is possible from 12c.
>
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com The Russian Postgres 
> Company
>
> ______________________________________________________________________
> _______
>
> Alex, documentation you point to just proves what Rakesh said:
>
> "For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded.
Forexample, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4."
 
>
> So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
> But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures.
 
>
> Regards,
> Igor
Not true again.

 From Oracle docs
https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section "How the COMPATIBLE Initialization Parameter
Operatesin Oracle Database"
 
-
If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.0.0, then it
generatesdatabase structures on disk that are compatible with Oracle Database 11g. 
 
Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new
featuresthat make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs.
However,any new features that do not make incompatible changes on disk are enabled.
 

So if feature do not change disk in incompatible way this features is enable.

Also from this
https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
:

"This parameter specifies the release with which Oracle must maintain compatibility. It enables you to take advantage
ofthe maintenance improvements of a new release immediately in your production systems without testing the new
functionalityin your environment. Some features of the release may be restricted."
 

So this parameter is made with rollback in mind and you have some new feature from newer software release.

Alex Ignatov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

____________________________________________________________________________________________________________________

B.t.w., In Postgres release which doesn't change "database structures"  is called "minor" (just replacing binaries),
andallows downgrade.
 

"Compactable" or not,  in "Oracle world" no DBA in their right mind will attempt major upgrade without having complete
(andtested) backup that could be used in case of upgrade going wrong.
 

Regards,
Igor


Re: Uber migrated from Postgres to MySQL

От
Chris Travers
Дата:


On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> On 27 July 2016 at 15:22, Scott Mead <scottm@openscg.com> wrote:
>>
>>  "The bug we ran into only affected certain releases of Postgres 9.2 and
>> has been fixed for a long time now. However, we still find it worrisome that
>> this class of bug can happen at all. A new version of Postgres could be
>> released at any time that has a bug of this nature, and because of the way
>> replication works, this issue has the potential to spread into all of the
>> databases in a replication hierarchy."
>>
>>
>> ISTM that they needed a tire swing and were using a dump truck.  Hopefully
>> they vectored somewhere in the middle and got themselves a nice sandbox.
>
>
> At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in
> an initial refusal to accept any problem existed, followed by (once that
> particular strategy had run out of steam) the developer simply ignoring the
> bug until it was closed automatically by their bug system. As far as I'm
> aware those bugs still exist in the most recent version.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.


As for MySQL issues, personally I love the fact that a single query inserting a bunch of rows can sometimes deadlock against itself.  And I love the fact that this is obliquely documented as expected behavior.  May I mention I am *really glad* PostgreSQL doesn't go the whole multi-threaded backend route and that this is exhibit A as to why (I am sure it is a thread race issue between index and table updates)?  

As someone who has gotten more than one bug fix from pgsql in less
than 48 hours, I feel sorry for anyone who finds a bug in a MySQL
version they are running in production.  


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

Re: Uber migrated from Postgres to MySQL

От
Alex Ignatov
Дата:
On 28.07.2016 18:41, Igor Neyman wrote:
> -----Original Message-----
> From: Alex Ignatov [mailto:a.ignatov@postgrespro.ru]
> Sent: Thursday, July 28, 2016 11:26 AM
> To: Igor Neyman <ineyman@perceptron.com>; Rakesh Kumar <rakeshkumar464a3@gmail.com>
> Cc: PostgreSQL General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>
>
> On 28.07.2016 18:09, Igor Neyman wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org
>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
>> Sent: Thursday, July 28, 2016 10:59 AM
>> To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
>> Cc: PostgreSQL General <pgsql-general@postgresql.org>
>> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>>
>>
>> On 28.07.2016 17:50, Rakesh Kumar wrote:
>>> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>>
>>>> Sorry, what? You can rollback to previous version of software what you had.
>>>>
>>>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm
>>>> #
>>>> UPGRD007
>>> Not so fast. This requires the db to be set in compatibility mode of
>>> the lower version. Once that is removed, no downgrade is possible.
>>>
>>> Also starting from Oracle 12 this downgrade option has been removed.
>>> That is, you can't upgrade in-place from Or 11 to 12 and then
>>> downgrade back. The only supported way is the restore from an old
>>> backup (which is true  for all products). I don't work in Oracle, but
>>> got this clarified by someone who is an Oracle dba.
>>>
>>>
>> Not true -
>>
>> https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007
>>
>> About compatible params you are right but downgrade is possible from 12c.
>>
>> Alex Ignatov
>> Postgres Professional: http://www.postgrespro.com The Russian Postgres
>> Company
>>
>> ______________________________________________________________________
>> _______
>>
>> Alex, documentation you point to just proves what Rakesh said:
>>
>> "For supported releases of Oracle Database, you can downgrade a database to the release from which you last
upgraded.For example, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4." 
>>
>> So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
>> But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures. 
>>
>> Regards,
>> Igor
> Not true again.
>
>   From Oracle docs
> https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section "How the COMPATIBLE Initialization Parameter
Operatesin Oracle Database" 
> -
> If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.0.0, then it
generatesdatabase structures on disk that are compatible with Oracle Database 11g. 
> Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new
featuresthat make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs.
However,any new features that do not make incompatible changes on disk are enabled. 
>
> So if feature do not change disk in incompatible way this features is enable.
>
> Also from this
> https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
> :
>
> "This parameter specifies the release with which Oracle must maintain compatibility. It enables you to take advantage
ofthe maintenance improvements of a new release immediately in your production systems without testing the new
functionalityin your environment. Some features of the release may be restricted." 
>
> So this parameter is made with rollback in mind and you have some new feature from newer software release.
>
> Alex Ignatov
> Postgres Professional:http://www.postgrespro.com
> The Russian Postgres Company
>
> ____________________________________________________________________________________________________________________
>
> B.t.w., In Postgres release which doesn't change "database structures"  is called "minor" (just replacing binaries),
andallows downgrade. 
>
> "Compactable" or not,  in "Oracle world" no DBA in their right mind will attempt major upgrade without having
complete(and tested) backup that could be used in case of upgrade going wrong. 
>
> Regards,
> Igor
>
  Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their
right to do major upgrade without complete and tested backup?
Ok,  I understand you. In Postgres world there always sky is blue and
sun is shining.


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



Re: Uber migrated from Postgres to MySQL

От
Chris Travers
Дата:


On Thu, Jul 28, 2016 at 6:32 PM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

On 28.07.2016 18:41, Igor Neyman wrote:
-----Original Message-----
From: Alex Ignatov [mailto:a.ignatov@postgrespro.ru]
Sent: Thursday, July 28, 2016 11:26 AM
To: Igor Neyman <ineyman@perceptron.com>; Rakesh Kumar <rakeshkumar464a3@gmail.com>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL


On 28.07.2016 18:09, Igor Neyman wrote:
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
Sent: Thursday, July 28, 2016 10:59 AM
To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL


On 28.07.2016 17:50, Rakesh Kumar wrote:
On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

Sorry, what? You can rollback to previous version of software what you had.

https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm
#
UPGRD007
Not so fast. This requires the db to be set in compatibility mode of
the lower version. Once that is removed, no downgrade is possible.

Also starting from Oracle 12 this downgrade option has been removed.
That is, you can't upgrade in-place from Or 11 to 12 and then
downgrade back. The only supported way is the restore from an old
backup (which is true  for all products). I don't work in Oracle, but
got this clarified by someone who is an Oracle dba.


Not true -

https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007

About compatible params you are right but downgrade is possible from 12c.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com The Russian Postgres
Company

______________________________________________________________________
_______

Alex, documentation you point to just proves what Rakesh said:

"For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded. For example, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the compatible initialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4."

So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use newer features.

Regards,
Igor
Not true again.

  From Oracle docs
https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section "How the COMPATIBLE Initialization Parameter Operates in Oracle Database"
-
If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.0.0, then it generates database structures on disk that are compatible with Oracle Database 11g.
Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new features that make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs. However, any new features that do not make incompatible changes on disk are enabled.

So if feature do not change disk in incompatible way this features is enable.

Also from this
https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
:

"This parameter specifies the release with which Oracle must maintain compatibility. It enables you to take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. Some features of the release may be restricted."

So this parameter is made with rollback in mind and you have some new feature from newer software release.

Alex Ignatov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

____________________________________________________________________________________________________________________

B.t.w., In Postgres release which doesn't change "database structures"  is called "minor" (just replacing binaries), and allows downgrade.

"Compactable" or not,  in "Oracle world" no DBA in their right mind will attempt major upgrade without having complete (and tested) backup that could be used in case of upgrade going wrong.

Regards,
Igor

 Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their right to do major upgrade without complete and tested backup?
Ok,  I understand you. In Postgres world there always sky is blue and sun is shining.

And see, I am just wondering, what DBA in their right mind would allow a db to run without a complete and tested backup.  I mean when you upgrade the last thing you do is take another backup and the first thing you do after upgrading is take another backup, right?  Or am I just being dense?

Re: Uber migrated from Postgres to MySQL

От
Scott Marlowe
Дата:
On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>
>  Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their right
> to do major upgrade without complete and tested backup?
> Ok,  I understand you. In Postgres world there always sky is blue and sun is
> shining.

Of course we have backups. But we also have slony. So we CAN go back
and forth between latest and previous without a restore.


Re: Uber migrated from Postgres to MySQL

От
Alex Ignatov
Дата:
On 28.07.2016 19:43, Scott Marlowe wrote:
> On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>   Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their right
>> to do major upgrade without complete and tested backup?
>> Ok,  I understand you. In Postgres world there always sky is blue and sun is
>> shining.
> Of course we have backups. But we also have slony. So we CAN go back
> and forth between latest and previous without a restore.
>
>

And? Oracle and MySql doesnt have it but can downgrade right out the
box. Quick and easy.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



Re: Uber migrated from Postgres to MySQL

От
Rakesh Kumar
Дата:
> And? Oracle and MySql doesnt have it but can downgrade right out the box.
> Quick and easy.

No it is not for mysql.

http://dev.mysql.com/doc/refman/5.7/en/downgrading.html

"In-place Downgrade: Involves shutting down the new MySQL version,
replacing the new MySQL binaries or packages with the old ones, and
restarting the old MySQL version on the existing data directory.
In-place downgrades are supported for downgrades between GA versions
within the same release series. For example, in-place downgrades are
supported for downgrades from 5.7.10 to 5.7.9.
The above is easy and same as PG if you are going from 9.5.2 to 9.5.1.
"
Now let us take about major downgrade.

Downgrading one release level is supported using the logical downgrade
method. For example, downgrading from 5.7 to 5.6 is supported. Logical
Downgrade: Involves using mysqldump to dump all tables from the new
MySQL version, and then loading the dump file into the old MySQL
version. Logical downgrades are supported for downgrades between GA
versions within the same release series and for downgrades between
release levels. For example, logical downgrades are supported for
downgrades from 5.7.10 to 5.7.9 and for downgrades from 5.7 to 5.6.

Only a fool will consider this as simple. And BTW all products support
methods similar to mysqldump. What we are looking at, is an in-place
downgrade after a major version upgrade and that looks to be almost
non-existent.


Re: Uber migrated from Postgres to MySQL

От
Scott Marlowe
Дата:
On Thu, Jul 28, 2016 at 11:23 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>
> On 28.07.2016 19:43, Scott Marlowe wrote:
>>
>> On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov <a.ignatov@postgrespro.ru>
>> wrote:
>>>
>>>   Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their
>>> right
>>> to do major upgrade without complete and tested backup?
>>> Ok,  I understand you. In Postgres world there always sky is blue and sun
>>> is
>>> shining.
>>
>> Of course we have backups. But we also have slony. So we CAN go back
>> and forth between latest and previous without a restore.
>>
>>
>
> And? Oracle and MySql doesnt have it but can downgrade right out the box.
> Quick and easy.

So you can swap between oracle 11 and 12 back and forth in a live
environment with no downtime? Please pull the other leg.


Re: Uber migrated from Postgres to MySQL

От
"Joshua D. Drake"
Дата:
On 07/28/2016 10:43 AM, Scott Marlowe wrote:
> On Thu, Jul 28, 2016 at 11:23 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

>> And? Oracle and MySql doesnt have it but can downgrade right out the box.
>> Quick and easy.
>
> So you can swap between oracle 11 and 12 back and forth in a live
> environment with no downtime? Please pull the other leg.

This conversation seems to be degrading, can we keep it productive please?

JD


>
>


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


Re: Uber migrated from Postgres to MySQL

От
Edson Richter
Дата:
Em 28/07/2016 13:07, Chris Travers escreveu:


On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> On 27 July 2016 at 15:22, Scott Mead <scottm@openscg.com> wrote:
>>
>>  "The bug we ran into only affected certain releases of Postgres 9.2 and
>> has been fixed for a long time now. However, we still find it worrisome that
>> this class of bug can happen at all. A new version of Postgres could be
>> released at any time that has a bug of this nature, and because of the way
>> replication works, this issue has the potential to spread into all of the
>> databases in a replication hierarchy."
>>
>>
>> ISTM that they needed a tire swing and were using a dump truck.  Hopefully
>> they vectored somewhere in the middle and got themselves a nice sandbox.
>
>
> At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in
> an initial refusal to accept any problem existed, followed by (once that
> particular strategy had run out of steam) the developer simply ignoring the
> bug until it was closed automatically by their bug system. As far as I'm
> aware those bugs still exist in the most recent version.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.


As for MySQL issues, personally I love the fact that a single query inserting a bunch of rows can sometimes deadlock against itself.  And I love the fact that this is obliquely documented as expected behavior.  May I mention I am *really glad* PostgreSQL doesn't go the whole multi-threaded backend route and that this is exhibit A as to why (I am sure it is a thread race issue between index and table updates)?  

Sorry, I think this is a biased vision. Multi-threading will show as much problems as multi-process - both has to have simultaneous access (or, at least, right semaphor implementation to serialize writes and syncronize reads).
The fact is **on this point at least** is that Postgres is correctly implemented, and MySQL is faulty.
I've faced the "lost FK integrity hell" (caused by the problem above) with MySQL long before decided to migrate all systems to PostgreSQL.
My personal experience is that MySQL is excellent for data that is not sensitive (web site, e-mail settings, etc). Everything else goes to PostgreSQL (or Oracle, or MS SQL Server, or Sybase, or DB2 - in *my* order of preference).


Regards,

Edson Richter

Re: Uber migrated from Postgres to MySQL

От
"D'Arcy J.M. Cain"
Дата:
On Thu, 28 Jul 2016 19:32:39 +0300
Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
> On 28.07.2016 18:41, Igor Neyman wrote:
> > "Compactable" or not,  in "Oracle world" no DBA in their right mind
> > will attempt major upgrade without having complete (and tested)
> > backup that could be used in case of upgrade going wrong.
>
>   Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their
> right to do major upgrade without complete and tested backup?
> Ok,  I understand you. In Postgres world there always sky is blue and
> sun is shining.

Yikes!  Where did you read that?

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote:
> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
> > On 7/27/2016 9:39 PM, Jeff Janes wrote:
> >>
> >> That depends on how how many objects there are consuming that 1 TB.
> >> With millions of small objects, you will have problems.  Not as many
> >> in 9.5 as there were in 9.1, but still it does not scale linearly in
> >> the number of objects.  If you only have thousands of objects, then as
> >> far as I know -k works like a charm.
> >
> >
> > millions of tables?
>
> Well, it was a problem at much smaller values, until we fixed many of
> them.  But the perversity is, if you are stuck on a version before the
> fixes, the problems prevent you from getting to a version on which it
> is not a problem any more.

Uh, that is only true if the slowness was in _dumping_ many objects.
Most of the fixes have been for _restoring_ many objects, and that is
done in the new cluster, so they should be OK.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
John R Pierce
Дата:
On 7/28/2016 3:16 PM, Bruce Momjian wrote:
On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote:
> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
> > On 7/27/2016 9:39 PM, Jeff Janes wrote:
> >>
> >> That depends on how how many objects there are consuming that 1 TB.
> >> With millions of small objects, you will have problems.  Not as many
> >> in 9.5 as there were in 9.1, but still it does not scale linearly in
> >> the number of objects.  If you only have thousands of objects, then as
> >> far as I know -k works like a charm.
> >
> >
> > millions of tables?
> 
> Well, it was a problem at much smaller values, until we fixed many of
> them.  But the perversity is, if you are stuck on a version before the
> fixes, the problems prevent you from getting to a version on which it
> is not a problem any more.
Uh, that is only true if the slowness was in _dumping_ many objects. 
Most of the fixes have been for _restoring_ many objects, and that is
done in the new cluster, so they should be OK.

I thought we were talking about pg_upgrade in -k link mode?    or does that rely on a dump/restore --schema-only operation to create the metadata?

-- 
john r pierce, recycling bits in santa cruz

Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Thu, Jul 28, 2016 at 03:26:17PM -0700, John R Pierce wrote:
>     Uh, that is only true if the slowness was in _dumping_ many objects.
>     Most of the fixes have been for _restoring_ many objects, and that is
>     done in the new cluster, so they should be OK.
>
>
> I thought we were talking about pg_upgrade in -k link mode?    or does that
> rely on a dump/restore --schema-only operation to create the metadata?

Yes, it does, with our without -k --- -k only controls file link vs file
copy.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Joe Conway
Дата:
On 07/28/2016 03:16 PM, Bruce Momjian wrote:
> On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote:
>> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
>>> On 7/27/2016 9:39 PM, Jeff Janes wrote:
>>>>
>>>> That depends on how how many objects there are consuming that 1 TB.
>>>> With millions of small objects, you will have problems.  Not as many
>>>> in 9.5 as there were in 9.1, but still it does not scale linearly in
>>>> the number of objects.  If you only have thousands of objects, then as
>>>> far as I know -k works like a charm.
>>>
>>>
>>> millions of tables?
>>
>> Well, it was a problem at much smaller values, until we fixed many of
>> them.  But the perversity is, if you are stuck on a version before the
>> fixes, the problems prevent you from getting to a version on which it
>> is not a problem any more.
>
> Uh, that is only true if the slowness was in _dumping_ many objects.
> Most of the fixes have been for _restoring_ many objects, and that is
> done in the new cluster, so they should be OK.


Not really true. I ran into two separate cases where on older (pre 9.3 I
believe) Postgres if you had hundreds of thousands of tables (in the
case I remember well, it was about 500k tables) the schema dump from the
old cluster basically never finished (ok, was killed after about a
week). I had to find the patch that fixed a good bit of the slowness and
backport it to the older version so we could successfully run pg_upgrade
(in something like 14 hours instead of 7+ days).

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


Вложения

Re: Uber migrated from Postgres to MySQL

От
"Joshua D. Drake"
Дата:
On 07/28/2016 04:58 PM, Joe Conway wrote:
> On 07/28/2016 03:16 PM, Bruce Momjian wrote:
>
> Not really true. I ran into two separate cases where on older (pre 9.3 I
> believe) Postgres if you had hundreds of thousands of tables (in the
> case I remember well, it was about 500k tables) the schema dump from the
> old cluster basically never finished (ok, was killed after about a
> week). I had to find the patch that fixed a good bit of the slowness and
> backport it to the older version so we could successfully run pg_upgrade
> (in something like 14 hours instead of 7+ days).

Correct, I don't know if it is still true but definitely pre 9.3, if you
had lots and lots of tables, you were looking at very long times to
actually start a dump. The thing is, although 500k tables is very rare,
10k tables isn't nearly as rare. That would still take entirely too long.

Sincerely,

jD

--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


Re: Uber migrated from Postgres to MySQL

От
Chris Travers
Дата:


On Thu, Jul 28, 2016 at 8:09 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
Em 28/07/2016 13:07, Chris Travers escreveu:


On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> On 27 July 2016 at 15:22, Scott Mead <scottm@openscg.com> wrote:
>>
>>  "The bug we ran into only affected certain releases of Postgres 9.2 and
>> has been fixed for a long time now. However, we still find it worrisome that
>> this class of bug can happen at all. A new version of Postgres could be
>> released at any time that has a bug of this nature, and because of the way
>> replication works, this issue has the potential to spread into all of the
>> databases in a replication hierarchy."
>>
>>
>> ISTM that they needed a tire swing and were using a dump truck.  Hopefully
>> they vectored somewhere in the middle and got themselves a nice sandbox.
>
>
> At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in
> an initial refusal to accept any problem existed, followed by (once that
> particular strategy had run out of steam) the developer simply ignoring the
> bug until it was closed automatically by their bug system. As far as I'm
> aware those bugs still exist in the most recent version.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.


As for MySQL issues, personally I love the fact that a single query inserting a bunch of rows can sometimes deadlock against itself.  And I love the fact that this is obliquely documented as expected behavior.  May I mention I am *really glad* PostgreSQL doesn't go the whole multi-threaded backend route and that this is exhibit A as to why (I am sure it is a thread race issue between index and table updates)?  

Sorry, I think this is a biased vision. Multi-threading will show as much problems as multi-process - both has to have simultaneous access (or, at least, right semaphor implementation to serialize writes and syncronize reads).
The fact is **on this point at least** is that Postgres is correctly implemented, and MySQL is faulty.
I've faced the "lost FK integrity hell" (caused by the problem above) with MySQL long before decided to migrate all systems to PostgreSQL.

Both have potential for the same sorts of problems to be sure, but the difference is that process isolation does give you some protection and helps contain the issues.  Not the only way to do so, to be sure.  But getting some extra help from the OS and CPU in this area really is not a bad thing.  My point is that I like the design decision here regarding PostgreSQL.
 
My personal experience is that MySQL is excellent for data that is not sensitive (web site, e-mail settings, etc). Everything else goes to PostgreSQL (or Oracle, or MS SQL Server, or Sybase, or DB2 - in *my* order of preference).

MySQL is a good system for a few sorts of problems.  InnoDB is well-optimized for simple primary key lookups.  So if really what you want is a SQL access to a dumb information store with network access and reasonable write scalability, it is not a bad system.  You will note that is actually pretty close to Uber's use case with schemaless.  But at that point, foreign keys are pretty close to being optional.....
 


Regards,

Edson Richter




--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

Re: Uber migrated from Postgres to MySQL

От
Achilleas Mantzios
Дата:
On 28/07/2016 21:09, Edson Richter wrote:
Em 28/07/2016 13:07, Chris Travers escreveu:


On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> On 27 July 2016 at 15:22, Scott Mead <scottm@openscg.com> wrote:
>>
>>  "The bug we ran into only affected certain releases of Postgres 9.2 and
>> has been fixed for a long time now. However, we still find it worrisome that
>> this class of bug can happen at all. A new version of Postgres could be
>> released at any time that has a bug of this nature, and because of the way
>> replication works, this issue has the potential to spread into all of the
>> databases in a replication hierarchy."
>>
>>
>> ISTM that they needed a tire swing and were using a dump truck.  Hopefully
>> they vectored somewhere in the middle and got themselves a nice sandbox.
>
>
> At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in
> an initial refusal to accept any problem existed, followed by (once that
> particular strategy had run out of steam) the developer simply ignoring the
> bug until it was closed automatically by their bug system. As far as I'm
> aware those bugs still exist in the most recent version.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.


As for MySQL issues, personally I love the fact that a single query inserting a bunch of rows can sometimes deadlock against itself.  And I love the fact that this is obliquely documented as expected behavior.  May I mention I am *really glad* PostgreSQL doesn't go the whole multi-threaded backend route and that this is exhibit A as to why (I am sure it is a thread race issue between index and table updates)?  

Sorry, I think this is a biased vision. Multi-threading will show as much problems as multi-process - both has to have simultaneous access (or, at least, right semaphor implementation to serialize writes and syncronize reads).
The fact is **on this point at least** is that Postgres is correctly implemented, and MySQL is faulty.
I've faced the "lost FK integrity hell" (caused by the problem above) with MySQL long before decided to migrate all systems to PostgreSQL.
My personal experience is that MySQL is excellent for data that is not sensitive (web site, e-mail settings, etc). Everything else goes to PostgreSQL (or Oracle, or MS SQL Server, or Sybase, or DB2 - in *my* order of preference).


+1


Regards,

Edson Richter



-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Wed, Jul 27, 2016 at 01:02:40PM -0400, Bruce Momjian wrote:
> Watching the video was helpful:
>
>     https://vimeo.com/145842299
>
> You can see the failover happened because of various user errors.  That
> doesn't excuse our bug, but I am not sure exactly how much they
> understood of Postgres behavior.  His talk near the end about the
> replication infrastucture being exposed to them was also interesting.

Here is a more balanced blog post that corrects some missing
information, e.g. HOT updates, hot_standby_feedback:

    http://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databases

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Jerry Sievers
Дата:
Condor <condor@stz-bg.com> writes:

> On 26-07-2016 21:04, Dorian Hoxha wrote:
>
>> Many comments: https://news.ycombinator.com/item?id=12166585
>> https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
>>
>> On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
>>
>>> Honestly, I've never heard of anyone doing that. But it sounds like
>>> they had good reasons.
>>>
>>> https://eng.uber.com/mysql-migration/
>>>
>>> Thoughts?
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>
>
> They are right for upgrades.
> It's a hard to shutdown 1 TB database and wait couple of days
> pg_upgrade to finish upgrade and meanwhile database is offline.
> In some distros after upgrade of PG version you don't have old binary
> and library, need to do full dump and restore that take time and disk
> space.

Yeah, very hard indeed when done by unskilled DBAs :-)

I've done several ~7TB pg_upgrades and with the hard link option and a
framework that parallelizes the post-analyzer phase...

...45 minutes till completion.


>
> Regards,
> Hristo S.

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


Re: Uber migrated from Postgres to MySQL

От
"Maeldron T."
Дата:
On 26/07/16 19:39, Guyren Howe wrote:
> Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons.
>
> https://eng.uber.com/mysql-migration/
>
> Thoughts?
>
1. Open the page

2. Press Cmd-F on Mac, Ctrl-F on Linux/Windows. (Find on page)

3. Type "transaction" in the search field, without the quotes

4. Scroll through the article and look for the highlights


There are 17 highlights. I count 2 in the MySQL part. Chances are good
that the rest 15 are in the PostgreSQL part.

It tells a lot.

When I am told that MySQL supports transactions I face the fact that the
word "transaction" must have at least two meanings and no matter what I
do I know only one of them.

Every time I had to work with MySQL I felt overwhelming sloppiness. I
can’t (or don’t want to) deal with accepting 0 as NULL but only once,
auto typecasting pianos to cats (take it as a metaphor), committing a
"transaction" (in MySQL terms), without sending commit, on client
disconnect. (Older version).

One can say it can’t be that bad as Facebook and now Uber are using it.
The same logic tells that junk food is the best for humans.

In the last few years I tried out more or less every hyped schemaless
databases. Not for their main feature as my data like like the rest of
the data in the Universe can be put in a schema. I did it because faced
some of the issues mentioned in the article and other issues that aren’t
mentioned, even on smaller scale, that’s why. (Smaller scale means
smaller company, less resources, less people. At the end it hurts the
same way.)

I still don’t see how I could live without transactions, and not only
because a simple and intentional rollback saved me from much coding and
complexity with one the most important features of my application. But
having a single update statement modified about the 70% of the records
before Cassandra crashed is not for me. It tried to repair about 1000
records, using 3 nodes, for 1 or 2 hours before I deleted the test cluster.

Maybe I did it wrong. Or probably. It can’t be that bad. People at Uber
probably know more about the internals than I ever will. I also know
that a few big companies had about 1 day long downtimes thanx to MongoDB
and CouchDB.

Since I know that people who are way more professional than me decide to
use a database engine in production that doesn’t tell you whether it
could store your data or not, I don’t care who is doing what and what is
on his business card.


And yes, I hate upgrading PostgreSQL especially on FreeBSD where
pg_upgrade isn’t really an option.

Sometimes the answer is manual or full vacuum, no matter what the manual
says. (And yes, the downtime does hurt.)


On the other hand, if there was a stable and officially integrated
logical replication that supports multi-master setups, many of the
issues would just be gone. Upgrades wouldn’t be painful anymore,
timeline and pg_rewind bugs wouldn’t matter, and the DBA could remove
the bloat form the masters one by one by doing that thing in the night
when no one sees it. (Until the full-vacuum-police would find him and come.)

M.




Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote:
> And yes, I hate upgrading PostgreSQL especially on FreeBSD where pg_upgrade
> isn’t really an option.

Is that because it is hard to install the old and new clusters on the
same server on FreeBSD?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Larry Rosenman
Дата:
On 2016-07-29 12:59, Bruce Momjian wrote:
> On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote:
>> And yes, I hate upgrading PostgreSQL especially on FreeBSD where
>> pg_upgrade
>> isn’t really an option.
>
> Is that because it is hard to install the old and new clusters on the
> same server on FreeBSD?
>
The current FreeBSD Ports collection ports only allow ONE version to be
installed at a time.


--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281


Re: Uber migrated from Postgres to MySQL

От
Jerry Sievers
Дата:
Bruce Momjian <bruce@momjian.us> writes:

> On Wed, Jul 27, 2016 at 10:22:27AM -0400, Scott Mead wrote:
>
>> That being said, it doesn't really provide a back-out plan.  The beauty of
>> replication is that you can halt the upgrade at any point if need be and cut
>> your (hopefully small) losses. If you use -k, you are all in.  Sure, you could
>> setup a new standby, stop traffic, upgrade whichever node you'd like (using -k)
>> and still have the other ready in the event of total catastrophe.  More often
>> than not, I see DBAs and sysads lead the conversation with "well, postgres
>> can't replicate from one version to another, so instead.... " followed by a
>> fast-glazing of management's eyes and a desire to buy a 'commercial database'. 
>
> I agree, but I am not sure how to improve it.  The big complaint I have
> heard is that once you upgrade and open up writes on the upgraded
> server, you can't re-apply those writes to the old server if you need to
> fall back to the old server.  I also don't see how to improve that either.

Hmmm, is it at least theoretically possible that if a newly upgraded
system were run for an interval where *no* incompatible changes to DDL
etc had been done...

...that a downgrade could be performed?

Er, using a not yet invented pg_downgrade:-)

I reason that the same kind of voodoo that lets us do those very quick
hard linked upgrades could be used to revert as well without data loss.

Such a feature would be part of whatever newer version that the upgrade
was done to in the first place.

That is, since higher version knew enough about lower version to
rejigger everything...  just maybe it could do the reverse.

Had the new version been run for very long with substantial data
changes, then a post-analyze on the downgraded system might be necessary
as well but possibly even this could be omitted in some cases.

Totally nuts?  Yes, perhaps :-)

FWIW

>   Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> +                     Ancient Roman grave inscription +

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


Re: Uber migrated from Postgres to MySQL

От
Stephen Frost
Дата:
Jerry,

* Jerry Sievers (gsievers19@comcast.net) wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > I agree, but I am not sure how to improve it.  The big complaint I have
> > heard is that once you upgrade and open up writes on the upgraded
> > server, you can't re-apply those writes to the old server if you need to
> > fall back to the old server.  I also don't see how to improve that either.
>
> Hmmm, is it at least theoretically possible that if a newly upgraded
> system were run for an interval where *no* incompatible changes to DDL
> etc had been done...
>
> ...that a downgrade could be performed?
>
> Er, using a not yet invented pg_downgrade:-)

The short answer is 'no'.  Consider a case like the GIN page changes- as
soon as you execute DML on a column that has a GIN index on it, we're
going to rewrite that page using a newer version of the page format and
an older version of PG isn't going to understand it.

Those kind of on-disk changes are, I suspect, why you have to set the
"compatibility" option in the big $O product to be able to later do a
downgrade.

> That is, since higher version knew enough about lower version to
> rejigger everything...  just maybe it could do the reverse.

That might work if you opened the database in read-only mode, but not
once you start making changes.

Thanks!

Stephen

Вложения

Re: Uber migrated from Postgres to MySQL

От
"D'Arcy J.M. Cain"
Дата:
On Fri, 29 Jul 2016 13:06:04 -0500
Larry Rosenman <ler@lerctr.org> wrote:
> > Is that because it is hard to install the old and new clusters on
> > the same server on FreeBSD?
> >
> The current FreeBSD Ports collection ports only allow ONE version to
> be installed at a time.

As does NetBSD.  The problem is that unlike Python (which BSD allows
multiple versions) there is only one executable to deal with.  It's not
an insurmountable problem but it could get messy.

The answer is either chroot or mount and run pg_upgrade on another
server. If you can afford the downtime you can also delete PG, install
the new version and run pg_upgrade without modifying the existing DB.
If it succeeds then replace the directories and restart the new
version.  If it fails then uninstall PG, reinstall the older version
and restart. Lather, rinse, repeat until it upgrades cleanly.

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Fri, Jul 29, 2016 at 03:03:46PM -0400, D'Arcy J.M. Cain wrote:
> As does NetBSD.  The problem is that unlike Python (which BSD allows
> multiple versions) there is only one executable to deal with.  It's not
> an insurmountable problem but it could get messy.
>
> The answer is either chroot or mount and run pg_upgrade on another
> server. If you can afford the downtime you can also delete PG, install
> the new version and run pg_upgrade without modifying the existing DB.
> If it succeeds then replace the directories and restart the new
> version.  If it fails then uninstall PG, reinstall the older version
> and restart. Lather, rinse, repeat until it upgrades cleanly.

pg_upgrade needs to run the old and new server binaries as part of its
operation, so that would not work.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Fri, Jul 29, 2016 at 02:50:32PM -0400, Stephen Frost wrote:
> > Er, using a not yet invented pg_downgrade:-)
>
> The short answer is 'no'.  Consider a case like the GIN page changes- as
> soon as you execute DML on a column that has a GIN index on it, we're
> going to rewrite that page using a newer version of the page format and
> an older version of PG isn't going to understand it.
>
> Those kind of on-disk changes are, I suspect, why you have to set the
> "compatibility" option in the big $O product to be able to later do a
> downgrade.

Yes, you would need a mode that prevented new-format writes on the new
server.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Allowing multiple versions of PG under NetBSD

От
"D'Arcy J.M. Cain"
Дата:
On Fri, 29 Jul 2016 15:07:53 -0400
Bruce Momjian <bruce@momjian.us> wrote:
> > The answer is either chroot or mount and run pg_upgrade on another
> > server. If you can afford the downtime you can also delete PG,
> > install the new version and run pg_upgrade without modifying the
> > existing DB. If it succeeds then replace the directories and
> > restart the new version.  If it fails then uninstall PG, reinstall
> > the older version and restart. Lather, rinse, repeat until it
> > upgrades cleanly.
>
> pg_upgrade needs to run the old and new server binaries as part of its
> operation, so that would not work.

My mistake.  I must have used the chroot idea last time I did an
upgrade.

I might take a look at the NetBSD package (I'm a developer) to see how
hard it would be to allow multiple versions.  We do keep all the lib
stuff in a separate directory so that part would be relatively simple.
We just need to find all the binaries and make the names versioned and
add a symlink to the user selected primary version to the bare version
of the binary name.  Example:
 - psql.8.3
 - psql.9.1
 - psql.9.3
 - psql ==> psql.9.3

Other than linking to the correct library can you think of any other
issues with this?

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net


Re: Allowing multiple versions of PG under NetBSD

От
Larry Rosenman
Дата:
On 2016-07-29 15:04, D'Arcy J.M. Cain wrote:
> On Fri, 29 Jul 2016 15:07:53 -0400
> Bruce Momjian <bruce@momjian.us> wrote:
>> > The answer is either chroot or mount and run pg_upgrade on another
>> > server. If you can afford the downtime you can also delete PG,
>> > install the new version and run pg_upgrade without modifying the
>> > existing DB. If it succeeds then replace the directories and
>> > restart the new version.  If it fails then uninstall PG, reinstall
>> > the older version and restart. Lather, rinse, repeat until it
>> > upgrades cleanly.
>>
>> pg_upgrade needs to run the old and new server binaries as part of its
>> operation, so that would not work.
>
> My mistake.  I must have used the chroot idea last time I did an
> upgrade.
>
> I might take a look at the NetBSD package (I'm a developer) to see how
> hard it would be to allow multiple versions.  We do keep all the lib
> stuff in a separate directory so that part would be relatively simple.
> We just need to find all the binaries and make the names versioned and
> add a symlink to the user selected primary version to the bare version
> of the binary name.  Example:
>  - psql.8.3
>  - psql.9.1
>  - psql.9.3
>  - psql ==> psql.9.3
>
> Other than linking to the correct library can you think of any other
> issues with this?

Data Directory naming, as well as keeping the init-scripts straight.

--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281


Re: Allowing multiple versions of PG under NetBSD

От
Larry Rosenman
Дата:
On 2016-07-29 15:06, Larry Rosenman wrote:
> On 2016-07-29 15:04, D'Arcy J.M. Cain wrote:
>> On Fri, 29 Jul 2016 15:07:53 -0400
>> Bruce Momjian <bruce@momjian.us> wrote:
>>> > The answer is either chroot or mount and run pg_upgrade on another
>>> > server. If you can afford the downtime you can also delete PG,
>>> > install the new version and run pg_upgrade without modifying the
>>> > existing DB. If it succeeds then replace the directories and
>>> > restart the new version.  If it fails then uninstall PG, reinstall
>>> > the older version and restart. Lather, rinse, repeat until it
>>> > upgrades cleanly.
>>>
>>> pg_upgrade needs to run the old and new server binaries as part of
>>> its
>>> operation, so that would not work.
>>
>> My mistake.  I must have used the chroot idea last time I did an
>> upgrade.
>>
>> I might take a look at the NetBSD package (I'm a developer) to see how
>> hard it would be to allow multiple versions.  We do keep all the lib
>> stuff in a separate directory so that part would be relatively simple.
>> We just need to find all the binaries and make the names versioned and
>> add a symlink to the user selected primary version to the bare version
>> of the binary name.  Example:
>>  - psql.8.3
>>  - psql.9.1
>>  - psql.9.3
>>  - psql ==> psql.9.3
>>
>> Other than linking to the correct library can you think of any other
>> issues with this?
>
> Data Directory naming, as well as keeping the init-scripts straight.
>
And who gets 5432, and Unix socket naming, it starts to get messy.....
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281


Re: Allowing multiple versions of PG under NetBSD

От
Bruce Momjian
Дата:
On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote:
> >>I might take a look at the NetBSD package (I'm a developer) to see how
> >>hard it would be to allow multiple versions.  We do keep all the lib
> >>stuff in a separate directory so that part would be relatively simple.
> >>We just need to find all the binaries and make the names versioned and
> >>add a symlink to the user selected primary version to the bare version
> >>of the binary name.  Example:
> >> - psql.8.3
> >> - psql.9.1
> >> - psql.9.3
> >> - psql ==> psql.9.3
> >>
> >>Other than linking to the correct library can you think of any other
> >>issues with this?
> >
> >Data Directory naming, as well as keeping the init-scripts straight.
> >
> And who gets 5432, and Unix socket naming, it starts to get messy.....

Well, pg_upgrade uses a socket created in the current run directory, so
that should be fine.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Allowing multiple versions of PG under NetBSD

От
Larry Rosenman
Дата:
On 2016-07-29 15:14, Bruce Momjian wrote:
> On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote:
>> >>I might take a look at the NetBSD package (I'm a developer) to see how
>> >>hard it would be to allow multiple versions.  We do keep all the lib
>> >>stuff in a separate directory so that part would be relatively simple.
>> >>We just need to find all the binaries and make the names versioned and
>> >>add a symlink to the user selected primary version to the bare version
>> >>of the binary name.  Example:
>> >> - psql.8.3
>> >> - psql.9.1
>> >> - psql.9.3
>> >> - psql ==> psql.9.3
>> >>
>> >>Other than linking to the correct library can you think of any other
>> >>issues with this?
>> >
>> >Data Directory naming, as well as keeping the init-scripts straight.
>> >
>> And who gets 5432, and Unix socket naming, it starts to get messy.....
>
> Well, pg_upgrade uses a socket created in the current run directory, so
> that should be fine.
if we're talking JUST for pg_upgrade, that's one thing.  Peaceful
co-existence on an ongoing basis
is quite another.....
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281


Re: Allowing multiple versions of PG under NetBSD

От
"D'Arcy J.M. Cain"
Дата:
On Fri, 29 Jul 2016 15:09:59 -0500
Larry Rosenman <ler@lerctr.org> wrote:
> >> version to the bare version of the binary name.  Example:
> >>  - psql.8.3
> >>  - psql.9.1
> >>  - psql.9.3
> >>  - psql ==> psql.9.3
> >>
> >> Other than linking to the correct library can you think of any
> >> other issues with this?
> >
> > Data Directory naming, as well as keeping the init-scripts straight.
> >
> And who gets 5432, and Unix socket naming, it starts to get messy.....

I assume that anyone running multiple versions knows how to specify all
that stuff when initializing and starting the different servers.

From the comments I saw it looked to me like other systems (Linux?) had
already solved this problem.  Was I wrong?

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net


Re: Uber migrated from Postgres to MySQL

От
Condor
Дата:
On 29-07-2016 20:33, Jerry Sievers wrote:
> Condor <condor@stz-bg.com> writes:
>
>> On 26-07-2016 21:04, Dorian Hoxha wrote:
>>
>>> Many comments: https://news.ycombinator.com/item?id=12166585
>>> https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
>>>
>>> On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com>
>>> wrote:
>>>
>>>> Honestly, I've never heard of anyone doing that. But it sounds like
>>>> they had good reasons.
>>>>
>>>> https://eng.uber.com/mysql-migration/
>>>>
>>>> Thoughts?
>>>>
>>>> --
>>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>> They are right for upgrades.
>> It's a hard to shutdown 1 TB database and wait couple of days
>> pg_upgrade to finish upgrade and meanwhile database is offline.
>> In some distros after upgrade of PG version you don't have old binary
>> and library, need to do full dump and restore that take time and disk
>> space.
>
> Yeah, very hard indeed when done by unskilled DBAs :-)
>
> I've done several ~7TB pg_upgrades and with the hard link option and a
> framework that parallelizes the post-analyzer phase...
>
> ...45 minutes till completion.
>
>
>>
>> Regards,
>> Hristo S.

GL to you


Re: Allowing multiple versions of PG under NetBSD

От
Adrian Klaver
Дата:
On 07/29/2016 01:17 PM, D'Arcy J.M. Cain wrote:
> On Fri, 29 Jul 2016 15:09:59 -0500
> Larry Rosenman <ler@lerctr.org> wrote:
>>>> version to the bare version of the binary name.  Example:
>>>>  - psql.8.3
>>>>  - psql.9.1
>>>>  - psql.9.3
>>>>  - psql ==> psql.9.3
>>>>
>>>> Other than linking to the correct library can you think of any
>>>> other issues with this?
>>>
>>> Data Directory naming, as well as keeping the init-scripts straight.
>>>
>> And who gets 5432, and Unix socket naming, it starts to get messy.....
>
> I assume that anyone running multiple versions knows how to specify all
> that stuff when initializing and starting the different servers.
>
> From the comments I saw it looked to me like other systems (Linux?) had
> already solved this problem.  Was I wrong?
>
No:

https://wiki.debian.org/PostgreSql

--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Allowing multiple versions of PG under NetBSD

От
Larry Rosenman
Дата:
On 2016-07-29 15:17, D'Arcy J.M. Cain wrote:
> On Fri, 29 Jul 2016 15:09:59 -0500
> Larry Rosenman <ler@lerctr.org> wrote:
>> >> version to the bare version of the binary name.  Example:
>> >>  - psql.8.3
>> >>  - psql.9.1
>> >>  - psql.9.3
>> >>  - psql ==> psql.9.3
>> >>
>> >> Other than linking to the correct library can you think of any
>> >> other issues with this?
>> >
>> > Data Directory naming, as well as keeping the init-scripts straight.
>> >
>> And who gets 5432, and Unix socket naming, it starts to get messy.....
>
> I assume that anyone running multiple versions knows how to specify all
> that stuff when initializing and starting the different servers.
>
> From the comments I saw it looked to me like other systems (Linux?) had
> already solved this problem.  Was I wrong?

They have, we (*BSD) just need to make sure we document it, and we make
damn
sure we don't break existing folks.

I'm willing to help on the FreeBSD side.

--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281


Re: Uber migrated from Postgres to MySQL

От
Kevin Grittner
Дата:
On Fri, Jul 29, 2016 at 3:18 PM, Condor <condor@stz-bg.com> wrote:
> On 29-07-2016 20:33, Jerry Sievers wrote:

>> I've done several ~7TB pg_upgrades and with the hard link option and a
>> framework that parallelizes the post-analyzer phase...
>>
>> ...45 minutes till completion.

> GL to you

Luck has nothing to do with anything.  You really might want to
post with more details and see whether people can help sort out why
you have seen such slow performance where so many others have not.
I hope it's not just a matter of saying "I have a 1TB database and
upgrade is slow, therefore it is slow because it is 1TB."  That
would be roughly the equivalent of saying "I have a blue car and it
is slow, therefore it is slow because it is blue."  It just might
be the flat tire that actually matters.  If your upgrade is slow
because you have 10 million database objects, that might be a hard
one to overcome, but it might be something with an easy solution in
the pg_upgrade options or server configuration.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Allowing multiple versions of PG under NetBSD

От
Kevin Grittner
Дата:
On Fri, Jul 29, 2016 at 3:22 PM, Larry Rosenman <ler@lerctr.org> wrote:

> I'm willing to help on the FreeBSD side.

One more tip -- if you are running multiple clusters (same version
or not) on the same machine, it is best to run each cluster under a
separate OS user.  It's not *required*, but it makes a restart
after a crash less problematic and it is generally better from a
security standpoint, so you might want to look for a way to allow
it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Allowing multiple versions of PG under NetBSD

От
Stephen Frost
Дата:
* Larry Rosenman (ler@lerctr.org) wrote:
> On 2016-07-29 15:14, Bruce Momjian wrote:
> >On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote:
> >>>Data Directory naming, as well as keeping the init-scripts straight.
> >>>
> >>And who gets 5432, and Unix socket naming, it starts to get messy.....
> >
> >Well, pg_upgrade uses a socket created in the current run directory, so
> >that should be fine.
> if we're talking JUST for pg_upgrade, that's one thing.  Peaceful
> co-existence on an ongoing basis
> is quite another.....

It's not an insurmountable problem, though it's a bit painful.  Still,
both the Debian-based and RedHat-based distributions demonstrate how it
can be done.

Thanks!

Stephen

Вложения

Re: Allowing multiple versions of PG under NetBSD

От
Thomas Munro
Дата:
On Sat, Jul 30, 2016 at 8:51 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Larry Rosenman (ler@lerctr.org) wrote:
>> On 2016-07-29 15:14, Bruce Momjian wrote:
>> >On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote:
>> >>>Data Directory naming, as well as keeping the init-scripts straight.
>> >>>
>> >>And who gets 5432, and Unix socket naming, it starts to get messy.....
>> >
>> >Well, pg_upgrade uses a socket created in the current run directory, so
>> >that should be fine.
>> if we're talking JUST for pg_upgrade, that's one thing.  Peaceful
>> co-existence on an ongoing basis
>> is quite another.....
>
> It's not an insurmountable problem, though it's a bit painful.  Still,
> both the Debian-based and RedHat-based distributions demonstrate how it
> can be done.

As a user of FreeBSD (as of recently) and Debian (since forever), I
have often thought that I''d really like to see a FreeBSD port of the
excellent postgresql-common package[1], and then
postgresql[major][minor] ports that can be installed in parallel.  The
binaries would have be installed under somewhere like
/usr/local/libexec/postgresql/[major].[minor], the default pg_data
location would have to be somewhere like like
/usr/local/pg_data/[major].[minor]/[cluster-name], the config files
somewhere like /usr/local/etc/postgresql/[major].[minor]/[cluster-name]/postgresql.conf
(or they could stay inside the pg_data dirs, as long as they can be
found by the postgresql-common cluster management scripts), and the
postgresql-common port would provide wrapper commands like
pg_createcluster, pg_lsclusters, ... and that psql wrapper that takes
an extra optional switch like --cluster 9.5/main.  The client library
on the other hand would not be versioned in that way: there would be
just the latest major version's libpq5[2], and that is what other
things like py-psycopg2 etc would depend on (instead of depending on a
specific client major version like postgresql93-client).

[1] https://packages.debian.org/jessie/postgresql-common
[2] https://packages.debian.org/jessie/libpq5

--
Thomas Munro
http://www.enterprisedb.com


Re: Uber migrated from Postgres to MySQL

От
Jason Dusek
Дата:


On Thu, 28 Jul 2016 at 01:18 Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 28/07/16 17:52, Jason Dusek wrote:
> With regards to write amplification, it makes me think about about
> OIDs. Used to be, every row had an OID and that OID persisted across
> row versions.
>
> https://www.postgresql.org/docs/9.5/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS
>
> Would reintroducing such a feature address some of Uber's concerns
> about multiple indexes? It could, and would do so without the implicit
> requirement of a foreign key; but it would also require a fast OID to
> CTID mapping.

Would it be best to increase OIDs to 64 bits?

Possibly a choice of 32/64 to be decided when the DB is created???

Moving to 64 bit OIDs would make the OID->CTID mapping take more memory and could, consequently, make it slower; but OIDs would seem to be required to support temporal tables so maybe they should make a comeback?

Kind Regards,
  Jason Dusek

Re: Uber migrated from Postgres to MySQL

От
Jeff Janes
Дата:
On Thu, Jul 28, 2016 at 3:16 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote:
>> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
>> > On 7/27/2016 9:39 PM, Jeff Janes wrote:
>> >>
>> >> That depends on how how many objects there are consuming that 1 TB.
>> >> With millions of small objects, you will have problems.  Not as many
>> >> in 9.5 as there were in 9.1, but still it does not scale linearly in
>> >> the number of objects.  If you only have thousands of objects, then as
>> >> far as I know -k works like a charm.
>> >
>> >
>> > millions of tables?
>>
>> Well, it was a problem at much smaller values, until we fixed many of
>> them.  But the perversity is, if you are stuck on a version before the
>> fixes, the problems prevent you from getting to a version on which it
>> is not a problem any more.
>
> Uh, that is only true if the slowness was in _dumping_ many objects.
> Most of the fixes have been for _restoring_ many objects, and that is
> done in the new cluster, so they should be OK.

There have been improvements on both sides.  For the improvements that
need to exist in the old-server to be effective, we did backpatch the
main one back to 9.1, in the October 2015 releases, specifically to
help people get off the old versions.  So if you are on 9.1 with
tens/hundreds of thousands of objects, you need to do a minor version
upgrade to at least 9.1.19 before doing the major version upgrade.  If
you are on 9.0 or before with so many objects, you don't have a lot of
good options.


Cheers,

Jeff


Re: Uber migrated from Postgres to MySQL

От
Bruce Momjian
Дата:
On Mon, Aug  1, 2016 at 11:54:00AM -0700, Jeff Janes wrote:
> > Uh, that is only true if the slowness was in _dumping_ many objects.
> > Most of the fixes have been for _restoring_ many objects, and that is
> > done in the new cluster, so they should be OK.
>
> There have been improvements on both sides.  For the improvements that
> need to exist in the old-server to be effective, we did backpatch the
> main one back to 9.1, in the October 2015 releases, specifically to
> help people get off the old versions.  So if you are on 9.1 with
> tens/hundreds of thousands of objects, you need to do a minor version
> upgrade to at least 9.1.19 before doing the major version upgrade.  If
> you are on 9.0 or before with so many objects, you don't have a lot of
> good options.

Yeah, that's what I remember.  Thanks for the details.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +


Re: Uber migrated from Postgres to MySQL

От
Tatsuo Ishii
Дата:
> On 27/07/16 18:54, Chris Travers wrote:
>> Another one I think they obliquely referred to (in the subtle problems
>> section) was the fact that if you have longer-running queries on the
>> replica with a lot of updates, you can get funny auto-vacuum-induced
>> errors (writes from autovacuum on the master can interrupt queries on
>> the slave).  BTW if there is interest in what could be done for that,
>> something which allows autovacuum to decide how long to wait before
>> cleaning up dead tuples would be a great enhancement.
>
> You mean something like hot_standby_feedback?
>
> https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK

I wonder if their problem could be fixed by using
hot_standby_feedback.  I have encountered similar problem but it seems
hot_standby_feedback was not any help in this case:

https://www.postgresql.org/message-id/20130829.164457.863984798767991096.t-ishii%40sraoss.co.jp

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


Re: Uber migrated from Postgres to MySQL

От
Simon Riggs
Дата:
On 5 August 2016 at 09:16, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
>> On 27/07/16 18:54, Chris Travers wrote:
>>> Another one I think they obliquely referred to (in the subtle problems
>>> section) was the fact that if you have longer-running queries on the
>>> replica with a lot of updates, you can get funny auto-vacuum-induced
>>> errors (writes from autovacuum on the master can interrupt queries on
>>> the slave).  BTW if there is interest in what could be done for that,
>>> something which allows autovacuum to decide how long to wait before
>>> cleaning up dead tuples would be a great enhancement.
>>
>> You mean something like hot_standby_feedback?
>>
>> https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
>
> I wonder if their problem could be fixed by using
> hot_standby_feedback.  I have encountered similar problem but it seems
> hot_standby_feedback was not any help in this case:
>
> https://www.postgresql.org/message-id/20130829.164457.863984798767991096.t-ishii%40sraoss.co.jp

There have been various bugs and enhancements over the years, not all
of which were backpatched.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Uber migrated from Postgres to MySQL

От
Tatsuo Ishii
Дата:
>>> https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
>>
>> I wonder if their problem could be fixed by using
>> hot_standby_feedback.  I have encountered similar problem but it seems
>> hot_standby_feedback was not any help in this case:
>>
>> https://www.postgresql.org/message-id/20130829.164457.863984798767991096.t-ishii%40sraoss.co.jp
>
> There have been various bugs and enhancements over the years, not all
> of which were backpatched.

The paticular case still does not work with PostgreSQL 9.5.3.

On primary:
create table t1(i int);
insert into t1 values(1),(2),(3);

On standby:
begin;
test=# select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
(1 row)

select * from t1;
 i
---
 1
 2
 3
(3 rows)

On primary:
delete from t1;

On standby:
select * from t1;
 i
---
(0 rows)

On primary:
test=# vacuum verbose t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": removed 3 row versions in 1 pages
INFO:  "t1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "t1": truncated 1 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

After while on standby:
test=# select * from t1;
FATAL:  terminating connection due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


Re: Uber migrated from Postgres to MySQL

От
Simon Riggs
Дата:
On 5 August 2016 at 14:06, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> On primary:
> test=# vacuum verbose t1;
> INFO:  vacuuming "public.t1"
> INFO:  "t1": removed 3 row versions in 1 pages
> INFO:  "t1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> Skipped 0 pages due to buffer pins.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "t1": truncated 1 to 0 pages
> DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
> VACUUM
>
> After while on standby:
> test=# select * from t1;
> FATAL:  terminating connection due to conflict with recovery
> DETAIL:  User was holding a relation lock for too long.
> HINT:  In a moment you should be able to reconnect to the database and repeat your command.
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.

Yes, the VACUUM truncation is still an issue. But statements are
retryable, just like deadlocks.

Unfo the truncation logic always kicks in or small tables of less than
16 blocks. It's more forgiving on bigger tables.

Maybe we could defer the truncation on the standby in some cases.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Uber migrated from Postgres to MySQL

От
Tatsuo Ishii
Дата:
> Yes, the VACUUM truncation is still an issue. But statements are
> retryable, just like deadlocks.
>
> Unfo the truncation logic always kicks in or small tables of less than
> 16 blocks. It's more forgiving on bigger tables.

Oh, I didn't know that. Thanks for the info.

> Maybe we could defer the truncation on the standby in some cases.

Do we want to add this to the TODO list?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


Re: Uber migrated from Postgres to MySQL

От
Achilleas Mantzios
Дата:
On 29/07/2016 21:06, Larry Rosenman wrote:
> On 2016-07-29 12:59, Bruce Momjian wrote:
>> On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote:
>>> And yes, I hate upgrading PostgreSQL especially on FreeBSD where pg_upgrade
>>> isn’t really an option.
>>
>> Is that because it is hard to install the old and new clusters on the
>> same server on FreeBSD?
>>
> The current FreeBSD Ports collection ports only allow ONE version to be installed at a time.
>
>

In our installation, pgsql is one of the very few packages that we prefer to deal by hand.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: Uber migrated from Postgres to MySQL

От
Amitabh Kant
Дата:

On Tue, Aug 16, 2016 at 1:08 PM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 29/07/2016 21:06, Larry Rosenman wrote:
On 2016-07-29 12:59, Bruce Momjian wrote:
On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote:
And yes, I hate upgrading PostgreSQL especially on FreeBSD where pg_upgrade
isn’t really an option.

Is that because it is hard to install the old and new clusters on the
same server on FreeBSD?

The current FreeBSD Ports collection ports only allow ONE version to be installed at a time.



In our installation, pgsql is one of the very few packages that we prefer to deal by hand.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



From my discussion with one of the  maintainers of the port on FreeBSD, support for multiple versions is being worked upon. Hopefully we should have it some time soon.

--
Amitabh