RE: [firebird-support] Re: How to improve update performance with millions records?
> >Hi, I'm jimmy > > Hi Jimmy! > > > I have a problem, please help me! > > I have a table with 3,000,000 rows record, every row update 1 to 5 times in > > one day. > > The speed of update statement become slowly, about 30 > > records/second, but insert speed above 1000 records/second. > > Is my usage be bad or not? > > what should i do? > > I don't know whether your usage is bad or not. What kind of indexes do > you have and how do you update? What about transactions, do you have a > noticeable gap between oldest (active) transaction and next > transaction? I don't know whether it is still relevant (it is a very > old article), but in some cases I think rdb$db_key can be useful for > updates: http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY > > HTH, > Set > > Thanks your help! > My table have a primary key with integer, when update 200 rows then > commit trans. Th Sounds OK, but what's more important is the gap mentioned above - it doesn't help if the update commit every 200 rows if there is one or more other, concurrent transactions, that runs for a long time without committing (well, transactions that are read only AND read committed are OK, but other combinations are not). And take up Thomas offer, he will notice if there's something wrong with the output of gstat. Set
Re: RES: [firebird-support] How to improve update performance with millions records?
>>> What frequency do you backup/restore this database? >>> This database can not stop, It used to realtime collect data. >> so no restore only backup, backup it once a week. >>> What firebird version? SS CS? >>> FB2.5 SS, i use FB2.1 SS to test，same result. >> OS is win XP, change to Win 2008 Server, same result. >>> What your garbage policy? >>> Sweep interval = 200 to 2, when sweeper run, the update speed >> become more slowly than normal. > > Could be: > > * Background garbage collection (data + index) > * Automatic sweep kicking in > * Using AutoCommit/Commit Retaining without doing a hard commit from > time to time > > > - Is this a single client attachment/application doing the work or are > multiple attachments connecting to the database? > - Can you clarify what the update process exactly does? Forgot to mention, when things are slow, run: gstat -r -i Feel free to send the output to: t . steinmaurer AT upscene . com (remove/adjust the obvious things *g*) for further inspection. -- With regards, Thomas Steinmaurer (^TS^) Firebird Technology Evangelist http://www.upscene.com/ Do you care about the future of Firebird? Join the Firebird Foundation: http://www.firebirdsql.org/en/firebird-foundation/ > Btw, SuperServer can't utilize multiple cores/CPUs for a single database. > > > Regards, > Thomas > > > > >>> De: firebird-support@yahoogroups.com >>> [mailto:firebird-support@yahoogroups.com] Em nome de firebird_jimmy >>> Enviada em: segunda-feira, 11 de junho de 2012 10:01 >>> Para: firebird-support@yahoogroups.com >>> Assunto: [firebird-support] How to improve update performance with millions >>> records? >>> >>> >>> >>> >>> >>> Hi, I'm jimmy >>> I have a problem, please help me! >>> I have a table with 3,000,000 rows record, every row update 1 to 5 times in >>> one day. The speed of update statement become slowly, about 30 >>> records/second, but insert speed above 1000 records/second. >>> Is my usage be bad or not? >>> what should i do? >>> >>> thanks! >>> >>> 20120610 >>> >>> >>> >>> >>> >>> [Non-text portions of this message have been removed] >>> >> >> >> >> >> >> >> ++ >> >> 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 >> >> >> > > > > > ++ > > 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 > > > ++ 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 <*> 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: http://docs.yahoo.com/info/terms/
[firebird-support] Re: How to improve update performance with millions records?
--- In firebird-support@yahoogroups.com, Svein Erling Tysv�r wrote: > > >Hi, I'm jimmy > > Hi Jimmy! > > > I have a problem, please help me! > > I have a table with 3,000,000 rows record, every row update 1 to 5 times in > > one day. > > The speed of update statement become slowly, about 30 records/second, but > > insert speed > > above 1000 records/second. > > Is my usage be bad or not? > > what should i do? > > I don't know whether your usage is bad or not. What kind of indexes do you > have and how do you update? What about transactions, do you have a noticeable > gap between oldest (active) transaction and next transaction? I don't know > whether it is still relevant (it is a very old article), but in some cases I > think rdb$db_key can be useful for updates: > http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY > > HTH, > Set > > Thanks your help! > My table have a primary key with integer, when update 200 rows then commit > trans. th
Re: RES: [firebird-support] How to improve update performance with millions records?
> --- In firebird-support@yahoogroups.com, "Fabiano" wrote: >> >> What frequency do you backup/restore this database? >> This database can not stop, It used to realtime collect data. >so no restore only backup, backup it once a week. >> What firebird version? SS CS? >> FB2.5 SS, i use FB2.1 SS to test，same result. >OS is win XP, change to Win 2008 Server, same result. >> What your garbage policy? >> Sweep interval = 200 to 2, when sweeper run, the update speed >become more slowly than normal. Could be: * Background garbage collection (data + index) * Automatic sweep kicking in * Using AutoCommit/Commit Retaining without doing a hard commit from time to time - Is this a single client attachment/application doing the work or are multiple attachments connecting to the database? - Can you clarify what the update process exactly does? Btw, SuperServer can't utilize multiple cores/CPUs for a single database. Regards, Thomas >> De: firebird-support@yahoogroups.com >> [mailto:firebird-support@yahoogroups.com] Em nome de firebird_jimmy >> Enviada em: segunda-feira, 11 de junho de 2012 10:01 >> Para: firebird-support@yahoogroups.com >> Assunto: [firebird-support] How to improve update performance with millions >> records? >> >> >> >> >> >> Hi, I'm jimmy >> I have a problem, please help me! >> I have a table with 3,000,000 rows record, every row update 1 to 5 times in >> one day. The speed of update statement become slowly, about 30 >> records/second, but insert speed above 1000 records/second. >> Is my usage be bad or not? >> what should i do? >> >> thanks! >> >> 20120610 >> >> >> >> >> >> [Non-text portions of this message have been removed] >> > > > > > > > ++ > > 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 > > > ++ 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 <*> 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: http://docs.yahoo.com/info/terms/
RE: [firebird-support] How to improve update performance with millions records?
>Hi, I'm jimmy Hi Jimmy! > I have a problem, please help me! > I have a table with 3,000,000 rows record, every row update 1 to 5 times in > one day. > The speed of update statement become slowly, about 30 records/second, but > insert speed > above 1000 records/second. > Is my usage be bad or not? > what should i do? I don't know whether your usage is bad or not. What kind of indexes do you have and how do you update? What about transactions, do you have a noticeable gap between oldest (active) transaction and next transaction? I don't know whether it is still relevant (it is a very old article), but in some cases I think rdb$db_key can be useful for updates: http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY HTH, Set
Re: RES: [firebird-support] How to improve update performance with millions records?
--- In firebird-support@yahoogroups.com, "Fabiano" wrote: > > What frequency do you backup/restore this database? > This database can not stop, It used to realtime collect data. so no restore only backup, backup it once a week. > What firebird version? SS CS? > FB2.5 SS, i use FB2.1 SS to testï¼same result. OS is win XP, change to Win 2008 Server, same result. > What your garbage policy? > Sweep interval = 200 to 2, when sweeper run, the update speed become more slowly than normal. > > > De: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] Em nome de firebird_jimmy > Enviada em: segunda-feira, 11 de junho de 2012 10:01 > Para: firebird-support@yahoogroups.com > Assunto: [firebird-support] How to improve update performance with millions > records? > > > > > > Hi, I'm jimmy > I have a problem, please help me! > I have a table with 3,000,000 rows record, every row update 1 to 5 times in > one day. The speed of update statement become slowly, about 30 > records/second, but insert speed above 1000 records/second. > Is my usage be bad or not? > what should i do? > > thanks! > > 20120610 > > > > > > [Non-text portions of this message have been removed] >
RES: [firebird-support] How to improve update performance with millions records?
What frequency do you backup/restore this database? What firebird version? SS CS? What your garbage policy? De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Em nome de firebird_jimmy Enviada em: segunda-feira, 11 de junho de 2012 10:01 Para: firebird-support@yahoogroups.com Assunto: [firebird-support] How to improve update performance with millions records? Hi, I'm jimmy I have a problem, please help me! I have a table with 3,000,000 rows record, every row update 1 to 5 times in one day. The speed of update statement become slowly, about 30 records/second, but insert speed above 1000 records/second. Is my usage be bad or not? what should i do? thanks! 20120610 [Non-text portions of this message have been removed]
[firebird-support] How to improve update performance with millions records?
Hi, I'm jimmy I have a problem, please help me! I have a table with 3,000,000 rows record, every row update 1 to 5 times in one day. The speed of update statement become slowly, about 30 records/second, but insert speed above 1000 records/second. Is my usage be bad or not? what should i do? thanks! 20120610
[firebird-support] Re: Firebird hangs for a while blocking all DB operations periodically.
Hi Vlad, The issue occurred again, but, unfortunately, we only obtained a full dump. The full dump is 8GB (1GB zipped). Would this be useful to send to you? Please let me know so that I can put it on an ftp site for you to download. Unfortunately, we did not obtain a mini-dump. Regards, Anthony --- In firebird-support@yahoogroups.com, "hvlad" wrote: > > --- In firebird-support@yahoogroups.com, "anthonyjang2012" wrote: > When\if it occurs next time, please, take two memory dumps (one is fulldump > and second is minidump) from fb_inet_server process and send minidump to me > for analyze. > > Regards, > Vlad >
Re: [firebird-support] Re: How to shorten =================================== between header and data rows?
Afternoon ehaerim, On 09/06/12 02:16, ehaerim wrote: > thx it works again! > > but when it comes to INTEGER or SMALLINT type, it won't work again. > for example, It seems, unfortunately, that the set width command only affects character columns. I've done a few tests with other data types and it has no effect on those. You may need to cast the numeric columns to character to get the desired result: SQL> select a from norm; A 1 10 100 1000 SQL> set width a 5; SQL> select a from norm; A 1 10 100 1000 SQL> select cast (a as varchar(10)) as a from norm; A = 1 10 100 1000 Problem is, now you lose the right justification normally given to numerics. :-( Cheers, Norm. PS. I thought there was a corresponding "set numwidth a 5" command. I eventually remembered that that is in Oracle - set numwidth 5. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
Re: [firebird-support] Statement get stuck and never terminates
Hi Marc, > I'm using Firebird 2.5.1 SuperServer on windows 7 from a .Net application > that uses the .Net Ado Provider 2.5.2. The firebird server and the > application run on the same machine. > > In order to test a component from the application, I create 100 threads that > will read, update, insert and delete in the same table in the database during > 1 minute. Each thread has its own connection to the database. These > statements are run within short transactions (less than 5 statements each) > and each statement is limited to a few rows. Moreover, the transactions that > only read the data are configured in SNAPSHOT, WAIT, READ ONLY. The > transactions that may write data are configured in SNAPSHOT, WAIT, READ > WRITE, and lock the table with PROTECTED WRITE. * What's the read/write ratio in your test? * Any special reason to lock the entire table for write operations? I guess that's PROTECED WRITE supposed to do. Sounds a bit restrictive. Try to use NO WAIT instead of WAIT at transaction level to get immediately notified about lock conflicts. I would also configure the trace to get some output at transaction level and not only at statement level (prepare/start/finish). -- With regards, Thomas Steinmaurer (^TS^) Firebird Technology Evangelist http://www.upscene.com/ Do you care about the future of Firebird? Join the Firebird Foundation: http://www.firebirdsql.org/en/firebird-foundation/ > Around half of the time that I run this test, the server gets stuck executing > a statement. By that, I mean that the statement does not terminate. So I have > one thread that executes the statement within a transaction that has a lock > on the table, and 99 threads that are waiting to open a transaction with a > lock on the same table. Things remain stuck forever and the server does not > detect any deadlock. The statement on which Firebird is stuck is not always > the same. Sometimes it is a SELECT which only reads data, but it is always > within a transaction that may write data and is configured as above. > > When I query the monitoring tables from ISQL, I see that there are only two > transactions (the one executing the statement and the one from ISQL) and that > there are only two statements that are executing (the one that is stuck and > the one from ISQL to query the monitoring tables). What I noticed, is that in > MON$STATEMENTS, the MON$TRANSACTION_ID is for the statement that is > stuck. I don't know if this is relevant, but I found this strange, since I'm > executing it within a transaction. > > I activated the trace log and I'm tracing anything regarding the transactions > and the statements. In the log, I can see that the transaction has been > started and that the stuck statement has been prepared. But the logs ends > here, the statement does not continue with the EXECUTE_STATEMENT_START phase > as ususal. Moreover, the trace confirms that the stuck statement is related > to the transaction that is in the MON$TRANSACTION table. > > The performance monitor shows that Firebird consumes 0% of the CPU and that > it does not make any IO to disk or to the network. > > This behavior happens only since I updated firebird to version 2.5.1. I had > never had it before, and to be sure I reinstalled Firebird 2.1.4 and could > not reproduce this behavior. > > Would anybody have an idea of why this could be happening or what could I do > to find out? I'm running out of ideas to debug this. > > Thank you very much and have a nice week, > > Marc > > > > > > ++ > > 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 > > >
[firebird-support] Statement get stuck and never terminates
Hi everybody, I'm using Firebird 2.5.1 SuperServer on windows 7 from a .Net application that uses the .Net Ado Provider 2.5.2. The firebird server and the application run on the same machine. In order to test a component from the application, I create 100 threads that will read, update, insert and delete in the same table in the database during 1 minute. Each thread has its own connection to the database. These statements are run within short transactions (less than 5 statements each) and each statement is limited to a few rows. Moreover, the transactions that only read the data are configured in SNAPSHOT, WAIT, READ ONLY. The transactions that may write data are configured in SNAPSHOT, WAIT, READ WRITE, and lock the table with PROTECTED WRITE. Around half of the time that I run this test, the server gets stuck executing a statement. By that, I mean that the statement does not terminate. So I have one thread that executes the statement within a transaction that has a lock on the table, and 99 threads that are waiting to open a transaction with a lock on the same table. Things remain stuck forever and the server does not detect any deadlock. The statement on which Firebird is stuck is not always the same. Sometimes it is a SELECT which only reads data, but it is always within a transaction that may write data and is configured as above. When I query the monitoring tables from ISQL, I see that there are only two transactions (the one executing the statement and the one from ISQL) and that there are only two statements that are executing (the one that is stuck and the one from ISQL to query the monitoring tables). What I noticed, is that in MON$STATEMENTS, the MON$TRANSACTION_ID is for the statement that is stuck. I don't know if this is relevant, but I found this strange, since I'm executing it within a transaction. I activated the trace log and I'm tracing anything regarding the transactions and the statements. In the log, I can see that the transaction has been started and that the stuck statement has been prepared. But the logs ends here, the statement does not continue with the EXECUTE_STATEMENT_START phase as ususal. Moreover, the trace confirms that the stuck statement is related to the transaction that is in the MON$TRANSACTION table. The performance monitor shows that Firebird consumes 0% of the CPU and that it does not make any IO to disk or to the network. This behavior happens only since I updated firebird to version 2.5.1. I had never had it before, and to be sure I reinstalled Firebird 2.1.4 and could not reproduce this behavior. Would anybody have an idea of why this could be happening or what could I do to find out? I'm running out of ideas to debug this. Thank you very much and have a nice week, Marc
