On Jan 3, 2014 8:26 PM, "Tory M Blue" <tmblue at gmail.com> wrote:
>
>
>
>
> On Fri, Jan 3, 2014 at 5:03 PM, Scott Marlowe <scott.marlowe at gmail.com>
wrote:
>>
>> On Thu, Jan 2, 2014 at 12:35 PM, Tory M Blue <tmblue at gmail.com> wrote:
>> >
>> > Wondering what settings I need to speed this up. To do a rebuild of a
db it
>> > takes a long time, 6 hours for a singe  table. No I/O issues, no load,
just
>> > slon postgres taking their sweet old time. I would like to use the
resources
>> > available to speed this up.
>>
>> 1: How fast can you scp a file between these two boxes?
>> 2: How far apart are they / is there a lot of latency between them?
>> 3: How fast can you create a good sized file on the slave? (dd
>> if=/etc/zero of=bigfile bs=1000000 count=1000)
>>
>> If they are far apart then make sure your slon daemon is running on
>> the slave not the master. This can make a huge difference.
>>
>> > #sync_max_rowsize=8192
>>
>> Try turning this up to 32768 as well.

Hmm.  Probably only matters if you have big tuples.  E.g. where some rows
can get large, such as where some columns sometimes contain big blobs of
data.  (We have replicated the RT ticket system; if someone attaches a huge
80MB spreadsheet to a ticket, that leads to a ~80MB tuple to replicate.)

If this matters, bumping the max improves streaming, but at the cost of
potentially increasing memory consumption a lot.  If it's an issue, then
WAY better to upgrade to Slony 2.2 where it uses COPY to stream in updates,
and the issue (and the parameter) goes away.

>
> 1) Very quick , Accessing an iscsi volume I can scp  the bigfile across
at roughly 45MB/s, so plenty.
> 2) Again same segment, nothing in the way
> 3) ISCSI volume (ISCSI device is running at roughly 6GB network speed)
>  [user at idb02 tmp]# time dd if=/dev/zero of=bigfile bs=1000000 count=1000
> 1000+0 records in
> 1000+0 records out
> 1000000000 bytes (1.0 GB) copied, 1.52658 s, 655 MB/s
>
> real 0m1.529s
> user 0m0.002s
> sys 0m1.526s
>
> "Slon daemon is running on the slave not the master", ummm I'm running
the slon daemon on all of my DB's (master/Slave, QuerySlave1, QuerySlave2)
>
>  slon $CLS_CLUSTER -f /data/pgsql/slon.conf  "dbname=$ADMCLSDB
user=$SUPERUSER host=$SLAVEHOST" 2>&1 > /data/logs/slon.log &

The point is that the slon that manages each node talks to its oo;;f[; a
lot, so keep the slon near its "own" database.

Best is for slon processes to do their initial connections to localhost, so
they are located on The Very Same Host.

Nearby is probably ok; we've had DB 'app servers,' one at each data centre,
and all the slons for the data centre run on one host.  Arguably slower,
but it means processes and logs are centralized to one place, which is
pretty convenient when managing the cluster.  (No need to ssh to a bunch of
places to start/stop the cluster.)

Bad is if there's a data centre in New York and one in San Francisco, and
the slons are all in NY, so that the ones managing SF nodes have
cross-the-country latency.

Less a big deal in Slony 2.2, where replication uses COPY streaming on
subscribers, but for 2.1 and lower, bad latency is Real Bad; keep slons
close to the nodes they manage :-).

> I'll give the sync_max_rowsize a check as well
>
> Thanks again
>
> Tory(((((((((((8((
>
> _________________________________((_____________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinf((((o/slony1-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20140105/4bedfd8c/attachment.htm