Keystone revocation list key perf test

perf_test.py

import time
import MySQLdb
import sys
import importlib
import datetime

def get_revocation_list(index_name, times=1):
    db = MySQLdb.connect(host=dbc.IP, port=dbc.PORT, user=dbc.DB_USER,
                         passwd=dbc.DB_PASSWORD, db=dbc.DB_NAME)
    db.autocommit(True)

    cs = db.cursor()
    start = datetime.datetime.now()
    try:
        for _ in range(times):
            cs.execute("""SELECT token.id AS token_id, token.expires AS 
                    token_expires FROM token use index (`%s`) WHERE token.expires > '%s' AND 
                    token.valid = 0""" % (index_name, datetime.datetime.now() ))
            rets = set((r[0], r[1]) for r in cs.fetchall())
    finally:
        cs.close()
        db.close()
    print("use index: %s spent: %s" % (index_name, (datetime.datetime.now() - start)))

if __name__ == '__main__':
    db_config = sys.argv[1]
    dbc = importlib.import_module(db_config)
    get_revocation_list('ix_token_expires_valid', 50)
    get_revocation_list('ix_token_valid_expires', 50)

The token table structure:

CREATE TABLE token (
id varchar(64) NOT NULL,
expires datetime DEFAULT NULL,
extra text,
valid tinyint(1) NOT NULL,
trust_id varchar(64) DEFAULT NULL,
user_id varchar(64) DEFAULT NULL,
PRIMARY KEY (id),
KEY ix_token_expires (expires),
KEY ix_token_expires_valid (expires,valid),
KEY ix_token_user_id (user_id),
KEY ix_token_trust_id (trust_id),
KEY ix_token_valid_expires (valid,expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We are using PKIZ token type, the result is:

use index: ix_token_expires_valid spent: 0:00:07.826846
use index: ix_token_valid_expires spent: 0:00:00.014565

This test was done on my macbook pro, spec:
2.6 GHz Intel Core i7
16 GB 1600 MHz DDR3
500G SSD

Mysql: 5.6.27
There are 1 million PKIZ token in the token table, a few of them is invalid.

As you can see, using index key (valid, expires) is much more faster than (expires, valid).

I also did the same thing in our production mysql(running on BM, Galera cluster), using (valid, expires) key is 9x faster at least.

Redo the same test using UUID token,

Table structure is the same, contains both keys:

CREATE TABLE token (
id varchar(64) NOT NULL,
expires datetime DEFAULT NULL,
extra text,
valid tinyint(1) NOT NULL,
trust_id varchar(64) DEFAULT NULL,
user_id varchar(64) DEFAULT NULL,
PRIMARY KEY (id),
KEY ix_token_expires (expires),
KEY ix_token_expires_valid (expires,valid),
KEY ix_token_user_id (user_id),
KEY ix_token_trust_id (trust_id),
KEY ix_token_valid_expires (valid,expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Token table has 600k tokens.

The result is the same:
use index: ix_token_expires_valid spent: 0:00:06.024553
use index: ix_token_valid_expires spent: 0:00:00.028125