Get best of the week

What happens to the popularity of MySQL and PostgreSQL? Mitap discussion

On April 24th, we hosted the MySQL @ Scale online mitap on MySQL scalability issues. Speakers from Avito, Badoo and ECOMMPAY participated: Andrey Aksenov (author of Sphinx, leader of the search infrastructure), Evgeny Kuzovlev (CIO ECOMMPAY), Vladimir Fedorkov (MySQL expert / DBA in ECOMMPAY) and Nikolai Korolev (MySQL expert / DBA in Badoo).

Mitap came out long, so we decided to publish it in parts, and start from the end - with a very interesting discussion in our opinion about the popularity of MySQL and PostgreSQL, the reasons for the popularity of PostgreSQL, ORM, impedance mismatch, fractal indices, anger, denial, bargaining and setting up auto-vacuum and other problems of choosing a DBMS by guestbook developers on NodeJS. Attention! There is not very censored vocabulary, a number of incorrect generalizations have been replaced, and any coincidences are random and in no way are offensive in nature.

Alexey Rybak : Take MySQL vs PostgreSQL, not so much just a holivar, but a quite measurable and visible thing. There is analytics, I looked not only at one indicator. Now I remember two things in my head. We have a Facebook group about managing and developing large projects. There are several thousand people. I did a poll on PostgreSQL or MySQL, including who is cloud, non-cloud uses, because this is also such a new topic, very hot in the last few years.

And it turned out that Postgres is (slightly) ahead of MySQL, and for me it was basically some kind of new story, because it seemed that the proportion should be different. And there are polls that are conducted at HighLoad conferences, they were published. I don’t have a straightforward systematic comparison year to year, but it clearly showed that Postgres at some point on the number of answers to the question “what is your main database” caught up and overtook MySQL.

I had a conversation with Petya Zaitsev. Quite a long time ago, maybe two years ago, he came to Moscow. This is basically all Petya’s thoughts. Thoughts are such that, firstly, a lot has been done to introduce various kinds of management solutions or operators in cloud history. And the initial gap between the choice of MySQL or Postgres, it seemed to be leveled, in the sense that if everything was simpler with MySQL, it took and it worked, then Postgres had to have a lot of dances with tambourines first so that this thing would heal . And in the cloud environment you have something: clicked a button - everything appeared. That is, on the one hand, this gap was removed. Then a certain general perception in terms of licenses, proprietary, not proprietary, blah blah blah, also affected. Roughly speaking, if you simplify it completely, if I do not care to do it with one button, I will choose what is more free, well,or they say that it’s free. In fact, it is not clear, but nonetheless. What is packaged in an ideologically more correct box. This is one story.

And the second story is that it’s specifically for Russia, since I rely on the data, after all, not world, but Russian, maybe in the world the story is a little different. There was such a story that at some point the post-congressors united quite strongly, and began to PR very much. And they created working groups and conferences, and for several years there has been a company that actually makes the Russian enterprise solution.

First of all, what do you think, in Russia, in the world, is this rebalancing between MySQL and Postgres, and if so, what are its reasons, how much of its reasons that I have indicated are rational, reasonable, maybe there are others causes?

Evgeny Kuzovlev: To be honest, I never thought that the cloud seemed to smooth out all the differences, probably because I have everything on-premise. For me, clouds are things that, say there, something on the Amazon, let's quickly populate it, then it's clouds for me. Therefore, from this side I somehow never considered. But here, indeed, complexity is reduced.

Earlier, conditionally, about eight years ago, in order for Postgres to work just for you, it was necessary to dance so much with a tambourine that right now mother don’t grieve. And you put MySQL out of the box and it worked for you. Maybe not eight, maybe 10-12 years ago. Indeed, this complexity in Postgres has been greatly reduced, and the point of such entry is operational and development, it was compared with MySQL in some way.

But I, for example, for ourselves, we solved such a problem, I tried to approach, and I try to approach as distantly as possible. We needed, when we started DWH, to select the engine for the denormalized table. At the same time, there is no analytics there, analytics there is at a slightly different level. It’s just pure storage. A revolutionary DBMS was needed to provide, respectively, an analysis of data comparison.

And we approached this question MySQL or Postgres, and despite the fact that the operational resources are quite highly qualified, we chose. And I, for example, got the impression that Postgres, he is, you know, academic. As written in the textbook, so it will work. And MySQL, it’s somehow quite light and with a bunch of hacks. But at the same time, these hacks work in 99% of cases. And no matter how many Postgres guys give synthetic benchmarks, in real situations, which cover 99% of the real use of databases, MySQL wins over ordinary users.

And the plus, really, damn it, it's just a huge plus of MySQL, that it does not have auto-vacuum. Because setting up this thing in Postgres, not everyone can. And as soon as users ...

Vladimir Fedorkov: Sounds meanly (in relation to engineers - approx. AR ).

Evgeny Kuzovlev: And as soon as users run into the auto-vacuum, they begin to experience the wildest problems. That is, just like that, to get a 60 percent degradation of the system, that’s for yourself, this is ...

Alexei Rybak: No, I just went into the trump card.

Evgeny Kuzovlev: Well, I'm sorry. Immediately laid out as if on a table. However, I know, of course, we have Postgres. We have a lot of it. We have problems with the auto-vacuum, thank God, no. But in my career I went through all these cases - anger, denial, bargaining, and tuning auto-vacuum. But this is such a painful sensation, I will tell you. MySQL doesn’t cause so much pain.

At the same time, I am wildly furious because in MySQL I don’t have the opportunity ... I want, but, here I have a B-tree index and that’s it. And even though you crack! I don’t know, I can’t just build ...

Alexey Rybak: There are hash indexes.

Evgeny Kuzovlev: Yes. But, let's compare the number of indexes that we have in MySQL and the number of indexes that we have in Postgres. This is incomparable. At the same time, the same can be said about table engines. At MySQL we have a table engine, you want, I don’t know, you’ll go, take some TokuDB, and you will enjoy in one percent of cases and in 99% of cases you don’t understand why you took it ...

Andrey Aksyonov: smear with fractals.

Alexey Rybak:Well, wait, with the engines, this is also a hoax. Previously, when it was invented, it seemed like a cool thing. But in fact, nothing really took root.

Andrey Aksyonov: Why didn’t it take root? InnoDB has taken root.

Alexey Rybak: No, I mean, besides InnoDB. That is, InnoDB has become just a standard. And all the other stories with column pieces, with some more specific pieces, like Toku. You're right, I don’t even know where this percentage is. That is, the engines turned out to be such a dubious thing.

Evgeny Kuzovlev: You know, human psychology, it is such that sometimes the very possibility of choice is more important than the choice itself. MySQL gives it.

Alexei Rybak: People do not choose MySQL against Postgres, because there are different engines. I think this is the last ...

Vladimir Fedorkov: Because when people choose MySQL against Postgres, if there is at least someone in the company who is behind Postgres, he will be chosen because it is a religion.

Alexey Rybak: MySQL is also a religion.

Vladimir Fedorkov: No, by no means.

Andrey Aksyonov:Nobody threw another important point, called “contingent has grown.” The requirements have naturally grown in a certain layer. The initial question is why the percentage of Postgres is growing, the percentage of this miserable fake called MySQL is steadily decreasing. And, of course, in the end, In principle, the planet is the same, the dinosaurs will trample and MongoDB will defeat everyone, but it’s in the future, but webscale is still important, because the requirements that the applications make, they change in time - once, and at one point Postgres matured, for its part, and a set of requirements from developers matured on their part.

Alexei Rybak: Listen, I don’t believe it, forgive me. Tell me, let’s specifically ...

Andrey Aksyonov: Come on specifically. Look, 1995 ...

Alexey Rybak:The companies that originally launched webscale products that are designed for the same dudes ... God, got out of my head! Just called ... Now, here is who will defeat everyone ...

Andrey Aksyonov: Mongo will win.

Alexey Rybak: Mongo, yes, yes, yes, sorry, yes. So, Mongo initially had two things. First, the object store, do not think, in short, ORM, just write, save and do not need anything, SQL does not need to know, in short, that's all. This led some dudes, their requirements have not changed, they are simple: yes, but it was possible, SQL could not be taught, wow! How cool! He simply said: save the object, and he left me somewhere, class! This is the first.

Second what? Don’t think about fault tolerance, we initially can do it in several DCs and so on. At first it was marketing bullshit. Then they twisted something, and, in principle, as I understand it, in the latest versions now (it works and) a lot where really Mongo is used in large installations.

I mean, this is a good example when you enter the market, another market, having felt these two possibilities, that, firstly, the people who grew up in the programming paradigm in object languages, in general, this impedance mismatch, This is a fundamental story. It’s easier for them to use tools that say: “just save me,” I’ll do everything for you. And at the same time, out of the box, such things associated with the queue, and something else.
Here I would understand, despite the fact that I say again, you troll, you put it as some kind of hyperbolic, relatively speaking, case, some kind of fiction. Nevertheless, this case, he says exactly about it, that you can go out with such a product, find such a niche, and he will fly up, trample, blow up. I don’t understand what Postgres suggested or how they changed and what requirements Postgres suddenly, according to your logic, became so attractive.

Andrey Aksyonov: I repeat once again. Sprouts on both sides; Postgres on certain parameters, on the one hand, and developers on certain parameters, on the other. Once again, 1995. You are a developer. You sit in B ... ( Atyrau ), earn $ 15, and this is a year. And you write c ***** ( worthless) guestbook in Perl. And then you get up the selection process - which database to choose. Surprisingly, both MySQL and Postgres already exist. And if they are not, then they will appear in 1996. But you, s *** (female of the canine family) , the developer of the guest book on Perl in B ... ( Syzran )! You have a task of this magnitude, you solve them.

The only benchmark you can think of is how many “per circle” requests per second you will suck. And “on the circle” of requests per second at this moment Postgres draws in four and a half times less. Just like that, just because f *** you. After that, you look: well, yes, but in Postgres transactions, and in MySQL 3.23 MyISAM. But to be honest, the transactions for my guestbook are n **** to me (at all)Not needed. Strongly choose MySQL.

Now a little time has passed, 25 years. In principle, you still live in B ... ( Tuymazy ), you are no longer writing in Perl, but in NodeJS, your salary has been indexed, because it’s just dollar inflation, and now it’s 15 dollars, multiplied by inflation, - 45, and this is a month. You have the same problems, plus 100,500 packages in NodeJS. And these 100500 NodeJS packages need to store their entire state somewhere, complex requests about the package manager, and so on.

And then suddenly you have your requirements, which you bite to the database, have grown a little. For example, for some reason you cannot live and do not want to live without transactions. What an amazing coincidence! You still need joins. Here are some strange indexes that begin to think about, and not just an automatic B-tree, such: oh, damn it! But it would be nice to have a geographic index, some R-tree, or, say, God save us, fractal trees.

This has changed the set of requirements by developers, times. And on the other hand, Postgres, which in the years of the ancients naturally simply enchantingly slowed down on simple "circles" ( requests), which were dumb and needed by everyone, he greatly improved over the past short period of 25 years, for his part. And so they met suddenly, and more and more people are surprised to find that, op! You look! But Postgres is not so bad, it turns out! And it’s somewhat better for my requirements. My hypothesis is exactly such that, roughly speaking, at the start of MySQL ...

Alexey Rybak: Listen, but I don’t understand where is better? What is better? I understand that they are equal in something, but what is better, I do not understand.

Andrey Aksyonov: So they are both worse.

Alexey Rybak: Than Mongo?

Andrey Aksyonov:Of course, than, in quotation marks, "Mongo". But this is a topic for a whole separate conversation, and a whole separate conversation thread called "where is the bright future."

Vladimir Fedorkov: There is no bright future. The database is evil. As soon as you select a database, you sign yourself a death sentence. Do not use databases. Write files.

Alexey Rybak: / dev / null! Write to / dev / null, friends, dev / null is web scale.

Andrey Aksyonov: Yes. Well, okay, you can also run into / dev / null in principle on performance, we can.

Alexey Rybak: Okay, friends. Everyone is probably very tired. We have been talking for more than two hours.

Andrey Aksyonov: But really interesting questions have not been raised.

Nikolay Korolev:Just started.

Alexey Rybak: Seriously? Do you want to continue?

Andrey Aksyonov: I haven’t poured whiskey either.

Alexey Rybak: But this is a big question why you didn’t.

Andrei Aksyonov: I didn’t prepare, of course ...

Vladimir Fedorkov: So they said that broadcasting, broadcasting, nothing is impossible, you can’t swear, you can’t plump.

Alexey Rybak: Ah! Friends! Everyone who was watching the broadcast, thank you very much for being with us. We begin the unofficial part, where does it turn off ... Now ... I remove the Live stream. Thank you all very much, thank you to our guests - Vladimir, Andrey, Eugene, Nikolai. Stream off, goodbye, see you later.

(end of recording)

The full record of the mitap can be viewed on youtube:



In the near future, we will also publish other parts on the infrastructure, scaling / fault tolerance patterns, and the state of the MySQL eco-system.

If you like this format, then this Friday there will be another rally dedicated to container infrastructure, there are still places for it . Participants: Evgeny Potapov (CEO of ITSumma), Dmitry Stolyarov (CTO Flant), Denis Remchukov (aka Eric Oldmann, COO argotech.io, ex - RAO UES of Russia), Andrey Fedorovsky (CTO News360.com) and Ivan Kruglov (systems engineer, ex - Booking.com). Let's talk about the tools, problems and prospects of containers and Kubernetes in a modern infrastructure.

We also have the mentioned Facebook group “Management and development of large IT projects” , the @feedmeto channel with interesting publications from corporate (mostly foreign) technology blogs, and the author’s channel @rybakalexey about development management in food companies.

More articles:


All Articles