[firebird-support] Re: High write access on disk
+1 > Hey Alexey > First of all, thanks for all the help. Second, have you or anyone else > working professional with Firebird thought about writing a book about > Firebird optimisation - I would for sure buy a copy. > > >
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
On 11/13/19 9:51 AM, blackfalconsoftw...@outlook.com [firebird-support] wrote: > > Thank you Anne, for your succinct comments regarding the differences > between the efficiencies of a CHAR and VARCHAR field definitions. > > Human observation cannot actually elicit any observable effects > between the two. > > However, internally, the CHAR field definition is more efficient as > the database does not have to perform any field size calculations at > the moment that data is being updated to a VARCHAR field. Again, this > is not noticeable to a user unless the data that is being updated into > a VARCHAR field is quite large. > > Steve Naidamast > Sr. Software Engineer By the SQL standard, there are some noticeable effects. CHAR is defined as a fixed width, so data is padded with blanks to reach that width, and then the blanks are removed on retrieval. Because of this, a CHAR field can not store a value with trailing blanks. On the other hand, since VARCHAR doesn't need to pad the field, it doesn't need to trim trailing blanks, so the field CAN store data with trailing blanks. Firebird might not implement this distinction since it doesn't need to pad CHAR fields. -- Richard Damon
[firebird-support] Re: Why won't Firebird restore backups from a network drive?
Thank you, that is the correct answer. When I tried with embedded Firebird, that doesn't use the services manager, it works fine, with either way of accessing the network drive (UNC path or mapped drive letter). Feeling a bit dumb. :)
Re: [firebird-support] Re: High write access on disk
13.11.2019 21:06, kragh.tho...@yahoo.com [firebird-support] wrote: > Just to clarify, one could make a RAM drive with tmpfs, eg /ramdrive, and > then specify > this path in firebird.config under "TempDirectories"? Not quite. > If correctly understood, does this provide any benefits over just allocation > more memory > for TempCacheLimit, under Firebird 3? It is not clear what exactly caused high I/O in your case. /tmp is used by default not only for sorting files and temporary tables but also for a lot of other things. Making it RAM drive is such a generic approach that can improve performance without further detailed investigation. You can change TempDirectories and see if high I/O spot will be moved accordingly or stay in /tmp. -- 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/
[firebird-support] Re: High write access on disk
Hey Dimitry Just to clarify, one could make a RAM drive with tmpfs, eg /ramdrive, and then specify this path in firebird.config under "TempDirectories"? If correctly understood, does this provide any benefits over just allocation more memory for TempCacheLimit, under Firebird 3? Thomas Kragh
[firebird-support] Re: High write access on disk
Hey Alexey First of all, thanks for all the help. Second, have you or anyone else working professional with Firebird thought about writing a book about Firebird optimisation - I would for sure buy a copy.
[firebird-support] INET/inet_error: fork/CreateProcess errno = 5
Hi all, I have recently run into INET/inet_error: fork/CreateProcess errno = 5 error in one of my Firebird boxes and I was wondering if anyone could help me with understanding what it actually means. I have several identical Windows Server machines running Firebird 2.1.3 64-bit in Classic Server mode. Recently application using Firebird on one of these servers died. When investigating the issue I came across following records in Firebird.log that coincided with the application crash. NB-ES-0180 Wed Nov 06 12:12:16 2019 INET/inet_error: fork/CreateProcess errno = 5 NB-ES-0180 Wed Nov 06 12:32:16 2019 INET/inet_error: fork/CreateProcess errno = 5 NB-ES-0180 Wed Nov 06 13:52:16 2019 INET/inet_error: fork/CreateProcess errno = 5 NB-ES-0180 Wed Nov 06 15:32:16 2019 INET/inet_error: fork/CreateProcess errno = 5 NB-ES-0180 Wed Nov 06 15:52:16 2019 INET/inet_error: fork/CreateProcess errno = 5 I have also discovered that database file on that machine was corrupted. gfix -v -full -no_update -user sysdba -pass localhost/3050: Summary of validation errors Number of index page errors : 4 Number of database page errors : 4424 I am not sure whether this is actually symptom or the cause of the errors reported in Firebird.log. Database files on other boxes are corruption free. But there were no problems with other Firebirds. I have looked into resource consumption at the time of the crash and found nothing out of the ordinary. The memory was sitting comfortably at 50% utilization which it always does. CPU was also under-utilized (25%). I was able to fix the database corruption and run the application again. The system is running without an incident since then. I am just wondering what could possibly cause the "INET/inet_error: fork/CreateProcess errno = 5" so I can prevent it in future. Did anyone else encounter this error? Any thoughts whether or not this could be related to the database corruption? Thanks! Miroslav Vacek
[firebird-support] How check if database iscorrupted: backup/restore or validation
I'm developing a new updater for my application that uses a Firebird database. My old version was using the gbak tool for backup and restore the database (in a secondary FDB). If the backup and restore was finishing with success, I acsume that the database integrity is OK. The problem is that this approch consumes a lot of time in large databases. So my question here is: Can I run a database validation (gfix -validate -no_update) and acsume that the database integrity is OK when this command finishes without errors ? In this case I'll only perform a backup for rollback the update in cases of errors.
[firebird-support] Table is fragmented?
Recently I ran a "Database statistics" with ibexpert on one of our dbs, and there were a lot of tables marked in yellow, with the tooltip saying "Table xxx Is fragmented" I couldn't quite find an explanation for it online, so I have a few doubts about it: * Is this the same as index fragmentation? * Is this something to be worried about? * Does gfix or backup/restore fix the problem? * How can I query the metadata to figure out if there are fragmented tables in my db?
Re: [firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
13.11.2019 18:42, blackfalconsoftw...@outlook.com [firebird-support] wrote: > So my understanding of how the VARCHAR field works is correct and what I have > stated earlier. Your understanding is wrong and either IB Expert site is wrong as well or you misread it. Yest remember: CHAR - for fixed length data, VARCHAR - for variable length data. That's all. The rest is irrelevant for your job. -- 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] Why won't Firebird restore backups from a network drive?
13.11.2019 18:53, dco...@sympatico.ca [firebird-support] wrote: > We're actually already doing the backups and restores with the Services API. Usually account used to run Firebird server has no access to network at all. Thus the error you see. -- 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] Why won't Firebird restore backups from a network drive?
We're actually already doing the backups and restores with the Services API.
[firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
Here is the link to the definition of CHAR and VARCHAR field definitions from the IB-Experts site... https://www.ibexpert.net/ibe/index.php?n=Doc.CHAR https://www.ibexpert.net/ibe/index.php?n=Doc.CHAR It defines the storing of CHAR and VARCHAR in the same manner, though the VARCHAR data type is provided the extra two bytes for actual length information. In neither the CHAR or VARCHAR definitions is it noted that unused space is filled with nulls or blank data as both are stored as variable length fields internally within a table. However, the CHAR data type, as would be expected, can only store as a maximum, the number of characters that its original definition set forth. The VARCHAR field can hold up to over 32,000+ bytes of data. Thus Firebird, does in fact store VARCHAR data within its tables as all other database engines do, Its only deviation is that the CHAR field definition is made variable, similar to a VARCHAR field but only up to the maximum length initially defined. So my understanding of how the VARCHAR field works is correct and what I have stated earlier. Anne Harrison's comments also mirror what this link provides as when either a CHAR or VARCHAR field is placed in memory than the full lengths of each field definition type is provided space for. The reading suggestions you recommended are what provided the link above to the IB-Experts site... Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
13.11.2019 17:37, blackfalconsoftw...@outlook.com [firebird-support] wrote: > Your statement is suggesting a null length (until the field is updated) with > two bytes for > an actual length, which is how VARCHAR fields work in all other databases to > my > knowledge. Otherwise, to follow the previous statement, a Firebird table > could have a > VARCHAR field for 1000 characters, be stored as such with the initial > storage-info bytes > holding the actual length, which would be 1000. When the field is updated to > lets say 20 > characters of data, the field would still have an actual size of 1000 > characters but the > storage-info would be 20. > > How does this make any sense? Nothing you said make sense. There nothing like "initial storage-info". I recommend you to read Interbase API Guide and Developers Guide in part of "Data Types" to understand how data types are represented internally. -- 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/
[firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
Your statement, which may be true, does not sit well with a previous statement that states that a VARCHAR field of 1000 characters is stored in the table with 1000 characters. Your statement is suggesting a null length (until the field is updated) with two bytes for an actual length, which is how VARCHAR fields work in all other databases to my knowledge. Otherwise, to follow the previous statement, a Firebird table could have a VARCHAR field for 1000 characters, be stored as such with the initial storage-info bytes holding the actual length, which would be 1000. When the field is updated to lets say 20 characters of data, the field would still have an actual size of 1000 characters but the storage-info would be 20. How does this make any sense? Steve Naidamast Sr. Software Engineer
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
> On Nov 13, 2019, at 10:01 AM, Dimitry Sibiryakov s...@ibphoenix.com > [firebird-support] wrote: > > 13.11.2019 15:51, blackfalconsoftw...@outlook.com [firebird-support] wrote: >> However, internally, the CHAR field definition is more efficient as the >> database does not >> have to perform any field size calculations at the moment that data is being >> updated to a >> VARCHAR field. > > No such calculations is performed in Firebird. Both CHAR and VARCHAR are > always stored > with full declared length. > In memory, yes. But the entire record will be compressed before being written to disk. Good luck, Ann > > > > > > > ++ > > 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] Why won't Firebird restore backups from a network drive?
13.11.2019 16:45, dco...@sympatico.ca [firebird-support] wrote: > Whether I used a mapped drive letter, or a UNC path, if I try to restore a > backup from a > network drive it fails, with a message like "Cannot open file". Don't you by chance try it using Services API or "-se" switch of gbak? -- 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] Re: What are the trade-offs of CHAR vs. VARCHAR?
13.11.2019 16:09, blackfalconsoftw...@outlook.com [firebird-support] wrote: > If a VARCHAR field is defined for 1000 characters and it is stored in the > table at a 1000 > character length than there is no purpose to an actual VARCHAR field > definition. There is. I addition to 1000 characters two bytes of real length is stored. And believe or not it is a huge advantage. -- 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/
[firebird-support] Why won't Firebird restore backups from a network drive?
Whether I used a mapped drive letter, or a UNC path, if I try to restore a backup from a network drive it fails, with a message like "Cannot open file". I fully understand why Firebird won't open a database file on a network drive, but I'm trying to do the restore to a DB on a local drive. And I can't understand what problem is trying to be avoided by whatever coding was done to prevent backups being restored from network drives. Can someone please explain? Any chance this might change? I'm using Firebird 2.5.
[firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
Though I cannot dispute what you are saying, to me it does not make much sense. If a VARCHAR field is defined for 1000 characters and it is stored in the table at a 1000 character length than there is no purpose to an actual VARCHAR field definition. VARCHAR fields have always been dynamic in other database engines and what you are saying appears to have Firebird be the only database engine to not support dynamically allocated VARCHAR fields... Steve Naidamast Sr. Software Engineer
AW: AW: [firebird-support] CTE difficult question
Hello Karol,
thank you, that was the one piece I was looking for, the right position for the
listing.
Best thanks.
Regards.
Olaf
Von: firebird-support@yahoogroups.com
Gesendet: Mittwoch, 13. November 2019 09:51
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] CTE difficult question
Hi,
from your description i really do not know what is working for you and what is
not working.
And your expectation.
but to understand recursive CTE look at simple sample. Recursive CTE work
throught tree.
### metadata ###
CREATE TABLE TEST_TREE
(
ID INTEGER NOT NULL,
ID_HEADER INTEGER,
CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);
CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);
### test data ###
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');
### test query ###
WITH RECURSIVE
R_TREE AS
(
SELECT TT.ID AS A, CAST(TT.ID AS VARCHAR(100)) AS ASUM, 0 AS LEVEL
FROM TEST_TREE TT
WHERE TT.ID_HEADER IS NULL
UNION ALL
SELECT TT.ID AS A, RT.ASUM || '_' || TT.ID, LEVEL + 1
FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER
)
SELECT
*
FROM
R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A
###
run it and then addapt to your needs, as your situation looks same to me
regards,
Karol Bieniaszewski
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
13.11.2019 15:51, blackfalconsoftw...@outlook.com [firebird-support] wrote: > However, internally, the CHAR field definition is more efficient as the > database does not > have to perform any field size calculations at the moment that data is being > updated to a > VARCHAR field. No such calculations is performed in Firebird. Both CHAR and VARCHAR are always stored with full declared length. -- 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 are the trade-offs of CHAR vs. VARCHAR?
Thank you Anne, for your succinct comments regarding the differences between the efficiencies of a CHAR and VARCHAR field definitions. Human observation cannot actually elicit any observable effects between the two. However, internally, the CHAR field definition is more efficient as the database does not have to perform any field size calculations at the moment that data is being updated to a VARCHAR field. Again, this is not noticeable to a user unless the data that is being updated into a VARCHAR field is quite large. Steve Naidamast Sr. Software Engineer
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
13.11.2019 15:07, Clyde Eisenbeis 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? As big as possible for your Firebird version. -- 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 are the trade-offs of CHAR vs. VARCHAR?
Thanks for the useful responses! Assume I have two fields => "stFixed CHAR(10)" and "stVar VARCHAR(8191) CHARACTER SET UTF8" --- what is the optimum pageSize? ``` On Wed, Nov 13, 2019 at 6:17 AM Ann Harrison aharri...@ibphoenix.com [firebird-support] wrote: > > > > > > On Nov 12, 2019, at 10:44 PM, Richard Damon rich...@damon-family.org > [firebird-support] wrote: > > > >> On 11/12/19 12:38 PM, cte...@gmail.com [firebird-support] wrote: > >> > >> > >> What are the trade-offs of CHAR vs. VARCHAR? I know that VARCHAR > >> consumes less space. Anything thing else (are VARCHAR searches slower)? > >> > > In some implementations of SQL (I don't know if firebird is one of > > them), a row without any variable length items (like VARCHAR) and thus > > of fixed length could be stored in a somewhat optimized way making its > > access somewhat faster because all the records were the same size. > > In Firebird all records are compressed on disk. > > > > VARCHAR also doesn't always take less space, as very short CHAR fields > > can be smaller than the overhead of a VARCHAR, and if the CHAR field is > > storing a value that is always the same length (like maybe a hash code) > > the overhead of VARCHAR is just wasted. > > > > -- > > Richard Damon > > > > > > > > > > Posted by: Richard Damon > > > > > > ++ > > > > 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] Re: High write access on disk
Hello, For predictable load reads/fetches is very useful metric, but in case of spikes like Thomas has - 96 connections to 1300, it will be far less useful. Regards, Alexey On 13.11.2019 14:50, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 13.11.2019 12:41, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote: >> we also started with Page Buffers = 25% RAM and increased it step by step >> (and still >> continue to work with fine tuning). > Isn't it better to use cache hit ratio to make the decision about its > growth? > > ++ 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] Re: High write access on disk
Hello, Yes, better use default - pagecache in terms of Linux is different from Page Buffers/Page cache of Firebird. Regards, Alexey On 13.11.2019 15:10, kragh.tho...@yahoo.com [firebird-support] wrote: Hey Alexey Thanks for the answer, I only have one question left, do I need to adjust linux page cache(vm.pagecache) according to amount of RAM allocated for DefaultDBCachePages, so that memory is not over "subscribed"? Eg. if DefaultDBCachePages is increased should i then decrease vm.pagecache? Or should I use vm.pagecache default values and have Linux work it out?
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
> On Nov 12, 2019, at 10:44 PM, Richard Damon rich...@damon-family.org > [firebird-support] wrote: > >> On 11/12/19 12:38 PM, cte...@gmail.com [firebird-support] wrote: >> >> >> What are the trade-offs of CHAR vs. VARCHAR? I know that VARCHAR >> consumes less space. Anything thing else (are VARCHAR searches slower)? >> > In some implementations of SQL (I don't know if firebird is one of > them), a row without any variable length items (like VARCHAR) and thus > of fixed length could be stored in a somewhat optimized way making its > access somewhat faster because all the records were the same size. In Firebird all records are compressed on disk. > > VARCHAR also doesn't always take less space, as very short CHAR fields > can be smaller than the overhead of a VARCHAR, and if the CHAR field is > storing a value that is always the same length (like maybe a hash code) > the overhead of VARCHAR is just wasted. > > -- > Richard Damon > > > > > Posted by: Richard Damon > > > ++ > > 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 > > >
[firebird-support] Re: High write access on disk
Hey Alexey Thanks for the answer, I only have one question left, do I need to adjust linux page cache(vm.pagecache) according to amount of RAM allocated for DefaultDBCachePages, so that memory is not over "subscribed"? Eg. if DefaultDBCachePages is increased should i then decrease vm.pagecache? Or should I use vm.pagecache default values and have Linux work it out?
Re: [firebird-support] Re: High write access on disk
13.11.2019 12:41, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote: > we also started with Page Buffers = 25% RAM and increased it step by step > (and still > continue to work with fine tuning). Isn't it better to use cache hit ratio to make the decision about its growth? -- 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] Re: High write access on disk
Hello Thomas, Ok, good to know that you are prepared for the migration! FB3 with proper migration and config certainly will increase the overall performance. In my presentation regarding Linux I spoke about specific case which is under everyday control, and in our long way, we also started with Page Buffers = 25% RAM and increased it step by step (and still continue to work with fine tuning). Also, there we have 320Gb and smooth predictable increase of connections during the day, and you have much more intensive spikes, so, better be more conservative - if you will see no problems with 25% of RAM, increase to 40-50-70, week by week. Regards, Alexey On 13.11.2019 13:59, kragh.tho...@yahoo.com [firebird-support] wrote: Hey Alexey Its not that I see high traffic to /tmp as a problem, I was just worried that it was becoming a bottleneck in our system. Great to know that HQbird has a way of tracking these qureys, I have already been looking into HQbird especially because of prepared statements. Regarding the upgrade to Fb 3 SuperServer, a complete restore of production database to Fb 3 have already been made in our dev envioment last week without problems, and for almost a year dev, test and preprod have been running Fb 3, so i am quite comfortable in that regard. We have also uses FB TraceManager to weed out bad queries, and procedures and triggers recompile without errors. My only concern is that I somehow configures Fb 3 in a way that results in worse performance that our current 2.5 installation. The server is a dedicated Firebird server with a single database, does this change your recommendation to allocate 25% RAM for DefaultDBCachePages? My initial estimate was based on your presentation at this years Firebird conference(20_tuninglinux.pdf page 28) Thomas Kragh
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
On 2019-11-12 23:06, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > As I understand, the FbConnection.CreateDatabase max pageSize for > VARCHAR is 8191. Does the page size change to less than 8191 if the > VARCHAR is less? > > What is the max pageSize for CHAR? Page size is about the size of database pages, Firebird supports page sizes of 4096, 8192, 16384, and - Firebird 4 - 32768 (earlier versions also supported 1024 and 2048). Page size is unrelated to datatypes sizes, except for things you want to index (because the page size does restrict the key size of an index. In any case, CHAR is limited to 32767 characters with a single byte character set and 8191 characters with UTF8. VARCHAR is limited to 32765 characters (single byte) or 8191 characters with UTF8. The only good reason to choose CHAR is for fixed width data, in all other cases you should use VARCHAR. Storage-wise it doesn't really matter in Firebird (although VARCHAR actually requires two bytes more than CHAR in storage format), but VARCHAR is smailler in transmission in the wire protocol. However, CHAR is padded with spaces, while VARCHAR is not. For most uses of string data, VARCHAR is simply better. Mark
Re: [firebird-support] Multiple FB Installations
13.11.2019 01:14, 'Andrew Zenz' and...@aimsoftware.com.au [firebird-support] wrote: > I recently installed Firebird Client (2.5.8) and Firebird ODBC drivers > (2.0.5) on a > clients RDS server and was told I had broken a previously installed app that > appears to > use FB Embedded. > > The error is: > Attempting connection to localhost on port 30632 > Failed to connect to host localhost on port 30632. > Socket Error # 10061 > Connection refused. The error definitely show that it is not Embedded. > Is there a way to co-habit an embedded server and client installation? They usually can coexist out-of-box without any problem. > How do I fix what I FUBARed? Go to installation directory of your server and execute "instreg r". -- 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] Re: High write access on disk
13.11.2019 11:59, kragh.tho...@yahoo.com [firebird-support] wrote: > Its not that I see high traffic to /tmp as a problem, I was just worried that > it was > becoming a bottleneck in our system. In this case you can use for it tempfs backed up with auto-expanded swap files. -- 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] Re: High write access on disk
Hey Alexey Its not that I see high traffic to /tmp as a problem, I was just worried that it was becoming a bottleneck in our system. Great to know that HQbird has a way of tracking these qureys, I have already been looking into HQbird especially because of prepared statements. Regarding the upgrade to Fb 3 SuperServer, a complete restore of production database to Fb 3 have already been made in our dev envioment last week without problems, and for almost a year dev, test and preprod have been running Fb 3, so i am quite comfortable in that regard. We have also uses FB TraceManager to weed out bad queries, and procedures and triggers recompile without errors. My only concern is that I somehow configures Fb 3 in a way that results in worse performance that our current 2.5 installation. The server is a dedicated Firebird server with a single database, does this change your recommendation to allocate 25% RAM for DefaultDBCachePages? My initial estimate was based on your presentation at this years Firebird conference(20_tuninglinux.pdf page 28) Thomas Kragh
Re: [firebird-support] Re: High write access on disk
Hello Thomas, My advice was only to fix the obvious mistake regarding the TempCacheLimit. In general, I don't see a problem in high traffic to /tmp, and I don't understand why do you think sorting queries produce the problem - and even if they produce, the first attempt would be to identify such queries with TempSpaceLogThreshold (it works in HQbird only) and then fix them with index/disabling index, restructuring it, etc. As another short advice: 1) I need to say that tuning of 2.5 SuperClassic and 3.0 SuperClassic is different, and, at least, I would recommend to a) put DefaultDBCachePages to databases.conf b) allocate 25% of RAM at the first step, then increase it by 10-20%. 2) Migration 2.5 - > 3.0 requires at least clean metadata (isql -x > metadata.sql on 2.5, isql -i metadata.sql on 3.0 should not give errors), I hope you tested it, and, also, 3.0.4 have different optimization, so some queries can start to work slower, and thorough testing is required. Regards, Alexey Kovyazin IBSurgeon On 13.11.2019 11:56, kragh.tho...@yahoo.com [firebird-support] wrote: Hey Alexey Thanks for the advise, I changed TempCacheLimit to 21, last night and it looks like it had a positive effect on the disk queue size on sda. I have read a lot about Firebird tuning however I have never come across that limit on fb 2.5 https://pasteboard.co/IGsU650A.png The green line is when I updated the config. The spikes around midnight is due to backup/restore. The queue size has gone from around 1 to 0,1-0,2 during office hours. However the amount of wirte access to sda, did not change after the config change. Perhaps the sensor that we use monitors the writes Firebird makes when firebird sorts response? https://pasteboard.co/IGt93ec.png To accommodate further growth in users I am planning an upgrade to Firebird 3.0.4(SuperServer) this weekend. The server will be the same, however with a ram upgrade to 192Gb. I am planning to use the following config, do you or anyone else see any problems? Firebird config DefaultDbCachePages = 6000K #96Gb (page size 16Kb) FileSystemCacheThreshold = 2K TempCacheLimit = 10G #10Gb TempBlockSize = 2M LockMemSize = 116117248 LockHashSlots = 40099 OS config Vm.pagecache = 30 Vm.swappiness = 10 vm.min_free_kbytes = 1048576 vm.max_map_count=25 fs.file-max=2097152 net.core.somaxconn = 4096 net.core.netdev_max_backlog = 65536 net.core.optmem_max = 25165824 Thomas Kragh
[firebird-support] Re: High write access on disk
Hey Alexey Thanks for the advise, I changed TempCacheLimit to 21, last night and it looks like it had a positive effect on the disk queue size on sda. I have read a lot about Firebird tuning however I have never come across that limit on fb 2.5 https://pasteboard.co/IGsU650A.png https://pasteboard.co/IGsU650A.png The green line is when I updated the config. The spikes around midnight is due to backup/restore. The queue size has gone from around 1 to 0,1-0,2 during office hours. However the amount of wirte access to sda, did not change after the config change. Perhaps the sensor that we use monitors the writes Firebird makes when firebird sorts response? https://pasteboard.co/IGt93ec.png https://pasteboard.co/IGt93ec.png To accommodate further growth in users I am planning an upgrade to Firebird 3.0.4(SuperServer) this weekend. The server will be the same, however with a ram upgrade to 192Gb. I am planning to use the following config, do you or anyone else see any problems? Firebird config DefaultDbCachePages = 6000K #96Gb (page size 16Kb) FileSystemCacheThreshold = 2K TempCacheLimit = 10G #10Gb TempBlockSize = 2M LockMemSize = 116117248 LockHashSlots = 40099 OS config Vm.pagecache = 30 Vm.swappiness = 10 vm.min_free_kbytes = 1048576 vm.max_map_count=25 fs.file-max=2097152 net.core.somaxconn = 4096 net.core.netdev_max_backlog = 65536 net.core.optmem_max = 25165824 Thomas Kragh
Re: AW: [firebird-support] CTE difficult question
Hi,
from your description i really do not know what is working for you and what is
not working.
And your expectation.
but to understand recursive CTE look at simple sample. Recursive CTE work
throught tree.
### metadata ###
CREATE TABLE TEST_TREE
(
ID INTEGER NOT NULL,
ID_HEADER INTEGER,
CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);
CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);
### test data ###
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');
### test query ###
WITH RECURSIVE
R_TREE AS
(
SELECT TT.ID AS A, CAST(TT.ID AS VARCHAR(100)) AS ASUM, 0 AS LEVEL
FROM TEST_TREE TT
WHERE TT.ID_HEADER IS NULL
UNION ALL
SELECT TT.ID AS A, RT.ASUM || '_' || TT.ID, LEVEL + 1
FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER
)
SELECT
*
FROM
R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A
###
run it and then addapt to your needs, as your situation looks same to me
regards,
Karol Bieniaszewski
AW: [firebird-support] CTE difficult question
Sorry, I don't want to annoy, but can I do this with a separate list-function or is it possible to realize it with new functions in firebird 4? TA TB A B B is a part from A A C C is a part from A A D D is a part from A D X X is a part from D, X is material D Y Y is a part from D, Y is material C Z Z is a part from C, Z is material B Z Z is a part from B, Z is material Now I would get all from A with Material: A - B - Z1, material:Z1 A - C - Z, material: Z A - D - X, material: X A - D - Y, material: Y Later I can build a sum from all materials, for example Z Thank you. Von: firebird-support@yahoogroups.com Gesendet: Dienstag, 12. November 2019 14:23 An: firebird-support@yahoogroups.com Betreff: AW: [firebird-support] CTE difficult question Ist it possible somehow? I would get each Part and the complete Path for it. If the Part is a material, I would get it and the complete list with all parts from the first til the material itself. Thanks. Hello, I have the following situation: There are some parts with parts inside Part A has Part B inside Part A has Part C inside Part B has BA inside Part B has BB inside Part BA has BAA inside Part BA has also BAB inside Part BB has BBA inside Part C has CA inside A -> B-> BA -> BAA A -> B-> BA -> BAB A -> B-> BB -> BBA A -> C-> CA With a cte I can get every last parts, for example BA with BAA, BA with BAB, BB with BBA and C with CA. This is fine, but I would get the entire combination in a List Instead of BA - BAA I would get A - B - BA - BAA. Teilenummer is in this case the first left part, Matteilenr is the last part Saved in the Table tmaterial Teilenr (pteilenr)MatteilenrAnzahl (amount) A B1 BBA 10 BA BAA 10 BA BAB 5 B BB 5 BB BBA 4 A C2 CCA 10 The CTE: (tteile is just for the unit) for with recursive ang as( select a.matteilenr, a.teilenr as pteilenr, a.anzahl * :anzahlt as anzahl, b.einheitnr, a.kundennr from tmaterial a left join tteile b on(a.matteilenr = b.teilenr) where a.teilenr = :teilenr union all select aa.matteilenr, aa.teilenr as pteilenr, aa.anzahl * ang2.anzahl, ab.einheitnr, aa.kundennr from tmaterial aa left join tteile ab on (aa.matteilenr = ab.teilenr) inner join ang as ang2 on (aa.teilenr = ang2.matteilenr) ) select a.matteilenr, a.pteilenr, sum(a.anzahl), a.einheitnr, a.kundennr from ang a group by a.matteilenr, a.pteilenr, a.einheitnr, a.kundennr into :materialnr, :pteilenr, :anzahlm, :einheit, :lieferant do suspend; I give the cte the :teilenr (for Example A) and get every part itself and every block of two pairs. Now I would get the entire path, all layers. Thank you.
