Re: Uber migrated from Postgres to MySQL

On 5 August 2016 at 14:06, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)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