We are using SQLAnywhere 10.0.1.4295, hosted on Windows 2003R2/64. The database file is approximately 42GB in size. Early yesterday morning, the DB server slowed to a crawl and became unusable. I could not connect with iSQL; 15 minutes after entering login credentials, I still had no screen. At the server console, I tried to shut the database down. I immediately got the message "shutdown initiated", but 90 minutes later the database as still not shut down. I became impatient, and terminated the dbsrv10 task.
In restart, the server announced it was doing a database recovery (no surprise). It did that for 8 hours, and during that time the size of the db file grew to 70GB. At that point, I stopped the task, restored a backup of the database, and restarted.
My questions are:
Any insight appreciated
asked 28 May '14, 09:51
The excessive login time could be caused by all the worker threads being tied up, make sure you have an appropriate number of worker threads for your environment (-gn). In 12+ we can automatically increase the number of workers as needed. How many logical processors is the database running on? There are several problems related to parallel queries that are fixed in newer versions but aren't available for the 10 branch. After the server is running for sometime, check
If you would like more diagnostic information regarding what the database to doing during slowdowns like this, you could try implementing minimal request level logging or database tracing to record the statements being executed. This may allow you to pinpoint the query that is making the server do so much work.
What stage of recovery was the database stuck in? If it was the rollback stage, then a transaction (probably the one that was causing the slow down) had a large number of uncommitted transactions that needed to be rolled back. This would explain the large size of the database file and the very long recovery time. If it was in the applying transactions phase you should check the translation log with dbtran to see what it was trying to apply during recovery.
answered 28 May '14, 10:51