#+TITLE: Literate Database Example #+AUTHOR: Howard Abrams #+EMAIL: howard.abrams@gmail.com #+DATE: 2015 Apr 13 #+TAGS: technical database #+OPTIONS: ^:nil ** MySQL Analysis :PROPERTIES: :engine: mysql :dbhost: localhost :database: keystone :dbuser: keystone :dbpassword: d97d880017c8b965eea1 :cmdline: --protocol=tcp :exports: both :END: Not knowing anything about the token properties in the Keystone database structure, I jumped into the database to expose a bit of the schema. What follows is a summary of my exploration as well as some recommendations we can use to ascertain its health. First, here are the tables associated with the =keystone= database: #+BEGIN_SRC sql SHOW tables; #+END_SRC #+RESULTS: | Tables_in_keystone | |------------------------| | credential | | domain | | endpoint | | group | | group_domain_metadata | | group_project_metadata | | migrate_version | | policy | | project | | role | | service | | token | | trust | | trust_role | | user | | user_domain_metadata | | user_group_membership | | user_project_metadata | The =user= table has the following schema: #+BEGIN_SRC sql SHOW columns FROM user; #+END_SRC #+RESULTS: | Field | Type | Null | Key | Default | Extra | |--------------------+--------------+------+-----+---------+-------| | id | varchar(64) | NO | PRI | NULL | | | name | varchar(255) | NO | | NULL | | | extra | text | YES | | NULL | | | password | varchar(128) | YES | | NULL | | | enabled | tinyint(1) | YES | | NULL | | | domain_id | varchar(64) | NO | MUL | NULL | | | default_project_id | varchar(64) | YES | | NULL | | Each person that connects to the Keystone service is given an entry in the =user= database. How many have logged in? #+BEGIN_SRC sql SELECT count(*) AS users FROM user; #+END_SRC #+RESULTS: | users | |-------| | 40 | Keep in mind that some of these entries are for the other OpenStack components and our CI accounts: #+BEGIN_SRC sql SELECT COUNT(*) as non_users FROM user WHERE extra LIKE '{"email": ""}'; #+END_SRC #+RESULTS: | non_users | |-----------| | 21 | An /active user/ is given a token during the authentication process, and that record is stored in the =token= table. Here is its schema: #+BEGIN_SRC sql SHOW columns FROM token; #+END_SRC #+RESULTS: | Field | Type | Null | Key | Default | Extra | |----------+-------------+------+-----+---------+--------| | id | varchar(64) | NO | PRI | NULL | | | expires | datetime | YES | MUL | NULL | | | extra | mediumtext | YES | | NULL | | | valid | tinyint(1) | NO | | NULL | 1 or 0 | | trust_id | varchar(64) | YES | | NULL | | | user_id | varchar(64) | YES | | NULL | | How many token entries are there at this time? #+BEGIN_SRC sql SELECT count(*) AS tokens FROM token; #+END_SRC #+RESULTS: | tokens | |--------| | 570 | Is the =expires= timestamp something that can be in the past? #+BEGIN_SRC sql SELECT count(*) AS expired FROM token WHERE expires < NOW(); #+END_SRC #+RESULTS: | expired | |---------| | 217 | Clearly that is a lot of expired tokens. How old is the oldest expired token? #+BEGIN_SRC sql SELECT expires, (UNIX_TIMESTAMP(expires) - UNIX_TIMESTAMP(NOW()))/60 AS minutes_ago, (UNIX_TIMESTAMP(expires) - UNIX_TIMESTAMP(NOW()))/60/60 AS hours_ago FROM token ORDER BY expires DESC LIMIT 1 #+END_SRC #+RESULTS: | expires | minutes_ago | hours_ago | |---------------------+-------------+-------------| | 2015-04-10 20:14:49 | 1435.3667 | 23.92277778 | That is /almost/ 24 hours ago. Is that our policy? Actually, it is indeed a configurable policy. The default value is set to 24 hours, in case long running stories cache that token. If we think data storage is still an issue, we could lower that policy in order to force the excise of those expire tokens. If a system does cache it, the token can be renewed, or the script could be easily reset. What is the token /duration/? According to the database entries, this would be the value with the greatest =expires= entry: #+BEGIN_SRC sql SELECT expires, (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(expires))/60/60 AS hours FROM token ORDER BY expires ASC LIMIT 1 #+END_SRC #+RESULTS: | expires | hours | |---------------------+-------------| | 2015-04-07 06:23:21 | 15.44972222 | Since we can easily /re-new/ a token, why is that value so large? How many tokens are expiring in the next ten minutes? Are those tokens from normal users or from the other cluster components? #+BEGIN_SRC sql SELECT user.name AS soon_to_expire FROM token, user WHERE user.id = token.user_id AND expires > NOW() AND expires < DATE_ADD(NOW(), INTERVAL 10 MINUTE); #+END_SRC #+RESULTS: | soon_to_expire | |-------------------| | johnny.thunder | | johnny.thunder | | casey.jones | Clearly, this will depend on the activity of our system. We have someone here with two tokens. How many tokens are assigned to active user account? #+BEGIN_SRC sql SELECT user.name, count(token.id) AS tokens FROM token LEFT JOIN user ON user.id = token.user_id GROUP BY token.user_id ORDER BY tokens DESC #+END_SRC #+RESULTS: | name | tokens | |----------------+--------| | neutron | 498 | | jazz.ci | 214 | | johnny.thunder | 113 | | wpc-buildadm | 106 | | paul.mccartney | 89 | | jazz.ci.gold | 68 | | howard.abrams | 49 | | megane.smith | 32 | | mini.song | 29 | | ravi.shankar | 18 | | guido-ci | 12 | | admin | 6 | | adrian.smith | 4 | | glance | 2 | | casey.jones | 2 | | nova | 1 | I guess I can see why the =neutron= account has the most tokens, but normal user accounts seem to have quite a bit. Why? Are they expired? #+BEGIN_SRC sql SELECT user.name, count(token.id) AS expired_tokens FROM token LEFT JOIN user ON user.id = token.user_id WHERE token.expires < NOW() GROUP BY token.user_id ORDER BY expired_tokens DESC #+END_SRC #+RESULTS: | name | expired_tokens | |----------------+----------------| | neutron | 247 | | jazz.ci | 102 | | johnny.thunder | 99 | | paul.mccartney | 84 | | wpc-buildadm | 53 | | howard.abrams | 49 | | jazz.ci.gold | 39 | | megane.smith | 32 | | mini.song | 10 | | ravi.shankar | 8 | | admin | 6 | | adam.smith | 4 | While it appears that most of the tokens are indeed expired, the non-expired tokens seem to be more than needed. But I guess that is just the OpenStack way. The =token= table has a =valid= field that is either =1= or =0=. How many tokens are invalid (equal to zero instead of one)? The OpenStack documentation describes this field as /revoked/: #+BEGIN_SRC sql SELECT count(*) AS invalid FROM token WHERE valid = 0; #+END_SRC #+RESULTS: | invalid | |---------| | 6 | Which user has these invalid tokens. Are invalid tokens also expired? No, since the =expires= field can be either greater or less than the current time: #+BEGIN_SRC sql SELECT name, expires, (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(expires))/60 AS minutes_when FROM token, user WHERE user.id = token.user_id AND valid = 0; #+END_SRC #+RESULTS: | name | expires | minutes_when | |---------------+---------------------+--------------| | howard.abrams | 2015-04-09 17:48:44 | 166.2000 | | howard.abrams | 2015-04-09 17:48:52 | 166.0667 | | ravi.shankar | 2015-04-09 16:03:14 | 271.7000 | | ravi.shankar | 2015-04-09 20:40:05 | -5.1500 | | howard.abrams | 2015-04-09 17:48:42 | 166.2333 | | ravi.shankar | 2015-04-09 17:40:52 | 174.0667 | The revoked tokens can come about due to actually calling the Delete API on the token (even though I don’t remember doing anything like that with my account): #+BEGIN_EXAMPLE DELETE: /token/{token_id} #+END_EXAMPLE This API sets the valid field of token to false in the database (1). Then the token is now called a /revoked token/. The code for accessing and managing the revoked tokens are in =keystoneclient.middleware.auth_token=, so we might want to read the code to see the details. *Note:* Token revocation is often a side effect of some other operation. For example, changing a password revokes all tokens issued prior to the password change. Removing a role assignment from a user revokes all tokens that have that role assignment. Does someone with invalid or revoked tokens also have valid tokens? #+BEGIN_SRC sql SELECT count(*) as howards_tokens FROM token, user WHERE token.user_id = user.id AND valid = 1 AND user.name = "howard.abrams" #+END_SRC #+RESULTS: | howards_tokens | |----------------| | 46 | I guess so....