[GENERAL] valid use of wildcard
Hi, Is the following query a valid use of the 'wildcard' in (='2008-10-27%')? [EMAIL PROTECTED] arcsoft]$ psql metadata Password: Welcome to psql 8.1.9, the PostgreSQL interactive terminal. metadata=# SELECT * FROM viewspace.siap AS t WHERE t."startDate"='2008-10-27%' AND t.prop_id LIKE '%' LIMIT 1000; Causes the %CPU to jump and process lingers for over an hour. Processes: 87 total, 3 running, 84 sleeping... 321 threads15:51:49 Load Avg: 0.28, 0.28, 0.24 CPU usage: 11.4% user, 9.1% sys, 79.5% idle SharedLibs: num = 164, resident = 29.5M code, 4.52M data, 7.30M LinkEdit MemRegions: num = 10409, resident = 311M + 13.8M private, 501M shared PhysMem: 750M wired, 125M active, 1.42G inactive, 2.27G used, 1.73G free VM: 13.2G + 97.3M 30039(0) pageins, 0(0) pageouts PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 10637 postgres69.1% 0:17.43 1 952 7.60M- 433M 56.9M- 1.06G 10635 psql 0.0% 0:00.00 11422 256K+ 608K 728K+ 27.2M 10634 top 9.1% 0:03.96 12120 492K 396K 976K 27.0M 10633 bash 0.0% 0:00.00 11416 204K 792K 808K 27.1M 10632 sshd 0.0% 0:00.00 11145 116K 1.58M 516K 30.0M 10628 sshd 0.0% 0:00.09 11846 144K 1.58M 1.47M 30.1M 10562 postgres 0.0% 0:43.65 1 930 1.30M 433M 64.8M 1.05G 10559 psql 0.0% 0:00.03 11423 252K 608K 736K 27.2M I do a 'reindexdb -d metadata' and re-run same query and get a response back quickly: [EMAIL PROTECTED] arcsoft]$ psql metadata Password: Welcome to psql 8.1.9, the PostgreSQL interactive terminal. metadata=# SELECT * FROM viewspace.siap AS t WHERE t."startDate"='2008-10-27%' AND t.prop_id LIKE '%' LIMIT 1000; image_id | reference | fits_extension | object | prop_id | startDate | ra | dec | equinox | numberOfAxes | naxis_length | scale | mimeType | instrument | telesco pe | cprojection | crefpixel | crefvalue | cdmatrix | fileSize | pixflags | bandpass_id | bandpas s_unit | bandpass_lolimit | bandpass_hilimit | exposure | depth | depthErr | seeing | releaseDate | vo_id ---+---++---++-+-- -+---+-+--+--+-+++ ---+-+---+---+--+---+--+-+ ---+--+--+--+-+--+-+-- ---+--- ct1417659 | ct1417659.fits.gz | 1 | object| noao | 2008-10-27 00:00:00 | 14:59:22. 49 | -30:08:17.49 | 2000.0 |2 | unknown | unknown | image/fits | mosaic_2 | ct4m | unknown | unknown | unknown | unknown | 88343772 | unknown | VR Supermacho c6027 | unknown | unknown | unknown | 1.000| unknown | unknown | unknown | 2010-04-27 00:00: 00 | ct1417660 | ct1417660.fits.gz | 1 | unknown | smarts | 2008-10-27 00:00:00 | 18:05:49. 42 | -19:26:22.6 | 2000.0 |2 | unknown | unknown | image/fits | ccd_spec | ct15m | unknown | unknown | unknown | unknown |270250 | unknown | CuSO4 | unknown | unknown | unknown | 0.000| unknown | unknown | unknown | 2010-04-27 00:00: 00 | ct1417661 | ct1417661.fits.gz | 1 | unknown | smarts | 2008-10-27 00:00:00 | 18:06:02. 66 | -19:26:22.8 | 2000.0 |2 | unknown | unknown | image/fits | ccd_spec | ct15m | unknown | unknown | unknown | unknown |269673 | unknown | CuSO4 | unknown Why does reindexdb help? How is WHERE t."startDate"='2008-10-27%' getting interpreted? Thank you. -- irene - Irene BargEmail: [EMAIL PROTECTED] NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] valid use of wildcard
The 'real' problem was the database had not been re-indexed in a long
while (it is a test system). After re-indexing the db, the query below
ran fairly quicky:
metadata=# SELECT * FROM viewspace.siap AS t WHERE
t."startDate"='2008-10-27%' AND t.prop_id LIKE '%' LIMIT 1000;
The 'startDate' is a timestamp. I was just questioning the use of the
'=' operator with '%' instead of LIKE. I would have expected the '=' to
take the '%' as a literal.
Thanks Tom, Klint and Scott. I learned some debugging tips from this post.
--irene
Tom Lane wrote:
Klint Gore <[EMAIL PROTECTED]> writes:
Surprisingly, '2008-10-27%' casts to a date in 8.3.3.
Yeah, the datetime input code is pretty willing to overlook unexpected
punctuation. There are enough odd formats out there that I'm not sure
tightening it up would be a good idea.
regards, tom lane
--
-
Irene BargEmail: [EMAIL PROTECTED]
NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg
950 N. Cherry Ave.Voice: 520-318-8273
Tucson, AZ 85726 USA FAX: 520-318-8360
-
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuumdb -z do a reindex?
I thought 'vacuumdb -z dbname' also reindex is this true? I've had a simple update running for over 4 hours now (see results from pg_top below). The sql is: The database has 1016789 records, vacuumdb -z is ran once a day. I have not ran 'reindexdb' in weeks. The system is a: 2xIntel 4-core Xeon Model E5430 (Harpertown) 2.66GHz, 32GB RAM and 8x145GB SAS drives configured with software RAID10 Your comments are appreciated. --irene last pid: 1185; load avg: 2.17, 2.21, 1.60; up 38+01:36:40 13:52:27 14 processes: 2 running, 12 sleeping CPU states: 14.0% user, 0.0% nice, 10.5% system, 75.4% idle, 0.0% iowait Memory: 11G used, 20G free, 456M buffers, 8724M cached Swap: PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND 28508 postgres 170 93M 38M run 265:53 58.42% 99.08% postgres: postgres metadata 140.252.26.34(34717) UPDATE 31609 postgres 160 91M 36M run 7:05 57.85% 98.09% postgres: system_admin metadata 140.252.26.34(43303) SELECT 25156 postgres 160 102M 46M sleep 7:28 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(40350) idle 25363 postgres 180 93M 37M sleep 5:08 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(35951) idle 31622 postgres 150 95M 38M sleep 1:45 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(51917) idle 31624 postgres 150 95M 38M sleep 0:14 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(53908) idle 28755 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41270) idle 28757 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41272) idle 28756 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41271) idle 28758 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41273) idle 28754 postgres 150 92M 9724K sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41269) idle 25180 postgres 150 91M 7016K sleep 0:00 0.00% 0.00% postgres: postgres metadata 140.252.6.51(33997) idle 25179 postgres 150 91M 6956K sleep 0:00 0.00% 0.00% postgres: postgres metadata 140.252.6.51(47331) idle 1186 postgres 160 90M 4808K sleep 0:00 0.00% 0.00% postgres: arcsoft metadata [local] idle [arcs...@archdbn1 ~]$ date Fri Nov 27 13:53:28 MST 2009 -- ----- Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuumdb -z do a reindex?
Hi Scott, Scott Marlowe wrote: On Fri, Nov 27, 2009 at 2:17 PM, Irene Barg wrote: I've had a simple update running for over 4 hours now (see results from pg_top below). The sql is: Have you looked in pg_locks and pg_stat_activity? Yes, I did look at pg_stat_activity and did not see anything alarming. What would have been indicators of something bad? The runtime was the only alarming thing I saw. The database has 1016789 records, vacuumdb -z is ran once a day. I have not ran 'reindexdb' in weeks. The system is a: 2xIntel 4-core Xeon Model E5430 (Harpertown) 2.66GHz, 32GB RAM and 8x145GB SAS drives configured with software RAID10 So do you have autovacuum disabled? What pg version are you running? Yes. It seems simpler than trying to configure the many options. an 8 drive RAID array is usually pretty fast, unless it's on a bad RAID controller or something. What do "vmstat 10" and "iostat -x 10" say about your io activity? -bash-3.2$ vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 0 0 0 21143944 471304 892801600 0 401 2 1 97 0 0 -bash-3.2$ iostat -x 10 Linux 2.6.18-128.1.10.el5 (archdbn1)11/28/09 avg-cpu: %user %nice %system %iowait %steal %idle 1.910.001.420.000.00 96.67 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.01 5.19 0.03 3.27 2.7167.6921.37 0.025.71 0.12 0.04 sda1 0.00 0.00 0.00 0.00 0.00 0.0011.66 0.001.84 1.27 0.00 sda2 0.01 4.07 0.02 3.21 2.6958.2418.84 0.025.69 0.12 0.04 sda3 0.00 0.00 0.00 0.00 0.01 0.0411.15 0.000.83 0.80 0.00 sda4 0.00 0.00 0.00 0.00 0.00 0.00 2.00 0.008.75 8.75 0.00 sda5 0.00 0.00 0.00 0.00 0.00 0.0115.44 0.000.82 0.70 0.00 sda6 0.00 1.12 0.00 0.05 0.01 9.41 171.06 0.007.70 0.13 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.000.000.010.000.00 99.99 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.60 0.00 1.00 0.0012.8012.80 0.000.00 0.00 0.00 sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda2 0.00 0.60 0.00 1.00 0.0012.8012.80 0.000.00 0.00 0.00 sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 I did a reindexdb today, and it took less than 2 minutes. So I don't think it had anything to do with a bloated db or index. I need some utilities and training to be able to convince myself when a problem is with the 'system' (PostgreSQL+hw+config) vs design of the db. Thanks to all who responded. Cheers, --irene Your comments are appreciated. --irene last pid: 1185; load avg: 2.17, 2.21, 1.60; up 38+01:36:40 13:52:27 14 processes: 2 running, 12 sleeping CPU states: 14.0% user, 0.0% nice, 10.5% system, 75.4% idle, 0.0% iowait Memory: 11G used, 20G free, 456M buffers, 8724M cached Swap: PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND 28508 postgres 170 93M 38M run 265:53 58.42% 99.08% postgres: postgres metadata 140.252.26.34(34717) UPDATE 31609 postgres 160 91M 36M run 7:05 57.85% 98.09% postgres: system_admin metadata 140.252.26.34(43303) SELECT 25156 postgres 160 102M 46M sleep 7:28 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(40350) idle 25363 postgres 180 93M 37M sleep 5:08 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(35951) idle 31622 postgres 150 95M 38M sleep 1:45 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(51917) idle 31624 postgres 150 95M 38M sleep 0:14 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(53908) idle 28755 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41270) idle 28757 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41272) idle 28756 postgres 150 91M 10M sleep 0:02 0.00% 0.00% pos
Re: [GENERAL] vacuumdb -z do a reindex?
Hi Scott, On Sat, Nov 28, 2009 at 3:12 PM, Irene Barg wrote: > Hi Scott, > > Scott Marlowe wrote: >> >> On Fri, Nov 27, 2009 at 2:17 PM, Irene Barg wrote: >>> >>> I've had a simple update running for over 4 hours now (see results from >>> pg_top below). The sql is: >> >> Have you looked in pg_locks and pg_stat_activity? > By the time I saw your last post, the 'update' had finished, but it took 8 hours. I loaded a dump of the same database on our test system, then ran the same 'update' statement. Below is all of the stats plus a few others. The test system is 2xAMD Athlon(tm) 64 X2 Dual Core Processor 5600+, 6GB RAM, 2xSATA drives. # pg_locks # metadata=# select * from pg_locks; locktype| database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted ---+--+--+--+---+---+-+---+--+-+---+--+- transactionid | | | | |841483 | | | | 841483 | 12742 | ExclusiveLock| t relation |21800 |21849 | | | | | | | 841478 | 12753 | RowShareLock | t relation |21800 |22086 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22054 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |21847 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22064 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22088 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22090 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22134 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |21873 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |10328 | | | | | | | 841483 | 12742 | AccessShareLock | t relation |21800 |22092 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22094 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22136 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22062 | | | | | | | 841478 | 12753 | AccessShareLock | t transactionid | | | | |841478 | | | | 841478 | 12753 | ExclusiveLock| t relation |21800 |21851 | | | | | | | 841478 | 12753 | RowExclusiveLock | t relation |21800 |22066 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |21892 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |21892 | | | | | | | 841478 | 12753 | RowExclusiveLock | t relation |21800 |22050 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |21915 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22056 | | | | | | | 841478 | 12753 | RowExclusiveLock | t relation |21800 |21837 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22048 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22135 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |22060 | | | | | | | 841478 | 12753 | AccessShareLock | t relation |21800 |
[GENERAL] psql 8.1.9 autovacuum safe?
Hi, We are running postgresql-8.1.9 and plan to upgrade to 8.2 or even 8.3 but can't just yet. I need to run analyze periodically (like hourly), but before I write a script to loop through the tables in each schema and run analyze, I thought I would try autovacuum. I say one post that said there was a bug with autovacuum in 8.1.x? Is autovacuum under 8.1.9 safe or should I wait until I upgrade? Thanks in advance. -- irene - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql 8.1.9 autovacuum safe?
Hi, I found the answer i needed in the HISTORY file that came with the source as well as the online release notes below. I found the answer to a few other questions as well. I will do more digging before posting in the future. Thank you all. --irene Tom Lane wrote: "Raymond O'Donnell" writes: On 12/04/2009 17:27, Irene Barg wrote: We are running postgresql-8.1.9 and plan to upgrade to 8.2 or even 8.3 but can't just yet. I need to run analyze periodically (like hourly), Well, the current version in that branch is 8.1.17, so you're missing a *lot* of bug fixes. It would be worth your while looking through the release notes for the intervening 8.1.X releases - you may well find the answer to your question there. Like, say, here: http://www.postgresql.org/docs/8.1/static/release-8-1-16.html regards, tom lane -- ----- Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] number of relations reported by vacuumdb -av
Hi, I have a PostgreSQL installation with 8 databases (counting postgres, template0, and template1). I run 'vacuumdb -z' daily on 3 of the largest user databases. The vacuumdb logs show the 'max_fsm_pages' need to be increased with almost each vacuum. So I did a 'vacuumdb -av' on all the db's: INFO: free space map contains 81016 pages in 100 relations DETAIL: A total of 8 page slots are in use (including overhead). 187792 page slots are required to track all free space. Current limits are: 8 page slots, 1000 relations, using 534 KB. NOTICE: number of page slots needed (187792) exceeds max_fsm_pages (8) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 187792. VACUUM I have a couple questions. 1) I can increase 'max_fsm_pages' from 80K to 200K, but why does it keep growing? The main database sees on average 2500-5000 rows inserted per day, and deletes are relatively small (although I don't have stats on deletes). 2) How is '100 relations' getting calculated? If I connect to each one of my 8 db's and do: select count(*) from pg_class; The total number of relations is 1725. So shouldn't I increase 'max_fsm_relations' from 1000 to 1725? Thank you in advance. -- irene - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] number of relations reported by vacuumdb -av
never mindI found the answer in the archives.postgresql.org. The answer is 'yes' I use the sum of relations from all of the databases. So I have reset 'max_fsm_relations' from 1000 to 2000. Irene Barg wrote: Hi, I have a PostgreSQL installation with 8 databases (counting postgres, template0, and template1). I run 'vacuumdb -z' daily on 3 of the largest user databases. The vacuumdb logs show the 'max_fsm_pages' need to be increased with almost each vacuum. So I did a 'vacuumdb -av' on all the db's: INFO: free space map contains 81016 pages in 100 relations DETAIL: A total of 8 page slots are in use (including overhead). 187792 page slots are required to track all free space. Current limits are: 8 page slots, 1000 relations, using 534 KB. NOTICE: number of page slots needed (187792) exceeds max_fsm_pages (8) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 187792. VACUUM I have a couple questions. 1) I can increase 'max_fsm_pages' from 80K to 200K, but why does it keep growing? The main database sees on average 2500-5000 rows inserted per day, and deletes are relatively small (although I don't have stats on deletes). 2) How is '100 relations' getting calculated? If I connect to each one of my 8 db's and do: select count(*) from pg_class; The total number of relations is 1725. So shouldn't I increase 'max_fsm_relations' from 1000 to 1725? Thank you in advance. -- irene - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 ----- -- - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] number of relations reported by vacuumdb -av
System: MacOS XServer, 4GB RAM PostgreSQL-8.1.9: the MCAT database 7.6GB big has 525 relations Hi Martin The current setting is: max_fsm_pages = 20 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 2000# min 100, ~70 bytes each I reset it yesterday and bounced the postmaster, but value needed for 'max_fsm_pages' continues go grow, note result from vacuumdb on Apr 18 15:15 [arcs...@dsan3 data]$ cat /tmp/dovacuumdb-pm.log start vacuumdb -z MCAT 2009-04-18 15:00:00 NOTICE: number of page slots needed (270944) exceeds max_fsm_pages (20) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 270944. VACUUM vacuumdb completed 2009-04-18 15:15:07 The activity on this database is almost exclusively INSERTS averaging between 2500-3500 INSERTS daily. I am vacuuming twice a day at 9AM and again at 3PM, and the number of page_slots needed increase with each vacuum. The postmaster contains two other active databases: JBoss db (mostly message queues) 1.5GB, 208 relations dsmixed 82 MB 214 relations The last vacuumdb log for Jboss also showed max_fsm_pages was exceeded: [arcs...@dsan3 data]$ cat /tmp/dovacuumdb_jboss.log start vacuumdb -z jboss 2009-04-18 11:45:00 NOTICE: number of page slots needed (271856) exceeds max_fsm_pages (20) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 271856. VACUUM vacuumdb completed 2009-04-18 11:45:35 But the vacuumdb log for 'dsmixed' was ok. What type of statistics do I need to collect to set these two parameters to a level I do not have to bounce the postmaster daily? Or is it safe to just double the max_fsm_page value to 50 or possibly 100? --irene Martin Gainty wrote: Good Morning Irene could you verify the requirement to set max_fsm_pages (integer) to 16 times new value of 'max_fsm_relations' Thanks! Martin Gainty __ Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sat, 18 Apr 2009 03:23:49 -0700 From: ib...@noao.edu To: pgsql-general@postgresql.org Subject: Re: [GENERAL] number of relations reported by vacuumdb -av never mindI found the answer in the archives.postgresql.org. The answer is 'yes' I use the sum of relations from all of the databases. So I have reset 'max_fsm_relations' from 1000 to 2000. Irene Barg wrote: Hi, I have a PostgreSQL installation with 8 databases (counting postgres, template0, and template1). I run 'vacuumdb -z' daily on 3 of the largest user databases. The vacuumdb logs show the 'max_fsm_pages' need to be increased with almost each vacuum. So I did a 'vacuumdb -av' on all the db's: INFO: free space map contains 81016 pages in 100 relations DETAIL: A total of 8 page slots are in use (including overhead). 187792 page slots are required to track all free space. Current limits are: 8 page slots, 1000 relations, using 534 KB. NOTICE: number of page slots needed (187792) exceeds max_fsm_pages (8) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 187792. VACUUM I have a couple questions. 1) I can increase 'max_fsm_pages' from 80K to 200K, but why does it keep growing? The main database sees on average 2500-5000 rows inserted per day, and deletes are relatively small (although I don't have stats on deletes). 2) How is '100 relations' getting calculated? If I connect to eac
