Database opened multiple times

[robalexdev]

I was wondering if there's a preferred way to cleanly close all database connections. When running the sample code, sql.open() will get called twice, but db.close() will only get called once. My understanding is that the k6 lifecycle will run code any init code multiple times. Moving the sql.open() to setup() doesn't work.

It doesn't look like k6 supports a way to only run the init stage once. The db.close() in teardown() will only close one connection. I can't tell if some other mechanism is closing the extra connection, but unneeded connections to the database may degrade load test performance. Is there a recommended approach to this issue?

[imiric]

Hi there, and thanks for this great question. It's not something we've considered, but you're absolutely right that more than one connection would be created, and that db.close() would only close one of them.

As you've found out, setup() can't really propagate the connection to VUs, so unfortunately, I don't see a clean way around this. You could call open() and close() in the default function and skip setup() and teardown() altogether, using some __ITER conditionals to call sql.open() or create the table only once, but this complicates the script, and runs the risk of db.close() not being run at all if something fails in the script.

That said, at most only one additional connection would be created for each test run, and this is fixed and doesn't increase with more VUs you run the test with, so it wouldn't have an impact on test performance. This connection would be auto-closed when the test run ends and the process exits, so I don't think there's a functional impact.

It would be tricky to avoid this in the extension, and might be easier with support for a per-VU or per-scenario init function in k6, but I'm not sure we should consider that when the impact is so negligible.

[ralexander-phi]

Looking again, you are right that this is one per run instead of one per VU. That greatly reduces my concern. I think this can be resolved as-is. Thanks for the quick response!

[imiric]

I'm reopening this issue, as a similar question was posted on the forum.

After looking at this again, it does appear that a connection is established for each VU. I ran db.exec("SELECT pg_sleep_for('10 minutes');") in the default function, with 10 VUs/10 iterations, and running:

SELECT count(*) FROM pg_stat_activity
  WHERE pid <> pg_backend_pid() and usename = 'user' and backend_type = 'client backend';

... in psql showed one PID per VU (and an additional one for the CREATE TABLE statement).

I must've tested with a short query before, and wasn't seeing all the connections.

That said, I'm wondering if this is an issue that should be fixed. For Postgres, each connection can execute a single statement at a time, and I reckon this is the case for other RDBMSs. Even if we implement connection pooling, VUs would still need to wait for connections to be free in order to reuse them. So in high usage scenarios, it would still lead to 1 connection being used by each VU.

From a testing perspective, xk6-sql can serve as a way to load test the RDBMS. Each virtual user represents an individual load element, so it makes sense for each VU to create a separate DB connection.

Also, sharing data between VUs is notoriously tricky, which is why it's rarely done in k6. In order to implement this, we might need some changes in k6 itself, which likely wouldn't be trivial.

We'll discuss this internally, but it's likely a solution won't be implemented anytime soon, if at all.

[kieoo]

Why not add a test lifecycle function do on Once per VU* at the end, when each VUE was executed, just like vueTeardown; AND we can execute the open function in init and close function in vueTeardown; @imiric

[jamessewell]

^ this would be great because you could set up PREPARED statements there are well (needs to be done once per connection)