SQL AnyWhere 16 working with only one core after a few minutes/hours

[Former Member]

Hi there,

I'm having an issue with Sybase SQL AnyWhere 16 with Windows Server 2012 Standard (x64) 8GB RAM, Xeon E3-1231, SSD Disc.

Since our application is running 64 bits, the DB is very fast at the beginning but after a few minutes or a few hours of usage (it dépends on Customer system...) the DB is becoming very slow.

What I can clearly see is that when it is slow the process "dbsrv16.exe" is only using one virtual core. If I restart the DB Service, it's workin fast for a few minutes again (working with all cores). And then again only with one core and very slow...

I tried to optimize with cache parameters. With 32 bits systems we only wrote "-c 45P" and it was ok, we never have had a performance issue.

With Cache parameters "-c 300m -ch 300m" it stays a little bit longer fast but it doesn't solve the problem.

If I put for example -c 1000m -ch 1000m it becomes slow after the first query (it works already with only one core).

I tried to use -gn -gnh parameters but nothing works fine.

Is it a bug of Sybase ? How can I fix that ?

The final result of this issue is that after a few time of usage, a query which takes normally 4-5 sec, needs 30 sec to be executed.

Thank you for your answer.

[Former Member]

Hi Ivan,

the problem has been solved?

[former_member188493]

How are you determining how many processors SQL Anywhere is using?

The following query will tell you how many processors the engine will use...

SELECT PROPERTY ( 'NumLogicalProcessorsUsed' );

The following query will show more details about the processors:

SELECT * FROM sa_cpu_topology( );

Are you concerned about performance of multiple connections, or one single connection? The following query will tell you how many processors MIGHT be used by one single connection using "intra-query parallelism"; zero means "all"

SELECT CONNECTION_PROPERTY ( 'max_query_tasks' )

Since you mention -gn, are you actually concerned about the number of threads and the multiprogramming level? (a different topic)

[Former Member]

Hi,

I see it in the task manager.

NumLogicalProcessorsUser returns 8

sa_cpu_topology returns:

max_query_tasks returns 0

Yes even with -gn I'm still concerned by the issue.

[former_member188493]

How exactly is Task Manager telling you how many processors are in use? Are you going by the eight "CPU Usage History" windows?

The NumLogicalProcessorsUsed property says that all 8 processors are being used by the server... if there is no work for them to do, Task Manager won't show anything in the "CPU Usage History" windows.

Your problem probably has NOTHING to do with a shortage of processors, or an unwillingness by SQL Anywhere to use the processors. It probably has some other explanation, like (possibly) all the connections are blocked by a long-running transaction.

Please answer the question "Are you concerned about performance of multiple connections, or one single connection?"

Do not assume there is a bug in SQL Anywhere, or focus on some unproven explanation (shortage of processors)... look at the connection activity. Use a monitor like Foxhound, or check out the Performance and Tuning article in the wiki.

[Former Member]

Here the example when I'm having the issue (10 minutes for the query) :

And when I restart the service the same query is taking 5 sec we can see in this monitor that the load is balanced through all cores.

Answer: Performance of multiple connections

I already have read everything I found and tried about 50 different configurations...

[former_member188493]

All you are showing is a symptom (slow execution, low CPU usage).

Have you used Application Profiling? Have you looked at what other connections are doing?

"50 different configurations" is a sign of desperation... if the application is badly behaved then no amount of fiddling with server options is going to help.

Did you actually read that article in the wiki? The statement that you "have read everything" sounds unlikely... if you had read everything, you would have a lot more information to share... Task Manager is not telling you anything helpful.

[former_member207653]

The latest build of SA 16 is 2127. Are you running this build?

Do you mean that queries are fast initially because executed by multiple cores/processors in parallel and then they become slow because they are executed by a core/processor?

Have you already compared query plans with statistics before and after the performance issue?

DocCommentXchange

[Former Member]

This message was moderated.