Skip to content

Instantly share code, notes, and snippets.

@fzxu
Created December 11, 2015 22:15
Show Gist options
  • Save fzxu/a2cd8b24d9196552798c to your computer and use it in GitHub Desktop.
Save fzxu/a2cd8b24d9196552798c to your computer and use it in GitHub Desktop.
Keystone revocation list key perf test
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)
@fzxu
Copy link
Author

fzxu commented Dec 11, 2015

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;

@fzxu
Copy link
Author

fzxu commented Dec 11, 2015

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

@fzxu
Copy link
Author

fzxu commented Dec 11, 2015

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.

@fzxu
Copy link
Author

fzxu commented Dec 15, 2015

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment