On Thu, Jan 2, 2014 at 1:08 PM, Shamshinur <shurale9811 at gmail.com> wrote:

> Very strange, shouldn’t take that long, you may try increase  the value of
>  maintenance_work_mem  in postgres.conf, to speed up COPY
>
> For instance:
>
>  maintenance_work_mem = 1024MB
>
>
>
>
>
> *From:* slony1-general-bounces at lists.slony.info [mailto:
> slony1-general-bounces at lists.slony.info] *On Behalf Of *Tory M Blue
> *Sent:* Thursday, January 02, 2014 2:35 PM
> *To:* slony1-general
> *Subject:* [Slony1-general] 6 hours to replicate single table, 12 hours
> to replicate DB
>
>
>
>
>
> 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.
>
>
>
> The table is
>
>
>
> 2013-12-21 19:17:58 PST CONFIG remoteWorkerThread_1: Begin COPY of table
> "impressions"
>
> 2013-12-21 19:37:03 PST CONFIG remoteWorkerThread_1: 12657163552 bytes
> copied for table ”impressions”
>
> 2013-12-22 01:40:22 PST CONFIG remoteWorkerThread_1: 22944.144 seconds to
> copy table ”impressions” <— 6 hours
>
>
>
> Postgres 9.2.4 slony 2.1.3
>
>
>
> This is a larger table, but because of bloat etc, we need to do ground ups
> to clean it out every so often (Vacuums don't do it).
>
>
>
>
>
> Slony config , pretty much at default  other than sync interval.
>
>
>
> # Check for updates at least this often in milliseconds.
>
> # Range: [10-60000], default 2000
>
> sync_interval=1000
>
> #sync_interval_timeout=10000
>
> # apply every single SYNC by itself.
>
> # Range:  [0,100], default: 6
>
> #sync_group_maxsize=6
>
> #sync_max_rowsize=8192
>
> #sync_max_largemem=5242880
>
>
>
> I either need some advanced settings for when we are doing a rebuild, to
> speed up the process, or I need to do some configurations that stay during
> normal workloads as well. But normal workloads things are replicated and
> keep in sync, it's just the rebuild portion. I would like to see it
> actually stressing my boxen :)
>
>
>
> Thanks
>
> Tory
>


We are getting outside of slon and into postgres and i'll try to keep this
light, here are my postgresql custom config aspects

shared_buffers = 2000MB
max_prepared_transactions = 0
work_mem = 100MB
maintenance_work_mem = 128MB


checkpoint_segments = 75                # in logfile segments, min 1, 16MB
each
checkpoint_timeout = 10min              # range 30s-1h
#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 -
1.0
checkpoint_warning = 3600s

The hardware is being refreshed but right now it's an 8 core, 32GB CentOS
system, being replaced with some pretty big hardware 256GB/32cores many
SSD's. But tuning this now and knowing where I need to tweak when I add the
added capacity would be great.

thanks again
Tory
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20140102/f34ae09a/attachment.htm