High CPU usage on 1-node CockroachDB

[gomezjdaniel]

Hello,

BUG REPORT

1. Please supply the header (i.e. the first few lines) of your most recent
   log file for each node in your cluster.

I180507 07:24:56.878569 1 util/log/clog.go:1104  [config] file created at: 2018/05/07 07:24:56
I180507 07:24:56.878569 1 util/log/clog.go:1104  [config] running on machine: 964ac46d307a
I180507 07:24:56.878569 1 util/log/clog.go:1104  [config] binary: CockroachDB CCL v2.0.1 (x86_64-unknown-linux-gnu, built 2018/04/23 18:39:21, go1.10)
I180507 07:24:56.878569 1 util/log/clog.go:1104  [config] arguments: [/cockroach/cockroach start --http-port 26256 --insecure]

debug.zip
profile.zip

2. Please describe the issue you observed:

• What did you do?

I had been long time developing a project (in Go) that uses CockroachDB so that everytime that I run tests the database is dropped, tables created again and queries executed.

• What did you expect to see?

I expect them to be ran smooth and consistently in time

• What did you see instead?

For no apparent reason sometimes the same package testcases are executed in X seconds and minutes later when running them again they last twice, thrice or much more while being tests exactly same (recreating the database + schema is what apparently things gets slower),

while as top output shows CockroachDB is taking a lot CPU, both, while running tests but also when is idle, i.e. no test or process that's using cockroach is running at the moment.

[tbg]

Cc @jordanlewis for triage. Maybe cache hit for @andreimatei.

[gomezjdaniel]

Maybe related with #24762 and #20753

[nvanbenschoten]

@gomezjdaniel thank you for including the CPU profile in the initial bug report!

Yes, I think you are correct that it's related to both of those issues. As discussed, CockroachDB does not currently handle large numbers of tables (O(1k)) very well at the moment. Unfortunately, because CockroachDB uses an MVCC scheme under the hood, even tables that have been deleted will stick around for 24 hours by default before being removed completely and no longer counting towards this limit. This is necessary to allow historical queries on these now-deleted tables, but exacerbates the issue here.

One way to help get around this is to drop your garbage collection TTL, as explained here in the docs. This isn't a great solution, because it means that you won't be able to perform historical queries as far back in time, but it will allow CockroachDB to clean up these deleted tables earlier.

This is a problem that we are aware of and we plan to make progress on for our 2.1 release cycle. I'm going to close this because it is a duplicate of the other two issues listed, but I appreciate you filing so that we know that this is affecting other users.

[gomezjdaniel]

For the record,

I have entirely deleted my cockroach-data/ folder, set gc.ttlseconds to 600 in the .default replication zone and still get 200% CPU peaks while running tests :(

[nvanbenschoten]

@gomezjdaniel do you have an estimate on the number of tables your test suite is creating and deleting?

[gomezjdaniel]

Yes,

Right now 26 tables are created, then, these are deleted with DROP DATABASE XXX CASCADE

I though you could access to the databases in the debug.zip file. If so, you can see the schema in databases is_development_tests or is_store_tests

[vivekmenezes]

@gomezjdaniel this is probably the same as #24762 . While you are creating only 26 tables, the very fact that you're doing it in a loop means that there are more than 26 tables needing to be GC-ed later which release 2.0 is bad at. We're going to be publishing a new alpha release in early June with the fix; is this something you could use? or do you need the fix to be backported to the 2.0 release. Thanks!

[gomezjdaniel]

We'd appreciate if you guys backport the fix to the 2.0 release 👍

[vivekmenezes]

@gomezjdaniel the fix will be in our next 2.0 release; mid June. Thanks!