When I used mysql as the keystone's backend in openstack, iI 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 | ********** |
| Handler_read_next | ********** |
| Handler_read_prev | 1235 |
| Handler_read_rnd | 1951101 |
| Handler_read_rnd_next | ********** |
+-----------------------+-------------+
and
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 498 |
| Qcache_free_memory | 1192512 |
| Qcache_hits | ********** |
| Qcache_inserts | 352700155 |
| Qcache_lowmem_prunes | 34145019 |
| Qcache_not_cached | ********** |
| 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 help me?
