- 论坛徽章:
- 0
|
DB2性能调整优化问题,请高手指点,急...
1、楼主用的是SMS，db2empfa是个不错的方案，不过改后就改不回来了！
2、楼主又加了个BUFFERPOOL？能把：
- select a.tbspace,b.bpname,b.npages,b.pagesize from syscat.tablespaces a ,syscat.bufferpools b where a.bufferpoolid=b.bufferpoolid
列出来看看吗？楼主会明白，一个Tbs只能使用一个BufferPool，而一个BufferPool可用于多个Tbs吧！
3、运行重整的有效方法是：runstats =>; reorg =>; rebind
4、151517的bufferpool对你1G的MEM来说该是太大了，调小一些，过大的BufferPool会使性能很低！
5、为什么不打开监视开关看看呢？
顺便贴一个关于BufferPool的不错的文章！
摘自《DB2 Magazine》By Roger Sanders
Buffer Pools
If you have any experience with DB2 UDB, you probably know that a buffer pool is an area of memory allocated to the DB2 Database Manager solely for caching data pages either as new data is added to a database or as data is retrieved from disk in response to a query. Buffer pools improve overall database performance by reducing disk input/output operations because data can be accessed much faster from memory than from disk. In fact, analyzing how buffer pools are created and used is the most important step when tuning a database for optimum performance.
Given the importance of buffer pools, every DBA should understand how DB2 UDB uses them. Here's a quick refresher course. When new data is added to a database, it's added to a new page in the buffer pool first. Eventually, that page is externalized to database storage. On the other hand, when data is retrieved from a database in response to a query, the DB2 Database Manager places the page (or pages) that contain the requested data into a buffer pool before passing the data itself onto the application or user that requested it. Each time a new query is processed, every available buffer pool is searched to see if the page that contains the data needed already resides in memory. If so, it's immediately passed on to the appropriate application or user. However, if the data requested can't be found in any of the available buffer pools, the DB2 Database Manager retrieves it from storage and copies it to a buffer pool before passing it on. Once a page has been copied to a buffer pool, that page stays in the buffer pool until the database is shut down or until the space it occupies is needed to store another page. (Because all data additions and modifications take place in the buffer pool first — modified pages are eventually flushed to disk — data stored in a buffer pool is always up-to-date.) When a buffer pool becomes full, the DB2 Database Manager selects pages to remove by examining when a page was last referenced, the likelihood that the page will be referenced again, the page type, and whether or not the page has been altered without externalizing the changes to disk. For example, a page that was retrieved in response to a query that was run 30 minutes ago would more likely be overwritten than a page that contains data for an update operation that hasn't yet been committed.
By default, DB2 UDB creates one buffer pool (named IBMDEFAULTBP) as part of the database creation process. On Linux and Unix platforms, this buffer pool is allocated 1,000 4KB pages of memory; on Windows platforms, this buffer pool is allocated 250 4KB pages. You can increase or decrease the number of 4KB pages this buffer pool can hold by selecting the appropriate action from the Buffer Pools menu found in the Control Center or by executing the ALTER BUFFERPOOL SQL statement. You can also create additional buffer pools using the Buffer Pools menu in the Control Center or by executing the CREATE BUFFERPOOL SQL statement.
Because buffer pools are so important, you should carefully consider how many buffer pools your implementation needs, how big each buffer pool should be, and how each buffer pool available should be used. In most environments, the number of buffer pools that can be used effectively depends on the amount of system memory available. If the total amount of memory available can hold only 10,000 4k pages (or less), it's often better to use a single large buffer pool than several small buffer pools. Using several small buffer pools can cause frequently accessed pages to be swapped in and out of memory more often, which, in turn, can lead to I/O contention for storage objects such as system catalog tables or repeatedly accessed user tables and indexes. However, if more memory is available, consider creating individual buffer pools for the following:
* Each temporary tablespace defined
* Tablespaces that contain tables that are accessed consistently and repeatedly by many short-duration update transactions
* Tablespaces that contain tables and indexes that are updated frequently
* Tablespaces that contain tables and indexes that are queried frequently but are updated infrequently
* Tablespaces that contain tables that are queried frequently in a random manner
* Tablespaces that contain data that is accessed by infrequently used applications
* Any tablespace that contains data and indexes you want to favor.
In most cases, larger buffer pools are better than smaller buffer pools. However, take into account the total amount of memory available as well as how the buffer pools will be used. If you have an application that performs many random access operations against a very large table, you should create and use a small buffer pool for that particular table. In such a situation, there's no need to keep data pages in buffer pool memory once they've been used to resolve a single query. On the other hand, if you have an application that frequently retrieves data from several small look-up tables, you should consider creating a buffer pool that's large enough to hold all of the data stored in these tables. With this design, the data can be loaded into memory once, allowing it to be accessed repeatedly without requiring additional disk I/O. |
|
免费注册
楼主: joey945
发表于 2004-11-04 08:57
发表于 2004-11-04 10:44
B2 Node:000 
