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.
