After doing some more digging into our architecture my understanding is it’s a single db connection for all listeners, per process. So our app has a status page for each of the commercial sites our users are at. When a user has the status page is open we start up a GenServer to as a cache for every time that page is open. No reason to run these expensive calculations again to serve the same status info to multiple connections. The cache starts a listener and then sets up multiple listens for all the pg_notifys whose updates could could affect the caches. In short: each site sets up a Postgrex listener and updates the cache and does regular Phoenix PubSub out to the LiveViews displaying the data. We have this pattern in a few more places and the result seems to be that everything works fine until we hit some threshold of user activity where our connection pool gets emptied out (probably exacerbated by some poorly performing queries). Then our web users start refreshing the page and we end up doing a DoS attack on ourselves.
So it looks like both @al2o3cr and @benwilson512 are correct that it’s not the number of notifications we are listening to that’s causing our problems, but maybe we are setting up too many listeners.
Here’s our setup:
# MyApp.Repo
def start_notification_listener() do
Postgrex.Notifications.start_link(__MODULE__.config())
end
def listen(listener, channel_name) do
Postgrex.Notifications.listen(listener, channel_name)
end
# In our caches
def setup_listeners(site_id) do
{:ok, listener} = MyApp.Repo.start_notification_listener()
{:ok, _ref} = MyApp.Repo.listen(listener, "site_users:#{site_id}")
{:ok, _ref} = MyApp.Repo.listen(listener, "site_equipment:#{site_id}")
end
def handle_info({:notification, _pid, _ref, "site_users:" <> _, payload}, state) do
...
end
I guess I need to replace the listeners in each cache with one single listener for that everyone in MyApp.Repo can reuse?
