CODEWITHSUNDEEP
informixdatabase-administration
Nwn sn
Nwn sn

Reputation: 1

How to determine which values to put as extent size and next size in informix table creation?

Suppose I'm going to create following table in an Informix database.

create table table_name 
  (
    col1 date,
    col2 smallint,
    col3 char(12),
    col4 char(12),
    col5 char(12),
    col6 integer,
    col7 integer,
    col8 datetime year to second,
    col9 datetime year to second,
    col10  datetime year to second,
    col11  datetime year to second,
    col12  datetime year to second,
    col13 CHAR(1),
    col14 CHAR(1),
    col15 CHAR(1)
  ) in db_space extent size ? next size ? lock mode row;

Suppose there are three scenarios going to happen.

1.reference table, 100000 records going to be stored in this table

2.data table, daily adding record count is 1000

3.data table, monthly adding record count is 100000

I'm confusing about how to determine the extent sizes according to above scenarios. what is the logic behind that?

Upvotes: 0

Views: 1539

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 754410

The first step is adding up the sizes of the columns to establish how much space each row uses — or create a dummy table and get the size from the system catalog. That gives a size of 93 bytes per row. There is also some slot overhead and page overhead; you can get about 20 rows per 2 KiB page, and just over 40 rows per 4 KiB page. For practical mental arithmetic, treat the records as 100 bytes each, ignoring overheads, and assuming 2000 or 4000 bytes per page.

Scenario 1: 100,000 rows, minimal growth.

  • You need about 5,000 pages if your system uses 2 KiB pages; about 2,500 if it uses 4 KiB pages, but since you specify extent sizes in KiB, the answer is essentially the same — you need a first extent size of 10,000 (KiB). Since the table won't grow, the next extent size isn't critical; use 1,000 KiB. I've not accounted for any indexes; those also need space.

Scenario 2: 1,000 rows per day.

  • Each day will need about 100 KB of data space. You don't say anything about records being deleted, so we'll assume continuous growth. Informix employs an extent size doubling strategy to keep the number of extents down, so you need to decide on how big your table will get. Using 400 days per year (we're talking round numbers here) and 100 KB per day, you'll need 40 MB per year. In this case, I'd think in terms of using 40,000 for first and next extent size. That might be bigger than you want — you could tune it downwards, but I'd suggest at least 10,000 for the first extent, and the next size roughly the same.

Secnario 3: 100,000 records per day.

  • Here, you're working with 10 MB per day, 4 GB per year. You'll probably want to think about fragmenting this using a range partitioning scheme based on date so that you can remove (old) fragments in due course. However, assuming you store at least a year's worth in total, I'd be thinking in terms of first extent size of 300,000 (300 MB, about a month's worth), and the next extent size the same.

In general, don't get hung up on the extent sizing. Because of extent size doubling, if you choose too small a number, Informix compensates over time by allocating larger extents automatically. If you find the next size is too big, you can alter the next size. However, none of these sizes is very big — 4 GB per year is pocket change in the server storage world these days, even if that's SSD instead of spinning disk. You'd need to be in the terabytes per year range before you need to worry too much. It is worth paying attention to the space, but it is not worth spending much more arithmetical calculation than I've done.

Don't forget your indexes.

Some references to IBM documentation for Informix 12.10:

Upvotes: 1

Related Questions