LGWR与AIX上的进程优先级

用户的事务及提交非常频繁，大约每秒要执行20000个事务，每个事务都需要独立提交。
在压力测试情况下，用户发现最终的数据库瓶颈出现在LGWR上。

最显著的等待是LOG FILE SYNC，最终发现问题出在LGWR进程，由于该进程活动过于频繁，该进程在系统上的nice值被调至68，AIX上缺省进程nice值为60，nice值越高表示优先级越低。

经过renice之后，系统性能恢复正常。

问题在于能否将进程nice值固定，据说询问了IBM工程师，说没有办法。

我搜索了一下，找到了解决方案，记录一下。
根据IBM的文章AIX 5 performance series: CPU monitoring and tuning，我们可以通过setpri调用来固定进程的优先级：


Using the setpri() and thread_setsched() subroutines

There are now two system calls that allow users to make individual processes or threads to be scheduled with fixed priority. The setpri() system call is process-oriented and thread_setsched() is thread-oriented. Use caution when calling these two subroutines, since improper use might cause the system to hang.

An application that runs under the root user ID can invoke the setpri() subroutine to set its own priority or the priority of another process. The target process is scheduled using the SCHED_RR scheduling policy with a fixed priority. The change is applied to all the threads in the process. Note the following two examples:

retcode = setpri(0, 45);

Gives the calling process a fixed priority of 45.

retcode = setpri(1234, 35);

Gives the process with PID of 1234 a fixed priority of 35.

If the change is intended for a specific thread, the thread_setsched() subroutine can be used:

retcode = thread_setsched(thread_id,priority_value, scheduling_policy)


The parameter scheduling_policy can be one of the following: SCHED_OTHER, SCHED_FIFO, or SCHED_RR.

When SCHED_OTHER is specified as the scheduling policy, the second parameter (priority_value) is ignored.


由于没有AIX的环境，我没有测试，但是相信官方文档的说法应该没有问题。

Oracle建议在AIX上将Oracle进程优先级固定为40，Metalink上可以找到相关文档。