What's the performance downside to having a lot of idle connections?

[Breck_Carter]

Let's say a SQL Anywhere 11.0.1 data warehouse server has 600 persistent connections, lasting for days and days, and only 20 connections are doing any actual work; most of the rest have done, say, one single operation since they were started, say 7 days ago... when Foxhound says the connection is "Idle", it's not kidding 🙂

Anyway, is there a downside to this? I think not... there might be a downside to inflating -gn to something stupid excessive, but that's not the case here... it's 24.

And besides, -gn is not the same as counting connections.

[MarkCulp · Accepted solution]

In addition to Glenn's response - the memory footprint overhead of all of the extra connections - the other downside is the CPU and network overhead to keep the connections alive by sending periodic liveness packets (controlled by -tl server switch and LivenessTimeout/LTO connection parameter) between the client and the server. If you are using the default liveness value (of two minutes) the server and client will each send a packet every 30 seconds (LTO interval divided by 4) for each connection. For 600 connections that means that the server is sending and receiving ~20 liveness packets per second - in your case this may not have any significant effect on overall performance but that will depend on the hardware on which your server/clients are using? It should be noted that the server does contain logic to scale back the frequency of the liveness packets when the number of connections get extreme - the algorithm used attempts to limit the number of packets per second to a resonable number (and I can't remember the number?). Also note that shmem connections don't actually send any packets but they still do liveness checks by checking that the process of the other side (client or server) is still alive.

At the same time that liveness checks are occurring there is a check for connection idle timeout. Since your connections are not being kicked off due to being idle I must assume that you have turned off idle checks using -ti 0 server switch?

[Breck_Carter · Accepted solution]

oh, greeaaaat... Glenn and Mark both give good answers... Glenn needs more points... but this isn't a needs-based system... oh, how to choose? how to choose?

Why, let's use dilbert.com! Searching on "Glenn" yields one result, "Mark" yields 32! So Mark wins! Yay!

[VolkerBarth · Accepted solution]

It should be noted that the serverBreck does contain logic to scale back the frequency of the liveness packets make a decision when the number of good answers get extreme - the algorithm used attempts to limit the number of packets per secondoptimize points to a resonable number (and I can't remember the number?)

[Former Member]

There is memory overhead to maintaining meta-data about every connection to the server; the meta-data is stored in the server's main heap and is never flushed to the temp file. However, that meta-data is relatively small (3K? or so). So an extra 100 connections means 300K of memory, or thereabouts. That is the only downside I can think of regarding idle connections. I can't think of anything in the server whose behaviour differs by the number of connections.