Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

update stats, engines, worker processes

2 posts in General Discussion Last posting was on 2009-07-24 15:52:08.0Z
vtpcnk Posted on 2009-07-22 10:26:20.0Z
Sender: 4ce9.4a66e8f1.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: update stats, engines, worker processes
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a66e94c.4d27.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 22 Jul 2009 03:26:20 -0700
X-Trace: forums-3-dub.sybase.com 1248258380 10.22.241.188 (22 Jul 2009 03:26:20 -0700)
X-Original-Trace: 22 Jul 2009 03:26:20 -0700, forums-3-dub.sybase.com
Lines: 11
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28021
Article PK: 77268

at a point in time how many update stats processes can a
dataserver handle effectively?

as many as the number of engines?

as many as the number of worker processes?

as many as the number of worker processes/max parallel
degree?

appreciate the feedback.


Bret Halford [Sybase] Posted on 2009-07-24 15:52:08.0Z
From: "Bret Halford [Sybase]" <bret@sybase.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: update stats, engines, worker processes
References: <4a66e94c.4d27.1681692777@sybase.com>
In-Reply-To: <4a66e94c.4d27.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a69d8a8$1@forums-3-dub.sybase.com>
Date: 24 Jul 2009 08:52:08 -0700
X-Trace: forums-3-dub.sybase.com 1248450728 10.22.241.152 (24 Jul 2009 08:52:08 -0700)
X-Original-Trace: 24 Jul 2009 08:52:08 -0700, vip152.sybase.com
Lines: 57
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28046
Article PK: 77293


vtpcnk wrote:
> at a point in time how many update stats processes can a
> dataserver handle effectively?
>
> as many as the number of engines?
>
> as many as the number of worker processes?
>
> as many as the number of worker processes/max parallel
> degree?
>
> appreciate the feedback.

It depends on several factors. As a general overview...

One is what you mean by "effectively". Do you want to optimize
on minimizing the time it takes to complete each update stats command?
Do you want to optimize on getting as much work done as possible over a
time period (i.e. keeping the engines 100% utilized)? The answer is
going to be smaller for the first criteria than the second.

Does effectiveness include a requirement that all the work be completed
within a time frame (say, start of business on Monday)? In this case,
it is certainly possible to have too many commands running.

A big factor in this puzzle would be whether the tables all fit into
cache and are already loaded into cache. In that case, no
physical i/o would need to be done, the processes doing
update stats would presumably just scream along using
their full time slices. Throughput won't greatly improve by having
more than one update stat command going per engine, but won't
degrade much either - though each individual command would take longer
to complete. The total time to complete would be similar whether you
ran the commands serially in one connection or in parallel using
separate connections. Using worker processes would probably degrade
performance due to the additional overhead.

If the cache isn't big enough to hold everything, then spids
will be encountering cache misses and having to do physical i/o
and yield the engine. Having additional spids with work to do
will increase throughput as they can now be scheduled on the engine
(until they need to yield for some reason). Worker processes will
probably improve performance. The smaller the cache hit rate, the
more benefit there will be from having more processes, up to the
point where they start interfering with each other (replacing pages in
cache that other spids needed and now have to read back into cache).

Running update stats with multiple consumers, or running multiple
update stats in parallel will improve the chances of there always
being some runnable process available when an engine is yielded.
However, the optimal number is going to depend on how often spids
will be yielding the engine due to needing to do physical i/o.


-bret