+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
