Re: Best replication solution? - Mailing list pgsql-performance

Hi,

Ok I need to answer some more :)

Le 8 avr. 09 à 20:20, Jeff a écrit :
> To add a table with a pk you edit slon_tools.conf and add something
> along the lines of:
>
> "someset" => {
>     "set_id" => 5,
>     "table_id" => 5,
>     "pkeyedtables" => [ "tacos", "burritos", "gorditas" ]
> }
>
> then you just run
>
> [create tables on slave(s)]
> slonik_create_set someset;
> slonik_subscribe_set 1 2;

  $ londiste.py setup.ini provider add schema.table
  $ londiste.py setup.ini subscriber add schema.table

Note both of those commands are to be run from the same host (often
enough, the slave), if you have more than one slave, issue the second
of them only on the remaining ones.

> there are other handy scripts in there as well for failing over,
> adding tables, merging, etc. that hide a lot of the suck.
> Especially the suck of adding a node and creating the store paths.

There's no set in Londiste, so you just don't manage them. You add
tables to queues (referencing the provider in fact) and the subscriber
is free to subscribe to only a subset of the provider queue's tables.
And any table could participate into more than one queue at any time
too, of course.

> I'm running slony on a rather write intensive system, works fine,
> just make sure you've got beefy IO.  One sucky thing though is if a
> slave is down sl_log can grow very large (I've had it get over 30M
> rows, the slave was only down for hours) and this causes major cpu
> churn while the queries slon issues sift through tons of data.  But,
> to be fair, that'll hurt any replication system.

This could happen in Londiste too, just set pgq_lazy_fetch to a
reasonable value and Londiste will use a cursor to fetch the events,
lowering the load. Events are just tuples in an INSERT only table,
which when not used anymore is TRUNCATEd away. PGQ will use 3 tables
where to store events and will rotate its choice of where to insert
new envents, allowing to use TRUNCATE rather than DELETE. And
PostgreSQL is quite efficient to manage this :)
   http://wiki.postgresql.org/wiki/Londiste_Tutorial#Londiste_is_eating_all_my_CPU_and_lag_is_raising


Oh and some people asked what Londiste with failover and DDL would
look like. Here's what the API being cooked looks like at the moment:
  $ londiste setup.ini execute myddl.script.sql

  $ londiste conf/londiste_db3.ini change-provider --provider=rnode1
  $ londiste conf/londiste_db1.ini switchover --target=rnode2

But I'm not the one who should be unveiling all of this, which is
currently being prepared to reach alpha soon'ish.

Regards,
--
dim