Clear out temp space?
David hit "out of temp space" on 12.10.FC3/Solaris, seeing dozens of system-generated SORT objects that Server Studio reported as empty yet still holding ~5GB. John explained these are sort files, not tables, so partition statistics in sysmaster show no rows/space used; the space isn't freed until the owning query/cursor is closed, and the session or thread id is often embedded in the sort file name. Temp dbspaces are recreated at every server restart, which is why bouncing the instance cleared it. Andreas noted 12.10.xC8 adds sysmaster:sysptnhdr.sid to link a temp partition to its session. No fix beyond identifying and ending the offending session or restarting.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 12.10FC3 Solaris 10 1/13 I had a user complaint, this morning, about running out of temp space when executing a query. I checked and noticed a few dozen system generated SORT tables, etc. The interesting thing was that Server Studio showed all of them as empty, yet they still consumed space. That is, about 5 GB of space had been allocated, but the actual space occupied was listed as zero, in every one of those bad boys. I wonder if there is some command that will remove the detritus of unused, system-generated temp tables. This was a test/development database server, so I could bounce it, and, of course, that immediately eliminated the problem. The user could then run his query. No tables remain in the temp space after the query completed. (All cleaned up, as I would expect.) Regards, DG
=0ADavid:=0A=0AMy guess is you have active cursor against a query which doe= s an order=0Aby of a large amount of data. Since sort files are NOT tables= they will=0Anot show you much space is used or how many rows. The sort f= ile will=0Anot be released until the query is closed. I am guessing server = studio=0Ais looking at sysmaster partition statistics which will not reflec= t how=0Amuch is used for sort files. In many case the sort file name will= have=0Athe session or thread id embedded in side the name of the file.=0A= =0AThe temp spaces are re-created each time the server boots. This was=0Aa= dded in version 7.3 to make sure that the server cleaned up after=0Aitself.= The reason why the temp spaces are recreated, is that it is=0Afaster to r= e-create the temp space(s) than walk the temp space looking=0Afor space whi= ch need to be cleaned up.=0A=0A=0AJohn=0A=0A-------- Original Message -----= ---=0ASubject: Clear out temp space? [40188]=0AFrom: "DAVID GROVE" <david.g= rove@alaska.gov>=0ADate: Mon, November 13, 2017 2:35 pm=0ATo: ids@iiug.org= =0A=0AInformix 12.10FC3 =0ASolaris 10 1/13 =0A=0AI had a user complaint, th= is morning, about running out of temp space=0Awhen =0Aexecuting a query. = =0A=0AI checked and noticed a few dozen system generated SORT tables, etc. = The=0A=0Ainteresting thing was that Server Studio showed all of them as emp= ty,=0Ayet they =0Astill consumed space. That is, about 5 GB of space had be= en allocated,=0Abut the =0Aactual space occupied was listed as zero, in eve= ry one of those bad=0Aboys. =0A=0AI wonder if there is some command that wi= ll remove the detritus of=0Aunused, =0Asystem-generated temp tables. =0A=0A= This was a test/development database server, so I could bounce it, and,=0Ao= f =0Acourse, that immediately eliminated the problem. The user could then r= un=0Ahis =0Aquery. No tables remain in the temp space after the query compl= eted.=0A(All =0Acleaned up, as I would expect.) =0A=0ARegards, =0A=0ADG =0A= =0A=0A*********************************************************************= **********=0A=0A Forum Note: Use "Reply" to post a response in the discussi= on forum.
12.10.xC8 onwards will provide new sysmaster:sysptnhdr.sid field which would allow you to see which session a temp partition is associated with. From: "DAVID GROVE" <david.grove@alaska.gov> To: ids@iiug.org Date: 11/13/2017 11:35 PM Subject: Clear out temp space? [40188] Sent by: ids-bounces@iiug.org Informix 12.10FC3 Solaris 10 1/13 I had a user complaint, this morning, about running out of temp space when executing a query. I checked and noticed a few dozen system generated SORT tables, etc. The interesting thing was that Server Studio showed all of them as empty, yet they still consumed space. That is, about 5 GB of space had been allocated, but the actual space occupied was listed as zero, in every one of those bad boys. I wonder if there is some command that will remove the detritus of unused, system-generated temp tables. This was a test/development database server, so I could bounce it, and, of course, that immediately eliminated the problem. The user could then run his query. No tables remain in the temp space after the query completed. (All cleaned up, as I would expect.) Regards, DG ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you, John. So, they were likely files, not tables, and I could have observed them with an 'ls' command? Then, I might have been able to identify the session by inspecting the contents of the file. Then, kill the session (assuming I could identify it)? DG