Re: [Openstack] Discussion / proposal: deleted column marker

[Robert Collins]

On Wed, Oct 3, 2012 at 5:42 AM, Pitucha, Stanislaw Izaak
 wrote:
...> - Add a system similar to notifications, but for auditable events - who did
> what with which resource at what time - in some semi-structured way that
> allows reviewing and summaries (basic information as separate columns + a
> description as a text message). I saw there was some blueprint for a
> cloudaudit api
> (https://blueprints.launchpad.net/openstack-common/+spec/cloud-audit-api),
> but not much happened with it for a year, so I'm assuming it's dead now.

There is a django implementation of an audit API at
https://launchpad.net/auditor/ (also on pypi) - this was written for
precisely the use case you describe - to let auditing be done in a
freeform way, to let tools query the audit history, and to let it be
used from heterogeneous services. Does it fit your needs?

> This would allow both proper cleanup of the data and retention of what's
> really necessary. It would also make it possible to use unique constraints
> where they're really needed (mainly IP descriptions) to prevent silly
> mistakes.
>
> Any additional external processing of deleted records would be easier to do
> because the database trigger could be just set on the delete action.
> Thoughts, comments and critique welcome :) Let me know what you think about
> these issues.

+1 on the whole thing :)

-Rob

___
Mailing list: https://launchpad.net/~openstack
Post to : openstack@lists.launchpad.net
Unsubscribe : https://launchpad.net/~openstack
More help   : https://help.launchpad.net/ListHelp

Re: [Openstack] Discussion / proposal: deleted column marker

[Joe Gordon]

+1 to the design proposed here

Looks like there is already a session proposed for Grizzly summit:
http://summit.openstack.org/cfp/details/63

best,
Joe

On Wed, Oct 3, 2012 at 10:07 AM, Federico Innocenti <
federico.innoce...@hp.com> wrote:

>
> +1 to the design proposed here.
>
> Even without embracing anything nova specific but simply from a database
> perspective the soft-delete approach is proven to be a poor solution to
> most of the problems it promises to solve.
>
> In addition to what Stan already pointed out, let me recap something that
> you may already know but so that we have a complete picture, this time
> solely from a db point of view:
>
> - restoring a record is more than doing set deleted=0. It is about
> recovering the all graph of references in the database. Beside the
> complexity of a restore procedure, it makes not possible to selectively
> recover just the information we want.
>
> - tables grow also when alive data (deleted=0) are a small percentage of
> the total. This takes more space/time for backups and maintenance
> operations.
>
> - all queries require for every table involved an additional filter on
> deleted=0 and thus an additional scan, even though likely most DBMS are
> able to optimize queries discriminating on a binary flag.
>
> - using unique constraints and foreign keys is impossible. The nova
> database schema is now holding more foreign keys than in the old days, but
> unless normal deletions are performed they are worthless as they cannot
> protect the database from inconsistencies. And in Nova we saw a certain
> number of inconsistencies arising in a large usage context.
>
>
> What the soft delete approach tries (badly) to do is in practice to keep
> an archive of historical data. The best archiving solution could be left to
> the choice of the single vendor for the time being (until a more
> comprehensive notification system is in place), since every major DBMS
> provides its own facilities to implement it. In MySQL as in many other
> databases you can write db triggers which insert the row being deleted to a
> shadow table in the same db or in another db.
>
> Cheers,
> Federico Innocenti
>
>
>
> __**_
> Mailing list: 
> https://launchpad.net/~**openstack
> Post to : openstack@lists.launchpad.net
> Unsubscribe : 
> https://launchpad.net/~**openstack
> More help   : 
> https://help.launchpad.net/**ListHelp
>
___
Mailing list: https://launchpad.net/~openstack
Post to : openstack@lists.launchpad.net
Unsubscribe : https://launchpad.net/~openstack
More help   : https://help.launchpad.net/ListHelp

Re: [Openstack] Discussion / proposal: deleted column marker

[Federico Innocenti]

+1 to the design proposed here.

Even without embracing anything nova specific but simply from a database 
perspective the soft-delete approach is proven to be a poor solution to most of 
the problems it promises to solve.

In addition to what Stan already pointed out, let me recap something that you 
may already know but so that we have a complete picture, this time solely from 
a db point of view:

- restoring a record is more than doing set deleted=0. It is about recovering 
the all graph of references in the database. Beside the complexity of a restore 
procedure, it makes not possible to selectively recover just the information we 
want.

- tables grow also when alive data (deleted=0) are a small percentage of the 
total. This takes more space/time for backups and maintenance operations.

- all queries require for every table involved an additional filter on 
deleted=0 and thus an additional scan, even though likely most DBMS are able to 
optimize queries discriminating on a binary flag.

- using unique constraints and foreign keys is impossible. The nova database 
schema is now holding more foreign keys than in the old days, but unless normal 
deletions are performed they are worthless as they cannot protect the database 
from inconsistencies. And in Nova we saw a certain number of inconsistencies 
arising in a large usage context.


What the soft delete approach tries (badly) to do is in practice to keep an 
archive of historical data. The best archiving solution could be left to the 
choice of the single vendor for the time being (until a more comprehensive 
notification system is in place), since every major DBMS provides its own 
facilities to implement it. In MySQL as in many other databases you can write 
db triggers which insert the row being deleted to a shadow table in the same db 
or in another db.

Cheers,
Federico Innocenti


___
Mailing list: https://launchpad.net/~openstack
Post to : openstack@lists.launchpad.net
Unsubscribe : https://launchpad.net/~openstack
More help   : https://help.launchpad.net/ListHelp

Re: [Openstack] Discussion / proposal: deleted column marker

[Doug Hellmann]

On Wed, Oct 3, 2012 at 9:08 AM, Day, Phil  wrote:

> I *think* deleted flavours used to be needed as there could still be
> instances running against them and the flavour definition was used by the
> quota calculations.  Not sure if this is still the case, or if the data now
> comes straight from the instances table.Some aspects of a flavour (e.g.
> rxtx_factor) could be useful to a scheduler, and that data currently isn't
> saved into the instance.
>
> I guess the usage audit type functionality (i.e. tell me about all
> instances that have run sometime in this audit period) may be another case
> where deleted instances are required at the moment.
>

That data is being gathered by ceilometer now, so maybe we can do away with
it in the nova database during Grizzly.

Doug


>
>
>
> -Original Message-
> From: openstack-bounces+philip.day=hp@lists.launchpad.net [mailto:
> openstack-bounces+philip.day=hp@lists.launchpad.net] On Behalf Of
> Pitucha, Stanislaw Izaak
> Sent: 03 October 2012 13:09
> To: openstack@lists.launchpad.net
> Subject: Re: [Openstack] Discussion / proposal: deleted column marker
>
> Hi Johannes,
> I know the names collide here, but since this technique is known as
> soft-deletes... We need more namespaces :)
>
> Thanks for the idea of grepping for read_deleted. Fortunately I think the
> situation isn't as bad as it would seem. Let me group the places which
> change read_deleted in the code (many results from grep are comments).
> Reading only deleted entries, or all:
>
> - xenserver (instance) - cleanup tool - I don't do xen, so I'm not sure
> how useful is it. Anyone?
> - tests - can be ignored - if there is no functionality, tests can be
> killed
> - sqlalchemy api (instance) - fixed ip can reference a deleted instance
> (tricky situation; from the commit message: "It adds a test  to verify that
> the code works with a duplicate deleted floating_ip" - this seems very
> wrong...)
> - sqlalchemy api (iscsi) - getting deleted iscsi targets which are still
> referenced by volume
> - sqlalchemy api (various) - instance migration, s3image, bandwidth,
> storage manager, flavors (only available from nova-manage)
> - compute manager (instance) - reaping deleted instances - I can't see why
> the same logic wouldn't apply if the rows are actually missing (needs
> analysis, maybe there's a reason)
> - compute instance_types (flavour) - apparently flavours are usually read
> even if they're deleted
> - network manager (instance) - making sure that ips/networks can be
> removed even if the instance is already deleted
>
> So here's what I can see: pretty much all the usage is about deleting
> instances or making sure parts connected to instances go away if the
> instance is deleted earlier. It doesn't seem right, but could be
> progressively fixed. It looks like another "state" of the instance, which
> could be integrated into the other state fields.
>
> Nothing else uses the deleted column explicitly (unless I missed something
> - possible). Ips, networks, keys, anything that actually goes away
> permanently (and doesn't involve a big chain of cleanup events) is never
> read back once it's marked as deleted.
> So maybe a better approach is not to remove the deleted column completely,
> but to start stripping it from places where it's not needed (fixed,
> floating ips, networks, ssh keys being good candidates). This could be done
> by creating a new layer over NovaBase and removing the "deleted" marker
> from NovaBase itself. Or maybe even by creating a SoftDeleteMixin and
> applying it to all current models, then removing it where unnecessary? That
> would keep the current behaviour where it's currently needed, but at the
> same time it would provide a known migration path to get rid of it.
> We could start stripping the new layer then table by table and adding
> unique constraints where they make sense, before trying to tackle the
> really tricky parts (for instances table, maybe the marker actually makes
> sense? maybe not? - it's definitely not going to be an easy decision/fix)
>
> Regards,
> Stanisław Pitucha
> Cloud Services
> Hewlett Packard
>
>
> -Original Message-
> From: openstack-bounces+stanislaw.pitucha=hp@lists.launchpad.net
> [mailto:openstack-bounces+stanislaw.pitucha=hp@lists.launchpad.net] On
> Behalf Of Johannes Erdfelt
> Sent: Tuesday, October 02, 2012 6:12 PM
> To: openstack@lists.launchpad.net
> Subject: Re: [Openstack] Discussion / proposal: deleted column marker
>
> On Tue, Oct 02, 2012, Pitucha, Stanislaw Izaak 
> wrote:
> > Does anyone know why soft-delete is still 

Re: [Openstack] Discussion / proposal: deleted column marker

[Day, Phil]

I *think* deleted flavours used to be needed as there could still be instances 
running against them and the flavour definition was used by the quota 
calculations.  Not sure if this is still the case, or if the data now comes 
straight from the instances table.Some aspects of a flavour (e.g. 
rxtx_factor) could be useful to a scheduler, and that data currently isn't 
saved into the instance.

I guess the usage audit type functionality (i.e. tell me about all instances 
that have run sometime in this audit period) may be another case where deleted 
instances are required at the moment.



-Original Message-
From: openstack-bounces+philip.day=hp@lists.launchpad.net 
[mailto:openstack-bounces+philip.day=hp@lists.launchpad.net] On Behalf Of 
Pitucha, Stanislaw Izaak
Sent: 03 October 2012 13:09
To: openstack@lists.launchpad.net
Subject: Re: [Openstack] Discussion / proposal: deleted column marker

Hi Johannes,
I know the names collide here, but since this technique is known as 
soft-deletes... We need more namespaces :)

Thanks for the idea of grepping for read_deleted. Fortunately I think the 
situation isn't as bad as it would seem. Let me group the places which change 
read_deleted in the code (many results from grep are comments).
Reading only deleted entries, or all:

- xenserver (instance) - cleanup tool - I don't do xen, so I'm not sure how 
useful is it. Anyone?
- tests - can be ignored - if there is no functionality, tests can be killed
- sqlalchemy api (instance) - fixed ip can reference a deleted instance (tricky 
situation; from the commit message: "It adds a test  to verify that the code 
works with a duplicate deleted floating_ip" - this seems very
wrong...)
- sqlalchemy api (iscsi) - getting deleted iscsi targets which are still 
referenced by volume
- sqlalchemy api (various) - instance migration, s3image, bandwidth, storage 
manager, flavors (only available from nova-manage)
- compute manager (instance) - reaping deleted instances - I can't see why the 
same logic wouldn't apply if the rows are actually missing (needs analysis, 
maybe there's a reason)
- compute instance_types (flavour) - apparently flavours are usually read even 
if they're deleted
- network manager (instance) - making sure that ips/networks can be removed 
even if the instance is already deleted

So here's what I can see: pretty much all the usage is about deleting instances 
or making sure parts connected to instances go away if the instance is deleted 
earlier. It doesn't seem right, but could be progressively fixed. It looks like 
another "state" of the instance, which could be integrated into the other state 
fields.

Nothing else uses the deleted column explicitly (unless I missed something - 
possible). Ips, networks, keys, anything that actually goes away permanently 
(and doesn't involve a big chain of cleanup events) is never read back once 
it's marked as deleted.
So maybe a better approach is not to remove the deleted column completely, but 
to start stripping it from places where it's not needed (fixed, floating ips, 
networks, ssh keys being good candidates). This could be done by creating a new 
layer over NovaBase and removing the "deleted" marker from NovaBase itself. Or 
maybe even by creating a SoftDeleteMixin and applying it to all current models, 
then removing it where unnecessary? That would keep the current behaviour where 
it's currently needed, but at the same time it would provide a known migration 
path to get rid of it.
We could start stripping the new layer then table by table and adding unique 
constraints where they make sense, before trying to tackle the really tricky 
parts (for instances table, maybe the marker actually makes sense? maybe not? - 
it's definitely not going to be an easy decision/fix)

Regards,
Stanisław Pitucha
Cloud Services
Hewlett Packard


-Original Message-
From: openstack-bounces+stanislaw.pitucha=hp@lists.launchpad.net
[mailto:openstack-bounces+stanislaw.pitucha=hp@lists.launchpad.net] On
Behalf Of Johannes Erdfelt
Sent: Tuesday, October 02, 2012 6:12 PM
To: openstack@lists.launchpad.net
Subject: Re: [Openstack] Discussion / proposal: deleted column marker

On Tue, Oct 02, 2012, Pitucha, Stanislaw Izaak 
wrote:
> Does anyone know why soft-delete is still in place?
> Are there any reasons it can't / shouldn't be removed at this time?
> If it's possible to remove it, would you miss it?

I'm certainly not a fan of the database soft-delete for many of the same
reasons you've described, but there are some places where removing it would
require code changes.

Off the top of my head would be pretty much anywhere a context sets
read_deleted to 'yes' or 'only', which is a surprising number of places now
that I've done a grep.

I suspect at least a handful of those cases 

Re: [Openstack] Discussion / proposal: deleted column marker

[Pitucha, Stanislaw Izaak]

Hi Johannes,
I know the names collide here, but since this technique is known as
soft-deletes... We need more namespaces :)

Thanks for the idea of grepping for read_deleted. Fortunately I think the
situation isn't as bad as it would seem. Let me group the places which
change read_deleted in the code (many results from grep are comments).
Reading only deleted entries, or all:

- xenserver (instance) - cleanup tool - I don't do xen, so I'm not sure how
useful is it. Anyone?
- tests - can be ignored - if there is no functionality, tests can be killed
- sqlalchemy api (instance) - fixed ip can reference a deleted instance
(tricky situation; from the commit message: "It adds a test  to verify that
the code works with a duplicate deleted floating_ip" - this seems very
wrong...)
- sqlalchemy api (iscsi) - getting deleted iscsi targets which are still
referenced by volume
- sqlalchemy api (various) - instance migration, s3image, bandwidth, storage
manager, flavors (only available from nova-manage)
- compute manager (instance) - reaping deleted instances - I can't see why
the same logic wouldn't apply if the rows are actually missing (needs
analysis, maybe there's a reason)
- compute instance_types (flavour) - apparently flavours are usually read
even if they're deleted
- network manager (instance) - making sure that ips/networks can be removed
even if the instance is already deleted

So here's what I can see: pretty much all the usage is about deleting
instances or making sure parts connected to instances go away if the
instance is deleted earlier. It doesn't seem right, but could be
progressively fixed. It looks like another "state" of the instance, which
could be integrated into the other state fields.

Nothing else uses the deleted column explicitly (unless I missed something -
possible). Ips, networks, keys, anything that actually goes away permanently
(and doesn't involve a big chain of cleanup events) is never read back once
it's marked as deleted.
So maybe a better approach is not to remove the deleted column completely,
but to start stripping it from places where it's not needed (fixed, floating
ips, networks, ssh keys being good candidates). This could be done by
creating a new layer over NovaBase and removing the "deleted" marker from
NovaBase itself. Or maybe even by creating a SoftDeleteMixin and applying it
to all current models, then removing it where unnecessary? That would keep
the current behaviour where it's currently needed, but at the same time it
would provide a known migration path to get rid of it.
We could start stripping the new layer then table by table and adding unique
constraints where they make sense, before trying to tackle the really tricky
parts (for instances table, maybe the marker actually makes sense? maybe
not? - it's definitely not going to be an easy decision/fix)

Regards,
Stanisław Pitucha
Cloud Services 
Hewlett Packard


-Original Message-
From: openstack-bounces+stanislaw.pitucha=hp@lists.launchpad.net
[mailto:openstack-bounces+stanislaw.pitucha=hp@lists.launchpad.net] On
Behalf Of Johannes Erdfelt
Sent: Tuesday, October 02, 2012 6:12 PM
To: openstack@lists.launchpad.net
Subject: Re: [Openstack] Discussion / proposal: deleted column marker

On Tue, Oct 02, 2012, Pitucha, Stanislaw Izaak 
wrote:
> Does anyone know why soft-delete is still in place?
> Are there any reasons it can't / shouldn't be removed at this time?
> If it's possible to remove it, would you miss it?

I'm certainly not a fan of the database soft-delete for many of the same
reasons you've described, but there are some places where removing it would
require code changes.

Off the top of my head would be pretty much anywhere a context sets
read_deleted to 'yes' or 'only', which is a surprising number of places now
that I've done a grep.

I suspect at least a handful of those cases don't need the functionality and
others probably use it as a crutch around other problems.

JE


___
Mailing list: https://launchpad.net/~openstack
Post to : openstack@lists.launchpad.net
Unsubscribe : https://launchpad.net/~openstack
More help   : https://help.launchpad.net/ListHelp


smime.p7s
Description: S/MIME cryptographic signature
___
Mailing list: https://launchpad.net/~openstack
Post to : openstack@lists.launchpad.net
Unsubscribe : https://launchpad.net/~openstack
More help   : https://help.launchpad.net/ListHelp

Re: [Openstack] Discussion / proposal: deleted column marker

[Doug Hellmann]

On Wed, Oct 3, 2012 at 4:46 AM, Haynes, Dave (Cloud Services) <
dave.hay...@hp.com> wrote:

> +1. Let's do it.
>
> If we need to add some extra tests to protect against regressions, then so
> be it. I will help.
> I also think better use could be made of the notifications system. A
> properly defined topic namespace would go a long way to assist that.
>

The ceilometer project is making *extensive* use of the existing
notifications to collect metering data about resources (I expect us to be
consuming notifications from all of the other OpenStack services by the
time we're done). So, if you're going to change the way notifications work,
please bring us into that discussion.

Here are a few proposed summit sessions related to notifications, messaging
in general, and ceilometer integration:

http://summit.openstack.org/cfp/details/110
http://summit.openstack.org/cfp/details/113
http://summit.openstack.org/cfp/details/117
http://summit.openstack.org/cfp/details/128

Both Nick Barcet and I will be at the summit.


>
> -Original Message-
> From: openstack-bounces+dave.haynes=hp@lists.launchpad.net [mailto:
> openstack-bounces+dave.haynes=hp@lists.launchpad.net] On Behalf Of
> Pitucha, Stanislaw Izaak
> Sent: 02 October 2012 17:43
> To: openstack@lists.launchpad.net
> Subject: [Openstack] Discussion / proposal: deleted column marker
>
> Hi all,
> I'd like to open a discussion on a topic that's been bugging me for a
> number of reasons - soft deletes (by that I mean marking rows with
> deleted=1 in the
> db) and related - actions audit. Some research and speculations first...
> To be honest I could not find any reason why the feature is there in the
> first place. Here's the commit that introduced the 'deleted' columns:
>
> https://github.com/openstack/nova/commit/ae6905b9f1ef97206ee3c8722cec3b26fc0
> 64f38 - unfortunately the description says only "Refactored orm to
> support atomic actions". So the guessing part starts here. These are the
> possible uses for soft-deletion of the database records that I could come
> up with:
>
> 1. safety net (recover data what was deleted by accident) 2. audit / log
> (preserve the information about past data) 3. some kind of
> micro-optimisation where update is more useful than deletion
> - be it speed or ease of handling foreign constraints (or not handling
> them straight away more likely) 4. ... no... that's all
>
> But I think there's a number of issues with that approach. First - what
> are the issues with the possible uses above. Then - issues that I can see
> otherwise. Point by point:
>
> 1. Soft-deletion probably makes some restoration possible, but I doubt
> there's much that could be done without full analysis of the situation.
> Mainly because the database is only about metainformation - the actual
> data users care about either goes away (ephemeral disks, memory, ...) or
> not (volumes, networks, ...) and is not recoverable. Since resources like
> ips and volumes can be just reused in other instances, not all recovery is
> possible anyway. Most hardcore fixes could be done by reinserting the
> original/reconstructed data just as easily as verifying what's safe to
> undelete. Both actions require looking at existing data and locking out
> information so it doesn't get reused while we're messing with the previous
> state.
>
> 2. Soft-deleted records are not great as a source of old information. This
> is connected to the previous point - some resources are just reused /
> rewritten instead of created and deleted. For example there's no record of
> what happens with old floating ips - the information gets overwritten when
> the IP is reassigned to the new instance, so the useful bits are gone.
>
> 3. This is the only thing I could come up with related to the commit
> message itself and the "support atomic actions" part. Maybe it was
> sometimes easier to mark something as deleted rather than managing and
> properly ordering deletes of a number of related entries.
>
> So with that out of the way, here's a number of issues related to
> soft-deletes that I run into myself:
>
> 4. Indexing all this data on a busy system is getting a bit silly. Unless
> you do your own cleanup of old entries, you will end up in a situation
> where looking up instances on a host actually looks through thousands of
> "deleted"
> rows even if only around 20 or so can be live and interesting. I know it's
> not a huge deal, but still an unnecessary cpu cycle burning.
>
> 5. Some things are just not possible to do in a safe and portable way at
> the moment. For example adding a new network and fixed IPs (there's a bug
> for that h

Re: [Openstack] Discussion / proposal: deleted column marker

[Haynes, Dave (Cloud Services)]

+1. Let's do it.

If we need to add some extra tests to protect against regressions, then so be 
it. I will help.
I also think better use could be made of the notifications system. A properly 
defined topic namespace would go a long way to assist that.

-Original Message-
From: openstack-bounces+dave.haynes=hp@lists.launchpad.net 
[mailto:openstack-bounces+dave.haynes=hp@lists.launchpad.net] On Behalf Of 
Pitucha, Stanislaw Izaak
Sent: 02 October 2012 17:43
To: openstack@lists.launchpad.net
Subject: [Openstack] Discussion / proposal: deleted column marker

Hi all,
I'd like to open a discussion on a topic that's been bugging me for a number of 
reasons - soft deletes (by that I mean marking rows with deleted=1 in the
db) and related - actions audit. Some research and speculations first...
To be honest I could not find any reason why the feature is there in the first 
place. Here's the commit that introduced the 'deleted' columns:
https://github.com/openstack/nova/commit/ae6905b9f1ef97206ee3c8722cec3b26fc0
64f38 - unfortunately the description says only "Refactored orm to support 
atomic actions". So the guessing part starts here. These are the possible uses 
for soft-deletion of the database records that I could come up with:

1. safety net (recover data what was deleted by accident) 2. audit / log 
(preserve the information about past data) 3. some kind of micro-optimisation 
where update is more useful than deletion
- be it speed or ease of handling foreign constraints (or not handling them 
straight away more likely) 4. ... no... that's all

But I think there's a number of issues with that approach. First - what are the 
issues with the possible uses above. Then - issues that I can see otherwise. 
Point by point:

1. Soft-deletion probably makes some restoration possible, but I doubt there's 
much that could be done without full analysis of the situation.
Mainly because the database is only about metainformation - the actual data 
users care about either goes away (ephemeral disks, memory, ...) or not 
(volumes, networks, ...) and is not recoverable. Since resources like ips and 
volumes can be just reused in other instances, not all recovery is possible 
anyway. Most hardcore fixes could be done by reinserting the 
original/reconstructed data just as easily as verifying what's safe to 
undelete. Both actions require looking at existing data and locking out 
information so it doesn't get reused while we're messing with the previous 
state.

2. Soft-deleted records are not great as a source of old information. This is 
connected to the previous point - some resources are just reused / rewritten 
instead of created and deleted. For example there's no record of what happens 
with old floating ips - the information gets overwritten when the IP is 
reassigned to the new instance, so the useful bits are gone.

3. This is the only thing I could come up with related to the commit message 
itself and the "support atomic actions" part. Maybe it was sometimes easier to 
mark something as deleted rather than managing and properly ordering deletes of 
a number of related entries.

So with that out of the way, here's a number of issues related to soft-deletes 
that I run into myself:

4. Indexing all this data on a busy system is getting a bit silly. Unless you 
do your own cleanup of old entries, you will end up in a situation where 
looking up instances on a host actually looks through thousands of "deleted"
rows even if only around 20 or so can be live and interesting. I know it's not 
a huge deal, but still an unnecessary cpu cycle burning.

5. Some things are just not possible to do in a safe and portable way at the 
moment. For example adding a new network and fixed IPs (there's a bug for that 
https://bugs.launchpad.net/nova/+bug/755138). I tried to fix this situation, 
but actually discovered that this is not possible to do using only sessions and 
with the 'deleted' column in place. There are ways to do it in a specific 
database (you can lock the whole table in mysql for example), but it's not 
portable then. The best you can do easily is limit the issue and hope that two 
inserts in different sessions won't happen at the same time. This could be 
easily done with an unique constraint if the 'deleted' column wasn't there.
I haven't checked, but guess that anything that can be named (and should have a 
unique name) has the same problem - security groups, keys, instances, ...

6. The amount of data grows pretty quickly in a busy environment. It has to be 
cleaned up, but due to some constraints, it can't be done easily in one go. 
Cleanup triggers help here, but that's some additional work that needs 
maintenance during schema changes. Schema changes themselves get interesting 
when you're actually spending time converting mo

Re: [Openstack] Discussion / proposal: deleted column marker

[Johannes Erdfelt]

On Tue, Oct 02, 2012, Pitucha, Stanislaw Izaak  wrote:
> Does anyone know why soft-delete is still in place?
> Are there any reasons it can't / shouldn't be removed at this time?
> If it's possible to remove it, would you miss it?

I'm certainly not a fan of the database soft-delete for many of the same
reasons you've described, but there are some places where removing it
would require code changes.

Off the top of my head would be pretty much anywhere a context sets
read_deleted to 'yes' or 'only', which is a surprising number of places
now that I've done a grep.

I suspect at least a handful of those cases don't need the functionality
and others probably use it as a crutch around other problems.

JE


___
Mailing list: https://launchpad.net/~openstack
Post to : openstack@lists.launchpad.net
Unsubscribe : https://launchpad.net/~openstack
More help   : https://help.launchpad.net/ListHelp

Re: [Openstack] Discussion / proposal: deleted column marker

[Johannes Erdfelt]

On Tue, Oct 02, 2012, Pitucha, Stanislaw Izaak  wrote:
> I'd like to open a discussion on a topic that's been bugging me for a number
> of reasons - soft deletes (by that I mean marking rows with deleted=1 in the
> db) and related - actions audit.

This is a subject I'm interested in and I'll follow up in a little bit
with some comments, but I wanted to clarify that there is also a VM state
called "soft delete" that is different than this.

The concepts between this database "soft delete" and the VM "soft
delete" are similar (in that mark an instance as deleted in software,
but don't remove data associated with them), but they different and
unrelated :)

JE


___
Mailing list: https://launchpad.net/~openstack
Post to : openstack@lists.launchpad.net
Unsubscribe : https://launchpad.net/~openstack
More help   : https://help.launchpad.net/ListHelp

[Openstack] Discussion / proposal: deleted column marker

[Pitucha, Stanislaw Izaak]

Hi all,
I'd like to open a discussion on a topic that's been bugging me for a number
of reasons - soft deletes (by that I mean marking rows with deleted=1 in the
db) and related - actions audit. Some research and speculations first...
To be honest I could not find any reason why the feature is there in the
first place. Here's the commit that introduced the 'deleted' columns:
https://github.com/openstack/nova/commit/ae6905b9f1ef97206ee3c8722cec3b26fc0
64f38 - unfortunately the description says only "Refactored orm to support
atomic actions". So the guessing part starts here. These are the possible
uses for soft-deletion of the database records that I could come up with:

1. safety net (recover data what was deleted by accident)
2. audit / log (preserve the information about past data)
3. some kind of micro-optimisation where update is more useful than deletion
- be it speed or ease of handling foreign constraints (or not handling them
straight away more likely)
4. ... no... that's all

But I think there's a number of issues with that approach. First - what are
the issues with the possible uses above. Then - issues that I can see
otherwise. Point by point:

1. Soft-deletion probably makes some restoration possible, but I doubt
there's much that could be done without full analysis of the situation.
Mainly because the database is only about metainformation - the actual data
users care about either goes away (ephemeral disks, memory, ...) or not
(volumes, networks, ...) and is not recoverable. Since resources like ips
and volumes can be just reused in other instances, not all recovery is
possible anyway. Most hardcore fixes could be done by reinserting the
original/reconstructed data just as easily as verifying what's safe to
undelete. Both actions require looking at existing data and locking out
information so it doesn't get reused while we're messing with the previous
state.

2. Soft-deleted records are not great as a source of old information. This
is connected to the previous point - some resources are just reused /
rewritten instead of created and deleted. For example there's no record of
what happens with old floating ips - the information gets overwritten when
the IP is reassigned to the new instance, so the useful bits are gone.

3. This is the only thing I could come up with related to the commit message
itself and the "support atomic actions" part. Maybe it was sometimes easier
to mark something as deleted rather than managing and properly ordering
deletes of a number of related entries.

So with that out of the way, here's a number of issues related to
soft-deletes that I run into myself:

4. Indexing all this data on a busy system is getting a bit silly. Unless
you do your own cleanup of old entries, you will end up in a situation where
looking up instances on a host actually looks through thousands of "deleted"
rows even if only around 20 or so can be live and interesting. I know it's
not a huge deal, but still an unnecessary cpu cycle burning.

5. Some things are just not possible to do in a safe and portable way at the
moment. For example adding a new network and fixed IPs (there's a bug for
that https://bugs.launchpad.net/nova/+bug/755138). I tried to fix this
situation, but actually discovered that this is not possible to do using
only sessions and with the 'deleted' column in place. There are ways to do
it in a specific database (you can lock the whole table in mysql for
example), but it's not portable then. The best you can do easily is limit
the issue and hope that two inserts in different sessions won't happen at
the same time. This could be easily done with an unique constraint if the
'deleted' column wasn't there.
I haven't checked, but guess that anything that can be named (and should
have a unique name) has the same problem - security groups, keys, instances,
...

6. The amount of data grows pretty quickly in a busy environment. It has to
be cleaned up, but due to some constraints, it can't be done easily in one
go. Cleanup triggers help here, but that's some additional work that needs
maintenance during schema changes. Schema changes themselves get interesting
when you're actually spending time converting mostly rows you really don't
care about. There were also instances where migration over many steps failed
for some reason on very old rows (virtual interface related, can't recall
which step was it at the moment). 

7. Not directly related, but I'll get back to that in the summary: owners of
bigger deployments will either want to or are required to hold some record
of various events and customer information. For example to handle security
abuse reports, it would be great to know who owned a specific floating IP at
a specific moment.


So what's my point?
Any use case I can find right now is not really improved by the current
schema. It doesn't look like there are many benefits, but there are
definitely some downsides.

Does anyone know why soft-delete is still in place?
Are there any