Re: Mirroring existing mysql setup

On Dec 18, 2008, at 11:32 AM, snacktime wrote:

> Where I work we use mysql for a fairly busy website, and I'd like to
> eventually start transitioning to postgres if possible. The largest
> obstacle is the lack of replication as a core feature. I'm well aware
> of the history behind why it's not in core, and I saw a post a while
> back saying it would be in 8.4. I'd like to say I think this is a
> very good idea, and I know a number of shops personally that did not
> go with postgres just for this reason.
>
> So anyways our general setup is that we have one master replicating to
> one slave. We use the slave for generating various leaderboard stats
> for our games. Most of these get generated every 4 hours. If we
> wanted to duplicate this on postgres I'm not sure which replication
> option would work best. Last time I looked at slony you had to edit
> configs for each table you wanted to replicate, and the whole setup
> was more complex then it needed to be. If it's still like that, I
> think we would lose more then we gain by moving to postgres. Once
> setup, the replication needs to be free of daily administration other
> then routine automated tasks. We add new tables/remove old ones
> almost on a daily basis.

You should check out Londiste, part of the Skytools package of
Postgres projects. For simple, master-slave replication it's *loads*
easier to set up and administer than Slony. The only reason I could
see to go with Slony right now is if you need some kind of complex
setup with cascaded replication or what-not. Adding and removing
tables to/from the replication stream is also a cinch in Londiste but
you *do* have to actually do it -- they don't get added automatically
like in MySQL's built-in replication. However, you may want to wait a
few months with your fingers crossed to see if Hot Standy replication
is ready for 8.4 in March.

> Now for one of the main things we don't like about mysql. You can't
> add indexes without locking the whole table, which means you can't go
> back and add indexes later on a production app without shutting down
> the whole system. The effect his has had is that when we add new
> features to our games that would normally require an additional
> column, we have to add a new table since we can't add an index to the
> old table. When you add indexes in postgres, how much of a
> performance hit will the database be taking while adding the index?
> I haven't worked on a postgres installation that's as busy as our
> mysql installation is. We get roughly 3-4 million page views per day,
> with each page view probably averaging 4-6 db queries. Probably 20%
> of these are cached. In addition we have our slave which does far
> fewer, but more complicated queries. Quite a few of our tables will
> gain thousands of rows per day, some tens of thousands. Some of our
> busiest tables have tens of millions of rows. We could start to
> archive some of these.

You can use CREATE INDEX CONCURRENTLY to avoid the table locks.
However, that takes two passes over the data instead of one so there's
a bigger IO hit.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k