I hear the following questions at least once a week in some form from sales, support and customers:
“What changes should I make to my database to make it faster?”
“What should the specs be on my server machine that will run SQL Anywhere?”
“My application is slow. How can I make it faster?”
The answer to all of these questions is:
No really, the answers to all of these questions depends on things like:
- Number of users
- Size of database
- Database architecture
- Application design
- Expected usage pattern
- Identification of existing bottlenecks
There is no magic bullet, no magic “go fast” option for the database server. SQL Anywhere does a fantastic job of utilizing whatever resources are available to their full extent, dynamically adjusting as things (eg. machine workload, database workload, etc…) change. However, if you want to improve performance, you are going to have to put in some time analyzing and determining what changes should be made to get you the biggest bang for your buck. If you don’t, you risk
spending wasting money on things that may have little or no impact on performance.
The above information, formatted to fit the situation, is my stock response every time the performance question comes in. Inevitably, I get the follow-up question which boils down to “Please give us some something.”
I understand why these questions come in. SQL Anywhere supports a wide variety of operating environments and has relatively low minimum requirements. In turn, our customers, who embed SQL Anywhere in their solutions, have to support a variety of end-user environments. They need to provide some sort of initial guidance to their end customers to ensure a positive experience with their applications.
Because every situation is different, it is impossible to give a well-defined “if X then Y” solution to guaranteeing good performance. However, I have used the following high-level very loose rules of thumb, which don’t really help at all IMO, but sometimes seem to satisfy people.
- More RAM never hurts.
- Faster/more disks never hurts
- Faster/more processors never hurts
Basically, if you want to go out and spend money on new hardware, you will not likely decrease performance. However, we can’t guarantee that it will increase performance by a specific amount either.
There is a second level of very loose rules of thumb that can be used in scenarios where people want/need guidance on where to focus their efforts/expenditures. These only apply if at least some effort has been put into identifying where bottlenecks are (or where they are likely to be). The more effort put in to understand performance characteristics, the better your chances are of making good decisions:
- If the database server I/O is heavy (ie. lots of write activity), spending more on disks is probably a good idea
- If the disk is ‘thrashing’ a lot, more RAM and more/faster disks is probably a good idea.
- If the CPU is pegged at or near 100%, more faster CPUs/cores is probably a good idea.
- If disk activity is light and CPU usage is low, network issues or blocking/deadlock issues are likely causing the performance problems. In both cases, improving application or database architecture is a good idea, and more/faster RAM/CPU/Disk will not likely help much.