<div dir="ltr"><div dir="ltr"><div dir="ltr"><div class="gmail_default" style="font-size:small">Hi Mohammed,</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Apr 1, 2019 at 4:29 AM Mohammed Naser <<a href="mailto:mnaser@vexxhost.com">mnaser@vexxhost.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Sun, Mar 31, 2019 at 10:21 PM Mohammed Naser <<a href="mailto:mnaser@vexxhost.com" target="_blank">mnaser@vexxhost.com</a>> wrote:<br>
><br>
> Hi there,<br>
><br>
> During upgrades, I've noticed that when running online_data_migrations<br>
> with "infinite-until-done" mode, it loops over all of the migrations<br>
> one by one.<br>
><br>
> However, one of the online data migrations<br>
> (instance_obj.populate_missing_availability_zones) makes a query that<br>
> takes a really long time as it seems inefficient (which eventually<br>
> results in 0, cause it already ran), which means as it loops in<br>
> "blocks" of 50, there's almost a 2-3 to 8 minute wait in really large<br>
> environments.<br></blockquote><div><br></div><div><div class="gmail_default" style="font-size:small">Hmm, all we do in that migration is try to get instance records whose availability_zone is None [1] and if no records are found we just return all done. While I agree that once a migration is done, the next time we loop through all the migrations we again do the query at least once to ensure we get back zero records for most of the migrations (we don't always use persistent markers to see if the migration was completed in the previous run) which means we do run through the whole table.</div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
><br>
> The question ends up in specific:<br>
><br>
> SELECT count(*) AS count_1<br>
> FROM (SELECT instance_extra.created_at AS instance_extra_created_at,<br>
> instance_extra.updated_at AS instance_extra_updated_at,<br>
> instance_extra.deleted_at AS instance_extra_deleted_at,<br>
> instance_extra.deleted AS instance_extra_deleted, <a href="http://instance_extra.id" rel="noreferrer" target="_blank">instance_extra.id</a> AS<br>
> instance_extra_id, instance_extra.instance_uuid AS<br>
> instance_extra_instance_uuid<br>
> FROM instance_extra<br>
> WHERE instance_extra.keypairs IS NULL AND instance_extra.deleted = 0) AS anon_1<br>
><br></blockquote><div><br></div><div><div class="gmail_default" style="font-size:small">This is the keypair_obj.migrate_keypairs_to_api_db migration that was added in Newton.</div></div><div><div class="gmail_default" style="font-size:small">Since we are just counting, we need not pull the whole record I guess (not sure how much improvement that would cause), I am myself not an SQL expert, maybe jaypipes can help here.</div><br></div><div><div class="gmail_default" style="font-size:small"><br></div></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
> The explain for the DB query in this example:<br>
><br>
> +------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+<br>
> | id | select_type | table | type | possible_keys | key |<br>
> key_len | ref | rows | Extra |<br>
> +------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+<br>
> | 1 | SIMPLE | instance_extra | ALL | NULL | NULL |<br>
> NULL | NULL | 382473 | Using where |<br>
> +------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+<br>
><br>
> It's possible that it can be ever worse, as this number is from<br>
> another very-long running environments.<br>
><br>
> +------+-------------+----------------+------+---------------+------+---------+------+---------+-------------+<br>
> | id | select_type | table | type | possible_keys | key |<br>
> key_len | ref | rows | Extra |<br>
> +------+-------------+----------------+------+---------------+------+---------+------+---------+-------------+<br>
> | 1 | SIMPLE | instance_extra | ALL | NULL | NULL |<br>
> NULL | NULL | 3008741 | Using where |<br>
> +------+-------------+----------------+------+---------------+------+---------+------+---------+-------------+<br>
><br>
> I'm not the SQL expert, could we not optimize this? Alternatively,<br>
> could we update the online data migrations code to "pop out" any of<br>
> the migrations that return 0 for the next iteration, that way it only<br>
> works on those online_data_migrations that *have* to be done, and<br>
> ignore those it knows are done?<br></blockquote><div><br></div><div><div class="gmail_default" style=""><span style="font-size:small"></span></div></div><div class="gmail_default" style=""><span style="font-size:small">I don't know if there is a good way by which we can persistently store the state of finished migrations to ensure they are not executed ever again (as in not having to make the query) once done.</span></div><div class="gmail_default" style=""><span style="font-size:small">It would also be nice to also be able to opt-in into specific migrations specially since these span over releases.</span></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
and while we're at it, can we just bump the default rows-per-run to<br>
something more than<br>
50 rows? it seems super .. small :)<br>
<br></blockquote><div> </div><div><div class="gmail_default" style="font-size:small">I agree the default 50 is a pretty small batch size specially for large deployments.</div><br></div><div class="gmail_default" style="font-size:small">[1] <a href="https://github.com/openstack/nova/blob/95a87bce9fa7575c172a7d06344fd3cd070db587/nova/objects/instance.py#L1302">https://github.com/openstack/nova/blob/95a87bce9fa7575c172a7d06344fd3cd070db587/nova/objects/instance.py#L1302</a></div></div><div><br></div><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div class="gmail_default" style="font-size:small">Thanks for bringing this up,</div><div>Regards,<br></div><div>Surya.<br></div></div></div></div></div></div></div>