12

I'm creating a dashboard which should monitor a database table. I have only database access (no application layer). The table is rather large (10 million rows), however not changing rapidly (100 inserts/updates per minute)

How can I find out whether the table changed? I would try to hit the database every second, but this seems like a brute-force approach...

Databases: MySQL/Postgres

  • This might be helpful. We are using this to monitor our server farm. I'm pretty sure this will have the feature to track a table change in DB. Unfortunately we haven't configured up to table level. So, I'm not aware of configuring up to table level. – Jude Niroshan May 7 '15 at 5:20
  • Thanks for the comment. But how can nagios help? I have only database access. I can't install anything on remote machines. – Kiril May 7 '15 at 5:24
  • 3
    Do you really want to be notified -- in real time -- every time a row is inserted or updated? Think again. – Tulains Córdova Aug 14 '15 at 15:02
  • Is there any valid reason why you don't have an application layer? It would seem to me the best way to do things, i.e. to have an application layer that handles the monitoring. E.g. sending e-mail from database server doesn't look like a clean architecture. – juhist Jul 11 '17 at 10:03
  • I have a small mysql plugin that does this: github.com/Cyclonecode/mysql-notification – Cyclonecode Oct 2 at 5:29
9

You can use triggers.

CREATE TRIGGER notifyMe
ON table1
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DB AutoMailer',
        @recipients = 'user@example.com',
        @body = 'The DB has changed',
        @subject = 'DB Change';
GO
  • I'm not sure this would be better, than hitting the database every second, or would you send the whole change in the mail, and let the dashboard software parse the email? – Kiril May 7 '15 at 6:52
  • 1
    You can get the specific changed values by selecting from the 'inserted', 'updated', and 'deleted' tables, and you can add the ids to the body. – stechray May 8 '15 at 0:40
  • 2
    Instead of sending email, you can also call a stored procedure that connects to a web api that the dashboard supports. See here for details: forums.asp.net/t/… – stechray May 8 '15 at 0:43
  • 3
    Or have the triggers put data in an additional table that you then query (and empty) at intervals you desire. Not technically 'real time', but can be made to look like it. – Jan Doggen Aug 14 '15 at 11:30
  • 1
    Have the trigger push the update info onto a queue (or topic) on a messaging broker. This has always been my favorite approach. This way you don't have any complex logic in the trigger, and you have a lot of flexibility with what you do with the message when you receive it. And if you push to a pub/sub topic, you can even have multiple consumers receiving it and doing different things with it. – mindcrime Aug 15 '15 at 2:50
3

For PostgreSQL I know a way to get notification from the database when a row changes.

  1. use trigger when insert / update / delete occurs.
  2. when event occurs send a notify to a client socket.
  3. be sure you app have a client to the server.
  4. then your app will receive a notification.

You can see my code or PostgreSQL's documentation.

It seems the notification is not a reliable notification, but at least it works for me.

  • Nice approach. This is basically a custom trigger? – Kiril Jun 20 '17 at 8:32

protected by gnat Oct 2 at 5:45

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.