[Pulp-dev] Concerns about bulk_create and PostgreSQL

[Daniel Alley]

Brian, that's an excellent writup, thanks!

>From what I can tell, it looks very very unlikely that MySQL will add the
"returning" syntax.  MariaDB however has supported "returning" for DELETE
operations *only* for about 5 years, and has a 2 year old issue to add it
for "INSERT" https://jira.mariadb.org/browse/MDEV-10014

However, it also seems like Django takes the "common denominator" approach
towards supporting them (since they are so similar, it treats them as the
same database), so even if one or the other were to add support, I doubt
that functionality would be exposed through the ORM unless they *both* were
to support it.  Therefore I think we can assume we won't see that
functionality any time soon.

I'll probably do the performance investigation as described in #4290 some
time late next week.  I'd like for some of the refactoring that has been
going on in the stages API to be completed before I proceed with that.

On Wed, Jan 16, 2019 at 4:11 PM Brian Bouterse <bbouters at redhat.com> wrote:

> Today @jortel, @dalley, @daviddavis, and @asmacdo been taking a look at
> our options in terms of resolving the PK issue that prevents Pulp from
> running on MariaDB. After considering various of possible solutions on a
> call, and @dalleydoing a little bit of research into the feasibility of
> those, I believe there are only two options:
>
> A) switch to UUIDs or B) implement a fallback that saves one at a time if
> there's no "RETURNING".
>
> In reflecting on this, I have two observations about option (B). First,
> the root cause of this is that MariaDB doesn't implement the "RETURNING"
> option like PostgreSQL and Oracle does. The best solution would be for
> MariaDB to do that. If they aren't able to fix it where the problem is
> created, investing a good deal of time and energy into how to make Pulp run
> fast on MariaDB doesn't seem worth it to me. Secondly, our fallback to
> one-by-one mode would effectively cause Pulp sync on MariaDB to experience
> the slowdowns described in this ticket [0] which were generally identified
> as unacceptable for Pulp users. In terms of timeline, since we had talked
> about MariaDB compatibility being an RC blocker, I don't think we want to
> wait for MariaDB to make any changes, like an implementation for
> "RETURNING".
>
> In thinking about option (A) it resolves the compatibility issue and
> allows plugin writers to avoid the "my dbs don't work the same with
> bulk_create" problem entirely, so it's a great solution to the central
> problem (db compatibility). My only concern with (A) is that we know it is
> a some amount slower in terms of write performance, read performance, and
> index size concerns. @dalley has an issue written up [1] to look at the
> impact of adopting (A) on Pulp workloads.
>
> So the next step would be to complete [1] and share the results. After
> looking at them we would yes/no the adopting of UUIDs as likely our only
> feasible resolution. Any other ideas on what to do or how to do it are
> welcome.
>
> [0]: https://pulp.plan.io/issues/3770
> [1]: https://pulp.plan.io/issues/4290
>
> -Brian
>
>
>
> On Wed, Jan 9, 2019 at 1:41 PM Matthias Dellweg <dellweg at atix.de> wrote:
>
>> On Wed, 9 Jan 2019 08:46:18 -0500
>> David Davis <daviddavis at redhat.com> wrote:
>>
>> > The Rubygems api includes sha as part of the metadata for a gem.
>> > Couldn't you use that as part of the natural key?
>> >
>> > I'm surprised that Chef's supermarket API doesn't include this as
>> > well. Maybe we could open a feature request?
>> >
>> > David
>>
>> WRT rubygems i use repositories generated by `gem generate_index`.
>> There is no chechsum in the metadata that i found.
>> But also the whole thing seems to be documented rather poorly.
>> Using rubygems.org as a remote is not adviseable until there are proper
>> filters implemented. ;)
>>   Matthias
>> _______________________________________________
>> Pulp-dev mailing list
>> Pulp-dev at redhat.com
>> https://www.redhat.com/mailman/listinfo/pulp-dev
>>
> _______________________________________________
> Pulp-dev mailing list
> Pulp-dev at redhat.com
> https://www.redhat.com/mailman/listinfo/pulp-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/pulp-dev/attachments/20190116/b42a3c91/attachment.htm>