The third database engine - parallelism and prefetch are typically built at the application level, rather than using capabilities of the database engine.
Because my environment is data hungry/cache hostile, a lot of application time is spent performing synchronous reads.
In many cases, we calculate the system effect of an increased read load by using total database block physical read rate as a proxy for database cache eviction rate.
Although that works reasonably well for analyzing the performance and scalability of the system, lately I've been spending a lot of my time looking for a needle in a haystack, where the database environment as a whole seems to be doing fine, but one lonely process thread is falling behind in its task. This particular haystack is within a Power7 AIX 7.1 context.
I've got a theory for that particular case: I think that the particular task in question is extremely sensitive to the buffer cache eviction rate/hold time. I believe it has a fairly long average time between re-references of any given database block, and the working set has very little intersection with the working set of other activity in the database. A small decrease in cache hold time could disproportionately affect this process then, if its cache hit ratio is reduced and the number of physical reads to accomplish a given amount of logical work rises.
I can build a circumstantial case for that theory by tracking the read rate with iostat, and the logical progress of this task over time. If the efficiency of this task decreases after increases in read rate, recovering only after the read rate falls to previous levels, that will be a pretty good circumstantial case.
But what I'd really like to show is that this particular process is forced to do more physical reads for a given amount of logical work when the read rate increases. (Again - I'd rather use the cache eviction rate than physical read rate... someday I'll explain why those two measures are best suited to different types of analysis.)
So - what I want is the number of physical reads over time for a specific AIX process ID. AIX iostat will provide the system-wide read rate, and database level tools will provide the rate of database instructions processed by the thread and the rate of database logical reads.
The Workload Manager is just what the doctor ordered for this type of analysis.
In the next few days, hopefully we'll spin up a few full-blown perf and scalability tests - I want to validate that the observer overhead to enabling passive mode is indeed negligible or at least tolerable.
"Application requirements
for Workload Manager configuration" says same thing for AIX 5.3/6.1/7.1:
"Running WLM in passive
mode and refreshing WLM (always in passive mode) involves low risk, has low
overhead operation, and can be done safely on a production system without
disturbing normal system operation."
That sounds good and all - but I'm still gonna try to measure the observer overhead before I recommend enabling this on an already busy system for investigation purposes :).
Today I'll just play around in a low-risk dev environment.
# wlmcntrl -p
# wlmcntrl -q
WLM is running in passive mode
# ps -e -o pid,tdiskio,time | awk -u '$2>0{print $0};$2=TDISKIO{print $0}';
PID TDISKIO TIME
3014796 1680 00:38:41
5111972 8 00:01:48
6095048 36 00:00:03
6815962 4 00:00:29
7405584 16 00:02:18
9502812 288 00:15:53
9961530 460 13:07:03
10813516 16 00:05:07
11141204 48 00:05:14
11206742 8 00:05:12
11272280 9 00:10:28
11337818 1840 00:21:33
11665508 64 00:14:39
13107214 48 01:45:02
# ps -e -o pid,tdiskio,time | awk -u '$2>0{print $0};$2=TDISKIO{print $0}';
PID TDISKIO TIME
3014796 1928 00:38:41
5111972 8 00:01:48
6095048 36 00:00:03
6815962 4 00:00:29
7405584 16 00:02:18
9502812 288 00:15:53
9764950 8 00:00:00
9961530 464 13:07:04
10813516 16 00:05:07
11141204 48 00:05:14
11206742 8 00:05:12
11272280 13 00:10:28
11337818 2000 00:21:33
11665508 128 00:14:39
13107214 48 01:45:02
18940026 8 00:00:00
# exit
sasquatch v-mthome:/home/sasquatch> ps -e -o pid,tdiskio,time | awk -u '$2>0{print $0};$2=TDISKIO{print $0}';
PID TDISKIO TIME
3014796 2428 00:38:41
5111972 8 00:01:48
6095048 36 00:00:03
6815962 8 00:00:29
7405584 16 00:02:18
9502812 432 00:15:53
9961530 476 13:07:05
10813516 16 00:05:07
11141204 48 00:05:14
11206742 8 00:05:12
11272280 17 00:10:28
11337818 2848 00:21:33
11665508 192 00:14:39
13107214 72 01:45:02
21037130 8 00:00:00
Yep. This looks like its gonna be my shiny new toy. Pending validation of low observer overhead :)
No comments:
Post a Comment