when i used mysql as the keystone's backend in openstack ,i found that the 'token' table saved 29 millions record (using myisam as engine,the size of token.MYD is 100G) and have 4 new token save per second. That result to the slow query of a token .since of inserting new token frequently,how could i set the configure to speed up the query operation.
the token's struct is id,expires,extra,valid,user_id with index {expires,valid} and the select sql is "select id,expires,extra,valid,user_id from token where valid=1 and expires >='XXXX-XX-XX XX:XX:XX' and user_id ='XXXXXXXXXXXXXXXXXXX';"with often return 2 results. Here is some db status data in a real openstack environment with 381 active VMs: +-----------------------+-------------+ | Variable_name | Value | +-----------------------+-------------+ | Handler_read_first | 259573419 | | Handler_read_key | 1344821219 | | Handler_read_next | 3908969530 | | Handler_read_prev | 1235 | | Handler_read_rnd | 1951101 | | Handler_read_rnd_next | 48777237518 | +-----------------------+-------------+ and +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | Qcache_free_blocks | 498 | | Qcache_free_memory | 1192512 | | Qcache_hits | 1122242834 | | Qcache_inserts | 352700155 | | Qcache_lowmem_prunes | 34145019 | | Qcache_not_cached | 1529123943 | | Qcache_queries_in_cache | 1681 | | Qcache_total_blocks | 4949 | +-------------------------+------------+ it seems that the 'insert' operation of saving new token affects the query buffer,and result of a low-level of query-hit's rate. please give me some help,thanks.