ASE latest EBF crashing when near 300 connections
f.alves
near 300 connections. I have the core dumps. My application
is a read-only database, not a single write occurs. I have
12 Engines active, and unlimited RAM,procedure cache, etc.
This is my sp_monitorconfig 'all'
Name,Num_free,Num_active,Pct_act,Max_Used,Reuse_cnt
'txn to pss ratio',8192,0,' 0.00',0,0
'size of unilib cache',357678,976,' 0.27',976,0
'size of shared class heap',6144,0,' 0.00',0,0
'size of process object he',3000,0,' 0.00',0,0
'size of global fixed heap',300,0,' 0.00',0,0
'procedure cache size',250239,7762,' 3.01',13076,0
'permission cache entries',15,0,' 0.00',0,0
'partition groups',1024,0,' 0.00',0,0
'number of worker processe',20,0,' 0.00',18,0
'number of user connection',482,30,' 5.86',33,0
'number of sort buffers',500,0,' 0.00',288,0
'number of remote sites',10,0,' 0.00',0,0
'number of remote logins',20,0,' 0.00',0,0
'number of remote connecti',20,0,' 0.00',0,0
'number of open partitions',4244,756,' 15.12',756,0
'number of open objects',4943,57,' 1.14',60,0
'number of open indexes',454,46,' 9.20',46,0
'number of open databases',3,9,' 75.00',9,0
'number of messages',64,0,' 0.00',18,0
'number of mailboxes',0,56,'100.00',60,0
'number of locks',9220,780,' 7.80',2176,0
'number of large i/o buffe',6,0,' 0.00',1,0
'number of java sockets',496,33,' 6.24',33,0
'number of dtx participant',500,0,' 0.00',0,0
'number of devices',6,4,' 40.00',4,0
'number of aux scan descri',256,0,' 0.00',0,0
'number of alarms',27,13,' 32.50',14,0
'memory per worker process',640,384,' 37.50',592,0
'max online engines',0,12,'100.00',12,0
'max number network listen',4,1,' 20.00',1,0
'max memory',26911524,1208476,' 4.30',1208476,0
'max cis remote connection',2048,0,' 0.00',0,0
'heap memory per user',20000,0,' 0.00',0,0
'disk i/o structures',256,0,' 0.00',240,0
'audit queue size',100,0,' 0.00',0,0
'additional network memory',11007996,3982880,'
26.57',3985152,0
My tempdb is 3 GB.
The question is: since I have a developer license and there
is no free support, how do I get some help? I am not going
to pay for support incidents on a crash, that is unethical.
This product should not crash, instead, it should write an
error to the log. I feel that Sybase maybe the wrong tool,
but before I abandon the project and look elsewhere, I want
to get your feeling on what is happenning.
I know a few thins:
The dik is not full
Tempdb is not full
Memory is plenty
procedure cache is huge, as you can see.
Any idea? It crashes all the time I get near 300
connections. Futhermore, all connections execute one single
stored procedure. I followe the instructions in the Tips and
Tricks book and enabled all the possible merges, so Sybase
can choose the best query plan. In fact, performance is
perfect for my needs.
Except for the crashes.
if somebody wants to look further into the issue, please
contactme to venefax at gmail.
Rob V [Sybase]
If it is you app, you need to debug it and determine where it dies. If it is
Sybase ASE, there should be a stack trace in the ASE errorlog file, which
would be helpful to post.
Exactly what flavour of ASE are you're running? The ASE Developer's Edition
is limited to 25 user connections, so it's probably not that.
HTH,
Rob V.
-----------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------
<F. Alves> wrote in message news:49e37b5c.67...@sybase.com...
Neal Stack [Sybase]
ASE catches all signals that generally cause a crash (SIGBUS,
SIGSEGV, etc.) and writes details to the log file about the
spid that caused the problem, the SQL the spid was executing
and the stack trace of the spid.
If ASE crashes without this information, it should create a
core file (unless you have ulimit coredumpsize set to 0).
Please provide the exact version/EBF of ASE encountering the
problem (type select @@version and provide the results), post
the log file from the time of the crash so we can see if there
are any related errors.
I am not sure how you get 300 concurrent connections to the
Developer's Edition when it only allows 25:
http://infocenter.sybase.com/help/topic/com.sybase.dc35890_1500/html/instalhp/CHDDBEED.htm
1> sp_configure "number of user connections", 300
2> go
Msg 10848, Level 16, State 1:
Server 'NSTACKXP', Procedure 'sp_configure', Line 912:
The Adaptive Server Enterprise Developer Edition does not allow you to set this
configuration parameter to a value higher than 25.
Regards,
Neal
f.alves
did configure 500 connections. The developer license is only
until I see if ASE works as my company needs it. And yes, I
have two core dumps, and no trace of any kind of any issue
in the error log. What crashes is ASE. My app runs in
another box. This is my @@version:
Adaptive Server Enterprise/15.0.3/EBF 16549
ESD#1/P/x86_64/Enterprise Linux/ase1503/2681/64-bit/FBO/Mon
Apr 13 04:10:47 2009
Yours
Federico
Neal Stack [Sybase]
OK, if you have two core files, I would suggest getting stack
traces from them with gdb. For example:
% gdb $SYBASE/$SYBASE_ASE/bin/dataserver core.xxxx
(gdb) thread apply all where
If you don't have gdb, let me know and I will create a directory
on our ftp site so you can upload the files so we can examine
them to see if you are running into a known issue.
Regards,
Neal
f.alves
Core was generated by `/opt/sybase/ASE-15_0/bin/dataserver
-ONLINE:1,0,0x1024001, 0x1427c3000, 0x5e448'.
Program terminated with signal 11, Segmentation fault.
#0 0x00000039c9430012 in ?? ()
(gdb) thread apply al bt full
Thread 7 (process 4120):
#0 0x00000039ca00cc8b in ?? ()
No symbol table info available.
#1 0x0000000000000000 in ?? ()
No symbol table info available.
Thread 6 (process 4121):
#0 0x00000039ca00a496 in ?? ()
No symbol table info available.
#1 0x0000000000000000 in ?? ()
No symbol table info available.
Thread 5 (process 4122):
#0 0x00000039ca00a496 in ?? ()
No symbol table info available.
#1 0x0000000000000000 in ?? ()
No symbol table info available.
Thread 4 (process 4123):
#0 0x00000039ca00a496 in ?? ()
No symbol table info available.
#1 0x0000000000000000 in ?? ()
No symbol table info available.
Thread 3 (process 4124):
#0 0x00000039c94c92e6 in ?? ()
No symbol table info available.
#1 0x00000000432048e0 in ?? ()
No symbol table info available.
#2 0x0000000000000001 in ?? ()
No symbol table info available.
#3 0x0000000002793910 in Msg_unistrPrm ()
No symbol table info available.
#4 0x0000000000e5c4ce in ct__api_secencryption ()
No symbol table info available.
#5 0x0000000000cc38ac in ups__upd_indptnmap ()
No symbol table info available.
Previous frame inner to this frame (corrupt stack?)
Thread 2 (process 4125):
#0 0x00000039c9431624 in ?? ()
No symbol table info available.
#1 0x0000000000cbe2aa in rbld_rebuild_indexes ()
No symbol table info available.
Previous frame inner to this frame (corrupt stack?)
Thread 1 (process 4119):
#0 0x00000039c9430012 in ?? ()
No symbol table info available.
Cannot access memory at address 0x3c00e5213e353
(gdb)
Neal Stack [Sybase]
The address for thread #0 is not in the dataserver's address space
which usually indicates that this was an issue caused by something
outside of ASE.
Program terminated with signal 11, Segmentation fault.
#0 0x00000039c9430012 in ?? ()
(gdb) pwd
Working directory /remote/releases/linuxamd/rel1503_ebfs/ebf16549/ASE-15_0/bin.
(gdb) x 0x00000039c9430012
0x39c9430012: Cannot access memory at address 0x39c9430012
Some of the other addresses in the stack trace are in the dataserver's range:
(gdb) x 0x0000000000cc38ac
0xcc38ac <ups__upd_indptnmap+92>: 0x4466c189
Please provide the version and glibc level of the operating system
you are running ASE on. Or just include the ASE's error log when
you upload the core file.
I have created a directory for you on our ftp site to upload the
core file so I can examine the problem further. I will send you the
instructions on how to upload the core file(s) in a separate email.
Regards,
Neal
f.alves
for support 24x7? I somebody from Sybase may actually give
me a free support account until I finish my evaluation, that
woulde just fair.
kernel Current process (0x1460146) infected with signal 11
(SIGSEGV)
kernel Address 0x0x00000000009eb85e
(TabStatGetSysTab(GttiTableOrIndex*, int)+0x1e), siginfo
(code, address) = (1, 0x0x0000000000000008)
kernel ************************************
kernel SQL causing error : der.id_prov <> @self_provider
--and provider.ipaddress like '%.%.%.%'
order by len(provrates_x.code) desc,
inter asc
kernel Current Procedure: XRoutingX
kernel Current statement number: 79 Current line number:
171
kernel ************************************
server SQL Text: der.id_prov <> @self_provider
--and provider.ipaddress like '%.%.%.%'
order by len(provrates_x.code) desc,
inter asc
server SQL Text: select provrates_x.id_prov,
provrates_x.code, provrates_x.inter, provrates_x.peakid,
provrates_x.termcode, provrates_x.prefix, outmap.codclient,
outmap.id_prov, outmap.disabled, provider.id_prov,
provider.name, provider.contactname, provider.contacttitle,
provider.address, provider.city, provider.region,
provider.postalcode, provider.country, provider.phone,
provider.fax, provider.homepage, provider.date_in,
provider.status, provider.line800s, prov
server SQL Text: ider.totalsold, provider.balance,
provider.disabled, provider.isdefault, provider.alias,
provider.masterclient, provider.prefix, provider.bottleneck,
provider.ipaddress, provider.protocol,
provider.msrepl_tran_version, provider.mytimestamp,
provider.ldaccess, provider.flag1, provider.flag2,
provider.flag3, provider.flag4, provider.flag5,
provider.virtual, provider.clid, provider.forceani,
provider.roundto, provider.mintime, provider.termcode, provi
06:00000:00396:2009/03/05 16:00:00.21 server SQL Text:
der.rotateani, provider.postfix --select @plist=@plist
+Plist
-- 'g'+convert(varchar,provrates_x.id_prov)+
-- 'h'+str_replace(provrates_x.code,'%',null)+
-- 'i'+convert(varchar,provrates_x.inter)+
-- 'j'+convert(varchar,provrates_x.peakid)+']-'
from (select
ipaddress,id_prov,code,inter,peakid,termcode,prefix,postfix
FROM provrates_x WHERE
(intcode=convert(int,left(@tempcode,4)) or intcode=1) and
patindex(code, @tempcode) > 0 and provrates_x.inter <
06:00000:00396:2009/03/05 16:00:00.22 server SQL Text: =
abs(@firstmin)) provrates_x INNER JOIN outmap ON
provrates_x.id_prov = outmap.id_prov and outmap.codclient =
@codclient and outmap.disabled = 0 inner join provider on
provrates_x.id_prov = provider.id_prov and
outmap.id_prov=provider.id_prov and flag1>=@flag1 and
provider.disabled = 0 and provider.id_prov <> @self_provider
--and provider.ipaddress like '%.%.%.%'
order by len(provrates_x.code) desc, inter asc --select
'Plist'=@plist, len(@plist)
kernel curdb = 4 tempdb = 2 pstat = 0x10000
kernel lasterror = 313 preverror = 0 transtate = 1
kernel curcmd = 192 program =
kernel extended error information: hostname: kosher login:
minixel
kernel pc: 0x0000000001673b95 pcstkwalk+0x30()
kernel pc: 0x0000000001673989 ucstkgentrace+0x199()
kernel pc: 0x000000000162cede ucbacktrace+0x40()
kernel pc: 0x000000000074ac5b terminate_process+0xccb()
kernel pc: 0x0000000001654755 kisignal+0x255()
kernel pc: 0x00000000009eb85e
TabStatGetSysTab(GttiTableOrIndex*, int)+0x1e()
kernel pc: 0x00000000007c5356
GttTable::GtGatherStats(OptBlock*)+0x276()
kernel pc: 0x00000000009e7ee2
StgStatPkg::StgStatPkgSecondaryInit(OptBlock*)+0x572()
kernel pc: 0x00000000007893e3
OptGlobal::_GblSearchEngine(int*)+0x783()
kernel pc: 0x000000000078a42c
OptGlobal::GblOptimize(int*)+0xfc()
kernel pc: 0x000000000078ccf3 Optimize+0x163()
kernel pc: 0x0000000000bfbe38 s_compile_stmt+0x598()
kernel pc: 0x0000000000bfa33c s_compile+0x3ec()
kernel pc: 0x000000000147157e s_recompile+0x194()
kernel [Handler pc: 0x0x0000000000c458c0 hdl_stack
installed by the following function:-]
kernel [Handler pc: 0x0x0000000000c23ac0 s_handle installed
by the following function:-]
kernel pc: 0x000000000194a35c sequencer+0x10dc()
kernel pc: 0x0000000000c10209 execproc+0x859()
kernel pc: 0x0000000000c1b239 s_execute+0x8289()
kernel [Handler pc: 0x0x0000000000c458c0 hdl_stack
installed by the following function:-]
kernel [Handler pc: 0x0x0000000000c23ac0 s_handle installed
by the following function:-]
kernel pc: 0x000000000194999e sequencer+0x71e()
kernel pc: 0x0000000000c10209 execproc+0x859()
kernel pc: 0x0000000000c1b239 s_execute+0x8289()
kernel [Handler pc: 0x0x0000000000c458c0 hdl_stack
installed by the following function:-]
kernel [Handler pc: 0x0x0000000000c23ac0 s_handle installed
by the following function:-]
kernel pc: 0x000000000194999e sequencer+0x71e()
kernel pc: 0x00000000010fa4b6 tdsrecv__dynexec+0xd2()
kernel pc: 0x00000000010f90fe tdsrecv_dynamic+0xf5()
kernel [Handler pc: 0x0x0000000000cd1da0 ut_handle
installed by the following function:-]
kernel pc: 0x000000000180af0e conn_hdlr+0x133e()
kernel end of stack trace, spid 396, kpid 21365062, suid 1
F.Alves
server SQL Text: der.rotateani, provider.postfix --select
@plist=@plist +Plist
-- 'g'+convert(varchar,provrates_x.id _prov)+
-- 'h'+str_replace(provrates_x.code,' %',null)+
-- 'i'+convert(varchar,provrates_x.in ter)+
-- 'j'+convert(varchar,provrates_x.pe akid)+']-'
from (select
ipaddress,id_prov,code,i nter,peakid,termcode,prefix,postfix
FROM provrates_x WHERE
(intcode=convert(int,left(@tempcode,4)) or intcode=1) and
patindex(code, @tempcode) > 0 and provrates_x.inter <
> What can I do? I ported my app to SYBASE and It crashes
Cory Sane [TeamSybase]
Neal Stack is a Sybase Employee and Guru ... His previous posts shows that he is willing to help.
You are in Good hands.
Cory
--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0
"F. Alves" wrote in message news:49e4071b.166...@sybase.com...
ThanksButNo
> It crashes once per hour.
>
> kernel Current process (0x1460146) infected with signal 11
> (SIGSEGV)
I've seen that "infected with signal 11" before back in 11.9.2.
Probably the lamest error message ever thought up since the
"good old" IBM days of "ABEND".
It means you could have a bug in the dataserver (which
Neal is checking out for you and BOY ARE YOU LUCKY to get
that kind of powerhouse help WITHOUT PAYING A NICKEL.)
Or it could mean you have a hardware problem, such as a
flaky chunk of memory. If you have any hardware analysis
tools available, you should try them.
OR;
> kernel Address 0x0x00000000009eb85e
> (TabStatGetSysTab(GttiTableOrIndex*, int)+0x1e), siginfo
> (code, address) = (1, 0x0x0000000000000008)
> kernel ************************************
> kernel SQL causing error : der.id_prov <> @self_provider
> --and provider.ipaddress like '%.%.%. %'
> order by len(provrates_x.code) desc,
> inter asc
> kernel Current Procedure: XRoutingX
You could have a bug in this stored procedure, or it might have
become corrupted somehow. Check the code for this particular
stored procedure and see if you can spot anything at or around
the SQL shown in the error log. Try dropping and recreating
it.
Try running without the stored procedure entirely. If the
problem is a bug there, then the dataserver should run fine
without it, and then you'll know.
What the "infected with signal 11" means essentially is that
the dataserver spotted a problem that it didn't know how to
handle. If the problem is in the client, it kills the client.
If the problem is in the server, it commits suicide. That
protects the system from becoming even further corrupted by
whatever the problem is.
Thankfully, that issue occurs *rarely*. Indeed, the last time
I saw it was probably a decade or so ago, when it merely
killed off the client and the server kept humming along.
Other than that, all you can do is wait for wiser minds to
contribute.
\:-\
Neal Stack [Sybase]
You provided two core files for the 'bcp' utility and one for the 'dataserver' issue.
So it appears that you may be running into two issues.
1) For the ASE issue, the core file seems too small so it may have been truncated by Linux.
Please ensure that the "limit coredumpsize" is set to unlimited before starting ASE.
I have not been able to find any known issues that appear similar to the infected with
signal 11 issues in TabStatGetSysTab().
I am a little concerned with the lasterror=313 that comes with the signal 11. It may
or may not be related but you may want to look into resolving it:
http://infocenter.sybase.com/help/topic/com.sybase.dc00729_1500/html/errMessageAdvRes/BABHHGEH.htm
I will need some advanced diagnostics to further analyze these issues. Please follow
these steps to enable a shared memory dump on your server. It will create a large file
that will allow me to look at what was going on in the server at the time of the problem...
================================================================================
Enable the server to generate a shared memory dump on conditions:
1> sp_configure "dump on conditions", 1
2> go
Request a one-time memory dump on signal 11:
1> sp_shmdumpconfig "add", signal, 11,1,"/some_dump_dir"
2> go
Request a memory dump on error 313:
1> sp_shmdumpconfig "add", "error", 313, 1, "/some_dump_dir"
2> go
After creating the CSMDs, you can turn drop the condtions and turn off the feature:
1> sp_shmdumpconfig "drop", "error", 313
2> go
1> sp_shmdumpconfig "drop", signal, 11
2> go
1> sp_configure "dump on conditions", 0
2> go
================================================================================
The ASE log file will show where the dump files were created. Please compress
them and upload them to the ftp site I gave you earlier.
2) For the bcp issue, can you provide me with the complete syntax of the bcp command you issued?
If you used a bcp format file, can you provide that to me as well?
Regards,
Neal
Neal Stack [Sybase]
One more piece of information to gather if you can.
The functions TabStatGetSysTab() and GtGatherStats() have to do
with gathering histogram statistics.
If you could try running the procedure with the following "show"
options enabled, it might tell us which table & columns from the
query are contributing to the problem:
isql -Usa -P -o show_options.out -w 132
dbcc traceon(3604)
go
set option show_missing_stats on
go
set option show_histograms on
go
exec XRoutingXDebug
go
Regards,
Neal
F.Alves
as 250 million records, namely one per US phone number, and
Sybase froze all other connections, I guess while it was
doing IO to retrieve the data. The "real" client application
had to be restarted. The time it takes for the stored
procedure to run is 1/2 to 1 seconds, and it became over a
minute and a half. The question is: how do tell ASE that my
queries take absolute precedence over a bcp connection?
Pleased remember that all the "real" query does is fire the
same stored procedure over and over.
ThanksButNo
> I have another problem. I am trying to bcp out a table that
> as 250 million records, namely one per US phone number, and
> Sybase froze all other connections, I guess while it was
> doing IO to retrieve the data. The "real" client application
> had to be restarted. The time it takes for the stored
> procedure to run is 1/2 to 1 seconds, and it became over a
> minute and a half. The question is: how do tell ASE that my
> queries take absolute precedence over a bcp connection?
> Pleased remember that all the "real" query does is fire the
> same stored procedure over and over.
My first thought is to try using the "-b" parameter to limit
how many rows it grabs in a batch. The default is 1000.
A smaller batch size would give the server more frequent
opportunities to service other clients.
/:-/
Sherlock, Kevin [TeamSybase]
"ThanksButNo" <no.no....@gmail.com> wrote in message
news:542e57d7-227a-45b1...@z14g2000yqa.googlegroups.com...
-b only affects bcp in controling how often a transaction is committed. Bcp
out is a read-only operation so there are no transactions, and bcp
connections are scheduled and consume timeslices running through run-queues
just like any other connection (unless you use LPM).
F.Alves
this issue. My app is very sensititive to timing. Please
imagine yourself dialing a local call and waiting 6 seconds
for a firt ringback. I need to enforce some rules where the
routing application has higher priority than anything else
in the system. It can be the user that the app is using,
etc. Does such a thing exists in Sybase? One of the reasons
I want to move to a Linux database is the impredictability
of a windows database, without saying any product names.
Yours
Federico
> "ThanksButNo" <no.no....@gmail.com> wrote in message
> news:542e57d7-227a-45b1...@z14g2000yqa.goog
Manish Negandhi
> routing application has higher priority than anything else
> in the system. It can be the user that the app is using,
> etc. Does such a thing exists in Sybase?
You can use "execution classes" in Sybase to priorotize one task over
the other. If you think one or very few processes eating up lot of
server resources and you want to limit them to use certain number of
cpus, you can create engine group followed by execution class to
execute such processes with lower priority. Similarly you can create
another execution class to give higer priority to selected
applications or logins. Here is a link explaining how execution class
works in ASE
http://infocenter.sybase.com/help/topic/com.sybase.dc20020_1251/html/databases/X17316.htm
-HTH
Manish Negandhi
[TeamSybase]
Sherlock, Kevin [TeamSybase]
that "bcp -b" option is only valid for "bcp in" operations. I mentioned
Logical Process Manager which _might_ be of use to you.
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc20020.1502/html/basics/X17759.htm
There are other/additional options as well, such as creating named caches,
replicating to a "DSS" system to separate the load between critical OLTP and
decision reporting users, etc.
I'm confident that there is a solution to your problem. Take a good look at
the Performance and Tuning manuals and consider all of the various options
that you have as a DBA.
<F.Alves> wrote in message news:49e547e5.3a1...@sybase.com...
Chris N. Brown [Sybase, Inc.]
Another question, what version / flavor of Linux are you running?
-Chris