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.

Multi Ports Performance Problem

9 posts in General Discussion Last posting was on 2004-01-27 16:33:14.0Z
gdurniak Posted on 2004-01-22 17:32:01.0Z
Sender: 5b0e.401008d9.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Multi Ports Performance Problem
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40100911.5b16.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Jan 2004 09:32:01 -0800
X-Trace: forums-1-dub 1074792721 10.22.241.41 (22 Jan 2004 09:32:01 -0800)
X-Original-Trace: 22 Jan 2004 09:32:01 -0800, 10.22.241.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2371
Article PK: 4576

Sybase Adaptive Server Anywhere
Database Engine Version 7.0.4.2788
Windows 2000 Professional

I have a strange problem

We are running 5 databases on 5 ports, on one PC
7374
7375
7376
7377
7378

The client application queries 3, 4, or 5 of the databases,
and sums the results

Queries of 3 databases runs extremely fast (< 1 sec each),
e.g.
7376
7377
7378

But, Queries to the last 2 databases runs extremely slow (>
50 sec each), if there are 5 in a row, e.g.
7374
7375
7376
7377
7378

Is this a multi port problem?

searching the documentation, I have found only two
references, which I will be trying today:
1. to connect to multi database servers on the same machine,
the client application must specify all the ports it will
need to comunicate with, when it starts
2. on windows NT, you must use the -gx switch, to allow more
threads

all of the tables, and indexes have been reloaded

any suggestions would be appreciated

thanks


Robert Waywell Posted on 2004-01-22 17:57:46.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40100911.5b16.846930886@sybase.com>
Subject: Re: Multi Ports Performance Problem
Lines: 77
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <40100f1a$1@forums-1-dub>
Date: 22 Jan 2004 09:57:46 -0800
X-Trace: forums-1-dub 1074794266 172.31.142.236 (22 Jan 2004 09:57:46 -0800)
X-Original-Trace: 22 Jan 2004 09:57:46 -0800, rwaywell-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2372
Article PK: 4577

Why not run all of these databases on a single server?
To determine if this is somehow an OS issue with regards to the ports, what
happens if you switch the order of the databases?
If you only run the 2 slow databases, does the performance change?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

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

<gdurniak@aol.com> wrote in message
news:40100911.5b16.846930886@sybase.com...
> Sybase Adaptive Server Anywhere
> Database Engine Version 7.0.4.2788
> Windows 2000 Professional
>
> I have a strange problem
>
> We are running 5 databases on 5 ports, on one PC
> 7374
> 7375
> 7376
> 7377
> 7378
>
> The client application queries 3, 4, or 5 of the databases,
> and sums the results
>
> Queries of 3 databases runs extremely fast (< 1 sec each),
> e.g.
> 7376
> 7377
> 7378
>
> But, Queries to the last 2 databases runs extremely slow (>
> 50 sec each), if there are 5 in a row, e.g.
> 7374
> 7375
> 7376
> 7377
> 7378
>
> Is this a multi port problem?
>
> searching the documentation, I have found only two
> references, which I will be trying today:
> 1. to connect to multi database servers on the same machine,
> the client application must specify all the ports it will
> need to comunicate with, when it starts
> 2. on windows NT, you must use the -gx switch, to allow more
> threads
>
> all of the tables, and indexes have been reloaded
>
> any suggestions would be appreciated
>
> thanks


gdurniak Posted on 2004-01-22 19:36:51.0Z
Sender: 5cc5.40102513.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Re: Multi Ports Performance Problem
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40102653.5cde.846930886@sybase.com>
References: <40100911.5b16.846930886@sybase.com><40100f1a$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Jan 2004 11:36:51 -0800
X-Trace: forums-1-dub 1074800211 10.22.241.41 (22 Jan 2004 11:36:51 -0800)
X-Original-Trace: 22 Jan 2004 11:36:51 -0800, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2373
Article PK: 4578

all 5 databases are on 1 windows 2000 PC

if I query databases 3,4,5 the response is <1 sec each

if I query 1,2,3,4,5 then 4,5 are each >50 sec, for the SAME
query

each query finds ~20 rows, so they should be fast


Robert Waywell Posted on 2004-01-23 15:32:15.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40100911.5b16.846930886@sybase.com><40100f1a$1@forums-1-dub> <40102653.5cde.846930886@sybase.com>
Subject: Re: Multi Ports Performance Problem
Lines: 60
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <401140c9$1@forums-2-dub>
X-Original-Trace: 23 Jan 2004 07:42:01 -0800, rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 23 Jan 2004 07:16:09 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 23 Jan 2004 07:32:15 -0800
X-Trace: forums-1-dub 1074871935 10.22.108.75 (23 Jan 2004 07:32:15 -0800)
X-Original-Trace: 23 Jan 2004 07:32:15 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2378
Article PK: 4583

Yes they are on 1 machine, but to be using different ports you must be
running 5 different ASA servers on that single machine. Why not run all 5
databases on a single ASA server on that 1 machine?

The fact that the queries are fast when you query any 2 or 3 databases but
that the queries for a couple of databases are slow when you query all 5
suggests a resource limitation on the server machine as one possibility.

Have you monitored the CPU usage during this process?
How much cache do you currently have assigned to each database server?
How much memory is in the machine?
Are you seeing excessive disk swapping?

Another thing to look at is the query plans. Are you getting the same query
plans when the queries are running slowly as when they are running quickly?

How are you 'joining' or otherwise processing the multiple result sets?
Could this be a processing issue in the application rather than the
database?
- the Request Level Log output can be used to determine how long each
request to the database is taking
- we used to have a utility available for download that would help you
analyze the Request Level Log output for 7.x databases, as of 8.x we
included the functionality in system stored procedures in the database
(sa_get_request_times and sa_get_request_profile)
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

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

<gdurniak@aol.com> wrote in message
news:40102653.5cde.846930886@sybase.com...
> all 5 databases are on 1 windows 2000 PC
>
> if I query databases 3,4,5 the response is <1 sec each
>
> if I query 1,2,3,4,5 then 4,5 are each >50 sec, for the SAME
> query
>
> each query finds ~20 rows, so they should be fast


gdurniak Posted on 2004-01-23 18:13:07.0Z
Sender: 786a.4011639f.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Re: Multi Ports Performance Problem
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40116680.789a.846930886@sybase.com>
References: <40100911.5b16.846930886@sybase.com><40100f1a$1@forums-1-dub> <40102653.5cde.846930886@sybase.com><401140c9$1@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 23 Jan 2004 10:22:56 -0800, 10.22.241.42
Lines: 26
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 23 Jan 2004 09:57:03 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 23 Jan 2004 10:13:07 -0800
X-Trace: forums-1-dub 1074881587 10.22.108.75 (23 Jan 2004 10:13:07 -0800)
X-Original-Trace: 23 Jan 2004 10:13:07 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2381
Article PK: 4586

yes, the 5 databases could be combined into one, and I am
considering that

cpu usage is low, ~10 to 75%
2G total memory
the two problem databases are the largest, and are assigned
a max cache of 650M
I/O bytes read is fairly high (mega bytes)

the plan does not change

the WHERE in the SELECT query refers to 5 indexed columns,
yet the plan refers to only 1 index

the plan says "estimate getting her 38655 times", yet it
only needs to find ~20 rows

is there a whitepaper that explains how to read a plan, in
detail?
what does "estimate getting here" really mean?

I am somewhat new at this

PS
I am also considering upgrading ASA, to get the better
troubleshooting tools


gdurniak Posted on 2004-01-25 00:41:57.0Z
Sender: 7635.401310ab.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Re: Multi Ports Performance Problem
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <401310d5.7638.846930886@sybase.com>
References: <40100911.5b16.846930886@sybase.com><40100f1a$1@forums-1-dub> <40102653.5cde.846930886@sybase.com><401140c9$1@forums-2-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Jan 2004 16:41:57 -0800
X-Trace: forums-1-dub 1074991317 10.22.241.41 (24 Jan 2004 16:41:57 -0800)
X-Original-Trace: 24 Jan 2004 16:41:57 -0800, 10.22.241.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2386
Article PK: 4590

this is not a port problem. Sorry for the confusion.

after a few hours of testing, I now see that the data is
loaded each week, approx. in order, by:

ID
Hour

the plan always shows one of these, either ID or Hour, and
ID is much faster (10x better)

so, I deleted the index on Hour, and the queries appear to
run better overall


Robert Waywell Posted on 2004-01-26 14:35:29.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40100911.5b16.846930886@sybase.com><40100f1a$1@forums-1-dub> <40102653.5cde.846930886@sybase.com><401140c9$1@forums-2-dub> <401310d5.7638.846930886@sybase.com>
Subject: Re: Multi Ports Performance Problem
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <401525b1@forums-1-dub>
Date: 26 Jan 2004 06:35:29 -0800
X-Trace: forums-1-dub 1075127729 172.31.142.236 (26 Jan 2004 06:35:29 -0800)
X-Original-Trace: 26 Jan 2004 06:35:29 -0800, rwaywell-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2391
Article PK: 4597

Does you query reference ID and Hour? Have you considered a combined index
on the ID and Hour columns together?

Keep in mind that ASA will only use 1 index per table, so if you have
indexes on ID and on Hour as single column indexes, then you would be
forcing the optimizer to choose one or the other. Based on what you just
saw, an index on ID,Hour might be a good choice.

By the way, in 9.0 we introduced an Index Consultant that can capture and
analyze a workload to recommend indexes to be either added or dropped.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

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

<gdurniak@aol.com> wrote in message
news:401310d5.7638.846930886@sybase.com...
> this is not a port problem. Sorry for the confusion.
>
> after a few hours of testing, I now see that the data is
> loaded each week, approx. in order, by:
>
> ID
> Hour
>
> the plan always shows one of these, either ID or Hour, and
> ID is much faster (10x better)
>
> so, I deleted the index on Hour, and the queries appear to
> run better overall


gdurniak Posted on 2004-01-27 13:24:09.0Z
Sender: 3a39.401665bc.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Re: Multi Ports Performance Problem
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <401668d2.3a45.846930886@sybase.com>
References: <40100911.5b16.846930886@sybase.com><40100f1a$1@forums-1-dub> <40102653.5cde.846930886@sybase.com><401140c9$1@forums-2-dub> <401310d5.7638.846930886@sybase.com><401525b1@forums-1-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 27 Jan 2004 05:34:10 -0800, 10.22.241.42
Lines: 11
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 27 Jan 2004 05:07:41 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 27 Jan 2004 05:24:09 -0800
X-Trace: forums-1-dub 1075209849 10.22.108.75 (27 Jan 2004 05:24:09 -0800)
X-Original-Trace: 27 Jan 2004 05:24:09 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2395
Article PK: 4600

yes, I have come to the same conclusion, I need a combined
index

when the database was first setup, I did not realize that
ASA uses only 1 index per query, so I created two separate
indexes.
I'm sure this is obvious to everyone here, but not so
obvious from reading the ASA documentation (it is in there,
but mentioned only in passing).

thank you for actually following up on my posts.


Breck Carter [TeamSybase] Posted on 2004-01-27 16:33:14.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Multi Ports Performance Problem
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <1m3d10d3b85510712uhsp917ij6kd97bvl@4ax.com>
References: <40100911.5b16.846930886@sybase.com><40100f1a$1@forums-1-dub> <40102653.5cde.846930886@sybase.com><401140c9$1@forums-2-dub> <401310d5.7638.846930886@sybase.com><401525b1@forums-1-dub> <401668d2.3a45.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 27 Jan 2004 08:33:14 -0800
X-Trace: forums-1-dub 1075221194 64.7.134.118 (27 Jan 2004 08:33:14 -0800)
X-Original-Trace: 27 Jan 2004 08:33:14 -0800, bcarter.sentex.ca
Lines: 23
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2397
Article PK: 4602

Not one index per query, but at most one index per *table*... so a
multi-table query may use more than one index.

Breck

On 27 Jan 2004 05:24:09 -0800, gdurniak@aol.com wrote:

>yes, I have come to the same conclusion, I need a combined
>index
>
>when the database was first setup, I did not realize that
>ASA uses only 1 index per query, so I created two separate
>indexes.
>I'm sure this is obvious to everyone here, but not so
>obvious from reading the ASA documentation (it is in there,
>but mentioned only in passing).
>
>thank you for actually following up on my posts.

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com