Cleaning up [User.Session].[Session] and [User.Session].[History.ResolveURL]

[sn...@bsd.uchicago.edu]

Hello --

High DB memory utilization and increasingly slower performance. Our DBA has noted we have two tables that have grown very large.

We're running ProfilesRNS v3.1.0. We have never cleaned up or maintained [User.Session].[Session] and [User.Session].[History.ResolveURL] tables, which have grown to a tremendous 300M rows between them. Our Triple table has 10,382,881 rows while Node has 15,932,698 for comparison.

I searched the news group and can find old suggestions that we can truncate these tables safely, which is probably what we'd like to do but I also found a reference to  https://github.com/ProfilesRNS/ProfilesRNS/blob/master/Database/Schema/User.Session.DeleteOldSessionRDF.StoredProcedure.sql which while apparently not addressing [History.ResolveURL] cleans up .[Session], also touches the Node and Triple tables which makes us think there's a dependency we do not understand. 

In any case, we seem to have records going all the way back to the app launch so it doesn't look like that stored proc is being executed.

Advice here?  

Thanks, Stephan

[Brown, Nicholas William]

Stephan

We have noticed over the last year or so that we are seeing more bot traffic being classed as human traffic by the profiles logic, Rows are created for human traffic, but not bot traffic, which is likely why you are seeing the tables grow faster than in the past. 

There is no problem with truncating the  [User.Session].[Session] and [User.Session].[History.ResolveURL] tables, we back these tables up and truncate them periodically, that way we can access the historical data if we every want it for troubleshooting or comparison with current data. In addition to these you may as well truncate the [Search.].[History.Query] table too.

If you want to delete all the session data from the node and triple tables you can use the following:

declare @t bigint, @s bigint

set @t = [RDF.].[fnURI2NodeID]('http://www.w3.org/1999/02/22-rdf-syntax-ns#type')

set @s = [RDF.].[fnURI2NodeID]('http://profiles.catalyst.harvard.edu/ontology/prns#Session')

create table #sessionDelete (tripleID bigint not null primary key, nodeid bigint not null)

CREATE UNIQUE INDEX sessionDelect_nodeID ON #sessionDelete(nodeID)

while exists( select top 1 1 from [RDF.].[Triple] where predicate = @t and object = @s) -- type and session

begin

      

      insert into #sessionDelete

      select top 300000 tripleID, subject from [RDF.].[Triple] where predicate = @t and object = @s

      delete from [RDF.].Node where nodeID in (select nodeID from #sessionDelete)

      delete from [RDF.].Triple where tripleID in (select tripleID from #sessionDelete)

      truncate table #sessionDelete

end

drop table #sessionDelete

This handles huge amounts of session data better than the stored procedure referenced below. You can add the stored procedure and add a step to your weekly or monthly jobs to run that stored procedure to prevent the data from building up in the future.

Nick

---

From: 'sn...@bsd.uchicago.edu' via ProfilesRNS <profi...@googlegroups.com>
Sent: Wednesday, July 9, 2025 3:51 PM
To: ProfilesRNS <profi...@googlegroups.com>
Subject: [ProfilesRNS] Cleaning up [User.Session].[Session] and [User.Session].[History.ResolveURL]

 

--
You received this message because you are subscribed to the Google Groups "ProfilesRNS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to profilesrns...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/profilesrns/9a2fadbd-3f2a-4630-9ca4-99e99a159ef8n%40googlegroups.com.

[Noe, Stephan [BSD]]

Nick,

 

Thank you I’ll run this by our DBA team so we can test this in one of our downstream environments.

 

Stephan Noe

BSDIS Technical Services Manager

The University of Chicago Medicine | Biological Sciences Division

Email: sn...@bsd.uchicago.edu

 

 

 

From: profi...@googlegroups.com <profi...@googlegroups.com> On Behalf Of Brown, Nicholas William
Sent: Wednesday, July 9, 2025 3:20 PM
To: ProfilesRNS <profi...@googlegroups.com>
Subject: [EXTERNAL] Re: [ProfilesRNS] Cleaning up [User.Session].[Session] and [User.Session].[History.ResolveURL]

 

Stephan We have noticed over the last year or so that we are seeing more bot traffic being classed as human traffic by the profiles logic, Rows are created for human traffic, but not bot traffic, which is likely why you are seeing the tables

ZjQcmQRYFpfptBannerStart

External: Use caution with links, attachments, and providing information.     Report Suspicious    ‌

ZjQcmQRYFpfptBannerEnd

---

This message was received from outside of the organization. Please pay special attention and practice care when clicking on any links or providing any information to the sender. Cyber attacks commonly attempt to trick you in to thinking the sender is a reputable individual who you can trust.

---

--
You received this message because you are subscribed to a topic in the Google Groups "ProfilesRNS" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/profilesrns/aE8VAtactpg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to profilesrns...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/profilesrns/MN2PR07MB6144C06D541D2F23DFF8DB7CA649A%40MN2PR07MB6144.namprd07.prod.outlook.com.

[Meeks, Eric]

We just truncated those tables a few days ago!

 

In the past few weeks our traffic as measured by the [User.Session].[Session] table has tripled!  The IP’s are all over the place and the user agents are not for bots, but clearly it must be bots. We might need to look at CloudFlare or some other commercial solution for being a traffic cop in front of Profiles. We are also trying to use nginx as a file based cache in front of Profiles.  If we find something that works, we will share our learnings.

 

Thanks to Moisey and Anirvan for much of this work.

 

Eric

 

From: 'Noe, Stephan [BSD]' via ProfilesRNS <profi...@googlegroups.com>
Sent: Wednesday, July 9, 2025 1:26 PM
To: profi...@googlegroups.com
Subject: RE: [ProfilesRNS] Cleaning up [User.Session].[Session] and [User.Session].[History.ResolveURL]

 

Nick, Thank you I’ll run this by our DBA team so we can test this in one of our downstream environments. Stephan Noe BSDIS Technical Services Manager The University of Chicago Medicine | Biological Sciences Division Email: snoe@ bsd. uchicago. edu

ZjQcmQRYFpfptBannerStart

This Message Is From an Untrusted Sender You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd

To view this discussion visit https://groups.google.com/d/msgid/profilesrns/CH0PR04MB7938D8A43BC75E4C71D0619CF149A%40CH0PR04MB7938.namprd04.prod.outlook.com.

[Brian Zimmel]

UMass Chan created a new stored procedure [dbo].[MonthlyCleanup] (run monthly) to eliminate Session and Search history, executing in the following order:
[User.Session].[History.ResolveURL]
[User.Session].[Session]
[Search.].[History.Phrase]
[Search.].[History.Query]

We have been seeing frequent heavy "bot" traffic across multiple websites where even CloudFlare detects the traffic to our university's site as Automated bot not a verified bot. We are planning to review the WAF on Azure Application Gateway for bot protection as we host Profiles in our Azure tenant.

Brian Zimmel
He/Him/His
Manager, Solutions Development
Information Technology