Hi all. I'm using SA12.0.1 on Linux. A module of our application execute scheduled and parallel (java multithread) queries (only SELECT statements ...) We've experience of progressive performance degradation passing from 5 parallel executions (of the same query) to 10 executions ... Some annotations: 1) Executing a single instance of the query al works fine ... 2) We've noticed performance degradation using a pool of different queries, too ... How can we tune the system (Sa12 and/or OS ...) to obtain best performance ? Thanks.

asked 01 Aug '16, 05:24

NCister's gravatar image

NCister
16191016
accept rate: 0%

edited 01 Aug '16, 05:26

Start by reading all about "performance" in the SQL Anywhere 12 documentation.

(01 Aug '16, 12:33) Breck Carter

Hi Breck and thank you for replay. We've already tried all performance "tips" explaned in the documentation: 1) page size setting 2) multiprogramming level setting 3) cache size tuning 4) hardware escalation 5) ... ... without success .. Thanks.

(01 Aug '16, 12:43) NCister
Replies hidden

Have you spent any time looking for performance bottlenecks, or have you concentrated on fiddling with switches?

If the latter, try setting everything back to the defaults and studying the performance statistics that SQL Anywhere makes available on a real-time basis.

There are a number of tools you can use for this: SQL Anywhere Monitor, Sybase Central Performance Monitor, DBConsole Utility, Windows Performance Monitor and Foxhound 3; you can see a comparison here. Foxhound 3 requires SQL Anywhere 16 to run, but it can handle target databases running on Version 12.

(01 Aug '16, 15:18) Breck Carter
Comment Text Removed

Hi Breck. I'm not working on potential bottlenecks because the query(ies), executed in a single "session", works fine ... while the same query(ies), executed in a multiple session environment (a tipical procuction scenario) become progressively slower. It seems to be a "stress" problem ... as if the (db)server was struggling to respond to multiple simultaneous requests.

(02 Aug '16, 04:18) NCister

Can you please tell us the server configuration?
The concurrency of your requests?
What does SA show in metrics: I/O consumption, CPU consumption, memory consumption, disk reads, cache hits... something like that.
If you explain the plan when the server is idle and under the load of the same query. Do you see any difference in costs?

(02 Aug '16, 04:53) Vlad
3

"I'm not working on potential bottlenecks"

Say what? Bottlenecks can (and often do) happen outside an individual query... but you know what you're doing, so I'm sure you'll be successful!

(02 Aug '16, 07:35) Breck Carter

You are (obviously ...) right, Breck. We are investigating about a couple of queries to decrease their executing time (... and relative CPU resources consuption when used concurrently ...). Thanks.

(05 Aug '16, 05:07) NCister
showing 2 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×31
×8

question asked: 01 Aug '16, 05:24

question was seen: 540 times

last updated: 05 Aug '16, 05:07