Oracle间歇性物理IO缓慢,hp-ux内存不足导致间歇性Oracle访问缓慢ssh主机无响应

客官°小女子只卖身不卖艺 2023-01-18 06:59 102阅读 0赞

下午客户反映管理后台无法登录，其它连接数据库的应用也反应缓慢。

ssh登录DB时等一两分钟才出输入密码提示，登录成功后系统反应迟钝，cpu使用率不高，但vhand进程cpu占用很高，swapinfo显示内存使用正常

$ /usr/sbin/swapinfo

Kb      Kb      Kb   PCT  START/      Kb

TYPE      AVAIL    USED    FREE  USED   LIMIT RESERVE  PRI  NAME

dev     8388608 3640072 4748536   43%       0       -    1  /dev/vg00/lvol2

reserve       - 4743460 -4743460

memory  15926556 4412752 11513804   28%

查询原因无果，查询过程中自动恢复正常，观察之。

第二天上午又重现昨天问题，10分钟后自行恢复。DB 16G内存，SGA分配了6G，SWAP 8G，起始怀疑SWAP太小引起的问题，联系HP工程师给了kmeminfo工具安装之，结果如下：

Physical memory usage summary (in page/byte/percent):

Physical memory       =  4186439   16.0g 100%

Free memory           =    15401   60.2m   0%

User processes        =  1577435    6.0g  38%  details with -user

System                =   832501    3.2g  20%

Kernel              =   832331    3.2g  20%  kernel text and data

Dynamic Arenas    =   466094    1.8g  11%  details with -arena

btree\_chunk\_are =    66250  258.8m   2%

vx\_global\_kmcac =    43267  169.0m   1%

spinlock\_arena  =    36163  141.3m   1%

reg\_fixed\_arena =    29941  117.0m   1%

FCACHE\_ARENA    =    26879  105.0m   1%

Other arenas    =   263594    1.0g   6%  details with -arena

Super page pool   =    32213  125.8m   1%  details with -kas

Static Tables     =   260179 1016.3m   6%  details with -static

pfdat           =   204415  798.5m   5%

vhpt            =    32768  128.0m   1%

text            =    11720   45.8m   0%  vmunix text section

bss             =     6844   26.7m   0%  vmunix bss section

data            =     2026    7.9m   0%  vmunix data section

Other tables    =     2404    9.4m   0%  details with -static

Buffer cache        =      170  680.0k   0%  details with -bufcache

UFC file mrg        =  1694779    6.5g  40%

HP工程师结论是物理内存占用完导致系统持续vhand，建议：

1、加大物理内存

2、改小SGA

3、调整UFC(默认物理内存5%~50%)

====================相关话题====================

客户反映一台32G的HP-UX经常swap，但，分给Oracle的SGA+PGA内存仅仅23个G，并且，该机器上没有跑其他的应用，理论上不应该导致交换。

glance了一下：

发现FileCache占用了8.3Gb的内存，导致了内存交换。

对于HP-UX 11i Version 3，可以修改初始化参数filecache\_max来控制FileCache占总内存的百分比

NAME

filecache\_max, filecache\_min — maximum or minimum amount of physical memory used for caching file I/O data

VALUES

Failsafe

filecache\_min: approximately 5% of physical memory

filecache\_max: approximately 5% of physical memory

Default

filecache\_min: approximately 5% of physical memory

filecache\_max: approximately 50% of physical memory

The defaults are computed and adjusted automatically, based on the amount physical memory on the system.

The current default value, when displayed by kctune, will be expressed as the current value used internally by the system (representing bytes). The displayed value will vary as the internal value is automatically adjusted.

Allowed values

Minimum filecache\_min: not less than the equivalent of 1 megabyte

Minimum filecache\_max: not less than the equivalent of 1 megabyte

Maximum filecache\_min: not greater than the equivalent of 70% of physical memory

Maximum filecache\_max: not greater than the equivalent of 90% of physical memory

See Restrictions on Changing below.

Values can be specified as:

1) Default

2) A percentage of total physical memory:

A positive whole number followed by a percent symbol (for example, 70%).

3) A constant value:

A positive whole number that represents number of bytes of physical memory, optionally followed by a multiplier suffix, where K=1000, KB=1024, M=1000000, MB=(1024\*1024), and GB=(1024\*1024\*1024).

Recommended values

It is recommended that these tunables are left in the automatic (default) state, to allow the system to better balance the memory usage among filesystem I/O-intensive processes and other types of processes.

DESCRIPTION

These tunables control the amount of physical memory that can be used for caching file data during file system I/O operations.

The amount of physical memory that is specified by the filecache\_min tunable is reserved and guaranteed to be available for file caching.

The amount of physical memory used for file caching can grow beyond filecache\_min, up to filecache\_max, depending on I/O load and competing requests for physical memory.

When these tunables are set to default, or set to a percent value, they automatically adjust with Online Addition or Deletion (OL\*) of physical memory, as appropriate.

Who Is Expected to Change These Tunables?

The automatic (default) state should be appropriate for most environments.

You must set these tunables to a constant value (not default or percent) if you want to specify file cache limits with finer granularity than percent of physical memory (for example, a minimum size or fixed size of <1% of physical memory).

You must set these tunables to a constant value (not default or percent) if you do not want the limits of the file cache to adjust with OL\* of physical memory.

To discriminate in favor of deterministic I/O on systems with large file I/O activity, or on the contrary, to discriminate in favor of better performance of non-I/O-intensive processes, you can consider changing the values of these tunables, keeping in mind the side effects as described below.

To determine a reasonable value for the cache size you should consider the file I/O-intensive applications on your system, and the size of their working set. Depending on the type of applications, the working set size can be based on the size of a transaction, or data size in given unit of time. For example, for a conservative value of filecache\_min in megabytes, you can use the following formula:

MB = number-of-system-processes \* largest-application-record-length-in-MB \* number-of-records-in-working-set

Only those processes that actively use disk I/O for file data should be included in the calculation. All others can be excluded. Here are some examples of what processes should be included in or excluded from the calculation.

Include:

NFS daemons, text formatters, database management applications, text editors, compilers, and so on, that access or use source and/or output files stored in one or more file systems mounted on the system.

Exclude:

X-display applications, hpterm, rlogin, login shells, system daemons, telnet or uucp connections, and so on. These processes use very little, if any, disk I/O for file data.

Restrictions on Changing

These tunables are dynamic and automatic.

The system rounds the specified tunable value(s) down to the closest physical page boundary.

The amount of physical memory represented by filecache\_min must be equal to or less than the memory represented by tunable filecache\_max.

Setting these tunables to a constant value will de-couple them from OL\* events.

Tuning up of the filecache\_min parameter may fail if there is not enough free physical memory to satisfy the request.

These tunables, filecache\_min and filecache\_max, must both be set to a relative state (either default of percent state), or must both be set to a constant value. For example, the following are acceptable settings:

\# kctune filecache\_min=100MB filecache\_max=100MB

\# kctune filecache\_min=15% filecache\_max=65%

\# kctune filecache\_min=Default filecache\_max=65%

The following will result in an error:

\# kctune filecache\_min=10% filecache\_max=1GB

ERROR: The specified units for the values of filecache\_min and filecache must be consistent: both as relative (percentage or default) or both as constant value.

If filecache\_min is currently set to default, the following is acceptable:

\# kctune filecache\_max=Default

\# kctune filecache\_max=40%

But the following will result in an error:

\# kctune filecache\_max=1GB

See other restrictions in the Allowed values section above.

When Should the Value of These Tunables Be Raised?

Low system performance at initialization time and/or on a system with filesystem I/O-intensive processes may be an indication that the values of these tunables are too low. If there is a large number of processes actively and constantly using file data I/O, you should raise the value of filecache\_min for more deterministic I/O. In most cases, especially when the file data I/O is expected to peak only occasionally, it is recommended that the value of the maximum limit, filecache\_max, is raised instead.

What Are the Side Effects of Raising the Values?

The amount of memory reserved for the minimum file cache size, dictated by filecache\_min, cannot be used on the system for other purposes. Be careful not to raise this value so high that it can eventually cause memory pressure and overall system performance degradation.

When Should the Values of These Tunables Be Lowered?

The value of the minimum limit, filecache\_min, can be lowered to allow a larger percentage of memory to be used for purposes other than filesystem I/O caching, depending on competing requests. By lowering the value of filecache\_max, a larger amount of memory is available for other purposes, without competing with file I/O requests.

What Are the Side Effects of Lowering the Values?

If there are many competing requests for physical memory, and the file cache tunables are set to too low a value, very high demand on file I/O operations can eventually cause filesystem I/O performance degradation.

EXAMPLES

Set the file cache minimum to 10% of physical memory:

\# kctune filecache\_min=10%

Set a fixed size file cache of 1 gigabyte:

\# kctune filecache\_min=1GB filecache\_max=1GB

Set the file cache minimum to 15% of physical memory, and the maximum to 65% of the physical memory:

\# kctune filecache\_min=15% filecache\_max=65%

WARNINGS

All HP-UX kernel tunable parameters are release specific. These parameters may be removed or have their meaning changed in future releases of HP-UX.

Other tunable parameters related to sizing the buffer cache that existed in previous HP-UX releases are now obsolete. The tunables filecache\_min and filecache\_max should be used to set limits to the file cache. Note that, on any given system, the optimum values of these two new tunables are not necessarily equivalent to the optimum values of the obsolete tunable values in the older systems. You should first determine if the new default values yield acceptable performance on your system, before attempting to change the values of the new file cache tunables.

Installation of optional kernel software, from HP or other vendors, may cause changes to tunable parameter values. After installation, some tunable parameters may no longer be at the default or recommended values. For information about the effects of installation on tunable values, consult the documentation for the kernel software being installed. For information about optional kernel software that was factory installed on your system, see HP-UX Release Notes at

AUTHOR

filecache\_max and filecache\_min were developed by HP.