| |||||||||||||
PostgreSQL, I wishPostgreSQL, I wishPosted Aug 20, 2012 23:15 UTC (Mon) by khim (subscriber, #9252)In reply to: PostgreSQL, I wish by man_ls Parent article: MariaDB: Disappearing test cases or did another part of MySQL just become closed source?
Perhaps the shadow of MySQL is what is keeping PostgreSQL from greater acceptance Nope. It's the same thing which keeps PHP alive: availability on cheap hosting. Now, just why MySQL is available on cheap hosting and PostgreSQL is premium? First of all is of course, speed: on tiny databases and trivial requests MySQL is still faster. Another important capability: upgradeability. MySQL can be upgraded and downgraded in seconds. The latter capability is important for cheap hosters: they can plan for upgrades, but they can not test these upgrades. They really need the ability to migrate clients to new version and back in seconds. You can easily install two versions from MySQL and PostgreSQL side-by-side, but with MySQL you can easily go back and forth (depending on the reaction of clients) while PostgreSQL requires dump/restore cycle. Not acceptable. Now, you may say that for "serious users" this is not an important capability. And I agree. But "serious users" are not born that way. They start from some position. And usually they start from MySQL. And then few years down the road PostgreSQL is fighting with users who knows MySQL, who lives with MySQL and for whom PostgreSQL is technology from Mars. Why will they switch without really serious reasons? It's much easier to attract newbie rather then attract someone who've invested time and money in the competitor technology - and PostgreSQL developers for years refuse to admit that.
(Log in to post comments)
PostgreSQL, I wish Posted Aug 21, 2012 1:27 UTC (Tue) by Cyberax (✭ supporter ✭, #52523) [Link]
>PostgreSQL requires dump/restore cycle. Not acceptable.
Not true. PostgreSQL now has live migration feature - you can migrate between _major_ _releases_ live, without interrupting service at all.
PostgreSQL, I wish Posted Aug 21, 2012 9:46 UTC (Tue) by andresfreund (subscriber, #69562) [Link]
Which features are you referring to? Neither Hot-Standby+Streaming Rep, not pg_upgrade really give you that.
You can stitch something together if you add something like londiste or slony to the mix, but thats rather complicated to get right.
Getting really seamless migration is still very, very hard.
PostgreSQL, I wish Posted Aug 21, 2012 14:46 UTC (Tue) by Cyberax (✭ supporter ✭, #52523) [Link]
You need to set up streaming replication (yes, it works across Postgres versions) slave, upgrade it (in-place upgrades are fast), let it catch up with the master and then switch over to it. The switchover procedure is the most complicated step here.
PostgreSQL, I wish Posted Aug 21, 2012 15:27 UTC (Tue) by andresfreund (subscriber, #69562) [Link]
> You need to set up streaming replication (yes, it works across Postgres versions) slave
Unfortunately no. Streaming Rep doesn't work across versions. Streaming rep requires that the wal version number/magic, the catalog version number and several other identifying factors are the same between both ends.
SR/HS work by replaying the wal from the primary on the standby. Unfortunately its format changed in at least each of the last 5 releases. Even 9.3 - with 9.2 being in beta atm - already has significant changes to the format.
> upgrade it (in-place upgrades are fast), let it catch up with the master
Don't get me wrong: I *really* like postgres. I really like SR/HS. I even like that pg_upgrade exists although the way it works isn't that elegant from my POV.
PostgreSQL, I wish Posted Aug 22, 2012 11:12 UTC (Wed) by robert_s (subscriber, #42402) [Link]
"on tiny databases and trivial requests MySQL is still faster"
Yes, that the conventional wisdom. But you never see any real proof of this against a modern and properly tuned postgres.
PostgreSQL, I wish Posted Aug 22, 2012 18:29 UTC (Wed) by dlang (✭ supporter ✭, #313) [Link]
The problem is that it is non-trivial to properly tune postgres.
What Postgres really needs is a "benchmark this box and set the parameters accordingly" utility.
The Postgres defaults are suitable for a many-years-old system that is going to be running postgres as well as many other things and try to make sure that postgres isn't going to interfere with the other things running on the system.
If Postgres is the reason for the system, the default values are grossly off from anything sane, and there really aren't many good resources to help a newby who's not an experienced DBA figure out what to set them to (even within an order of magnatude)
PostgreSQL, I wish Posted Aug 22, 2012 18:56 UTC (Wed) by raven667 (subscriber, #5198) [Link]
What's your opinion on pgtune fitting this bill? There seems to be only a handful of truly critical postgresql performance tuning knobs with quickly diminishing returns that will be highly workload-specific and not hardware-specific. I wonder if there is any idea in the postgresql community to just have it detect cpu/memory at startup time and automatically adjust the internal buffers unless overridden by the config file, making pgtune unnecessary.
PostgreSQL, I wish Posted Aug 22, 2012 19:31 UTC (Wed) by andresfreund (subscriber, #69562) [Link]
Some of that is happening (wal_buffers is autotuned since 9.1, max_fsm_* doesn't exist anymore since 8.4, default_statistics_target has a sane default since 8.4, 9.3 won't need adjustment of sysv /proc settings for most things), in most of the other situations its either hard to do or it very much depends on the environment. Many pg instances don't run on a dedicated machine. Many database servers serve multiple pg clusters at the same time. Many database servers run in virtualized environments where you get better average performance if you use less memory.
There are other variables where nobody fought enough to get them changed. Other changes are beneficial in a wide range of systems but hurt others badly...
PostgreSQL, I wish Posted Aug 22, 2012 19:40 UTC (Wed) by andresfreund (subscriber, #69562) [Link]
I agree that its way too hard to tune it and that lots of folkloristic knowledge is needed to do so.
Unfortunately the "benchmark & set" idea doesn't really work in real life. Many of the parameters really, really depend on the workload you want to run:
I could go on without a problem for quite some time.
For some of those idea exists to make a setting more generally acceptable, for others not.
PostgreSQL, I wish Posted Aug 22, 2012 19:56 UTC (Wed) by dlang (✭ supporter ✭, #313) [Link]
then possibly the answer isn't a 'benchmark ahead of time" approach, but is instead a "run it for a little while and then ask it for recommendations of things to try"
I am the type of person who tends to appriciate manual knobs to tune things, however when the normal answer to any performance question is "well, you must have things tuned wrong" without there being any way for a newby to know how to tune it, there is a problem.
The fact that MySQL has historically done better for simple queries with the default configuration leads people to be scared of Postgres.
In many ways, this is the same way the Microsoft made it's way into the corporate datacenter. They pitched it as "no expertise needed to run it, just install and go", and for small, simple setups they were close enough to right for people to believe it. The fact that running a large setup on Microsoft products frequently requires more resources, and more experise than running the same userbase on a *nix solution is missed by many people because they got started cheaply and so they assume that the ramp-up is going to be equally hard for competing products.
|
|||||||||||||