We are using SQL Anywhere v12.01.3810.

We have some large postings that run for about 4 hours (Day End type postings) that we schedule to run at Midnight each night. We also have a shift of workers that are doing transactional type work (small queries / update / insert statements) at the same time. The problem we have is that some of the postings are very intensive and are causing performance issues for the other workers.

What are your suggestions in this case?

I have tried setting priority of posting user to Low (SET OPTION "Admin"."priority" = 'Low';) and our transactional user to Critical. I am going to change priority of our "Admin" user to Background to see if that will make a difference.

Thanks for any suggestions.

Brian

asked 10 Jul '13, 10:00

bgreiman's gravatar image

bgreiman
385151624
accept rate: 20%

2

When you say performance issues, do you mean slowness, pauses etc rather than blocked connections, deadlocks or actual failures?

Does your "posting" user do everything in one big transaction or is it done bit by bit? Is there any deleting involved? Are the transactional users working on the same tables as the posting user?

Sorry for lots of questions rather than answers :)

(10 Jul '13, 10:23) Justin Willey
Replies hidden

Justin's questions are the right ones to ask. Some of them can be answered by running Foxhound.

(10 Jul '13, 10:52) Breck Carter

Pauses. The stored procedure calls triggered the "transactional" users are stalled out. I don't believe there are any blocking / deadlocks - but that is an angle I could pursue.

"Posting" user is running many steps with updates / inserts / deletes involved. Not typically using the same tables as the "posting user".

(10 Jul '13, 17:55) bgreiman

Here are some questions to ask about the overall server performance (other questions can be asked about individual connections)...

What is the cache size? What percentage of the maximum has it grown to? (from CurrentCacheSize and MaxCacheSize properties)

Is the cache big enough? (from the CacheHits, CachePanics, CacheRead and QueryLowMemoryStrategy properties)

What is the throughput? (requests per second, commits per second, bytes per second from Req, Commit, BytesReceived and BytesSent properties)

What is the latency? (response time to a simple query like select * from dummy)

What are the numbers of active request being processed and waiting requests? (from the ActiveReq, MultiProgrammingLevel, Threads and UnschReq properties)

How many locks are being held and connections blocked? (from the LockCount and BlockedOn properties)

How much CPU time is being used? (from the NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU properties)

How much temporary space is being used? (from the PageSize and TempFilePages properties)

Is the disk drive being pounded? (from the DiskRead, DiskWrite, LogWrite, IndAdd, IndLookup and FullCompare properties)

( FWIW Foxhound shows you all that stuff on a single page, continuously, 24 x 7... :)

(10 Jul '13, 20:16) Breck Carter
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

question asked: 10 Jul '13, 10:00

question was seen: 626 times

last updated: 10 Jul '13, 20:16