RE: Excessive Transaction Versioning

[Mark Mumy]

David,

 

On the surface it looks like this connection isn't issuing a commit along the way.    It is doing a lot of work (the SA to IQ and IQ to SA counts are high meaning a lot of stuff is moving back and forth internally).

 

Do you know what this connection is doing?  That might help shed some light on the issue.

 

Mark

 

============================================================
Mark D. Mumy
Principal Systems Consultant
Sybase, Inc.
VOICE: 972.687.6478
MOBILE: 972.839.9346
mailto:Mark...@sybase.com
============================================================

 

"The greatest accomplishment is not in never falling,
         but in rising again after you fall."  -- Vince Lombardi

 

---

From: owner...@ods4o.odshp.com [mailto:owner...@ods4o.odshp.com] On Behalf Of Louie, David
Sent: Tuesday, June 13, 2006 4:24 PM
To: iq...@odscompanies.com
Subject: Excessive Transaction Versioning

We are seeing excessive versioning in out IQ database server

  Other Versions:                       
        39393 = 130Gb                                                                                      
                           

When I execute sp_iqtransaction I see 39K transactions most in commited state .

We think it is this spid in IQ:

Name:                
Userid:              tsgops
LastReqTime:         2006-06-13 16:26:28.450
ReqType:             STMT_EXECUTE_ANY_IMM
IQCmdType:           RELEASESAVEPOINT
LastIQCmdTime:       Jun 13 2006  4:26PM
IQCursors:           0
LowestIQCursorState: NONE
IQthreads:           1
TxnID:               16353114
ConnCreateTime:      Jun 13 2006  1:10PM
TempTableSpaceKB:    93480
TempWorkSpaceKB:     128
IQconnID:            94
satoiq_count:        216108
iqtosa_count:        1669301
CommLink:            local
NodeAddr:            
LastIdle:            8
Dbremote:            0
 

Which had been executing since 1:10PM. 

Does anyone know what RELEASESAVEPOINT cmd type is?
What could be the reason for not releasing the versions and queuing up in dbspace?

I've told the user to load up the IQ table in batches which is more efficient as he is doing 1 single row inserts. 

Why would single row insert create all these versions?  The read activity on this server is minimal as this is our dev box.  (only 3 users connected.

Thanks

David Louie
Database Administration
BlackRock Solutions
(212) 810-3541

THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof.

[Carlos J. Delgado]

Hi All,

 

Could it be TRUE the two following issues?:

1.-Extract binary  is faster than extract ascii

2.- Load binary is slower than load ascii

 

Question: Is the LOAD algorithm in multiplex different than in simplex ?

Taken away  the writer and reader issue .

Why are there  suggestions or it seems to be - recent emails in the iqug - !!

That the loads thr'  mpx

would be faster than in simplex !!  Is that TRUE ?

 

[Carlos J. Delgado]

--According to John.Barton --

 IQ is very good at ETL processing so loading a large number of rows into a
staging table and then processing the load data it is a commonly used
technique.

John Barton

--------------

John,

Do you mean , staging into same IQ and then processing ?

What would be better :

A single 100 column table OR several narrow tables in a star fact-dimension lay out ??

Or Once the single table is loaded .Then populate the federated tables ( fact and dimensions ) from the already loaded table.

Thank You,

carlos

 

 

[Ron Watkins]

The only possible variations in speed between Simplex and Mpx that I can
think of would be due to
1) shared cpu between mpx nodes. In this case, the writer may have less
resources than the simplex node.
2) overhead for dbremote to replicate activity in writer node. This should
be so small as to hardly be measurable
As for the binary/ascii issue, in my environment, binary is faster than
ascii on both extract and loads. I know that with ascii, there are
different ways to load, such as single threaded, column seperated, fixed
width, etc... Those affect speed as well, while binary is only one way to
load.
Ron

--
Ron Watkins
602.547.9342 (w)
602.743.5272 (c)

[Ron Watkins]

However, insert...select... is not very fast, compared to direct flat-file
loading. I believe this is due to the overhead of creating rows from
columns on the select side, only to be broken back into columns on the
insert...
Ron

[Mark Mumy]

The nature of multiplex is such that we segregate performance between nodes.  It makes no difference to loads whether the loading node is a writer node or a simplex node.  Assuming that all the hardware is the same and that the only difference is whether IQ is simplex or multiplex, there will be no load time differences.

 

Binary loads into IQ should be faster than ASCII as we don't have to do the ASCII to binary conversion internally with a binary load.  The same is true on a binary extraction.  So, both binary in and binary out should be faster than ASCII in and ASCII out.  Of course mileage can vary.  Binary data is always fixed width (has to be) whereas ASCII can be variable if our output is delimited.  In the case of a disk subsystem that is slow, writing more bytes of data in binary format may appear to take longer because more data has to be written than an ASCII unload.  If the disk subsystem is fast enough to handle the load, binary should be faster.

 

Mark

 

============================================================
Mark D. Mumy
Principal Systems Consultant
Sybase, Inc.
VOICE: 972.687.6478
MOBILE: 972.839.9346
mailto:Mark...@sybase.com
============================================================

 

"The greatest accomplishment is not in never falling,
         but in rising again after you fall."  -- Vince Lombardi

 

---

From: owner...@ods4o.odshp.com [mailto:owner...@ods4o.odshp.com] On Behalf Of Carlos J. Delgado
Sent: Wednesday, June 14, 2006 2:53 AM
To: iq...@odscompanies.com
Subject: LOAD statement

[Mark Mumy]

Any connection can commit its work and other users will see it when they request the latest information.  The state will change from COMMITED to APPLIED when the oldest open transaction commits.  If transactions are stuck in a "COMMITTED" state it generally means that there is one or more transactions that are older than that one.  When the older transactions commit the information, the COMMITED transactions will change to APPLIED.  When a checkpoint runs the APPLIED transactions are then removed from this output.

 

Mark

 

============================================================
Mark D. Mumy
Principal Systems Consultant
Sybase, Inc.
VOICE: 972.687.6478
MOBILE: 972.839.9346
mailto:Mark...@sybase.com
============================================================

 

"The greatest accomplishment is not in never falling,
         but in rising again after you fall."  -- Vince Lombardi

 

---

From: owner...@ods4o.odshp.com [mailto:owner...@ods4o.odshp.com] On Behalf Of shah
Sent: Wednesday, June 14, 2006 12:25 PM
To: John....@sybase.com; iq...@odscompanies.com
Subject: RE: Excessive Transaction Versioning

Hi

 

I have regular problem with versionining.

Currently i have 4344 transaction with sp_iqtransaction.

Also all transaction is in Commieted State.  why IQ is not releasing the same.

 

 

But active connection i can see only 10. 

 

 

regards,

dc

John....@sybase.com wrote:

David,

Each commit is a version, so if each insert or load table get committed
separately and a large number of loads are performed then this would result
in a large number of versions as you described in your email. In versioning
a transaction is a version. You can see the pages created and pages
destroyed in sp_iqtransaction. IQ will keep a version around only as long
as necessary. IQ also removes Intermediate versions, these are versions
that can not be used based on their transaction id and all the connection
transaction id's of the database connections. Use sp_iqconection to
identify the connection(s) which is preventing versions from getting
released, it has the lowest transaction id.

Even it you load periodically you are going to have versions. It would be
much more efficient to load tables in larger transactions preferably with
the load table statement. The speed of individual inserts and wasted IQ
processing power will easily justify moving to a periodic loading. Data
currency will also get better as IQ is less likely to fall behind. IQ is

very good at ETL processing so loading a large number of rows into a
staging table and then processing the load data it is a commonly used
technique.

John Barton

Principal Systems Consultant
Sybase Inc. - OEM Business Unit
Office: 602-346-5179
Mobile: 602-418-4308
john....@sybase.com
============================================================




"Louie, David"
krock.com> To
Sent by: "Mark Mumy" ,
owner-iqug@ods4o.
odshp.com cc

Subject
06/13/2006 06:47 RE: Excessive Transaction
PM Versioning










Mark,

The user is putting data into a file (1 row / file) . Generic load process
picks up the file into an IQ #temp table and insert from the #temp table
into IQ table. Basically he's single row inserting data into an IQ table!
I told him IQ is better for bulk loading verses single row inserts. This
would explain the SA to IQ and IQ to SA high counts.

Would single row inserts cause excessive versioning verses bulk inserting
the preferred method of loading an IQ table?

Thanks
-David

From: Mark Mumy [mailto:mark...@sybase.com]
Sent: Tuesday, June 13, 2006 6:02 PM
To: Louie, David; iq...@odscompanies.com
Subject: RE: Excessive Transaction Versioning

David,

On the surface it looks like this connection isn't issuing a commit along
the way. It is doing a lot of work (the SA to IQ and IQ to SA counts are
high meaning a lot of stuff is moving back and forth internally).

Do you know what this connection is doing? That might help shed some light
on the issue.

Mark

============================================================
Mark D. Mumy
Principal Systems Consultant
Sybase, Inc.
VOICE: 972.687.6478
MOBILE: 972.839.9346
mailto:Mark...@sybase.com
============================================================

"The greatest accomplishment is not in never falling,
but in rising again after you fall." -- Vince Lombardi

From: owner...@ods4o.odshp.com [mailto:owner...@ods4o.odshp.com] On

Send instant messages to your online friends http://in.messenger.yahoo.com

Stay connected with your friends even when away from PC. Link: http://in.mobile.yahoo.com/new/messenger/