Index page size - test result....

Hi All, I always read here about the vantages to use indexes in bigger pages, where have best performance. But I never seen/found any effective test. So I decide try one in small scale, what for me appear works and show the index with 16k page size run faster and consume less CPU resources. I'm sharing here what I do and like to know the opinion of your guys, if this tests is valid and if have other situation what this configuration maybe isn't the best way to work. What , where , when : - IDS 11.50 UC6W2 - Linux OpenSuse 11.5 - The Environment: table with 520.000 records, 1 index The index is a NCHAR(100) field. The content of this data is all filenames of my linux (loaded this table running a "find / ..." on my Linux) only filename , the path aren't include, so I have some duplicated values... The index is dropped and recreated for each test into 2k/16k dbspaces , always with buffer pool enough to keep all index on memory After recreate the index I execute a update statistics low drop distrib and high in this field/index... - The basic test is : Run sequentially 100.000 random fetch , key-only . Created a temp table (tp01) and load into 100.000 records (before I random this data with "sort -R" command) Created stored procedure with this code: | create procedure test_idxaccess () returning interval second(9) to fraction | define vNome char(100); | define vNomeIdx char(100); | define vHrI datetime year to fraction; | define vHrF datetime year to fraction; | SELECT DBINFO( 'UTC_TO_DATETIME', sh_curtime ) into vHrI FROM sysmaster:sysshmvals; | foreach select nome into vNome from tp01 | -- here this select use key-only on field "nome_arquivo" | select first 1 nome_arquivo into vNomeIdx from fs_full_newb where nome_arquivo = vNome ; | end foreach; | SELECT DBINFO( 'UTC_TO_DATETIME', sh_curtime ) into vHrF FROM sysmaster:sysshmvals; | return vHrF - vHrI; | end procedure Run this procedure 6 times select 1,* from table(test_idxaccess()) | select 1,* from table(test_idxaccess()) | union | select 2,* from table(test_idxaccess()) | union | select 3,* from table(test_idxaccess()) | union | select 4,* from table(test_idxaccess()) | union | select 5,* from table(test_idxaccess()) | union | select 6,* from table(test_idxaccess()) ; Monitored with SQLTRACE Profile zeroed before of the execution (onstat -z) Linux monitored with "top" command during the execution to check if no other process affect the execution. - The Results: ***************************** INDEX WITH PAGESIZE 2K ***************************** oncheck -pT output | Average Average | Level Total No. Keys Free Bytes | ----- -------- -------- ---------- | 1 1 4 1688 | 2 4 13 560 | 3 55 15 321 | 4 866 16 288 | 5 14430 36 314 | ----- -------- -------- ---------- | Total 15356 34 313 SELECT ... FROM sysindices |idxname ix_fsfullnewb2 |owner informix |tabid 381 |levels 5 |leaves 14430,00000000 |nunique 211036,0000000 |clust 437762,0000000 |nrows 520579,0000000 |indexkeys 2 [1] |pagesize 2048 onstat -g his after end the testcheck : "Buffer Read", "Est Cost", "Avg ROws Per Sec", "Total Time" | Database: myfs_db | Procedure Call Stack: | myfs_db:test_idxaccess() | | SELECT using tables [ fs_full_newb ] | | Iterator/Explain | ================ | ID Left Right Est Cost Est Rows Num Rows Partnum Type | 1 0 0 5 2 1 12582914 Index Scan | | Statement information: | Sess_id User_id Stmt Type Finish Time Run Time TX Stamp PDQ | 80 2001 SELECT 23:23:31 0.0002 131ea9aa 0 | | Statement Statistics: | Page Buffer Read Buffer Page Buffer Write | Read Read % Cache IDX Read Write Write % Cache | 0 6 100.00 0 0 0 0.00 | | Lock Lock LK Wait Log Num Disk Memory | Requests Waits Time (S) Space Sorts Sorts Sorts | 4 0 0.0000 0.000 B 0 0 0 | | Total Total Avg Max Avg I/O Wait Avg Rows | Executions Time (S) Time (S) Time (S) IO Wait Time (S) Per Sec | 55716 131.4583 0.0024 0.0248 0.000000 0.000000 4946.7987 | | Estimated Estimated Actual SQL ISAM Isolation SQL | Cost Rows Rows Error Error Level Memory | 0 0 0 0 0 CR 13704 | onstat -g glo , after finish the test check: CPU / total | MT global info: | sessions threads vps lngspins | 1 33 13 0 | | sched calls thread switches yield 0 yield n yield forever | total: 25733 7323 20397 3057 0 | per sec: 13 0 13 0 0 | | Virtual processor summary: | class vps usercpu syscpu total | cpu 1 167.15 0.39 167.54 | aio 4 0.00 0.01 0.01 | lio 1 0.00 0.00 0.00 | pio 1 0.00 0.00 0.00 | adm 1 0.00 0.02 0.02 | soc 1 0.00 0.01 0.01 | msc 1 0.00 0.00 0.00 | adt 1 0.00 0.00 0.00 | str 1 0.00 0.00 0.00 | fifo 1 0.00 0.00 0.00 | total 13 167.15 0.43 167.58 OUTPUT OF THE STORED PROCEDURE EXECUTED. | (constant) unnamed_col_1 | | 1 27.000 | 2 26.000 | 3 27.000 | 4 27.000 | 5 26.000 | 6 27.000 | | ***************************** INDEX WITH PAGESIZE 16K ***************************** oncheck -pT output |==================================== | Average Average | Level Total No. Keys Free Bytes | ----- -------- -------- ---------- | 1 1 13 15052 | 2 13 138 2395 | 3 1799 289 2701 | ----- -------- -------- ---------- | Total 1813 288 2706 SELECT ... FROM sysindices | |idxname ix_fsfullnewb2 |owner informix |levels 3 |leaves 1799,000000000 |nunique 211036,0000000 |clust 437762,0000000 |nrows 520579,0000000 |indexkeys 2 [1] |pagesize 16384 onstat -g his after end the testcheck : "Buffer Read", "Est Cost", "Avg ROws Per Sec", "Total Time" | Database: myfs_db | Procedure Call Stack: | myfs_db:test_idxaccess() | | SELECT using tables [ fs_full_newb ] | | Iterator/Explain | ================ | ID Left Right Est Cost Est Rows Num Rows Partnum Type | 1 0 0 1 2 1 22020100 Index Scan | | Statement information: | Sess_id User_id Stmt Type Finish Time Run Time TX Stamp PDQ | 80 2001 SELECT 23:17:11 0.0002 131d4d2a 0 | | Statement Statistics: | Page Buffer Read Buffer Page Buffer Write | Read Read % Cache IDX Read Write Write % Cache | 0 4 100.00 0 0 0 0.00 | | Lock Lock LK Wait Log Num Disk Memory | Requests Waits Time (S) Space Sorts Sorts Sorts | 4 0 0.0000 0.000 B 0 0 0 | | Total Total Avg Max Avg I/O Wait Avg Rows | Executions Time (S) Time (S) Time (S) IO Wait Time (S) Per Sec | 55716 125.6381 0.0023 0.0042 0.000000 0.000000 5064.2061 | | Estimated Estimated Actual SQL ISAM Isolation SQL | Cost Rows Rows Error Error Level Memory | 0 0 0 0 0 CR 13704 | onstat -g glo , after finish the test check: CPU / total@@

----- Original Message ----- From: "Cesar Inacio Martins" <cesar_inacio_martins@yahoo.com.br> To: <ids@iiug.org> Sent: Friday, August 20, 2010 7:44 AM Subject: Index page size - test result.... [20979] > Hi All, > > I always read here about the vantages to use indexes in bigger pages, > where > have best performance. > > But I never seen/found any effective test. So I decide try one in small > scale, > what for me appear works and show the index with 16k page size run faster > and > consume less CPU resources. > > I'm sharing here what I do and like to know the opinion of your guys, if > this > tests is valid and if have other situation what this configuration maybe > isn't > the best way to work. I did a similar test a few years ago and found that if you can cache all of the index pages and have the cache primed with all of the index pages so there is no I/O during the tests then larger page sizes outperform the smaller ones because you've reduced the number of levels in your index and it takes fewer CPU cycles to traverse the index tree. But... When I introduced I/O into the equation (i.e. the entire index can't fit in buffers or buffers also have to hold data pages) the samller page sizes outperformed the larger ones (for my tests, anyway). I found that this was caused by (at least) two things. 1) When I had to read an index page in from disk 16K takes longer to read than 2K (cache miss penalty increases) 2) I was doing this more often because my 16K pages contained more data that was not likely to be needed soon, leaving less memory to hold data I would need soon. A row that was recently accessed is typically more likely than a row that was not accessed recently to be accessed in the near future. 16K pages had the potential to conain more of these rows that I would not access than the more selective 2K page size (number of cache misses increase) It all boils down to a YYMV depending on your hardware resources, data access patterns, data size, etc. and I think the only way to know for sure if larger page sizes for index is a good or bad thing is to test it against your environment and application. Andrew

Hi Andrew, Thanks for your answer... Very interesting your results. Regards Cesar --- Em sex, 20/8/10, Andrew Ford <aford@networkip.net> escreveu: De: Andrew Ford <aford@networkip.net> Assunto: Re: Index page size - test result.... [20988] Para: ids@iiug.org Data: Sexta-feira, 20 de Agosto de 2010, 13:46 ----- Original Message ----- From: "Cesar Inacio Martins" <cesar_inacio_martins@yahoo.com.br> To: <ids@iiug.org> Sent: Friday, August 20, 2010 7:44 AM Subject: Index page size - test result.... [20979] > Hi All, > > I always read here about the vantages to use indexes in bigger pages, > where > have best performance. > > But I never seen/found any effective test. So I decide try one in small > scale, > what for me appear works and show the index with 16k page size run faster > and > consume less CPU resources. > > I'm sharing here what I do and like to know the opinion of your guys, if > this > tests is valid and if have other situation what this configuration maybe > isn't > the best way to work. I did a similar test a few years ago and found that if you can cache all of the index pages and have the cache primed with all of the index pages so there is no I/O during the tests then larger page sizes outperform the smaller ones because you've reduced the number of levels in your index and it takes fewer CPU cycles to traverse the index tree. But... When I introduced I/O into the equation (i.e. the entire index can't fit in buffers or buffers also have to hold data pages) the samller page sizes outperformed the larger ones (for my tests, anyway). I found that this was caused by (at least) two things. 1) When I had to read an index page in from disk 16K takes longer to read than 2K (cache miss penalty increases) 2) I was doing this more often because my 16K pages contained more data that was not likely to be needed soon, leaving less memory to hold data I would need soon. A row that was recently accessed is typically more likely than a row that was not accessed recently to be accessed in the near future. 16K pages had the potential to conain more of these rows that I would not access than the more selective 2K page size (number of cache misses increase) It all boils down to a YYMV depending on your hardware resources, data access patterns, data size, etc. and I think the only way to know for sure if larger page sizes for index is a good or bad thing is to test it against your environment and application. Andrew ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.