aix下 maxperm设置引起数据库性能问题
来源:互联网 发布:文都网络 编辑:程序博客网 时间:2024/06/05 20:52
当初在某个银行遇到在aix下数据库运行时间长了,数据库的性能就会慢慢变慢的现象,然后就需要重新启动数据库,这样反复的去做这个事情,客户也排查了很长时间没有解决。
我们在metalink上查询到一片文章,找到了原因,其实就是一些内核参数的问题,我把这篇文章共享给大家。
文档号:316533.1
标题:AIX: Database performance gets slower the longer the database is running
Oracle Server - Enterprise Edition - Version:
AIX5L Based Systems (64-bit)
AIX Based Systems (32-bit)
Bull Escala RL AIX (64-bit)
AIX 4.3 Based Systems (64-bit)
Oracle databases running on AIX based systems.
Symptoms
Database performance continues to get slower and slower the longer the database is left running. You may also notice a continuing increase in the amount of paging space usage the longer the database is left running. However, database performance returns to normal after rebooting the system, or shutting down and restarting the database.
The slowdown can appear as increased latch contention for system critical latches such as the "library cache" and "shared pool" latches.
Cause
It is likely that you have not tuned the AIX Virtual Memory Manager (VMM). The default values for the AIX VMM are generally not appropriate for use with relational databases. The default values for the AIX VMM will gradually allow up to 80% of physical memory to be used to buffer file I/O. Since Oracle is already buffering file I/O in the SGA, the same data is unnecessarily being buffered twice, and leaves only 20% of physical memory to run the Oracle database(s) and all other programs. This causes the majority of the Oracle database to be pushed out of physical memory to paging space, thus greatly impacting database performance.
The information in this article does not apply, or the impact will be much less, if you are using one or more of the following storage types for the database datafiles, because AIX does not buffer file I/O for these types:
Raw logical volumes, filesystems using the Concurrent I/O (CIO) option, filesystems using the Direct I/O (DIO) option
Note that there is no "built-in" support for CIO or DIO in Oracle Database 9iR2 (9.2.0) or lower, though you can force the use of CIO (JFS2) or DIO (JFS) with filesystem mount options.
Also note that tuning the AIX VMM is outside the scope of Oracle Support. If you need help with checking, setting, or tuning the AIX VMM beyond what is covered in this article, you must contact your AIX systems administrator and/or IBM Support for further assistance.
Solution
To check whether your system is using the untuned default values for the AIX VMM, run the command:
/usr/sbin/vmo -a
If you do not have the /usr/sbin/vmo file you will need to have your AIX systems administrator load the AIX fileset "bos.perf.tune". The vmo command will list out all of the VMM parameters and their current values. The parameters you want to examine are:
MINPERM%, MAXPERM%, and MAXCLIENT%
Here is an example of the vmo report:
# vmo –a
memory_frames = 1572864
pinnable_frames = 1431781
maxfree = 1088
minfree = 960
minperm% = 20
minperm = 294356
maxperm% = 80
maxperm = 1177427
strict_maxperm = 0
maxpin% = 80
maxpin = 1258292
maxclient% = 80
lrubucket = 131072
…
The untuned default settings are MINPERM%=20%, MAXPERM%=80%, and MAXCLIENT%=80%. There is no "correct" value for these parameters and only extensive testing will reveal the optimal values. The optimal value may be different for different databases on the same system, so keep this in mind when tuning the VMM and choose values which work best for all of the databases you will be running on the system. Use the following values as a starting point:
MINPERM% = 10-15%, MAXPERM% = 20-30%, MAXCLIENT% = MAXPERM%
To get a snapshot of how much physical memory is being used by AIX to buffer file I/O, run the command:
/usr/bin/svmon -G
The svmon command is part of the same AIX fileset "bos.perf.tune" that vmo belongs to. The last line of the svmon output should be "in use". Add the values for "in use / pers" and "in use / clnt". Now divide the sum by the value for "memory / size". For best database performance, this value should generally not be higher than 30% (0.30).
Here is an example of the svmon output:
# svmon -G
size inuse free pin virtual
memory 131072 129432 1640 11704 50091
pg space 262144 100913
work pers clnt lpage
pin 11704 0 0 0
in use 47062 76126 6244 0
In this example, (in use / pers) 76126 plus (in use / clnt) 6244 equals 82370. 82370 divided by (memory / size) 131072 equals 0.628 or approximately 63% of physical memory being used by AIX to buffer file I/O. This indicates the AIX VMM needs to be tuned to allow more physical memory to be used by Oracle and other processes, and less physical memory to be used to buffer file I/O.
Remember that although AIX associates this memory with the Oracle processes (because Oracle requested the file I/O), all of the memory used to buffer file I/O is completely allocated and controlled by AIX, not Oracle. If you need help checking, setting, or tuning the AIX VMM, contact your AIX systems administrator and/or IBM Support. You may also want to review the AIX "Performance Management Guide" by IBM linked in the References section below.
UPDATE: After this article was originally written, IBM has introduced a new VMM parameter which is also very helpful with this issue. The parameter is...
lru_file_repage
The default value is "1", but it is recommended to set this to "0". This setting hints to the VMM to only steal file pages (from the AIX file buffer cache) and leave the computational pages (from the SGA) alone.
This new lru_file_repage parameter is only available on AIX 5.2 ML04 or higher and AIX 5.3 ML01 or higher.
NOTE: If you are using an older AIX system which does not support the lru_file_repage parameter, then you must use the "legacy" settings above. If you are using a newer AIX system which does support the lru_file_repage parameter, then you should use the revised settings below instead.
As documented in the IBM "VMM Tuning Tip" referenced below, if you are running on AIX 5.2 ML04 or higher, or AIX 5.3 ML01 or higher, IBM is now recommending the following VMM settings for use with programs which need to protect computational memory (like Oracle):
strict_maxperm=0 (default)
strict_maxclient=1 (default)
lru_file_repage=0
maxperm%=90
minperm%=5 (physical RAM <32 GB)
minperm%=10 (physical RAM >32 GB but <64 GB)
minperm%=20 (physical RAM >64 GB)
v_pinshm=1
maxpin%=percent_of_real_memory
Where "percent_of_real_memory" = ( (size of SGA / size of physical memory) *100) + 3
and
Set Oracle database parameter LOCK_SGA to TRUE in the pfile/spfile
and
Sum of all SGAs on the system do not exceed approximately 60% of physical memory
- aix下 maxperm设置引起数据库性能问题
- AIX系统maxperm与maxperm%的关系分析
- AIX下oracle数据库归档模式设置
- 引起数据库性能问题的因素
- gc cr引起的数据库性能问题
- AIX 分页技术 - minperm、maxperm 和 lru_file_repage
- AIX下oracle数据库自动重启设置
- 循环中读取数据库、嵌套循环引起的性能问题
- Oracle优化01-引起数据库性能问题的因素
- 修改aix操作系统参数maxclient%和maxperm%的一点记录
- 常见引起性能的问题
- aix 下创建数据库用户
- iOS一个简单的设置圆角不引起性能问题的分类
- 数据库并发引起的问题
- AIX 下磁盘 I/O 性能分析
- AIX 下磁盘 I/O 性能分析
- 如何在AIX下安装设置tftpserver
- aix下设置自动启动、关闭oracle
- 某银行数据插入慢问题分析过程
- 深度学习助力前端开发:自动生成GUI图代码(附试用地址)
- Database Hangs: What to collect for support
- Python数字和表达式
- 理解c++语言的重载、覆盖和隐藏
- aix下 maxperm设置引起数据库性能问题
- Determining Oracle memory usage on AIX
- MIME type类型
- ABAP 7.4 新语法-内嵌生命和内表操作
- Diagnosing Oracle memory on HP using GLANCE
- 排序之----冒泡,直接插入,选择排序
- BZOJ 4276 [ONTAK2015]Bajtman i Okrągły Robin 费用流 线段树优化建图
- 商品展示
- 存储过程与执行计划缓存 优秀文章目录