19

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

6
  • 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. Commented May 7, 2015 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
    Commented May 7, 2015 at 5:24
  • 3
    Do you really want to be notified -- in real time -- every time a row is inserted or updated? Think again. Commented Aug 14, 2015 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
    Commented Jul 11, 2017 at 10:03
  • 1
    I have a small mysql plugin that does this: github.com/Cyclonecode/mysql-notification Commented Oct 2, 2019 at 5:29

2 Answers 2

10

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 = '[email protected]',
        @body = 'The DB has changed',
        @subject = 'DB Change';
GO
6
  • 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
    Commented May 8, 2015 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
    Commented May 8, 2015 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
    Commented Aug 14, 2015 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
    Commented Aug 15, 2015 at 2:50
  • 1
    This is for Microsoft SQL Server, not for MySQL / Postgress, isn't it? Commented Sep 14, 2018 at 6:53
5

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.

1
  • Nice approach. This is basically a custom trigger?
    – Kiril
    Commented Jun 20, 2017 at 8:32

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