Ask Your Question
0

Keystone resulting in slow mysql queries

asked 2013-05-16 14:23:38 -0600

adam500 gravatar image

I used this install guide: https://github.com/mseknibilel/OpenStack-Grizzly-Install-Guide/blob/OVS_MultiNode/OpenStack_Grizzly_Install_Guide.rst (https://github.com/mseknibilel/OpenSt...)

1 controller/network node and 4 compute nodes running on Ubuntu 13.04 server 64bit. I have keystone running on the controller with a local mysql database (15K SAS drives).

As you can see below mysql is constantly running at 100% cpu utilization. Any api call that needs keystone authentication results in this. 10655 mysql 20 0 3970m 554m 7740 S 101.7 3.5 75:53.22 /usr/sbin/mysqld

API calls take 5-8 seconds to return a value. A simple nova list takes 5 seconds. I have checked the keystone logs and there is nothing getting logged. I enabled slow query logging in the my.cnf and noticed the following:

================================================================================================ SET timestamp=1368712775; SELECT token.id AS token_id, token.expires AS token_expires, token.extra AS token_extra, token.valid AS token_valid, token.user_id AS token_user_id, token.trust_id AS token_trust_id FROM token WHERE token.expires > '2013-05-16 13:59:32' AND token.valid = 0;

Time: 130516 9:59:39

User@Host: keystone[keystone] @ [10.70.0.1]

Query_time: 3.804186 Lock_time: 0.000048 Rows_sent: 3 Rows_examined: 117374

SET timestamp=1368712779; SELECT token.id AS token_id, token.expires AS token_expires, token.extra AS token_extra, token.valid AS token_valid, token.user_id AS token_user_id, token.trust_id AS token_trust_id FROM token WHERE token.expires > '2013-05-16 13:59:36' AND token.valid = 0;

Time: 130516 9:59:43

User@Host: keystone[keystone] @ [10.70.0.1]

Query_time: 3.800959 Lock_time: 0.000047 Rows_sent: 3 Rows_examined: 117375

SET timestamp=1368712783; SELECT token.id AS token_id, token.expires AS token_expires, token.extra AS token_extra, token.valid AS token_valid, token.user_id AS token_user_id, token.trust_id AS token_trust_id FROM token WHERE token.expires > '2013-05-16 13:59:39' AND token.valid = 0;

Time: 130516 9:59:47

User@Host: keystone[keystone] @ [10.70.0.1]

Query_time: 3.810399 Lock_time: 0.000048 Rows_sent: 3 Rows_examined: 117377

SET timestamp=1368712787; SELECT token.id AS token_id, token.expires AS token_expires, token.extra AS token_extra, token.valid AS token_valid, token.user_id AS token_user_id, token.trust_id AS token_trust_id FROM token WHERE token.expires > '2013-05-16 13:59:43' AND token.valid = 0;

Time: 130516 9:59:51

User@Host: keystone[keystone] @ [10.70.0.1]

Query_time: 3.871686 Lock_time: 0.000042 Rows_sent: 3 Rows_examined: 117377

SET timestamp=1368712791; SELECT token.id AS token_id, token.expires AS token_expires, token.extra AS token_extra, token.valid AS token_valid, token.user_id AS token_user_id, token.trust_id AS token_trust_id FROM token WHERE token.expires > '2013-05-16 13:59:47' AND token.valid = 0;

Time: 130516 9:59:55

User@Host: keystone[keystone] @ [10.70.0.1]

Query_time: 3.928294 Lock_time: 0.000050 Rows_sent: 3 Rows_examined: 117379

SET timestamp=1368712795; SELECT token.id AS token_id, token.expires AS token_expires, token.extra AS token_extra, token.valid AS token_valid, token.user_id AS token_user_id, token.trust_id AS token_trust_id FROM token

WHERE token.expires > '2013-05-16 13:59:51' AND token.valid = 0;

As you can see the same query is ... (more)

edit retag flag offensive close merge delete

5 answers

Sort by ยป oldest newest most voted
0

answered 2013-05-28 09:26:44 -0600

Hi Adam, me too facing the same problem and i am using the same tutorial to install grizzly, any news ???

edit flag offensive delete link more
0

answered 2013-05-30 22:36:07 -0600

shrinand gravatar image

Same problem. Trimming the "token" table works great.

edit flag offensive delete link more
0

answered 2013-05-28 13:47:22 -0600

adam500 gravatar image

I have a few fixes. Now that the tokens are encrypted in the database it is taking much longer to do the queries. Here is the bug report I filed after not getting any response here: https://bugs.launchpad.net/keystone/+bug/1182481 (https://bugs.launchpad.net/keystone/+...)

I have tuned my /etc/mysql/my.cnf with the following:

key_buffer = 2048M max_allowed_packet = 2048M thread_stack = 512M thread_cache_size = 1024 query_cache_limit = 2048M query_cache_size = 128M innodb_buffer_pool_size = 2048M

restart mysql after making the changes

Also it was suggest to modify the date

/etc/init.d/ntp stop date -s "date" /etc/init.d/ntp start

This has helped greatly.

I also found that flushing the keystone tokens and only keeping ones that are less than a day old helped out.

Here is the mysql command: USE keystone ; DELETE FROM token WHERE NOT DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= expires;'

I had a cron doing this but I have stopped the cron and it is looking like it is running better now.

I am not sure that this is a real fix but I want to keep monitoring my environment and I will keep you all updated.

Hope this helps. Adam

edit flag offensive delete link more
0

answered 2013-05-29 09:23:54 -0600

fecn gravatar image

Hi Adam,

I was having exactly the same issue with 100% mysql load on my own little 5-node openstack setup.

Clearing out the obsolete keystone tokens as you suggested has resolved the issue for me.

DELETE FROM token WHERE NOT DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= expires; Query OK, 185040 rows affected (35.78 sec)

That seems like rather a lot of expired tokens to be stored in the database. Things are much faster now they're gone.

Cheers,

Jim

edit flag offensive delete link more
0

answered 2013-06-03 18:42:10 -0600

adam500 gravatar image

Clearing the token table is the answer here. Currently this is not automatically done for security reasons. For more information see the following bug report: https://bugs.launchpad.net/keystone/+bug/1182481 (https://bugs.launchpad.net/keystone/+...)

I have simply set up a cron that flushes the tokens every day.

Adam

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Get to know Ask OpenStack

Resources for moderators

Question Tools

1 follower

Stats

Asked: 2013-05-16 14:23:38 -0600

Seen: 898 times

Last updated: Jun 03 '13