Search Postgresql Archives

Re: Uber migrated from Postgres to MySQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



If we're talking about favourite bug https://bugs.mysql.com/bug.php?id=21153 is mine

Join with many tables hangs mysql (and taking 100% cpu)
Description:
the following query hangs the mysql server taking 100% cpu. also an "explain" of the query hangs the server!

It's "not a bug" because you can change some of the default query planning parameters to avoid it:

Igor Babaev
This is not a bug.
The reported query is a 18-way join. For such queries we expect that the full search for the best execution plan will take a significant amount of time.
At the same due to a specific structure of the reported query we can hope to get a good execution plan with a limited search (see Manual 5.0: 7.5.3. Controlling Query Optimizer Performance).
Setting the value of the global variable 'optimizer_search_depth' to 4 or even to 2 we can get the same execution plan as with a full search. Yet it will take much less time:

To me that speaks volumes. Sure, you can tweak a db params to get better performance, but I shouldn't have to deviate from the default for it to simply work at all!

Jim

On Thu, Jul 28, 2016 at 9:38 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@xxxxxxxx> wrote:
> On 27 July 2016 at 15:22, Scott Mead <scottm@xxxxxxxxxxx> wrote:
>>
>>  "The bug we ran into only affected certain releases of Postgres 9.2 and
>> has been fixed for a long time now. However, we still find it worrisome that
>> this class of bug can happen at all. A new version of Postgres could be
>> released at any time that has a bug of this nature, and because of the way
>> replication works, this issue has the potential to spread into all of the
>> databases in a replication hierarchy."
>>
>>
>> ISTM that they needed a tire swing and were using a dump truck.  Hopefully
>> they vectored somewhere in the middle and got themselves a nice sandbox.
>
>
> At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in
> an initial refusal to accept any problem existed, followed by (once that
> particular strategy had run out of steam) the developer simply ignoring the
> bug until it was closed automatically by their bug system. As far as I'm
> aware those bugs still exist in the most recent version.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.

As someone who has gotten more than one bug fix from pgsql in less
than 48 hours, I feel sorry for anyone who finds a bug in a MySQL
version they are running in production.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux