如何确定命名缓冲区的大小?
为提高数据库的性能,需要建立命名缓冲区,将一些并发性高的表绑定到命名缓冲区中,以减少对缺省缓冲区的争用。问题:命名缓冲区的大小和表的大小有关系吗?命名缓冲区一般设多大比较好?
请各位大虾发表一下意见。谢谢! 大家可否将生产环境的内存配置,发一个示例看看。 我觉得得看你得Sybase内存有多大,并发性高的表本身有多大?应用逻辑是怎么使用这些表的。 内存为8GB。
max memory=6.4gb
data cache=4.5gb
有2张表有600万行,容量超过20gb(这种表是否需要分配命名缓存?)。
有些热点表在10万行以下,容量为1-2gb以内(是否可以分配足够大的内存,把表都放到内存里?还是分配其容量的10%-20%?)。 这两个超过600万的表 应用是怎么访问的?插入?select ? 应用以insert为主。
Transaction Profile
-------------------
Transaction Summary per sec per xact count% of total
---------------------------------------------------------------------
Committed Xacts 16.2 n/a 14563 n/a
Transaction Detail per sec per xact count% of total
---------------------------------------------------------------------
Inserts
APL Heap Table 4708.0 291.0 4237197 99.8 %
APL Clustered Table 5.3 0.3 4788 0.1 %
Data Only Lock Table 4.2 0.3 3821 0.1 %
---------------------------------------------------------------------
Total Rows Inserted 4717.6 291.5 4245806 99.8 %
Updates
APL Deferred 0.0 0.0 0 0.0 %
APL Direct In-place 0.0 0.0 3 0.4 %
APL Direct Cheap 0.0 0.0 6 0.8 %
APL Direct Expensive 0.0 0.0 0 0.0 %
DOL Deferred 0.0 0.0 0 0.0 %
DOL Direct 0.9 0.1 791 98.9 %
---------------------------------------------------------------------
Total Rows Updated 0.9 0.1 800 0.0 %
Data Only Locked Updates
DOL Replace 0.3 0.0 256 32.4 %
DOL Shrink 0.0 0.0 10 1.3 %
DOL Cheap Expand 0.2 0.0 154 19.5 %
DOL Expensive Expand 0.4 0.0 371 46.9 %
DOL Expand & Forward 0.0 0.0 0 0.0 %
DOL Fwd Row Returned 0.0 0.0 0 0.0 %
---------------------------------------------------------------------
Total DOL Rows Updated 0.9 0.1 791 0.0 %
Deletes
APL Deferred 3.1 0.2 2759 30.5 %
APL Direct 2.8 0.2 2497 27.6 %
DOL 4.2 0.3 3777 41.8 %
---------------------------------------------------------------------
Total Rows Deleted 10.0 0.6 9033 0.2 %
===========================================================
Total Rows Affected 4728.5 292.2 4255639 如果以对这两个表的访问insert为主,我觉得绑定缓存似乎没什么大的用处吧?
另外
APL Heap Table 4708.0 291.0 4237197 99.8 %
对APL堆表的操作似乎应该是insert 临时表吧? APL heap tables
“APL Heap Tables” reports the number of row inserts that took place on allpages-locked heap tables—all tables that do not have a clustered index. This includes:
Partitioned heap tables
Unpartitioned heap tables
Slow bulk copy inserts into heap tables
select into commands
Inserts into worktables
The “% of total” column shows the percentage of row inserts into heap tables as a percentage of the total number of inserts.
If there are a large number of inserts to heap tables, determine if these inserts are generating contention.
Check the sp_sysmon report for data on last page locks on heaps in “Lock detail”. If there appears to be a contention problem, Adaptive Server Monitor can help you figure out which tables are involved.
In many cases, creating a clustered index that randomizes insert activity solves the performance problems for heaps. In other cases, you might need to establish partitions on an unpartitioned table or increase the number of partitions on a partitioned table. 不是临时表。应用上就是对这2张表插入数据。
现在看来简单设立缓存不能解决问题了。 现在采取的方法是通过spotlight for ase对default data cache的使用情况进行监控,计算每个OBJECT占用的内存大小。根据这个结果再确定各命名缓冲区的大小。
页:
[1]