How to force a session to end when close_session does not work.
I have sessions that are showing as still active and holding memory, via resource_acquisitions and sessions. However, there are no corresponding rows in transactions for these sessions. I have tried using interrupt_statement and close_session many times, but the sessions will not go away. The sessions are all coming in via JDBC connections. The problem is that as more of these sessions accumulate, I end up getting resource allocation errors. I've also tried a force shutdown on the database but that never completes either. So far, the only way I have found to get rid of them is to hard-kill the unix os vertica process itself - which is most definitely not a preferred way of dealing with things.
Is there any other method of force killing a session and getting the resources released?
Thanks,
-Sheila
Comments
I normally identify the pid of the sesison and kill it the OS.
When I log in to the Vertica server - RHEL - there are no processes for individual vertica connections. The only pid I could kill would be the database itself.
That is strange !
Hi
I just wanted to know whether when you perform close_session('session_id') does it shows socket errors in the vertica.log files like "socket shutdown error: Transport endpoint is not connected"?
If this is the case then in such scenerio I would suggest you to perform either of the below workarounds:
1. You can use execute the following SQL as superuser (dbadmin):
vsql => select close_all_sockets('waiting_sends');
If above option doesn't work then we have only option restart that initiator node.
2. You need reboot the initiator node of those zombie sessions. This will clear these sessions.
Let us know if it helps in anyways.
Regards
Rahul Choudhary
Yes, the socket message is written to the vertica.log when I try to close the session. The close_all_sockets did not work, so I had to restart things.
I'll make note of the close_all_sockets for future reference. Thanks much.
-Sheila
Can you tell us what options come with thie "new" close_all_sockets() function ?
Thx
Has there been a better solution found for this issue? Having clients restart initiator nodes is not very appealing to them. I think we need a "force" option on the CLOSE_SESSION function...
Hey is a better solution available now.
Is there a better solution now how to force terminate the session which is persistent even CLOSE_SESSION() command was executed?
@josefp - Which version of Vertica are you running?
Check out this post:
https://forum.vertica.com/discussion/240886/stop-a-stubborn-query-that-won-t-cancel