How to search solved cases ?

[Former Member]

Hi,

Can anyone help with how to search solved cases ?

I'm using the Sybase Support Portal and have selected "KBAs & Solved Cases".

There is just one box to search for.

I select "create index hang" and get "Displaying 1-10 of 5000 results".

How can I restrict the search to just version 15.7 of Sybase ? (I tried searching "create index hang 15.7" but that doesn't help)

Or can can I order it or restrict it on date ? (The first page includes a case from "16.04.2002" which I'd like to filter out).

[former_member182259]

Why do you think it "hangs" instead of that the create index is just taking a while??   How big is the table, what is the index definition, and do you have access to the MDA tables???

Bug fixes for each release are the coverletter shipped with each release.

[former_member182259]

I should also point out that HW has a significant role in create index speed - especially IO subsystem and CPU speed....so you might want to let us know what HW you are running on....and if running in a VM that might be swapped.

[Former Member]

> Why do you think it "hangs" instead of that the create index is just taking a while??

It ran for 373 minutes on a temp table with 1 row in it - syslogs filled up (probably due to other processes)

We killed the client process and ran "kill <spid>" on ASE and it wouldn't die.

syslogshold showed it was holding syslogs.

The only way we could free it up was to bounce the server - it happens about 5% of the time with this proc. (3x in 2 days)

We've just upgraded from 15.5 -> 15.7 (2 days) and it was working fine with 15.5.

We've reloaded the proc just in case we had some corruption.

> How big is the table, what is the index definition,

1 row in it (sometime it can be 2000 rows)

CREATE TABLE #CMappingIO

(   CID     varchar(36) NOT NULL,

    Date        datetime    DEFAULT '01-JAN-1900' NOT NULL,

    CIDTo   varchar(36) NULL

)

We're using 5 tempdb's each of 4Gb 

> and do you have access to the MDA tables???

Yes we automatically collect a lot of the data.

plan is "create index"

Here's what we have from MDA

228

tempdb_grid3

13531

Mar 2 2015 6:55PM

313 mins

$CREATE INDEX non-clustered #CMappingIO.i1 ID=1700879068

In Command

All of these show times of < 3 seconds

wait to acquire latch

waiting for a disk read to complete

waiting for a disk write to complete

waiting for device semaphore

waiting for input from the network

waiting for memory or a buffer

waiting to be scheduled

waiting to output to the network

waiting to take a lock

wait for mass read to finish when getting page

[Former Member]

> I should also point out that HW has a significant role in create index speed - especially IO subsystem and

> CPU speed....so you might want to let us know what HW you are running on....and if running in a VM that

> might be swapped.

We're running on a 48 core (24 core x 2 thread x 2.7GHz), 512 Gb  RAM (no VM used)

[former_member182259]

Tell your DBA to check out lct_admin() function.   Kill typically isn't used on processes suspended due to a full log in tempdb.

select lct_admin('abort', <spid>)

[former_member182259]

Actually, that is a 24 core box....a thread isn't a core - it simply leverages idle CPU time while another thread was executing a multi-clock cycle instruction (e.g. a main memory fetch).    Be careful with assuming you have 48 cores of power there - on x86, that is only about 36 engines on a 24 core/2 threads per core host.

[Former Member]

Yep -my mistake -  I mean to write "48 *process" (24 core x 2 thread x 2.7GHz), 512 Gb  RAM (no VM used)"

[former_member182259]

One thing to point out - if you create an index, ASE has to log the index insertions (as well as the DDL change).  So if the create index was in log suspend, then it definitely was trying to append to the transaction log when it couldn't due to log full....resulting in log suspend.    As noted, a process in log suspend should be aborted with lct_admin('abort',<spid>) vs. kill.    Kill attempts a rollback - which of course, needs log space to do so - kind of a self defeating mechanism when in log space. 

[Former Member]

We have another create index running for 55 mins on a table of 1181 rows.

syslogs grew to 4Gb

syslogs         0               3995408 KB      3995408 KB      0 KB            0 KB

so we ran select lct_admin('abort', 1947)

and got

    "Cannot abort process 1947, as it is not sleeping on log-suspend state in database 'tempdb4'."

trying to insert more data into the tempdb shows

Can't allocate space for object 'syslogs' in database 'tempdb4' because 'logsegment' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.

and kill does nothing

[Former Member]

Hi,

Just to say - we've found the problem

The new server was set up in "thread" mode - by changing it to "process" mode we haven't had any problems since then (about 48 hours of stability so far)

Is there any plan to either improve the Solved case searching ability ?

Or could there be an option to allow a download of the cases returned in a search so we can filter them ourselves ?

Thanks

[former_member182259]

That sounds suspiciously like the IO Controller threads - especially the disk task - was getting clipped by the OS.   You may think that is weak, but we had a huge issue with Solaris customers moving to the threaded kernel due to an OS bug that prevented process threads from doing kernel async IO.   Oracle released a jumbo patch to fix and then we had to put a config in sp50 & sp100 to enable.    With saying only 2 threads/core, however, I don't suspect you are running Solaris on SPARC....    What is your OS release??

WRT solved cases - sorry - I can't address that - that is a question for AGS support.

[Former Member]

Hi

Thanks for the reply.

After changing from thread to kernel - we managed 3 days without needing to bounce - but then we found the same problem - so its not only down to threading mode.

We've now dropped the number of engines and we're running at very low utilisation in the hope we don't need to bounce very often.

We're going to try SP132 to see if that fixes it and if not then we'll look at downgrading to 15.5.

With regard to this ..

> especially the disk task - was getting clipped by the OS.You may think that is weak,

> but we had a huge issue with Solaris customers moving to the threaded kernel due to an OS

> bug that prevented process threads from doing kernel async IO. 

Don't think its weak - I spent many years as a low-level C coder. Trying to get this code running across different flavours of Unix wasn't easy in my day.

> What is your OS release??

We're running Red Hat Enterprise Linux Server release 6.4 kernel 2.6.32-431.17.1.el6.x86_64.

Mike

[Former Member]

Thanks for the help - this has now been confirmed as a bug in ASE.

I'll find out what version it was introduced in.

[Former Member]

FYI... I've been told it was introduced in SP111

[Former Member]

In case anyone else is affected...

Here are the notes for this issue.

https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F6465...

[Former Member]

Alternatively can someone point me to the list of bugs fixed in the recent releases ?