Our users are using applications that each, on a per app basis, create a connection to the database. There is some concern in my company that many connections (even unused ones) may create a drain on the resources of the server, and the thought is to limit the amount of connections one user may have.

I pushed back on this, from the POV that I don't want to write a whole bunch of code to solve a possible performance issue without having a real performance issue, but I got pushed back on that in return.

The question that was asked is: what is the real cost of a connection (one that has no locks, no open cursors, no nothing, just a connection)

I don't know the answer. My guess is all a connection is is a pointer to some (very tiny amount of) memory. Beyond that, there are no clock cycles involved, or anything. Is that right? Or is there a cost and should we plan to minimize connections?

asked 15 Jan '13, 13:49

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%

2

Push back hard! SQL Anywhere is not one of those bloated horrors like Oracle where every idle connection is a waste of critical resources. Consider this: SQL Anywhere uses separate connections for each web page delivered via its builtin HTTP server... and for each separate image etcetera on that webpage if the image is served up by its own SQL Anywhere SERVICE... what that's saying is that even active connections are light weight.

But, back to your question: No, idle connections are not to be avoided. Many applications use huge connection pools where most connections remain idle for most of the time, and they aren't hurting (pools address the overhead of making and dropping connections for teeny tiny transactions)

(16 Jan '13, 09:50) Breck Carter

You are correct, the cost of a connection that is holding no locks and has no open statements or cursors is very small. The last time we measured it - which was many many years ago now - the cost was under 4K of memory per connection. I suspect the memory cost is slightly higher now but it would not be by much.

There is a small amount of CPU cost per connection as well: in order to keep the connections alive and to detect dead connections there is a "liveness packet" that is sent across each connection every 40 seconds or so (depending on your liveness timeout value - default is 120 seconds, and we send a packet fourthree times per timeout interval - FWIW: it use to be four times per interval). If you have thousands of idle connections then this can have an effect but the s/w automatically tunes itself to ensure that the overhead is kept to a reasonable amount (it does this by automatically adjusting how often a liveness packet is sent).

The other thing to consider is the licensing of your server. If you are using a per seat license then each connection to the server may be using one of the seat licenses. If you are licenced per CPU then this is not an issue.

permanent link

answered 15 Jan '13, 14:05

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

edited 15 Jan '13, 14:23

Thank you!

(15 Jan '13, 15:08) RobertDD
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:

×133

question asked: 15 Jan '13, 13:49

question was seen: 462 times

last updated: 16 Jan '13, 09:50