We are using SA 10 version 10.0.1.3805(linux) or 10.0.1.3807(Windows).
One of propose - aggregation data from different time resolution
(hours-day-month). One of our customer encounter with problem durring
aggregation process database hungs - not connection; dbstop cannot
stop; only kill dbsrv10 process will stoped database. After our
analyse we found out - aggregation was done from table D1 (have 70M
records) to table M1(have 65M records) ; aggregation result was 38M
record inserted to M1 table. Database hungs(stuck) on COMMIT statement
that comming after aggregation INSERT statement.
How can we check what is the problem,

Best Regards, Hanan Brener

You should probably show the SQL for this 'aggregation'.
[and maybe an estimate-only graphical plan for the statement(s)
involved]

A single query should not hang a server to this degree. There
is a possibility that one or more thread may become busy but
not the whole server. It is possible that the request in question
exceeds the physical capabilities of the computer the server
is running on but you will need to monitor that outside the
server.

It is likely the server is not hung but simply busy for the
current request. Other connections may actually get
serviced and you should check for that possibility as
well.

Your aggregation process does sound like it has the potential
of dominating some resources while being satisfied but
O/Ss involved have preemptive schedulers and thread
libraries so a complete hang should be rare. You should
check the console output (see the -o switch) to see if
it is not asserted or reporting a fatal error when this
occurs [and/or see the syslog facility].

There are a few more recent bug fixes related to compressed
columns, excessive demands on the temporary file, OpenClient V15,
diagnositic tracing at a high detail level, external environments, etc
so one of those may be a factor if you are using any of those
facilities or features; so trying one of the most recent ebfs may
help.

But if your implication is that this server is lightly loaded with
only a few connections active then one need to look into
the specifics of this process.

For such I would recommend working closely with technical
support.

"Hanan Brener" <hananbrener (AT) gmail (DOT) com> wrote

Thanks for answer.

This problem we have reproduced on our test environment just with 2
(two) big tables and aggregation stored procedure - prepared
aggregation insert statement and COMMIT. The -o log file was clean
without any ASSERT or errors messages. Only our test messages -
I. 10/27 16:53:44. Aggregation data from VC_STAT_DAY_2_7 result -
38790313 records
I. 10/27 16:53:44. Start Commit ...
'Start Commit..' is last message in -o file, this print is directly
before COMMIT statement. Aggregation insert have take around 3800
second.It looks like problems with COMMIT for this count of records.
Any problem messages in system events files(in /var/log directory)
didn't appears. Database server is started with follows parameters -
${SQLANY10}/bin32/dbsrv10 -n test_stc /opt/sybase/data/db/stc/
allot_stc -o /opt/allot/log/allot_stc.txt -on 5M -c 50P -ca 0 -qp -qw -
m -x "tcpip(ServerPort=50001)" -ud

Best Regards, Hanan Brener

Unfortunately the strings:
'Aggregation data from VC_STAT_DAY_2_7 result - 38790313 records'
and
'Start Commit ...'
are from message statements inside your stored procedure
and the information in this posting tells us little more that
your first one does.

First things first though:

Since you are running with the '-m'
you are turning your COMMIT into
a complete checkpoint and (quite
possibly) a transaction log file rename
and delete. You may be blocking on
some file system operation there. I would
try removing the -m switch to see if that
changes the behaviour.

While you are poking around, such a big
commit operation can take some time.
[I also suspect your message 'COMMIT'
is hiding a big set of permanent operations
quite possibly including massive operations
one temporary tables.] Normal commits
can take some time when millions of rows
are affected [even longer if those are randomly
scattered across the database]. What can
cost you even more time here is things that
happen at commits and ends of operations.
Those would include cleaning up temporary
tables, closing cursors, executing statement
level triggers, ... and that is just my starting
list of candidates.


Given you need to know which operations are
causing this activity, I would focus in on this
with either our SQL Debugger (in Sybase Central)
or the Procedure Profiler (also in Sybase Central).
Whoever designed those stored procedures should
be involved at this point.


"Hanan Brener" <hananbrener (AT) gmail (DOT) com> wrote

Commit or rollback of such huge transactions can take a long time even
without -m. Checkpoints cannot occur during a commit or rollback and,
amongst other things, that means the server cannot shutdown during a
commit. Also note that shutting down the server with an uncommitted
transaction in progress will disconnect the client(s) and rollback the
transaction(s) before the server shuts down.

Is it possible to commit periodically during the aggregation so that the
transaction is not so huge?

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Nick Elson [Sybase iAnywhere] wrote: