Help fixing a huge amount of rerecompilation time when using ODBC.jl

Ronis_BR · November 8, 2023, 1:15pm

Hi!

We use everyday the ODBC.jl package to fetch satellite telemetries from our database and perform some automatic analysis. However, since Julia v1.9, we are experiencing a huge precompilation time. The first SQL execution takes roughly 50s.

I have opened a PR with a proposal that seems to fix the problem but I am not aware if it can also introduce others. Unfortunately, I have received not feedback yet:

github.com/JuliaDatabases/ODBC.jl

Reduce precompilation work in the function `Buffer`

JuliaDatabases:main ← ronisbr:precompilation

opened 08:43PM - 24 Sep 23 UTC

ronisbr

+18 -13

Hi! I managed to reduce the precompilation work in Julia 1.9 by just rewritin…g the function that creates a `Buffer`. Unfortunately, I have no ideia if those actions have performance consequences in other scenarios since I only have access to one MSSQL server (I cannot install docker at the moment here). For my use case, there was no measurable performance degradation when fetching data, but the time to fetch the first package was greatly reduced. Here is the timing without the modifications in Julia 1.9: ``` julia> @time using ODBC 0.129348 seconds (161.98 k allocations: 12.275 MiB, 9.80% compilation time) ... julia> function test() DBInterface.execute(conn, "SELECT * FROM SYSOBJECTS WHERE xtype = 'U'") return nothing end test (generic function with 1 method) julia> t₀ = time(); test(); t₁ = time(); println(t₁ - t₀) 49.72623920440674 ``` Hence, it takes 50s to retrieve all the tables in the MSSQL server. The second time is way faster. Here is the timing with the modifications in Julia 1.9: ``` @time using ODBC 0.118692 seconds (159.37 k allocations: 12.150 MiB, 10.20% compilation time) julia> function test() DBInterface.execute(conn, "SELECT * FROM SYSOBJECTS WHERE xtype = 'U'") return nothing end test (generic function with 1 method) julia> t₀ = time(); test(); t₁ = time(); println(t₁ - t₀) 7.495282888412476 ``` **Note**: I could reduce even further the time to fetch the first package here using precompilation statements. However, it did not provide useful gains, because the time to load the package went from 0.11s to more than 5s. Closes #371

Can anyone help me?

Thanks!

Palli · November 8, 2023, 2:25pm

Possibly you could copy these lines to ODBC.jl source file helping that module:

github.com

JuliaPlots/Plots.jl/blob/92f36ab62176066abfde58894a50d0985c090306/src/Plots.jl#L3C1-L8C4


      
          if isdefined(Base, :Experimental) && isdefined(Base.Experimental, Symbol("@optlevel"))
              @eval Base.Experimental.@optlevel 1
          end
          if isdefined(Base, :Experimental) && isdefined(Base.Experimental, Symbol("@max_methods"))
              @eval Base.Experimental.@max_methods 1
          end

Is this a problem? I mean precompilation is on be default, and is a good thing. Maybe you meant recompilation? You can turn off precompilation, I’m not sure it’s adviced, but since it only happens once, and since 1.9 precompiled to native binaries, they should be fast and the lower compilation trick, first used for Plots.jl, maybe by now outdated? See also there a PR, on how to help tune (I’ve only looked a bit at the former, not yet its successor):

github.com/JuliaPlots/Plots.jl

Migrate from SnoopPrecompile to PrecompileTools

JuliaPlots:master ← timholy:teh/precompiletools

opened 07:34AM - 25 Apr 23 UTC

timholy

+5 -5

This pull request migrates the package from [SnoopPrecompile](https://github.com…/timholy/SnoopCompile.jl/tree/master/SnoopPrecompile) to [PrecompileTools](https://github.com/JuliaLang/PrecompileTools.jl). PrecompileTools is **nearly a drop-in replacement** except that there are **changes in naming and how developers locally disable precompilation** (to make their development workflow more efficient). These changes are described in [PrecompileTool's enhanced documentation](https://julialang.github.io/PrecompileTools.jl/stable/), which also includes instructions for users on how to set up custom "Startup" packages, handling precompilation tasks that are not amenable to workloads, and tips for troubleshooting. Why the new package? It meets several goals: - The name "SnoopPrecompile" was easily confused with "SnoopCompile," a package designed for *analyzing* rather than *enacting* precompilation. - SnoopPrecompile/PrecompileTools has become (directly or indirectly) a dependency for much of the Julia ecosystem, a trend that seems likely to grow with time. It makes sense to host it in a more central location than one developer's personal account. - As Julia's own stdlibs migrate to become independently updateable (true for DelimitedFiles in Julia 1.9, with others anticipated for Julia 1.10), several of them would like to use PrecompileTools for high-quality precompilation. That requires making PrecompileTools its own "upgradable stdlib." - We wanted to change the [use of Preferences](https://github.com/timholy/SnoopCompile.jl/issues/356) to make packages more independent of one another. Since this would have been a breaking change, it seemed like a good opportunity to fix other issues, too. For more information and discussion, see this [discourse post](https://discourse.julialang.org/t/ann-snoopprecompile-precompiletools/97882).

I would also try on 1.10 (or master), about to be released. One way to avoid this is NOT to use that package, SQLAlchemy (is something I’ve seen suggested, maybe something else) with PythonCall.jl. Since I very recently suggested ODBC.jl I would also like to know it works (well). You have no problems, after some initial (on each run?) use? Instead of migrating from the package then to Julia 1.8 or 1.6 temporarily would then also “fix”…?

Ronis_BR · November 8, 2023, 3:27pm

Hi @Palli !

Is this a problem? I mean precompilation is on be default, and is a good thing. Maybe you meant recompilation? You can turn off precompilation, I’m not sure it’s adviced, but since it only happens once, and since 1.9 precompiled to native binaries, they should be fast and the lower compilation trick, first used for Plots.jl, maybe by now outdated? See also there a PR, on how to help tune (I’ve only looked a bit at the former, not yet its successor):

Sorry, that was a typo. I mean recompilation, it happens every new Julia session. Hence, every time the automatic system starts to execute the analysis, it takes 50s only compiling that function mentioned in the PR.

I tried to generate precompile statements using PrecompileTools.jl. However, in this case I get roughly 50s to execute using ODBC.

I would also try on 1.10 (or master), about to be released. One way to avoid this is NOT to use that package, SQLAlchemy (is something I’ve seen suggested, maybe something else) with PythonCall.jl.

I will try in Julia 1.10. I used PyODBC in the past through PythonCall.jl. However, I really do not want to depend on Python for this project.

Since I very recently suggested ODBC.jl I would also like to know it works (well). You have no problems, after some initial (on each run?) use? Instead of migrating from the package then to Julia 1.8 or 1.6 temporarily would then also “fix”…?

It works really well besides this huge time to perform the first query. Going back to 1.8 is not an option because our telemetry analysis system contains the entire satellite telemetry database with a lot of functions. In v1.8, we had recompilation problems which are way worse than I am reporting here in ODBC.jl.

Palli · November 8, 2023, 3:54pm

For a quick test you can also try starting with

julia -O0

or -O1, or even --compile=min.

It’s good to know, and a clue to what is happening. I think recompilation is a solvable problem in general, a consequence of inlining, and avoidable by completely disabling it with --inline=no (implied above I believe), or with Julia using it more selectively.

I would at least want this problem to go away; in general. Doesn’t happen with Python use…! Should be possible, and do you agree with my analysis of it? I want to know if I’m wrong and (then) understand the problem better.

Ronis_BR · November 8, 2023, 5:26pm

Ok!

Here are the results of the time it takes to execute DBInterface.execute for the first time:

Current (v1.9 without modifications): 35.1s
julia -O1: 34.9s
julia --compile=min: 34.6s
@optlevel 1 and @max_methods 1: 34.9s
Julia v1.10-RC1: 0.7s!!!

So indeed, something happened in Julia 1.10 that drastically reduced this recompilation time

Thanks @Palli !

Ronis_BR · November 8, 2023, 5:59pm

Just for future reference, I can confirm that Julia 1.10 leads to a huge gain in the compilation time I mentioned here in macOS (ARM) and FreeBSD (Intel).

Ronis_BR · November 8, 2023, 6:06pm

Just for curiosity, can anyone tell me what changed between 1.9 and 1.10 that can explain this huge gain?