Informix 数据库维护指南v1.0 - 图文(6)

Informix数据库维护指南

onstat -g iof

IBM Informix Dynamic Server Version 11.70.UC4 -- On-Line -- Up 2 days 23:54:35 -- 160552 Kbytes

AIO global files:

gfd pathname bytes read page reads bytes write page writes io/s 3 rootdbs_chk01 13479936 6582 54761472 26739 378.2 op type count avg. time seeks 0 N/A reads 2619 0.0039 writes 14908 0.0024 kaio_reads 0 N/A kaio_writes 0 N/A

4 logdbs01_chk01 8192 4 8777728 4286 353.7 op type count avg. time seeks 0 N/A reads 4 0.0032 writes 2704 0.0028 kaio_reads 0 N/A kaio_writes 0 N/A

5 phydbs_chk01 8192 4 2048 1 501.3 op type count avg. time seeks 0 N/A reads 4 0.0021 writes 1 0.0017 kaio_reads 0 N/A kaio_writes 0 N/A

6 tempdbs01_chk01 2048 1 8192 4 94.0 op type count avg. time seeks 0 N/A reads 1 0.0055 writes 3 0.0124 kaio_reads 0 N/A kaio_writes 0 N/A

表访问情况信息

onstat -g ppf

IBM Informix Dynamic Server Version 11.70.UC4 -- On-Line -- Up 2 days 23:57:09 -- 160552 Kbytes

Partition profiles

partnum lkrqs lkwts dlks touts isrd iswrt isrwt i sdel bfrd bfwrt seqsc rhitratio 0x6 0 0 0 0 4 0 0 0 0 0 0 0 0x1f 0 0 0 0 19 0 0 0 0 0 0 0 0x21 0 0 0 0 3 0 0 0 0 0 0 0

北京数据中心

第26页 共 97页

Informix数据库维护指南

0x26 0 0 0 0 6480 0 0 0 0 0 0 0 0x28 0 0 0 0 57 0 0 0 0 0 0 0 0x29 0 0 0 0 702 0 0 0 0 0 0 0 0x2a 0 0 0 0 19 0 0 0 0 0 0 0 0x2f 0 0 0 0 37 0 0 0 0 0 0 0 0x57 0 0 0 0 100 26 0 0 0 0 0 0 0x58 0 0 0 0 38 0 0 0 0 0 0 0 0x59 0 0 0 0 1 0 0 0 0 0 0 0 0x100001 0 0 0 0 0 0 0 0 24304 0 0 100 0x100002 29015 0 0 0 10239 0 0 0 30993 1 14 100 0x100003 0 0 0 0 0 0 0 0 50 0 0 98 0x100004 126993 0 0 0 126987 0 0 0 273079 0 0 100 表位置定位 表PARTNUM组成 = 0xDDDTTTTT DDD 为16进制的dbspace编号 TTTTT 为表在 dbspace的逻辑编号 2.6.4 CHUNK设备选择

裸设备分为字符设备和块设备两种，针对Informix的访问特性，使用字符设备的性能要好于块设备。

Unix系统在创建裸设备时，通常对同一个裸设备会生成上述的两种设备。在挂载时需要认真选择。

裸设备属性查看方式， Linux下 ls /dev crw-rw---- 1 root audio 14, 12 Apr 20 13:28 adsp crw------- 1 root root 10, 175 Apr 20 13:28 agpgart crw-rw---- 1 root audio 14, 4 Apr 20 13:28 audio crw------- 1 root root 10, 62 Apr 20 13:28 autofs brw-rw---- 1 root floppy 2, 0 Apr 20 13:28 fd0 brw-rw---- 1 root floppy 2, 84 Apr 20 13:28 fd0u1040 brw-rw---- 1 root floppy 2, 88 Apr 20 13:28 fd0u1120 brw-rw---- 1 root floppy 2, 28 Apr 20 13:28 fd0u1440 设备属性列，第一个字符C表示字符设备，如果是B表示为块设备 北京数据中心

第27页 共 97页

Informix数据库维护指南

2.6.5 KAIO

在informix内部，执行读写操作有两种形式，一种为AIO即异步IO，一种为KAIO即核心异步IO.

AIO通常能够用于裸设备和磁盘文件的读写，而KAIO仅能用于裸设备的读写。 由于KAIO对于磁盘的访问方式，消耗CPU时间更短。因此在使用裸设备的情况下，应打开系统对KAIO特性的支持，在开启informix的KAIO功能。在$INFORMIXDIR/release/en_us/0333/*machine_notes*.txt 文件中，即机器说明文件，描述了该种操作系统是否支持KAIO，以及如何在该系统上打开informix的KAIO功能。

AIX操作系统默认情况下已经打开KAIO功能。 KAIO线程数量，与实例内使用的CPU VP数量相同。 检查数据库初始化以后是否已经打开KAIO

onstat -g ath # Print all threads

IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 13 days 23:10:37 -- 40749020 Kbytes

Threads:

tid tcb rstcb prty status vp-class name

2 c00000080c619cf0 0 1 IO Idle 60lio* lio vp 0 3 c00000080ce7f460 0 1 IO Idle 61pio* pio vp 0 4 c00000080ceab890 0 1 IO Idle 62aio* aio vp 0 5 c00000080ced7cc0 0 1 IO Idle 63msc* msc vp 0 6 c00000080cf0c460 0 1 IO Idle 64fifo* fifo vp 0 7 c00000080cf39890 0 1 IO Idle 65aio* aio vp 1 8 c00000080cf65cc0 0 1 IO Idle 66aio* aio vp 2 9 c00000080cf92460 0 1 IO Idle 67aio* aio vp 3 10 c00000080cfbeaa0 c00000080b68b030 3 sleeping secs: 1 53cpu main_loop() 11 c00000080c74bcf0 0 1 running 68soc* soctcppoll 12 c00000080c7a2cf0 0 1 running 69soc* soctcppoll 35 c00000080c598cc0 0 2 sleeping forever 1cpu* soctcplst 36 c00000080c61acc0 0 2 sleeping forever 3cpu* soctcplst 37 c00000080c673cc0 c00000080b68b888 1 sleeping secs: 1 40cpu flush_sub(0) 38 c00000080c69dcc0 c00000080b68c0e0 1 sleeping secs: 1 54cpu flush_sub(1) 39 c00000080c69ecc0 c00000080b68c938 1 sleeping secs: 1 54cpu flush_sub(2) 40 c00000080c69fcc0 c00000080b68d190 1 sleeping secs: 1 54cpu flush_sub(3) 41 c00000080c6c9cc0 c00000080b68d9e8 1 sleeping secs: 1 54cpu flush_sub(4) 42 c00000080c6cacc0 c00000080b68e240 1 sleeping secs: 1 58cpu flush_sub(5) 270 c00000080efc6920 c00000080ee01240 2 sleeping secs: 1 54cpu aslogflush

271 c00000080efc6b90 c00000080ee01a98 1 IO Wait 4cpu btscanner_0 273 c00000080efc65c0 c00000080edfe888 1 IO Wait 41cpu btscanner_1 276 c00000080efc6030 0 3 running 3cpu* kaio 411 c0000008112f8950 0 3 IO Idle 35cpu* kaio 416 c00000080efe8520 0 3 running 19cpu* kaio 北京数据中心

第28页 共 97页

Informix数据库维护指南 422 c00000081132f6a0 0 3 IO Idle 28cpu* kaio 433 c0000008113b6430 0 3 running 9cpu* kaio 444 c00000081125d5b0 0 3 IO Idle 18cpu* kaio 457 c0000008100743f0 c00000080ee009e8 3 sleeping secs: 1 1cpu* onmode_mon 458 c0000008100746c0 c00000080ee3f328 3 sleeping secs: 1 59cpu periodic 479 c00000081101d0e0 c00000080ee04450 1 cond wait bp_cond 25cpu bf_priosweep() 5281 c000000857a13030 c0000008113c72d8 1 cond wait netnorm 34cpu sqlexec 5282 c000000857a39780 c0000008113c7b30 1 cond wait netnorm 1cpu sqlexec 5283 c000000857a95030 c0000008113c8388 1 cond wait netnorm 44cpu sqlexec 5289 c0000008596c8cf0 c0000008113c9c90 1 cond wait netnorm 21cpu sqlexec 5290 c000000859719570 c0000008113ca4e8 1 cond wait netnorm 14cpu sqlexec 5291 c00000085975f450 c00000080ee251f8 1 cond wait netnorm 59cpu sqlexec 5292 c00000082e71ac10 c00000080ee39760 1 cond wait netnorm 44cpu sqlexec 5293 c00000085a0d7030 c0000008113cad40 1 cond wait netnorm 13cpu sqlexec 2.7 网络

2.7.1 数据库总连接数限制

数据库经常使用两种连接方式，一种为 TCP方式，另一种为共享内存方式，即SHM方式。

TCP没有明显的连接上限设置，连接上限仅取决于操作系统内关于网络连接数及文件描述符的限制，因为打开一个TCP连接即相当于打开一个文件，需要占用一个文件描述符。

SHM有连接上限的限制，连接上限就是在CONFIG配置参数内设定的值，不能动态增加。 SHM连接数量监控 onstat -g ntu IBM Informix Dynamic Server Version 11.70.UC4 -- On-Line -- Up 00:01:42 -- 161084 Kbytes global network information: #netscb connects read write q-free q-limits q-exceed alloc/max 11/ 11 2 0 7 7/ 0 0/ 134 10/ 0 0/ 0 Individual thread network information (basic): netscb type thread name sid fd poll reads writes q-nrm q-pvt q-exp 4b482ac8 ipcshm sqlexec 30 0 0 5 5 0/ 0 0/ 0 0/ 0 4b47ac88 ipcshm sqlexec 29 0 0 0 0 0/ 0 0/ 0 0/ 0 4b478ac8 ipcshm sqlexec 28 0 0 0 0 0/ 0 0/ 0 0/ 0 4ca009c0 ipcshm sqlexec 27 0 0 0 0 0/ 0 0/ 0 0/ 0 4b480c58 ipcshm sqlexec 26 0 0 0 0 0/ 0 0/ 0 0/ 0 4c837c48 ipcshm sqlexec 25 0 0 0 0 0/ 0 0/ 0 0/ 0 北京数据中心 第29页 共 97页

Informix数据库维护指南

4b460de0 ipcshm sm_discon 8 0 0 0 0 0/ 0 0/ 0 0/ 0 4b456d38 ipcshm sm_listen 5 0 0 2 0 0/ 0 0/ 0 0/ 0 4b44ec48 soctcp soctcplst 4 1 5 0 0 0/ 0 0/ 0 0/ 0 4b44aac8 ipcshm sm_poll 3 0 0 8 0 0/ 0 0/ 0 0/ 0 4b44cd98 soctcp soctcppoll 2 0 5 0 0 0/ 0 0/ 0 0/ 0 2.7.2 连接数增长的趋势

利用上述监控方式，长期记录连接数量，预测未来连接数需求，据此更改相关操作系统及数据库的配置参数。

2.8 统计更新

2.8.1 统计更新策略及粒度设置

When to Execute Number of rows has changed significantly or After migration from previous version of database server For all columns that are not the leading column of any index Queries have non-indexed join columns or filter columns Queries have an indexed join columns or filter columns UPDATE STATISTICS Statement UPDATE STATISTICS LOW DROP DISTRIBUTIONS UPDATE STATISTICS LOW UPDATE STATISTICS MEDIUM DISTRIBUTIONS ONLY UPDATE STATISTICS HIGH table (leading columin index) Queries have a multicolumn indexed defined on join columns or filter UPDATE STATISTICS HIGH table (first differincolumns column in multicolumn index) Queries have a multicolumn indexed defined on join columns or filter UPDATE STATISTICS low table (all columns columns multicolumn index) Queries have many small tables (fit into one extent) Queries use SPL routines UPDATE STATISTICS HIGH on small tables UPDATE STATISTICS for procedure 2.8.2 统计更新执行时机

统计更新操作并不会对数据上锁，因此不会阻碍应用程序的运行。但是在统计更新的工作过程中会有很频繁的I/O操作，影响系统性能。

需要选择合适的时机来执行统计更新操作： 1） 2）

新建表以后

执行大量的数据变更操作后，例如删除，插入，更新

第30页 共 97页

北京数据中心

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Informix 数据库维护指南v1.0 - 图文(6)在线全文阅读。