List:General Discussion« Previous MessageNext Message »
From:曾国仕 Date:August 2 2014 2:26pm
Subject:how to improve mysql's query speed in table 'token' of keystone,when using in openstack with lots of VMs?
View as plain text  
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.
Thread
how to improve mysql's query speed in table 'token' of keystone,when using in openstack with lots of VMs?曾国仕2 Aug 2014