Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
On 3/9/2014 9:05 PM, ChangBo Guo wrote: 2014-03-10 4:47 GMT+08:00 Jay Pipes jaypi...@gmail.com mailto:jaypi...@gmail.com: 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. No, I don't think this matters much, to be honest. I'm not entirely sure what the long-term purpose of the shadow tables are in Nova -- perhaps someone could clue me in to whether the plan is to keep them around? As I know the tables shadow_* are used by command ' nova-manage db archive_deleted_rows' , which moves records with deleted=True to table shadow_* . That means these tables are used by other process, So, I think we need other tables to store the old records in your migration . So you mean move records where instances.uuid == None to shadow_instances? That's not going to work though if we make the uuid column non-nullable on both instances and shadow_instances, unless you generate a random UUID for the shadow_instances table records that get moved, which is just another hack - and that would break moving them back on downgrade since you wouldn't know which records to move back, i.e. since you wouldn't be able to query shadow_instances for records where instances.uuid == None. Other thoughts? If you did really want to back these records up, I think it would have to be a different backup table rather than shadow_instances since I think we want to keep the schema the same between instances and shadow_instances. -- ChangBo Guo(gcb) ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev -- Thanks, Matt Riedemann ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
On 3/9/2014 9:18 PM, Jay Pipes wrote: On Mon, 2014-03-10 at 10:05 +0800, ChangBo Guo wrote: 2014-03-10 4:47 GMT+08:00 Jay Pipes jaypi...@gmail.com: 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. No, I don't think this matters much, to be honest. I'm not entirely sure what the long-term purpose of the shadow tables are in Nova -- perhaps someone could clue me in to whether the plan is to keep them around? As I know the tables shadow_* are used by command ' nova-manage db archive_deleted_rows' , which moves records with deleted=True to table shadow_* . That means these tables are used by other process, So, I think we need other tables to store the old records in your migration. Yeah, that's what I understood the shadow tables were used for, I just didn't know what the long-term future of these tables was... curious if there's been any discussion about that. Best, -jay ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev I think Joe Gordon was working on something in the hopes of eventually killing the shadow tables but I can't remember exactly what that was now. -- Thanks, Matt Riedemann ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
On 03/10/2014 10:11 AM, Matt Riedemann wrote: On 3/9/2014 9:18 PM, Jay Pipes wrote: On Mon, 2014-03-10 at 10:05 +0800, ChangBo Guo wrote: 2014-03-10 4:47 GMT+08:00 Jay Pipes jaypi...@gmail.com: 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. No, I don't think this matters much, to be honest. I'm not entirely sure what the long-term purpose of the shadow tables are in Nova -- perhaps someone could clue me in to whether the plan is to keep them around? As I know the tables shadow_* are used by command ' nova-manage db archive_deleted_rows' , which moves records with deleted=True to table shadow_* . That means these tables are used by other process, So, I think we need other tables to store the old records in your migration. Yeah, that's what I understood the shadow tables were used for, I just didn't know what the long-term future of these tables was... curious if I think Joe Gordon was working on something in the hopes of eventually killing the shadow tables but I can't remember exactly what that was now. https://blueprints.launchpad.net/nova/+spec/db-purge-engine It didn't get done for Icehouse. Maybe it will for Juno. Everyone agrees that db-archiving is not ideal, but we probably want something better in place before we rip it out. -- David Ripton Red Hat drip...@redhat.com ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
Joe, Fully agree. We should just make a blueprint Get rid of soft deletion. So we will get much better performance of DB + cleaner code, and avoid such things like shadow tables and DB purge scripts. Probably we should some other thread to cover this topic? Best regards, Boris Pavlovic On Mon, Mar 10, 2014 at 10:57 PM, Joe Gordon joe.gord...@gmail.com wrote: On Mon, Mar 10, 2014 at 7:11 AM, Matt Riedemann mrie...@linux.vnet.ibm.com wrote: On 3/9/2014 9:18 PM, Jay Pipes wrote: On Mon, 2014-03-10 at 10:05 +0800, ChangBo Guo wrote: 2014-03-10 4:47 GMT+08:00 Jay Pipes jaypi...@gmail.com: 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. No, I don't think this matters much, to be honest. I'm not entirely sure what the long-term purpose of the shadow tables are in Nova -- perhaps someone could clue me in to whether the plan is to keep them around? As I know the tables shadow_* are used by command ' nova-manage db archive_deleted_rows' , which moves records with deleted=True to table shadow_* . That means these tables are used by other process, So, I think we need other tables to store the old records in your migration. Yeah, that's what I understood the shadow tables were used for, I just didn't know what the long-term future of these tables was... curious if there's been any discussion about that. Best, -jay ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev I think Joe Gordon was working on something in the hopes of eventually killing the shadow tables but I can't remember exactly what that was now. I haven't been working on this but I do have a plan. Originally we couldn't hard delete anything in nova DB, because people wanted to keep the records around for well record keeping. The long term solution is make nova support (although not default to) hard delete. This means we need another place to store these records (ceilometer). Until then, we have shadow tables as a short term solution. If you want to keep records around but don't want them in your main nova DB. On a related note, nothing in nova should actually be using soft deleted data or shadow tables, any cases should be treated as bugs. -- Thanks, Matt Riedemann ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
On Mon, Mar 10, 2014 at 12:12 PM, Boris Pavlovic bpavlo...@mirantis.com wrote: Joe, Fully agree. We should just make a blueprint Get rid of soft deletion. So we will get much better performance of DB + cleaner code, and avoid such things like shadow tables and DB purge scripts. ++ Probably we should some other thread to cover this topic? ++ Best regards, Boris Pavlovic On Mon, Mar 10, 2014 at 10:57 PM, Joe Gordon joe.gord...@gmail.com wrote: On Mon, Mar 10, 2014 at 7:11 AM, Matt Riedemann mrie...@linux.vnet.ibm.com wrote: On 3/9/2014 9:18 PM, Jay Pipes wrote: On Mon, 2014-03-10 at 10:05 +0800, ChangBo Guo wrote: 2014-03-10 4:47 GMT+08:00 Jay Pipes jaypi...@gmail.com: 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. No, I don't think this matters much, to be honest. I'm not entirely sure what the long-term purpose of the shadow tables are in Nova -- perhaps someone could clue me in to whether the plan is to keep them around? As I know the tables shadow_* are used by command ' nova-manage db archive_deleted_rows' , which moves records with deleted=True to table shadow_* . That means these tables are used by other process, So, I think we need other tables to store the old records in your migration. Yeah, that's what I understood the shadow tables were used for, I just didn't know what the long-term future of these tables was... curious if there's been any discussion about that. Best, -jay ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev I think Joe Gordon was working on something in the hopes of eventually killing the shadow tables but I can't remember exactly what that was now. I haven't been working on this but I do have a plan. Originally we couldn't hard delete anything in nova DB, because people wanted to keep the records around for well record keeping. The long term solution is make nova support (although not default to) hard delete. This means we need another place to store these records (ceilometer). Until then, we have shadow tables as a short term solution. If you want to keep records around but don't want them in your main nova DB. On a related note, nothing in nova should actually be using soft deleted data or shadow tables, any cases should be treated as bugs. -- Thanks, Matt Riedemann ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
On 12/16/2013 11:01 AM, Shawn Hartsock wrote: +1 on a migration to make uuid a non-nullable column. I advocated a few patches back in Havana that make assumptions based on the UUID being present and unique per instance. If it gets nulled the VMware drivers will have have breakage and I have no idea how to avoid that reasonably without the UUID. On Mon, Dec 16, 2013 at 11:59 AM, Russell Bryant rbry...@redhat.com mailto:rbry...@redhat.com wrote: On 12/16/2013 11:45 AM, Matt Riedemann wrote: 1. Add a migration to change instances.uuid to non-nullable. Besides the obvious con of having yet another migration script, this seems the most straight-forward. The instance object class already defines the uuid field as non-nullable, so it's constrained at the objects layer, just not in the DB model. Plus I don't think we'd ever have a case where instance.uuid is null, right? Seems like a lot of things would break down if that happened. With this option I can build on top of it for the DB2 migration support to add the same FKs as the other engines. Yeah, having instance.uuid nullable doesn't seem valuable to me, so this seems OK. -- Russell Bryant ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org mailto:OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev -- # Shawn.Hartsock - twitter: @hartsock - plus.google.com/+ShawnHartsock http://plus.google.com/+ShawnHartsock ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev I've been working on this more and am running up against some issues, part of this has to do with my lack of sqlalchemy know-how and inexperience with writing DB migrations, so dumping some info/problems here to see where people would like me to take this. My original thinking for doing a migration was to delete the instances records where uuid == None and then move those to shadow_instances, then make instances.uuid.nullable=False. Some of the problems with this approach are: 1. There are at least 5 other tables related to instances that need to be handled for a delete: InstanceFault, InstanceMetadata, InstanceSystemMetadata, InstanceInfoCache and SecurityGroupInstanceAssociation. Also, these tables don't define their instance_uuid column the same way, some have it nullable=False and others don't. 2. I'm not sure if I can use a session in the migration to make it a transaction. 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. The whole reason behind using shadow_instances (or any backup table I guess) was so I could restore the records on DB downgrade. So the more I think about this, I'm getting to the point of asking: 1. Do we even care about instances where uuid is None? I'd have to think those wouldn't be working well in the current code with how everything relies on uuid for foreign keys and tracking relationships to volumes, images and networks across services. If the answer is 'no' then the migration is pretty simple, just delete the records where uuid is None and be done with it. You couldn't downgrade to get them back, but in this case we're asserting that we don't want them back. 2. Have an alternative schema in the DB2 case. This would be handled in the 216_havana migration when the instances table is defined and created, we'd just make the uuid column non-nullable in the DB2 case and leave it nullable for all other engines. Anyone moving to DB2 would have to install from scratch anyway since there is no tooling to migrate a MySQL DB to DB2, for example. As it stands, the 216_havana migration in my patch [1] already has a different schema for DB2 because of the foreign keys it can't create due to this problem. Anyway, looking for some thoughts on how to best handle this, or if anyone has other ideas or good reasons why either approach couldn't be used. [1] https://review.openstack.org/#/c/69047/ -- Thanks, Matt Riedemann ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
Hi Matt, interesting questions/points. Some comments inline. On Sun, 2014-03-09 at 15:20 -0500, Matt Riedemann wrote: snip I've been working on this more and am running up against some issues, part of this has to do with my lack of sqlalchemy know-how and inexperience with writing DB migrations, so dumping some info/problems here to see where people would like me to take this. My original thinking for doing a migration was to delete the instances records where uuid == None and then move those to shadow_instances, then make instances.uuid.nullable=False. Some of the problems with this approach are: 1. There are at least 5 other tables related to instances that need to be handled for a delete: InstanceFault, InstanceMetadata, InstanceSystemMetadata, InstanceInfoCache and SecurityGroupInstanceAssociation. Also, these tables don't define their instance_uuid column the same way, some have it nullable=False and others don't. All tables should define the instance uuid column in the same way, and making that consistent should best be done as part of the same migration. The reasoning, of course, is that these columns represent identical data (primary to child key relations), and therefore the schema of all the columns should be identical. 2. I'm not sure if I can use a session in the migration to make it a transaction. You can use a transaction around all statements, including DDL, in PostgreSQL. In MySQL, you can use a transaction only around DML statements, but any DDL statement will cause a flush of all transactions before the DDL statement begins. I'd recommend doing all of the necessary moves of records (between the shadow tables and regular tables) within a single transaction so that at least there is some level of data consistency ensured for those operations. But, unfortunately, the DDL schema changes will not occur within a transaction unless you are using PostgreSQL and Alembic migrations. 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. No, I don't think this matters much, to be honest. I'm not entirely sure what the long-term purpose of the shadow tables are in Nova -- perhaps someone could clue me in to whether the plan is to keep them around? The whole reason behind using shadow_instances (or any backup table I guess) was so I could restore the records on DB downgrade. So the more I think about this, I'm getting to the point of asking: 1. Do we even care about instances where uuid is None? I'd have to think those wouldn't be working well in the current code with how everything relies on uuid for foreign keys and tracking relationships to volumes, images and networks across services. Agreed. I don't personally think instance records with no UUID are useful in Nova any more. If the answer is 'no' then the migration is pretty simple, just delete the records where uuid is None and be done with it. You couldn't downgrade to get them back, but in this case we're asserting that we don't want them back. This is exactly what I would recommend. 2. Have an alternative schema in the DB2 case. This would be handled in the 216_havana migration when the instances table is defined and created, we'd just make the uuid column non-nullable in the DB2 case and leave it nullable for all other engines. Anyone moving to DB2 would have to install from scratch anyway since there is no tooling to migrate a MySQL DB to DB2, for example. As it stands, the 216_havana migration in my patch [1] already has a different schema for DB2 because of the foreign keys it can't create due to this problem. Nah, I don't think we want to keep a special schema just for DB2. That's technical debt that I don't think it worthwhile to keep around... Best, -jay Anyway, looking for some thoughts on how to best handle this, or if anyone has other ideas or good reasons why either approach couldn't be used. [1] https://review.openstack.org/#/c/69047/ ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
2014-03-10 4:47 GMT+08:00 Jay Pipes jaypi...@gmail.com: 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. No, I don't think this matters much, to be honest. I'm not entirely sure what the long-term purpose of the shadow tables are in Nova -- perhaps someone could clue me in to whether the plan is to keep them around? As I know the tables shadow_* are used by command ' nova-manage db archive_deleted_rows' , which moves records with deleted=True to table shadow_* . That means these tables are used by other process, So, I think we need other tables to store the old records in your migration . -- ChangBo Guo(gcb) ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
On Mon, 2014-03-10 at 10:05 +0800, ChangBo Guo wrote: 2014-03-10 4:47 GMT+08:00 Jay Pipes jaypi...@gmail.com: 3. This would make the instances and shadow_instances tables have different schemas, i.e. instances.uuid would be nullable=False in instances but nullable=True in shadow_instances. Maybe this doesn't matter. No, I don't think this matters much, to be honest. I'm not entirely sure what the long-term purpose of the shadow tables are in Nova -- perhaps someone could clue me in to whether the plan is to keep them around? As I know the tables shadow_* are used by command ' nova-manage db archive_deleted_rows' , which moves records with deleted=True to table shadow_* . That means these tables are used by other process, So, I think we need other tables to store the old records in your migration. Yeah, that's what I understood the shadow tables were used for, I just didn't know what the long-term future of these tables was... curious if there's been any discussion about that. Best, -jay ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
+1 on a migration to make uuid a non-nullable column. I advocated a few patches back in Havana that make assumptions based on the UUID being present and unique per instance. If it gets nulled the VMware drivers will have have breakage and I have no idea how to avoid that reasonably without the UUID. On Mon, Dec 16, 2013 at 11:59 AM, Russell Bryant rbry...@redhat.com wrote: On 12/16/2013 11:45 AM, Matt Riedemann wrote: 1. Add a migration to change instances.uuid to non-nullable. Besides the obvious con of having yet another migration script, this seems the most straight-forward. The instance object class already defines the uuid field as non-nullable, so it's constrained at the objects layer, just not in the DB model. Plus I don't think we'd ever have a case where instance.uuid is null, right? Seems like a lot of things would break down if that happened. With this option I can build on top of it for the DB2 migration support to add the same FKs as the other engines. Yeah, having instance.uuid nullable doesn't seem valuable to me, so this seems OK. -- Russell Bryant ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev -- # Shawn.Hartsock - twitter: @hartsock - plus.google.com/+ShawnHartsock ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
On 12/16/2013 11:45 AM, Matt Riedemann wrote: 1. Add a migration to change instances.uuid to non-nullable. Besides the obvious con of having yet another migration script, this seems the most straight-forward. The instance object class already defines the uuid field as non-nullable, so it's constrained at the objects layer, just not in the DB model. Plus I don't think we'd ever have a case where instance.uuid is null, right? Seems like a lot of things would break down if that happened. With this option I can build on top of it for the DB2 migration support to add the same FKs as the other engines. Yeah, having instance.uuid nullable doesn't seem valuable to me, so this seems OK. -- Russell Bryant ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova][db] Thoughts on making instances.uuid non-nullable?
On 12/16/2013 11:59 AM, Russell Bryant wrote: On 12/16/2013 11:45 AM, Matt Riedemann wrote: 1. Add a migration to change instances.uuid to non-nullable. Besides the obvious con of having yet another migration script, this seems the most straight-forward. The instance object class already defines the uuid field as non-nullable, so it's constrained at the objects layer, just not in the DB model. Plus I don't think we'd ever have a case where instance.uuid is null, right? Seems like a lot of things would break down if that happened. With this option I can build on top of it for the DB2 migration support to add the same FKs as the other engines. Yeah, having instance.uuid nullable doesn't seem valuable to me, so this seems OK. +1 -jay ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev