Sorry if I didn't explain it correctly.
It is not the calculation itself. Because the calculation based on fields
of the table the DB is not able to use the indexes for the search.
Please see a simplified test below.
1) A query with the DATE_ADD function d hit doesn't the index of
TIME_CREATED.
mysql> EXPLAIN DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE
TOKEN_STATE='ACTIVE' AND ("2018-05-21 06:48:15" > DATE_ADD(TIME_CREATED,
INTERVAL 10 DAY));
+----+-------------+-------------------------+------+-----------------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | *rows* | Extra |
+----+-------------+-------------------------+------+-----------------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | IDN_OAUTH2_ACCESS_TOKEN | ALL |
IDX_IDN_OAUTH2_ACCESS_TOKEN_STATE | NULL | NULL | NULL | *4276* | Using
where |
+----+-------------+-------------------------+------+-----------------------------------+------+---------+------+------+-------------+
2) A query without the function call function hits the index of
TIME_CREATED.
mysql> EXPLAIN DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE
TOKEN_STATE='ACTIVE' AND ("2018-05-21 06:48:15" > TIME_CREATED);
+----+-------------+-------------------------+-------+------------------------------------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | *rows* | Extra |
+----+-------------+-------------------------+-------+------------------------------------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | IDN_OAUTH2_ACCESS_TOKEN | range |
IDX_TC,IDX_IDN_OAUTH2_ACCESS_TOKEN_STATE | IDX_TC | 4 | const | *1*
| Using where |
+----+-------------+-------------------------+-------+------------------------------------------+--------+---------+-------+------+-------------+
So this clearly has an impact on the queries when the token table grows.
Doing the calculation from the application (or even from SQL insert) during
the token generation adds only a fraction of overhead compared to the index
miss.
Thank you
On Mon, May 21, 2018 at 8:32 PM Sagara Gunathunga <sag...@wso2.com> wrote:
>
>
> On Mon, May 21, 2018 at 8:10 PM, Tharindu Edirisinghe <tharin...@wso2.com>
> wrote:
>
>> Currently in the IDN_OAUTH2_ACCESS_TOKEN table, we store the time of
>> token generation in TIME_CREATED column in human readable date time format.
>> Therefore at the time of token generation, we'll have to get the current
>> timestamp and store it in another column and also add the VALIDITY_PERIOD
>> to that and store in another column, which would be the expiry timestamp.
>>
>> If we are doing above, the performance of the responses sent by the Token
>> Introspection endpoint can also be improved. It sends the iat and exp
>> values doing the calculation.
>>
>> {"scope":"openid","active":true,"token_type":"Bearer","exp":1526916282,"iat":1526912682
>> .......
>>
>> This way there won't be a calculation in the Token Introspection
>> responses, where it can directly send the values stored in the data table.
>>
>>
>> On Mon, May 21, 2018 at 7:46 PM Rushmin Fernando <rush...@wso2.com>
>> wrote:
>>
>>> Hi Devs,
>>>
>>> Right now it is the validity period which is getting stored along with
>>> the tokens.
>>>
>>> So in order to expire a token, a calculation is needed during expiry
>>> tasks.
>>>
>>> When there a lot of tokens, this becomes a bottleneck since the database
>>> indexes are not applicable.
>>>
>>
> Do you have evidence about the latency introduced by this calculation ?
>
> According to my understanding ...
>
> - Token generation is a runtime task.
> - Token expire task is a non-runtime task and we perform above calculation
> while running this task.
> - What you suggest here is, move the calculation into token generation
> time, which will increase the runtime overhead.
>
> If my understanding on above is correct I'm not much keen with the
> suggested approach and IMO we are not solve any problems by moving
> non-runtime calculation into the runtime, it's just move the problem into a
> much critical layer.
>
> Thanks !
>
>>
>>> But if the expiry timestamps are stored in the database, during the
>>> expiry tasks calculations are not needed and therefore the indexes are
>>> applicable. This should significantly improve the task execution time.
>>>
>>> --
>>> *Best Regards*
>>>
>>> *Rushmin Fernando*
>>> *Technical Lead*
>>>
>>> WSO2 Inc. <http://wso2.com/> - Lean . Enterprise . Middleware
>>>
>>> mobile : +94775615183
>>>
>>>
>>>
>>
>> --
>>
>> Tharindu Edirisinghe
>> Associate Technical Lead | WSO2 Inc
>> Platform Security Team
>> Blog : http://tharindue.blogspot.com
>> mobile : +94 775181586
>>
>
>
>
> --
> Sagara Gunathunga
>
> Director; WSO2, Inc.; http://wso2.com
> Linkedin; http://www.linkedin.com/in/ssagara
> Blog ; http://ssagara.blogspot.com
> Mobile : +9471 <+94%2071%20565%209887>2149951
>
>
--
*Best Regards*
*Rushmin Fernando*
*Technical Lead*
WSO2 Inc. <http://wso2.com/> - Lean . Enterprise . Middleware
mobile : +94775615183
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
