Re: young guy wanting (Postgres DBA) ammo - Mailing list pgsql-general

On Friday 02 November 2007 00:03, Kevin Hunter wrote:
> However, I'm not a DBA and only minimally know what's involved in doing
> the job, so I don't have "ammo" to defend (or agree?) with my friend
> when he says that "Postgres requires a DBA and MySQL doesn't so that's
> why they choose the latter."  Basically, I have so far not run across
> the need to mess with any of the tunables.  (I am naive, I know.)
> Clearly, one should generally pick the best tool for the job, so I'm no
> pundit that Postgres is *always* the right answer, but I'd like to be
> informed.  My questions:
>
> - With 8.2, and the almost-out 8.3, what kinds of responsibilities
> should a Postgres DBA expect to have?
>

For smaller workloads, the day to day responsabilities are approaching zero;
just turn on autovacuum and postgresql will take care of itself.  There are
some other responsibilties someone should be cognisent of, for example you'll
probably want to do daily backups (cron job a pg_dump is probably sufficient,
though our need for both pg_dump/pg_dumpall tools does complicate life for
the SA doing DBA style work).  The types of jobs I am talking about would be
things like running an internal bulletin board, powering a cacti instance, as
a backing to a jabber server, maybe running nagios against it, or similar
types of workloads.

> - More in line with the conversation with my friend, what/why is it that
> Postgres needs a DBA while MySQL doesn't?  I highly suspect that the
> assumption that MySQL doesn't need a DBA is incorrect, but that's what
> was posed to me and I couldn't agree or disagree.
>

I think the perception comes from the typical workloads each database has to
deal with.  At OmniTI we run a lot of MySQL servers for small workloads like
the nagios/cacti type stuff, and we also use it for dumb data stores, where
we might dump a bunch of log data into a database every day and then do a
couple selects against it to pull out some data and aggregate it in perl.
Many of these instance still run on mysql 4, and many I have never seen; our
SA team handles them and they need zero administration because the jobs they
do are just not complicated, the data they hold is fairly replaceable, and
the applications that run against them have compensated for a lack of
features in thier design up front.  Now, we also run a couple of Postgres
installations in that capacity as well, and honestly my involvement in those
database is pretty much near zero too. I generally take an initial swing
through their postgresql.conf, making sure autovacuum is on, and then I don't
go back. But, speaking in generalities, MySQL is the defacto choice for those
types of workloads, and has been filling that role for years, so the idea of
a DBA-less DB has perpetuated. Note people also used to make this claim about
BerkelyDB back in the day.

Now, we also run a bunch of those large, TB size instances and 100-1000 tps
workloads, both in PostgreSQL, MySQL, (and Oracle too actually), and trust
me, all of those systems do have a DBA looking at them with frequency (along
with bunches of graphs and custom alerts written to notify when things go
wrong).  The PostgreSQL ones tend to be more complicated, because PostgreSQL
has a mature feature set with views/triggers/procedures and very complex SQL
that our developers take full advantage of. On the MySQL side, the developers
tend to push more of that logic into the application because the tools aren't
as mature in the database. This requires less interaction with a DBA, at the
cost of making applications harder to write and maintain.  Now, if your
talking about small departmental databases, then you probably arn't going to
have to worry about this stuff, but since PostgreSQL's typical usage has
leaned more toward solving larger/more complex problems, the idea that you
would have a DBA standing behind it generally has been true in far more
cases.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL