Limit memory slow dbsrv12

[Former Member]

Dear Alle,

I have many questions that need to be answered.

What is the memory limit of sql anywhere 32bits version 12 and 17?

What is the memory limit of sql anywhere 64 bit version 12 and 17?

We have a large customer here with a memory use of 3.6 gb at the moment with sql anywhere 12 32bits. but it is very slow. If users log out and use less memory, it's all right. can that be the problem?

HELP, What can we do? upgrading to differt version of sybase?

Thanksss Ruben

[Vlad]

Your questions are good, but unclear to me:
• What does it mean "slow"? The average/95%/maximum/minimum response time is above "what" limit?
• Are all queries slow, or only particular ones?
• Are there any statistics regarding CPU %, Memory consumption, Disk I/O (reads/writes), Swap usage?
• How many users are active (e.g. worker threads, active connections)?
• Is the system always slow or after X hours?

[VolkerBarth]

Note that 32 bit servers cannot handle caches > 2.7 GB unless an AWE cache ist used on Windows.

[Former Member]

• What does it mean "slow"? The average/95%/maximum/minimum response time is above "what" limit?

from 08:00 till 16:30 is its slow,

closing screens, clicking of parts in or program, Average use is Slow. Normally very fast with smaller customers

• Are all queries slow, or only particular ones?

the same queries are fast bij everye customer with or programma. only this one is biggest one. Maybe they need more Memory for the database or differend version of sybase?

• Are there any statistics regarding CPU %, Memory consumption, Disk I/O (reads/writes), Swap usage?

CPU = Very low about 33% but some times a peak to 100%

Memory = total server 32 Gb but in use 10,8 Gb and the DBSRV12.exe *32 not higher than 3,6 gB

DISK I/O = 825 kb/sec - 2 MB/sec but that is low.

• How many users are active (e.g. worker threads, active connections)?

140 active connections (120 on-primes users with a ODBC connections and 20 webservices connections)

• Is the system always slow or after X hours?

Its fast about 06:30 - 8:00 and 16:30 - 23:00 but than is every one coming to work.

[Vlad]

Thank you for the provided comment. I know that other participants might have a different opinion, but here is my "vision" 🙂 I hope it will not be very confusing.

High CPU spikes show that the DB server is currently busy with some long-running or heavy tasks that cannot be completed easily. E.g. instead of taking a value from an index (or cache), the server has to find this value either in the entire cache, or on the disk (e.g. table scans, or joins on calculated fields).

If your DB server (I forgot to ask regarding the number of cores) has few cores (4-8), but the number of active connections is above 100. Who knows, maybe you have lack of CPU resources, or you have to really optimize every single query. You can start with the analysis of the longest requests and check their execution plans.

You said that when the user close the screen, this will be slow during the peak times. Try to trace the query that is executed at this time and analyze it. CPU on DB shouldn't be 100%. Anything but not CPU.

You have said that I/O is quite low. 2 MB/s. I don't know what HDD you are using, but I think you are right - this is not high, meaning that even if you have a low cache size, your data is small enough, so the DB server can evict table pages from cache and read them from the disk if necessary. Usually, if you have to process much data, but your cache is not sufficient to have all required pages there, you should see high I/O activity and in this particular scenario this might the bottleneck.

Usually you should think that if CPU is high -> data processing; Memory is high (or even swaps) -> much data, increase memory, cache; I/O is high -> much data, slow HDD.

By the way, how large is your DB file? Is it possible for you to switch SA to 64-bit and use instead of 3.6 GB, e.g. 20 GB?

If my assumption is correct and this is not a "cache issue", I think this is a typical high-load case and you should start looking into the direction how to optimize queries.

[Former Member]

Thank you, the db file is now 19,6 GB.

[VolkerBarth]

Given the large RAM (that a 32 bit engine cannot handle) and the big database, I would recommend to simply try the 64 bit engine with an appropriate cache size. You should only have to adapt the server's start line then, no need to update the database files.

[reimer_pods]

I support that suggestion, that was my first idea while reading that thread. We've had some customers cases with noticeable performance improvement, when teh were moving from 32 to 64 bit.

[chris_keating]

This is documented in the limitations and depends on bitness and platform:

v12

v17