openstack team mailing list archive

[Sean Dague <sdague@xxxxxxxxxxxxxxxxxx>]

On 04/11/2012 04:48 PM, Yun Mao wrote:
<snip>
> * Database access: Each "nova list" API call will issue 4 db APIs: 3
> instance_get_all_by_filters(), 1
> instance_fault_get_by_instance_uuids(), so 1200 db API calls total
> (note: not necessarily 1200 SQL statements, could be more). The 900
> instance_get_all_by_filters() calls took 30.2 seconds (i.e. 0.03s
> each)! The 300 instance_fault_get_by_instance_uuids() calls only took
> 1.129 seconds (0.004 each).
>
> You might think: MySQL sucks. Not so fast. Remember this is a tiny
> database with only 10 VMs. Profile also shows that the actual
> _mysql.connection.query() method only took 1.883 seconds in total. So,
> we pretty much spend 29 seconds out of 60 seconds doing either
> sqlalchemy stuff or our own wrapper. You can also see from the sheer
> volume of sqlalchemy library calls involved.

Reducing the sqlalchemy 50% is probably going to involve being smarter 
about assembling the queries in a more complex way, that prevents us 
from going to the db quite so often.

On the MySQL query 50% of the time, it would be good if you can figure 
out if we are table scanning in the instance_get_all_by_filters. My 
inspection so far definitely shows a lot of things we do WHERE clauses 
on that don't have indexes, which is generally bad form.

This bug also has a previous, slightly different, look at digging out 
some of these issues - https://bugs.launchpad.net/nova/+bug/964824.

	-Sean

--
Sean Dague
IBM Linux Technology Center
email: sldague@xxxxxxxxxx
alt-email: sdague@xxxxxxxxxxxxxxxxxx