Loading Scenarios from Platform gets very slow over time

[glatterf42]

Thank you @khaeru. Modifying the config.json file worked very well for me at first. However, when running my code, over time it eventually gets slower and slower every time I re-initialize the scenario as seen in the following code block.

import message_ix
import ixmp as ix
from ixmp import Scenario
from message_ix import make_df
#from functions.format_demand import format_demand
import pandas as pd
from pathlib import Path
from message_ix.reporting import Reporter
from update_units import unit_correction
import numpy as np


# Stand-alone model
modelName = 'MESSAGEix_CA_Hydrogen'      # A MESSAGE model name
scenarioName = 'Carbon Price'       # A MESSAGE model scenario name
ssp = 'SSP1'

mp = ix.Platform(name="p1")

# Creating a new MESSAGE stand-alone model
msg = message_ix.Scenario(mp, modelName, scenarioName,
                            version='new', annotation='')

I've gotten to the point where it takes 20 minutes to run this simple segment of code (this took 30 seconds originally) and I have "jvmargs" set to -Xmx12G in my config.json file. If anyone has a solution to this issue that would be much appreciated. I have attempted clearing memory and temporary files etc with no success. Not sure what else I could try.

config.json

Originally posted by @brendandanaher in #694 (reply in thread)

[glatterf42]

Hi @brendandanaher, let's make this a new discussion since it's a new topic.
The issue you refer to most likely arises from your local database being crowded with Scenarios. This happens for local DBs occasionally, depending on how many Scenarios you store in them (but relatively independent of how large your individual Scenarios are).
If you are a IIASA collaborator, maybe we can figure something out that you can use our internal DBs, which don't suffer from this issue. If you want to keep using your own DBs, the common solution is to set up a new local DB.
So in your config.json (thanks for attaching that!), you would add a new entry, pretty much identical to p1 and p2, but with a new name (e.g. "p3"). Then, you can use that via mp = ix.Platform(name="p3"). But you could also check if p2 is still usable :)
And if you don't want to specify name=p3 all the time, you can change the "default": "p2", line in your config.json to the new platform name as the default.
Finally, you could consider deleting some Scenarios from your p1 DB. If not directly, then you could migrate those you want to keep to a new DB (maybe called archive or so) and delete the whole p1.

[awais307]

Thanks, @brendandanaher, for raising this issue. I wanted to mention this too. While using local databases and even modifying config.json, making new platforms (since it gets overloaded with local scenarios) over time, the scenario loading and ixmp sometimes take super long.
I wanted to ask if there is a way to clean up cache or memory. One time, I ended up deleting all my local databases to make things faster, but for me, a country-level energy scenario with 13 nodes took me 30 minutes to convert to an Excel file. Normally, reading from the Excel file takes 4-5 minutes. Perhaps it's just reading and writing to Excel that's slower?

[khaeru]

I do most of my work with IIASA's shared/central Oracle database server, so I don't often work with ixmp.Platform backed by local/HyperSQL databases.

However, when I do, I usually do this:

• Use multiple ixmp Platforms. (To be clear, one Platform usually corresponds to one "database".) For instance:
  • One or more "source" platform that I treat as read-only.
  • One or more "working" platform, where I do development work and model runs.
  • One or more "archive" platform, where store particular model solutions that I want to retain.
• For the "source" platform(s):
  • I store only Scenarios cloned from remote databases, or loaded from particular Excel files sent to me.
  • I don't edit these on this platform. Thus it has a small number of Scenarios and versions.
  • I record precisely where I got these scenarios from: which other platform/model/scenario/version, or which specific file.
• For the "working" platform(s):
  • I treat this as throw-away, i.e. I am ready at any moment to delete this and start over. (This is also a way of ensuring that my code is always able to reproduce certain scenarios based on the contents of the "source" platform. Reproducibility is essential for research.)
  • I can use Scenario.clone() to clone scenarios from a "source" platform to my "working" platform, and then modify them in the latter location.
  • I can also use the command-line tool to entirely overwrite my "working" platform with a copy of all the data in a "source" platform: ixmp platform copy my-source-platform my-working-platform
• For the "archive" platform(s):
  • I only copy a few, select Scenarios/versions from the "working" platform to this one.
  • These are versions that I wish to retain for later reference.
  • However, because of the point above about reproducibility, I also know I can repeat the same steps (populate the "source" platform, clone/copy to "working", run the same code) to get the same scenario again.
  • I keep backups in case I make any mistake.

The slow-down of the ixmp.JDBCBackend with large, local/HyperSQL databases is a known issue, iiasa/ixmp#433. See iiasa/ixmp#433 (comment) that I wrote ~2 years ago; this is still the current status. The above tactics/practices are just one (not the only) way to make use of the existing code that works around these limitations, and while we wait for an updated, Python-based backend that has better performance in these situations.

[glatterf42]

I've heard similar complaints from numerous colleagues by now. Enough to make me wonder: would you be willing to present this workflow in a weekly meeting -- or even as a small hands-on session at the Community Meeting? External colleagues might struggle with that even more. Or should we copy this comment almost one-for-one to the docs as a guide on how to use Platforms efficiently?

[khaeru]

I worry that time spent developing, formalizing, and training colleagues in such work-arounds for a known/"wontfix" issue competes with time spent to develop a new backend that is free of the issue.

Recall we also talked about adopting similar practices (using multiple Platforms/databases) for working with central databases, whether Oracle or Postgres. The benefits there were also about performance, but in a different way: total disk storage rather than time to initialize/connect. But that's also pending work to create those databases and figure out how to operationalize such workflows.

I think this comment can be linked in the meanwhile, and when the latter are in effect we can document them with message-ix-models and add a note, “You can also follow similar practices and get the same benefits in working with local databases.”