Re: [firebird-support] Why is this setup not working?
Hi Alexey Kovyazin. Thanks for your reply. Here are some information about the server. 2x AMD 2.20 GHz 32GB Ram 64bit Windows server 2008 R2 Standard The firebird DB size is 6.2gb. I have the firebird conf but im not sure how this yahoo forum works. Can I attach files somehow? The main issue (we think) is the database connections and not the queries. We have made an extensive work in optimizing the queries and havent registred any issues with the queries. When we restart the webcluster all transactions are closed and the transaction counter is at zero. When we start the cluster and the connections start to build up we see the problem begin at 400-500~ connections. There are no errors in the system log nor in the firebird error log. /Ronnie ---In firebird-support@yahoogroups.com, ak@... wrote: Hi Ronnie, You did not provide key characteristics of the server, database and transaction statistics, so please don't expect that you will have reasonable answer (load of RAM and CPU are not characteristics of the server. People have various opinions about big' and small). You also did not provide firebird.conf, operation system settings, database size, typical queries and their lifecycle, etc. According to our experience I can suppose that the answer for your question Why is this setup not working? is the following: probably database application design and/or transaction management have problems. Can it be fixed? Sure - with more details. Regards, Alexey Kovyazin www.IBSurgeon.com Hey guys. We have a webcluster consisting of 8 webservers running a PHP application with a firebird DB. The application have a rather hard peak one time a day and firebird are not able to give us the firebird connections quick enough. When the peak start we experience long responses and timeouts from the webservers due to the connection with the firebird DB. We expected the DB connections to be the problem so we then used persistent connections and told Apache2 to keep its threads for 2500 requests. This made a significant performance boost since we would have the connections ready when we hit the peak. At the peak we have 4000 request pr/m. Now letting Apache2 handle the connection pooling is not a nice way of doing things. We have been looking for a connection pool for firebird but haven't been able to find any software that supports firebird(only one but it didn't support the use of stored procedures)? Does anyone know of any software that can make connection pooling with firebird? The next thing is that even though we keep the connections open we still have timeouts due to the rotation of Apache2 processes. Everytime it rotates it needs to make a new persistent connection and this either give a long response time (20-30 sec) or more that resolves in a timeout from the webserver. The firebird database have ~640 connections when the pool have been build. If we restart our cluster and let Apache2 build up its connection pool we see some strange behavior. Everything is fine until the database hits ~400-~450 connections. Then the timeouts begin. Are we really pushing firebird to its limits? Prier we had issues with firebird as well that let us to shard the database into two instances(on separate hardware). Again this gave us a boost because the load was separated. We have around 1.800.000 requests on one day. This leads to around 720 process rotations that the firebird db is not handling well. We are running firebird WI-V2.5.2.26539 Firebird 2.5. on two windows servers. The physical hardware is very potent. We have load of RAM and CPU and have a flash card installed. If we look at the resources on the physical server there is nothing to see. We do see more resources being used on the peak as is expected. But we are not near on depleting the resources of the hardware. /Ronnie
Re: AW: AW: AW: [firebird-support] difficult question
Olaf, Stil for this problem, there is a possible solution. If you have only one user at each time, and if the user have the possibility of deleting only the last that have been inserted, you can use a delete trigger, that will renew the value of the generator (the next value that will be generated) in a manner that the next new id to be generated will be the one that has been deleted. Inside the trigger, you can compare the Id of the record to be deleted with the generator value to be sure that the last generated will be deleted and, if really so, update the value of the generator. This will need some learning and tests of you, but if so is the usage environment, there is a big chance you will get this resolved. Good luck, Roberto Camargo. On Monday, December 9, 2013 3:56 PM, Mark Rotteveel m...@lawinegevaar.nl wrote: On 9-12-2013 19:29, checkmail wrote: I have change it, now the IDs were created by trigger. Only one disadvantage arises, if the user deletes the last recordset and creates a new, the last skipped now. Why is that a disadvantage? Holes in your numbering would also happen if a user deleted a different recordset (eg the second last) instead of the last. Mark -- Mark Rotteveel ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links (Yahoo! ID required) http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Why is this setup not working?
On 10 Dec 2013 01:53:42 -0800, talker3...@hotmail.com wrote: Hi Alexey Kovyazin. Thanks for your reply. Here are some information about the server. 2x AMD 2.20 GHz 32GB Ram 64bit Windows server 2008 R2 Standard The firebird DB size is 6.2gb. I think the most important piece of information you left out is what architecture of Firebird you use: Classic, SuperClassic or SuperServer. I have the firebird conf but im not sure how this yahoo forum works. Can I attach files somehow? No you can't attach files. Either post it on something like pastebin, or include all non-default settings in your mail (not the entire conf file please) Mark
Re: [firebird-support] Retrieving relation and field info from system tables
On Mon, Dec 9, 2013 at 6:58 PM, Christian Gütter n...@guetter.org wrote: nobody replied to the questions I asked about the Firebird system tables some days ago. Maybe they were a bit too lengthy and specific. I am wondering if anybody could point me to some documentation regarding the Firebird system tables instead, as I could not find any. Probably not in the InterBase books - or the Firebird Book (which I recommend highly and is available through ibphoenix.com) - is the reason for the very odd numeric values for datatypes. Firebird is the open source clone of InterBase. InterBase was designed to be compatible at the API level with the VAX Rdb products. The level of compatibility was such that if you pointed gbak at an Rdb/VMS database, backed it up, it could be restored as an InterBase database and all the applications would work if they invoked the InterBase client library rather than the Rdb client library. The Rdb products used the VAX/VMS datatype codes, plus some out-of-band codes for non VMS types like blobs. That heritage is also the reason for the obscure error code values - every project at DEC had a unique hex prefix for its error messages so different products wouldn't conflict. The codes look much better in hex than in decimal. Good luck, Ann
[firebird-support] Single Row Table Becomes Unresponsive
Hi, We're using Firebird 2.1.2.1818 on Centos 5.9. We have a third party replication product SymmetricDS, that accesses a single row table very often to do reads and writes. (It seems to be using the table to manage some sort of locking between SymmetricDS processes.) Very occasionally, this table becomes unresponsive. The SymmetricDS process hangs and IBExpert cannot access the table. Looking at the Firebird server box we can see a connection that we cannot kill, even with kill -9. The only way we can resolve the problem is to reboot the Firebird machine. Does anyone have any ideas on this? Thanks, Greg
Re: [firebird-support] Single Row Table Becomes Unresponsive
Hi Greg, Run gstat -r or IBAnalyst and check number of record versions in this table. I suppose there could be millions of versions. Regards, Alexey Kovyazin IBSurgeon Hi, We're using Firebird 2.1.2.1818 on Centos 5.9. We have a third party replication product SymmetricDS, that accesses a single row table very often to do reads and writes. (It seems to be using the table to manage some sort of locking between SymmetricDS processes.) Very occasionally, this table becomes unresponsive. The SymmetricDS process hangs and IBExpert cannot access the table. Looking at the Firebird server box we can see a connection that we cannot kill, even with kill -9. The only way we can resolve the problem is to reboot the Firebird machine. Does anyone have any ideas on this? Thanks, Greg
Re: [firebird-support] Single Row Table Becomes Unresponsive
We're using Firebird 2.1.2.1818 on Centos 5.9. We have a third party replication product SymmetricDS, that accesses a single row table very often to do reads and writes. (It seems to be using the table to manage some sort of locking between SymmetricDS processes.) Very occasionally, this table becomes unresponsive. The SymmetricDS process hangs and IBExpert cannot access the table. Looking at the Firebird server box we can see a connection that we cannot kill, even with kill -9. The only way we can resolve the problem is to reboot the Firebird machine. Does anyone have any ideas on this? Updating a single row very often (what is very often in your case?) results in a hot-spot in your environment. Even worse if pessimistic locking is used upon read to ensure serialization if the requirement is to have atomic values. Often used for invoice numbers without gaps, thus generators are out of question. Even with optimistic locking, read requests might fail if you use read committed isolation level without record versioning. The number of record versions for this hot-spot row is also something you should keep an eye on. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.