---------- Original Message ----------- From: Michael Ludwig <mil...@gmx.de> > How do you measure metadata volume? I mean, when looking at taskmgr or > ps/top/vmstat, how do you know that the memory consumption is due to > metadata and not buffers allocated for this or that? ------- End of Original Message -------
AFAIK, you guess. I was measuring it just by knowing what the current users (me) were doing, watching disk i/o, timing during query-prepare times: initial, subsequent, subsequent & different, subsequent & different but related to previously prepared, etc. to see what would cause RAM usage to increase (and stay increased), and what would lead to prepare-times going down. In a CS environment, with connection pools and user activities that don't all touch the same metadata (some just do some logging, some do minor clerk-level stuff, and some do full workflow stuff), some instances of FB would sit for hours (active, though) at a few dozen megs of RAM, others, once "initialized" for workflow the first time, would eat 300. The initial prepare time for queries touching workflow would often take 3-4 seconds, and there was a flurry of i/o right about then. After that, most workflow-related queries (even significantly different from the initial one) prepared quickly, on that particular connection/instance. "workflow" can here be read as "tables that are all related to each other through a web of cascading triggers." Napkin math, sorry. I could be entirely wrong about the underlying cause. But it does fit well with SS vs. CS [non-]shared-cache differences. I realize that a prepare may involve caching some data-pages as well, not just pure metadata, as FB tries to analyze the cardinality of joins, but it's at the very least not reading the data itself, just examining the pointer pages, index roots, etc. The reason for asking about the size of metadata in RAM, and the concept of "inline", is that a full metadata-only backup is only 33 megs. The amount of RAM seemingly eaten up the first time I prepared queries touching the workflow tables was in the range of 180-250 megs. That's significantly more than the pure definition of the metadata (all of it), and doesn't even need to actually get "all of it". The triggers/SPs/tables are interdependent, but not 100%. I also hide some of the dependencies via execute-statement (but only in procedures that are called once, at commit; most everything else would be exposed to the whole dependency web.) So a prepare on query X may require examining metadata for 30 tables, 150 triggers, etc. -- but not necessarily all of them, either. So ... that's expensive. Maybe there's an entirely unrelated cause for the symptom. Note: most people would not even remotely be affected by this. It seems to be the result of having 2600 procedures, 9800 triggers, 2800 tables, with interdependencies and per-object permissions. I don't want to scare anybody off just because I very occasionally get sub-optimal behavior in my one, abnormal, scenario. Follow-up: so far I'm happy with the SS performance. I don't miss the multiple cores, at least not for now. The single instance may sit at 800 megs of RAM, but at least our page file isn't growing and we're not thrashing on swap space. The response times are both lower and more consistent. (Before, it depended on which CS instance you got connected to, and whether or not it had everything cached that you needed. And yes, I did try tweaking connection-pool sizes, but there was no ideal size for this problem.) -Philip
