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.

CPU useage

4 posts in General Discussion Last posting was on 2009-03-23 16:21:26.0Z
Wilson Posted on 2009-03-17 14:43:10.0Z
Sender: 74df.49bfb6a3.1804289383@sybase.com
From: Wilson
Newsgroups: ianywhere.public.general
Subject: CPU useage
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49bfb6fe.74eb.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Mar 2009 06:43:10 -0800
X-Trace: forums-1-dub 1237300990 10.22.241.41 (17 Mar 2009 06:43:10 -0800)
X-Original-Trace: 17 Mar 2009 06:43:10 -0800, 10.22.241.41
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7409
Article PK: 5822

I have a SQL Anywhere 10 (with latest patches) on a PC with
Pentium 4, 2GB memory, and 300GB disk on XP Pro & SP3.

The database size has grown to 3.9GB after three months of
continues data storing work.

The dbsrv10.exe uses 1.60GB of memory in any given time and
go to 100% CPU when a SQL query is executed that bog down
the system. The tables are very simple and all the data are
numbers.

Is it normal?, should I have limitation on the database
growth based on resources.

I have a switch: -on 100k. Is it a reason for high CPU use.
The Current page size is 2MB. By rebuilding the whole
database with pagesize 4MB, would it improve the
performance?


Stephen Rice [Sybase] Posted on 2009-03-18 17:55:14.0Z
From: "Stephen Rice [Sybase]" <stevedotriceatsybasedotcom>
Newsgroups: ianywhere.public.general
References: <49bfb6fe.74eb.1681692777@sybase.com>
Subject: Re: CPU useage
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49c13582$1@forums-1-dub>
Date: 18 Mar 2009 09:55:14 -0800
X-Trace: forums-1-dub 1237398914 10.22.241.152 (18 Mar 2009 09:55:14 -0800)
X-Original-Trace: 18 Mar 2009 09:55:14 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7411
Article PK: 5824

First you probably want to move this conversation to
sybase.public.sqlanywhere.general since most of the sql anywhere gurus hanf
out there.

To get you started...it is impossible to tell if this is normal or not yet.
There are all sorts of normal reasons to use lots of cpu.

On the surface the version is good and the basic cache setting seems ok. We
have lots of customers using databases in this size range with no issues.

Have you been able to isolate which queries cause the cpu to spike? Does
this reproduce in a test environment? If it does reproduce please use
dbisql to capture a query plan with statistics and post it.

If you haven't been able to isolate the queries then we need to do that
first. Start by reviewing the manual section "SQL Anywhere Server - SQL
Usage > Monitoring and Improving Performance"
--
/steve
Stephen Rice, Sybase iAnywhere
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Docs : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<Wilson> wrote in message news:49bfb6fe.74eb.1681692777@sybase.com...
>I have a SQL Anywhere 10 (with latest patches) on a PC with
> Pentium 4, 2GB memory, and 300GB disk on XP Pro & SP3.
>
> The database size has grown to 3.9GB after three months of
> continues data storing work.
>
> The dbsrv10.exe uses 1.60GB of memory in any given time and
> go to 100% CPU when a SQL query is executed that bog down
> the system. The tables are very simple and all the data are
> numbers.
>
> Is it normal?, should I have limitation on the database
> growth based on resources.
>
> I have a switch: -on 100k. Is it a reason for high CPU use.
> The Current page size is 2MB. By rebuilding the whole
> database with pagesize 4MB, would it improve the
> performance?


Breck Carter [TeamSybase] Posted on 2009-03-18 19:44:14.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Re: CPU useage
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <ovh2s45573nt3lva1th8a5tjce6n3j58f0@4ax.com>
References: <49bfb6fe.74eb.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 18 Mar 2009 11:44:14 -0800
X-Trace: forums-1-dub 1237405454 10.22.241.152 (18 Mar 2009 11:44:14 -0800)
X-Original-Trace: 18 Mar 2009 11:44:14 -0800, vip152.sybase.com
Lines: 54
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7412
Article PK: 5827

In addition to what Stephen said:

2K is a funky page size, not the default, why did you pick it?
Generally 4K is the best choice in the absence of careful study and
comparison (I assume "2MB" and "4MB" are speling misteaks :)

Is the hard drive fragmented? That may not account for high CPU usage
but it is *often* crippling to performance. The engine should tell you
how many fragments there are, early in the console log.

Most likely, it's a query you already know about... or should. You can
try using the application profiling feature...

SQL Anywhere® Server - SQL Usage
Monitoring and Improving Performance
Advanced application profiling using diagnostic tracing

Or you can start the server with -zt and -zl and use the monitor in
Foxhound to watch for a connection running a rogue query; see

http://www.risingroad.com/foxhound/foxhound1_help/foxhound_monitor.html#connections

Then go dig out that query and run it in dbisql and get the graphical
plan with statistics (which we can help with). If you need more
information about doing that, just ask.

Breck

On 17 Mar 2009 06:43:10 -0800, Wilson wrote:

>I have a SQL Anywhere 10 (with latest patches) on a PC with
>Pentium 4, 2GB memory, and 300GB disk on XP Pro & SP3.
>
>The database size has grown to 3.9GB after three months of
>continues data storing work.
>
>The dbsrv10.exe uses 1.60GB of memory in any given time and
>go to 100% CPU when a SQL query is executed that bog down
>the system. The tables are very simple and all the data are
>numbers.
>
>Is it normal?, should I have limitation on the database
>growth based on resources.
>
>I have a switch: -on 100k. Is it a reason for high CPU use.
>The Current page size is 2MB. By rebuilding the whole
>database with pagesize 4MB, would it improve the
>performance?

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Wilson Posted on 2009-03-23 16:21:26.0Z
Sender: 2bd9.49c7b4e2.846930886@sybase.com
From: Wilson
Newsgroups: ianywhere.public.general
Subject: Re: CPU useage
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49c7b706.2c3d.1681692777@sybase.com>
References: <ovh2s45573nt3lva1th8a5tjce6n3j58f0@4ax.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Mar 2009 08:21:26 -0800
X-Trace: forums-1-dub 1237825286 10.22.241.41 (23 Mar 2009 08:21:26 -0800)
X-Original-Trace: 23 Mar 2009 08:21:26 -0800, 10.22.241.41
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7427
Article PK: 5841

Thank you for helpful tips.
Yes it is 2K. I will try it with 4K an let you know.

So basically limiting the database log file size using -on
100k does not impact anything.

> In addition to what Stephen said:
>
> 2K is a funky page size, not the default, why did you pick
> it? Generally 4K is the best choice in the absence of
> careful study and comparison (I assume "2MB" and "4MB" are
> speling misteaks :)
>
> Is the hard drive fragmented? That may not account for
> high CPU usage but it is *often* crippling to performance.
> The engine should tell you how many fragments there are,
> early in the console log.
>
> Most likely, it's a query you already know about... or
> should. You can try using the application profiling
> feature...
>
> SQL Anywhere® Server - SQL Usage
> Monitoring and Improving Performance
> Advanced application profiling using diagnostic
> tracing
>
> Or you can start the server with -zt and -zl and use the
> monitor in Foxhound to watch for a connection running a
> rogue query; see
>
>
http://www.risingroad.com/foxhound/foxhound1_help/foxhound_monitor.html#connections
>
> Then go dig out that query and run it in dbisql and get
> the graphical plan with statistics (which we can help
> with). If you need more information about doing that, just
> ask.
>
> Breck
>
> On 17 Mar 2009 06:43:10 -0800, Wilson wrote:
>
> >I have a SQL Anywhere 10 (with latest patches) on a PC
> with >Pentium 4, 2GB memory, and 300GB disk on XP Pro &
> SP3. >
> >The database size has grown to 3.9GB after three months
> of >continues data storing work.
> >
> >The dbsrv10.exe uses 1.60GB of memory in any given time
> and >go to 100% CPU when a SQL query is executed that bog
> down >the system. The tables are very simple and all the
> data are >numbers.
> >
> >Is it normal?, should I have limitation on the database
> >growth based on resources.
> >
> >I have a switch: -on 100k. Is it a reason for high CPU
> use. >The Current page size is 2MB. By rebuilding the
> whole >database with pagesize 4MB, would it improve the
> >performance?
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com