IDS Forum

Index page size - test result....

Posted By: Cesar Inacio Martins
Date: Friday, 20 August 2010, at 8:44 a.m.

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 test
check : "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 test
check : "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

| MT global info:
| sessions threads vps lngspins
| 2 34 13 0
|
| sched calls thread switches yield 0 yield n yield forever
| total: 24567 6544 19995 2401 0
| per sec: 45 45 0 39 0
|
| Virtual processor summary:
| class vps usercpu syscpu total
| cpu 1 158.21 0.44 158.65
| aio 4 0.00 0.00 0.00
| lio 1 0.00 0.00 0.00
| pio 1 0.00 0.00 0.00
| adm 1 0.00 0.01 0.01
| soc 1 0.00 0.00 0.00
| 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 158.21 0.45 158.66

OUTPUT OF THE STORED PROCEDURE EXECUTED.

| (constant) unnamed_col_1
|
| 1 25.000
| 2 26.000
| 3 25.000
| 4 26.000
| 5 25.000
| 6 26.000

Messages In This Thread

• Index page size - test result....
  Cesar Inacio Martins -- Friday, 20 August 2010, at 8:44 a.m.
  • Re: Index page size - test result....
    Andrew Ford -- Friday, 20 August 2010, at 12:46 p.m.
    • Re: Index page size - test result....
      Cesar Inacio Martins -- Monday, 23 August 2010, at 6:36 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.