murny
commented
6 years ago Thanks Neil for the detective work here! Agree that this is a big problem.
Regarding sessions, we shouldn't be storing this in the database. The default for Rails for the past 5+ years has been storing the user session in a cookie, like most web frameworks in the world. You actually have to go out of your way to get the sessions to be stored in the database as this is no longer supported in Rails and has been ripped out into a separate gem: https://github.com/rails/activerecord-session_store and has been added to discovery here: https://github.com/ualbertalib/discovery/blob/master/Gemfile#L4
This was ripped out of Rails for the reasons you have experienced first hand. That the major issue with storing sessions in the database is it's not scalable. It puts an unnecessary load on your database. Once your application receives a large amount of traffic, the sessions database table is continuously bombarded with read/write operations.
(NOTE: Jupiter/NEOSdiscovery are done properly and don't store sessions in the database)
Regarding the users and searches tables, these are both Blacklight problems.
Blacklight will create a new user in the database for every new session/visit to the site via this gem from samvera/blacklight community: https://github.com/cbeer/devise-guests (this is used mainly for the "bookmarks" feature of blacklight)
Blacklight will also save every single search a user makes and make them viewable here: https://library.ualberta.ca/search_history (and funny enough the "clear search history" button doesn't actually delete the entries from the database, just clears the users session) or https://library.ualberta.ca/saved_searches (for logged in users which isn't really supported in discovery)
Both of these "features" and how they been implemented from Blacklight are pretty insane and probably no one actually uses these in discovery. Which is another great example of how over bloated Blacklight is and how little of it we actually use.
Regarding quick fixes, we can do something just like our custom rake task and cronjob for clearing out session data. As not surprisingly, these rake tasks already exist in the code base:
rake blacklight:delete_old_searches[days_old] # Removes entries in the searches table that are older than the number of days given
rake devise_guests:delete_old_guest_users[days_old] # Removes entries in the users table for guest users that are older than the number of days given
seanluyk
nmacgreg
weiweishi
Description In Production, the database grows without bound. See the graph of the database growth, since we built a new DB server, back in June, 2018
Expected behavior This application must not allow its database to grow without bound. That way lies madness.
Additional context This application already includes a rake task ("sessions:cleanup"), implemented as a crontab, via ansible, for keeping the sessions table clean. But there are more tables that need to be cleaned up: