Re: Recovery/Restore and Roll Forward Question.

On Jun 21, 2007, at 5:16 AM, Bruce McAlister wrote:

> Thats exactly what I think. There is something strange going on. At
> the
> moment I think it is the disk I am writing the data to that is slow,
> possibly due to the fact that it is mounted up as "forcedirectio",
> so as
> not to interfere with the file system cache which we want to have
> mainly
> pg datafiles in, and the RAID controller has this particular logical
> driver configured as write-through, so there is no buffering in-
> between.
> The cpu's and network are not the problem here (2 x Dual Core Opterons
> and Quad Gigabit Ethernet, total cpu usage is around 10%, NIC's are
> pushing around 3Mbit/s over each).
>
> It's not all that big to be honest, the total database size is around
> 11GB and I'm currently recking my head to find out how to improve the
> backup times, and not adversely affect our running instance. I just
> recently tried to use UFS snapshots, but the backing store filled up
> before i could complete a backup of the snapshot. I need to find a way
> to improve the write speed of our destination disk. I have another
> question in this pg group about autovacuum that is not running on
> one of
> our database tables which adds an average of around 2.1GB of bloat to
> the database each day. I've now (today) scheduled a cron job every 10
> minutes to get around this in the meantime. Hopefully that should
> reduce
> the amount of data backed up by 2GB when we get to the bottom of that
> issue (autovacuum)
>

You said in your other thread that your on Solaris 10, right?  We are
as well and just discovered that having stats_block_level set to on
increases write volume a lot and noticed a significant drop when we
turned it off as well a significant drop in wal file traffic.  The
same goes for stats_row_level (wrt write volume at least), but you
need that if you want query information to come through
pg_stat_activity (we left that on).  We just migrated off of a server
wherein forcedirectio actually helped us a lot, but now we're
wondering if that was due to us having forcedirectio on.  We only at
the beginning of a lot of systems migrations and restructuring so now
that we have some new avenues  and room to experiment, I'll try to
post our results in a couple weeks.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com