SQL Anywhere database - Multi-CPU Server with ASA 9.0.2 latest EBF

Author

Multi-CPU Server with ASA 9.0.2 latest EBF

Michael Gould

2005-05-26, 9:23 am

Just a question and this isn't really version specific because I
see it happen with both ASA 8 and 9. We have customers that have
multiple CPU servers that are dedicated to running our database.
It appears that almost all of the transactions are being
processed by one processor with very little usage on the second
processor. Is this because the transactions are finishing so
quickly that processor 1 can handle most of the transactions? It
doesn't appear that any kind of load balancing is done when
transactions arrive to the ASA engine. 99% of all transactions
are online type of transactions.

Regards,

Michael Gould

John Smirnios

2005-05-26, 9:23 am

There can be several explanations for what you are seeing. The one you
have given is certainly one of them. Locking can also explain it: if
each connection updates the same row (for example a statistic value
'number of requests serviced' or allocating a unique ID via an update),
no more than one request will be able to run at a time so long as that
lock is held.

You also didn't explicitly say whether you are using the network server
(dbsrvX) or the standalone server (dbengX). The network server will only
use the number of CPUs for which you are licensed. The standalone server
is limited to using 1 CPU.

-john.
--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Michael Gould wrote:
> Just a question and this isn't really version specific because I see it
> happen with both ASA 8 and 9. We have customers that have
> multiple CPU servers that are dedicated to running our database.
> It appears that almost all of the transactions are being processed by
> one processor with very little usage on the second processor. Is this
> because the transactions are finishing so quickly that processor 1 can
> handle most of the transactions? It doesn't appear that any kind of load
> balancing is done when transactions arrive to the ASA engine. 99% of
> all transactions are online type of transactions.
>
>
> Regards,
>
> Michael Gould

Michael Gould

2005-05-26, 11:23 am

John,

This particular machine is licensed for 2 CPU's and uses dbsrv9.
All connections are via ODBC. We aren't having any locking
issues with the application. The application has 20 locations
logging into a Citrix server. The database server is a separate
server. Each table has a unique autogenerated SysID column.
Additionally, each of the locations has a separate location ID
so that also segments the data that they can receive back in a
resultset. I rarely see anything more than a schema lock when I
monitor sa_locks() except when a update is being done. We
serialize all updates. We aren't experiencing any performance
issues, it is just more of an observation that we don't seem to
be getting anywhere near a even distribution between processors.

Regards,

Michael Gould

John Smirnios wrote:

> There can be several explanations for what you are seeing. The one you
> have given is certainly one of them. Locking can also explain it: if
> each connection updates the same row (for example a statistic value
> 'number of requests serviced' or allocating a unique ID via an update),
> no more than one request will be able to run at a time so long as that
> lock is held.
>
> You also didn't explicitly say whether you are using the network server
> (dbsrvX) or the standalone server (dbengX). The network server will only
> use the number of CPUs for which you are licensed. The standalone server
> is limited to using 1 CPU.
>
> -john.

John Smirnios

2005-05-26, 8:23 pm

Well, it does seem a bit strange to me too. In my experience, with even
a single client rapid-firing a sequence of requests at a server the CPU
load tends to get spread across all CPUs. Spreading the load across
multiple CPUs is actually a bad thing if you aren't doing more than one
thing at once because it is better to use the CPU that may have some of
your code & data already in the on-board cache.

If you are licensed for two CPUs, you are using the server, you haven't
used any command-line switches to control concurrency in the server
(-gt, -gn, etc), you are confident that locks are not causing request
serialization and the load is still skewed I would be tempted to suspect
that it is just an artefact of your load & how the OS is doing the
scheduling. If you are concerned that the server may not be using all
processors, you could try executing concurrent cpu-intensive requests to
verify that all are used.

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Michael Gould wrote:[color=darkred
]
> John,
>
> This particular machine is licensed for 2 CPU's and uses dbsrv9. All
> connections are via ODBC. We aren't having any locking issues with the
> application. The application has 20 locations logging into a Citrix
> server. The database server is a separate server. Each table has a
> unique autogenerated SysID column. Additionally, each of the locations
> has a separate location ID
> so that also segments the data that they can receive back in a
> resultset. I rarely see anything more than a schema lock when I monitor
> sa_locks() except when a update is being done. We serialize all
> updates. We aren't experiencing any performance issues, it is just more
> of an observation that we don't seem to be getting anywhere near a even
> distribution between processors.
>
> Regards,
>
> Michael Gould
>
>
>
> John Smirnios wrote:
>

Cesare

2005-05-29, 7:23 am

"John Smirnios" < smirnios_at_sybase_d
ot_com> ha scritto nel messaggio
news:4295dd16$1@foru
ms-2-dub...
> You also didn't explicitly say whether you are using the network server
> (dbsrvX) or the standalone server (dbengX). The network server will only
> use the number of CPUs for which you are licensed. The standalone server
> is limited to using 1 CPU.

Question: how to get how many CPU-licenses is installed on Sybase product
already installed (there was installed from previous sys-adm).

The CPU-Licenses work that it use at least "n-1" (where "n" is the number of
CPU available on the server), if licensed? Or there are a configuration
parameter (like ASE, with "number of engine")?

Thanks.
Cesare

Sponsored Links