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
signature.asc
Description: Message signed with OpenPGP using GPGMail
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
