检查点持续时间太长，如何解决？？？高手请进！

princejian

各位高手朋友请留步，帮小弟解决一个问题吧。
数据库系统为INFORMIX 7.31UD6W5，OS为SOLARIS5.8，机型为SUN V440，所做操作为数据导入。
要导入的数据大约有三四百万条，问题表现为一个检查点的持续时间特别长，甚至达到了几百秒。而且导入数据后数据库奇慢无比，让人无法忍受！
以下是onconfig配置文件里的一些主要的参数项的设置情况：

# Physical Log Configuration

PHYSDBS         phydbs          # Location (dbspace) of physical log
PHYSFILE        500000          # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES        10              # Number of logical log files
LOGSIZE         10000           # Logical log size (Kbytes)

# System Configuration

MULTIPROCESSOR  1               # 0 for single-processor, 1 for multi-processor
NUMCPUVPS       1               # Number of user (cpu) vps
SINGLE_CPU_VP   1               # If non-zero, limit number of cpu vps to one


# Shared Memory Parameters

LOCKS           500000          # Maximum number of locks
BUFFERS         100000          # Maximum number of shared buffers
NUMAIOVPS      30              # Number of IO vps
PHYSBUFF        64              # Physical log buffer size (Kbytes)
LOGBUFF         64              # Logical log buffer size (Kbytes)
LOGSMAX         20              # Maximum number of logical log files
CLEANERS        8               # Number of buffer cleaner processes
SHMBASE         0xa000000         # Shared memory base address
SHMVIRTSIZE     32000           # initial virtual shared memory segment size
SHMADD          32000           # Size of new shared memory segments (Kbytes)
SHMTOTAL        0               # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL       86400           # Check point interval (in sec)
LRUS            32              # Number of LRU queues
LRU_MAX_DIRTY   2               # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY   1               # LRU percent dirty end cleaning limit
LTXHWM          50              # Long transaction high water mark percentage
LTXEHWM         60              # Long transaction high water mark (exclusive)
TXTIMEOUT       0x12c             # Transaction timeout (in sec)
STACKSIZE       32              # Stack size (Kbytes)

下面是我摘的一段online.log，是三个检查点相继执行的这段时间的日志：
15:42:11  Logical Log 1084 Complete.
15:42:12  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1084 Complete." "Logical Log 1084 Complete."
15:42:51  Logical Log 1085 Complete.
15:42:52  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1085 Complete." "Logical Log 1085 Complete."
15:43:37  Logical Log 1086 Complete.
15:43:38  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1086 Complete." "Logical Log 1086 Complete."
15:44:24  Logical Log 1087 Complete.
15:44:25  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1087 Complete." "Logical Log 1087 Complete."
15:45:10  Logical Log 1088 Complete.
15:45:11  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1088 Complete." "Logical Log 1088 Complete."
15:47:11  Logical Log 1089 Complete.
15:47:12  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1089 Complete." "Logical Log 1089 Complete."
15:48:01  Logical Log 1090 Complete.
15:48:02  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1090 Complete." "Logical Log 1090 Complete."
15:48:46  Logical Log 1091 Complete.
15:48:47  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1091 Complete." "Logical Log 1091 Complete."
15:49:38  Logical Log 1092 Complete.
15:49:39  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1092 Complete." "Logical Log 1092 Complete."
16:12:05  Checkpoint Completed:  duration was 1346 seconds.
16:12:05  Checkpoint loguniq 1093, logpos 0x310

16:12:40  Logical Log 1093 Complete.
16:12:41  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1093 Complete." "Logical Log 1093 Complete."
16:13:25  Logical Log 1094 Complete.
16:13:26  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1094 Complete." "Logical Log 1094 Complete."
16:14:29  Logical Log 1095 Complete.
16:14:30  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1095 Complete." "Logical Log 1095 Complete."
16:15:21  Logical Log 1096 Complete.
16:15:22  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1096 Complete." "Logical Log 1096 Complete."
16:16:12  Logical Log 1097 Complete.
16:16:13  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1097 Complete." "Logical Log 1097 Complete."
16:17:00  Logical Log 1098 Complete.
16:17:01  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1098 Complete." "Logical Log 1098 Complete."
16:17:52  Logical Log 1099 Complete.
16:17:53  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1099 Complete." "Logical Log 1099 Complete."
16:18:50  Logical Log 1100 Complete.
16:18:51  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1100 Complete." "Logical Log 1100 Complete."
16:19:47  Logical Log 1101 Complete.
16:19:48  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1101 Complete." "Logical Log 1101 Complete."
16:28:45  Checkpoint Completed:  duration was 538 seconds.
16:28:45  Checkpoint loguniq 1102, logpos 0x310

16:31:43  Logical Log 1102 Complete.
16:31:44  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1102 Complete." "Logical Log 1102 Complete."
16:32:43  Logical Log 1103 Complete.
16:32:44  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1103 Complete." "Logical Log 1103 Complete."
16:33:25  Logical Log 1104 Complete.
16:33:26  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1104 Complete." "Logical Log 1104 Complete."
16:34:23  Logical Log 1105 Complete.
16:34:24  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1105 Complete." "Logical Log 1105 Complete."
16:35:14  Logical Log 1106 Complete.
16:35:15  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1106 Complete." "Logical Log 1106 Complete."
16:35:59  Logical Log 1107 Complete.
16:36:00  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1107 Complete." "Logical Log 1107 Complete."
16:36:54  Logical Log 1108 Complete.
16:36:55  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1108 Complete." "Logical Log 1108 Complete."
16:37:43  Logical Log 1109 Complete.
16:37:44  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1109 Complete." "Logical Log 1109 Complete."
16:38:34  Logical Log 1110 Complete.
16:38:35  Process exited with return code 142: /bin/sh /bin/sh -c /opt/informix/etc/log_full.sh 2 23 "Logical Log 1110 Complete." "Logical Log 1110 Complete."
16:46:28  Checkpoint Completed:  duration was 474 seconds.
16:46:28  Checkpoint loguniq 1111, logpos 0x310

各位大哥，能不能给出一个调优的方案啊？让检查点持续时间短一点。还需要什么信息，我尽量提供！谢谢了！
此外导入数据后该如何对数据库进行一个优化操作，让它不至于那么慢啊？

[ 本帖最后由 princejian 于 2006-5-21 00:45 编辑 ]

iceiceberg

看log应该是逻辑日志满，需要备份。但我个人对备份、恢复、逻辑日志都不太了解，另外onbar的返回码142是什么意义也不清楚，还是请高手来研究一下吧。
如果你只是想让cp的持续时间短些，可以把CKPTINTVL设的小点（比如300）试一下。
另外感觉你的物理日志文件有点大了（500M），但是持续时间长是否与它有关系我也搞不清。因为在运行CP时应该只是将物理日志清空就可以了，照理说不会花太长时间的。你可以试试把它改小一点再跑跑看：）

我对这个问题很感兴趣，期待最后不仅能解决，还能找到问题的原因：）

wenlq

机型为SUN V440 相当与什么级别的机器？

是不是 BUFFERS=100000 太大了
LRU_MAX_DIRTY   2               # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY   1               # LRU percent dirty end cleaning limit
按理会触发 LRU 写了。
10000的2% 是20000， 1% 是10000，
考虑一下 10000页-20000页的checkpoint 是不是太长？降低BUFFERS 测测

iceiceberg

wenlq有个地方我觉得你说的不太正确。
超过LRU_MAX_DIRTY会触发页面清理程序，但并不是checkpoint。（虽然checkpoint会引发LRU清理线程）

princejian

原帖由 iceiceberg 于 2006-5-21 22:42 发表
wenlq有个地方我觉得你说的不太正确。
超过LRU_MAX_DIRTY会触发页面清理程序，但并不是checkpoint。（虽然checkpoint会引发LRU清理线程）

我也这么感觉,它只是触发了cleaner吧?

wenlq

lz 的checkpoint 间隔是 86400秒 一整天。
其checkpoint应该是由于物理日志达到 75%而起动的。
1%-2%的页可能在checkpoint时间去刷新。
我是怀疑10000-20000页的刷新对楼主的机器可能太慢。

iceiceberg

我公司的一台V440，BUFFERS设的是192000. MIN=0 MAX=1
应该也差不多，没有出现以上问题。
但我们的PHYSFILE的大小是楼主的十分之一。
我觉得我们还是应该多注意一下log中的报错信息。

hukeqin333

倒入数据后,有没有做UPDATE STATISTATICS
还有你的计算机内存多大,是不是就作为数据库服务器用?是的话,把BUFFERS改成内存的一半
还有你用没有把表的页级锁改成行级锁?

hukeqin333

你的计算机是几个CPU的?
我有个配置你可以参考:6个CPU 16G内存 300多张表,最多的表有1000万条记录,数据量60G
是从7.3移到9.4的

LOCKS8000000# Maximum number of locks
BUFFERS3000000# Maximum number of shared buffers
NUMAIOVPS       8               # Number of IO vps
PHYSBUFF        4096            # Physical log buffer size (Kbytes)
LOGBUFF         4096            # Logical log buffer size (Kbytes)
CLEANERS        16              # Number of buffer cleaner processes
SHMBASE         0x0               # Shared memory base address
SHMVIRTSIZE     1024000         # initial virtual shared memory segment size
SHMADD          128000          # Size of new shared memory segments (Kbytes)
SHMTOTAL        0               # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL       600             # Check point interval (in sec)
LRUS            16              # Number of LRU queues
LRU_MAX_DIRTY   0.200000        # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY   0.100000        # LRU percent dirty end cleaning limit
TXTIMEOUT       0x12c             # Transaction timeout (in sec)
STACKSIZE       256             # Stack size (Kbytes)