[Maria-discuss] Monitoring InnoDB tables?
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
Triggers? Monitor the binlog, as you state, might be a goer. You could enable the performance schema and monitor queries there? If you cannot change the source database at all how about additional an additional slave that you can change? I think I'd go for this option. -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Erik Cederstrand Sent: Wednesday, April 13, 2016 9:25 AM To: maria-discuss@lists.launchpad.net Subject: [Maria-discuss] Monitoring InnoDB tables? 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
Hello Rhys, Thanks for your suggestions!
Den 13. apr. 2016 kl. 09.48 skrev Rhys.Campbell@swisscom.com:
Triggers?
You mean create a custom logging table in the customer database? I guess I could do something like this on each table: CREATE TRIGGER insert_event AFTER INSERT ON t1 FOR EACH ROW INSERT INTO event_log SET table_name = 't1', count=1 ON DUPLICATE KEY UPDATE count = count + 1;
Monitor the binlog, as you state, might be a goer.
Yes, but I'd like to know which schema and table changed, not just that *something* changed somewhere on the server. Otherwise I have too much work to do.
You could enable the performance schema and monitor queries there?
I tried to enable the performance_schema. Any pointers to where I should look? Poking around, my best bet right now would be: SELECT object_schema, object_name, sum_timer_write_allow_write FROM table_lock_waits_summary_by_table WHERE object_schema='test' AND object_name='t1';
If you cannot change the source database at all how about an additional slave that you can change? I think I'd go for this option.
I could try that. Erik
Hello Erik,
I'm not sure if that would work for you, but you could create a log of
specific modifications on a table using MaxScale and the QLA Filter using
regexes.
https://github.com/mariadb-corporation/MaxScale/blob/develop/Documentation/F...
Then just monitor the log file for changes.
Regards,
On Wed, Apr 13, 2016 at 11:27 AM Erik Cederstrand
Hello Rhys,
Thanks for your suggestions!
Den 13. apr. 2016 kl. 09.48 skrev Rhys.Campbell@swisscom.com:
Triggers?
You mean create a custom logging table in the customer database? I guess I could do something like this on each table:
CREATE TRIGGER insert_event AFTER INSERT ON t1 FOR EACH ROW INSERT INTO event_log SET table_name = 't1', count=1 ON DUPLICATE KEY UPDATE count = count + 1;
Monitor the binlog, as you state, might be a goer.
Yes, but I'd like to know which schema and table changed, not just that *something* changed somewhere on the server. Otherwise I have too much work to do.
You could enable the performance schema and monitor queries there?
I tried to enable the performance_schema. Any pointers to where I should look? Poking around, my best bet right now would be:
SELECT object_schema, object_name, sum_timer_write_allow_write FROM table_lock_waits_summary_by_table WHERE object_schema='test' AND object_name='t1';
If you cannot change the source database at all how about an additional slave that you can change? I think I'd go for this option.
I could try that.
Erik _______________________________________________ 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
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation
Triggers?
Yes. I ran a system like this using triggers for all insert/update/delete records replicating data between MySQL and MSSQL. A PHP script was used to copy the changes between systems. Sounds a little nuts but it worked very well for a smallish (< 10GB) dataset.
Performance_schema -> Just a thought really. I wouldn't do this. Not sure it's viable. But I was thinking of the events_statements_summary* tables.
-----Original Message-----
From: Erik Cederstrand [mailto:erik@cederstrand.dk]
Sent: Wednesday, April 13, 2016 11:27 AM
To: Campbell Rhys, PMK-ACS-QPM
Den 13. apr. 2016 kl. 09.48 skrev Rhys.Campbell@swisscom.com:
Triggers?
You mean create a custom logging table in the customer database? I guess I could do something like this on each table: CREATE TRIGGER insert_event AFTER INSERT ON t1 FOR EACH ROW INSERT INTO event_log SET table_name = 't1', count=1 ON DUPLICATE KEY UPDATE count = count + 1;
Monitor the binlog, as you state, might be a goer.
Yes, but I'd like to know which schema and table changed, not just that *something* changed somewhere on the server. Otherwise I have too much work to do.
You could enable the performance schema and monitor queries there?
I tried to enable the performance_schema. Any pointers to where I should look? Poking around, my best bet right now would be: SELECT object_schema, object_name, sum_timer_write_allow_write FROM table_lock_waits_summary_by_table WHERE object_schema='test' AND object_name='t1';
If you cannot change the source database at all how about an additional slave that you can change? I think I'd go for this option.
I could try that. Erik
participants (3)
-
Erik Cederstrand -
Guillaume Lefranc -
Rhys.Campbell＠swisscom.com