Re: [firebird-support] What is the optimum pageSize?
14.11.2019 13:41, cte...@gmail.com [firebird-support] wrote: > Assume I have two fields => "stFixed CHAR(10)" and "stVar VARCHAR(8191) > CHARACTER SET > UTF8" --- what is the optimum pageSize? Optimum page size doesn't depend on data but on application usage. I can repeat once again: make page size as big as your Firebird version allow you. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] What is the optimum pageSize?
What is the max page size for Firebird 3? Also, could you clarify a page? Does each record consume a page, or are could there be multiple records on a page? ` On Thu, Nov 14, 2019 at 8:09 AM Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 14.11.2019 13:41, cte...@gmail.com [firebird-support] wrote: > > Assume I have two fields => "stFixed CHAR(10)" and "stVar VARCHAR(8191) > CHARACTER SET > > UTF8" --- what is the optimum pageSize? > >Optimum page size doesn't depend on data but on application usage. > >I can repeat once again: make page size as big as your Firebird version > allow you. > > > -- >WBR, SD. > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > >
Re: [firebird-support] What is the optimum pageSize?
14.11.2019 23:30, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > What is the max page size for Firebird 3? 16 kilobytes. > Also, could you clarify a page? Quoting https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15 "The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages." Firebird definition of database page is the same. > Does each record consume a page, or are could there be multiple records on a > page? Multiple records can be on one data page or one record can be split to several database pages depending on record's size. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] What is the optimum pageSize?
>> Does each record consume a page, or are could there be multiple records on a >> page? >Multiple records can be on one data page or one record can be split to > several database > pages depending on record's size. What is the max record size in FB3 (in bytes)? I search for this information without success. -- kind regards jerzy
Re: [firebird-support] What is the optimum pageSize?
15.11.2019 11:58, jerz...@o2.pl [firebird-support] wrote: > What is the max record size in FB3 (in bytes)? > I search for this information without success. https://firebirdsql.org/en/firebird-technical-specifications/ -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] What is the optimum pageSize?
This is very useful info!!! On Fri, Nov 15, 2019 at 5:13 AM Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 15.11.2019 11:58, jerz...@o2.pl [firebird-support] wrote: > > What is the max record size in FB3 (in bytes)? > > I search for this information without success. > >https://firebirdsql.org/en/firebird-technical-specifications/ > > > -- >WBR, SD. > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > >
Re: [firebird-support] What is the optimum pageSize?
But only for FB 2.5, not for FB3! My question was about FB3. Where is info about new versions? > This is very useful info!!! On Fri, Nov 15, 2019 at 5:13 AM Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: 15.11.2019 11:58, jerz...@o2.pl [firebird-support] wrote: >> What is the max record size in FB3 (in bytes)? >> I search for this information without success. > https://firebirdsql.org/en/firebird-technical-specifications/ -- WBR, SD. -- kind regards jerzy
Re: [firebird-support] What is the optimum pageSize?
15.11.2019 22:09, jerz...@o2.pl [firebird-support] wrote: > But only for FB 2.5, not for FB3! > My question was about FB3. Where is info about new versions? In Release Notes for these versions. Maximum size of record was not changed. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] What is the optimum pageSize?
>> But only for FB 2.5, not for FB3! >> My question was about FB3. Where is info about new versions? >In Release Notes for these versions. >Maximum size of record was not changed. This is a very big limitation, a few larger char fields with multibyte coding and we get an exception. I don't understand why this limitation hasn't been removed. And in FB4 it is also 64 kB? -- kind regards, jerzy
Re: [firebird-support] What is the optimum pageSize?
On 16-11-2019 09:33, jerz...@o2.pl [firebird-support] wrote: > >>> But only for FB 2.5, not for FB3! >>> My question was about FB3. Where is info about new versions? > >> In Release Notes for these versions. >> Maximum size of record was not changed. > > > This is a very big limitation, a few larger char fields with multibyte coding > and we get an exception. I don't understand why this limitation hasn't been > removed. Because it is hard to do. A lot of internals in Firebird and its clients rely on this maximum size (hint: the number itself fits in a 2-byte unsigned integer). It is not like you just need to update a MAX_ROW_SIZE constant and it is changed. Consider that PostgreSQL has a limit of around 8KB (with its default page size). However, PostgreSQL has the advantage that variable length fields can be automatically stored out-of-line (ie basically as a blob, but that is transparent to the user). > And in FB4 it is also 64 kB? Yes. Mark -- Mark Rotteveel
Re: [firebird-support] What is the optimum pageSize?
16.11.2019 09:33, jerz...@o2.pl [firebird-support] wrote: > a few larger char fields with multibyte coding and we get an exception. An entity with several large text attributes is unusual. Can you provide an example? -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] What is the optimum pageSize?
On Sat, 16 Nov 2019 10:29:44 +0100, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: >>> In Release Notes for these versions. >>> Maximum size of record was not changed. >> This is a very big limitation, a few larger char fields with multibyte >> coding and we get an exception. I don't understand why this limitation >> hasn't been removed. > Because it is hard to do. A lot of internals in Firebird and its clients > rely on this maximum size (hint: the number itself fits in a 2-byte > unsigned integer). It is not like you just need to update a MAX_ROW_SIZE > constant and it is changed. I understand it's not trivial. But that's why there are new versions like 3, 4 or now 5 to avoid such limitations. > Consider that PostgreSQL has a limit of around 8KB (with its default > page size). However, PostgreSQL has the advantage that variable length > fields can be automatically stored out-of-line (ie basically as a blob, > but that is transparent to the user). However, according to the documentation "row size" is much larger. But maybe I am misreading this information. https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F -- kind regards jerzy
Re: [firebird-support] What is the optimum pageSize?
17.11.2019 12:22, jerz...@o2.pl [firebird-support] wrote: > > Because it is hard to do. A lot of internals in Firebird and its clients > > rely on this maximum size (hint: the number itself fits in a 2-byte > > unsigned integer). It is not like you just need to update a MAX_ROW_SIZE > > constant and it is changed. > > I understand it's not trivial. But that's why there are new versions like 3, > 4 or now 5 to > avoid such limitations. Feel free to prepare a patch and create pull request. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] What is the optimum pageSize?
> On Nov 17, 2019, at 6:22 AM, jerz...@o2.pl [firebird-support] > wrote: > > On Sat, 16 Nov 2019 10:29:44 +0100, Mark Rotteveel m...@lawinegevaar.nl > [firebird-support] wrote: > In Release Notes for these versions. Maximum size of record was not changed. > > >>> This is a very big limitation, a few larger char fields with multibyte >>> coding and we get an exception. I don't understand why this limitation >>> hasn't been removed. > >> Because it is hard to do. A lot of internals in Firebird and its clients >> rely on this maximum size (hint: the number itself fits in a 2-byte >> unsigned integer). It is not like you just need to update a MAX_ROW_SIZE >> constant and it is changed. > > I understand it's not trivial. But that's why there are new versions like 3, > 4 or now 5 to avoid such limitations. > >> Consider that PostgreSQL has a limit of around 8KB (with its default >> page size). However, PostgreSQL has the advantage that variable length >> fields can be automatically stored out-of-line (ie basically as a blob, >> but that is transparent to the user). Perhaps a better solution for Firebird would be a similar text type that holds arbitrary length strings. > > However, according to the documentation "row size" is much larger. But maybe > I am misreading this information. > > https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F > > -- > kind regards > jerzy > > > > > Posted by: jerz...@o2.pl > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] What is the optimum pageSize?
On 2019-11-17 12:22, jerz...@o2.pl [firebird-support] wrote: > On Sat, 16 Nov 2019 10:29:44 +0100, Mark Rotteveel > m...@lawinegevaar.nl [firebird-support] wrote: >> Because it is hard to do. A lot of internals in Firebird and its >> clients >> rely on this maximum size (hint: the number itself fits in a 2-byte >> unsigned integer). It is not like you just need to update a >> MAX_ROW_SIZE >> constant and it is changed. > > I understand it's not trivial. But that's why there are new versions > like 3, 4 or now 5 to avoid such limitations. That is because there is a cost to everything, manpower is limited, and other things are considered more important to do. >> Consider that PostgreSQL has a limit of around 8KB (with its default >> page size). However, PostgreSQL has the advantage that variable length >> fields can be automatically stored out-of-line (ie basically as a >> blob, >> but that is transparent to the user). > > However, according to the documentation "row size" is much larger. But > maybe I am misreading this information. > > https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F That is because that row size takes the out-of-line (toast) fields (max 1GB per field) into account. Out-of-line fields are similar to blobs, but they are less intrusive because - as far as I know - within the protocol they are communicated with the rowdata, instead of having to fetch them individually like you need to do with Firebird. Personally, I would prefer changes that make VARCHAR (and VARBINARY) practically unlimited and have Firebird do similar tricks like PostgreSQL to automatically store as a blob if a value is too long, but continue to communicate the data in-band in the protocol itself. Mark
