Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5380

binlog row format and table without PK

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: 5.5.34
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Environment:
      Using MariaDB Generic Linux packages on Debian Wheezy 64-bits

      Description

      I set a replication master -> slave.
      I create a simple table with NO Primary Key
      I populate data (a few thousands rows)

      Then, I switch binlog format to ROW
      I update rows on the master (it takes a few seconds to execute)

      => On slave, it takes a very long time to apply changes (on my original table with 200'000 rows, I skipped the query (with SQL_SLAVE_SKIP_COUNTER) after 20 minutes still in "Reading event from the relay log" state...).

      If I create a primary key, changes are applied instantly on slave.

      Here is a test case to reproduce the problem :

      MASTER
      --------

      mydb=# CREATE TABLE `nptest` (
      -> `id` int(11) NOT NULL DEFAULT '0',
      -> `tmp` tinyint(4) DEFAULT NULL
      -> );
      Query OK, 0 rows affected (0.07 sec)

      mydb=# INSERT INTO nptest (id) SELECT id FROM othertable;
      Query OK, 192743 rows affected (1.83 sec)
      Records: 192743 Duplicates: 0 Warnings: 0

      mydb=# SET binlog_format = ROW;
      Query OK, 0 rows affected (0.00 sec)

      mydb=# UPDATE nptest SET tmp = 1;
      Query OK, 192743 rows affected (3.76 sec)
      Rows matched: 192743 Changed: 192743 Warnings: 0

      SLAVE
      --------

      (none)=# SHOW PROCESSLIST\G

      *************************** 23. row ***************************
      Id: 627527
      User: system user
      Host:
      db: NULL
      Command: Connect
      Time: 103
      State: Reading event from the relay log
      Info: NULL
      Progress: 0.000

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              Hide
              elenst Elena Stepanova added a comment -

              It's not really surprising though, is it? You run one update on the master, it triggers 200,000 updates on the slave, each of which has to look up a row without any index.

              Is there any particular reason why you choose not to use a PK?

              MySQL bug report for the reference (there were many before and after, but I think this one is most representative):

              http://bugs.mysql.com/bug.php?id=53375

              Show
              elenst Elena Stepanova added a comment - It's not really surprising though, is it? You run one update on the master, it triggers 200,000 updates on the slave, each of which has to look up a row without any index. Is there any particular reason why you choose not to use a PK? MySQL bug report for the reference (there were many before and after, but I think this one is most representative): http://bugs.mysql.com/bug.php?id=53375
              Hide
              nico Nicolas Payart added a comment -

              Thank you Elena. Yes of course, without PK I now realize that slave must scan all rows to apply changes. I thought it had some internal id or whatever to identify each row but it is not... Well, another good reason to always create a PK!

              Show
              nico Nicolas Payart added a comment - Thank you Elena. Yes of course, without PK I now realize that slave must scan all rows to apply changes. I thought it had some internal id or whatever to identify each row but it is not... Well, another good reason to always create a PK!
              Hide
              elenst Elena Stepanova added a comment -

              Closing for now as "won't fix". Hopefully in future versions there will be improvements in the area.

              Show
              elenst Elena Stepanova added a comment - Closing for now as "won't fix". Hopefully in future versions there will be improvements in the area.

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  nico Nicolas Payart
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: