|
We are using v12.01.3810. We recently upgraded our storage that database resides on from a FusionIO 320GB MLC SSD card to a FusionIO 1650GB SSD card. Since that time we have been seeing many SQL Anywhere Monitor events "Database server unscheduled request threshold exceeded". We would get 1-2 per week prior and now maybe get 20-30 per week. Our application is also much less responsive as it appears that it is waiting for the database at random intervals (some queries and some updates/inserts/deletes). We have a 280 GB database with 56GB or cache runing on Windows Server 2008R2 64-bit. Looking for any direction that can be given to troubleshoot this issue. We are also talking to Fusion tech support. We are planning on moving back to the old card this weekend to see if that returns back to normal speed. But we will need the additional storage so we need to come up with a long-term solution. Any guidance is appreciated. Thanks!!! Brian |
|
I have seen past customer cases where certain hard drives are slower at reading/writing certain page sizes. For one particular customer in a pure I/O test outside of SQL Anywhere, we found that writing 4K sectors was 30% slower than a comparably sized drive by a different manufacturer. This performance decrease amounted to a lot of time taken up in general I/O tasks in the database server and could be proven in controlled performance testing scenario. While we were performing the 4K I/O test with the customer, we also increased in to 8K and discovered that writing 8K was actually faster on this "slower" drive than the comparable drive, and changing to an 8K page size for the database significantly improved drive access times when re-running the test, based solely on the drive access time characteristics. If you're already involved with your hard drive vendor, it may be beneficial to ask if they have any performance times to share, at different page sizes. Otherwise, running your own I/O test against both drives to see if there is anything "obvious" about the access times, may also be beneficial.
Diagnostic Tracing may also be of help to discover where the 'long' queries are happening that are consuming the time during your periods of slowness. If you are seeing lots of 'unscheduled request' notifications in SQL Anywhere 12, it could be the case that your queries are expected to be parallelized when there aren't a lot of worker threads available and the current automatic tuning level is currently set too low (since there was a period of 'slowness' where we reduced the number of workers). Either turning down the amount of automatic intra-query parallelism ( Turning on the -gns switch will also display the current multiprogramming level in the console log to help you determine if you are running low on worker threads. |
|
In addition to what Jeff said... When the UnschReq statistic goes over the threshold, it would be interesting to know what the values for ActiveReq, MultiProgrammingLevel and UnschReq all were before, during and after that point in time (Active Req, Max Req, Waiting Req in the screenshot below). Also, what else was happening; e.g., disk activity statistics, CPU usage, bytes in and out, that sort of stuff. You can use Foxhound to watch all of that on one screen.
|
