Обсуждение: 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 Thu, Mar 29, 2012 at 3:06 PM, Alexander Fortin <alexander.fortin@gmail.com> wrote:
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!
Yes, pg_database_size include indexes in the database.
Can you also tell what replication is in place ?
---
--
Alexander Fortin
http://about.me/alexanderfortin/
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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 Fri, Mar 30, 2012 at 2:34 PM, Alexander Fortin <alexander.fortin@gmail.com> wrote:
On 3/30/12 12:14 PM, Bernhard Schrader wrote:
# df -h /var/lib/postgresqlFilesystem 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.
Are you rotating postgresql log files? If so, how do you do that?
Do you have archive_command set in postgresql.conf?
Vladimir Rusinov
http://greenmice.info/
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/