Transaction blocked after 'update statistics'??? 
Author Message
 Transaction blocked after 'update statistics'???

P>
P> This transaction works perfectly everytime EXCEPT after the
P> 'update statistics' command has been executed on 'tableB'.
P>

The update statistics command is lame.  It gathers shared
locks along the way thus preventing an exclusive lock to be
acquired.  That is, you have a reader and a writer waiting
for the reader to be done.

I believe G. Lindahl (sp?) first hammered the group for an
update stat that was non-locking.  I believe 11.5 has
something like this...  
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://www.***.com/ [ /Sybase_FAQ ]



Mon, 24 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

I am currently running Sybase 10, and have recently encountered a
problem with the following transaction:

        Begin transaction
        select count(*) from tableA where ...

                update tableA ....
        else
                insert tableA ....
        update tableB
        commit transaction

This transaction works perfectly everytime EXCEPT after the
'update statistics' command has been executed on 'tableB'.

If the 'update statistics' command has been started on 'table', and
then my transaction is run, then it will wait until the operation
has completed.  The problem is that the transaction never truly aborts.
Instead it remains in Sybase and causes all other transaction to wait on
it.

I have previously been very successful with this command up until I
included the select and conditional statements as part of the
transaction. (I previously always did an insert on both tableA and
tableB.

Any ideas as to why the select and conditional statements on 'tableA'
woud affect the system ?

Thank you



Mon, 24 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Quote:

> I believe G. Lindahl (sp?) first hammered the group for an
> update stat that was non-locking.  I believe 11.5 has
> something like this...  

You never know, maybe Sybase actually listened to that blistering
"user survey" I filled out a while ago... I also really like
some of the other 11.5 features I hear about, too bad I don't
do Sybase anymore.

-- g



Tue, 25 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Greg>
Greg> You never know, maybe Sybase actually listened to that blistering
Greg> "user survey" I filled out a while ago...

Perhaps if they would get rid of the 2K page size and make
it configurable a la Oracle (per table) we could get some
good performance for DSS.  Perhaps with 11.5 and the
informix-like PDQ stuff we can get some good
numbers... (+7MB/s per disk would be nice)

Greg> I also really like some of the other 11.5 features I
Greg> hear about, too bad I don't do Sybase anymore.

I'd be interested to hear what you think of the other
"vendor's" features that you think Sybase can use.  

I just found out that I need to learn The Beast.  I'm sure
that'll make Thomas (of Oracle) grin a little.  :-)
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]



Tue, 25 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Quote:

> I just found out that I need to learn The Beast.  I'm sure
> that'll make Thomas (of Oracle) grin a little.  :-)

        And the number of the Beast is a 666K page size.

-am



Wed, 26 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Anthony>
Anthony> question is "SCSI Bus Throughput - Sequential Disk
Anthony> Access". For a rated 20MB/sec SCSI bus, doing 2K
Anthony> i/o only achieves 2MB/sec of aggregate throughput
Anthony> on a single disk. This peeks at 8MB/sec
Anthony> agg. thruput at 5 to 6 disks.  Doing 64K i/o, you
Anthony> get 6MB/sec of agg. thruput on a single disk,
Anthony> climbing to 17MB/sec with 4 disks.  The author
Anthony> notes that you can get slightly more thruput with
Anthony> even higher i/o (though the bus saturates at fewer
Anthony> disks), but what bothers me is that jumping from a
Anthony> 2K i/o to a 64K i/o only results in a doubling of
Anthony> the aggregate throughput.
Anthony>
Anthony> Makes me wonder if its really worthwhile.

Makes me wonder about his bench... :-) I have an internal
white paper on our *old* boxes with some numbers.  The bench
did some work with two disks per controller on a Challenge
box.  Here are some numbers on sequential operations on a
100 MB file.... but first a simple diagram:

Application        XFS                 XLV Stripe Unit    DISKS
blocking           blocking            (128KB)
(1 MB)             (64K)

 |---|              !---!
 |   |    /-------> !---!--\            |---|
 |   |   /                  \________>  |   | ---------> disk #1
 |   |  /       /-> !---!   /           |---|
 |   | /       /    !---!--/
 |   |/-------/
 |   |              !---!          
 |   |    /-------> !---!--\            |---|
 |   |   /                  \________>  |   | ---------> disk #2
 |   |  /       /-> !---!   /           |---|
 |   | /       /    !---!--/
 |   |/-------/
 .....
 |---|

NOTE:  XFS is SGI's file system and XLV is SGI's logical
       volume manager.

Average performance in MB/sec using 64KB XFS block

writes, using directed I/O:

   Application Blocking      MB/sec w/270 stripe unit size
   256 KB                    8.25
   2048 KB                   26.86

reads, using directed I/O:

   Application Blocking      MB/sec
   256 KB                    42.88 w/64 stripe unit size
   2048 KB                   54.91 w/270 stripe unit size

Note:  directed I/O is slightly slower than raw because of
       the filesystem.
--------------
As can be seen, large application blocking values *do*
help.  I don't know how this person setup their bench....
55MB per second ain't chickenscratch!  :-)

<marketing hat>
We have the bandwidth...  we recently did a terabyte backup
in an hour.  Under an hour cold, a few minutes over an hour
hot:  transactions running on a database.  One hour!  Yee
ha!!!

http://www.sgi.com/Headlines/1997/July/terabyte_release.html

This was done in our lab by one of the folks on my team...
read as:  he's very honest about what he did.  No
flubbing.
</marketing hat>

Anthony> -am    (who's shocked that he'd buy a book by anyone named Wong,
Anthony>     and especially one with Capacity Planning in the title)

You must have hit yur head on something... :-)
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]



Thu, 27 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Quote:

> Perhaps if they would get rid of the 2K page size and make
> it configurable a la Oracle (per table) we could get some
> good performance for DSS.

        I seem to remember that you mentioned once before
        that you liked Orable's 64K i/o. I know that performance
        seems to improve with the larger page sizes available
        under System XI when run against large selects
        (according to my findings and those of others and
        published docs).

        However, I've been doing some reading lately (yeah,
        I know, dangerous thing to do!), in particular
        Brian L. Wong's book "Configuration and Capacity
        Planning for Solaris Servers" (here's a reference URL -
        http://www.sun.com/books/books/Wong/Wong.html
        [talk about two words!]), and the graph and comments
        on page 294 caught my attention. The section in
        question is "SCSI Bus Throughput - Sequential Disk
        Access". For a rated 20MB/sec SCSI bus, doing 2K i/o
        only achieves 2MB/sec of aggregate throughput on a single
        disk. This peeks at 8MB/sec agg. thruput at 5 to 6 disks.
        Doing 64K i/o, you get 6MB/sec of agg. thruput on a
        single disk, climbing to 17MB/sec with 4 disks. The
        author notes that you can get slightly more thruput
        with even higher i/o (though the bus saturates at
        fewer disks), but what bothers me is that jumping from
        a 2K i/o to a 64K i/o only results in a doubling of
        the aggregate throughput.

        Makes me wonder if its really worthwhile.

-am    (who's shocked that he'd buy a book by anyone named Wong,
        and especially one with Capacity Planning in the title)



Thu, 27 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Quote:

> Greg> I also really like some of the other 11.5 features I
> Greg> hear about, too bad I don't do Sybase anymore.

> I'd be interested to hear what you think of the other
> "vendor's" features that you think Sybase can use.  

I have whined about all of these in the past:

* parallelized system operations such as dbcc (supposedly in 11.5),
  bulkcopy, etc.
* cache simulator, a la Oracle, to tell you what your hit rate
  would be with more or less cache
* help determining when update statistics _needs_ to be run, or when
  a table is physically fragmented
* even better, incremental update statistics and defragment
* alternate caching algorithms such as two-level caching, which are
  very helpful for keeping the occasional table scan from trashing
  your cache.

-- g



Fri, 28 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Pablo>
Pablo> Boy I can't recall IPI drives...  I should do an altavista
Pablo> search...  what was their problem?  (Why'd they die?)
Pablo> --

Okay, I did a hotbot search and found some info on the IPI
drives...  made by seagate... several different models...
doing a quick view, the fastest drive I found was teh
ST-41800K Elite 2:

        External Xfer Rate.... 7.5MB/sec
        Spindle Speed......... 5,400 RPM
        Avg Access............  11.5ms

Compared to, say, an IBM Ultrastar ES2

        Spindle Speed......... 5,400 RPM
        Avg Access............ 10ms
        External Xfer Rate ... 40MB/sec
        MTB................... 800,000 HR  Wow!  

I've seen drives that spin at 7,200 RPM....  what was the
benny on the IPI drives?  Thx!
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]



Fri, 28 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Anthony>
Anthony>
Anthony>     That's obviously using a controller(s) with a wider
Anthony>     bandwidth than the 20MB/sec one the author used.
Anthony>

Nah, I believe that we only hooked up two disks per
controller.  :-)  There's a commercial in the states that
goes something like, "you can go for performance (a picture
of a sportscar) or go for space (a picture of a
yuppy-mobile)"... same thing with disks.... if you want to
push the disks hard, you need to:

1) not use all the disk
2) attach as few disks as possible to controllers.

Anthony, I know you know the above but I'm being verbose in
case anyone still gives a hoot about our little conversation
here...

Anthony>
Anthony> P.S. The author also had a few interesting things to say about
Anthony>      IPI drives. I used them with an old 4.9.2 server for the
Anthony>      log devices and found them to be our best performing drives
Anthony>      (from what SQL Server Monitor showed - I posted these details
Anthony>      late last year/early this year). Makes me sort of wish IPIs
Anthony>      were still around.

Boy I can't recall IPI drives...  I should do an altavista
search...  what was their problem?  (Why'd they die?)
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]



Fri, 28 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Quote:


> > but what bothers me is that jumping from a
> > 2K i/o to a 64K i/o only results in a doubling of
> > the aggregate throughput.

> > Makes me wonder if its really worthwhile.

> Makes me wonder about his bench... :-) I have an internal
> white paper on our *old* boxes with some numbers.  The bench
> did some work with two disks per controller on a Challenge
> box.  Here are some numbers on sequential operations on a
> 100 MB file.... but first a simple diagram:

        The author didn't document how he arrived at his
        findings (at least I didn't read anything along those
        lines - but then I haven't read the entire book yet!)

Quote:
> NOTE:  XFS is SGI's file system and XLV is SGI's logical
>        volume manager.

> Average performance in MB/sec using 64KB XFS block

> writes, using directed I/O:

>    Application Blocking      MB/sec w/270 stripe unit size
>    256 KB                    8.25
>    2048 KB                   26.86

> reads, using directed I/O:

>    Application Blocking      MB/sec
>    256 KB                    42.88 w/64 stripe unit size
>    2048 KB                   54.91 w/270 stripe unit size
> --------------
> As can be seen, large application blocking values *do*
> help.  I don't know how this person setup their bench....
> 55MB per second ain't chickenscratch!  :-)

        That's obviously using a controller(s) with a wider
        bandwidth than the 20MB/sec one the author used.

Quote:
> > -am    (who's shocked that he'd buy a book by anyone named Wong,
> >        and especially one with Capacity Planning in the title)

> You must have hit yur head on something... :-)

        I must be missing the earbashing!

-am

P.S. The author also had a few interesting things to say about
     IPI drives. I used them with an old 4.9.2 server for the
     log devices and found them to be our best performing drives
     (from what SQL Server Monitor showed - I posted these details
     late last year/early this year). Makes me sort of wish IPIs
     were still around.



Sat, 29 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Quote:

> Nah, I believe that we only hooked up two disks per
> controller.

        How'd you get a bandwidth of about 50MB/sec with just
        2 controllers? (I'm assuming that they do 20MB/sec each)

Quote:
> if you want to
> push the disks hard, you need to:

> 1) not use all the disk
> 2) attach as few disks as possible to controllers.

        This matches the graph in Wong's book. I think
        I mentioned at what number of disks per controller
        the thruput leveled off.

Quote:
> Anthony> P.S. The author also had a few interesting things to say about
> Anthony>      IPI drives. I used them with an old 4.9.2 server for the
> Anthony>      log devices and found them to be our best performing drives
> Anthony>      (from what SQL Server Monitor showed - I posted these details
> Anthony>      late last year/early this year). Makes me sort of wish IPIs
> Anthony>      were still around.

> Boy I can't recall IPI drives...  I should do an altavista
> search...  what was their problem?  (Why'd they die?)

        Not much demand. Wong notes in his book that both SCSI and IPI
        were spec'ed at about the same time by the same commitee (or what-
        ever). SCSI was meant for small computers while IPI was designed
        for larger systems doing serving etc. IPI was meant to have
        better performance (but at the obvious higher cost). Obviously,
        end users went for the cheaper alternative and manufacturers
        didn't much bother making both kinds.

-am



Sat, 29 Apr 2000 03:00:00 GMT
 Transaction blocked after 'update statistics'???

Anthony>
Anthony>     How'd you get a bandwidth of about 50MB/sec with just
Anthony>     2 controllers? (I'm assuming that they do 20MB/sec each)
Anthony>

Quote:
>> if you want to
>> push the disks hard, you need to:

>> 1) not use all the disk
>> 2) attach as few disks as possible to controllers.

Anthony>
Anthony>     This matches the graph in Wong's book. I think
Anthony>     I mentioned at what number of disks per controller
Anthony>     the thruput leveled off.
Anthony>

I wasn't clear, sorry.  The machine had four FWD controllers
each with two disks except for the system disk{*filter*} off
the first controller.  The numbers are with eight disks on
four controllers.

Essentially, each drive was being driven to its max of about
7.5MB/sec = 8*7.5 = 60MB/sec
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://www.***.com/ [ /Sybase_FAQ ]



Sat, 29 Apr 2000 03:00:00 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. Blocking SYB11 (set option blocking = 'off')

2. Transaction blocking other user's requests ??

3. DBCC Block User's Transaction

4. SQLOLE and update statistics: Can't stop from running it

5. Progress of Update Statistics/DBCC's

6. Update statistics - Questions for guru's ?

7. Update statistics - Questions for guru's ?

8. Can't update past SP1 but I blocked udp port 1434

9. Getting statistics on table 'hits'?

10. 'user commits' statistic for one user

11. Unidata : 'udtmon' statistics

12. Connection 'hanging', blocking other connections


 
Powered by phpBB® Forum Software