|
Hi everyone, We've been experiencing performance issues on our SQL Anywhere database for the past 2-3 months and have been trying to fix it. Just last week we made a switch to Server 2008 Enterprise R2 so that it's 64-bit and can have bigger cache sizes. One thing I've noticed is the statistic in Sybase Central named "remoteput wait," it's value climbs throughout the day to somewhere between 10,000 and 15,000 each day. I've noticed that our total buffers are between 60 and 80 and free buffers between 10 and 18. Since remoteput wait is basically telling us that it has to wait for available communication buffers I feel that this can be the reason or at least related to the slow performance speeds we've been seeing. What can be done to increase the amount of free buffers and decrease the remoteput wait? I haven't seen any change in those values since switching to 64-bit so I doubt it's related to the cache size. I only have read-access to the database and sybase central but I'm trying to get the developer to start thinking. I have no idea what lies in the architecture, all I can say is that from a system administrator's point of view, there is nothing on the server or network that should be causing our database to slow down. Hardware and software look fine and was built to handle 100 simultaneous users but we currently only have 50. I know that's probably not enough information to get a complete answer but I'm hoping to have someone share some theory regarding remoteput wait and point me in the right direction. |
|
The Sybase Central - Performance Monitor statistic named "Remoteput Wait" is actually the remoteput wait per second... yes, a bad name :) Historically speaking, it has NEVER appeared prominently in any discussion of SQL Anywhere performance, and it is not reported by the Foxhound performance monitor. I just had a look at a mostly-idle SQL Anywhere database, and the Remoteput Wait statistic is a staggering 14,276,019... perhaps someone from Engineering could offer an opinion. FWIW this database has NO performance problems at all. Anyway, you would be advised to take a fresh approach to the performance problem. There are some tools in the box (described in the Help; e.g., Performance monitoring and diagnostic tools), and there's Foxhound. |
|
The Remoteput Wait counter counts the number of times that the server had to wait to write data to a TCP/IP socket. This can happen when the network is unable to transmit the data to the client as fast as the server can generate it, or when the receiving end (i.e. the client) is not reading the data from the server fast enough. In either case a backlog is created in the network TCP/IP channel and the server must wait before it writes the next buffer of data to the socket. It is normal to see some waiting on the server to occur in cases that the server is streaming megabytes of data to the client in response to a query that returns a large result set (or a result set that contains a large amount of data. e.g. blobs). If your network is saturated or has a slow communication link somewhere then you may see faster increases in the remoteput waits counter and you should evaluate your network performance and/or the performance of your client computer (e.g. relative to the the server computer) and/or application. In your case a value of 10K-15K on a busy modern 64bit server is not extraordinary and I would consider it not likely the cause of any performance issues that you are seeing. I would take Breck's suggestion and take a step back and take a fresh approach by using some performance monitoring and diagnostic tools. Thanks for your answers. We have been monitoring the database with Sybase Central and Solarwinds SAM. We'r trying to get one of the developers to come out on site and might try to use Foxhound at that point to look deeper into the database. I've considered replacing the RAID drives which are SAS 15k with SSDs but the disk idle times have been great. We have the system (OS) drive on a RAID 1 and the database is on a RAID 10. One last question, is there a guideline or formula as to how many free pages you should have in respect to the database size?
(25 Mar '13, 17:33)
m0j0e311
Replies hidden
There is nothing even close to a guideline for free space because it depends on the database activity. For example, a database that is steadily growing might benefit from a huge amount of free space, whereas one that is updated heavily without growing in size might not need any. Generally, however, free disk space is not often the cause of, or solution to, performance problems. RAM is number one... you can never have too much RAM. Runaway queries tend to be high on the list... but all of this is guesswork without some hard facts about performance.
(25 Mar '13, 18:06)
Breck Carter
|
What version of SQL Anywhere are you using? Is the statistic you are quoting called "remoteput wait" or "remoteput wait/sec"?
We are running SQL Anywhere 12.0.1 and the statistic is called "Remoteput Wait"