Informix

Expand all | Collapse all

Index creation strategy when autolocate is set

  • 1.  Index creation strategy when autolocate is set

    Posted 18 days ago
    Hello All,

    If autolocate is set to a value greater than 1, the table is created with a round-robin schema,
    but indexes cannot be created with a round-robin schema.
    I would like to know if there are any best practices on how to create indexes in a database that is set to autolocate database.
    e.g. It is better to create an index in dbspace, not in the location where the table is stored.

    And the autolocate setting ensures that tables and indexes are created in the optimal dbspace, is the page size only considered?
    Doesn't the usage of each dbspace or anything else be taken into account?

    Thanks,

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------


  • 2.  RE: Index creation strategy when autolocate is set

    Posted 12 days ago
    Hi,

    AUTOLOCATE is a bit unusual in that even when set to 1 it still creates a round-robin table, just with a single fragment. You can see this because the table itself has no part number and there will be an entry in sysfragments of fragtype 'T', which a non-partitioned tables does not have. Indeed a single fragment R-R table cannot be created any other way: specifying a single dbspace after a "fragment by round robin in" storage clause will result in a "860: The fragmented object definition does not specify enough fragments.".

    Doing some quick tests, it seems that if I set AUTOLOCATE to 2, create a table with no storage clause, then add indices also with no storage clause, the indices all get placed in the same dbspace as the first table fragment (evalpos 0).

    This is probably fine for a lot of situations but on a busy system you may wish to place the table and each of its indices in different dbspaces for reasons of improving parallelism in the engine.

    General advice on here seems to be to place indices in dbspaces with large page sizes (e.g. 16 kB), possibly so that index scans can use nice big block reads, but there is no practical limit on the number of pages an index can use so in general any page size can be justified. What is on disk is represented in the buffer pools: smaller page sizes can reduce buffer contention so there are arguments both ways.

    Individual DBSpace usage should not be a big concern although having data spread over more dbspaces can improve backup/restore times.

    You can also fragment your indices by expression but I think this is outside the scope of your question.

    Ben.

    ------------------------------
    Benjamin Thompson
    ------------------------------



  • 3.  RE: Index creation strategy when autolocate is set

    Posted 12 days ago
    Hi Ben, thank you for your thoughtful reply. I saw an article on your blog about testing the autolocate feature. It was very impressive.

    It is interesting that when the AUTOLOCATE setting is 1, the partnum becomes 0 when the table is created.

    The reason I decided to use AUTOLOCATE was to apply this function while upgrading the customer's old Informix 11.10 version.

    Currently, the customer environment consists of raw device chunks for each dbspace, and the user creates tables and indexes by designating the dbspace for each purpose. Customers believe that this configuration can distribute disk I/O.
    e.g. Classification of dbspaces by purpose (A/B/C)
    A: adbs1, adbs2, adbs3
    B: bdbs1,bdbs2,bdbs3
    C: cdbs1,cdbs2

    I have also recommended customers to configure the dbspace as a file system, but they seem to feel burdened by the possibility that the time the chunk size increases will cause a load on the service even a little.
    And customers still believe that raw devices are faster and more reliable than file systems.

    However, I think that the free space of each dbspace tends to be wasted when used as above.
    This is because the raw device size is fixed and each dbspace may not be used at all, or other dbspaces may run out of space relatively.

    So, if all dbspaces are made to the same size using raw devices and the database is set to autolocate (a value greater than 1), I was guessing that the dbspace space will be used efficiently and disk I/O will be evenly distributed.
    Of course, I think that historical tables should be composed of intervals or expression fragments.

    In conclusion, I haven't been able to convince customers to try the autolocate feature. So I'm planning to migrate to a configuration using almost the same dbspace and raw device as now. ;)

    Thanks for reading.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 4.  RE: Index creation strategy when autolocate is set

    Posted 11 days ago
    Hi,

    It's been a while since I have had to look at I/O balancing across devices as a primary consideration. I can't comment on whether that is right for this system but if you do use AUTOLOCATE it will not respect that set up unless you can align these classifications with different databases within the instance.

    Regarding space, as you are upgrading from 11.10, you might find Informix grabs larger extents than it did before.

    Ben.

    P.S. Raw is still marginally faster than direct I/O with a filesystem but the difference is very small and easily trumped by other considerations such as manageability.

    ------------------------------
    Benjamin Thompson
    ------------------------------