Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Very slow prepare for complex interrelated metadata with many triggers indirectly involved in the operation being prepared [CORE2827] #3214

Closed
firebird-automations opened this issue Jan 26, 2010 · 35 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Pedro Rodrigues (pemisaro)

Attachments:
GIA.7z
FreeUDFLib.zip
Scripts.zip

Votes: 4

In triggers and procedures conditions are not analize and all instructions bellow that condition are prepared,.

Ex. I have a trigger and I put there the following condition in a trigger after update in a table2:

if \( 1=2\) then
begin
     exception teste;
     update table1 set field1=field1\+1;
end

table1 have 1.000.000 of records and when I change a record in table2 takes a long time and exception is never show but if I put the triggers like

if \(1=2\) then
begin
     exception teste;
     /\*update table1 set field1=field1\+1;           \*/
end

it is fast.

Please help me all me databases are very slow because this and I swear you that this is true.
Analyze and fix this , Please .....................

Commits: 24777be ec5979c

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Pedro, this is the fifth time you create tickets about the same issue without providing any test case and you seem to ignore all the comments made, so all these tickets are getting closed. I'm getting tired of this. Please post a reproducible test case or use a firebird-support list for asking questions.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Critical [ 2 ] => Major [ 3 ]

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Pedro Rodrigues (pemisaro)

I send you a test with with permission few days ago. If you wan I send you another. I only don't send you my database because it have 1Gb but I will send you another test . In the test with permission , Adriano says that with permission all expressions are ignore and all code in triggers are analyzed , my question is and in other ? statements below a condition are prepared but not executed even when condition is not verified.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Pedro, you can use prepared statements and not re-prepare it every time.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Pedro, please attach a metadata only backup (gbak -m) for your big database. And provide the real queries that are a problem for you.

@firebird-automations
Copy link
Collaborator Author

Commented by: Pedro Rodrigues (pemisaro)

File GIA.7z is a backup my database , extract it and then make a restore.

FreeUDFLib.zip is a udf library used in the database. it works in windows, but if you want a send you a version for linux.

Scripts.zip contains 2 files:
script1.sql that when you run it it is slow.
script2.sql that when you run it is very fast and the unique diference bettwen them is that in the 2 the update below a IF condition is comented, but the condition is never verified has you can see.

Why this happend ? I think that in this way you can test my teory .

Best regards

Pedro Rodrigues

@firebird-automations
Copy link
Collaborator Author

Modified by: Pedro Rodrigues (pemisaro)

Attachment: GIA.7z [ 11562 ]

Attachment: FreeUDFLib.zip [ 11563 ]

Attachment: Scripts.zip [ 11564 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

First of all, thanks for the test case. I can really confirm the problem. The database has very complex interrelated metadata. And when you add usage of another table into the trigger, it in turn adds a lot of dependencies (hundreds of objects get indirectly involved), thus significantly delaying the prepare process. As already explained, this is not a bug per se. All permissions (including nested) must be verified during prepare and this requirement is defined in the SQL specification. However, the underlying implementation seems being quite sub-optimal, making the prepare much slower than expected. I already foresee some room for improvement there. Also, as already suggested, it would make a lot of sense to use prepared statements to speed the things up (maybe impossible in the SQL script, but surely possible inside your application).

So, I'm changing this ticket to represent the performance issue instead of the incorrect behavior.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5 RC1 [ 10362 ]

Version: 3.0 Initial [ 10301 ]

Version: 1.5.6 [ 10225 ]

Version: 2.5 Beta 2 [ 10300 ]

Version: 2.5 Beta 1 [ 10251 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.5 Alpha 1 [ 10224 ]

Version: 2.0.4 [ 10211 ]

Version: 2.1.0 [ 10041 ]

Version: 1.5.5 [ 10220 ]

Version: 2.0.3 [ 10200 ]

Version: 2.0.2 [ 10130 ]

Version: 2.0.1 [ 10090 ]

Version: 1.5.4 [ 10100 ]

Version: 2.0.0 [ 10091 ]

Component: Engine [ 10000 ]

summary: Triggers and Procedures don't analise Conditions. => Very slow prepare for complex interrelated metadata with many triggers indirectly involved in the operation being prepared

@firebird-automations
Copy link
Collaborator Author

Commented by: Pedro Rodrigues (pemisaro)

Hi again,

I like to know if you have a prevision date to resolve this core, because our clients are very upset about this. Has you kwow I work in a company that produces software that is ALIDATA (http://www.alidata.pt) and we are probably the company that uses more Firebird Databases in Portugal, so because of this lost of performance our concurrency is take advantage of it and denigrating Firebird Databases image.
We are very concerned with this, so if you can tell us a date to sove this core we be grateful.

Best Regards,

Pedro Rodrigues
(Alidata)

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It depends on what solution would be acceptable to you. IIRC, the difference in time between those extreme cases (trivial trigger vs the one causing dependencies) was about 100 times (0.2sec vs 20sec or something like that). I've managed to reduce that difference to 10 times (about 2sec). With some luck, these improvements could be committed into v2.1.4 which is expected to appear in a couple of months (cannot promise about v2.0.6 though). I suppose some further optimizations could also be possible, but unlikely in the v2.x versions. Also, I wouldn't expect the timings being nearly the same anyway.

@firebird-automations
Copy link
Collaborator Author

Commented by: Jorge Santos (asantos)

Hello Dmitry,

Can you tell us if this improvements could be committed into v2.1.4 ?

I hope so ! We are looking forward for this improvements !

Best regards,

Jorge Santos

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Hopefully, it will be available in v2.1.4.

@firebird-automations
Copy link
Collaborator Author

Commented by: Pedro Rodrigues (pemisaro)

Hi again,

Can you tell me when this improvement will be available ? I know thar you have mutch work to do, but this is very important to me.
I have many clients complaining the performance of the Database and I like to give them good news.
Can you give a date to this issue be resolved ?

Best Regards,

Pedro Rodrigues
(Alidata)

@firebird-automations
Copy link
Collaborator Author

Commented by: Philip Williams (unordained)

Pedro, here's what I got from Dmitry Yemanov on 15/Jul/10, when I asked about this on firebird-devel: "It's scheduled for v2.1.4 but not yet implemented. And CORE2827 shows just one side of the issue but there are others."

I've no idea if this has been committed (partially?) and is part of any snapshot build? (I wouldn't mind testing, but am not setup to build FB.)

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It wasn't committed yet, because the current top priority is finalizing v2.5. You may expect some movement regarding this ticket in August (fingers crossed).

@firebird-automations
Copy link
Collaborator Author

Commented by: Philip Williams (unordained)

Dmitry, I can provide another test case if you need it. My situation is a little different from Pedro's:

My calls to the database follow this format: an AJAX call initiates a few individual row updates here and there in the database, and then I have an on-commit trigger that calls stored procedures that potentially touch a large number of tables; those in turn have triggers that affect each other. This only happens once per transaction, so I can't make advantageous use of PreparedStatements here. The triggers do affect the speed of the individual row updates, but not horribly (from a user perspective) so optimizing those with re-use of PreparedStatements wouldn't help much (assuming I even could -- every row I update can have a variable number of fields being updated, and I always touch as few fields as I can, so that doesn't really work out.) The cost for committing, however, can be quite noticeable: somewhere around 10 seconds, even on a really nice new server, which from a user-experience standpoint isn't great.

I was able to mitigate the cost somewhat with the following approaches: (I think I mentioned this on firebird-devel, but someone might get some ideas from this for their own situation)

- The on-commit trigger only conditionally calls the stored procedures, via EXECUTE STATEMENT, if it knows that procedure will have something to do; my understanding is that this prevents each procedure's preparation from helping the others (any cached security info gets thrown away) but at least some of the time, not every SP call needs to be prepared, helping speed things up. But the more "important" the updates being made, the more SP's will need to be called, and so the prepare time goes way up. I'm logging the time spent inside the SP's, and most of them can complete in a matter of 15 or 30ms. The total time spent "doing" something is far, far less than the apparent time to execute the outermost SP. I wouldn't mind undoing the EXECUTE STATEMENT changes if this fix makes them obsolete. I check for "stuff to do" both inside and outside the SP, so it's a bit redundant at the moment.

- I tried having my triggers perform their updates via EXECUTE STATEMENT themselves; that helped the speed on individual row updates; it made on-commit speed somewhat unpredictable but usually faster; but it made nightly import jobs that touch lots of rows absolutely impossibly slow, so I had to roll back that idea. (The job couldn't even get far enough along to test on-commit speed, because of the row-update speed decrease.)

I can privately provide you with a nearly-blank database for testing, or I can test builds locally. I'd like to help however I can.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Some good news for this thread :-) I seem to have found a better solution than the one tested priorly and it can be surely included into v2.1.4. As I said before, it solves only a half of the overall problem, but I believe it's worth testing anyway (as it provides a 8x performance boost for the test case). The second half is still under investigation. If anybody here is still waiting for a fix, please let me know.

@firebird-automations
Copy link
Collaborator Author

Commented by: Philip Williams (unordained)

I would *love* to help test this, yes -- I'm excited about 2.5, but I'm even more excited about 2.1.4!

@firebird-automations
Copy link
Collaborator Author

Commented by: Jorge Santos (asantos)

Hello!
Good to know that the solution of this problem will be included in v2.1.4. Although the solution is not final, an 8x increase in performance is very good ! :) And, of course, we (Alidata) would like to help test !

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Please test the next (tomorrow's) snapshot of v2.1.4, it should already contain the first part of the fix.

@firebird-automations
Copy link
Collaborator Author

Commented by: Pedro Rodrigues (pemisaro)

Hello,

Finally good news , I still waiting for resolution of this issue, and this is very important for me.
An 8x increase in performance is very good , and when the problem be completed solved it will be excelent.
But now I'm available to test this increase in performance, it will accelerate our application 8x.

Good work.

Best regards,

Pedro Rodrigues

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The second half of the solution has just been committed as well. The final performance boost is 20x in my tests.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1.4 [ 10361 ]

Fix Version: 2.5.1 [ 10333 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Jorge Santos (asantos)

20x !? Great news! After performing our tests, we will inform the results obtained in our databases. Thank you!

@firebird-automations
Copy link
Collaborator Author

Commented by: Philip Williams (unordained)

Dmitry, could we get a brief description of the "two halves" of the problem, and maybe a bit about the solutions, so we know what to look for when testing, what the limits to the improvements are (or should be)? (And for great release notes later!) Thanks!

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Part (1) is related to triggers only, SPs are not affects. But other issues affect all kinds of objects.

Only v3.0 build is broken. v2.1 snapshot should be available tomorrow (it seems today the build system missed my yesterday's changes because the change log is automagically updated by another host nearly at the same time :-).

@firebird-automations
Copy link
Collaborator Author

Commented by: Philip Williams (unordained)

So far, so good. For some of the worst cases, nearly 10s were spent preparing. Upgrading alone got me down to 3s; then I stripped out the "if (exists(...)) then execute statement 'execute procedure ...'" hacks I had put in place to prevent preparing procedures that were only conditionally called, and that got me down to about 1s (as we discussed, hiding calls prevents your solution from making use of permissions cache.) 10x improvement there.

Then I got down to about 500ms total time (prepare + run) with some further optimizations. I estimate I've got 200ms to 300ms left based on comparing the time it takes to do a connection.commit() (jaybird) vs. the timestamps I'm recording (UDFs writing to a log file) as the on-commit trigger runs: from start to finish, the actual SP code only thinks it took 200ms. But again, my situation involves both lots of SPs and lots of triggers, plus on the on-commit thing, so I may have other problems.

For individual operations (before committing) where triggers were also slowing things down, I'm seeing a dramatic improvement as well. No errors yet, security still seems to be in place. I've deployed to my test environment for users to play with, I'll keep an eye on it there.

Thanks!

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment