UPDATE

A further symptom that is reported after the working set of memory is reduced and before it returns to the original level, is that a frequent number of [Sybase]{ODBC Driver][SQL Anywhere]All threads are blocked errors are encountered. Once the memory usage has returned to the level allocated to cache in the start-up parameters these messages are not encountered again - until the next database re-start or minimization of the engine window.


v10.0.1.3931 but also reproducible in v12.0.1 Win XP & Server 2003 This is an issue reported by a client that I can reproduce locally.

When an open database engine window is minimized, the memory usage (as reported by task manager) drops from whatever value is was at to about 1MB and then rebuilds. During this time, performance seems to be effected, behaving in a similar way to when the database has been restarted and has not built up its cache.

Engine window open:

alt text

dbsrv10.exe - Mem Usage: 513,104K VMSize: 528,836K

a few seconds after the engine window was minimized:

alt text

dbsrv10.exe - Mem Usage: 5,976K VMSize: 528,836K

The VM size is unaffected, reflecting the cache settings of the service:

-n laptop10
-c 500M
-ca 0
-gp 4096
-x tcpip
-z
-md full

I know that Task Manager's memory usage reporting is by no means the whole story, but it seems very odd that minimizing the engine window should have such an effect (restoring the window does not seem to have any effect) and there does seem to be a real impact on users.

Does any one have thoughts?

asked 02 Nov '11, 08:45

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

edited 02 Nov '11, 13:35

1

I have no idea - but would suggest to test with SysInternals's Process Explorer and to check for working set sizes and the like...

(02 Nov '11, 08:55) Volker Barth
Replies hidden
1

v10.0.1.4075 does exactly the same thing, although I can't test if the performance is effected.

(02 Nov '11, 08:56) Daz Liquid

Type: Open - Minimized
Private Bytes: 528852 - 528852
Virtual Size: 1627724 - 1627724
Working Set: 513524 - 2452
WS Private: 509676 - 1944
WS Shareable: 3848 - 508
WS Shared: 644 - 280

sorry about the awful formatting - I can't get the hang of the markdown!

(02 Nov '11, 09:21) Justin Willey

From John's elaborate answer to this FAQ (highlighted by me:):

If you watch the working set (using perfmon, NOT task manager), you might notice that Windows will trim the server's working set size periodically. It will do so aggressively whenever you minimize the server's window for example. This behaviour is not problematic because those pages show up as "free" (ie the sum of "avail+working_set" stays the same) but, more importantly, they are not truly freed...

Funny thing: I've found this answer when searching for what memory statistics to look at - primarily I simply do know (like you, apparently) that Task Manager is not the tool of choice:)

permanent link

answered 02 Nov '11, 09:04

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 02 Nov '11, 09:11

If I read that link correctly, then John saying that this is expected behaviour, and not to worry about it, as the data in still in memory (assuming something else doesn't grab it) and the only penalty is a soft fault when first accessed.

It does seem to be having an effect in our case (at least in 10.0.1) as we appear to get a fall off in performance until the working set goes back up, it's had to be precise about this though.

One demonstrable effect is that during the "regrowing" phase, there is a time of about 5-10 minutes during which the server becomes unresponsive (user are not disconnected however). This happens after a server restart and after an engine window minimization.

(02 Nov '11, 09:38) Justin Willey
Replies hidden
2

From the "Memory / PageFaults/sec" counter description in perfmon: "Most processors can handle large numbers of soft faults without significant consequence". To regrow your working set from 5MB to 512MB, you will need to service about 132000 soft faults which doesn't sound like a lot to me. Certainly not 5-10 minutes worth. You might be able to monitor soft faults system-wide at least via "Memory / TransitionFaults/sec" though the documentation isn't clear and it includes a few other fault types. You could also compare "Process / PageFaults/sec" for the database server versus "Cache / Pages/sec" system-wide. The former includes all hard and soft faults for the the database server and the latter just the hard faults for the whole system. I don't see any more direct way of monitoring soft faults for a particular process.

(02 Nov '11, 09:59) John Smirnios

I don't think the soft faults could be the cause of the hiatus since it happens after a reboot as well as after the working set being trimmed.

It doesn't seem to kick in until the database gets busy - eg if you re-start the server during the evening, then the problem happens about half an hour after most users start in the morning. If you have to restart during the working day, it's about half an hour after the restart. During the hiatus, the MemUsage (measured in Task Manager) grows steadily but there is virtually no processor usage - normally about 40%.

(02 Nov '11, 10:13) Justin Willey
1

BTW, as 10.0.1 uses cache warming by default - do you know whether your database makes use (or could make use) of cached pages from the last start?

(02 Nov '11, 11:08) Volker Barth
1

Is running the engine as a service an alternative?

At least that might help you to get rid of the "minimizing the window" problem AFAIK...

(02 Nov '11, 11:10) Volker Barth
Replies hidden

It's on the default setting of ON. I had wondered about trying to turn it off.

(02 Nov '11, 13:15) Justin Willey

The example in question is in fact running as a service (but with the interact with desktop enabled). Now of course I can switch this off - which solves the immediate problem.

I raised the issue however, as what happens when the engine window is minimized is exactly the same as the behaviour when the database server starts - making me wonder if there really is something real happening.

(02 Nov '11, 13:23) Justin Willey
1

Well, I had formerly thought it would mean "cache reloading" (i.e. reload the cache contents from before the database was stopped) and then learnt that it just reloads the pages already cached by the last database start...

For older versions, I used to have particular queries such as "select avg(pk) from MyCentralTables)..." on startup to pre-load the cache with what I thought of "frequently used pages".

Possibly you can use similar queries to pre-load relevant pages? (From your postings, I would suspect the current pre-loaded cache is not that useful...)

(02 Nov '11, 13:38) Volker Barth

I think you are right. Really you want in cache is what was there when the database stopped last!!

(02 Nov '11, 13:51) Justin Willey

Cf. this thread called "Cache Hibernation" from the "product futures" NG...

(02 Nov '11, 17:40) Volker Barth
showing 2 of 10 show all flat view
Your answer
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:

×438
×275
×119
×26

question asked: 02 Nov '11, 08:45

question was seen: 3,617 times

last updated: 02 Nov '11, 17:40