|
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
Does anyone have an suggestions for diagnosing the issue or even settings we can try to work around it ? Thanks Daz. |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 27 Feb '18, 10:58
question was seen: 1,883 times
last updated: 02 Mar '18, 10:45
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...
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.
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 ?
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...
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 ?
Can it be that the all database pages are full and the database engine is extending the dbspaces ?
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?
> the phenomenon you are observing
...which is an hour-long period of unresponsiveness?
You certainly are using a transaction log for these databases, right?
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).
Two "server unresponsive" bugs have been fixed since 10.0.1.3960...
It can be that long yes, sometimes only 10mins.
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
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...
Switched that on too and will have a look, thanks.