add option to display open snapshots and iterators

[mdcallag]

With InnoDB we can see open transactions via SHOW ENGINE INNODB STATUS or SHOW ENGINE INNODB TRANSACTION STATUS. I want a command to show open iterators & snapshots for RocksDB. This can debug problems from clients that leave them open for too long by accident.

[jkedgar]

I'll take a look and see what I can come up with.

[jkedgar]

Mark, for snapshots, what information would you like to see in the SHOW ENGINE ROCKSDB TRANSACTION STATUS output? How long the snapshot has been active? What thread is holding the snapshot? Other things?

[mdcallag]

1) Time when snapshot was created 2) Information that lets me map it to MySQL connection ID in case I want to kill the connection

…

On Mon, Aug 17, 2015 at 10:34 AM, Jay Edgar <notifications@github.com> wrote: Mark, for snapshots, what information would you like to see in the SHOW ENGINE ROCKSDB TRANSACTION STATUS output? How long the snapshot has been active? What thread is holding the snapshot? Other things? — Reply to this email directly or view it on GitHub <https://github.com/MySQLOnRocksDB/mysql-5.6/issues/89#issuecomment-131899725> .

-- Mark Callaghan mdcallag@gmail.com

[jkedgar]

Mark,
In our Tuesday meeting, the team talked through some of the issues with this. One thing that was of concern is that currently there is no master list of iterators and creating one will require a mutex any time we create or delete one. The guys from RocksDB were concerned with the cost of this. Someone else pointed out that all statements should start snapshots so maybe it isn't necessary to list iterators - snapshots might be enough to know if something is stuck and needs to be killed.
Therefore for now I have just implemented listing snapshots. Here is a current snapshot of what it looks like:

How does this look?

Here's some questions for you.
1) Do you see any need for the sequence number? I'm not sure it would help the user much, but there is almost no cost to it, so I'm also fine with leaving it in.
2) For the Duration column, should I change it so that if the number of seconds is big enough I translate it to minutes, hours, or even days (i.e. 2 mins 10 secs instead of 130 secs)? That is more work, but it might be a cleaner look. Then again, how often will this get called and do we really care if it is exact? It is probably more important that you can see that it is big vs. small.
3) This is just the results of SHOW ENGINE ROCKSDB TRANSACTION STATUS, but the original request was for SHOW ENGINE ROCKSDB STATUS as well. What part of this would you like to see in the broader status request? Maybe just the number of active snapshots?

[mdcallag]

I hope to get global, not per transaction, state into SHOW ENGINE ROCKSDB status. That includes compaction IO stats dumped to LOG. For other questions, I think this should match InnoDB, like for start time or seconds open.

…

On Thu, Aug 20, 2015 at 7:38 PM, Jay Edgar <notifications@github.com> wrote: Mark, In our Tuesday meeting, the team talked through some of the issues with this. One thing that was of concern is that currently there is no master list of iterators and creating one will require a mutex any time we create or delete one. The guys from RocksDB were concerned with the cost of this. Someone else pointed out that all statements should start snapshots so maybe it isn't necessary to list iterators - snapshots might be enough to know if something is stuck and needs to be killed. Therefore for now I have just implemented listing snapshots. Here is a current snapshot of what it looks like: [image: screen shot 2015-08-20 at 7 28 11 pm] <https://cloud.githubusercontent.com/assets/8951526/9400102/23bd8bde-4772-11e5-9dcf-d9ea7129306a.png> How does this look? Here's some questions for you. 1) Do you see any need for the sequence number? I'm not sure it would help the user much, but there is almost no cost to it, so I'm also fine with leaving it in. 2) For the Duration column, should I change it so that if the number of seconds is big enough I translate it to minutes, hours, or even days (i.e. 2 mins 10 secs instead of 130 secs)? That is more work, but it might be a cleaner look. Then again, how often will this get called and do we really care if it is exact? It is probably more important that you can see that it is big vs. small. 3) This is just the results of SHOW ENGINE ROCKSDB TRANSACTION STATUS, but the original request was for SHOW ENGINE ROCKSDB STATUS as well. What part of this would you like to see in the broader status request? Maybe just the number of active snapshots? — Reply to this email directly or view it on GitHub <https://github.com/MySQLOnRocksDB/mysql-5.6/issues/89#issuecomment-133253626> .

-- Mark Callaghan mdcallag@gmail.com

[jkedgar]

The initial work for this is done - snapshots are displayed. Future improvements (memory, locks, etc.) will be in a separate task.