4 ms·

I used MySQL ages ago. It was pretty fast out of the box. I never needed to vacuum. You could get up and running with Upsert and connections without trouble. It

I used MySQL ages ago. It was pretty fast out of the box. I never needed to vacuum. You could get up and running with Upsert and connections without trouble. It did feel hacky, but don't discount that it mostly worked for a lot of use cases. It always reminded me of software developed with use cases in mind (vs theory). That's actually a complement (ie, full joins are rare, left joins more common - so MySQL was better at left joins). Going to postgre - much pickier. I found it slower out of box. You couldn't just throw tons of connections at it (ie, connection buildup / teardown felt slower). I had issues initially with quoting and capitalization etc. That was a long time ago. Now I enjoy postgres and haven't touched MySQL, but there is a real history where MySQL was the database you could get going with pretty easily (I was pre-Oracle buyout).

brightball 5y ago
The connection story is the primary reason that PHP + MySQL was a big thing. The PHP model of spinning up on every request depended on fast and lightweight connections to the DB. This was the one area that MySQL really excelled IMO.
- jake_morrison 5y ago
  To some extent, Postgres just follows the path that Oracle did to optimize performance. To see what Postgres needs to do next, just look at what Oracle had to do. Postgres forks a listener process for each connection, which is relatively heavyweight. Oracle used to do that, but implemented a separate multi-threaded listener process for performance reasons. MySQL is multi-threaded from the beginning.
  - DaiPlusPlus 5y ago
    > Postgres forks a listener process for each connection [...] MySQL is multi-threaded from the beginning. I thought on Linux that forking had the same cost, more-or-less, as spawning a new thread?
    - ShroudedNight 5y ago
      Was this generally or specifically when using vfork to call execve? I'm sure there are brilliant tricks but it's hard to imagine copy-on-write being completely free
    - willvarfar 5y ago
      In Linux, the actual cost of the call to create the thread vs the process is negligible (its actually just flags to same function, saying whether to share the address space or COW it). But then the cost of starting to use the process introduces a second speedbump, because once you start exercising it you start COWing things. There's also the cost of task switching. I don't know the details, but I wonder if modern side-channel mitigations in kernels flush a lot of stuff when switching processes that they don't need to do for threads? Postgres uses a lot of shared memory to communicate between the processes, so its really its own implementation of threads. Postgres is how it is because its history and portability from a time before threads. In any case, if you were starting out today you really ought have a few threads that are shared and use async processing and plenty of io_uring. Modern Linux allows massively better mechanical sympathy but all the big database engines are already written and can't shift their paradigms so easily. This is from 2010 about how to massively speed up databases with syscall batching (in this case, MySQL is used in the study): https://www.usenix.org/legacy/events/osdi10/tech/full_papers/Soares.pdf https://www.usenix.org/legacy/events/osdi10/tech/full_papers...
      - funcDropShadow 5y ago
        One advantage of PosgreSQL's design is that you cannot about a query which shell tools. Which has been helpful to me on occasion, although as measure of last resort.
        DaiPlusPlus 5y ago
        > is that you cannot about a query which shell tools. yerw0t m8?
  - whoknowswhat11 5y ago
    That does seem like a somewhat obvious solution to built a relatively simple pooler / more efficient listener in. Anyone working on something like this? Merge with an existing solution?
- whoknowswhat11 5y ago
  This was exactly our my case. I did 10 servers going flat out calling into one (beefy at the time) MySQL. I was amazed at how far you could go with that. Then we had replication at the time built in, and so did read replicas. For free and somewhat hacky software the scaling was solid. When I started playing with postgresql I had no clue why folks liked it. My memory may be bad, but we used UPSERT and replication. On Postgresql at least through 8 you could not upsert or stream replication.

brightball 5y ago

The connection story is the primary reason that PHP + MySQL was a big thing. The PHP model of spinning up on every request depended on fast and lightweight connections to the DB. This was the one area that MySQL really excelled IMO.

jake_morrison 5y ago
To some extent, Postgres just follows the path that Oracle did to optimize performance. To see what Postgres needs to do next, just look at what Oracle had to do. Postgres forks a listener process for each connection, which is relatively heavyweight. Oracle used to do that, but implemented a separate multi-threaded listener process for performance reasons. MySQL is multi-threaded from the beginning.
- DaiPlusPlus 5y ago
  > Postgres forks a listener process for each connection [...] MySQL is multi-threaded from the beginning. I thought on Linux that forking had the same cost, more-or-less, as spawning a new thread?
  - ShroudedNight 5y ago
    Was this generally or specifically when using vfork to call execve? I'm sure there are brilliant tricks but it's hard to imagine copy-on-write being completely free
  - willvarfar 5y ago
    In Linux, the actual cost of the call to create the thread vs the process is negligible (its actually just flags to same function, saying whether to share the address space or COW it). But then the cost of starting to use the process introduces a second speedbump, because once you start exercising it you start COWing things. There's also the cost of task switching. I don't know the details, but I wonder if modern side-channel mitigations in kernels flush a lot of stuff when switching processes that they don't need to do for threads? Postgres uses a lot of shared memory to communicate between the processes, so its really its own implementation of threads. Postgres is how it is because its history and portability from a time before threads. In any case, if you were starting out today you really ought have a few threads that are shared and use async processing and plenty of io_uring. Modern Linux allows massively better mechanical sympathy but all the big database engines are already written and can't shift their paradigms so easily. This is from 2010 about how to massively speed up databases with syscall batching (in this case, MySQL is used in the study): https://www.usenix.org/legacy/events/osdi10/tech/full_papers/Soares.pdf https://www.usenix.org/legacy/events/osdi10/tech/full_papers...
    - funcDropShadow 5y ago
      One advantage of PosgreSQL's design is that you cannot about a query which shell tools. Which has been helpful to me on occasion, although as measure of last resort.
      - DaiPlusPlus 5y ago
        > is that you cannot about a query which shell tools. yerw0t m8?
- whoknowswhat11 5y ago
  That does seem like a somewhat obvious solution to built a relatively simple pooler / more efficient listener in. Anyone working on something like this? Merge with an existing solution?
whoknowswhat11 5y ago
This was exactly our my case. I did 10 servers going flat out calling into one (beefy at the time) MySQL. I was amazed at how far you could go with that. Then we had replication at the time built in, and so did read replicas. For free and somewhat hacky software the scaling was solid. When I started playing with postgresql I had no clue why folks liked it. My memory may be bad, but we used UPSERT and replication. On Postgresql at least through 8 you could not upsert or stream replication.