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

gnat
  • 21,442
  • 29
  • 112
  • 288
Kiril
  • 529
  • 2
  • 4
  • 11
  • [This might be helpful](http://www.nagios.org/). 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 07 '15 at 05: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 07 '15 at 05: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
  • 1
    I have a small mysql plugin that does this: https://github.com/Cyclonecode/mysql-notification – Cyclonecode Oct 02 '19 at 05:29
  • I'd just question if there isn't any better solution if a realtime system is a requirement; MySQL might not be the best choice here. For instance, firebase has a realtime database for this exact purpose. – Jonny Nov 22 '21 at 06:48

2 Answers2

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 = 'user@example.com',
        @body = 'The DB has changed',
        @subject = 'DB Change';
GO
stechray
  • 120
  • 6
  • 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 07 '15 at 06: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 08 '15 at 00: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: http://forums.asp.net/t/1904724.aspx?Calling+a+Web+Service+from+T+SQL+Stored+Procedure+using+MSXML – stechray May 08 '15 at 00: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 02:50
  • This is for Microsoft SQL Server, not for MySQL / Postgress, isn't it? – Peter V. Mørch Sep 14 '18 at 06:53
4

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.

Glorfindel
  • 3,137
  • 6
  • 25
  • 33
user275831
  • 41
  • 1