[PERFORM] Update INSERT RULE while running for Partitioning
I'm using PostgreSQL 8.1.4 in a Hibernate Application and I am attempting to use partitioning via Inherited tables. At first I was going to create a rule per sub-table based on a date range, but found out with multiple rules postgres will only return the affected-row count on the last rule which gives Hibernate problems. So now I'm thinking the way to do it is just have one rule at a time and when I want to start appending data to a new partition, just change the rule on the parent table and also update the constraint on the last table to reflect the date ranges contained so that constraint_exclusion will work. this should perform better also. For instance Starting off with: Parent (Rule on insert instead insert into Child2) Child1 (Constraint date = somedate1) Child2 (Constraint date somedate1) Now I want to create another Partition: Create Table Child3 BEGIN Update Parent Rule( instead insert into Child3) somedate2 = max(date) from Child2 Update Child2 Constraint( date somedate1 AND date = somedate2 ) Set Constraint Child3 (date somedate2) END Which ends up with: Parent (Rule on insert instead insert into Child2) Child1 (Constraint date = somedate1) Child2 (Constraint date somedate1 AND date = somedate2) Child3 (Constraint date somedate2)Anyone else tried this or expect it to work consistently (without stopping db)? Is it possible that there could be a race condition for the insertion and constraints or will the transaction prevent that from occurring? I've done some testing and it seems to work but I could just get lucky so far and not lose any data :) Thanks for any help,Gene
Re: [PERFORM] getting better performance
Hi, Eugeny, Eugeny N Dzhurinsky wrote: Do you add / remove tables a lot? Could be you've got system catalog bloat. Yes, almost each table is dropped and re-created in 3-5 days. If your really recreate the same table, TRUNCATE may be a better solution than dropping and recreation. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] need vacuum after insert/truncate/insert?
Hi, Craig, Craig A. James wrote: If I insert a bunch of rows, then truncate, then insert a bunch more rows, do I need to vacuum? I've been assuming that TRUNCATE TABLE is a brute-force technique that more-or-less tosses the old table and starts fresh so that no vacuum is necessary. Second question: Same scenario as above, but now the table has indexes. Is a reindex needed, or are the indexes they truncated too? AFAIK, both table and indices are cut down nicely. But you will need an ANALYZE after refilling of the table, to have current statistics. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Update INSERT RULE while running for Partitioning
Hi, Gene, Gene wrote: I'm using PostgreSQL 8.1.4 in a Hibernate Application and I am attempting to use partitioning via Inherited tables. At first I was going to create a rule per sub-table based on a date range, but found out with multiple rules postgres will only return the affected-row count on the last rule which gives Hibernate problems. This could be considered a PostgreSQL bug - maybe you should discuss this on the appropriate list (general, hackers)? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] longest prefix match querries
Hi, all.
i'm trying to tune application which makes alots of queries
with semantics(find LONGEST PREFIX MATCH in a string) like:
SELECT cost
FROM tarif
WHERE $1 LIKE prefix
ORDER BY length(prefix) DESC
LIMIT 1
from table like:
CREATE TABLE tarif (
id bigint NOT NULL,
prefix varchar(55) NOT NULL,
cost numeric(x, x) not null
) WITHOUT OIDS;
where $1 is the phone numbers.. for example.
it's common task for voice billing applications.
so, generally i can do it that ways:
WHERE $1 LIKE prefix
WHERE $1 SIMILAR TO prefix
WHERE $1 ~ prefix
WHERE position(prefix in $1) = 0
(
surely i must adopt prefix for matching rules,
e.g. LIKE prefix || '%'
and the best is to create trigger which modifies prefix on
insert/update time
)
BUT! this methods doesn't use indexes!!
this is the main disadvantage.
voip3a=# EXPLAIN ANALYZE SELECT cost FROM tarif WHERE '78123319060' like prefix
ORDER BY length(prefix) LIMIT 1;
QUERY PLAN
--
Limit (cost=3028.90..3028.90 rows=1 width=22) (actual time=162.189..162.192
rows=1 loops=1)
- Sort (cost=3028.90..3030.43 rows=612 width=22) (actual
time=162.181..162.181 rows=1 loops=1)
Sort Key: length((prefix)::text)
- Seq Scan on tarif (cost=0.00..3000.57 rows=612 width=22) (actual
time=4.132..161.715 rows=39 loops=1)
Filter: ('78123319060'::text ~~ (prefix)::text)
Total runtime: 162.340 ms
(6 rows)
voip3a=# SELECT count(*) from tarif;
count
122323
(1 row)
AND there are many more effective algorithms for searching LONGEST PREFIX
MATCH in a string.. like
http://search.cpan.org/~avif/Tree-Trie-1.1/Trie.pm
for example
Is there any ways to improve perfomance?
May be implement indexes using Tire algoritm ?
(if so can you please show me some url's to start...)
Thanks, Sergey
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] Calling a SP from Curosor loop
On 29 Jun 2006 10:00:35 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have SP, which has a cursor iterations. Need to call another SP for every loop iteration of the cursor. The pseudo code is as follows.. i would suggest converting your code to pl/pgsql and reposting. that look awfully like t-sql stored procedure, you may as well be saying, 'gobble de gook bak wakka bakka bak!', got it? :-) (aside: pg/pgsql functions support nested calls, recursion, etc and should provide no problems when properly written). merlin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] suggested RAID controller for FreeBSD 6.1 +
Adaptecs RAID controllers as all underwhelming. The best commodity RAID controllers in terms of performance, size of available BBC, connectivity technologies (all of IDE, SCSI, SATA and FC are supported), etc are made by Areca. Get one of Areca's RAID controllers that hold up to 2 GB of BBC. ARC-11xx are the PCI-X based products. ARC-12xx are the PCI-E based products. Reviews at places like tweakers.net Areca is based in Taiwan, but has European and US offices as well Ron Peacetree -Original Message- From: Kenji Morishige [EMAIL PROTECTED] Sent: Jul 5, 2006 7:46 PM To: pgsql-performance@postgresql.org Cc: [EMAIL PROTECTED] Subject: [PERFORM] suggested RAID controller for FreeBSD 6.1 + PostgreSQL 8.1 I am currently running FreeBSD 4.11 (due to IT requirements for now) and Adaptec's 2200S RAID controller running in RAID5. I was advised in the past that the 2200S is actually a poor performing controller and obviously the RAID5 is less than ideal for databases. I chose to run the controller in RAID5 as the tech I talked to suggested that the 2200S was primarily designed for RAID5 and it would operate the best that way. My server is a dual Xeon 3.06Ghz box running on a motherboard approximately 2-3 years old now. I'd like to know what an ideal RAID controller that would be compatible with FreeBSD 6.1 would be these days. Thanks in advance, Kenji ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Update INSERT RULE while running for Partitioning
On Fri, Jul 07, 2006 at 03:51:38AM -0400, Gene wrote: Starting off with: Parent (Rule on insert instead insert into Child2) Child1 (Constraint date = somedate1) Child2 (Constraint date somedate1) Now I want to create another Partition: Create Table Child3 BEGIN Update Parent Rule( instead insert into Child3) somedate2 = max(date) from Child2 Update Child2 Constraint( date somedate1 AND date = somedate2 ) Set Constraint Child3 (date somedate2) END Be aware that adding a constraint with ALTER TABLE will involve a whole table scan (at least in 8.1.2 or earlier). This is true even if if you have an index such that EXPLAIN SELECT EXISTS (SELECT date somedate1 AND date = somedate2 FROM Child2) claims it will run fast. ALTER TABLE is coded to always do a heap scan for constraint changes. To avoid this, this I've made a minor modification to my local PostgreSQL to give a construct similar to Oracle's NOVALIDATE. I allow ALTER TABLE ... ADD CONSTRAINT ... [CHECK EXISTING | IGNORE EXISTING]. To use this safely without any race conditions I setup my last partition with an explicit end time and possible extend it if needed. E.g. child1 CHECK(ts = '-infinity' and ts t1) child2 CHECK(ts = t1 and ts t2) child3 CHECK(ts = t2 and ts t3) Here doing: ALTER TABLE child3 ADD CONSTRAINT new_cstr CHECK(ts = t2 and ts t4) IGNORE EXISTING; ALTER TABLE child3 DROP CONSTRAINT old_cstr; is safe if t4 = t3. I have a regular cron job that makes sure if CURRENT_TIMESTAMP approaches tn (the highest constraint time) it either makes a new partition (actually, in my case, recycles an old partition) or extends the last partition. My data is such that inserts with a timestamp in the future make no sense. Anyone else tried this or expect it to work consistently (without stopping db)? Note that using ALTER TABLE to add a constraint as well as using DROP TABLE or TRUNCATE to remove/recycle partitions are DDL commands that require exclusive locks. This will block both readers and writers to the table(s) and can also cause readers and writers to now interfere with each other. For example, my work load is a lot of continuous small inserts with some long running queries (reports). MVCC allows these to not block each other at all. However, if my cron job comes along and naively attempts to do DROP TABLE, TRUNCATE, or ALTER TABLE it will block on the long running queries. This in turn will cause new INSERT transactions to queue up behind my waiting exclusive lock and now I effectively have reports blocking inserts. Always think twice about running DDL commands on a live database; especially in an automated fashion. There are methods to alleviate or work around some of the issues of getting an exclusive lock but I haven't found a true solution yet. I'd imagine that implementing true partitioning within the PostgreSQL back-end would solve this. Presumably because it would know that adding a new partition, etc can be done without locking out readers at all and it would use something other than an exclusive lock to do the DDL changes. Is it possible that there could be a race condition for the insertion and constraints or will the transaction prevent that from occurring? The required exclusive locks will prevent race conditions. (If you were to use something like my IGNORE EXISTING you'd need to make sure you manually got an exclusive lock before looking up the maximum value to set as the new constraint.) -- Dave Chapeskie ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] how to tune this query.
On 7/4/06, Luckys [EMAIL PROTECTED] wrote: Hi all, I got this query, I'm having indexes for PropertyId and Dates columns across all the tables, but still it takes ages to get me the result. What indexes would be proposed on this, or I'm helpless? I would suggest posting your table schemas and describe what you want the results to look like. After years of following this list, I regard your query as something of a classic. There simply has to be an easier way of writing it. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Opteron/FreeBSD/PostgreSQL performance poor
Hi Merlin, Thanks for the input. Please see below ... Merlin Moncure wrote: On 7/5/06, andy rost [EMAIL PROTECTED] wrote: fsync = on # turns forced synchronization have you tried turning this off and measuring performance? No, not yet. We're trying a couple of outher avenues before manipulating this parameter. stats_command_string = on I would turn this off unless you absoltely require it. It is expensive for what it does. We've turned this off a) All 4 CPUs are nearly always 0% idle; b) The system load level is nearly always in excess of 20; I am guessing your system is spending all it's time syncing. If so, it's solvable (again, just run fsync=off for a bit and compare). We've reduced the load significantly primarily by moving pg_xlog to its own drive and by increasing the effective cache size. While we still see high load levels, they don't last very long. We're trying improve performance from several angles but are taking it one step at a time. Eventually we'll experiment with fsynch c) the output from vmstat -w 10 looks like: procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr aa0 aa1 in sy cs us sy id 21 0 3 1242976 327936 2766 0 0 0 2264 0 2 2 17397 140332 104846 18 82 1 is that 100k context switches over 10 seconds or one second? that might be something to check out. pg 8.1 is regarded as the solution to any cs problem, though. According to man top, that's 100K per second. I'm interested in your recommendation but am not sure what pg 8.1 references NOTE - small user demands and high system demands d) Running top indicates a significant number or sblock states and occasional smwai states; e) ps auxww | grep postgres doesn't show anything abnormal; f) ESQL applications are very slow. We VACUUM ANALYZE user databases every four hours. We VACUUM template1 every 4 hours. We make a copy of the current WAL every minute. We create a PIT recovery archive daily daily. None of these, individually seem to place much strain on the server. your server should be able to handle this easily. Hopefully I've supplied enough information to start diagnosing the problem. Any ideas, thoughts, suggestions are greatly appreciated ... can you please approximate roughly how many transactions per second your server is handling while you are getting the 20 load condition (and, if possible, broken down into read and write transactions)? Do you have any suggestions on how I might obtain these metrics? merlin Thanks again Merlin ... Andy -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.noaa.gov ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Delete is very slow; PG not using existing index to check foreign keys
I've got a problem where Deletes on a certain table are taking very long (5 sec) (PG 8.1.3, linux). Explain Analyze on the delete shows that two (automatically created) triggers caused by foreign keys are responsible for 99% of the time. * The two tables are large (1.5mm and 400k rows), so sequential scans do take a long time. * I've got indices on these tables, but PG doesn't appear to be using them during the delete. * If I run the same SELECT in psql, it does use the index and responds very quickly. For example, I interrupted the Delete, and it appears that it was executing a select from an FK table: SELECT 1 FROM ONLY public.party_aliases x WHERE owner_party_id = $1 FOR SHARE OF x; OK, that's fine. There's an index on that column: CREATE INDEX party_aliases_owner_party_idx ON party_aliases USING btree (owner_party_id, id); I've run ANALYZE, and that doesn't appear to make any difference. Why would PG use the index when I run the select myself, but do a sequential scan when the same statement is run by the delete trigger? I looked through the mailing lists, but most suggestions appeared to be 1) create an index, or 2) run analyze. Any ideas? Thanks in advance, Kian Wright _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] VACUUM vs. REINDEX
Hi Jeff, We are running ANALYZE with the hourly VACUUMs. Most of the time the VACUUM for this table looks like this: INFO: vacuuming public.event_sums INFO: index event_sums_event_available now contains 35669 row versions in 1524 pages DETAIL: 22736 index row versions were removed. 1171 index pages have been deleted, 1142 are currently reusable. CPU 0.03s/0.04u sec elapsed 0.06 sec. INFO: index event_sums_date_available now contains 35669 row versions in 3260 pages DETAIL: 22736 index row versions were removed. 1106 index pages have been deleted, 1086 are currently reusable. CPU 0.06s/0.14u sec elapsed 0.20 sec. INFO: index event_sums_price_available now contains 35669 row versions in 2399 pages DETAIL: 22736 index row versions were removed. 16 index pages have been deleted, 16 are currently reusable. CPU 0.05s/0.13u sec elapsed 0.17 sec. INFO: event_sums: removed 22736 row versions in 1175 pages DETAIL: CPU 0.03s/0.05u sec elapsed 0.08 sec. INFO: event_sums: found 22736 removable, 35669 nonremovable row versions in 27866 pages DETAIL: 0 dead row versions cannot be removed yet. There were 767199 unused item pointers. 0 pages are entirely empty. CPU 0.49s/0.45u sec elapsed 0.93 sec. Without any increase in table traffic, every few weeks, things start to look like this: INFO: vacuuming public.event_sums INFO: index event_sums_event_available now contains 56121 row versions in 2256 pages DETAIL: 102936 index row versions were removed. 1777 index pages have been deleted, 1635 are currently reusable. CPU 0.03s/0.16u sec elapsed 1.04 sec. INFO: index event_sums_date_available now contains 56121 row versions in 5504 pages DETAIL: 102936 index row versions were removed. 2267 index pages have been deleted, 2202 are currently reusable. CPU 0.15s/0.25u sec elapsed 13.91 sec. INFO: index event_sums_price_available now contains 56121 row versions in 4929 pages DETAIL: 102936 index row versions were removed. 149 index pages have been deleted, 149 are currently reusable. CPU 0.13s/0.33u sec elapsed 0.51 sec. INFO: event_sums: removed 102936 row versions in 3796 pages DETAIL: CPU 0.31s/0.26u sec elapsed 0.92 sec. INFO: event_sums: found 102936 removable, 35972 nonremovable row versions in 170937 pages DETAIL: 8008 dead row versions cannot be removed yet. There were 4840134 unused item pointers. 0 pages are entirely empty. CPU 5.13s/1.68u sec elapsed 209.38 sec. INFO: analyzing public.event_sums INFO: event_sums: 171629 pages, 3000 rows sampled, 7328 estimated total rows There are a few things in the second vacuum results that catch my eye, but I don't have the skill set to diagnose the problem. I do know, however, that a REINDEX followed by a VACUUM FULL seems to make the symptoms go away for a while. And I agree that we should upgrade to an 8.x version of PG, but as with many things in life time, money, and risk conspire against me. -William At 04:18 PM 7/7/2006, you wrote: On Fri, 7 Jul 2006, William Scott Jordan wrote: Hi all! Can anyone explain to me what VACUUM does that REINDEX doesn't? We have a frequently updated table on Postgres 7.4 on FC3 with about 35000 rows which we VACUUM hourly and VACUUM FULL once per day. It seem like the table still slows to a crawl every few weeks. Running a REINDEX by itself or a VACUUM FULL by itself doesn't seem to help, but running a REINDEX followed immediately by a VACUUM FULL seems to solve the problem. I'm trying to decide now if we need to include a daily REINDEX along with our daily VACUUM FULL, and more importantly I'm just curious to know why we should or shouldn't do that. Any information on this subject would be appreciated. William, If you're having to VACUUM FULL that often, then it's likely your FSM settings are too low. What does the last few lines of VACUUM VERBOSE say? Also, are you running ANALYZE with the vacuums or just running VACUUM? You still need to run ANALYZE to update the planner statistics, otherwise things might slowly grind to a halt. Also, you should probably consider setting up autovacuum and upgrading to 8.0 or 8.1 for better performance overall. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] VACUUM vs. REINDEX
On Fri, 7 Jul 2006, William Scott Jordan wrote: Hi Jeff, We are running ANALYZE with the hourly VACUUMs. Most of the time the VACUUM for this table looks like this: INFO: vacuuming public.event_sums INFO: index event_sums_event_available now contains 56121 row versions in 2256 pages DETAIL: 102936 index row versions were removed. 1777 index pages have been deleted, 1635 are currently reusable. CPU 0.03s/0.16u sec elapsed 1.04 sec. INFO: index event_sums_date_available now contains 56121 row versions in 5504 pages DETAIL: 102936 index row versions were removed. 2267 index pages have been deleted, 2202 are currently reusable. CPU 0.15s/0.25u sec elapsed 13.91 sec. INFO: index event_sums_price_available now contains 56121 row versions in 4929 pages DETAIL: 102936 index row versions were removed. 149 index pages have been deleted, 149 are currently reusable. CPU 0.13s/0.33u sec elapsed 0.51 sec. INFO: event_sums: removed 102936 row versions in 3796 pages DETAIL: CPU 0.31s/0.26u sec elapsed 0.92 sec. INFO: event_sums: found 102936 removable, 35972 nonremovable row versions in 170937 pages DETAIL: 8008 dead row versions cannot be removed yet. There were 4840134 unused item pointers. 0 pages are entirely empty. CPU 5.13s/1.68u sec elapsed 209.38 sec. INFO: analyzing public.event_sums INFO: event_sums: 171629 pages, 3000 rows sampled, 7328 estimated total rows Hmmm..I was looking for something that looks like this: INFO: free space map: 109 relations, 204 pages stored; 1792 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 182 kB shared memory. VACUUM Maybe 7.4 doesn't give this? Or maybe you need to run vacuumdb -a -v to get it? There are a few things in the second vacuum results that catch my eye, but I don't have the skill set to diagnose the problem. I do know, however, that a REINDEX followed by a VACUUM FULL seems to make the symptoms go away for a while. And I agree that we should upgrade to an 8.x version of PG, but as with many things in life time, money, and risk conspire against me. You should still be able to use autovacuum, which might make you a little happier. Which 7.4 version are you using? -William At 04:18 PM 7/7/2006, you wrote: On Fri, 7 Jul 2006, William Scott Jordan wrote: Hi all! Can anyone explain to me what VACUUM does that REINDEX doesn't? We have a frequently updated table on Postgres 7.4 on FC3 with about 35000 rows which we VACUUM hourly and VACUUM FULL once per day. It seem like the table still slows to a crawl every few weeks. Running a REINDEX by itself or a VACUUM FULL by itself doesn't seem to help, but running a REINDEX followed immediately by a VACUUM FULL seems to solve the problem. I'm trying to decide now if we need to include a daily REINDEX along with our daily VACUUM FULL, and more importantly I'm just curious to know why we should or shouldn't do that. Any information on this subject would be appreciated. William, If you're having to VACUUM FULL that often, then it's likely your FSM settings are too low. What does the last few lines of VACUUM VERBOSE say? Also, are you running ANALYZE with the vacuums or just running VACUUM? You still need to run ANALYZE to update the planner statistics, otherwise things might slowly grind to a halt. Also, you should probably consider setting up autovacuum and upgrading to 8.0 or 8.1 for better performance overall. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] VACUUM vs. REINDEX
Hi Jeff, Ah, okay. I see what information you were looking for. Doing a VACUUM on the full DB, we get the following results: INFO: free space map: 885 relations, 8315 pages stored; 177632 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. -William At 05:22 PM 7/7/2006, you wrote: On Fri, 7 Jul 2006, William Scott Jordan wrote: Hi Jeff, We are running ANALYZE with the hourly VACUUMs. Most of the time the VACUUM for this table looks like this: INFO: vacuuming public.event_sums INFO: index event_sums_event_available now contains 56121 row versions in 2256 pages DETAIL: 102936 index row versions were removed. 1777 index pages have been deleted, 1635 are currently reusable. CPU 0.03s/0.16u sec elapsed 1.04 sec. INFO: index event_sums_date_available now contains 56121 row versions in 5504 pages DETAIL: 102936 index row versions were removed. 2267 index pages have been deleted, 2202 are currently reusable. CPU 0.15s/0.25u sec elapsed 13.91 sec. INFO: index event_sums_price_available now contains 56121 row versions in 4929 pages DETAIL: 102936 index row versions were removed. 149 index pages have been deleted, 149 are currently reusable. CPU 0.13s/0.33u sec elapsed 0.51 sec. INFO: event_sums: removed 102936 row versions in 3796 pages DETAIL: CPU 0.31s/0.26u sec elapsed 0.92 sec. INFO: event_sums: found 102936 removable, 35972 nonremovable row versions in 170937 pages DETAIL: 8008 dead row versions cannot be removed yet. There were 4840134 unused item pointers. 0 pages are entirely empty. CPU 5.13s/1.68u sec elapsed 209.38 sec. INFO: analyzing public.event_sums INFO: event_sums: 171629 pages, 3000 rows sampled, 7328 estimated total rows Hmmm..I was looking for something that looks like this: INFO: free space map: 109 relations, 204 pages stored; 1792 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 182 kB shared memory. VACUUM Maybe 7.4 doesn't give this? Or maybe you need to run vacuumdb -a -v to get it? There are a few things in the second vacuum results that catch my eye, but I don't have the skill set to diagnose the problem. I do know, however, that a REINDEX followed by a VACUUM FULL seems to make the symptoms go away for a while. And I agree that we should upgrade to an 8.x version of PG, but as with many things in life time, money, and risk conspire against me. You should still be able to use autovacuum, which might make you a little happier. Which 7.4 version are you using? -William At 04:18 PM 7/7/2006, you wrote: On Fri, 7 Jul 2006, William Scott Jordan wrote: Hi all! Can anyone explain to me what VACUUM does that REINDEX doesn't? We have a frequently updated table on Postgres 7.4 on FC3 with about 35000 rows which we VACUUM hourly and VACUUM FULL once per day. It seem like the table still slows to a crawl every few weeks. Running a REINDEX by itself or a VACUUM FULL by itself doesn't seem to help, but running a REINDEX followed immediately by a VACUUM FULL seems to solve the problem. I'm trying to decide now if we need to include a daily REINDEX along with our daily VACUUM FULL, and more importantly I'm just curious to know why we should or shouldn't do that. Any information on this subject would be appreciated. William, If you're having to VACUUM FULL that often, then it's likely your FSM settings are too low. What does the last few lines of VACUUM VERBOSE say? Also, are you running ANALYZE with the vacuums or just running VACUUM? You still need to run ANALYZE to update the planner statistics, otherwise things might slowly grind to a halt. Also, you should probably consider setting up autovacuum and upgrading to 8.0 or 8.1 for better performance overall. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] VACUUM vs. REINDEX
On Friday 07 July 2006 17:48, William Scott Jordan wrote: Hi Jeff, Ah, okay. I see what information you were looking for. Doing a VACUUM on the full DB, we get the following results: INFO: free space map: 885 relations, 8315 pages stored; 177632 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. There is one problem right there. Your max_fsm_pages is not enough, or at least you aren't vacuuming enough. Either increase your max_fsm_pages or vacuum more often. Also, honestly -- upgrade to 8.1 :) Joshua D. Drake -William At 05:22 PM 7/7/2006, you wrote: On Fri, 7 Jul 2006, William Scott Jordan wrote: Hi Jeff, We are running ANALYZE with the hourly VACUUMs. Most of the time the VACUUM for this table looks like this: INFO: vacuuming public.event_sums INFO: index event_sums_event_available now contains 56121 row versions in 2256 pages DETAIL: 102936 index row versions were removed. 1777 index pages have been deleted, 1635 are currently reusable. CPU 0.03s/0.16u sec elapsed 1.04 sec. INFO: index event_sums_date_available now contains 56121 row versions in 5504 pages DETAIL: 102936 index row versions were removed. 2267 index pages have been deleted, 2202 are currently reusable. CPU 0.15s/0.25u sec elapsed 13.91 sec. INFO: index event_sums_price_available now contains 56121 row versions in 4929 pages DETAIL: 102936 index row versions were removed. 149 index pages have been deleted, 149 are currently reusable. CPU 0.13s/0.33u sec elapsed 0.51 sec. INFO: event_sums: removed 102936 row versions in 3796 pages DETAIL: CPU 0.31s/0.26u sec elapsed 0.92 sec. INFO: event_sums: found 102936 removable, 35972 nonremovable row versions in 170937 pages DETAIL: 8008 dead row versions cannot be removed yet. There were 4840134 unused item pointers. 0 pages are entirely empty. CPU 5.13s/1.68u sec elapsed 209.38 sec. INFO: analyzing public.event_sums INFO: event_sums: 171629 pages, 3000 rows sampled, 7328 estimated total rows Hmmm..I was looking for something that looks like this: INFO: free space map: 109 relations, 204 pages stored; 1792 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 182 kB shared memory. VACUUM Maybe 7.4 doesn't give this? Or maybe you need to run vacuumdb -a -v to get it? There are a few things in the second vacuum results that catch my eye, but I don't have the skill set to diagnose the problem. I do know, however, that a REINDEX followed by a VACUUM FULL seems to make the symptoms go away for a while. And I agree that we should upgrade to an 8.x version of PG, but as with many things in life time, money, and risk conspire against me. You should still be able to use autovacuum, which might make you a little happier. Which 7.4 version are you using? -William At 04:18 PM 7/7/2006, you wrote: On Fri, 7 Jul 2006, William Scott Jordan wrote: Hi all! Can anyone explain to me what VACUUM does that REINDEX doesn't? We have a frequently updated table on Postgres 7.4 on FC3 with about 35000 rows which we VACUUM hourly and VACUUM FULL once per day. It seem like the table still slows to a crawl every few weeks. Running a REINDEX by itself or a VACUUM FULL by itself doesn't seem to help, but running a REINDEX followed immediately by a VACUUM FULL seems to solve the problem. I'm trying to decide now if we need to include a daily REINDEX along with our daily VACUUM FULL, and more importantly I'm just curious to know why we should or shouldn't do that. Any information on this subject would be appreciated. William, If you're having to VACUUM FULL that often, then it's likely your FSM settings are too low. What does the last few lines of VACUUM VERBOSE say? Also, are you running ANALYZE with the vacuums or just running VACUUM? You still need to run ANALYZE to update the planner statistics, otherwise things might slowly grind to a halt. Also, you should probably consider setting up autovacuum and upgrading to 8.0 or 8.1 for better performance overall. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)---
Re: [PERFORM] VACUUM vs. REINDEX
On 7/7/06, William Scott Jordan [EMAIL PROTECTED] wrote: Hi Jeff,Ah, okay.I see what information you were looking for.Doing aVACUUM on the full DB, we get the following results:INFO:free space map: 885 relations, 8315 pages stored; 177632 total pages neededDETAIL:Allocated FSM size: 1000 relations + 2 pages = 178 kBshared memory.-WilliamWilliam,You need to increase your fsm settings. The database is telling you it is trying to store 177K+ pages, but you have only provided it with 20K. Since these pages are cheap, I would set your fsm up with at least the following. max_fsm_pages 50max_fsm_relations 5000This should provide PostgreSQL with enough space to work. You still might need to run one more vacuum full once you change the setting so that you can recover the space that was lost due to your fsm begin to small. Keep an eye on these last couple of lines from vacuum and adjust your setting accordingly. It may take a couple of tries to get PostgreSQL happy. Once your fsm is large enough, you should be able to dispense with the vacuum fulls and reindexes and just do normal vacuuming. Also in regards to the vacuum vs reindex. Reindexing is great and gives you nice clean virgin indexes, however, if you do not run an analyze (or vacuum analyze), the database will not have statistics for the new indexes. This will cause the planner to make bad choices. What I used to do before upgrading to 8.1 was run a vacuum full, reindexdb, vacuum analyze every weekend (we were on 7.3.4). This gave me pristine indexes and tables for Monday's start of the week.If you can, look hard at upgrading to 8.1.x as it will fix a lot of the issues you are having with autovacuum (along with a ton of other improvements).HTH,Chris
Re: [PERFORM] VACUUM vs. REINDEX
William, You need to increase your fsm settings. The database is telling you it is trying to store 177K+ pages, but you have only provided it with 20K. Since these pages are cheap, I would set your fsm up with at least the following. max_fsm_pages 50 max_fsm_relations 5000 This should provide PostgreSQL with enough space to work. You still might need to run one more vacuum full once you change the setting so that you can recover the space that was lost due to your fsm begin to small. Yes he will need to run a vacuum full but I actually doubt he needs to increase his max_fsm_pages that much, he just needs to vacuum more. Joshua D. Drake ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
