Re: [firebird-support] Small Database with very very bad performance on LAN
> Hallo, > > Il 08/01/2016 16.22, Tim Ward t...@telensa.com [firebird-support] ha scritto: >> What are your queries, query plans, query statistics? > > The queries are similar to: SELECT * FROM TABLE. > > Investigating for query plans, statistics, Buffers as suggested by > Thomas, I discovered the origin of the problem: > FB is innocent :) the guilty is me because I use ZEOSLib to connect to > FB and I used the property "SortedFields" instead of using the clause > "ORDER BY" in SQL query thinking that is more flexible on a Delphi RAD > development like Delphi :( A great use case for the Firebird Trace API to investigate what's up behind the scene of ZEOS. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ 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] Small Database with very very bad performance on LAN
Hallo, Il 08/01/2016 16.30, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] ha scritto: > You have to sort out where your current bottleneck is. > > Can you pin-point it to a particular query or is performance in general > bad? With Firebird 2.5, you have quite some monitoring amory, e.g. > monitoring tables (tables starting with MON$...) or the Trace API. The performance is bad in general, the navigation on a tables are too slow. I find the problem, it's me that i used ZeosLib property "SortedFields" on a TQuery" component. > Increase "Page buffers" to e.g. 1 (e.g. with gfix command-line > tool), then re-connect your client application and see if this helps. Before I Find the problem, seems not helps, it's a bit well but not helps. > There is other tuning stuff, I'm reading the Firebird Book written by Helen Borrie to understand the usage of FB but is hard reading over 1000 pages when the software must be ready for yesterday ;) There are some other pages to read for undestrand the tuning of FB? > getting a big picture on your slow statements, their > execution plan probably pointing to missing indexes ... is a good start. This helped me to find the problem. > Good luck. Thank you -- Luigi Siciliano -- ++ 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] Small Database with very very bad performance on LAN
Hallo, Il 08/01/2016 16.22, Tim Ward t...@telensa.com [firebird-support] ha scritto: > What are your queries, query plans, query statistics? The queries are similar to: SELECT * FROM TABLE. Investigating for query plans, statistics, Buffers as suggested by Thomas, I discovered the origin of the problem: FB is innocent :) the guilty is me because I use ZEOSLib to connect to FB and I used the property "SortedFields" instead of using the clause "ORDER BY" in SQL query thinking that is more flexible on a Delphi RAD development like Delphi :( Now the application seems faster and works best of the same application that use BDE :) Thanks to all. -- Luigi Siciliano -- ++ 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] Small Database with very very bad performance on LAN
Hallo, I have a small database (less 100MB) that works on Firebird 2.5.5 SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620. I seems to work very well in local but with the 3 clients on LAN works very bad. It is very very slow to open and navigate a simple table with less of 5.000 rows! What i can do? I very novice in Firebird. The result of gstat is: Database header page information: Flags 0 Checksum12345 Generation 26613 Page size 4096 ODS version 11.2 Oldest transaction 26603 Oldest active 26604 Oldest snapshot 26604 Next transaction26605 Bumped transaction 1 Sequence number 0 Next attachment ID 139 Implementation ID 26 Shadow count0 Page buffers0 Next header page0 Database dialect3 Creation date Jan 4, 2016 15:44:10 Attributes force write Variable header data: Sweep interval: 2 *END* Thanks -- Luigi Siciliano -- ++ 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] Small Database with very very bad performance on LAN
Hi, At January 8, 2016, 7:50 AM, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] wrote: > Hallo, >I have a small database (less 100MB) that works on Firebird 2.5.5 > SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620. > I seems to work very well in local but with the 3 clients on LAN works > very bad. It is very very slow to open and navigate a simple table with > less of 5.000 rows! > What i can do? Have you tried with SuperClassic or Classic? SuperServer 2.5.5 still is a single thread per database, so basically all connections to one database, all share the same thread. While SuperClassic, uses one thread per connection and Classic uses one instance per connection. With SuperServer 3.0, it will be a true multi-threaded environment where you shouldn't see this kind of performance hit, since each connection should be using their own thread. At the beginning, we were using SuperServer, but after customers reporting slowdowns, when at least one user was doing something in the software that did use a lot of resources out of Firebird(i.e.: a report with data analysis). We decided to try Classic(SuperClassic wasn't available at the time), and our customers are not longer reporting those slowdowns. So, give Classic or SuperClassic a try. Unless, if you still want SuperServer, give Firebird 3 Superserver Release Candidate 1 a try. > I very novice in Firebird. > The result of gstat is: > Database header page information: > Flags 0 > Checksum12345 > Generation 26613 > Page size 4096 > ODS version 11.2 > Oldest transaction 26603 > Oldest active 26604 > Oldest snapshot 26604 > Next transaction26605 > Bumped transaction 1 > Sequence number 0 > Next attachment ID 139 > Implementation ID 26 > Shadow count0 > Page buffers0 > Next header page0 > Database dialect3 > Creation date Jan 4, 2016 15:44:10 > Attributes force write > Variable header data: > Sweep interval: 2 > *END* These stats are really good. Are they taken when the slowdown occurs, with all the connections and queries active, or is it after they are closed? Also, make certain that you are using a read-only transaction for reading the data and use a short read/write transaction to insert/update/delete data. -- Best regards, Daniel Rail Senior Software Developer ACCRA Solutions Inc. (www.accra.ca) ACCRA Med Software Inc. (www.filopto.com)
AW: [firebird-support] Small Database with very very bad performance on LAN
Verbose :) -Ursprüngliche Nachricht- Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Freitag, 8. Januar 2016 16:30 An: firebird-support@yahoogroups.com Betreff: Re: [firebird-support] Small Database with very very bad performance on LAN Hello, > Hallo, > I have a small database (less 100MB) that works on Firebird 2.5.5 > SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620. > > I seems to work very well in local but with the 3 clients on LAN works > very bad. It is very very slow to open and navigate a simple table > with less of 5.000 rows! > > What i can do? You have to sort out where your current bottleneck is. Can you pin-point it to a particular query or is performance in general bad? With Firebird 2.5, you have quite some monitoring amory, e.g. monitoring tables (tables starting with MON$...) or the Trace API. Try to get a big picture with that tooling to see what's going, statement execution plans etc..., I/O statistics, e.g. is data being fetched from disk or served from the Firebird cache, which I doubt with your page buffers value below. > > I very novice in Firebird. > > The result of gstat is: > Database header page information: > Flags 0 > Checksum12345 > Generation 26613 > Page size 4096 > ODS version 11.2 > Oldest transaction 26603 > Oldest active 26604 > Oldest snapshot 26604 > Next transaction26605 > Bumped transaction 1 > Sequence number 0 > Next attachment ID 139 > Implementation ID 26 > Shadow count0 > Page buffers0 > Next header page0 > Database dialect3 > Creation date Jan 4, 2016 15:44:10 > Attributes force write > > Variable header data: > Sweep interval: 2 > *END* With SuperServer and a page size of 4096 and a page buffers set of 0, which gets overriden with a default value of 2048 from firebird.conf (if not specified otherwise), you end up with a Firebird page cache of 8MB (!) only for your database. Increase "Page buffers" to e.g. 1 (e.g. with gfix command-line tool), then re-connect your client application and see if this helps. There is other tuning stuff, but as a first step increasing the Firebird page cache + getting a big picture on your slow statements, their execution plan probably pointing to missing indexes ... is a good start. Good luck. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ 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] Small Database with very very bad performance on LAN
On 08/01/2016 11:50, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] wrote: > Hallo, > I have a small database (less 100MB) that works on Firebird 2.5.5 > SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620. > > I seems to work very well in local but with the 3 clients on LAN works > very bad. It is very very slow to open and navigate a simple table with > less of 5.000 rows! > > What i can do? What are your queries, query plans, query statistics? -- Tim Ward ++ 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] Small Database with very very bad performance on LAN
Hello, > Hallo, > I have a small database (less 100MB) that works on Firebird 2.5.5 > SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620. > > I seems to work very well in local but with the 3 clients on LAN works > very bad. It is very very slow to open and navigate a simple table with > less of 5.000 rows! > > What i can do? You have to sort out where your current bottleneck is. Can you pin-point it to a particular query or is performance in general bad? With Firebird 2.5, you have quite some monitoring amory, e.g. monitoring tables (tables starting with MON$...) or the Trace API. Try to get a big picture with that tooling to see what's going, statement execution plans etc..., I/O statistics, e.g. is data being fetched from disk or served from the Firebird cache, which I doubt with your page buffers value below. > > I very novice in Firebird. > > The result of gstat is: > Database header page information: > Flags 0 > Checksum12345 > Generation 26613 > Page size 4096 > ODS version 11.2 > Oldest transaction 26603 > Oldest active 26604 > Oldest snapshot 26604 > Next transaction26605 > Bumped transaction 1 > Sequence number 0 > Next attachment ID 139 > Implementation ID 26 > Shadow count0 > Page buffers0 > Next header page0 > Database dialect3 > Creation date Jan 4, 2016 15:44:10 > Attributes force write > > Variable header data: > Sweep interval: 2 > *END* With SuperServer and a page size of 4096 and a page buffers set of 0, which gets overriden with a default value of 2048 from firebird.conf (if not specified otherwise), you end up with a Firebird page cache of 8MB (!) only for your database. Increase "Page buffers" to e.g. 1 (e.g. with gfix command-line tool), then re-connect your client application and see if this helps. There is other tuning stuff, but as a first step increasing the Firebird page cache + getting a big picture on your slow statements, their execution plan probably pointing to missing indexes ... is a good start. Good luck. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ 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] Small Database with very very bad performance on LAN
Hallo, Il 08/01/2016 14.20, Daniel Rail dan...@accra.ca [firebird-support] ha scritto: Have you tried with SuperClassic or Classic? SuperServer 2.5.5 still Ok, I tried SC and CS and the performance on LAN seems best of SS but not good :( May be the performance of CS is best of SC but seems very similar. With CS or SC the performance are too slow for working, seems the same like the SS The same application with BDE (Borland DataBase engine) works fast even in LAN but the application with firebird is tool slow even contain the same data! Any other suggestion? Thanks -- Luigi Siciliano --
