Customer wants to use sqlanywhere with 1000 connections.

Currently, he is using XP Server with 60 connections.

How does a server need to be configured ? or is a larger machine required ? How to size a machine for sqlanywhere with 1000 connections ?

Where can I get this kind of information and advice ?

asked 03 Jul '12, 21:45

ooops's gravatar image

ooops
91558
accept rate: 0%

edited 05 Jul '12, 10:24

Mark%20Culp's gravatar image

Mark Culp
22.6k9129265

2

1000 connections doing nothing will be easily served by the XP machine, so please describe in more detail the type of workload you expect for the average connection.

(04 Jul '12, 02:50) Martin
Replies hidden

And what SQL Anywhere version and data access APIs are you using?

(04 Jul '12, 03:18) Volker Barth

additionaly speaking,

SQL Anywhere version 12.0

Want to know a practical hardware sizing guide for sql anywhere with more than concurrent user 300 for voting system.

Maybe, transaction may be short and simple.

(04 Jul '12, 04:25) ooops

See Glenn's blog for the general response to this question.

To best determine your hardware information you have to create a performance test that mimics your production workload. This performance test should control as many different performance factors in the database server as possible, while modifying an individual server factor in a set of tests to establish its effects on your overall throughput. Once you have gathered this information (after varying things like the server cache size, number of worker threads, optimal levels of intra-query parallelism, etc.) you can then use statistical analysis to figure out which performance factors are the most important for your workload.

Once you have this information (e.g. How much server cache is required to get the desired throughput of your server for your workload? If you restrict the CPU to less cores, how much of an effect does this have on your results?, etc.), this can help translate into creating your hardware requirements.

For example, let's say increasing the server's cache size past 1GB doesn't have a large effect on your workload test results (maybe because your database is small and can easily fit into 1GB of memory). In this case, investing more money to buy additional RAM over 1GB in your production system would not make sense since it does not result in a performance benefit, and so you can instead invest that money into other hardware areas (a faster CPU, perhaps).

Unfortunately, you will only be able to determine this information accurately after completing the capacity planning tests described above - there is no "rule-of-thumb" that can accurately size all workload types from a rough description.

permanent link

answered 04 Jul '12, 11:58

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

4

One quibble: Just because a database file is only 1GB in size does NOT necessarily mean that 1GB of cache is enough. For example, suppose 100 busy connections each required 100MB of temp space... try running THAT on a SQL Anywhere engine with 1GB of RAM cache :)

The old rule of thumbs about cache size (10% of file size, no more than total file size, etcetera) no longer work with today's workloads and today's expectations of performance.

Today's rule of thumb is: You can never have too much RAM!

(04 Jul '12, 12:13) Breck Carter
Replies hidden
1

Thank you Breck for the quibble - I certainly agree! When originally writing the reply, I actually assumed in my head that the database was much smaller and that the "database plus workload requirements could fit into 1GB of RAM" - I definitely should have clarified in my response, so thank you for mentioning that.

I have my own quibble with "today's rule-of-thumb" though: while everyone may desire "the most possible amount" of RAM, you or your purchasing department for your organization cannot afford purchasing an unlimited amount of RAM for a system. When purchasing a new server, vendors usually have different upgrade options on the system all at different price points: clock speed/number of cores of CPU, size and speed of RAM, size and speed of hard-disks, etc. The question really is then to answer: which upgrades (if any) are going to result in the best throughput for your workload requests, within your purchasing budget?

Glenn's blog post really contains the quote for the "correct" rule-of-thumb here:

"There are no right answers, only tradeoffs." – William Cowan, Professor, University of Waterloo.

(09 Jul '12, 13:46) Jeff Albion
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×241
×10
×1

question asked: 03 Jul '12, 21:45

question was seen: 1,371 times

last updated: 09 Jul '12, 13:46