equivalent thing of mtr in mysql

[merryok]

Hi, guys. I'm new here.


I'm eager to figure out what is the equivalent thing of mtr in mysql in PG. 
When a dml operation occurs, it may modify multiple pages and gererate multiple 
redo log records. mtr can make those logs atomically be transferred to log 
buffer and written to disk. 
I think same situation exists in PG, but I can't find something like mtr, why ?


Any blogs that explain this will be appreciated.

Re: equivalent thing of mtr in mysql

[Peter J. Holzer]

On 2022-07-12 22:39:31 +0800, merryok wrote:
> Hi, guys. I'm new here.
> 
> I'm eager to figure out what is the equivalent thing of mtr in mysql in PG.

What is MTR? A search for "mtr mysql" yields "mysql test run" and
"multi-threaded replication", neither of which seems to be what you are
talking about. When referring to concepts from other databases, please
include a link to the relevant documentation.

> When a dml operation occurs, it may modify multiple pages and gererate 
> multiple
> redo log records. mtr can make those logs atomically be transferred to log
> buffer and written to disk. 
> I think same situation exists in PG, but I can't find something like mtr, why 
> ?

Transactions in PostgreSQL are always atomic (That should also be the
case with MySQL, unless you use MyISAM tables). Ensuring that these
changes also result in an atomic disk write seems to be both pointless
and impossible (that might be many gigabytes of data).

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: equivalent thing of mtr in mysql

[Rob Sargent]

On 7/12/22 12:57, Peter J. Holzer wrote:

On 2022-07-12 22:39:31 +0800, merryok wrote:

Hi, guys. I'm new here.

I'm eager to figure out what is the equivalent thing of mtr in mysql in PG.

What is MTR? A search for "mtr mysql" yields "mysql test run" and
"multi-threaded replication", neither of which seems to be what you are
talking about. When referring to concepts from other databases, please
include a link to the relevant documentation.


When a dml operation occurs, it may modify multiple pages and gererate multiple
redo log records. mtr can make those logs atomically be transferred to log
buffer and written to disk.
I think same situation exists in PG, but I can't find something like mtr, why ?


I thought OP was hinting at WAL stuff defn here 


Transactions in PostgreSQL are always atomic (That should also be the
case with MySQL, unless you use MyISAM tables). Ensuring that these
changes also result in an atomic disk write seems to be both pointless
and impossible (that might be many gigabytes of data).

 hp

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

[Laurenz Albe]

On Mon, 2022-07-11 at 05:37 +, Ken Yeung wrote:
> In the course of upgrading a bunch of database clusters from 10 to 14 using 
> pg_upgrade tool, some databases result in the following error recorded in 
> pg_upgrade_dump_1.log, such that cluster upgrade
> fails.
> FATAL:  password authentication failed for user "postgres"
> 
> This was done on Microsoft Windows 11.
> PostgreSQL 10 and 14 used are built using Visual Studio 2015.
> Only a small number of database clusters has this problem, around 2 of 200.
> It appears these database are used from earlier cluster upgrade from 9.4 to 
> 10 as well.
> 
> It would be very helpful if there is any possible workaround to overcome this 
> problem.
> 
> I attached a sample pg_upgrade_dump_1.log reported this error.

You configured "pg_hba.conf" so that a local login without password is
not possible.  The easiest solution is to change the "pg_hba.conf"
entries for "localhost" in IPv4 and IPv6 to "trust" for the duration of
the upgrade.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: equivalent thing of mtr in mysql

[Peter J. Holzer]

On 2022-07-12 13:07:41 -0600, Rob Sargent wrote:
> I thought OP was hinting at WAL stuff defn here

So did I.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Michael Paquier]

On Mon, Jul 11, 2022 at 10:50:23AM +0200, Aleš Zelený wrote:
> So far, it has happened three times (during a single week) from the 14.3 ->
> 14.4 upgrade, before 14.4 we haven't suffered from such an issue.
> 
> Questions:
> 1)  Can we safely downgrade from 14.4 to 14.3 by shutting down the instance
> and reinstalling 14.3 PG packages (to prove, that the issue disappear)?
> 2) What is the best way to diagnose what is the root cause?

Hmm.  14.4 has nothing in its release notes that would point to a
change in the vacuum or autovacuum's code paths:
https://www.postgresql.org/docs/14/release-14-4.html#id-1.11.6.5.4

There is nothing specific after a look at the changes as of, and I am
not grabbing anything that would imply a change in memory context
handling either:
`git log --stat REL_14_3..REL_14_4`
`git diff REL_14_3..REL_14_4 -- *.c`

Saying that, you should be able to downgrade safely as there are no
changes in WAL format or such that would break things.  Saying that,
the corruption issue caused by CONCURRENTLY is something you'd still
have to face.

> 2022-07-02 14:48:07 CEST [3930]: [3-1] user=,db=,host=,app= ERROR:  out of
> memory
> 2022-07-02 14:48:07 CEST [3930]: [4-1] user=,db=,host=,app= DETAIL:  Failed
> on request of size 152094068 in memory context "TopTransactionContext".
> 2022-07-02 14:48:07 CEST [3930]: [5-1] user=,db=,host=,app= CONTEXT:
>  automatic vacuum of table "prematch.replication.tab_queue_tmp"

This is the interesting part.  Do you happen to use logical
replication in a custom C++ plugin?

> 2022-07-02 14:48:47 CEST [4476]: [43-1] user=,db=,host=,app= LOG:  could
> not fork worker process: Cannot allocate memory
> terminate called after throwing an instance of 'std::bad_alloc'
>   what():  std::bad_alloc
>
> DETAIL: parameters: $1 = '1', $2 = '1748010445', $3 = '0', $4 = '1000'
> terminate
> called after throwing an instance of 'std::bad_alloc' terminate called
> after throwing an instance of 'std::bad_alloc' what(): what():
> std::bad_allocstd::bad_alloc 2022-07-08 14:54:23 CEST [4476]: [49-1]
> user=,db=,host=,app= LOG: background worker "parallel worker" (PID 25251)
> was terminated by signal 6: Aborted
> 2022-07-08 14:54:23 CEST [4476]: [51-1] user=,db=,host=,app= LOG:
>  terminating any other active server processes

Looks like something is going wrong in the memory handling of one of
your C++ extensions here.  If you can isolate an issue using a query
without any custom code, that would be a Postgres problem, but I think
that you are missing a trick in it.
--
Michael


signature.asc
Description: PGP signature

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

[Ken Yeung]

Hi,

From: Thomas Boussekey 
Sent: Tuesday, July 12, 2022 3:39 AM
> Hi,

> > Le lun. 11 juil. 2022 à 21:22, Ken Yeung  a 
> > écrit :
> > In the course of upgrading a bunch of database clusters from 10 to 14 using 
> > pg_upgrade tool, some databases result in the following error recorded in 
> > pg_upgrade_dump_1.log, such that cluster upgrade fails.
> > FATAL:  password authentication failed for user "postgres"
> This error happens on the restoration-side (Pg14)

> pg_restore: error: connection to server at "localhost" (::1), port 50432 
> failed: FATAL:  password authentication failed for user "postgres"

> means that you try to use ipv6 (is it possible, according to your target 
> postgresql.conf file?)
> Does your Pg14 target pg_hba.conf file allow this kind of connection?

Although we have some different settings in pg_hba.conf in different clusters, 
but both at least allows local connection to ::1.

Specific to each cluster, there are some other databases upgraded/restored in 
the same cluster without this problem, but only "template1" reported this 
error.  I attached some another log file recorded.
There may be a possibility some tables were created in the cluster with older 
pg_hba.conf settings, and then pg_hba.conf changed afterwards.  Would this 
situation possibly caused the reported error?

Best regards,
Ken

pg_upgrade_dump_12292.log
Description: pg_upgrade_dump_12292.log


pg_upgrade_dump_28137747.log
Description: pg_upgrade_dump_28137747.log

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

[Laurenz Albe]

On Wed, 2022-07-13 at 02:53 +, Ken Yeung wrote:
> From: Laurenz Albe 
> Sent: Wednesday, July 13, 2022 3:16 AM
> 
> > You configured "pg_hba.conf" so that a local login without password is
> > not possible.  The easiest solution is to change the "pg_hba.conf"
> > entries for "localhost" in IPv4 and IPv6 to "trust" for the duration of
> > the upgrade.
> 
> I am afraid this workaround may not be suitable for our case because of 
> certain security consideration.

If you want to make your life difficult, that's ok.  Remember that this change
would only be for *local* connections for the duration of the upgrade.

As an alternative, you could create a password file or, if that is also too
insecure, set the PGSSLCERT and PGSSLKEY environment variables so that you can
use a client certificate to connect.  Other authentication methods might also
be possible; you'll have to experiment.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com