Обсуждение: pg_database_size() and actual disk space usage

Hi folks,

I'm analyizing why our /var/lib/postgresql partition got a quick bump in
usage (from 32GB to 41GB) few days ago, even if, according to data from
monitoring software, database size kept increasing with no bump at all.

So I'm using "SELECT pg_database_size('mydb')" but I can't find much
documentation about what that "size" actually refers to. What I see is
that both the master and the replicas shows the same number (32GB), but
for both of those I see differences in the actual file system usage:

master:
41G     /var/lib/postgresql/9.1/main/base

replica:
37G     /var/lib/postgresql/9.1/main/base/

Does pg_database_size() return size occupied by indexes too? If not, the
difference could be due to index space I guess.

Thanks!

--
Alexander Fortin
http://about.me/alexanderfortin/

On 3/29/12 11:41 AM, Raghavendra wrote:
> Can you also tell what replication is in place ?

Sure thing, it's Postgresql 9.1.2, master + two replicas in streaming
replication hot standby

--
Alexander Fortin
http://about.me/alexanderfortin/

On 03/29/2012 12:41 PM, Alexander Fortin wrote:
> On 3/29/12 11:41 AM, Raghavendra wrote:
>> Can you also tell what replication is in place ?
>
> Sure thing, it's Postgresql 9.1.2, master + two replicas in streaming
> replication hot standby
>

Hi,

which filesystem do you use, and which kernel is running?

regards

On 3/29/12 3:24 PM, Bernhard Schrader wrote:
> Hi,
>
> which filesystem do you use, and which kernel is running?

Hi Bernhard,

it's XFS on Ubuntu server 11.10, Kernel is 3.0.0-16-virtual (m1.large
Ec2 instances)

--
Alexander Fortin
http://about.me/alexanderfortin/

On 03/30/2012 09:20 AM, Alexander Fortin wrote:
> On 3/29/12 3:24 PM, Bernhard Schrader wrote:
>> Hi,
>>
>> which filesystem do you use, and which kernel is running?
>
> Hi Bernhard,
>
> it's XFS on Ubuntu server 11.10, Kernel is 3.0.0-16-virtual (m1.large
> Ec2 instances)
>

Ok, well with Kernel 2.6.38 xfs introduced a new "feature" (i think it
was dynamic preallocation) which caused some strange behaviour for me
also, i discussed it on xfs mailinglist, maybe this gives you some hints
of solving your different diskusages.

http://old.nabble.com/Problems-with-filesizes-on-different-Kernels-td33342207.html#a33342207

At the end was the solution for me, but you can first check what i
posted in the first message - testing base directory with du -sh and du
--apparent-size -h

i hope this will help you.

regards

On 3/30/12 12:14 PM, Bernhard Schrader wrote:
> i hope this will help you.

Thanks Bernhard, I'll check the thread right now. In the meanwhile I've
noticed this strange behavior too and I was already going to post it,
maybe it's related to what you're suggesting:

# df -h /var/lib/postgresql
Filesystem            Size  Used Avail Use% Mounted on
/dev/md_d0            128G   44G   85G  35% /var/lib/postgresql

# /etc/init.d/postgresql stop
 * Stopping PostgreSQL 9.1 database server
   ...done.

# df -h /var/lib/postgresql
Filesystem            Size  Used Avail Use% Mounted on
/dev/md_d0            128G   36G   93G  28% /var/lib/postgresql


so, with Postgres shut down, numbers get back to make sense to me.

--
Alexander Fortin
http://about.me/alexanderfortin/

On 3/30/12 12:39 PM, Vladimir Rusinov wrote:
> Are you rotating postgresql log files? If so, how do you do that?
>
> Do you have archive_command set in postgresql.conf?

Hi Vladimir.

Yes, I'm using the packaged Postgres 9.1.2 version shipped with Ubuntu
11.10. From my understanding, it's logrotate that does the job, given
that the package postgresql-common creates an entry for daily
/var/log/postgresql/*.log rotation.

As for the archive_command, no, it's defaulted to empty:
#archive_command = ''

--
Alexander Fortin
http://about.me/alexanderfortin/

On 3/30/12 12:14 PM, Bernhard Schrader wrote:
>
> Ok, well with Kernel 2.6.38 xfs introduced a new "feature" (i think it
> was dynamic preallocation) which caused some strange behaviour for me
> also, i discussed it on xfs mailinglist, maybe this gives you some hints
> of solving your different diskusages.
>
> http://old.nabble.com/Problems-with-filesizes-on-different-Kernels-td33342207.html#a33342207
>
>
> At the end was the solution for me, but you can first check what i
> posted in the first message - testing base directory with du -sh and du
> --apparent-size -h
>
> i hope this will help you.

Hi Bernhard,
just to say thank you to have pointed this out, it actually seems to be
related to this "dynamic speculative EOF preallocation" feature of XFS.

For the records, I've done a couple of checks and I can see clearly that
it's definitely unrelated to Postgres (apparently seems that I can
reproduce the change of data occupation on the XFS file system just
accessing those files).

Talk soon,

--
Alexander Fortin
http://about.me/alexanderfortin/