Extend maximum database page size to 32KB [CORE2192]

[firebird-automations]

Submitted by: Sean Leyne (seanleyne)

Block progress on JDBC468

Votes: 1

With the evolution of disk subsystems, the ability to define the database page size larger than 16KB, to match RAID and Filesystem block sizes, would be beneficial.

Commits: 1706564 8186c1e FirebirdSQL/fbt-repository@1236d83

[firebird-automations]

Commented by: @hvlad

Note, big page size may be good for table scan's but is very bad for writes and random access.
Much more value is in implementation of (asyncronous) prefetch and page clustering to make prefetch more effective.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Vlad,

The prefetch and page clustering are certainly good things, but if the OS and hardware are reading/writing large blocks, it makes sense for the database page to take advantage of this.

Further, when Firebird supports a record size > 64KB, then a large page size would reduce the number of page reads which would be required for each row.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Commented by: @dyemanov

32KB page size is implemented, but disabled yet. Sean, do you volunteer to test?

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Sure, but what type/level of testing would be appropriate?

[firebird-automations]

Commented by: @dyemanov

I've adjusted the ticket title to limit request with 32KB page size. Bigger values are harder to implement and we need to evaluate 32KB pages properly before making any decision about bigger page sizes.

[firebird-automations]

Modified by: @dyemanov

summary: Support database page sizes greater than 16KB => Extend maximum database page size to 32KB

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

[firebird-automations]

Commented by: @pavel-zotov

What about ability to create index with max key_size = page_size /4 - 9 ?
If new maximal page_size = 32768 then we should be able to create index with 32768 / 4 - 9 = 8183 bytes.

But currently one can not to operate with key length >= 4092 byte:

SQL> create database 'localhost/3400:c:\temp\test32k.fdb' page_size 32768;
SQL> set list on;
SQL> select mon$page_size from mon$database;

MON$PAGE_SIZE 32768

SQL> commit;
SQL> recreate table testa(s varchar(4092) unique using index testa_s);
SQL> recreate table testb(s varchar(4093) unique using index testb_s);
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-RECREATE TABLE TESTB failed
-key size too big for index TESTB_S

=====

SQL> show version;
ISQL Version: WI-T4.0.0.171 Firebird 4.0 Unstable
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T4.0.0.171 Firebird 4.0 Unstable"
Firebird/Windows/Intel/i386 (remote server), version "WI-T4.0.0.171 Firebird 4.0 Unstable/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T4.0.0.171 Firebird 4.0 Unstable/tcp (csprog)/P13"

on disk structure version 12.0

[firebird-automations]

Commented by: @dyemanov

Pavel: fixed now, thanks.

[firebird-automations]

Commented by: @pavel-zotov

Some kind of bug is still with us...
Consider attempt to create long-key index based on single text field with charset = UTF8, using following three ways:
1) when field is defined via build-in character type;
2) when field is defined via domain, and domain is based on build-in character type;
3) when field is defined via domain, domain is based on COLLATION and collation has NO any attributes, i.e. final datatype will require the same number of octets per character as "1)" and "2)" -- i.e. is 4 octets.

While "1" and "2" work fine, way "3" fails:

SQL> create database 'localhost/3400:/var/db/fb40/tmp40.fdb' page_size 32768;
SQL> recreate table test1(s varchar( 2045 ) character set utf8 unique using index test1_s);
SQL> create domain dm_2045_non_coll as varchar( 2045 ) character set utf8;
SQL> recreate table test2(s dm_2045_non_coll unique using index test2_s);
SQL> create collation c4 for utf8 from unicode;
SQL> create domain dm_2045_collated as varchar( 2045 ) character set utf8 collate c4;
SQL> commit;

SQL> recreate table test3(s dm_2045_collated unique using index test3_s);
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-RECREATE TABLE TEST3 failed
-key size too big for index TEST3_S
SQL> rollback;

SQL> recreate table test3b( s1 dm_2045_non_coll, s2 dm_2045_collated );
SQL> create index test3b_s1 on test3b(s1);
SQL> create index test3b_s2 on test3b(s2);
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-CREATE INDEX TEST3B_S2 failed
-key size too big for index TEST3B_S2
SQL> rollback;
SQL>
SQL> set list on;
SQL> select c.rdb$collation_name,c.rdb$collation_id,c.rdb$character_set_id,c.rdb$collation_attributes,c.rdb$specific_attributes,s.rdb$character_set_name, s.rdb$bytes_per_character from rdb$collations c join rdb$character_sets s using(rdb$character_set_id) where c.rdb$collation_name containing 'C4';

RDB$COLLATION_NAME C4
RDB$COLLATION_ID 126
RDB$CHARACTER_SET_ID 4
RDB$COLLATION_ATTRIBUTES 1
RDB$SPECIFIC_ATTRIBUTES 1d:790
COLL-VERSION=49.192.5.41
RDB$CHARACTER_SET_NAME UTF8
RDB$BYTES_PER_CHARACTER 4

[firebird-automations]

Commented by: @asfernandes

Key for each character in UNICODE collation can have 6 bytes.

Key for each character in UTF8 without collate can have 4 bytes.

[firebird-automations]

Commented by: @pavel-zotov

> Key for each character in UNICODE collation can have 6 bytes.

is it possible to create such unicode collation that one char will require 8 (eight) octets ?

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @dyemanov

issuetype: New Feature [ 2 ] => Improvement [ 4 ]

[firebird-automations]

Modified by: @mrotteveel

Link: This issue block progress on JDBC468 [ JDBC468 ]