Hi Asela, I believe and have seen in my instances, people prefer backing up the expired token dump and then they prefer the stored-procedure option.
Given that the users can define their own desired mechanism to deal with the data, we should be focusing more on shipping the stored-procedure scripts. Hence, +1 for stored procedure option. Regards, Firzhan email: [email protected] mobile: (+94) 77 9785674 <%28%2B94%29%2071%205247551>*| blog: http://firzhanblogger.blogspot.com/ <http://firzhanblogger.blogspot.com/> <http://suhothayan.blogspot.com/>* *twitter: https://twitter.com/firzhan007 <https://twitter.com/firzhan007> | linked-in: **https://www.linkedin.com/in/firzhan <https://www.linkedin.com/in/firzhan>* On Thu, Nov 16, 2017 at 5:25 AM, roshan wijesena <[email protected]> wrote: > +1 for stored procedure option, it is a DBA's task to run scripts, hence > less overhead for the product. > > On Thu, Nov 16, 2017 at 12:18 AM, Asela Pathberiya <[email protected]> wrote: > >> Hi IAM/APIM team, >> >> We have already seen that large number of table entries in OAuth2 access >> token table , OAuth2 authorization code table & IDN session data tables >> are causing issues in production system. >> >> Sometime these tables contain around 10m entries. Most of entries are >> expired or invalid. We do not have a proper way to clean up those >> expired/invalid entries. There is a clean task for IDN session data tables >> but it causes for deadlock when there are high load in the live system. >> >> Following are the few suggestions for these three tables. If we can >> figure out the proper way, we can implement it in same manner for above. >> >> This would probably help to keep a continue performance of the product >> system. >> >> 1. Having a periodical cleanup task inside our servers (This is >> already there for IDN session data) >> >> -> Run from single node >> -> Deadlock/Slowness can be generated with high load >> -> Deadlock recovery can be built inside the code (OAuth2 is already >> having it) >> >> 2. Having a con job based cleanup task. >> >> -> Same as above but can run pre-defined time (when only load is low or >> weekends) >> >> 3. Stored procedure (This is already there for OAuth2 Access token) >> >> -> Need for script for all the database type >> -> Need to run/maintain separately (manual task) >> >> 4. Splitting/partition entries in to multiple tables when it is stored. >> >> -> May be based on index of access token / session id >> -> May be based on status (token status) >> >> 5. Moving non-active tokens or inactive session data in to separate table >> (if there are required for auditing) >> >> >> Thanks, >> Asela. >> >> -- >> Thanks & Regards, >> Asela >> >> ATL >> Mobile : +94 777 625 933 <+94%2077%20762%205933> >> +358 449 228 979 >> >> http://soasecurity.org/ >> http://xacmlinfo.org/ >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > _______________________________________________ > Architecture mailing list > [email protected] > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > >
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
