Re: Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 7:23 PM, Greg Sabino Mullane <greg(at)turnstep(dot)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