[openstack-dev] [Nova] compute_node_get_all() slowness by sql-a ORM
shane.wang at intel.com
Tue Aug 20 07:23:13 UTC 2013
For the bug https://bugs.launchpad.net/nova/+bug/1212428 raised by Chris, we did some further tests.
We have 10K compute nodes in compute_nodes, and each node has 20 stat records.
As we know, by using sql-a ORM, it takes our test code 16 seconds to call compute_node_get_all() and get all nodes.
The return nodes are constructed a list of dictionaries, each dictionary has an attribute 'stats' to get the stats of the node by ORM.
I make a patch to replace ORM with sql-a APIs, and test it.
Calling compute_node_get_all() costs about 4 seconds. However the format of the return nodes is a little bit different from the above.
Without ORM, each node is represented by 20 records, which indicate 20 stat records after join.
Again, I have a patch to use MySQLdb instead of sqlalchemy to fetch all compute nodes. It takes about 2 seconds.
Certainly running the SQL query under mysql command costs 0.54 second.
I don't think using stat table as "key":"value" is a good idea, because each node should aggregate all stat records, translate them into real_name: real_value with statmap() after retrieving them from db, and translate them into key:value with _prep_stats_dict() before saving them to db, at least at this moment.
If we consider to remove ORM some day, the stat format of the query results should be a problem, or we need to construct 'stats' attribute for each node by ourselves based on those 20 records, which still costs time.
I agree with Chris to save 'stats' as a json at this moment, mentioned in the bug description. I have ever had a similar patch https://review.openstack.org/#/c/38802/ which is abandoned currently.
What do you think? Also, do we have a plan to use MySQLdb to access mysql? I know sql-a is common to db backends, is it possible to create an abstract layer to use MySQLdb for mysql, and use sql-a for others in the future release?
More information about the OpenStack-dev