Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
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. 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
[firebird-support] Multiple FB Installations
Hi everyone. 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. 2 questions: Is there a way to co-habit an embedded server and client installation? How do I fix what I FUBARed? Cheers, Andrew Zenz
Re: [firebird-support] High write access on disk
Hello Thomas, Seems like your system generates a lot of sort files - the problem can be that TempCacheLimit in Firebird 2.5 cannot be more than 2Gb -1. When you set it to something between 2 and 4Gb, it means 0. So, set something like TempCacheLimit=21 Consider to tune other parameters from our optimized configurations https://ib-aid.com/en/optimized-firebird-configuration/ and worth to look through https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/ https://ib-aid.com/en/articles/23-more-ways-to-speed-up-firebird/ You provided lockprint from the moment when you had only 96 users, and peak seems to be ~1300 users, so it is not very useful :) Regards, Alexey On 13.11.2019 0:45, kragh.tho...@yahoo.com [firebird-support] wrote: Hey Where I work we run a website with at growing number of users, over a period of two months we have seen Firebird slow down at peek hours, where the number of concurrent website users is about 6.000. Usually there is about 250 attachments, however when the slowdown occurs the number rises to 800-1000 in about 5-10 seconds. This is what to be expected if query speed slows down. During my investigation I found out that there is a lot writing to the root partition(sda) where /tmp is located. both under normal load and more so when slowdown occurs. Queue size for sda rises above 1 during slowdown. Read/write operations to sdb where the database is located seems normal and is a fraction of operations on sda. Is this high number of write operations normal for Firebird, or do I need to tune some Firebird or OS settings? Is it perhaps because TempCacheLimit is too low, and Firebird uses disk for sorting, and OS is forced to flush this data to disk because almost all memory is used for filecaching? System information: CentOs 7 16 core virtual machine with 128Gb of Ram 3Par 8200 SAN (6 SSD about 75.000 IOPS) Server is dedicated to one database. Firebird 2.5.8 (superclassic) TempCacheLimit = 4294967296 DefaultDbCachePages = 2048 LockMemSize = 5048576 LockHashSlots = 30011 Database size: 155Gb [user@dbserver]$ free -m total usedfree shared buff/cache available Mem: 128765 3727912 4147 124125 120569 Swap: 0 0 0 fb_lock_print - not under load: LOCK_HEADER BLOCK Version: 145, Active owner: 0, Length: 116117248, Used: 111204848 Flags: 0x0001 Enqs: 17690670118, Converts: 74244796, Rejects: 20098430, Blocks: 413686610 Deadlock scans: 0, Deadlocks: 0, Scan interval: 10 Acquires: 20215919905, Acquire blocks: 1290646628, Spin count: 0 Mutex wait: 6.4% Hash slots: 30011, Hash lengths (min/avg/max): 0/ 0/ 9 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (96):forward: 26814936, backward: 24959608 Free owners (1183): forward: 61820848, backward: 88775232 Free locks (148866):forward: 71783848, backward: 1184592 Free requests (1442650):forward: 11030120, backward: 30750136 Lock Ordering: Enabled Best regards Thomas Kragh
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
12.11.2019 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? Database page size has no relation with any data type sizes. Records can be fragmented to more than one data page. -- 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] High write access on disk
Hey Where I work we run a website with at growing number of users, over a period of two months we have seen Firebird slow down at peek hours, where the number of concurrent website users is about 6.000. Usually there is about 250 attachments, however when the slowdown occurs the number rises to 800-1000 in about 5-10 seconds. This is what to be expected if query speed slows down. During my investigation I found out that there is a lot writing to the root partition(sda) where /tmp is located. both under normal load and more so when slowdown occurs. Queue size for sda rises above 1 during slowdown. Read/write operations to sdb where the database is located seems normal and is a fraction of operations on sda. Is this high number of write operations normal for Firebird, or do I need to tune some Firebird or OS settings? Is it perhaps because TempCacheLimit is too low, and Firebird uses disk for sorting, and OS is forced to flush this data to disk because almost all memory is used for filecaching? System information: CentOs 7 16 core virtual machine with 128Gb of Ram 3Par 8200 SAN (6 SSD about 75.000 IOPS) Server is dedicated to one database. Firebird 2.5.8 (superclassic) TempCacheLimit = 4294967296 DefaultDbCachePages = 2048 LockMemSize = 5048576 LockHashSlots = 30011 Database size: 155Gb [user@dbserver]$ free -m totalusedfree shared buff/cache available Mem: 128765 3727912 4147 124125 120569 Swap: 0 0 0 fb_lock_print - not under load: LOCK_HEADER BLOCK Version: 145, Active owner: 0, Length: 116117248, Used: 111204848 Flags: 0x0001 Enqs: 17690670118, Converts: 74244796, Rejects: 20098430, Blocks: 413686610 Deadlock scans: 0, Deadlocks: 0, Scan interval: 10 Acquires: 20215919905, Acquire blocks: 1290646628, Spin count: 0 Mutex wait: 6.4% Hash slots: 30011, Hash lengths (min/avg/max):0/ 0/ 9 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (96):forward: 26814936, backward: 24959608 Free owners (1183): forward: 61820848, backward: 88775232 Free locks (148866):forward: 71783848, backward: 1184592 Free requests (1442650):forward: 11030120, backward: 30750136 Lock Ordering: Enabled Best regards Thomas Kragh
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
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? On Tue, Nov 12, 2019 at 1:15 PM Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 12.11.2019 18:38, cte...@gmail.com [firebird-support] wrote: > >I know that VARCHAR consumes less space. > >It doesn't. CHAR has no advantages over VARCHAR in most usages. Only > very little cases > need fixed length data (and usually it is binary data). > > > -- >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] words reserved by version
On 12-11-2019 10:31, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > On 2019-11-12 10:14, Mark Rotteveel m...@lawinegevaar.nl > [firebird-support] wrote: >> On 2019-11-12 10:03, Norbert Saint Georges n...@tetrasys.eu >> [firebird-support] wrote: >>> Good morning all, >>> >>> Is there an exhaustive list of all the words reserved by version of >>> firebird (3.x, 4.x) somewhere? >>> >>> Thank you in advance. >> >> Yes, it is in the doc folder: >> https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.keywords > > If you want an exhaustive list, I also have a database (created using > https://github.com/mrotteveel/firebird-reserved-words). I use that > database to generate metadata information in Jaybird (which needs a list > of keywords not in SQL:2003). I'll put a backup of the latest version of > that database online somewhere later today. I put a backup of the database (Firebird 3) on https://www.dropbox.com/s/ep17q5hv43iwkll/fb_reserverwords_20191112.7z?dl=0 This represents the state of 7 months ago, so it might not contain keywords introduced in 2.5.9 and recent Firebird 4 snapshots. Mark -- Mark Rotteveel
Re: [firebird-support] distinct on list() function
On 12-11-2019 13:37, Michal Kurczabinski michk...@gmail.com [firebird-support] wrote: > Is this old bug from FB 2.5.x or some kind of feature ? > > This still exists in FB 3.x It would be helpful to also describe the problem, and not only let people execute a query and try to guess what problem you're seeing. Mark -- Mark Rotteveel
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
12.11.2019 18:38, cte...@gmail.com [firebird-support] wrote: > I know that VARCHAR consumes less space. It doesn't. CHAR has no advantages over VARCHAR in most usages. Only very little cases need fixed length data (and usually it is binary data). -- 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] What are the trade-offs of CHAR vs. VARCHAR?
What are the trade-offs of CHAR vs. VARCHAR? I know that VARCHAR consumes less space. Anything thing else (are VARCHAR searches slower)?
[firebird-support] ReleaseSemaphore failed. Error code 298
I just started getting these errors in Firebird.log. The first error is always followed by the second. It appears to always be the same database. What might be causing this? I am using Firebird 2.5.3.26778. NOVADB (Server) Mon Nov 11 19:17:03 2019 Operating system call ReleaseSemaphore failed. Error code 298 NOVADB (Server) Mon Nov 11 19:19:03 2019 Database: E:\LEGEND\OPERATIONS.FDB deadlock
[firebird-support] Re: distinct on list() function
This is not a bug, but a documented feature. list() returns BLOB. And blobs are distict because BLOB_ID-s are compared. SELECT DISTINCT, ORDER BY and GROUP BY work on the BLOB ID, not the contents. https://firebirdsql.org/refdocs/langrefupd21-blob.html https://firebirdsql.org/refdocs/langrefupd21-blob.html You ust use CAST in this case. with cte as (select 1 cnt from rdb$database union all select 2 from rdb$database ) select cast(list(cnt) as varchar(10)) from cte union select cast(list(cnt) as varchar(10)) from cte
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.
[firebird-support] distinct on list() function
Is this old bug from FB 2.5.x or some kind of feature ? This still exists in FB 3.x with cte as (select 1 cnt from rdb$database union all select 2 from rdb$database ) select list(cnt) from cte union select list(cnt) from cte -- Regards, Michał Kurczabiński
Re: [firebird-support] words reserved by version
On 2019-11-12 10:14, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > On 2019-11-12 10:03, Norbert Saint Georges n...@tetrasys.eu > [firebird-support] wrote: >> Good morning all, >> >> Is there an exhaustive list of all the words reserved by version of >> firebird (3.x, 4.x) somewhere? >> >> Thank you in advance. > > Yes, it is in the doc folder: > https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.keywords If you want an exhaustive list, I also have a database (created using https://github.com/mrotteveel/firebird-reserved-words). I use that database to generate metadata information in Jaybird (which needs a list of keywords not in SQL:2003). I'll put a backup of the latest version of that database online somewhere later today. Mark
[firebird-support] Re: words reserved by version
> Yes, it is in the doc folder: > https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.keywords I should have thought about it :-( Thank you / Dankjewel -- Norbert Saint Georges http://tetrasys.fi ++ 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] words reserved by version
On 2019-11-12 10:03, Norbert Saint Georges n...@tetrasys.eu [firebird-support] wrote: > Good morning all, > > Is there an exhaustive list of all the words reserved by version of > firebird (3.x, 4.x) somewhere? > > Thank you in advance. Yes, it is in the doc folder: https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.keywords
[firebird-support] words reserved by version
Good morning all, Is there an exhaustive list of all the words reserved by version of firebird (3.x, 4.x) somewhere? Thank you in advance. -- Norbert Saint Georges http://tetrasys.fi ++ 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/
