Monday, November 11, 2013

IBMPower AIX Wokload Manager Passive Mode: My Shiny New Toy

I divide my attention across three database engines currently.  SQL Server and Oracle both have built-in parallelism for SQL queries, and both also have built-in prefetch/readahead that often serves to warm the cache ahead of query processing.

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