[firebird-support] Is it possible to store firebird DB file on a NAS?
Hi all, I have a firebird server (FB 2.1, Superserver) on windows OS with database file store on an external harddisk. There are about 5 users accessing this database. I never try or have NAS, is it possible just to move firebird db file from external hardisk to NAS? If this is possible, can also anyone tell me roughly about read/write speed which one is better, NAS or external harddisk? Currently, I am using 5400rpm harddisk, attached via USB 2.0. I am planning to use WD My Cloud( cloud for personal/home), some review said that this NAS has about 50Mbps read/write speed on gigabits LAN, but i will only use it on 100 Mbps LAN. Thanks in advance, Anto
[firebird-support] Re: Is it possible to store firebird DB file on a NAS?
Hi,Thanks for you reply. If I understand correctly, WD Cloud come with password to access private folder. So, I am planing to install FB server where NAS attached and set private folder in NAS, then map this folder as a network drive. Other users, don't have direct access to this private folder. Accessing FB database is just the same with format comp name:network drive on NAS With this setting, is this necessary to set RemoteFileOpenAbility = 1? WD Cloud come with pre-installed harddisk, so I can't replace its disk. I still have a doubt with this cloud, it got good reviewer by some webs, but got bad rating by users experiences. Regards, Anto
[firebird-support] Alternative for Zebedee
Hi guys,I tried to installed Zebedee on CentOS 6.3, but failed because of missing library. The library was related with OpenSSL. I have search in the net, Zebedee using very old library that no longer supported on CentOs 6.3, the only way to install it on CentOS is to build from its source code. But I have very limited skill in Linux, so although there is a source code, I can't build it into an RPM. Is there any alternative for zebedee that works with CentOS? Or is there any Zebedee.rpm already built for CentOs? Thanks regards, Anto
Re: [firebird-support] Alternative for Zebedee
Wow, that's great.Please kindly send to my email, I need an 64 bit and thanks you so much. Regards, Anto
Re: [firebird-support] Alternative for Zebedee
Works great !!Thanks again for your help. Regards, Anto
[firebird-support] Re: Alternative for Zebedee
Hi,Thanks for your suggestion. Already look STunel, but since I am more familiar with zebedee, and now it works on Linux, I will stick with it. Regards, Anto
[firebird-support] Re: Tools for encription field in Firebird
Hi, thanks for your info. This is exactly what I am looking for. I need to encrypt some field and use firebird only to store, no index needed. May I know the program you use to encrypt your data? Thanks regards, Anto
[firebird-support] Re: Firebird on Raspberry Pi 2 : Problem to connect remotely
Yes you were right. Just found the solution, just comment RemoteBindAddress in firebird.conf & restart firebird service and it works. By the way, for a small database, firebird server performance on Raspberry Pi2 is not bad at all. With my microSD (read speed about 20MB/s), it took about 2 secs to retrieve data from store procedure while on my cpu(Phenom X6 3.0GHz) took 0.2 secs. It will use as a 24/7 server for data collection only, i think, performance is enough. Regards, Agus
[firebird-support] Re: Firebird on Raspberry Pi 2 : Problem to connect remotely
Yes you were right. Just found the solution, just comment RemoteBindAddress in firebird.conf & restart firebird service and it works. By the way, for a small database, firebird server performance on Raspberry Pi2 is not bad at all. With my microSD (read speed about 20MB/s), it took about 2 secs to retrieve data from store procedure while on my cpu(Phenom X6 3.0GHz) took 0.2 secs. It will use as a 24/7 server for data collection only, i think, performance is enough. Regards, Agus
[firebird-support] Firebird on Raspberry Pi 2 : Problem to connect remotely
Hi guys, Have any of you success to install Firebird on Raspberry and able to connect to its database remotely via Windows machine? I have these settings on Raspberry Pi: - OS : Raspbian Jessie - Firebird 2.5 SuperServer - static ip address on Pi : 192.168.1.50 With Isql on the raspbian's terminal, I can connect to database and operate select,insert,execute, etc. But when trying to connect remotely via flamerobin on Window OS, got an error "Unable to complete network request to host 192.168.1.50" Sometimes this error cause by firewall, but as I know, there is no firewall at all on Rapsbian. Ping Rapsbian hostname via Window failed, but ping with its ip address OK. Connect remotely by VNC viewer from windows is also OK. Any advices/hints how to solve this problem? Thanks in advance. Agus .
[firebird-support] Re: Firebird on Raspberry Pi 2 : Problem to connect remotely
Thanks for your reply, Unfortunally, there no inetd command on Raspbian and I don't know compatible command in it. Regards, Agus
[firebird-support] Alternative to Java Zebedee
Hi guys, Since Zebedee for Java is a beta ver. and not maintain again, are there any alternative to this program? Thanks in advance. Regards, Anto
RE: [firebird-support] Alternative to Java Zebedee
I need a Java jar file, so it can run on Linux or Windows.
[firebird-support] FB 3.0, a few questions on a new future
Hi guys,
I am building a new project using FB 3.0 - SS, I have a few questions :
1) Since FB 3.0 support WireCompression, should I still use Zebedee?
2) Is that possible to hide stored procedure/trigger codes in FB 3.0 ? Usually
I used this statement :
UPDATE RDB$TRIGGERS a set a.RDB$TRIGGER_SOURCE = NULL
where a.RDB$TRIGGER_NAME in ('BD_1','BD_2')
Thanks & Regards,
Anto
[firebird-support] Re: Simply bad, new is not always better. FB3 and ODBC
[#CORE-5210] Firebird 3.0 + fbclient 3.0 - POST_EVENT won't work - Firebird RDBMS Issue Tracker http://tracker.firebirdsql.org/browse/CORE-5210 http://tracker.firebirdsql.org/browse/CORE-5210 [#CORE-5210] Firebird 3.0 + fbclient 3.0 - POST_EVEN... http://tracker.firebirdsql.org/browse/CORE-5210 Database trigger POST_EVENT after field update. Test results: 1. Firebird 3 server + fbclient 3 - doesn't work 2. Firebird 3 server + fbclient 2 - works 3. Fire... View on tracker.fireb... http://tracker.firebirdsql.org/browse/CORE-5210 Preview by Yahoo Is this problem solved? Just tried on FB 3.0.1.32525, still didn't work.
[firebird-support] Re: Simply bad, new is not always better. FB3 and ODBC
In my case, POST_EVENT still not working correctly. Anto
[firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?
Dear all, Sorry, I have miscalculated the setting in DefaultDbCachePages, it was set more than available RAM on my Computer, no wonder it crashed. Now, everything is OK, tested on SS FB 3.0 in Win64. Thanks for your helps, Best regards, Anto.
Re: [firebird-support] Re: FB 3.0, got problem when add field to Table with existing data
Dear Mark, Thanks for you info, solved the problem. Regards, Anto.
[firebird-support] Max RAM for Database Cache in SS FB 3.0 64bit is to small?
Hi guys, Am I correct that maximum allowed RAM for database cache on SS FB 3.0 in Win64 is only 2 GB? Calculated from max DefaultDbCachePages (131072) x max Page Size (16384) = 2 GB. Setting DefaultDbCachePages more than allowed caused a crashed. If this true, how to optimaze free RAM for performance? Regards, Anto
[firebird-support] FB 3.0, got problem when add field to Table with existing data
Hi guys, I have a table with existing data & got a problem to add field that must have a value -> alter table Tbl1 add Fld1 int not null. If table doesn't have data, it is OK, but Firebird refused if table already have data. Any suggestion how to solve this problem? Thanks & regards, Anto
[firebird-support] Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi all, Just migrating from SC FB 2.5.2 to SS FB 3.0 (both in Win x64), i found a few problems : 1) Query that runs about 2-3 secs in SC FB 2.5.2, runs minutes in SS FB 3.0 Query is something like this : Select a.col1,b.col1 from tbl1 a, tbl2 b where a.id = b.id and b.id in (select c.id from tbl3 c) SS FB 3.0 uses different query plan that not use indices, no wonder it was so slow. Changed query to : Select a.col1,b.col1 from tbl1 a, tbl2 b, tbl3 c where a.id = b.id and b.id= c.id Runs around 1-2 secs in SS FB 3.0, but runs minutes in SC FB 2.5.2 2) Since in FB 3.0, SS is able to use SMP & shared Db Cache, I tried to set Db Cache to 512 MB via gfix. It was without error. First connection is OK & I could saw that the actual Db cache size is around 300 MB. But after connection closed & tried to connect again, FB raised an error, something like connection lost. Changed Db Cache to 256 MB fixed the problem. I think, for modern computer, 256 MB is too small. Regards, Anto.
Re: AW: AW: [firebird-support] Simply bad, new is not always better. FB3 and ODBC
From SC FB 2.5.2 to SS FB 3.0, all on Win64, I can confirm 2 things : 1) Post Event on SS 3.0, sometimes works, sometime not. I have an app that use post_event. Sometime I have to restart this app a few times to make post_event works. 2) Stored Procedure/Query I have noticed that SP/Query that runs very slow on SS FB 3.0 has an in clause. (perhaps there are other type of query that could cause this). SS FB 3.0 takes different query plan than SC 2.5.2, it slows because not use index. Regards, Anto
[firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, This is the Query plan from SS FB 3.0 PLAN (GET_STCK NATURAL) and here is the Query plan from SC FB 2.5.2 PLAN (G_T3 NATURAL)(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (TM_D), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (T1_M_DL), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (T1_M_DL), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))SORT ((G_T3 NATURAL)) This could be a big problem, all queries/stored procedures/triggers must be test again for its performance. regards, Anto
[firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, About Db cache, found the problem. Now I could set Db Cache per database at 2 GB (16384 x 131072), try to set at 4GB (16384 x 262144), no error on gfix, but it just crashed when tried to connect, no error message shown. Regards, Anto.
[firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, Tested again with FB 2.5.1 & FB 2.5.5, apparently, this is not a problem from FB 3.0, but it was already appeared since FB 2.5. Same machine, database, query & engine, SC FB 2.5.1 & SC FB 2.5.2 runs a lot faster than FB 2.5.5 & FB 3.0. Regards, Anton
[firebird-support] SQL performance SS FB 3.0.1 vs SC FB 2.5.2
Hi guys, Just found that on a particular case, SS FB 3.0.1 is about 4x slower than SC FB 2.5.2. Here is the query : == select a.id,b.nm from tbl1 a, tbl2 b where a.id = b.id and a.id in (select c.id from tbl3 c) If the query change to this : == select a.id,b.nm from tbl1 a, tbl2 b, tbl3 c where a.id = b.id and a.id = c.id SC FB 2.5.2 is about 1.3x faster than SS FB 3.0.1. Regards, Anto
[firebird-support] Strange behaviour on Update Operation
Hi all, I have a strange result when running update operation. I am using TIBSQL from Borland CBuilder 6, Firebird Super Clasic 2.5.2 (64bit) on Windows 7 64 bit. Here are the script from TIBSQL : = Update table1 set field1 = 'abc', field2 = 'abc', field3 = field3 + 1 where id = 1 returning new.field1; Field1 & Field2 = Varchar(12) Field3 = smallint default 0 After that script executed, my app checked whether field1 is null or not. If it was null, an error message raised to users. Sometimes (not always) field2 & field3 have a value while field1 is null, but no error message raised. This is strange because this is the only procedure on my app that could update these fields (field1,field2, & field3). Initial condition for these fields should be null (field1 & field2) and 0 for field3, and when update occur, field1 & field2 must have a value while field3 has a value 1 or 2 or 3, so on. Has anyone experiences some problem? Thanks in advance. Regards, Anto
[firebird-support] Connecting to Firebird 3.01 with JDBC
Hi guys, Anyone here able to connect to FB 3.01 with JDBC 2.x? I have tried with jaybird-full-2.2.11 & have adding these lines to firebird.conf 1) UserManager = Srp, Legacy_UserManager 2) AuthClient = Srp, Win_Sspi, Legacy_Auth But no avail, always got error message org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544421. connection rejected by remote interface Thanks in advance & best regards, Anto
[firebird-support] Re: Connecting to Firebird 3.01 with JDBC
Hi all, Thanks for all your help, at last, I manage to connect to FB 3.0 via JDBC. In case someone need to know how, this is steps required : Legacy Authentication http://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-compat-legacyauth.html http://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-compat-legacyauth.html Legacy Authentication http://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-compat-legacyauth.html Using a text editor, open firebird.conf and find the entry for the parameter UserManager: #UserManager = Srp Delete the “#” symbol an... View on firebirdsql.org http://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-compat-legacyauth.html Preview by Yahoo
[firebird-support] Is it possible to insert Image via sql stament?
Hi all, Is it possible to insert image into FB database directly via sql stament using isql or flamerobin? Thanks & regards, Anto
[firebird-support] SS FB3.01 - Is it possible to set CPU utilization to max?
Hi all, During project development/testing or database maintenance, such as backup/restore, mostly, there is only 1 connection to FB Server. I have 6 core processors and during those process, cpu's utilization is about 16%. I knew, this is because there is only 1 connection to FB server, 2 connections will raised cpu's utilization to 32% and so on. Is it possible to force Firebird to use max. cpu utilization when there is only 1 connection? Thanks & regards, Anto
[firebird-support] How to change cpu utilizatio for Firebird engine?
Hello all, As you know, on a single connection, Firebird's cpu utilization calculate by 100% / no of cores. On my 6 cores cpus, its only utilized 100% / 6 = 16%. During database maintenance & testing (backup, restore, testing, etc) , sometimes, i feel it was to slow. I want to change cpu utilization only for my own use. I have downloaded Firebird source code, please help me to point out which codes I should change & if my knowledge is enough if I only have a basic C knowledge? Thanks in advance. Anto
[firebird-support] How to change cpu utilization in Firebird engine?
Hello all, As you know, on a single connection, Firebird's cpu utilization calculate by 100% / no of cores. On my 6 cores cpus, its only utilized 100% / 6 = 16%. During database maintenance & testing (backup, restore, testing, etc) , sometimes, i feel it was to slow. I want to change cpu utilization only for my own use. I have downloaded Firebird source code, please help me to point out which codes I should change & if my knowledge is enough is I only have basic C? Thanks in advance. Anto
Re: Re[2]: [firebird-support] How to change cpu u tilization in Firebird engine?
Not related to queries, like I said before, it was for maintenance such as backup / restore, delete, insert,etc. From those operations, I noticed that Firebird 3.0 SS didn't use 100% cpu utilization. For queries, i guest, the behavior will be same If cpu utilization can be set, perhaps, all operation, including queries can benefit from higher cpu performance. Regards, Anto
Re: [firebird-support] How to change cpu utilization in Firebird engine?
Thanks for your clarification. I was planning to upgrade my cpu with a used Xeon 2683 V3 (price on my country is about the same with I7 6700K), but it has 14 cores & 35MB L3 cache. So, I guess, a single connection in Firebird 3.0 will running poorly on Xeon 2683 V3, it will only utilized about 7% cpu. I have to re evaluate again this plan. Btw, if there are 6 connection on 4 cores, how is cpu utilization calculate? Thanks & regards, Anto.
[firebird-support] Ryzen R7 vs Kaby Lake 7700K, which one to choose?
Hi all, I am considering to upgrade my development cpu. Between Ryzen & KL, although Ryzen is very good at multi threaded processing, I guest, for Firebird, KL will be faster. For development, mostly I used only single connection to FB, and since FB distributed workload among cores, for a single connection, total utilization for all cores is the same as 100% utilisation for 1 core, and for 1 core operation, KL is more superior than Ryzen. Can anyone confirm this? Or has anyone here have a Ryzen and care to share the experience using Ryzen with FB? Thanks & regards, Anto
[firebird-support] Hint for maximaze FB performance, CPU Utilization - again.
Hi guys, I am using FB 3.02 in Superserver mode. During my development - only single connection to FB Server, I do a lots of query. Each query took about 25-30 secs to complete, but CPU Utilization only used at around 16% on my six cores computer. On Ryzen 7 with 16 threads, I believe CPU utilization for FB is only 6%. Are there any hints to set Firebird to use more cpu's power? I think FB distributed works load based on number of cores. Each load divided equally on cores. So if there are 4 cores, each load/connection gets 1/4 cpu utilization. It will great if FB can distribute work load based on connection, first connection gets 100% cpu utilization, second connection, each one, get 50% cpu utilization, so on. Btw, on SuperClassic FB 2.5 & no of connection +/- 100, I rarely saw cpu utilization raised up to 90% during heavy process. Mostly it was aroung 50-60% CPU Utilization. Thanks & regards, Anto
RE: [firebird-support] Huge performance different from FB 2.5.2 vs FB 3.02
Hi, Thanks for the tips. I will test again with your tip. Those Query plan created automaticaly by Firebird. But on FB 3.0.2, it was a bad plan that hurt performance a lot. I want to moved to FB 3, but because of this, i am hesited. Anto.
[firebird-support] Huge performance different FB 2.5.2 vs FB 3.02
Hello guys,
I have this query :
SELECT a.BB, a.PLUS, a.MINUS, a.ENDBLNC, vw.UNT,vw.SLS_PRC_DS,vw.SLS_PRC_DT
from GET_STCK_MUT_PSG_CNT('09-18-17','09-18-17','All') a, M_FNGD_VW vw
where a.ID = vw.FNGD_ID
and a.ENDBLNC >= 0
order by vw.NM,vw.cl, vw.SZ_CD, vw.ASST_CD
On SuperClasic FB 2.52 on Win 7 64, it took +/- 50 secs :
PLAN SORT (JOIN (JOIN (JOIN (JOIN (G_T_IN_OT3 NATURAL, A INDEX (IDX_M_FNGD3)),
A INDEX (IDX_M_FNGD3), SORT (JOIN (H NATURAL, B INDEX (IDX_T_IN_OT11), J INDEX
(IDX_G_TMP23), A INDEX (T_IN_OT2_T_IN_OT1), C INDEX (RDB$PRIMARY25), D INDEX
(RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), G INDEX (RDB$PRIMARY22), F INDEX
(RDB$PRIMARY23))), A INDEX (IDX_M_FNGD3), A INDEX (IDX_M_FNGD3), SORT (JOIN (H
NATURAL, B INDEX (IDX_T_IN_OT11), J INDEX (IDX_G_TMP23), A INDEX
(T_IN_OT2_T_IN_OT1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX
(RDB$PRIMARY24), G INDEX (RDB$PRIMARY22), F INDEX (RDB$PRIMARY23, A INDEX
(IDX_M_FNGD3), A INDEX (IDX_M_FNGD3), SORT (JOIN (H NATURAL, B INDEX
(IDX_T_IN_OT11), J INDEX (IDX_G_TMP23), A INDEX (T_IN_OT2_T_IN_OT1), C INDEX
(RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), G INDEX
(RDB$PRIMARY22), F INDEX (RDB$PRIMARY23, SORT (G_T_IN_OT3 NATURAL))(VW A
INDEX (RDB$PRIMARY25))(VW C INDEX (RDB$PRIMARY28))(VW D INDEX
(RDB$PRIMARY21))(VW E INDEX (RDB$PRIMARY22))(VW F INDEX (RDB$PRIMARY24))(VW J
INDEX (RDB$PRIMARY23))(VW B INDEX (RDB$PRIMARY27))(VW H INDEX
(RDB$PRIMARY47))(VW I INDEX (RDB$PRIMARY48))(VW G INDEX (RDB$PRIMARY26)))
Executing...
Done.
70901350 fetches, 52178 marks, 236349 reads, 19 writes.
25577 inserts, 0 updates, 0 deletes, 18067187 index, 33745 seq.
Delta memory: 19474744 bytes.
G_T_IN_OT3: 25577 inserts.
Total execution time: 49.882s
Script execution finished.
On SuperServer FB 3.02, it took about 4.5 minutes :
PLAN SORT (JOIN (JOIN (JOIN (JOIN (A NATURAL, VW A INDEX (RDB$PRIMARY25)), VW
C INDEX (RDB$PRIMARY28), VW D INDEX (RDB$PRIMARY21), VW E INDEX
(RDB$PRIMARY22), VW F INDEX (RDB$PRIMARY24), VW J INDEX (RDB$PRIMARY23)), VW B
INDEX (RDB$PRIMARY27), VW H INDEX (RDB$PRIMARY47), VW I INDEX (RDB$PRIMARY48)),
VW G INDEX (RDB$PRIMARY26)))
Executing...
Done.
290144826 fetches, 51085 marks, 756444 reads, 15 writes.
25187 inserts, 0 updates, 0 deletes, 93504000 index, 25607 seq.
Delta memory: 21251608 bytes.
G_T_IN_OT3: 25187 inserts.
Total execution time: 0:04:31 (hh:mm:ss)
Script execution finished.
Huge different. Any idea how to fix this on FB 3.02 ?
Thanks & regards,
Anto
[firebird-support] Huge performance different from FB 2.5.2 vs FB 3.02
Hello guys,
I have this Query
select c.ID , e.CD, f.CD, sum(a.STCK_CLC) as qty, e.UNT, d.ID, g.ID
from t_in_ot2 a, t_in_ot1 b, M_FNGD c, M_ART d, M_Sz e, M_ASST f, M_CLR g,
m_trs_typ h, G_TMP j
where d.ID = c.ART_ID
and g.ID = c.CLR_ID
and e.ID = c.SZ_ID
and f.ID = c.ASST_ID
and c.id = a.id_gd
and b.id = a.id_in_ot1
and b.id_trs_typ = h.ID
and h.STCK_CLC is not NULL
and h.STCK_TYP = 'F'
and d.TYP = 'F'
and b.is_cls = 'F'
and b.trs_dt BETWEEN '12/31/11' and '09-18-17'
and b.id_div_lc = j.ID
and e.UNT in('PSG','CT')
and b.CHCK_BY is not null
and b.cncl_by is null
and a.IS_VW = 'Y'
and f.ID = c.ASST_ID
and g.ID = c.CLR_ID
GROUP by c.ID, e.cd, f.cd, e.UNT, d.ID, g.ID
On SuperClasic FB 2.5.2 on Win 7 64, it took about 48 sec, here is the PLAN :
PLAN SORT (JOIN (H NATURAL, B INDEX (IDX_T_IN_OT11), J INDEX (IDX_G_TMP23), A
INDEX (T_IN_OT2_T_IN_OT1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E
INDEX (RDB$PRIMARY24), G INDEX (RDB$PRIMARY22), F INDEX (RDB$PRIMARY23)))
Executing...
Done.
70013857 fetches, 20 marks, 235849 reads, 27 writes.
0 inserts, 0 updates, 0 deletes, 17905601 index, 8120 seq.
Delta memory: 74656432 bytes.
Total execution time: 48.672s
Script execution finished.
On SuperServer FB 3.0.2 on Win 7 64, it took about 4.5 minutes, here is the
PLAN
PLAN SORT (JOIN (JOIN (JOIN (JOIN (A NATURAL, VW A INDEX (RDB$PRIMARY25)), VW
C INDEX (RDB$PRIMARY28), VW D INDEX (RDB$PRIMARY21), VW E INDEX
(RDB$PRIMARY22), VW F INDEX (RDB$PRIMARY24), VW J INDEX (RDB$PRIMARY23)), VW B
INDEX (RDB$PRIMARY27), VW H INDEX (RDB$PRIMARY47), VW I INDEX (RDB$PRIMARY48)),
VW G INDEX (RDB$PRIMARY26)))
Executing...
Done.
290144826 fetches, 51085 marks, 756444 reads, 15 writes.
25187 inserts, 0 updates, 0 deletes, 93504000 index, 25607 seq.
Delta memory: 21251608 bytes.
G_T_IN_OT3: 25187 inserts.
Total execution time: 0:04:31 (hh:mm:ss)
Script execution finished.
Huge different ! This is because FB 3.0.2 took different PLAN from FB 2.5.2.
When PLAN from FB 2.5.2 applied to FB 3.0.2, execution time almost the same.
Looks like FB 3.0.2 Query PLAN not as good as FB 2.5.2.
How to make sure that Query PLAN from FB 3.0.2 is the optimum one? Bad plan
affect performance badly.
Thanks & regards,
Anto.
[firebird-support] FB 2.5.2 has a better query plan than FB 3.0.2
Hello guys, Just want to inform that FB 2.5.2, mostly have a better query plan than FB 3.0.2. I have 2 PCs, A & B, both have Win 7 x64 and almost identical hardware. On PC A, FB SC 2.5.2 installed, on PC B, SS 3.0.2 installed. Query on PC A, that process about 150K records, retrieved about 19 secs. Backup database on PC A and restored it on PC B and run same query on PC B, it took about 44 secs to finished. The different is because FB 3.0.2 took different plan than FB 2.5.2. Tested again on FB 3.0.2, but this time applied query plan from FB 2.5.2, it took about 19 secs, same with FB 2.5.2. It seem that FB 2.5.2 has a better query plan than FB 3.0.2, in fact it has also better than other newer version such as FB 2.5.7. Keep in mind that this test was not took intensively, it was only observe during real time usage, since I have FB 2.5.2 & FB 3.0.2 runs at the same time. Regards, Anto
[firebird-support] Is Firebird favor Intel's CPU?
Hello all, I ran an update query on a database with a computer and a notebook. CPU's computer is Phenom 1075T, while notebook is i5 2557M. Query is very simple, something like : update tbl set ln = 9; Firebird on Computer is SS 3.0.3, on notebook SS 3.0.2, both runs on Windows x64, both using SSD with roughly, same speed. I thought, query will runs much faster on a computer, but to my surprise, it runs almost the same time on both machine. Query runs in a single thread, on computer, runs around 3-3.5GHz, on notebook, runs about 2.7GHz. I am planning to upgrade my computer to ryzen, but with this result, make me wonder if Firebird favor intel's cpu. Regards, Anto
Re: [firebird-support] Is Firebird favor Intel's CPU?
I don't think so. When notebook speed to limit only 0.8GHz, process drop significantly. Update query, something like : update tbl, set ln=-999 where id = 1 Id is primary key in tbl. I think CPU processing was used to calculate search for index.
[firebird-support] Re: Is Firebird favor Intel's CPU?
I don't think so. When notebook speed limit to only 0.8GHz, process drop significantly. Update query, something like : update tbl, set ln=-999 where id = 1 Id is primary key in tbl. I think CPU processing was used to search for index.
Re: [firebird-support] Is Firebird favor Intel's CPU?
I don't think so. When notebook speed to limit only 0.8GHz, process drop significantly. Update query, something like : update tbl, set ln=-999 where id = 1 Id is primary key in tbl. I think CPU processing was used to calculate search for index.
Re: [firebird-support] Is Firebird favor Intel's CPU?
I don't think so. When CPU speed on notebook limit to 0.8GHz process drop significantly.
Re: [firebird-support] Re: Is Firebird favor Intel's CPU?
Yes it was update only 1 record at the time. But total records need to be updated is about 19.500.000. So I made a small program that update 1.000.000 record and that's how the calculation came out. If only updated 1000 records at the time, the desktop runs faster but when it reach 1.000.000 records , 2.7GHz speed of notebook is about same speed with 3.0-3.5GHZ on desktop.
[firebird-support] How to insert characters for barcode code 128 to table via code?
Hi guys, For example, I have these characters : Í*B#È4jÎ I can copy paste that characters with flamerobin into Firebird table, but how to insert it via code? Thanks & regards, Anto
[firebird-support] Page cache size in Firebird 3.0.4
Hi guys,
I am running SS FB 3.0.4 x64 in Windows 10 x64 with 16GB RAM.
Database properties
ODS Version 12
Page size 16384
Pages 437728
Size on disk 6.68GB
Page buffers 10240
Read only false
Settings
Dialect 3
Default character set NONE
Sweep interval 2
Forced writes
Database alias in databases.conf file
=
my_dba = C:\Dba\my_dba.fdb
{
FileSystemCacheThreshold = 2M
LockMemSize = 16M
LockHashSlots = 30011
TempCacheLimit = 2048M
TempBlockSize = 2M
DefaultDbCachePages = 65536
}
I ran a query with flamerobin, this is the statistic result :
=
46220441 fetches, 17965 marks, 172471 reads, 82 writes.
8847 inserts, 0 updates, 0 deletes, 15730299 index, 8847 seq.
Delta memory: 4206064 bytes.
G_TMP: 8847 inserts.
Total execution time: 52.434s
Script execution finished.
On Windows Task Manager, Firebird Server took 236MB RAM, max.
If not mistaken, Page Cache Size should be
Page Size x DefaultDbCachePages = 16384 x 65536 = ~ 1GB
Why Firebird server only took 236 MB RAM ? Is this normal or something missed
here ?
Thanks & regards,
Anto
Re: [firebird-support] How to know if FileSystemCacheThreshold works?
Thanks for your reply. Unfortunately, RAMMap don't work in Windows 10.
[firebird-support] Firebird 3.0 performance, Windows 10 vs Mint, big different
Hi guys, I compared firebird 3 SS performance in windows 10 pro 64 vs Linux mint 19.1 cinnamon 64 bit. Both OS use the same database configurations & almost the same speed ssd. Windows use a bit faster ssd. On windows 10, I have turn off everything than can be turn off, such as bloat ware, unnecessary services, background applications, etc. Database page buffer set to 131072, and page size set to 16384. Both OS have 16GB RAM. File cached threshold, set to 384000. Run query a few times via flamerobin, the results were, Firebird run faster on Mint, at least 20% faster, that was a big different. Don't know why on windows the performance was bad. Perhaps on windows 10, file cached didn't work, cause I saw the disk kept spinning while query ran. This is strange, on Windows 7, with the same database configuration, firebird took about 5GB RAM, which means that file cached work fine. Couldn't inspect actual RAM usage on windows 10 cause RAMMap doesn't work.
Re: [firebird-support] How to know if FileSystemCacheThreshold works?
Hello, Thanks for the replied. I have downloaded new RAMMap ver 1.52 and it work on Windows 10. On Process tab of RAMMap, firebird.exe took 2,183,484K. On File details tab, database file took 2,603,168K. Does it means, FileCached worked? It if worked, how come, the disk kept spinning when query runs? Thanks & regards, Anto.
Re: [firebird-support] Firebird 3.0 performance, Windows 10 vs Mint, big different
Thanks for your info. Downloaded ver 1.52, now it worked. On Process tab of RAMMap, firebird.exe took 2,183,484K. On File details tab, database file took 2,603,168K. Don't know, if this means file cached work or not. If file cached worked, then there is something else that make Firebird runs slower on Windows 10. Anto
Re: [firebird-support] Firebird 3.0 performance, Windows 10 vs Mint, big different
Hello, I have tried to enable LargeSystemCache on Windows 10, no effect. With or without LargeSystemCache, the second query run on Windows 10 was about 33% faster than the first run, I guest File Cached did work although still saw disk activity. But even the second query runs, it was still about 20% slower than Linux Mint. I will avoid Windows and use Linux for firebird if it is possible. Regards, Anto.
Re: [firebird-support] Firebird 3.0 performance, Windows 10 vs Mint, big different
Thanks for the info. I downloaded ver 1.52, now it works. As I expected, on Windows 10, File System Cached doesn't work. Discussion continue here, as it is a related topic https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/134237 https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/134237
[firebird-support] How to know if FileSystemCacheThreshold works?
Hi guys,
I have FB 3.04 SS in windows 10.
I have a database with these setting on database.conf
my_dba = C:\Dba\db.fdb
{
FileSystemCacheThreshold = 393216
LockMemSize = 16M
LockHashSlots = 30011
TempCacheLimit = 2048M
TempBlockSize = 2M
FileSystemCacheSize = 50
}
Database page size : 16834
Database pagebuffers : 131072
Size of database file is about 8GB. Total Windows RAM, 16GB, free RAM, more
than 8GB.
I run the same query via flamerobin about three time, then looked on WIndows
Task Manager and each time the query was running, the disk kept spinning.
Does it means that Firebird's FileSystemCache doesn't works? How to know if it
works?
Thanks in advance.
[firebird-support] FB 3.0 SS WriteFile Error
Hi guys, I have installed FB 3.04 on Windows 7 64 bit. On Firebird configuration file, have set Temporary Directory to e:\;d:\temp. I got an error : I/O error during "WriteFile" operation for file "e:\fb_table_u9" Error while trying to write to file. There is enough space on the disk. I checked drive e, there a file fb_table_u9, the size is about 2 GB and drive e has about 3.3 GB free space. Database file size is about 8 GB. Drive e is a ram drive, size is 8 GB. How many space needed for temporary file? 3 GB is not enough? If it was not enough, why Firebird not switch to drive d? Thanks in advance. Anto
