innodb_force_primary_key=1 to mitigate downtimes (row based replication of big tables without index)

[GETandSELECT]

Hey

I created a table with 200'000 records and NO index.

record looks like this:

INSERT INTO test.test 
            (uuid, 
             clock, 
             testfield) 
VALUES      (UUID(), 
             NOW(), 
             SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 1, 36))

Then I deleted all rows in that table DELETE FROM test.test;, which resulted that the cluster was down for almost one hour. All write operation timed out. During row based replication MariaDB had to do a full table scan for every record.

> show processlist;
+-----+-----------------------+-----------------+------+---------+------+-----------------------------------------+---------------------------+----------+
| Id  | User                  | Host            | db   | Command | Time | State                                   | Info                      | Progress |
+-----+-----------------------+-----------------+------+---------+------+-----------------------------------------+---------------------------+----------+
|   1 | system user           |                 | NULL | Sleep   | 5423 | wsrep aborter idle                      | NULL                      |    0.000 |
|   2 | system user           |                 | NULL | Sleep   |    0 | Delete_rows_log_event::find_row(637163) | NULL                      | 

more Info How the Lack of a Primary Key May Effectively Stop the Slave -> they write about Master/Slave, we could reproduce the same with Galera

What do you think about this (innodb_force_primary_key) possible mitigation for this issue? We are DBaaS provider.

From XtraDB/InnoDB Server System Variables

Description: If set to 1 (0 is default) CREATE TABLEs without a primary or unique key where all keyparts are NOT NULL will not be accepted, and will return an error.
Commandline: --innodb-force-primary-key
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.1.0 

thanks for feedback

[cf-gitbot]

We have created an issue in Pivotal Tracker to manage this:

https://www.pivotaltracker.com/story/show/154715999

The labels on this github issue will be updated when the story is started.

[ldangeard-orange]

hello #GETandSELECT 2 remarks:

• If you omit a primary key when you create a InnoDB table, MySQL create a invisible primary key (autoincrement)
• When you DELETE FROM test.test; mysql don't use the primary-key or any index. Index is used only with a WHERE clause

Galera don't like DELETE or UPDATE whith 200.000 records.

[ldangeard-orange]

The Physical Row Structure of an InnoDB Table : https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html If no primary key was defined for a table, each clustered index record also contains a 6-byte row ID field.

[GETandSELECT]

thanks @ldangeard-orange for input

from MariaDB KB:

In XtraDB/InnoDB tables, all indexes contain the primary key as a suffix. Thus, when using this storage engine, keeping the primary key as small as possible is particularly important. If a primary key does not exist and there are no UNIQUE indexes, InnoDB creates a 6-bytes clustered index which is invisible to the user.

I found no details about this 6-bytes clustered index. Very bad documented. It's seems to be different than Oracle Row-ID.

MySQL != MariaDB, special most recent versions.

I tested the same table with 200 000 records:

• No index and no primary key -> delete causes outage
• Index -> delete causes no outage
• Primary key -> delete causes no outage

The outage is generated by replication, where FULL TABLE scan for every record is done without primary key (or index). In this example he scans 200 000 x 200 000 records without index/primary key.

DELETE FROM test.test; uses index if defined (in replication)

[ldangeard-orange]

MySQL (Oracle distribution) use InnoDB engine, Percona and MariaDB 10.1.x use Xtradb Engine and there's some differences. I will test with and whitout pk en index.

[GETandSELECT]

@ldangeard-orange I am curious: do you use Oracle distribution with this bosh-release?

[ldangeard-orange]

No, MariaDB . But I'm DBA MySQL/Oracle and I bench MySQL 5.7 vs MariaDB 10.1

[ldangeard-orange]

Table with 500 000 records :

• with index ans pk : 43s
• with pk : 33s
• withou pk : 36s

Explain plan is the same :

explain delete from orders_pk_idx;
+------+-------------+---------------+------+---------------+------+---------+------+---------+-------+
| id   | select_type | table         | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+------+-------------+---------------+------+---------------+------+---------+------+---------+-------+
|    1 | SIMPLE      | orders_pk_idx | ALL  | NULL          | NULL | NULL    | NULL | 5869096 |       |
+------+-------------+---------------+------+---------------+------+---------+------+---------+-------+```

[ldangeard-orange]

Hello @GETandSELECT , after my test with my table without primary key, I upgrade cf-mysql-release 36.11 (with MariaDB 10.1.30). On the first node where I execute the DELETE, no problem, but on the 2 other nodes, MariaDB can't excute the shutdown. CPU at 100%. After my diagnosis, the table without pk was not empty.

If MySQL create a invisible primary key , GALERA don't use it to replicate DELETE. So ... it's a good idea @GETandSELECT to use innodb_force_primary_key=1