AUTOLOCATE

Introduction

AUTOLOCATE is a very interesting and relatively new Informix feature and one that I believe has been so far undersold. Introduced in 12.10.xC3, the Informix manual has this to say about it:

Use the AUTOLOCATE configuration parameter to control whether the database server controls the location of new databases, indexes, and tables and the fragmentation of those tables. If you set the AUTOLOCATE configuration parameter to a positive integer, the database server performs the following tasks:

• Stores new databases for which you do not specify a location in the optimal dbspace instead of in the root dbspace. By default, all dbspaces except dbspaces that are dedicated to tenant databases are available. However, you can control the list of available dbspaces.
• Fragments new tables by round-robin, where the number of fragments is equal to the value of the AUTOLOCATE configuration parameter.
• Adds more table fragments as the table grows.

I had ignored it, largely because on first reading it appears to be a feature aimed at newbies to Informix. Our systems are mostly well-established and decisions regarding dbspaces and fragmentation have already been made by DBAs sweating over a keyboard driving a spreadsheet (or script).

In fact it has a few highly significant and undocumented facets that address a number of fundamental storage issues. In a nutshell if you wanted a table to have the potential to grow as large as possible you needed to be aware of the following (taken from IBM support document, What is the maximum number of pages for a table or fragment? What is the maximum number of rows per page?):

• 3 bytes are used to store the page number a maximum of 16,777,215 pages can be used in one table.
• 1 byte is used to store the slot number, a single page can have at most 255 slots/rows.
• The maximum number of rows in a table or fragment is 4,278,189,825.

The last limitation is the combination of the first two at their limits and doesn’t need separate consideration, however all of this combined means:

• any table larger than 32, 64, 96, 128, 160, 196 or 256 GB, depending on the page size (2-16 kB), needs to be partitioned.
• tables with narrow rows cannot benefit from the larger page sizes without wasting storage space.
• when defining an interval for a table partitioned by range, the interval must be not so large that it allows fragments to reach the page limit.
• tables partitioned by round-robin are limited to the above size limits multiplied by the number of fragments you created.

The fundamental limits from the support document are still there and probably will be for some time to come. However, AUTOLOCATE makes it much much easier to deal with the most significant of them, as we will see.

AUTOLOCATE in action

Firstly let’s imagine a table using a 2 kB page size, fragmented by round robin with two partitions. Its maximum size is 64 GB. The term round-robin refers to how data gets inserted into the table. It will be in the fashion 1-2-1-2-1-2 etc. If the table had four partitions it would be 1-2-3-4-1-2-3-4-1-2-3-4. If your table starts from empty and you insert data over a period of time, all the partitions will contain the same number of rows. What happens if my table with two partitions approaches 64 GB, let’s say 60 GB, and I cannot delete any data and must keep inserting more? Well I can add another partition or two. (Even another ten.) My table will now will fill up 1-2-3-4-1-2-3-4 etc. However two partitions have 60 GB and two are empty so I am still 4 GB away from two of the partitions filling; it is just going to take twice as long to get there.

Let’s see how AUTOLOCATE can help by creating a table for testing and filling it with data.

Create table and populate:
drop table if exists rr_table;

create table rr_table (
myid bigserial not null,
mydata char(40) not null
) with rowids fragment by round robin in 2kb_dbspace1, 2kb_dbspace2;

create table source_data (
text_field varchar(40)
);

insert into source_data values ('r4dR3leer4IWTLNo5rZpYS9HTGrQngjcDp5FcQwn');
insert into source_data values ('FtIgPPVDPvnqlSf6QNSkkIDv7GvXIrN2L22w4x7K');
insert into source_data values ('N5VtAmIxSl89lt3O34hshvCktWjYOiMdGJjwwjVe');
insert into source_data values ('Kb13xQcI5IZqFjC0bu4u4zqK1JUSvZKjmGD1OZ3K');
insert into source_data values ('5XhqJQ1lm2LYKzatCngXrEJMEDGF8Qw5gxsetqTx');
insert into source_data values ('C1EXpTrmk28F2ETnvoK1QhF8v3gA4iBciaQw7NtW');
insert into source_data values ('7OvpiycZRjTFPEzk6FM2hJsORWVEAUhOM2O211tr');

drop procedure if exists insert_data;
create procedure insert_data ()

loop
begin work;
insert into rr_table (mydata) select * from source_data;
commit;
end loop;

end procedure;

execute procedure insert_data();

While this was running to fill up my table, I thought I would experiment with a different number of threads and values for AUTOLOCATE. Each result below is the average of five measurements taken over ten seconds. AUTOLOCATE can be changed dynamically and immediately affects running sessions. For the tests I created multiple source tables to rule out contention there.

Number of sessions inserting into two-way round-robin table, no indices	Rows inserted per second AUTOLOCATE 0	Rows inserted per second AUTOLOCATE 1
1	85 300	90 300
2	94 400	93 400
3	110 200	99 700
4	117 200	100 500
5	121 400	96 000
6	120 100	91 300
7	115 800	88 800
8	109 000	87 000

(For values of AUTOLOCATE greater than 1 the results were very similar to 1 so I haven’t bothered to include any in the table.)

So we can see that AUTOLOCATE does have an effect on how data are inserted: it changes the algorithm for insertion into tables fragmented by round-robin. If the parameter is set to any non-zero value the partition which is most empty will receive the rows. This means it isn’t really round-robin any more. However, when populating a table from empty the behaviour appears to be more or less the same, however in my example once my two new partitions are added they will receive all the new rows.

What happens in the situation where one or more fragments reaches the page limit? Without AUTOLOCATE set the database engine will continue to insert data into fragments with space, however maximum insert performance will reduce by a factor of about two hundred. This is presumably because it checks bitmap pages in the full fragments each time their turn comes in case any data were deleted by other processes.

However, with AUTOLOCATE set, with the new algorithm (most empty fragment gets priority) this situation does not arise unless all fragments are full.

Furthermore with AUTOLOCATE, when all fragments are full the database engine will automatically add more. One thing to be aware of is if, say, you had a R-R table with eight fragments and they all fill, if AUTOLOCATE is set to just 1, the engine will add just one fragment so you are no longer writing into multiple fragments. However the Informix manual section Managing automatic location and fragmentation explains this and how to control the dbspace used very well.

With AUTOLOCATE set to 0, I eventually hit the dreaded 271: Could not insert new row into the table.

The online log reports it too:
WARNING: partition 'mydbname:informix.rr_table': no more pages

Adding two more fragments with ALTER FRAGMENT ADD I can now test the situation where two fragments are full and two are empty and show the basis for the two-hundred times speed reduction claim above. I suspect this will be highly dependent on the number of bitmap pages to scan and how these pages are marked. In this test, if AUTOLOCATE is 0 you’ll continue to periodically see the no more pages message in the online log.

Number of sessions inserting into four-way round-robin table with two full fragments, no indices	Rows inserted per second AUTOLOCATE 0	Rows inserted per second AUTOLOCATE 1
1	390	74 220
2	588	83 300
3	741	91 900
4	919	96 100

There is a bit of a drop in performance over the initial results with a two-way R-R table, even when AUTOLOCATE is a non-zero value. An assumption you could make is that this is because there are more fragments to check the size of before inserting but I have not tested or verified this.

So you can see this is extremely useful if your round-robin fragmented tables are getting large: just add more fragments and ensure AUTOLOCATE is on; or even if you’re brave let the engine do this for you. I say brave because you should be aware that our testing found defect IT34090 which is where this process fails for tables with rowids and varchar or lvarchar columns. I hope it will be fixed in 12.10.xC15 and 14.10.xC5.

Bored of calculating the optimum page sizes for your tables?

I also mentioned that narrow tables cannot benefit from large page sizes because 255 times the row size can often come to under the page size. This means a careful DBA needs to calculate the optimum page size for any given table. There will usually be a range of page sizes that will be suitable and two approaches I have used are to take the largest that will not waste space or, if you perhaps intend to use table compression, assume your rows are two to three times less wide than they are and apply the same logic (since compression increases the number of rows per page possible but does not change the 255 rows/page limit).

However you can now let AUTOLOCATE do this job for you. The Informix manual states: stores new databases for which you do not specify a location in the optimal dbspace instead of in the root dbspace. “Optimal” is not defined but I hope it would take into account all the considerations above.

It’s quite easy to start experimenting by providing the database engine with a list of dbspaces of different page sizes and see where your data gets placed. (You will need to insert at least one row for an extent to actually be created.)

EXECUTE FUNCTION task("autolocate database", "my_dbname", "2kb_dbspace1,4kb_dbspace1,6kb_dbspace1,8kb_dbspace1,10kb_dbspace1,12kb_dbspace1,14kb_dbspace1,16kb_dbspace1");

Selected results are in the tables below. My initial expectation was that there would be clear stepping points based on the row size where the algorithm would select the next page size up and, while this is definitely an overall trend, the inner workings of the algorithm were difficult to ascertain when I only examined ten or so tables. To try and see the patterns more clearly I created around 1500 tables with different schemas and examined the page size selected versus the row size. For the test all possible page sizes were made available but your system may restrict what can be used.

The first result I found is that the page size selected seems to entirely depend on the row size as reported in systables; it does not seem to matter what columns are used, specifically whether there are any variable length columns.

The formula for calculating rows per page is rows per page = trunc(pageuse/(rowsize + 4)) where pageuse is the page size in bytes minus 28 (source Informix manual, Estimating tables with fixed-length rows). We can use this to calculate the minimum row size that can be used for each page size without wasting space.

Page size (bytes)	Minimum row size to fill page based on formula	Smallest row size at which AUTOLOCATE will select this page size based on empirical evidence	Largest row size at which AUTOLOCATE will select this page size based on empirical evidence
2048	4	1	32074
4096	12	43	2021
6144	20	85	1515
8192	28	81	1615
10240	36	137	4591
12288	44	133	2403
14336	52	142	4574
16384	60	224	3187

For the largest row size, I didn’t exhaustively try every row size; these are just what is used in the database schema I loaded.

We can maybe begin to see what the engine is trying to do. There is clearly a smallest row size for which each page size will be chosen and this gives at least 2.7x head room, presumably to allow compression to be effective.

At the extreme, there is a seemingly rogue result for the table with a 32074 row size (comprised of a serial, int, varchar(64), char(32000) and char(1) columns) being put in a 2 kB dbspace. (I would have thought 16 kB would suit better, minimising the number of pages each row spans.) Some simple math(s) this suggests that minimising space may be the overriding consideration as the following examples show.

• 4096 page size: 2 x (2021 + 4) + 28 is 4078.
• 6144 page size: 4 x (1515 + 4) + 28 is 6104.
• 8192 page size: 5 x (1615 + 4) + 28 is 8123.
• 10240 page size: 2 x (4591 + 4) + 28 is 9218 (not such a good fit).
• 12288 page size: 5 x (2403 + 4) + 28 is 12035.
• 14336 page size: 3 x (4574 + 4) + 28 is 13762.
• 16384 page size: 5 x (3187 + 4) + 28 is 15973.

If we apply the same logic elsewhere what do we get? Looking at a range of ten values minimising the space wasted does indeed seem to be a goal.

Row size	Page size selected (kB)	Bytes wasted 2 kB	Bytes wasted 4 kB	Bytes wasted 6 kB	Bytes wasted 8 kB	Bytes wasted 10 kB	Bytes wasted 12 kB	Bytes wasted 14 kB	Bytes wasted 16 kB
133	12	102	95	88	81	74	67	60	53
134	8	88	66	44	22	0	116	94	72
135	6	74	37	0	102	65	28	130	93
136	4	60	8	96	44	132	80	28	116
137	10	46	120	53	127	60	134	67	0
138	6	32	92	10	70	130	48	108	26
139	8	18	64	110	13	59	105	8	54
140	2	4	36	68	100	132	20	52	84
141	4	135	8	26	44	62	80	98	116
142	14	122	126	130	134	138	142	0	4

I did these calculations in Excel so they must be right, yeah? Assuming some of the possible page sizes are just too large to be considered, the only thing this theory can’t explain is why for a 137 row size the engine chose a 10 kB page size over a 2 kB one.

What is clear is that using the largest page size possible, thus reducing the number of I/O requests is not a feature of AUTOLOCATE. Perhaps for many systems this doesn’t help anyway: in the OLTP world if you want to read a row and need to read in one 2 kB page or one 16 kB page to do so, which is actually more efficient on memory and the buffer pools?

Advertisement

---