Hi All,
I’m working with a 3-node MySQL Percona XtraDB Cluster, and to track inserts and updates on a partitioned table, I’m currently using a helper table with a BIGINT counter and BEFORE INSERT/BEFORE UPDATE triggers to increment this counter and update a tracking column in the target table. My goal is to avoid using datetime(6) as a timestamp due to potential inconsistencies in this multi-node setup.
Is this the best approach for consistent tracking across nodes, or are there better methods?
Thanks for any guidance.
Hello @damienka
Check out the audit plugin. You can filter on just com_insert, and com_update. In the audit log, you’ll see the query itself, timestamp of when it occurred, the user executing the sql, and the connecting host. Toss this data into Clickhouse, Loki (integrates with PMM), or ElasticSearch for aggregate analytical analysis from all nodes.
Using the audit plugin removes the trigger overhead associated with each transaction.
What inconsistencies? If you run INSERT INTO table SET col1 = NOW() on node1, the evaluated result of that query will be replicated to node2 and node3. Meaning the exact timestamp will be present on all 3 nodes, down to the microsecond (if using that flag). PXC does not use statement-based replication, but rather transactional (eg write-set) replication, which directly avoids data inconsistencies.
This question makes me think you are writing to all 3 nodes in your PXC. This is not recommended. Best-practices with PXC is to write to a single node. Use the other nodes for reads. You should have something like HAProxy, or better, ProxySQL between your apps and the cluster to route connections to the current writer node.
Thanks, I will check out the audit plugin.
Regarding time inconsistencies, it’s the edge cases I’m worried about. e.g. The server time is adjusted back 1ms. Or failover to node2 with a sever time that’s slightly out.