Re: [firebird-support] how to use the x notation (ex ID=x'049094') inside parameter ?
nathanelrick schrieb am 30.03.2012 um 07:49 (-): how to use the x notation (ex ID=x'049094') inside parameter ? The hexadecimal notation for “binary” strings is for literals. http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-hexbinstrings.html insert into ... (Field1) Values (?); This looks like you're using some general purpose language (like Perl or Java). with the parameter x'049094' i receive an error Does it come with a message? probably i can write any char i want (from #0 to #255) inside parameter and i don't need the x notation ? Guess it depends on the type of Field1. Michael
RE: [firebird-support] OIT / NT
My BIG concern is that my program (Delphi XE with good old IBX components) run with the exactly same release, on 4 different clients generating diferent results. 3 of these are in normal parameters (while working, the gap between OIT/NT and OAT/NT and OST/NT increases by a few transactions / 5 sec) but when that particular client (same program, same FB client, same OS, same ISP) starts my program, the mentioned values increase in an alarming rate of 100...200 / 5 sec, so that at the end of a normal working day, the gap is arround 1.5 mil transactions. People using the program at this company complain about the slowdown in the afternoon compared with the morning. Anyway, I do a sweep each night, after a backup, so that in the morning things are reset, but I want to figure out what goes on wrong. I use MON$tables - this way I figured out who (IP) in slowing down the whole thing. Tried different options to improve the design of the program. What should normally happen, it that at the OAT/NT gap should increase at first, but then reach more or less a stable state, where you saw the gap sometimes increasing and sometimes decreasing. The difference of whether it increases by 5 or 500 per second is more an indication of how problematic it is (performance wise) for the user than an indication of whether transactions are handled correctly or not, the gap shouldn't continue to increase indefinitely (well, unless the use of the database continually increased, I've never heard of situations where you have 1 committed transactions today, 11000 tomorrow, 12000 the day after and continuing to rise for a prolonged period, but I could imagine such a situation if many transactions resulted in more transactions getting started, e.g. if your program recursively creates threads for parallel processing). The difference between your clients could just be that the problematic client just uses your database a lot more actively than your other clients. If the OAT doesn't increase, then there's some transaction that doesn't finish (commit or rollback, commit retaining is not a commit in this sense). That might be in your program or it might be a DBA just monitoring the database (read only AND read committed transactions doesn't prevent the OAT from advancing, I think all other transactions must be committed or rolled back for the OAT to advance. If the OAT does increase, but at a slower rate than the NT, then I'd say things are strange and would check to see whether there were thrown requests to Firebird at a higher rate than it could handle. E.g. if your program asks for the count of a subset of a 500 record table with no index every minute, then I would expect you to quickly run into trouble - even though it might not be a problem with only one copy of your program running. Set
Re: [firebird-support] an existing connection was forcibly closed by the remote host
homerjones1941 schrieb am 30.03.2012 um 08:12 (-): Server: Windows XP with all updates current.Database: Firebird 2.1 Client 1: Windows 7 - 64 bit Intel I7 - Wireless connection Client 2: Windows XP 32 bit AMD Dual Core - Wired connection (this is not the server). Language: Delphi 2007 - Data access via IBDAC components (devart). Application: Customer lookup and support data.The program normally has only one table open using an IBDAC Query component. The data is displayed one record at a time (not a grid). There are a good number of other tables, but they are not open when this problem occurs. Condition/Symptom: Within 15 or 20 seconds of opening the database, the program freezes for a short period before returning the error: ... an existing connection was forcibly closed by the remote host. Sounds like a Microsoft Windows TCP level problem; you probably know that much already. Troubleshooting: Connection Forcibly Closed http://msdn.microsoft.com/en-us/library/ms187005.aspx This happens while navigating from one record to the next. It also happens in the same amount of time when sitting idle.Examination of Firebird.log shows multiple coinsurances of : MYCOMPUTERNAME (Server) Thu Mar 29 18:11:06 2012 INET/inet_error: send errno = 10054 MYCOMPUTERNAME (Server) Thu Mar 29 18:19:03 2012 INET/inet_error: read errno = 10054 Assumptions:Since 10054 = Connection reset by peer, I assume the origin of both messages is occurring on the server, and they are both reporting same condition. Question:If my assumption is correct, is there something I need to do with Firebird that will prevent/minimize this? Can't think of anything, but I'm not an expert. Rather, try unsetting the SynAttackProtect registry name as suggested by the MSDN article. I plan to look at Firewall and FileZilla settings, Should I check anything else? Firewall, maybe; not sure how FileZilla would be related. More background:I have a server application (also written in Delphi using Indy) running on the same server in the same domain. It has been running flawlessly for many months. This same machine, router and switch has been my web server for over a year. The clients for that other server might just not happen to run into or trigger or detect this error condition. -- Michael Ludwig
Re: [firebird-support] SP problem execute statement
Jack Mills schrieb am 30.03.2012 um 12:30 (-): I can post the full sp if it would make thing clearer. Why not try and bisecting the problem down to the smallest query that exhibits the error? That's a proven debugging technique that always works. -- Michael Ludwig
Re: [firebird-support] how to use the x notation (ex ID=x'049094') inside parameter ?
On 30-3-2012 9:49, nathanelrick wrote: Hello, how to use the x notation (ex ID=x'049094') inside parameter ? When i do insert into ... (Field1) Values (?); with the parameter x'049094' i receive an error probably i can write any char i want (from #0 to #255) inside parameter and i don't need the x notation ? The x'...' is a literal escape. If you use parametrized queries you need to set the bytes in the parameter, however depending on your connection characterset and database character set this might cause transliteration errors if you use invalid byte combinations for the character set, unless the field is CHARACTER SET OCTETS. Mark -- Mark Rotteveel
Re: [firebird-support] OIT / NT
Hello, Tiberiu! Friday, March 30, 2012, 12:13:06 PM, you wrote: TH which) somehow foods my server. While this client is using my TH software, somehow the gap between OIT and NT increases 200 transactions each 5 seconds. I Other people noted lot of things, but I want to ask - what do you do in the application, that it generates 40 transactions per second? I mean, it's some kind of robot, inserting bulk data, or it is a human, typing data with non-human speed? Firebird does not generates transactions, so, all these transactions are coming only from your application. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] OIT / NT
Hello, Tiberiu! TH Nothing special. The same app, with the same database, TH the same amount of activity, works correctly for the same company , TH from a different location. No bulk data, nu threads ... Well, can you be more specific? For example, your application just wait for user input, and then commits data, or it is monitoring something by itself, or ...? Since you are using IBX, I hope all transactions are explicit, and there are no code that uses implicit transaction start and commit. TH I am wondering if maybe some troyan sends spam packets TH to the server, from this system, on port 3050. It can't be. Consider that only your application generates transactions. Use FBScanner, FBTraceManager, or audit in FB 2.5 - any of these - to understand what your application is doing. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] OIT / NT
Hello, Tiberiu! Friday, March 30, 2012, 12:13:06 PM, you wrote: TH which) somehow foods my server. While this client is using my TH software, somehow the gap between OIT and NT increases 200 transactions each 5 seconds. I Other people noted lot of things, but I want to ask - what do you do in the application, that it generates 40 transactions per second? I mean, it's some kind of robot, inserting bulk data, or it is a human, typing data with non-human speed? Firebird does not generates transactions, so, all these transactions are coming only from your application. O don't know If is firebird or not but i have had the same problem wiith ibx+delphi+interbase 2009. I have had jumps of 2 transactions, and of course is imposible by the kind of our app generate 2 transactions in seconds. I have observes it, but I have not been able To reproduce it . Jesus __,_. [Non-text portions of this message have been removed]
Re: [firebird-support] OIT / NT
At least I'm not alone . Tiberiu From: Jesus Garcia Sent: Saturday, March 31, 2012 4:20 PM To: firebird-support@yahoogroups.com Cc: firebird-support@yahoogroups.com Subject: Re: [firebird-support] OIT / NT O don't know If is firebird or not but i have had the same problem wiith ibx+delphi+interbase 2009. I have had jumps of 2 transactions, and of course is imposible by the kind of our app generate 2 transactions in seconds. I have observes it, but I have not been able To reproduce it . Jesus [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Firebird and sharding ? - Email found in subject
Hi Norm, Talking about optimizing using histograms - for example, an index on gender is valuable when looking for female professional rugby players, and much less so for males. I guess you could replace professional rugby players with open source developers, but ... And if it happens during a nested loop join? Re-optimize on every record? I'd expect a nested loop join to already have the rows it needs in the driving table by the time it hits the nested loop? Well, at least Firebird, InterBase, and MySQL (and NuoDB) execute nested loop joins by retrieving a row from the outer loop, using its values to look up the next inner loop and so on until all the joins are done, then sends the result to the client. If the result has to be sorted, it gets fed into the sorter right then. Maybe other systems get all the outer rows, then all the next inner ... but that would lead to a lot of latency to get the first row in all cases. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Firebird and sharding ? - Email found in subject
On 30-3-2012 14:24, Norman Dunbar wrote: Extensive use of prepared statements and variables Yes, and I'll bet they do what every Java application I've seen does, prepares the same statement hundreds of times, even though they should do it only once. I disagree, the application (or application developer) should do that (preparing statements every time it needs it). It is the driver and/or connection pool that should keep a pool of prepared statements (see section 11.6 of JDBC 4.1) for reuse. The application (and its developer) should not be bothered with keeping track of prepared statements. See http://qdosmsq.dunbar-it.co.uk/blog/2009/02/it-must-be-efficient-im-using-bind-variables/ for details. :-) Interesting read. However keeping track of connections and statements as part of your application makes it more complex and puts responsibilities in your application that don't belong there. It also duplicates what connection pools (including statement pools) could be doing for you. Mark -- Mark Rotteveel
Re[2]: [firebird-support] OIT / NT
Hello, Jesus and Tiberiu! JG O don't know If is firebird or not but i have had the same JG problem wiith ibx+delphi+interbase 2009. I have had jumps of 2 transactions, and of course JG is imposible by the kind of our app generate 2 transactions JG in seconds. I have observes it, but I have not been able To reproduce it . TH At least I'm not alone . Sorry guys, I don't believe in magic or some spy applications, that suddenly generates lot of transactions. One of the cases like that, when some application started to generate millions transaction per day, was the programmer's fault, when he forgot to construct web page content from the database in one transation. Instead, his code produced start/commit at each web page element, and there were lot of elements. So, this is just the result of bad transaction control in application. Teberiu, some you answers goes somehow not to this list, because field From does not contain anything. In your example you named lot of IBX components, nearly all of them except IBTransaction. Do you really NOT using it? TH couldn't this problem be because of a failure in some hardware component ? TH or the FIBClient is not properly installed ? NO. I say second time - nothing except you or your application is generating these transaction. Firebird (or fbclient) have no interest in data, transactions or even databases, and it does not do anything by itself. I can suggest you to read Helen's book about Firebird. And, about IBX - try to read my article with some translation from russian. You need to translate and read everything about IBTransaction component and the section about how to work with transactions in this document: http://www.ibase.ru/devinfo/ibx.htm or with translation http://translate.google.com/translate?sl=rutl=enjs=nprev=_thl=ruie=UTF-8layout=2eotf=1u=http%3A%2F%2Fwww.ibase.ru%2Fdevinfo%2Fibx.htmact=url Unfortunately google translates only part of the document. So, you will need to translate it by parts. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] OIT / NT
Friday, March 30, 2012, 12:13:06 PM, you wrote: TH which) somehow foods my server. While this client is using my TH software, somehow the gap between OIT and NT increases 200 transactions each 5 seconds. I Other people noted lot of things, but I want to ask - what do you do in the application, that it generates 40 transactions per second? I mean, it's some kind of robot, inserting bulk data, or it is a human, typing data with non-human speed? Firebird does not generates transactions, so, all these transactions are coming only from your application. O don't know If is firebird or not but i have had the same problem wiith ibx+delphi+interbase 2009. I have had jumps of 2 transactions, and of course is imposible by the kind of our app generate 2 transactions in seconds. I have observes it, but I have not been able To reproduce it . Any chance that you guys are running something in a loop with AutoCommit set to True? -- 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/
Re: Re[2]: [firebird-support] OIT / NT
Sorry guys, I don't believe in magic or some spy applications, that suddenly generates lot of transactions. One of the cases like that, when some application started to generate millions transaction per day, was the programmer's fault, when he forgot to construct web page content from the database in one transation. Instead, his code produced start/commit at each web page element, and there were lot of elements. So, this is just the result of bad transaction control in application. I also don't belive in magic, when talking about computer science, but is not a programmer error. I have to say that i have seen it after backup and restore cycle. Just starting to work our customers with the application, i saw jumps of a big number of transactions. In my case we control the transactions in all the application, and active/desactive them when we need it. I have not seen that behaviour with firebird, but with Interbase 2009 yes. I thought it was a bug, like others in IB, and i could not reproduce it. Regards, Jesus [Non-text portions of this message have been removed]
Re: [firebird-support] OIT / NT
Tiberiu, consider the comments on COMMIT RETAINING in the quotes from Helen's and Ann's messages below - maybe there's one place where you're running with the default and it's only triggered on the installation you're seeing the problem on. Michael Tiberiu Horvath schrieb am 31.03.2012 um 10:03 (+0300): My BIG concern is that my program (Delphi XE with good old IBX components) run with the exactly same release, on 4 different clients generating diferent results. FB performance - Helen Borrie 06.01.06 http://groups.yahoo.com/group/firebird-support/message/70793 Then look closely at how transactions are being committed. The IBX default for AutoCommit is to use CommitRetaining. This is handy for Delphi client apps and disastrous for the server. It causes garbage to build up steadily. You are seeing better performance on a clean database and degrading performance as garbage builds up and doesn't get cleared. 3 of these are in normal parameters (while working, the gap between OIT/NT and OAT/NT and OST/NT increases by a few transactions / 5 sec) but when that particular client (same program, same FB client, same OS, same ISP) starts my program, the mentioned values increase in an alarming rate of 100...200 / 5 sec, so that at the end of a normal working day, the gap is arround 1.5 mil transactions. People using the program at this company complain about the slowdown in the afternoon compared with the morning. From: Ann Harrison Sent: Friday, March 30, 2012 7:07 PM OK, time for my lecture on OAT and OIT. The OAT is the oldest transaction that the system considers to be active. It blocks garbage collection and induces database bloat. Transactions that commit using commit retaining do not advance the OAT. Transactions that are left open for hours - even transactions that have not changed the database - leave the OAT stuck. Once the OAT is stuck, Firebird must keep old versions of records that transaction might read if it ever wakes up and starts working again.
