[firebird-support] Help, tunning database
Hi all, I have Superserver Firebird 2.5.1 installed on Win7 pro 64 bit on I7 2600K. When data grows, Firebird Server starting to slow down. I shutdown Firebird Server, starting again without changing anything and Firebird Server became smooth again. Any ideas, what is the problem with this? Firebird.conf mostly on its default values except for cpuaffinity set to 15 and DefaultDbCachePages set to 4096. Anto
Re: [firebird-support] Help, tunning database
El 15/07/2013, a las 16:31, trskopo trsk...@yahoo.com escribió: Hi all, I have Superserver Firebird 2.5.1 installed on Win7 pro 64 bit on I7 2600K. When data grows, Firebird Server starting to slow down. I shutdown Firebird Server, starting again without changing anything and Firebird Server became smooth again. Any ideas, what is the problem with this? Firebird.conf mostly on its default values except for cpuaffinity set to 15 and DefaultDbCachePages set to 4096. Anto Probably is long running transactions. I have had this issue in two scenarios. One is long running transactions that makes db slowdown and the other one is when an auto sweep starts, because a long running transaction, there is an issue (that will be solved in 2.5.3) that makes Firebird run sweep several times. I have databases of 30 gb running fine for months without issues except when there is a long running transaction. The last issue was because gbak was out space and the gbak's transaction was opened for days. Our customer called us because the slowdown and we killed the gbak process and then auto sweep started. The sweep runned more than 2 hour (i think because the bug in 2.5.2) and after that all was running fine. (When i run a manual sweep in that database takes 20 minutes and does not slowdown the engine like when auto sweep is started) Jesús [Non-text portions of this message have been removed]
[firebird-support] Re: Help, tunning database
--- In firebird-support@yahoogroups.com, Jesús GarcÃa jegaza@... wrote: El 15/07/2013, a las 16:31, trskopo trskopo@... escribió: Hi all, I have Superserver Firebird 2.5.1 installed on Win7 pro 64 bit on I7 2600K. When data grows, Firebird Server starting to slow down. I shutdown Firebird Server, starting again without changing anything and Firebird Server became smooth again. Any ideas, what is the problem with this? Firebird.conf mostly on its default values except for cpuaffinity set to 15 and DefaultDbCachePages set to 4096. Anto Probably is long running transactions. I have had this issue in two scenarios. One is long running transactions that makes db slowdown and the other one is when an auto sweep starts, because a long running transaction, there is an issue (that will be solved in 2.5.3) that makes Firebird run sweep several times. I have databases of 30 gb running fine for months without issues except when there is a long running transaction. The last issue was because gbak was out space and the gbak's transaction was opened for days. Our customer called us because the slowdown and we killed the gbak process and then auto sweep started. The sweep runned more than 2 hour (i think because the bug in 2.5.2) and after that all was running fine. (When i run a manual sweep in that database takes 20 minutes and does not slowdown the engine like when auto sweep is started) Jesús [Non-text portions of this message have been removed] Thanks for your reply. How do I check that there is a long transaction running? Anto
Re: [firebird-support] Have a lots memory, but FB could not take advantages
What are you seeing that makes you think your goal has not already been met? On Mon, Jul 15, 2013 at 12:38 PM, trskopo trsk...@yahoo.com wrote: Hi all, I have a 16GB RAM and since my database file is small size, it can completely runs on RAM. The problem is I can not set FB SuperClasic 2.5.1 on WinXp 64 to take advantages of it. Setting Page Size and Page Buffers is useless. Performance seems just the same whether Page Size set to maximum (16384) or minimum (4096). Same with DefaultDbCachePages, I even set it to zero in firebird.conf. When Page Size and Page Buffers set to minimum value, memory consumption for firebird server also minimum, without any decrease in performance. Page Size and Page Buffers setting only useful when database file set on disk. Any hints, how to take advantage these memory? Thanks and regards Anto ++ 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 [Non-text portions of this message have been removed]
RES: [firebird-support] Have a lots memory, but FB could not take advantages
Just increase Page Buffers [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Help, tunning database
El 15/07/2013, a las 18:23, trskopo trsk...@yahoo.com escribió: --- In firebird-support@yahoogroups.com, Jesús García jegaza@... wrote: El 15/07/2013, a las 16:31, trskopo trskopo@... escribió: Hi all, I have Superserver Firebird 2.5.1 installed on Win7 pro 64 bit on I7 2600K. When data grows, Firebird Server starting to slow down. I shutdown Firebird Server, starting again without changing anything and Firebird Server became smooth again. Any ideas, what is the problem with this? Firebird.conf mostly on its default values except for cpuaffinity set to 15 and DefaultDbCachePages set to 4096. Anto Probably is long running transactions. I have had this issue in two scenarios. One is long running transactions that makes db slowdown and the other one is when an auto sweep starts, because a long running transaction, there is an issue (that will be solved in 2.5.3) that makes Firebird run sweep several times. I have databases of 30 gb running fine for months without issues except when there is a long running transaction. The last issue was because gbak was out space and the gbak's transaction was opened for days. Our customer called us because the slowdown and we killed the gbak process and then auto sweep started. The sweep runned more than 2 hour (i think because the bug in 2.5.2) and after that all was running fine. (When i run a manual sweep in that database takes 20 minutes and does not slowdown the engine like when auto sweep is started) Jesús [Non-text portions of this message have been removed] Thanks for your reply. How do I check that there is a long transaction running? Anto Select * from mon$transactions for example [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Have a lots memory, but FB could not take advantages
On Mon, Jul 15, 2013 at 12:54 PM, trskopo trsk...@yahoo.com wrote: Simple, i still have a lot of free RAM. As Sean pointed out, that suggests you may not have achieved your goal but does not definitely indicate that you haven't. Better indication would be file I/O that you would not expect. [Non-text portions of this message have been removed]
[firebird-support] Making a Value Matrix - Delphi Array or Create Query
Delphi XE, FB 2.5 I need to make an array of values from database columns (much like a crosstab) that can be attached to a data source. In my particular case, one database column holds the names of samples sent for analysis, one holds the names of the compounds, and a third hold the reported concentrations. My array would consist of the compound names along the top, names of samples on the left side, and results within the field of the array. Is it advisable to create a new FB table from a For Loop within Delphi and filling the table in the similar fashion? Thanks
[firebird-support] Re: Making a Value Matrix - Delphi Array or Create Query
I will use them in a GIS application or for charting to plot one compound against another, or to show pie charts in the GIS app --- In firebird-support@yahoogroups.com, Leyne, Sean Sean@... wrote: Delphi XE, FB 2.5 I need to make an array of values from database columns (much like a crosstab) that can be attached to a data source. In my particular case, one database column holds the names of samples sent for analysis, one holds the names of the compounds, and a third hold the reported concentrations. My array would consist of the compound names along the top, names of samples on the left side, and results within the field of the array. Is it advisable to create a new FB table from a For Loop within Delphi and filling the table in the similar fashion? Are you looking to have use the database to perform analysis on the sample results or just as a datastore for the array values? Sean
[firebird-support] Re: Have a lots memory, but FB could not take advantages
Thanks for all replays. I did a small test, here are the settings : - FB Super Classic 2.5.1 64 bit - Win7 pro 64 bit - Database size about 640mb - Select something like this : select a.field1, b.field2, sum(c.field3) from table1 a, table2 b, table3 c where group by - temp directory set to disk - temp cache set to 64mb Here are the options : 1) Database put on a disk. Page Size 16KB, DefaultDbCache 8192. Execution time is about 1 min. 2) Database put on a RAM. Page Size 4KB, DefaultDbCache 0. (Although DefaultDbCache set to 0, flamerobin indicate the value of Page Buffer = 50) Execution time is about 35 second. During each test, I reboot the comp. Option 1 consume 128 MB memory per connection, while option 2 consume 200KB per connection and 640MB for database file. So, I guess, by having database on RAM (if possible), is the top tweaking possible regarding file I/O.
[firebird-support] Re: Have a lots memory, but FB could not take advantages
Thanks for all replays. I did a small test, here are the settings : - FB Super Classic 2.5.1 64 bit - Win7 pro 64 bit - Database size about 640mb - Select something like this : select a.field1, b.field2, sum(c.field3) from table1 a, table2 b, table3 c where group by - temp directory set to disk - temp cache set to 64mb Here are the options : 1) Database put on a disk. Page Size 16KB, DefaultDbCache 8192. Execution time is about 1 min. 2) Database put on a RAM. Page Size 4KB, DefaultDbCache 0. (Although DefaultDbCache set to 0, flamerobin indicate the value of Page Buffer = 50) Execution time is about 35 second. 3) Database put on a disk. Page Size 16KB, DefaultDbCache 0. Execution time is more than 2 min. During each test, I reboot the comp. Option 1 consumed 128 MB memory per connection, while option 2 consumed 200KB per connection and 640MB for database file. So, I guess, by having database on RAM (if possible), is the top tweaking possible regarding file I/O. Anto
[firebird-support] singleton table with many fields
Hi all, I have a table that always contains 1 record and have about 50 fields with various type. It's function is to store system parameters and is accessed very frequent from application and also from database triggers and stored procedures. Is it wise to utilize a singleton table like this, or is it better to implement a table with many records storing parameter name, type, and values ? Thank you very much. Regards, Tjioe
Re: [firebird-support] singleton table with many fields
Em 16/7/2013 00:03, thp_pkmi escreveu: Hi all, I have a table that always contains 1 record and have about 50 fields with various type. It's function is to store system parameters and is accessed very frequent from application and also from database triggers and stored procedures. Is it wise to utilize a singleton table like this, or is it better to implement a table with many records storing parameter name, type, and values ? Thank you very much. Regards, Tjioe I doubt it will make any diference... I think you will have a dozen or so records.. that will fit in a single page... Since every operation will read/write a page... see you !
Re: [firebird-support] Re: Making a Value Matrix - Delphi Array or Create Query
Em 15/7/2013 21:55, Craig Cox escreveu: I will use them in a GIS application or for charting to plot one compound against another, or to show pie charts in the GIS app I prefer to create the cross tab on the client side... I think I have much more control over it... see you !
[firebird-support] Re: singleton table with many fields
Thank you Alexandre for your reply. Furthermore, is it any difference accessing that singleton table by: SELECT .. INTO .. FROM SINGLETON_TABLE or SELECT .. INTO .. FROM SINGLETON_TABLE ROWS 1 Regards, Tjioe --- In firebird-support@yahoogroups.com, Alexandre Benson Smith iblist@... wrote: Em 16/7/2013 00:03, thp_pkmi escreveu: Hi all, I have a table that always contains 1 record and have about 50 fields with various type. It's function is to store system parameters and is accessed very frequent from application and also from database triggers and stored procedures. Is it wise to utilize a singleton table like this, or is it better to implement a table with many records storing parameter name, type, and values ? Thank you very much. Regards, Tjioe I doubt it will make any diference... I think you will have a dozen or so records.. that will fit in a single page... Since every operation will read/write a page... see you !
Re: [firebird-support] Re: singleton table with many fields
Em 16/7/2013 01:37, thp_pkmi escreveu: Thank you Alexandre for your reply. Furthermore, is it any difference accessing that singleton table by: SELECT .. INTO .. FROM SINGLETON_TABLE or SELECT .. INTO .. FROM SINGLETON_TABLE ROWS 1 Regards, Tjioe There is no way a query in a table with a single row to be slow :) Put it in a loop for 1000 times and look for the diference... see you !
