maria-discuss team mailing list archive

[Erik Cederstrand <erik@xxxxxxxxxxxxxx>]

Hi list,

I'm developing a fast data synchronization tool and need to monitor some tables in a customer's MariaDB (10.1) server for changes. I only have access to the server as a MariaDB client (no filesystem access). Ideally, I would keep a connection open and poll every few seconds - AFAIK, it's not possible for e.g. a stored procedure to notify a listener external to MariaDB.

The tables have no 'timestamp' or other columns I can do MAX() on to check for updates. The database is used as a backend for some proprietary software, so I can't just ask the customer to add an autoincrement column. With MyISAM, I could look at UPDATE_TIME in information_schema.tables (and live with the full-second resolution) or ask the customer to "alter table t1 checksum=1;" so I can use "checksum table t1 quick;". None of these work with InnoDB, and "checksum table t1;" is too slow for large tables.

I've seen suggestions to use NUM_ROWS and MODIFIED_COUNTER in information_schema.innodb_sys_tablestats, but it's unreliable for me in a number of cases, e.g. if the number of rows is the same but contents are different. Here's an example:


MariaDB [test]> create table t1 (i int primary key, j int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into t1 values (1, 2);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|    15000 | test/t1 | Initialized       |        1 |                1 |                0 |                1 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

MariaDB [test]> delete from t1;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into t1 values (3, 4);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1';
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME    | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|    15000 | test/t1 | Initialized       |        1 |                1 |                0 |                1 |       0 |         1 |
+----------+---------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)


How can I monitor an InnoDB table for changes efficiently? I could *possibly* convince the customer to monitor the binlog or full query log, but then I don't even know which database was changed. The server has hundreds of databases.


Erik

Attachment: signature.asc
Description: Message signed with OpenPGP using GPGMail