Today I had to dump and restore a postgres database. On restore I was wondering how the database had become so big, and took a closer look:
These are standard postgres table files, they are 1g size. But on disk they take quite precisely 1.33g (albeit with slight variations). No compression, no copies.
The same thing is also visible from the zfs statistics:
I was very certain that this was not the case earlier, so I checked the backups, and indeed it was not the case earlier:
I compared the options on pool and filesystems with the backup, they are all identical, nothing changed. I only had recreated the pool and moved it onto an encrypted partition.
Then, while already writing this posting, I figured what more I had changed at that time: I had changed
Code:
ls -l 1387899*
-rw------- 1 postgres postgres 1073741824 Jun 12 20:12 1387899
-rw------- 1 postgres postgres 1073741824 Jun 12 18:52 1387899.1
-rw------- 1 postgres postgres 1073741824 Jun 12 19:14 1387899.2
-rw------- 1 postgres postgres 1073741824 Jun 12 19:29 1387899.3
-rw------- 1 postgres postgres 1073741824 Jun 12 19:47 1387899.4
root@edge:/var/db/postgres/tblspc2/PG_10_201707211/1387448 # du -sk 1387899*
1409034 1387899
1409013 1387899.1
1408970 1387899.2
1408981 1387899.3
1409013 1387899.4
These are standard postgres table files, they are 1g size. But on disk they take quite precisely 1.33g (albeit with slight variations). No compression, no copies.
The same thing is also visible from the zfs statistics:
Code:
gr/pgsql/tblspc2 used 11.9G -
gr/pgsql/tblspc2 referenced 11.9G -
gr/pgsql/tblspc2 compressratio 1.00x -
gr/pgsql/tblspc2 written 11.9G -
gr/pgsql/tblspc2 logicalused 8.93G -
gr/pgsql/tblspc2 logicalreferenced 8.93G -
I was very certain that this was not the case earlier, so I checked the backups, and indeed it was not the case earlier:
Code:
gr/pgsql/tblspc2 used 9.65G -
gr/pgsql/tblspc2 referenced 9.65G -
gr/pgsql/tblspc2 compressratio 1.00x -
gr/pgsql/tblspc2 written 9.65G -
gr/pgsql/tblspc2 logicalused 9.54G -
gr/pgsql/tblspc2 logicalreferenced 9.54G -
I compared the options on pool and filesystems with the backup, they are all identical, nothing changed. I only had recreated the pool and moved it onto an encrypted partition.
Then, while already writing this posting, I figured what more I had changed at that time: I had changed
vfs.zfs.min_auto_ashift
to 12 in order to accomodate for newer disks.