[firebird-support] Table update performance dropped significantly within few days.
Hi guys. I have one table that currently has 100 000 000 records. PageSize is 16384. Index statistics for primary key are 0.00 (I've recomputed them). Over a few days inserting and updating this table slowed down significantly. When I try to update one row: UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343 update executes in 0.250 miliseconds. What is surprising for me that just few days ago everything was fine.. how could I speed thing up? What could happen over these few days? Thanks in advance.
Re: [firebird-support] Table update performance dropped significantly within few days.
Increase database page buffers and try again. It happens when you combine a large table with multiple indexes. Dropping one or two indices can also increase speed a lot in this case. Em 15/01/2014 16:30, brucedickin...@wp.pl escreveu: Hi guys. I have one table that currently has 100 000 000 records. PageSize is 16384. Index statistics for primary key are 0.00 (I've recomputed them). Over a few days inserting and updating this table slowed down significantly. When I try to update one row: UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343 update executes in 0.250 miliseconds. What is surprising for me that just few days ago everything was fine.. how could I speed thing up? What could happen over these few days? Thanks in advance.
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 16:30, brucedickin...@wp.pl escreveu: Hi guys. I have one table that currently has 100 000 000 records. PageSize is 16384. Index statistics for primary key are 0.00 (I've recomputed them). Over a few days inserting and updating this table slowed down significantly. When I try to update one row: UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343 update executes in 0.250 miliseconds. What is surprising for me that just few days ago everything was fine.. how could I speed thing up? What could happen over these few days? Thanks in advance. It's hard to tell without more information. But the first thing I would look out is the transaction counters. Perhaps you have some open transaction that is preventing the garbage collection process and acumulating record versions. use GSTAT -h to get the header information of your database and post it back. see you !
Re: [firebird-support] Table update performance dropped significantly within few days.
2014/1/15 fabianoas...@gmail.com Increase database page buffers and try again. It happens when you combine a large table with multiple indexes. Dropping one or two indices can also increase speed a lot in this case. I think, 100 million records are not so much records, and If you use classic or superclassic, pagebuffers cannot be high. What firebird version? How many indices has that table? Do you update indexed fields? May be the problem is Firebird performance with large indices.
Re: [firebird-support] Table update performance dropped significantly within few days.
I will take a lok at this option, thank you.
Re: [firebird-support] Table update performance dropped significantly within few days.
What firebird version? Firebird 2.5.1 Classic, on Linux. How many indices has that table? 22 :( Do you update indexed fields? Partially May be the problem is Firebird performance with large indices. But you know, few days ago performance was good. I had not changed anything since 2 months in that database.
Re: [firebird-support] Table update performance dropped significantly within few days.
We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Em 15/01/2014 17:15, brucedickin...@wp.pl escreveu: I will take a lok at this option, thank you.
Re: [firebird-support] Table update performance dropped significantly within few days.
Finally this isn't a Firebird bug is only a configuration issue. Let me know if this solve your problem because you will need one more parameter ;) Em 15/01/2014 17:36, fabianoas...@gmail.com escreveu: We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Em 15/01/2014 17:15, brucedickin...@wp.pl escreveu: I will take a lok at this option, thank you.
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 17:36, fabianoas...@gmail.com escreveu: We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Maybe the problem was solved when you detached all connections because it closes the an old open transaction :) see you !
Re: [firebird-support] Table update performance dropped significantly within few days.
No. Only when increased cache. Shutdown all conections because when they reconnect will use the new cache limit. Em 15/01/2014 17:45, Alexandre Benson Smith ibl...@thorsoftware.com.br escreveu: Em 15/1/2014 17:36, fabianoas...@gmail.com escreveu: We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Maybe the problem was solved when you detached all connections because it closes the an old open transaction :) see you !
RE: [firebird-support] Table update performance dropped significantly within few days.
No. Only when increased cache. Shutdown all conections because when they reconnect will use the new cache limit. SL But this shutdown had the effect of closing any long running transactions, which is the most common cause of performance degradation because it inhibits garbage collection -- which was Alexandre's point.
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 18:01, fabianoas...@gmail.com escreveu: No. Only when increased cache. Shutdown all conections because when they reconnect will use the new cache limit. I have my doubts... I still think it's more related to a long running transaction then to the increase on the page buffers... see you !
