equivalent thing of mtr in mysql

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.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

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
<https://www.postgresql.org/docs/current/wal-intro.html&gt;

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.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

I'm sorry, first time to post a thread. mtr is short for mini-transaction.
About mtr in mysql, we can refer to https://dev.mysql.com/doc/refman/8.0/en/glossary.html or
https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/

So an insert in mysql, wrapped in a user transaction, may result in multiple mini-transactions, one is responsible for writing redo for undo, the other one for writing redo for clustered index/secondary index, and so on. Each mtr may modify multiple pages, whose redo logs are guaranteed by mtr to be written into disk or none of them are written.

At 2022-07-13 02:57:28, "Peter J. Holzer" <hjp-pgsql@hjp.at> wrote:

On 7/15/22 18:31, merryok wrote:

I'm sorry, first time to post a thread. mtr is short for mini-transaction.
About mtr in mysql, we can refer to
https://dev.mysql.com/doc/refman/8.0/en/glossary.html
<https://dev.mysql.com/doc/refman/8.0/en/glossary.html&gt; or
https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/
<https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/&gt;

So an insert in mysql,  wrapped in a user transaction, may result in
multiple mini-transactions, one is responsible for writing redo for
undo, the other one for writing redo for clustered index/secondary
index, and so on. Each mtr may modify multiple pages,  whose redo logs
are guaranteed by mtr to be written into disk or none of them are written.

Read this:

https://www.postgresql.org/docs/current/wal.html

and see if it answers your questions.

--
Adrian Klaver
adrian.klaver@aklaver.com

<div dir="ltr">I&#39;ve read the doc, and it doesn&#39;t help too much.<br>
</div><div dir="ltr">Finally I&#39;ve found START_CRIT_SECTION and END_CRIT_SECTION. It&#39;s like mtr.start(), mtr.commit() in mysql. May I ask why many places are wrapped into START_CRIT_SECTION/END_CRIT_SECTION during a single dml operation ?<br>
</div><div dir="ltr"><br>
</div><div dir="ltr">And if Assert(CritSectionCount &gt; 0) isn&#39;t satisfied (CritSectionCount need&#39;t be protected ?), PG server will panic and exit ? If so, what&#39;s the probability of that ?<br>
</div><div dir="ltr"><br>
</div><div dir="ltr"><br>
</div><div dir="ltr"><br>
</div><div dir="ltr"><br>
</div><div dir="ltr"><br>
</div><div class="wps_signature">发自我的小米手机</div><div class="wps_quotion">在 Adrian Klaver &lt;adrian.klaver@aklaver.com&gt;，2022年7月16日 23:30写道：<br type="attribution"><blockquote class="quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p></p><p dir="ltr">On 7/15/22 18:31, merryok wrote:
<br>
&gt; I&#39;m sorry, first time to post a thread. mtr is short for mini-transaction.
<br>
&gt; About mtr in mysql, we can refer to
<br>
&gt; <a href="https://dev.mysql.com/doc/refman/8.0/en/glossary.html&quot;&gt;https://dev.mysql.com/doc/refman/8.0/en/glossary.html&lt;/a&gt;
<br>
&gt; &lt;<a href="https://dev.mysql.com/doc/refman/8.0/en/glossary.html&quot;&gt;https://dev.mysql.com/doc/refman/8.0/en/glossary.html&lt;/a&gt;&amp;gt; or
<br>
&gt; <a href="https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design&quot;&gt;https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design&lt;/a&gt;/
<br>
&gt; &lt;<a href="https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design&quot;&gt;https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design&lt;/a&gt;/&amp;gt;
<br>
&gt;
<br>
&gt;
<br>
&gt;
<br>
&gt; So an insert in mysql,  wrapped in a user transaction, may result in
<br>
&gt; multiple mini-transactions, one is responsible for writing redo for
<br>
&gt; undo, the other one for writing redo for clustered index/secondary
<br>
&gt; index, and so on. Each mtr may modify multiple pages,  whose redo logs
<br>
&gt; are guaranteed by mtr to be written into disk or none of them are written.
<br>
&gt;
<br>

<br>
Read this:
<br>

<br>
<a href="https://www.postgresql.org/docs/current/wal.html&quot;&gt;https://www.postgresql.org/docs/current/wal.html&lt;/a&gt;
<br>

<br>
and see if it answers your questions.
<br>

<br>

<br>
--
<br>
Adrian Klaver
<br>
<a href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>
<br>
</p>
</blockquote></div>

On Jul 18, 2022, at 20:03, merryok <merryok@163.com> wrote:

I've read the doc, and it doesn't help too much.
Finally I've found START_CRIT_SECTION and END_CRIT_SECTION. It's like mtr.start(), mtr.commit() in mysql. May I ask why many places are wrapped into START_CRIT_SECTION/END_CRIT_SECTION during a single dml operation ?

A PostgreSQL critical section is not the equivalent of MySQL InnoDB mini-transaction.

A critical section in PostgreSQL is a section of code that needs to run without interruption to avoid corruption of internal in-memory data structures.

PostgreSQL doesn't have a direct equivalent of a MySQL mini-transaction. When WAL information is created by a statement, it's stored in the WAL buffers, and then flushed to disk by the WAL writer (to a first approximation). There's no special operation that groups pages together for atomic writes; that's done by the underlying file system flush operation.

And if Assert(CritSectionCount > 0) isn't satisfied (CritSectionCount need't be protected ?), PG server will panic and exit ? If so, what's the probability of that ?

If it occurs, it indicates a bug in PostgreSQL. It is *extremely* infrequent (as in, you can go years without seeing one; I can't remember the last time I did).