Sybase IQ version space

[Former Member]

When a connection in SYBASE IQ starts using version space ?

[markmumy · Accepted solution]

When a user begins a data change transaction (load, insert, update, delete) version space will grow.  Also, any user that is running a query and/or has an open transaction, version space can possibly grow.

In essence, any time a connection has begun a transaction for any reason (read or write), version space will accumulate.  However, the amount of version space will depend on the amount that is being changed.  For instance, I would run a query that takes 1 hour.  I will increase version space for that 1 hour.  If no data changes take place, though, then there is no version space increase as nothing changed.

Additionally, IQ will free version space if no connected user can access that data.  We will do version cleanup along the way so as to minimize the amount of versioned space.  We are quite good at this as we have been doing this for 15+ years.

Mark

[Former Member · Accepted solution]

Mark,

Thanks for your reply . in our case , we noticed one long running thread (was on iq server for 5 hours and lastiqcmdtime too older than 5 hours ) and it was holding 600 gb of version space and this version space was keep on increasing ( ie 20 to 30 gb for every 30 mins )

FYI - its a select query and no other transaction was older than this . we tried to kill this connection but it failed .

How to deal with it .

Please let me know how to cleanup the version space manually .

[tayeb_hadjou · Accepted solution]

Hi,

Get result of queries;

select top 10  * from sp_iqtransaction() order by TxnCreateTime desc;

select top 5  * from sp_iqtransaction() where State='ACTIVE' order by TxnCreateTime desc;

Regards,

Tayeb

[markmumy · Accepted solution]

The "how to deal with it" is a business decision.  First, you need to decide if this is normal or not.  There are some customers that have queries, reports, batches that will run for hours and hours.  While this is perfectly normal, it has version ramifications that need to be handled.  If this is not normal for your site, then I would look to implement a job that watches connections and after a period of time that you configure, do something with that connection (kill it, contact the user, lock the login, etc).

Mark

[Former Member · Accepted solution]

Tayeb,

Thanks for your queries.

I noticed conncreatetime and lastiqcmdtime for problematic connection handle is the oldest one . (none other connection handle is older that that - i am 100 % sure in that )

Since issue happened on aug 28 and we rebooted the IQ node during business off hours to clear the version space ( i can't get the results for mentioned issue now ). Will use these queries in future .

[Former Member · Accepted solution]

Mark, We have a script scheduled in cron (which checks for connections which are older than 4 hours )  and it will kill those connections during it's execution .

But the problematic query failed to kill . Fyi I tried to kill that connection handle using "drop connection id " for more than 20 times( after app team confirmed to kill )  ..But no luck .i couldn't able to kick that process out of my server

[markmumy · Accepted solution]

OK, so you have the hard part done! 

Which version of IQ are you on?  We've had past issues with connections not dropping and have done quite a lot to address that.  If you are on IQ 16 SP8 or SP10 then I would strongly recommend that you open a case so that we can provide you with data to collect when this happens again.  That way we can figure out why the connection is not dropping when it should be.

I've seen some issues with tools that put the connection into a state where it is waiting on an OS resource (like a named pipe, FIFO, etc).  That connection is locked by the OS and can't be dropped.  I don't know that I've seen that during queries, though.

Mark

[Former Member · Accepted solution]

Mark, i will check the version tomorrow and let you know . Thanks for your valuable time

At the beginning of our conversation , you have mentioned as " version clean up " . Can you please let me know how to clean up the version space manually ( Do you mean by dropping the oldest connection ?)

[markmumy · Accepted solution]

Version space cleanup happens when all connections that could need that data have committed, rolled back or have exited IQ.  Once that happens, IQ will clean up version space internally.  This typically happens on checkpoint.

Mark