To 16K My Indices ... Or Not to 16K Them, That is
A DBA on IDS 11.50.FC5/AIX asked whether moving indexes (especially primary keys) into 16K-page dbspaces would pay off, worrying about memory use and more locking/deadlocks. Replies were mixed: one user who converted 4K to 16K dbspaces saw higher I/O and fixed lock timeouts by switching tables from page- to row-level locking, and noted a 16K bufferpool is created automatically but needs buffer/LRU tuning. Another reported that in testing, 2K pages with more index levels actually outperformed 16K pages because of the larger cache-miss I/O penalty, unless everything fits in buffers. No firm conclusion; the poster then asked about 11.50.xC2 dynamic index compression, which drew no answer in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.FC5 AIX 5.3 My current client has a lot of work to do on their indices, a lot of it involving embedded primary keys. I would love to migrate these keys (and others) into dbspaces with at least a 16k pagesize to take advantage of the more rows per page, less pages means less levels and, most of all, the improvement of such an index upon query performance. What has me worried about doing this is the effect upon session memory and a possible higher number of deadlocks due to the need for the instance to lock these pages while peforming those pesky inserts, updates and deletes from said index pages. Has anyone else migrated their indices to larger page size dbspaces, experimentally or within production? If so, would you mind sharing your experience with doing so? Thanks in advance. Clifton _________________________________________________________________ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/171222985/direct/01/
Mr. Bean, We have an OLTP system with several dbspaces, converted from 4Kb to 16Kb. It was very usefull to overcome the database limits (based on pagesizes), but we had some troubles of IO levels (higher, on higher pages, of course). One thing that we had to change to avoid the lock timouts, was the lock mode of all tables, that was default (page) to row. This solved our troubles on almost every databases. Regards. Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix IIUG Member <http://www.iiug.org>; Clifton Bean escreveu: > IDS 11.50.FC5 AIX 5.3 > > My current client has a lot of work to do on their indices, a lot of it > involving embedded primary keys. I would love to migrate these keys (and > others) into dbspaces with at least a 16k pagesize to take advantage of the > more rows per page, less pages means less levels and, most of all, the > improvement of such an index upon query performance. > > What has me worried about doing this is the effect upon session memory and a > possible higher number of deadlocks due to the need for the instance to lock > these pages while peforming those pesky inserts, updates and deletes from said > index pages. > > Has anyone else migrated their indices to larger page size dbspaces, > experimentally or within production? If so, would you mind sharing your > experience with doing so? > > Thanks in advance. > > Clifton > > _________________________________________________________________ > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. > http://clk.atdmt.com/GBL/go/171222985/direct/01/ > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
That was practically the first thing I wrote a script to find and fix was those tables with page level locking. It has considerably reduced the number of deadlocks within the instance; those still occurring, I believe, are the result of programming issues which have yet to be discovered. I am presuming a BUFFERPOOL entry is needed for the 16k pages, yes? I cannot find anything in literature (hence my Shakespeare) that recommends a way to calculate how many BUFFERS may need to be allocated to a 16k BUFFERPOOL. Again, thanks for any assistance. Clifton > To: ids@iiug.org > From: amarini@fazenda.ms.gov.br > Subject: Re: To 16K My Indices ... Or Not to 16K Them, .... [18080] > Date: Thu, 12 Nov 2009 11:08:20 -0500 > > Mr. Bean, > We have an OLTP system with several dbspaces, converted from 4Kb to 16Kb. > It was very usefull to overcome the database limits (based on pagesizes), > but we had some troubles of IO levels (higher, on higher pages, of course). > One thing that we had to change to avoid the lock timouts, was the lock > mode of all tables, > that was default (page) to row. > This solved our troubles on almost every databases. > > Regards. > > Alexandre Marini > > Tecnologia da Informação - DBA > > SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix > > IIUG Member > > <http://www.iiug.org>; > > Clifton Bean escreveu: > > IDS 11.50.FC5 AIX 5.3 > > > > My current client has a lot of work to do on their indices, a lot of it > > involving embedded primary keys. I would love to migrate these keys (and > > others) into dbspaces with at least a 16k pagesize to take advantage of the > > more rows per page, less pages means less levels and, most of all, the > > improvement of such an index upon query performance. > > > > What has me worried about doing this is the effect upon session memory and a > > possible higher number of deadlocks due to the need for the instance to lock > > these pages while peforming those pesky inserts, updates and deletes from > said > > index pages. > > > > Has anyone else migrated their indices to larger page size dbspaces, > > experimentally or within production? If so, would you mind sharing your > > experience with doing so? > > > > Thanks in advance. > > > > Clifton > > > > _________________________________________________________________ > > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. > > http://clk.atdmt.com/GBL/go/171222985/direct/01/ > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/171222985/direct/01/
Hi Clifton I think there are some considerations: Are you going to post only indexes of large tables in this dbspace with large pages ? What I wanna mean: May be , you will utilize one page 16k to store only 2 or 5 kbytes of one indexes of a small table. Other: Imagine you'll do most selects (or update, or delete) and you have all indexes columns to read (and select) specific row. If you have a 16k page, the Server will read those 16k. It means: if you operational system has page size 2k, it will read 8 pages. If it has 4k page size, it will read 4 pages to compute a 16k page. And, remember: you'll transfer all of the pages to memory - buffer pool. I suggest you do not have large pages for indexes dbspace, my be you'll have I/O overhead because of that. In most OLTP systems, you have much more select/update, some delete and less inserts. Remember the job to do a page split .... BR Roberto Ferronato > To: ids@iiug.org > From: clifton_bean@hotmail.com > Subject: To 16K My Indices ... Or Not to 16K Them, That.... [18079] > Date: Thu, 12 Nov 2009 11:01:08 -0500 > > IDS 11.50.FC5 AIX 5.3 > > My current client has a lot of work to do on their indices, a lot of it > involving embedded primary keys. I would love to migrate these keys (and > others) into dbspaces with at least a 16k pagesize to take advantage of the > more rows per page, less pages means less levels and, most of all, the > improvement of such an index upon query performance. > > What has me worried about doing this is the effect upon session memory and a > possible higher number of deadlocks due to the need for the instance to lock > these pages while peforming those pesky inserts, updates and deletes from said > index pages. > > Has anyone else migrated their indices to larger page size dbspaces, > experimentally or within production? If so, would you mind sharing your > experience with doing so? > > Thanks in advance. > > Clifton > > _________________________________________________________________ > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. > http://clk.atdmt.com/GBL/go/171222985/direct/01/ > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Windows Live Hotmail: Your friends can get your Facebook updates, right from Hotmail®. http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social- network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_4:092009
Yes, a bufferpool of 16kb is necessary, but it´s automatically created on a 16kb dbspace creation. You must ajust all the parameters, like buffer count, lrus (let it 128 would be a good choice for OLTP), and maybe an agressive policy of lru_min_dirty and lru_max_dirty is also great, specially if you´re not using the AUTO_LRU_TUNING feature. Another thing I did was tunning the alice mode (index scanning) feature, ok? Regards. Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix IIUG Member <http://www.iiug.org>; Clifton Bean escreveu: > That was practically the first thing I wrote a script to find and fix was > those tables with page level locking. It has considerably reduced the number > of deadlocks within the instance; those still occurring, I believe, are the > result of programming issues which have yet to be discovered. > > I am presuming a BUFFERPOOL entry is needed for the 16k pages, yes? I cannot > find anything in literature (hence my Shakespeare) that recommends a way to > calculate how many BUFFERS may need to be allocated to a 16k BUFFERPOOL. > > Again, thanks for any assistance. > > Clifton > > >> To: ids@iiug.org >> From: amarini@fazenda.ms.gov.br >> Subject: Re: To 16K My Indices ... Or Not to 16K Them, .... [18080] >> Date: Thu, 12 Nov 2009 11:08:20 -0500 >> >> Mr. Bean, >> We have an OLTP system with several dbspaces, converted from 4Kb to 16Kb. >> It was very usefull to overcome the database limits (based on pagesizes), >> but we had some troubles of IO levels (higher, on higher pages, of course). >> One thing that we had to change to avoid the lock timouts, was the lock >> mode of all tables, >> that was default (page) to row. >> This solved our troubles on almost every databases. >> >> Regards. >> >> Alexandre Marini >> >> Tecnologia da Informação - DBA >> >> SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix >> >> IIUG Member >> >> <http://www.iiug.org>; >> >> Clifton Bean escreveu: >> >>> IDS 11.50.FC5 AIX 5.3 >>> >>> My current client has a lot of work to do on their indices, a lot of it >>> involving embedded primary keys. I would love to migrate these keys (and >>> others) into dbspaces with at least a 16k pagesize to take advantage of >>> > the > >>> more rows per page, less pages means less levels and, most of all, the >>> improvement of such an index upon query performance. >>> >>> What has me worried about doing this is the effect upon session memory and >>> > a > >>> possible higher number of deadlocks due to the need for the instance to >>> > lock > >>> these pages while peforming those pesky inserts, updates and deletes from >>> >> said >> >>> index pages. >>> >>> Has anyone else migrated their indices to larger page size dbspaces, >>> experimentally or within production? If so, would you mind sharing your >>> experience with doing so? >>> >>> Thanks in advance. >>> >>> Clifton >>> >>> _________________________________________________________________ >>> Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. >>> http://clk.atdmt.com/GBL/go/171222985/direct/01/ >>> >>> >>> >>> > ******************************************************************************* > >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> >>> >>> >> >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > _________________________________________________________________ > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. > http://clk.atdmt.com/GBL/go/171222985/direct/01/ > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
I think the answer to this question is...it depends. We looked at 16K page size (also looked at 4K, 6K, 8K, 12K and 14K) for some of our busiest OLTP indexes with the thought that a larger page size = more index entries per page/btree node = fewer index levels = better performance. It turns out that even though we reduced the number of index levels from 6 with a 2K page size to 4 with a 16K page size the smaller page size with more levels outperformed a larger page size with fewer levels. I think this was result of the cost of an I/O for a buffer cache miss when reading an index. With a 16K page size if you need to read an index page from disk because it is not in the buffers you need to read 8x as much data from disk than if you were using 2K pages, so your penalty for a cache miss with 16K page sizes is greater. This problem is magnified when your working data set is small relative to the size of the table and your data access is evenly distributed across the table. You will be bringing index leaf data in 16K at a time and will have a lower chance of a cache hit on your next read than if you were bringing data in at a more selective 2K. All of this assumes you don't have enough memory to keep the 16K index pages you need in the buffers. If you can prevent Informix from going to disk for index reads then the cache miss penalty doesn't exist and fewer index levels should give a little bit of a performance boost. I say a little bit because even with more levels the root node (level 1) and a lot of intermediate nodes (levels 2 to n-1) will have good chance of being in cache because they are accessed frequently. This comes into play when you're wondering why reducing the number of levels from 6 to 4 didn't improve performance by as much as you thought it would. At least that is what happened when I did my testing. YMMV, it depends and all that stuff. Andrew
Well ... perhaps I can skin this <insert your least favorite creature> another
way.
Has anyone tried the Dynamic Runtime Index Compression feature added in IDS
11.50.XC2?
Is anyone using this feature setting up OAT compression tasks at engine
startup using the following syntax:
execute function task("set index compression", table_partnum,compression_level (low, medium, or high))?
Thanks, as always, in advance.
Clifton
> To: ids@iiug.org
> From: aford@networkip.net
> Subject: Re: To 16K My Indices ... Or Not to 16K Them, .... [18090]
> Date: Thu, 12 Nov 2009 11:48:27 -0500
>
> I think the answer to this question is...it depends.
>
> We looked at 16K page size (also looked at 4K, 6K, 8K, 12K and 14K) for some
> of our busiest OLTP indexes with the thought that a larger page size = more
> index entries per page/btree node = fewer index levels = better performance.
>
> It turns out that even though we reduced the number of index levels from 6
> with a 2K page size to 4 with a 16K page size the smaller page size with more
> levels outperformed a larger page size with fewer levels.
>
> I think this was result of the cost of an I/O for a buffer cache miss when
> reading an index. With a 16K page size if you need to read an index page from
> disk because it is not in the buffers you need to read 8x as much data from
> disk than if you were using 2K pages, so your penalty for a cache miss with
> 16K page sizes is greater.
>
> This problem is magnified when your working data set is small relative to the
> size of the table and your data access is evenly distributed across the
table.
> You will be bringing index leaf data in 16K at a time and will have a lower
> chance of a cache hit on your next read than if you were bringing data in at
a
> more selective 2K.
>
> All of this assumes you don't have enough memory to keep the 16K index pages
> you need in the buffers. If you can prevent Informix from going to disk for
> index reads then the cache miss penalty doesn't exist and fewer index levels
> should give a little bit of a performance boost.
>
> I say a little bit because even with more levels the root node (level 1) and
a
> lot of intermediate nodes (levels 2 to n-1) will have good chance of being in
> cache because they are accessed frequently. This comes into play when you're
> wondering why reducing the number of levels from 6 to 4 didn't improve
> performance by as much as you thought it would.
>
> At least that is what happened when I did my testing. YMMV, it depends and
all
> that stuff.
>
> Andrew
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Hotmail: Trusted email with Microsofts powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141664/direct/01/