Thread: Huge size of Data directory
Hi all, Our postgresql database has been churning along just fine, albeit a little slower than I'd like. However, more surprising to me is that it has (over the past few years) become HUGE. It's currently 606MB. Almost all of this space is sitting in data/base/16556 and data/pg_xlog. The problem I have with this space ... is that I only have about 8MB of data stored in the database (according to pg_dump and pg_dumpall). I'm pasting my postgresql.conf, and the dir listing, to see if you can help me reduce the size. tcpip_socket = true max_connections = 128 port = 5432 shared_buffers = 8192 wal_buffers = 64 vacuum_mem = 32768 wal_files = 16 checkpoint_segments = 3 # in logfile segments (16MB each), min 1 checkpoint_timeout = 300 # in seconds, range 30-3600 syslog = 1 # range 0-2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' Dir listing: 1.7M ./base/1 1.7M ./base/16555 4.0k ./base/16556/pgsql_tmp 238M ./base/16556 2.2M ./base/666607 243M ./base 120k ./global 353M ./pg_xlog 9.5M ./pg_clog 606M . Inside base/16556 is: 1247 16398 16419 16432 16445 16458 16475 16577 76744 1249 16400 16420 16435 16446 16460 16477 16584 76762 1255 16402 16421 16436 16447 16462 16557 16586 76763 1259 16404 16422 16437 16448 16463 16559 16599 76771 16384 16406 16423 16438 16449 16465 16561 16601 pg_internal.init 16386 16408 16424 16439 16452 16466 16562 16614 pgsql_tmp 16388 16410 16425 16440 16453 16468 16563 16616 PG_VERSION 16390 16412 16426 16441 16454 16469 16565 448685 16392 16414 16427 16442 16455 16471 16566 76733 16394 16416 16428 16443 16456 16472 16568 76735 16396 16418 16429 16444 16457 16474 16575 76743 Inside pg_xlog is: -rw------- 1 postgres postgres 16777216 Jan 18 14:09 0000000600000081 -rw------- 1 postgres postgres 16777216 Jan 16 15:41 0000000600000082 -rw------- 1 postgres postgres 16777216 Jan 16 16:22 0000000600000083 -rw------- 1 postgres postgres 16777216 Jan 16 17:23 0000000600000084 -rw------- 1 postgres postgres 16777216 Jan 16 19:05 0000000600000085 -rw------- 1 postgres postgres 16777216 Jan 16 21:28 0000000600000086 -rw------- 1 postgres postgres 16777216 Jan 17 05:53 0000000600000087 -rw------- 1 postgres postgres 16777216 Jan 17 09:27 0000000600000088 -rw------- 1 postgres postgres 16777216 Jan 17 10:41 0000000600000089 -rw------- 1 postgres postgres 16777216 Jan 17 11:26 000000060000008A -rw------- 1 postgres postgres 16777216 Jan 17 12:27 000000060000008B -rw------- 1 postgres postgres 16777216 Jan 17 13:25 000000060000008C -rw------- 1 postgres postgres 16777216 Jan 17 14:19 000000060000008D -rw------- 1 postgres postgres 16777216 Jan 17 16:25 000000060000008E -rw------- 1 postgres postgres 16777216 Jan 17 18:19 000000060000008F -rw------- 1 postgres postgres 16777216 Jan 17 20:59 0000000600000090 -rw------- 1 postgres postgres 16777216 Jan 17 22:01 0000000600000091 -rw------- 1 postgres postgres 16777216 Jan 17 23:54 0000000600000092 -rw------- 1 postgres postgres 16777216 Jan 18 09:42 0000000600000093 -rw------- 1 postgres postgres 16777216 Jan 18 10:48 0000000600000094 -rw------- 1 postgres postgres 16777216 Jan 18 11:40 0000000600000095 -rw------- 1 postgres postgres 16777216 Jan 18 13:29 0000000600000096 Any ideas? --Anthony
"Anthony Presley" <anthony@resolution.com> writes: > However, more surprising to me is that it has (over the past few years) > become HUGE. It's currently 606MB. Almost all of this space is sitting > in data/base/16556 and data/pg_xlog. > The problem I have with this space ... is that I only have about 8MB of > data stored in the database (according to pg_dump and pg_dumpall). You need to read the documentation about routine vacuuming ... http://www.postgresql.org/docs/8.1/static/maintenance.html regards, tom lane
On Wed, Jan 18, 2006 at 04:02:27PM -0600, Anthony Presley wrote: > Our postgresql database has been churning along just fine, albeit a little > slower than I'd like. > > However, more surprising to me is that it has (over the past few years) > become HUGE. It's currently 606MB. Almost all of this space is sitting > in data/base/16556 and data/pg_xlog. > > The problem I have with this space ... is that I only have about 8MB of > data stored in the database (according to pg_dump and pg_dumpall). Have you been vacuuming regularly? What's the output of the following command? SELECT datname, age(datvacuumxid), age(datfrozenxid) FROM pg_database; If you haven't been vacuuming then your tables and indexes are probably bloated, which could also account for the unsatisfactory performance. You can use VACUUM FULL or CLUSTER to recover the wasted space; then be sure to schedule regular vacuums to avoid bloat. See "Routine Database Maintenance Tasks" in the documentation for more information. > wal_files = 16 Uh...what version of PostgreSQL are you running? wal_files was removed in 7.3; if you're running a version older than that then you should certainly upgrade. -- Michael Fuhr
I do routinely vacuum. I vacuum'd it yesterday, and about a week before that, and probably on a 2-3 week basis prior to that. And, I vacuum using "analyze". Running 7.1, I believe. --Anthony > "Anthony Presley" <anthony@resolution.com> writes: >> However, more surprising to me is that it has (over the past few years) >> become HUGE. It's currently 606MB. Almost all of this space is sitting >> in data/base/16556 and data/pg_xlog. > >> The problem I have with this space ... is that I only have about 8MB of >> data stored in the database (according to pg_dump and pg_dumpall). > > You need to read the documentation about routine vacuuming ... > http://www.postgresql.org/docs/8.1/static/maintenance.html > > regards, tom lane >
On Wed, Jan 18, 2006 at 05:07:39PM -0600, Anthony Presley wrote: > I do routinely vacuum. I vacuum'd it yesterday, and about a week before > that, and probably on a 2-3 week basis prior to that. And, I vacuum using > "analyze". Recommended vacuum frequency depends on usage, but most people consider "regularly" to mean daily, and more often than that if they do a lot of updates/deletes. Some people set up a cron job so it happens automatically. > Running 7.1, I believe. "SELECT version()" should say for sure, and if it's really 7.1 then you should upgrade as soon as possible. A lot of bugs have been fixed since then, some involving data loss; a few bloat problems have been fixed since then as well. -- Michael Fuhr