The value of delete chunk size depends on various factors such as:
The best way to calculate the chunk size it is to run a sample program that calculates the size. To do this, you may need to insert a large number of artificial records on the table and run a deletion query with incremental chunk sizes. Then you can plot the result in a table to see which is the optimal chunk size for a specific configuration.
Also, this chunk size configuration may change later if you decide to upgrade the DB cluster, their storage etc. Hence it is not a static value. The optimal value needs to be measured each time you do a change in your database infrastructure.
Note: Usually, the optimum chunk size is power of 2 and typically multiple of 512. This can be used for incremental values.
Note: To work with Single Sign-On you must have session persistence enabled.
Why we need to have a proper delete chunk size ?
In the world of World Wide Web, Sessions are the simplest way to store data for individual users against a unique session ID. These can be used to persist state information between page requests. When we consider all the requests ad responses that comes to a page per day, that is really a large amount. Due to this reason, the session data storing in the “IDN_AUTH_SESSION_STORE” table in the WSO2CARBON_DB of WSO2 IS is really high. This table fills up quickly if your system receive too much loads of requests.
After a certain period of time these session data is not really necessary. In order to stop the exponential table growth, we run a Session Clean Up Tasks in predefined time intervals via a script. If this storing session data is huge, the data that need to be deleted also will comes in bulk.
The session clean up task also takes a certain amount of time and consumes certain amount of resources. Therefore, we always need need to improve the performance of session clean up tasks. For this DeleteChunkSize property has introduced in WSO2 Identity Server. This will also help to reduce dead-locks happening during session clean up task. With the use of DeleteChunkSize value, the deletion happens batch wisely. “DeleteChunkSize” of data is deleted at one time.
But if you didn’t select a correct number for the DeleteChunkSize, you will end up with less performance. Therefore selecting suitable chunk size for you database is a must.
This chuck size may differ due to various factors of your environment
- The size of a row of the table. Calculate this by adding bytes needed for each column, and some control information per record.
- The underlying physical storage. e.g Normal SATA spinning Disk, SSD, SCSI, Amazon S3/EBS etc.
- The type of the database engine (e.g. MySql, MSSql, Oracle). Each one has different data organization mechanism in physical storage.
- OS where the DB server runs.
Therefore, it is hard to select a DeleteChunkSize without considering all the above factors.