On 08/02/2021 18:43, Rick Hillegas wrote:
The MergeInserter is doing a merge sort because there is no usable descending index on system_log.time. The storage layer's page cache (the ConcurrentCache) is filling up because you have to fault-in the entire contents of system_log. The logic in MergeInserter.insert() does not seem to be smart enough to realize that it is close to exhausting memory and needs to spill a merge run to disk.

I would recommend throwing more memory at your JVM or adding a descending index to system_log.time.

Thanks for this.

I already have a descending index on time -- here is the full declaration of system_log:

CREATE TABLE system_log (
  time        TIMESTAMP     DEFAULT NULL,
  username    VARCHAR(15),
  name        VARCHAR(520),
  facility    VARCHAR(15)   NOT NULL,
  event       VARCHAR(31)   NOT NULL,
  sector      VARCHAR(15),
  item        VARCHAR(255),
  details     VARCHAR(32000),
  CONSTRAINT systemlog_pk   PRIMARY KEY (id)

CREATE INDEX log_index ON system_log (time DESC);

Is there a way to check if the index is being used?

Alternatively, the id column is pretty much the same ordering as descending time (insertions always use CURRENT_TIMESTAMP); I'm trying to think of a way to special-case this one to order by id instead of time, to see if that makes a difference. But since I allow the data to be sorted on any column for display, it isn't going to help much.

Access to this particular table is slow anyway, which I had thought might be due to locking issues (since anything that changes the system state gets logged, as well as certain read requests). I'm using the default isolation level and autocommit for insertions. Ideas on ways to optimise this sort of use case would be welcome!

Meanwhile I'm bumping up the memory. The table is currently about 200,000 rows, so I assume that at some point when it gets bigger this will just start happening again.

John English

Reply via email to