uaa
uaa copied to clipboard
cloudfoundry
Update of last_logon_success_time consuming DB resources
What version of UAA are you running?
v75.21.0
How are you deploying the UAA?
The UAA (uaa-release) is deployed via bosh as part of the complete cf landscape installation. We are running the UAA against a Postgresql Database.
What did you do?
We saw that some users are requesting tokens for the same user in parallel in multiple threads. This is probably done in some automated job (not by us, but someone using our UAA).
What did you expect to see? What goal are you trying to achieve with the UAA?
We were expecting that the UAA would be able to handle the parallel requests without any problems.
What did you see instead?
We saw that the UAA is having problems to update the last_logon_success_time for the parallel requests. While the UAA logs show no problems here as eventually the update is done, we see in the DB insights, that some of the update commands to update the timestamp take several seconds. Most of this time is used to wait for other db processes that are accessing the same row at the same time. If the requests are kept at a constant rate this will lead to more and more DB resources consumed by this usually trivial update command. We actually got alerts that the DB was having problems to accept new connections because of such a situation lately.
In our own UAA-based service we saw this problem already some time ago and resolved this by changing the update command for the logon time to a version where we would try to get a lock for the row without waiting during the update. With this new statement the problem disappeared. Therefore we want to contribute this updated statement to the UAA so we won't see this in the future in the UAA.
As we only saw the problem in our postgresql based deployment and don't know about the behavior of the other DBMS in such a situation, the PR will only target Postgresql. The Syntax used in the new statement is Postgresql specific anyways, we need to differentiate between different DBMS anyways. If similar problems would appear with other vendors, it would be easy to implement similar mechanism for those, using their specific SQL dialect.
We have created an issue in Pivotal Tracker to manage this:
https://www.pivotaltracker.com/story/show/182889238
The labels on this github issue will be updated when the story is started.
I see you mention a PR and in this fix and here you mention only Postgresql So do you known that SKIP Locked is support from MYSQL too ? https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/
To me such an extension to have skip locked is fine, but please improve here UAA in general not only the postgresql case
