- 论坛徽章:
- 0
|
环境: 4 个 AMD 848(2.2G),16G 内存,Redhat AS 4(x86_64)
Syabse版本:ASE-12_5 (x86_64)
echo 8589934592 > /proc/sys/kernel/shmmax
echo 16106127360 > /proc/sys/kernel/shmall
ipcs 显示:
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 98304 gdm 600 393216 2 dest
0xf402a202 1441793 sybase 600 2126512128 3
0xf402a203 1474562 sybase 600 2126512128 3
0xf402a204 1507331 sybase 600 2126512128 3
0xf402a205 1540100 sybase 600 2126512128 3
0xf402a206 1572869 sybase 600 2126512128 3
0xf402a207 1605638 sybase 600 2126512128 3
0xf402a208 1638407 sybase 600 2126512128 3
0xf402a209 1671176 sybase 600 1220542464 3
------ Semaphore Arrays --------
key semid owner perms nsems
------ Message Queues --------
key msqid owner perms used-bytes messages
pmap 显示:
3443: /opt/sybase/ASE-12_5/bin/dataserver -ONLINE:1,0,0xfffffffff402a202, 0x2a9575d000, 0x7ec00000, 0x1559
0000000000400000 11452K r-x-- /dataserver
000000000102e000 3160K rw--- /dataserver
0000000001344000 668K rwx-- [ anon ]
0000000040000000 4K ----- [ anon ]
0000000040001000 12K rwx-- [ anon ]
0000000040004000 4K ----- [ anon ]
0000000040005000 12K rwx-- [ anon ]
0000000040008000 4K ----- [ anon ]
0000000040009000 12K rwx-- [ anon ]
0000002a95556000 4K rw--- [ anon ]
0000002a9557d000 8K rw--- [ anon ]
0000002a9557f000 772K r-x-- /libstdc++.so.5.0.7
0000002a95640000 1024K ----- /libstdc++.so.5.0.7
0000002a95740000 36K rw--- /libstdc++.so.5.0.7
0000002a95749000 80K rw--- [ anon ]
0000002a9575d000 2076672K rw-s- [ shmid=0x160001 ]
0000002b1435d000 2076672K rw-s- [ shmid=0x168002 ]
0000002b92f5d000 2076672K rw-s- [ shmid=0x170003 ]
0000002c11b5d000 2076672K rw-s- [ shmid=0x178004 ]
0000002c9075d000 2076672K rw-s- [ shmid=0x180005 ]
0000002d0f35d000 2076672K rw-s- [ shmid=0x188006 ]
0000002d8df5d000 2076672K rw-s- [ shmid=0x190007 ]
0000002e0cb5d000 1191936K rw-s- [ shmid=0x198008 ]
0000003433600000 84K r-x-- /ld-2.3.4.so
0000003433714000 8K rw--- /ld-2.3.4.so
0000003433800000 1188K r-x-- /libc-2.3.4.so
0000003433929000 1020K ----- /libc-2.3.4.so
0000003433a28000 12K r---- /libc-2.3.4.so
0000003433a2b000 12K rw--- /libc-2.3.4.so
0000003433a2e000 16K rw--- [ anon ]
0000003433b00000 532K r-x-- /libm-2.3.4.so
0000003433b85000 1020K ----- /libm-2.3.4.so
0000003433c84000 8K rw--- /libm-2.3.4.so
0000003433d00000 8K r-x-- /libdl-2.3.4.so
0000003433d02000 1020K ----- /libdl-2.3.4.so
0000003433e01000 8K rw--- /libdl-2.3.4.so
0000003434500000 64K r-x-- /libpthread-2.3.4.so
0000003434510000 1020K ----- /libpthread-2.3.4.so
000000343460f000 8K rw--- /libpthread-2.3.4.so
0000003434611000 16K rw--- [ anon ]
0000003434b00000 32K r-x-- /libpam.so.0.77
0000003434b08000 1020K ----- /libpam.so.0.77
0000003434c07000 4K rw--- /libpam.so.0.77
0000003436500000 44K r-x-- /libgcc_s-3.4.3-20041213.so.1
000000343650b000 1020K ----- /libgcc_s-3.4.3-20041213.so.1
000000343660a000 4K rw--- /libgcc_s-3.4.3-20041213.so.1
0000003438a00000 36K r-x-- /librt-2.3.4.so
0000003438a09000 1020K ----- /librt-2.3.4.so
0000003438b08000 8K rw--- /librt-2.3.4.so
0000003438b0a000 64K rw--- [ anon ]
000000343b200000 76K r-x-- /libnsl-2.3.4.so
000000343b213000 1024K ----- /libnsl-2.3.4.so
000000343b313000 8K rw--- /libnsl-2.3.4.so
000000343b315000 8K rw--- [ anon ]
0000007fbfffb000 20K rwx-- [ stack ]
ffffffffff600000 8192K ----- [ anon ]
total 15764516K
Sybase 数据库配置文件:
##############################################################################
#
# Configuration File for the Sybase SQL Server
#
# Please read the System Administration Guide (SAG)
# before changing any of the values in this file.
#
##############################################################################
[Configuration Options]
[General Information]
[Cache Manager]
number of oam trips = 1024
number of index trips = DEFAULT
memory alignment boundary = DEFAULT
global async prefetch limit = 80
global cache partition number = 64
extended cache size = DEFAULT
[Named Cache:ESBIScache]
cache size = 3355443.2K
cache status = mixed cache
cache replacement policy = DEFAULT
local cache partition number = DEFAULT
[2K I/O Buffer Pool]
pool size = DEFAULT
wash size = DEFAULT
local async prefetch limit = 0
[Named Cache:default data cache]
cache size = 943718.4K
cache status = default data cache
cache replacement policy = DEFAULT
local cache partition number = DEFAULT
[Named Cache:tempcache]
cache size = 1258291.2K
cache status = mixed cache
cache replacement policy = DEFAULT
local cache partition number = DEFAULT
[2K I/O Buffer Pool]
pool size = DEFAULT
wash size = DEFAULT
local async prefetch limit = DEFAULT
[Meta-Data Caches]
number of open databases = DEFAULT
number of open objects = 5000
open object spinlock ratio = 1000
number of open indexes = 5000
open index hash spinlock ratio = 1000
open index spinlock ratio = 1000
partition groups = 10240
partition spinlock ratio = 102400
[Disk I/O]
disk i/o structures = 1280
number of large i/o buffers = 256
page utilization percent = DEFAULT
number of devices = 128
disable disk mirroring = DEFAULT
allow sql server async i/o = DEFAULT
[Physical Memory]
max memory = 7864320
additional network memory = DEFAULT
shared memory starting address = DEFAULT
allocate max shared memory = 1
dynamic allocation on demand = DEFAULT
lock shared memory = 1
heap memory per user = 20480
[Processors]
max online engines = 4
number of engines at startup = 3
statement cache size = DEFAULT
[SQL Server Administration]
procedure cache size = 4096000
default database size = 10
identity burning set factor = DEFAULT
allow nested triggers = DEFAULT
allow updates to system tables = DEFAULT
default fill factor percent = 50
default exp_row_size percent = 80
number of mailboxes = DEFAULT
number of messages = DEFAULT
number of alarms = DEFAULT
number of pre-allocated extents = DEFAULT
event buffers per engine = 102400
cpu accounting flush interval = 20
i/o accounting flush interval = 102400
sql server clock tick length = DEFAULT
runnable process search count = DEFAULT
i/o polling process count = 102400
... ...
做了两个 cache 一个 bind 数据库(3G),另一个 bind tempdb(1G)
均比数据库略大。
可是在单机连接,重复进行某一个查询时,SCSI(Ultra 320) 硬盘显现出来的速度(4-5秒)比 SATA II 硬盘(SATA II 硬盘的机器才 2个 AMD 248 CPU、4G内存)的速度(2秒)要慢 2 秒。
在进行此查询时,scsi 硬盘灯亮;不亮了,查询也就结束了。
按照症状来看,似乎每次查询都到硬盘里面读取了某些数据,使得查询较慢。
再没做 bind 之前,default data cache 为 6G,重复查询速度一样为 4-5秒。bind 之后,速度没有变化,均是在读取硬盘完成后,查询才结束。
仔细观察过,Linux 的 swap 分区始终没有使用。
SATA 硬盘比 SCSI 硬盘快 ?
我的疑问:
default data cache 和 bind 数据库的 cache 均不起作用?
还是在查询中的哪个环节 Sybase 硬是要读取硬盘呢?
注意呀:我这里所指的速度是进行第二次、第三次、第 n 次查询速度,不是指第一次查询速度。
特向大家指教,不胜感激 !
[ 本帖最后由 gugong 于 2006-1-8 19:10 编辑 ] |
|