How to find drift in schema in PXC MySql 5.7 that has RSU enabled for schema changes

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

We are using PXC MySQL 5.7 multi-master cluster with several nodes part of it. We have several such clusters spread across different regions. Also one region is primary and have Master/donor node which replicates write-sets to all other secondary regions which has one node configured as slave to receive replication data (Master-Slave). We are using RSU to make changes in schema. So if a DDL is run on one node it has to be run across all the nodes before DML can be run to avoid cluster going into inconsistent state.

Question:
How we can know drift in schema and how we can set alerts for any drift/difference in schema across all the nodes before we run DML to avoid any kind of disruption since we are using RSU and same table/schema structure is expected across all the nodes?

1 Like
2

I appreciate any inputs in my above questions. Thanks

1 Like
3

Why not use pt-online-schema-change to execute the DDL? this way all nodes will have the same schema and you don’t have to worry about differences.

1 Like
4

Thanks. The documentation of this tools says pt-online-schema-change — Percona Toolkit Documentation> but there are two limitations: only InnoDB tables can be altered, and wsrep_OSU_method must be set to TOI (total order isolation). We are using Percona XtraDB Cluster and are using wsrep_OSU_method set to RSU so won’t be able to use this tool in our environment. Any other ideas? Thanks

1 Like
5

You should only be using Innodb only anyway in PXC, and for the wsrep_OSU_method you can switch that to TOI easily as the parameter is dynamic.

6

Thanks for suggestion on TOI. We want to keep using RSU as in our production when we alter large table all writes are block for a time period with TOI option. The timing of block depends on how large a table is . So without changing to TOI how we can achieve the solution? One solution that came to my mind is dumping the schema across all the DB nodes and comparing the hash value etc? But how we can dump schema without causing much load on the DB servers in production? Any other alternatives?

1 Like
7

Using TOI + pt-online-schema-change is not going to block even for large tables, that is the point of using pt-osc. I suggest you take some time to experiment with the tool if you haven’t used it before. That is really the easiest option.

1 Like
8

I’m going to 2nd what igroene said; You should use pt-osc with TOI. You can change to TOI at a per-session basis so this will not impact your other operations. The “point” of TOI is to keep changes in sync across all nodes.

How we can know drift in schema and how we can set alerts for any drift/difference in schema across all the nodes

You can’t; Not without writing some custom tool to check your schemas. There is nothing in the Galera protocol that will check schema consistency. This is the #1 negative about using RSU. There is no guarantee.

I also want to question your application design. Constant DDL’s are not normal and your application sounds like it was not designed correctly for a clustered architecture. I would re-evaluate why your application needs to run DDLs so often.

Next, look at PXC8 which includes support for “INSTANT ALTER” in some cases. You could possibly switch back to TOI and use the new INSTANT ALTER feature which would not require any locking.

1 Like
9

You can’t; Not without writing some custom tool to check your schemas.

That is the root of the original question. Yes you can, in some way you have to take data out and inject it into your monitoring solution. Off the top of my head, simply doing a show create table of everything in order put into a hash function would allow this. Perhaps there is the right kind of metadata in some table that this could turn into a single select statement put into a hash method SHA().

I also want to question your application design.

I’m not sure this thread gave any information on application design.

Constant DDL’s are not normal and your application sounds like it was not designed correctly for a clustered architecture.

I’ve re-read this thread a few times and I don’t see any references to the frequency. Can you expand more on the information you used to deduce the application design?

I would re-evaluate why your application needs to run DDLs so often.

It could be the DDL changes happen once every 5 years or every 5 minutes. How does that impact the answer to the question? I believe if you actually knew the frequency, you would not have felt it necessary to make this point.

But I agree, whatever scenario you must be envisioning sounds really bad. A system that is constantly changing DDL would be very strange. I honestly have a hard time imaging an application design that uses DDL changes as a central part of its functionality. If you happen to have references to clients doing this, I think it would be a fascinating read.

Fundamentally the idea is to identify differences when a mechanism is used outside of the normal procedure. Switching to TOI solves a very narrow scope of this issue, and adds exposure in all new ways. If a drop table command comes in, now it is gone, everywhere. An alter will most certainly lock everything up.

Let’s pretend we’re replicating is near the limit of applying to a cluster PXC. Using PTOSC on a 4TB table is going to inject enough records, soaking up whatever slack the remaining replication stream has in terms of capacity. I haven’t done the math, but it makes my head hurt to just think about needing to re-duplicate all the records like this. Does --max-load only references local variables, and thus things like replication impact require some more custom work?

I will admit though, that when we first used the tool under production loads the cluster crashed. If you look at the history, the surface area of possible bugs increases when using a tool like this.

Can we agree there are conditions which make it more impractical? Attempts to minimize any kind of risk to the system. Being able to make big changes to a node that has been isolated from the cluster, is in some ways risk mitigation.

Given a system that is using RSU, even if it is set as-needed. The update can be automated and properly managed, but we’d like to have a simplistic monitor that clues us that something might be different. Certainly we’re creating the link between the DB and the monitor. Any advice that would simplify table structure comparison?

1 Like
10

I thought of this originally, but due to AUTO_INCREMENT values being present in S_C_T this won’t work since there is no guarantee the A_I values are at the same when accessing each node. The best solution is querying I_S.COLUMNS for each table and, at minimum, checking count of number of columns. Once you have that, I would expand the tool into comparing column types along with precision. Then expand into I_S.INDEXES to make sure each table has same indexs.

Oooff. My fault. I had another similar question open in my tabs and my brain cross-fired.

Yes, we can agree here. A 4TB table does question the use of ptosc, more so in terms of total execution time.

While not an immediate solution, as I said above, PXC8 now has instant alter; if your DDLs are simply adding columns, this might help you tremendously.

1 Like
11

due to AUTO_INCREMENT values being present

Good point, sure way to get a different answer each and every time. It could be regex cleared out, but you have to run the query for each table.

best solution is querying I_S.COLUMNS for each table

SELECT
  SHA2(GROUP_CONCAT(COLUMNS.TABLE_NAME, 
                    COLUMNS.COLUMN_NAME, 
                    COLUMNS.ORDINAL_POSITION, 
                    COLUMNS.IS_NULLABLE, 
                    COLUMNS.COLUMN_TYPE, 
                    COLUMNS.NUMERIC_PRECISION), 256)
FROM TABLES,
     COLUMNS
WHERE TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA
	AND TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
	AND TABLES.TABLE_TYPE = 'BASE TABLE'
ORDER BY COLUMNS.TABLE_NAME, COLUMNS.ORDINAL_POSITION;

Ignoring indexes (INNODB_SYS_INDEXES?), I presume this is in the right direction.

In the context of a monitoring system using numbers, CRC32 seems to produce a directly usable value.

PXC8 now has instant alter; if your DDLs are simply adding columns, this might help you tremendously.

The online DDL from 5.6 was very much a welcomed improvement. This addition here in 8 is something I certainly look forward to! But the incompatibility of earlier nodes in a cluster means we’d be creating an all new cluster with binlog repl and do a pause and failover to the new cluster with one brush stroke.

1 Like
12

Something like that yes, but GROUP_CONCAT() will run into issues. I ran your SQL on my test server with about 50 tables total and got warnings about data truncation.

1 Like