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 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