Database appearing to hang

[former_SQLA_member1694874]

Hi,

We have a number of databases that have started hanging and not allowing any new connections or new SQL to be ran on existing connections, sometimes for a few minutes and sometimes for an hour or so. Usually they just spring back into life and continue working.

SQL Anywhere version 10.0.1.3960 (There's a bug somewhere in the later versions of 10 that caused us some other issues so have been on 3960 for a long time now)

We've tried

• Using request logging to see if it's a piece of SQL that is causing it but there's nothing obvious in there and if we rerun them after the server doesn't hang.
• Application Profiling which doesn't show anything we could see as useful for this
• DBUnload the database into a new one
• These server settings -gc 1 -gr 1 -gn 25
• Breck's max query tasks (last/first resort) option

Does anyone have an suggestions for diagnosing the issue or even settings we can try to work around it ?

Thanks Daz.

[VolkerBarth]

Does that appear when a checkpoint is done? IMHO during a checkpoint the database may appear "hanging", although the large timespan you describe would be strange for a checkpoint. Nevertheless, I guess the forum had some questions on checkpoints and unresponsive essay...

[Breck_Carter]

Try removing the -gc 1 -gr 1... unless you are absolutely certain you know better than SQL Anywhere 10 how often a checkpoint should occur (hint: if so, you'll be the first 🙂

Then, try Foxhound.

[former_SQLA_member1694874]

We just added those to see if they would work 🙂 More than happy to try foxhound, was looking at it again today to see if it could help but we weren’t sure if it could with us not being able to find anything in the request logs. Do you think it can ?

[VolkerBarth]

AFAIK a checkpoint is not recorded in the RLL, as it is not a request... But during a checkpoint there's a phase where all current requests are suspended, which might be the phenomenon you are observing...

[former_SQLA_member1694874]

I'll have a look and see what happens next time it hangs and if the database starts a checkpoint beforehand, assuming it does what could cause a checkpoint to take such a long time ?

[thomas_duemesnil]

Can it be that the all database pages are full and the database engine is extending the dbspaces ?

[Breck_Carter]

There is no guarantee that Foxhound will show you where the problem is... there is a very real possibility that Foxhound itself will be shut out during the hour-long period of unresponsiveness.

However, something might show up before the unresponsiveness starts.

Request logs are sometimes useful for debugging, but almost never for performance tuning.

What are the cache settings?

[Breck_Carter]

> the phenomenon you are observing

...which is an hour-long period of unresponsiveness?

[VolkerBarth]

You certainly are using a transaction log for these databases, right?

[former_SQLA_member1694874]

Sure am, checked dbconsole at the time of the hang too and it hadn't started a checkpoint (or at least hadn't added the checkpoint started msg).

[Breck_Carter]

Two "server unresponsive" bugs have been fixed since 10.0.1.3960...

    ================(Build #4128  - Engineering Case #642524)================
The server could have become unresponsive when processing index scans in 
which a residual predicate continually rejected candidate rows for the duration 
of the scan.  This has been fixed.

================(Build #4089  - Engineering Case #623779)================

Servers running databases with large schemas may experience periods of unresponsiveness 
at idle checkpoint time. The performance of checkpoints has been improved 
to reduce the length of this interval.

[former_SQLA_member1694874]

It can be that long yes, sometimes only 10mins.

[former_SQLA_member1694874]

Installed foxhound and you're right it gets #1 when the database becomes unresponsive. I can't see anything obvious beforehand in foxhound, any tips for what we should be looking at ?

Cache settings -c 43P -ch 43P

[VolkerBarth]

You might have a look at the console.log file itself. If the database becomes unresponsive, dbconsole is usually affected, as well, whereas the file output to the console log should still happen, and the start and end of a checkpoint are always logged there, so that should tell you if a checkpoint is the cause of the delay...

[former_SQLA_member1694874]

Switched that on too and will have a look, thanks.