Search Postgresql Archives

Re: Mirroring existing mysql setup

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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






--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux