Erik Jones <erik@myemma.com> writes: > Hi, I'm currently doctoring a situation wherein we've got table > inheritance scheme that over the years that has ballooned like only > in your nightmares (think well over 100K tables + indexes on those). > The obvious solution is to re-design the schema with a better > partitioning scheme in mind (see another msg from me later today on > that) but that's a big project that's just getting underway and an > immediate concern is the I/O on out data partition due in large part > to the stats file(s) getting hammered. Which PG version? Early 8.2.x releases had a nasty bug that caused excessive stats file writes. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

Erik Jones <erik@myemma.com> writes: > 8.2.5 on Solaris 10. Before we upgraded to 8.2.4 it was doing about > 65 Mbs/sec. Interestingly, a while back we were running with the > data directory mounted with forcedirectio and saw none of this, I'm > guessing that fsync calls would have something to do with that? Hmm ... no, because the stats file never gets fsync'd. I should think that forcedirectio would have made things worse. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend

Erik Jones <erik@myemma.com> writes: > For now, is renaming the > #define'd paths for the stats file and temp file sufficient for > moving them? I would think so, but haven't tried it. There definitely shouldn't be anything outside pgstat.c that's touching them. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > But then I thought, why do we need it to be a file > at all? Why not use a mmap'ed memory area or something like that, and > only write it to a file on postmaster shutdown? Yeah, we definitely need some other technology for this. The difficulty is in dealing with a highly variably sized chunk of data --- our existing shmem approach won't work well, and once you get away from that the old portability question raises its head. There's also a synchronization issue: how can the stats collector make updates appear atomic? mmap by itself doesn't solve that AFAIK. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq