Re: [firebird-support] Index Memory Usage
Re: [firebird-support] Awaiting Garbage Collector
At 08:09 a.m. 19/03/2015, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] wrote: >Ask your software vendor to fix their client transaction management. >Using auto commit as default transaction management mechanism is >Firebird's enemy number 1. Not only due to OAT not moving forward, you >may also reach the 32-bit transaction id limit, depending on your load, >in weeks/months until a backup/restore cycle is mandatory. It is not AutoCommit, per se, that prevents the OAT from moving forward, but another transaction setting, COMMIT WITH RETAIN. Assuming your software is written with Delphi or a derivative, the setting is called CommitRetaining. The hangup can be cleared if the software also periodically performs a hard COMMIT on the offending set. This isn't something you can fix yourself, if you don't have access to the source of the client code. Thomas' comments stand true, regarding AutoCommit eating up the transaction count. There may be another flaw in the transaction management: if a set is being used for display and select but not for writing - such as in a drill-down scenario or a lookup - that runs throughout the user session in a read/write transaction with ReadCommitted visibility, then this will hold the OAT back as well. When such sets run in a read-only transaction, they do not cause the OAT to get stuck. Again, this is for the software vendor to fix. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of "The Firebird Book" and "The Firebird Book Second Edition" http://www.firebird-books.net __
[firebird-support] Re: Awaiting Garbage Collector
Thank you for your answers Thomas. As we have found this problem to be very critical to the software performance we're restarting the firebird service every night, also sweep and weekly backup/restore. As for the client/connection transactions, we're interested because there are several pieces of software developed by the vendor that accesses the same database, then for us is very important to know which of those is causing the problem. Regards.
[firebird-support] Re: Awaiting Garbage Collector
Thank you for your answers Thomas. As we have found this problem to be very critical to the software performance we're restarting the firebird service every night, also sweep and weekly backup/restore. As for the client/connection transactions, we're interested because there are several pieces of software developed by the vendor that accesses the same database, then for us is very important to know which of those is causing the problem. Regards.
[firebird-support] Re: Awaiting Garbage Collector
Thank you for your answers Stephen. As we have found this problem to be very critical to the software performance we're restarting the firebird service every night, also sweep and weekly backup/restore. As for the client/connection transactions, we're interested because there are several pieces of software developed by the vendor that accesses the same database, then for us is very important to know which of those is causing the problem. Regards.
Re: [firebird-support] Awaiting Garbage Collector
> Hello, > I'll try explain the situation in which we are in the shortest way > possible... > > We're using a software in our company made by some people in Spain, the > software supports 70 simultaneous users and we are experiencing slowness > using the software so I started the investigation, and not later found > that our database was increasing awaiting garbage transactions every > second, as after 4 or 5 hours the number of awaiting transactions are up > to 90.000. I've been reading about Firebird and its retaining legacy > commit and performance degradation, but no one tells if 10.000 or 20.000 > or even 100.000 awaiting gc transactions could be the reason of the poor > software performance. I have confirmation from the programmer that the > component used to connect to firebird is DEVART IBDAC, with the > AutoCommit flag set to TRUE. We have a Firebird 2.5 Classic Server,and > also tried with Super Classic server architecture, but no difference, on > a linux box, plenty of RAM and CPU and disk speed. Ask your software vendor to fix their client transaction management. Using auto commit as default transaction management mechanism is Firebird's enemy number 1. Not only due to OAT not moving forward, you may also reach the 32-bit transaction id limit, depending on your load, in weeks/months until a backup/restore cycle is mandatory. > I'm using Sintatica to monitor the database. Sinatica only tells the > amount of transactions that are awaiting GC. I would like to know the > amount of transactions awaiting gc per user or per connection, so I can > know which client is piling up the most of transactions. Is there a > system table or another software monitor that could tell me that > information? If the software is the only piece accessing the database, usually it does not gain any benefit to know which user/connection. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Index Memory Usage
> Hi All, can anyone tell me (or point me in the direction of manuals) how > i can calculate the amount of memory used by the indexes of a particular > table? > I have a table in which I store sha1 hashes (currently about 2 million > records) as char(40) and its indexed, I'm wondering if the table would > consume less memory if the hashes are stored as BIGINT Use gstat -i to get the number of data pages occupied by an index multiplied by the page size of the database. This should give you a rough estimation on disk space used in KB for an index. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Revert DB to previous version
On 18/03/15 17:37, sboyd...@gmail.com [firebird-support] wrote: > If I understand what you are saying, you want me to copy gbak.exe for > 2.0.5 to the 2.1 system and use the 2.0.5 gbak to back up the 2.1 > database using the 2.1 Firebird service. Correct? Spot on ... But it will not work if you have added any '2.1' features since it was upgraded. If only data has been played with it shouldd work fine. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
Re: [firebird-support] Revert DB to previous version
Andrei; If I understand what you are saying, you want me to copy gbak.exe for 2.0.5 to the 2.1 system and use the 2.0.5 gbak to back up the 2.1 database using the 2.1 Firebird service. Correct?
Re: [firebird-support] Revert DB to previous version
With Firebird 2.1 installed, create a backup using gbak from 2.0.5 version. After that, do a restore with FB 2.0.5 installed. []s Andrei 2015-03-18 12:59 GMT-03:00 sboyd...@gmail.com [firebird-support] < firebird-support@yahoogroups.com>: > > > I need to be able to revert an existing 2.1 database to 2.0.5. How can I > accomplish this. GBAK won't do it. It tells me: > > > ERROR: Expected backup version 1,2,3,4,5,6 or 7. Found 8. > > > > > > >
Re: [firebird-support] Revert DB to previous version
On 18.03.2015 16:59, sboyd...@gmail.com [firebird-support] wrote: > > > I need to be able to revert an existing 2.1 database to 2.0.5. How can I > accomplish this. GBAK won't do it. It tells me: > > > ERROR: Expected backup version 1,2,3,4,5,6 or 7. Found 8. Hej, you've done the backup with the 2.1 fb, that won't work IMHO the clean solution would be to create a new empty database with the appropriate structure and pump the data afterwards. hth, Marcus > > > > [Non-text portions of this message have been removed]
[firebird-support] Revert DB to previous version
I need to be able to revert an existing 2.1 database to 2.0.5. How can I accomplish this. GBAK won't do it. It tells me: ERROR: Expected backup version 1,2,3,4,5,6 or 7. Found 8.
[firebird-support] ANN Firebird 2015 Tour: SQL optimization
Hi All, We invite all Firebird developers and administrators to 1-day seminar in the frames of Firebird 2015 Tour. Firebird 2015 Tour is devoted to the SQL performance optimization: SQL plans, queries and database statistics, query tuning techniques and deep understanding of undercover work of Firebird. Firebird 2015 Tour is organized by IBSurgeon and IBPhoenix, with support of Firebird Developers core team. Locations and dates of Firebird 2015 Tour (see details in the appropriate sections below): April 24 - Seligenstadt, Germany May 19 – Prague, Czech Republic June 5 – Moscow, Russia Topics of Firebird 2015 Tour seminar: 1)Performance of SELECT queries a.PLANs: what are INDEX, SORT, ORDER, NATURAL and how to manage them b.Execution statistics interpretation, data paths, cache hits c.Tracking slow and resource-consuming resources through MON$ and Trace API d.Firebird configuration parameters e.Best practices, tips and tricks to speed up queries 2)Performance of INSERT queries a.Typical errors which lead to slow INSERTs b.How indices, triggers and database structure affect INSERT performance c.Transactions and INSERTs d.Best practices to speed up INSERTs 3)Performance of UPDATE and DELETE queries a.General approach for updates and deletes b.How indices, triggers and database structure affect UPDATE and DELETE performance c.Update Conflicts, transactions and their resolution, DELETE, SELECT WITH LOCK d.Real-life example of UPDATEs algorithm without conflicts 4)Multi-user performance optimization a.Locks, mutexes and races for resources b.Selecting Firebird architectures and tuning their parameters for multi-user environments Locations details for Firebird 2015 Tour April 24, Seligenstadt, Germany Seminar will take place in Hotel Columbus (www.hotel-columbus.de/contact.php/), April 24, 2015. Important! All topics are presented in English! Seminar's schedule: 9-30-10-00 Registration 10-00 - 17-00 Seminar (with Lunch and Coffee breaks) Attendee's package includes seminar’s materials, lunch and coffee breaks. The early bird registration fee is EUR 49 (till April 5, volume discounts are available). https://secure.shareit.com/shareit/checkout.html?productid=300604679 May 19, Prague, Czech Republic Seminar will take in the Hotel Extoll Inn (http://extolinn.cz/en/contact.htm), May 19, 2015. Important! All topics are presented in English! Seminar's schedule: 9-30-10-00 Registration 10-00 - 17-00 Seminar 10-00 - 17-00 Seminar (with Lunch and Coffee breaks) Attendee's package includes seminar’s materials, lunch and coffee breaks. The early bird registration fee is EUR 39 (till April 25, volume discounts are available) https://secure.shareit.com/shareit/checkout.html?productid=300660565 June 5, Moscow, Russia Seminar will take place in TechnoPark «Slava» (www.technopark-slava.ru/contacts), June 5, 2015. Important! All topics are presented in Russian! Seminar's schedule: 9-30-10-00 Registration 10-00 - 17-00 Seminar (with Lunch and Coffee breaks) Attendee's package includes seminar’s materials and coffee breaks. The early bird registration fee is EUR 39 (roubles 2800) (till April 25). https://secure.shareit.com/shareit/checkout.html?productid=300604678 Для получения российского счета на оплату напишите sa...@ibase.ru Questions? Feel free to ask any questions at firebird2...@ib-aid.com Regards, Alexey Kovyazin IBSurgeon www.ib-aid.com
