Re: High Disk write and space taken by PostgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Thu, Aug 16, 2012 at 1:30 AM, J Ramesh Kumar <rameshj1977@xxxxxxxxx> wrote:
# To avoid freqent autovacuum
autovacuum_freeze_max_age = 2000000000
vacuum_freeze_min_age = 10000000
vacuum_freeze_table_age = 150000000

In general, I'm no expert, but I've heard, increasing freeze_max_age isn't wise. It's there to be decreased, and the risk is data corruption.

You should check PG's docs to be sure, but I think the default is usually safe and fast enough.

And, if you have updates (anywhere), avoiding autovacuum may not be a good idea either. Autovacuum won't bother you on tables you don't update, so I think you're optimizing prematurely here. If you're worrying about it, just increase its naptime.

You'll most definitely need to vacuum pg's catalog with that many (and regular) schema changes, and autovacuum also takes care of that.

You may also want to set asynchronous_commits, to better match MyISAM's characteristics. Or even, just for benchmarking, fsync=off (I wouldn't do it in production though).

Anyway, seeing the schema of at least one of the biggest growing tables would probably help figuring out why the disk usage growth. Index bloat comes to mind.


On Thu, Aug 16, 2012 at 1:30 AM, J Ramesh Kumar <rameshj1977@xxxxxxxxx> wrote:
What are your indexes?  Is the size in the indexes or the database tables?

The size I mentioned is the total folder size of the data directory. There is no difference in the database schema / index between MySQL and PostgreSQL.

You have a problem right there. Postgres and Mysql are completely different beasts, you *will* need to tailor indices specifically for each of them. You'll find, probably, many indices you needed in MySQL are no longer needed with postgres (because it has a much more sophisticated planner).


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux