[PERFORM] database performance and query performance question
Our database has slowed right down. We are not getting any performance from our biggest table forecastelement. The table has 93,218,671 records in it and climbing. The index is on 4 columns, origianlly it was on 3. I added another to see if it improve performance. It did not. Should there be less columns in the index? How can we improve database performance? How should I improve my query? PWFPM_DEV=# \d forecastelement Table public.forecastelement Column |Type | Modifiers +-+--- version| character varying(99) | not null origin | character varying(10) | not null timezone | character varying(99) | not null region_id | character varying(20) | not null wx_element | character varying(99) | not null value | character varying(99) | not null flag | character(3)| not null units | character varying(99) | not null valid_time | timestamp without time zone | not null issue_time | timestamp without time zone | not null next_forecast | timestamp without time zone | not null reception_time | timestamp without time zone | not null Indexes: forecastelement_vrwi_idx btree (valid_time,region_id.wx_element.issue_time) explain analyze select DISTINCT ON (valid_time) to_char(valid_time,'MMDDHH24MISS') as valid_time,value from (select valid_time,value,time(valid_time) as hour,reception_time, issue_time from forecastelement where valid_time between '2002-09-02 04:00:00' and '2002-09-07 03:59:59' and region_id = 'PU-REG-WTO-00200' and wx_element = 'TEMP_VALEUR1' and issue_time between '2002-09-02 05:00:00' and '2002-09-06 05:00:00' and origin = 'REGIONAL'and time(issue_time) = '05:00:00' order by issue_time,reception_time DESC,valid_time) as foo where (date(valid_time) = date(issue_time)+1 -1 or date(valid_time) = date(issue_time)+1 or (valid_time between '2002-09-07 00:00:00' and '2002-09-07 03:59:59' and issue_time = '2002-09-06 05:00:00')) order by valid_time ,issue_time DESC; USING INDEX forecastelement_vrwi_idx btree (valid_time, region_id, wx_element, issue_time) Unique (cost=116.75..116.76 rows=1 width=83) (actual time=9469.088..9470.002 rows=115 loops=1) - Sort (cost=116.75..116.75 rows=1 width=83) (actual time=9469.085..9469.308 rows=194 loops=1) Sort Key: to_char(valid_time, 'MMDDHH24MISS'::text), issue_time - Subquery Scan foo (cost=116.72..116.74 rows=1 width=83) (actual time=9465.979..9467.735 rows=194 loops=1) - Sort (cost=116.72..116.73 rows=1 width=30) (actual time=9440.756..9440.981 rows=194 loops=1) Sort Key: issue_time, reception_time, valid_time - Index Scan using forecastelement_vrwi_idx on forecastelement (cost=0.00..116.71 rows=1 width=30) (actual time=176.510..9439.470 rows=194 loops=1) Index Cond: ((valid_time = '2002-09-02 04:00:00'::timestamp without time zone) AND (valid_time = '2002-09-07 03:59:59'::timestamp without time zone) AND ((region_id)::text = 'PU-REG-WTO-00200'::text) AND ((wx_element)::text = 'TEMP_VALEUR1'::text) AND (issue_time = '2002-09-02 05:00:00'::timestamp without time zone) AND (issue_time = '2002-09-06 05:00:00'::timestamp without time zone)) Filter: (((origin)::text = 'REGIONAL'::text) AND (time(issue_time) = '05:00:00'::time without time zone) AND ((date(valid_time) = ((date(issue_time) + 1) - 1)) OR (date(valid_time) = (date(issue_time) + 1)) OR ((valid_time = '2002-09-07 00:00:00'::timestamp without time zone) AND (valid_time = '2002-09-07 03:59:59'::timestamp without time zone) AND (issue_time = '2002-09-06 05:00:00'::timestamp without time zone Total runtime: 9470.404 ms We are running postgresql-7.4-0.5PGDG.i386.rpm . on a Dell Poweredge 6650. system OS RHAS 3.0 cpu 4 memory 3.6 GB disk 270 GB raid 5 postgresql.conf max_connections = 64 shared_buffers = 4000 vacuum_mem = 32768 effective_cache_size = 312500 random_page_cost = 2 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] database performance and query performance question
Something that I do not understand is why if you use a valid_time = '2004-01-22 00:00:00' the query will use the index but if you do a valid_time '2004-01-22 00:00:00' it does not use the index? PWFPM_DEV=# explain analyze select * from forecastelement where valid_time date '2004-01-23'::date limit 10; QUERY PLAN Limit (cost=0.00..3.82 rows=10 width=129) (actual time=199550.388..199550.783 rows=10 loops=1) - Seq Scan on forecastelement (cost=0.00..2722898.40 rows=7131102 width=129) (actual time=199550.382..199550.757 rows=10 loops=1) Filter: (valid_time '2004-01-23 00:00:00'::timestamp without time zone) Total runtime: 199550.871 ms (4 rows) PWFPM_DEV=# explain analyze select * from forecastelement where valid_time = date '2004-01-23'::date limit 10; QUERY PLAN Limit (cost=0.00..18.76 rows=10 width=129) (actual time=176.141..276.577 rows=10 loops=1) - Index Scan using forecastelement_vrwi_idx on forecastelement (cost=0.00..160770.98 rows=85707 width=129) (actual time=176.133..276.494 rows=10 loops=1) Index Cond: (valid_time = '2004-01-23 00:00:00'::timestamp without time zone) Total runtime: 276.721 ms (4 rows) -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 3:01 PM To: Shea,Dan [CIS]; [EMAIL PROTECTED] Subject: Re: [PERFORM] database performance and query performance question Dan, Should there be less columns in the index? How can we improve database performance? How should I improve my query? Your query plan isn't the problem. It's a good plan, and a reasonably efficient query. Under other circumstances, the SELECT DISTINCT with the to_char could be a performance-killer, but it's not in that result set. Overall, you're taking 9 seconds to scan 93 million records. Is this the time the first time you run the query, or the 2nd and successive times? When did you last run VACUUM ANALYZE on the table? Have you tried increasing the ANALYZE statistics on the index columns to, say, 500? Your disks are RAID 5. How many drives? In RAID5, more drives improves the speed of large scans. And what's your sort_mem setting? You didn't mention it. Why is your effective cache size only 300mb when you have 3 GB of RAM? It's not affecting this query, but it could affect others. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] database performance and query performance question
-Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 3:01 PM To: Shea,Dan [CIS]; [EMAIL PROTECTED] Subject: Re: [PERFORM] database performance and query performance question Dan, Should there be less columns in the index? How can we improve database performance? How should I improve my query? Your query plan isn't the problem. It's a good plan, and a reasonably efficient query. Under other circumstances, the SELECT DISTINCT with the to_char could be a performance-killer, but it's not in that result set. Overall, you're taking 9 seconds to scan 93 million records. Is this the time the first time you run the query, or the 2nd and successive times? This is actually the second time. The first query took more time. Concerning the number of columns for an index, I switched the index to have only one column and tried the same query. It is below. When did you last run VACUUM ANALYZE on the table? Have you tried increasing the ANALYZE statistics on the index columns to, say, 500? It is run nightly. But last night's did not complete. It was taking quite some time and I cancelled it, over 4 hours. I will try increasing the ANALYZE statistics to 500. Your disks are RAID 5. How many drives? In RAID5, more drives improves the speed of large scans. There are 4 drives in this raid 5. We are using lvm with ext3 filesystem. Will be moving the database to a SAN within the next month. And what's your sort_mem setting? You didn't mention it. The sort_mem is the default PWFPM_DEV=# show sort_mem; sort_mem -- 1024 Why is your effective cache size only 300mb when you have 3 GB of RAM? It's not affecting this query, but it could affect others. Oh, I thought I had it set for 2.5 GB of RAM. 312500 * 8k = 2.5 GB QUERY WITH 1 column in index. Unique (cost=717633.28..717633.29 rows=1 width=83) (actual time=62922.399..62923.334 rows=115 loops=1) - Sort (cost=717633.28..717633.29 rows=1 width=83) (actual time=62922.395..62922.615 rows=194 loops=1) Sort Key: to_char(valid_time, 'MMDDHH24MISS'::text), issue_time - Subquery Scan foo (cost=717633.26..717633.27 rows=1 width=83) (actual time=62918.232..62919.989 rows=194 loops=1) - Sort (cost=717633.26..717633.26 rows=1 width=30) (actual time=62902.378..62902.601 rows=194 loops=1) Sort Key: issue_time, reception_time, valid_time - Index Scan using forecastelement_v_idx on forecastelement (cost=0.00..717633.25 rows=1 width=30) (actual time=1454.974..62900.752 rows=194 loops=1) Index Cond: ((valid_time = '2002-09-02 04:00:00'::timestamp without time zone) AND (valid_time = '2002-09-07 03:59:59'::timestamp without time zone)) Filter: (((region_id)::text = 'PU-REG-WTO-00200'::text) AND ((wx_element)::text = 'TEMP_VALEUR1'::text) AND (issue_time = '2002-09-02 05:00:00'::timestamp without time zone) AND (issue_time = '2002-09-06 05:00:00'::timestamp without time zone) AND ((origin)::text = 'REGIONAL'::text) AND (time(issue_time) = '05:00:00'::time without time zone) AND ((date(valid_time) = ((date(issue_time) + 1) - 1)) OR (date(valid_time) = (date(issue_time) + 1)) OR ((valid_time = '2002-09-07 00:00:00'::timestamp without time zone) AND (valid_time = '2002-09-07 03:59:59'::timestamp without time zone) AND (issue_time = '2002-09-06 05:00:00'::timestamp without time zone Total runtime: 62923.723 ms (10 rows) PWFPM_DEV=# expalin analyze 312500 PWFPM_DEV=# explain analyze select DISTINCT ON (valid_time) to_char(valid_time,'MMDDHH24MISS') as valid_time,value from PWFPM_DEV-#(select valid_time,value,time(valid_time) as hour,reception_time, PWFPM_DEV(#issue_time from forecastelement where PWFPM_DEV(#valid_time between '2002-09-02 04:00:00' and PWFPM_DEV(#'2002-09-07 03:59:59' and region_id = 'PU-REG-WTO-00200' PWFPM_DEV(#and wx_element = 'TEMP_VALEUR1' and issue_time between PWFPM_DEV(#'2002-09-02 05:00:00' and '2002-09-06 05:00:00' PWFPM_DEV(#and origin = 'REGIONAL'and time(issue_time) = '05:00:00' PWFPM_DEV(#order by issue_time,reception_time DESC,valid_time) as foo where PWFPM_DEV-#(date(valid_time) = date(issue_time)+1 -1 or date(valid_time) = date(issue_time)+1 or PWFPM_DEV(#(valid_time between '2002-09-07 00:00:00' and '2002-09-07 03:59:59' PWFPM_DEV(#and issue_time = '2002-09-06 05:00:00')) order by valid_time ,issue_time DESC; QUERY PLAN
Re: [PERFORM] database performance and query performance question
This sure speed up the query, it is fast. PWFPM_DEV=# explain analyze select * from forecastelement where valid_time between '2004-01-12'::date and '2003-01-12'::date; QUERY PLAN --- Index Scan using forecastelement_v_idx on forecastelement (cost=0.00..159607.11 rows=466094 width=129) (actual time=49.504..49.504 rows=0 loops=1) Index Cond: ((valid_time = '2004-01-12 00:00:00'::timestamp without time zone) AND (valid_time = '2003-01-12 00:00:00'::timestamp without time zone)) Total runtime: 49.589 ms (3 rows) -Original Message- From: Hannu Krosing [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 3:54 PM To: Shea,Dan [CIS] Cc: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: Re: [PERFORM] database performance and query performance question Hannu Krosing kirjutas N, 22.01.2004 kell 22:46: Shea,Dan [CIS] kirjutas N, 22.01.2004 kell 22:35: Something that I do not understand is why if you use a valid_time = '2004-01-22 00:00:00' the query will use the index but if you do a valid_time '2004-01-22 00:00:00' it does not use the index? It probably can't tell if is selective enough to justify using index. Together with limit 10 it may be. You could try explain analyze select * from forecastelement where valid_time between '2004-01-22'::date and '2004-01-22'::date limit 10; Sorry, that should have been: between '2004-01-22'::date and '2004-01-23'::date to see if this is considered good enough. -- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] database performance and query performance question
The end date in the previous example was actually invalid between '2004-01-12'::date and '2003-01-12'::date; There have been multiple inserts since I recreated the index but it took quite some time to complete the following PWFPM_DEV=# explain analyze select * from forecastelement where valid_time between '2004-01-12'::date and '2004-01-13'::date; QUERY PLAN --- Index Scan using forecastelement_v_idx on forecastelement (cost=0.00..832139.81 rows=2523119 width=129) (actual time=0.519..467159.658 rows=2940600 loops=1) Index Cond: ((valid_time = '2004-01-12 00:00:00'::timestamp without time zone) AND (valid_time = '2004-01-13 00:00:00'::timestamp without time zone)) Total runtime: 472627.148 ms (3 rows) -Original Message- From: Shea,Dan [CIS] Sent: Thursday, January 22, 2004 4:10 PM To: 'Hannu Krosing'; Shea,Dan [CIS] Cc: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: [PERFORM] database performance and query performance question This sure speed up the query, it is fast. PWFPM_DEV=# explain analyze select * from forecastelement where valid_time between '2004-01-12'::date and '2003-01-12'::date; QUERY PLAN --- Index Scan using forecastelement_v_idx on forecastelement (cost=0.00..159607.11 rows=466094 width=129) (actual time=49.504..49.504 rows=0 loops=1) Index Cond: ((valid_time = '2004-01-12 00:00:00'::timestamp without time zone) AND (valid_time = '2003-01-12 00:00:00'::timestamp without time zone)) Total runtime: 49.589 ms (3 rows) -Original Message- From: Hannu Krosing [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 3:54 PM To: Shea,Dan [CIS] Cc: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: Re: [PERFORM] database performance and query performance question Hannu Krosing kirjutas N, 22.01.2004 kell 22:46: Shea,Dan [CIS] kirjutas N, 22.01.2004 kell 22:35: Something that I do not understand is why if you use a valid_time = '2004-01-22 00:00:00' the query will use the index but if you do a valid_time '2004-01-22 00:00:00' it does not use the index? It probably can't tell if is selective enough to justify using index. Together with limit 10 it may be. You could try explain analyze select * from forecastelement where valid_time between '2004-01-22'::date and '2004-01-22'::date limit 10; Sorry, that should have been: between '2004-01-22'::date and '2004-01-23'::date to see if this is considered good enough. -- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Deleting certain duplicates
We have a large database which recently increased dramatically due to a change in our insert program allowing all entries. PWFPM_DEV=# select relname,relfilenode,reltuples from pg_class where relname = 'forecastelement'; relname | relfilenode | reltuples -+-+- forecastelement | 361747866 | 4.70567e+08 Column |Type | Modifiers +-+--- version| character varying(99) | origin | character varying(10) | timezone | character varying(99) | region_id | character varying(20) | wx_element | character varying(99) | value | character varying(99) | flag | character(3)| units | character varying(99) | valid_time | timestamp without time zone | issue_time | timestamp without time zone | next_forecast | timestamp without time zone | reception_time | timestamp without time zone | The program is supposed to check to ensure that all fields but the reception_time are unique using a select statement, and if so, insert it. Due an error in a change, reception time was included in the select to check for duplicates. The reception_time is created by a program creating the dat file to insert. Essentially letting all duplicate files to be inserted. I tried the delete query below. PWFPM_DEV=# delete from forecastelement where oid not in (select min(oid) from forecastelement group by version,origin,timezone,region_id,wx_element,value,flag,units,valid_time,iss ue_time,next_forecast); It ran for 3 days creating what I assume is an index in pgsql_tmp of the group by statement. The query ended up failing with dateERROR:write failed. Well the long weekend is over and we do not have the luxury of trying this again. So I was thinking maybe of doing the deletion in chunks, perhaps based on reception time. Are there any suggestions for a better way to do this, or using multiple queries to delete selectively a week at a time based on the reception_time. I would say there are a lot of duplicate entries between mid march to the first week of April. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Deleting certain duplicates
The index is Indexes: forecastelement_rwv_idx btree (region_id, wx_element, valid_time) -Original Message- From: Shea,Dan [CIS] [mailto:[EMAIL PROTECTED] Sent: Monday, April 12, 2004 10:39 AM To: Postgres Performance Subject: [PERFORM] Deleting certain duplicates We have a large database which recently increased dramatically due to a change in our insert program allowing all entries. PWFPM_DEV=# select relname,relfilenode,reltuples from pg_class where relname = 'forecastelement'; relname | relfilenode | reltuples -+-+- forecastelement | 361747866 | 4.70567e+08 Column |Type | Modifiers +-+--- version| character varying(99) | origin | character varying(10) | timezone | character varying(99) | region_id | character varying(20) | wx_element | character varying(99) | value | character varying(99) | flag | character(3)| units | character varying(99) | valid_time | timestamp without time zone | issue_time | timestamp without time zone | next_forecast | timestamp without time zone | reception_time | timestamp without time zone | The program is supposed to check to ensure that all fields but the reception_time are unique using a select statement, and if so, insert it. Due an error in a change, reception time was included in the select to check for duplicates. The reception_time is created by a program creating the dat file to insert. Essentially letting all duplicate files to be inserted. I tried the delete query below. PWFPM_DEV=# delete from forecastelement where oid not in (select min(oid) from forecastelement group by version,origin,timezone,region_id,wx_element,value,flag,units,valid_time,iss ue_time,next_forecast); It ran for 3 days creating what I assume is an index in pgsql_tmp of the group by statement. The query ended up failing with dateERROR:write failed. Well the long weekend is over and we do not have the luxury of trying this again. So I was thinking maybe of doing the deletion in chunks, perhaps based on reception time. Are there any suggestions for a better way to do this, or using multiple queries to delete selectively a week at a time based on the reception_time. I would say there are a lot of duplicate entries between mid march to the first week of April. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: 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] [ SOLVED ] select count(*) very slow on an already
Bill, if you had alot of updates and deletions and wanted to optimize your table, can you just issue the cluster command. Will the cluster command rewrite the table without the obsolete data that a vacuum flags or do you need to issue a vacuum first? Dan. -Original Message- From: Bill Moran [mailto:[EMAIL PROTECTED] Sent: Thursday, April 15, 2004 2:49 PM To: Rajesh Kumar Mallah Cc: Postgres Performance Subject: Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah wrote: Bill Moran wrote: Rajesh Kumar Mallah wrote: Hi, The problem was solved by reloading the Table. the query now takes only 3 seconds. But that is not a solution. If dropping/recreating the table improves things, then we can reasonably assume that the table is pretty active with updates/inserts. Correct? Yes the table results from an import process and under goes lots of inserts and updates , but thats before the vacuum full operation. the table is not accessed during vacuum. What i want to know is is there any wat to automate the dumping and reload of a table individually. will the below be safe and effective: The CLUSTER command I described is one way of doing this. It essentially automates the task of copying the table, dropping the old one, and recreating it. If the data gets too fragmented, a vacuum may not be enough. Also, read up on the recommendations _against_ vacuum full (recommending only using vacuum on databases) With full, vacuum condenses the database, which may actually hurt performance. A regular vacuum just fixes things up, and may leave unused space lying around. However, this should apparently achieve a balance between usage and vacuum. See the docs, they are much better at describing this than I am. i understand simultaneous vacuum and usage detoriates performance mostly. but this case is different. Just want to make sure we're on the same page here. I'm not talking about vacuuming simultaneous with anything. I'm simply saying that vacuum full isn't always the best choice. You should probably only be doing vacuum. The reason and details for this are in the admin docs. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already
Just a note, I was trying the cluster command and was short on space. I figured I had enough space for the new table and index. It failed on me twice. The reason is that I noticed for the command to complete, it needed the space of the new table and 2x the space of the new index. It looks like it creates the new table, then a new index. Afterwards it looked like it creates another index in the DB pgsql_tmp. So for me this is an important consideration, since the new index size was about 7GB. I had not anticipated the second index size so that is why it failed. I ended up creating a link of pgsql_tmp to another parttion to successfully complete. Dan. -Original Message- From: Bill Moran [mailto:[EMAIL PROTECTED] Sent: Thursday, April 15, 2004 4:14 PM To: Shea,Dan [CIS] Cc: Postgres Performance Subject: Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already Shea,Dan [CIS] wrote: Bill, if you had alot of updates and deletions and wanted to optimize your table, can you just issue the cluster command. Will the cluster command rewrite the table without the obsolete data that a vacuum flags or do you need to issue a vacuum first? From the reference docs: During the cluster operation, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes. CLUSTER preserves GRANT, inheritance, index, foreign key, and other ancillary information about the table. Because the optimizer records statistics about the ordering of tables, it is advisable to run ANALYZE on the newly clustered table. Otherwise, the optimizer may make poor choices of query plans. The primary reason CLUSTER exists is to allow you to physically reorder a table based on a key. This should provide a performance improvement if data with the same key is accessed all at once. (i.e. if you do SELECT * FROM table WHERE key=5 and it returns 100 rows, those 100 rows are guaranteed to be all on the same part of the disk after CLUSTER, thus a performance improvement should result.) Updates and inserts will add data in the next available space in a table with no regard for any keys, and _may_ require running all over the disk to retrieve the data in the previous example query. I doubt if CLUSTER is an end-all optimization tool. The specific reason I suggested it was because the original poster was asking for an easier way to drop/recreate a table (as prior experimentation had shown this to improve performance) I can't think of anything easier than CLUSTER tablename ON keyname Since CLUSTER recreates the table, it implicitly removes the dead tuples. However, it's going to be a LOT slower than vacuum, so if dead tuples are the main problem, vacuum is still the way to go. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Why will vacuum not end?
No, but data is constantly being inserted by userid scores. It is postgres runnimg the vacuum. Dan. -Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 12:02 AM To: Shea,Dan [CIS] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will vacuum not end? This vacuum is running a marathon. Why will it not end and show me free space map INFO? We have deleted a lot of data and I would like to be confident that these deletions will be used as free space, rather than creating more table files. Does another postgres query running have a lock on that table? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Why will vacuum not end?
PWFPM_DEV=# select * from pg_locks; relation | database | transaction | pid | mode | granted --+--+-+---+--+- 17472 |17347 | | 2618 | ShareUpdateExclusiveLock | t | |10858533 | 28778 | ExclusiveLock| t 17472 |17347 | | 2618 | ShareUpdateExclusiveLock | t | |10803814 | 2618 | ExclusiveLock| t 16759 |17347 | | 28778 | AccessShareLock | t (5 rows) PWFPM_DEV=# 17347 is the database PWFPM_DEV iod, The pids are below [EMAIL PROTECTED] root]# ps -ef |grep 28778|grep -v grep postgres 28778 504 0 18:06 ?00:00:00 postgres: scores PWFPM_DEV [local] idle [EMAIL PROTECTED] root]# ps -ef |grep 2618|grep -v grep postgres 2618 504 8 Apr22 ?02:31:00 postgres: postgres PWFPM_DEV [local] VACUUM [EMAIL PROTECTED] root]# A vacuum is running now. I restarted the database, set vacuum_mem = '196608'; and started a new vacuum. I also stopped inserting into the database. I hoping I will get some results. PWFPM_DEV=# select now();vacuum verbose analyze forecastelement;select now(); now --- 2004-04-22 13:38:02.083592+00 (1 row) INFO: vacuuming public.forecastelement INFO: index forecastelement_rwv_idx now contains 391385895 row versions in 5051132 pages DETAIL: 27962015 index row versions were removed. 771899 index pages have been deleted, 496872 are currently reusable. CPU 4499.54s/385.76u sec elapsed 55780.91 sec. INFO: forecastelement: removed 33554117 row versions in 737471 pages DETAIL: CPU 135.61s/83.99u sec elapsed 1101.26 sec. -Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 9:26 PM To: Shea,Dan [CIS] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will vacuum not end? No, but data is constantly being inserted by userid scores. It is postgres runnimg the vacuum. Dan. Well, inserts create some locks - perhaps that's the problem... Otherwise, check the pg_locks view to see if you can figure it out. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Why will vacuum not end?
Josh, how long should a vacuum take on a 87 GB table with a 39 GB index? I do not think that the verbose option of vacuum is verbose enough. The vacuum keeps redoing the index, but there is no indication as to why it is doing this. I see alot of activity with transaction logs being recycled (15 to 30 every 3 to 20 minutes). Is the vacuum causing this? -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 2:48 PM To: Shea,Dan [CIS]; 'Christopher Kings-Lynne' Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will vacuum not end? Guys, Well, inserts create some locks - perhaps that's the problem... Otherwise, check the pg_locks view to see if you can figure it out. FWIW, I've had this happen a couple of times, too. Unfortunately, it's happend in the middle of the day so that I had to cancel the processes and get the system back to normal in too much of a hurry to consider documenting it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Why will vacuum not end?
Manfred is indicating the reason it is taking so long is due to the number of dead tuples in my index and the vacuum_mem setting. The last delete that I did before starting a vacuum had 219,177,133 deletions. Dan. Dan, Josh, how long should a vacuum take on a 87 GB table with a 39 GB index? Depends: -- What's your disk support? -- VACUUM, VACUUM ANALYZE, or VACUUM FULL? VACUUM ANALYZE -- What's your vacuum_mem setting? set vacuum_mem = '196608' #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync -- What are checkpoint and wal settings? wal_buffers = 64 checkpoint_segments = 30 checkpoint_timeout = 300 I see alot of activity with transaction logs being recycled (15 to 30 every 3 to 20 minutes). Is the vacuum causing this? Probably, yes. How many checkpoint_buffers do you allow? I am not sure what the checkpoint_buffers are, we are running 7.4.0? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Why will vacuum not end?
There were defintely 219,177,133 deletions. The deletions are most likely from the beginning, it was based on the reception_time of the data. I would rather not use re-index, unless it is faster then using vacuum. What do you think would be the best way to get around this? Increase vacuum_mem to a higher amount 1.5 to 2 GB or try a re-index (rather not re-index so that data can be queried without soing a seqscan). Once the index is cleaned up, how does vacuum handle the table? Does it take as long as the index or is it faster? -Original Message- From: Manfred Koizar [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 1:57 PM To: Shea,Dan [CIS] Cc: 'Josh Berkus'; [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will vacuum not end? On Sat, 24 Apr 2004 10:45:40 -0400, Shea,Dan [CIS] [EMAIL PROTECTED] wrote: [...] 87 GB table with a 39 GB index? The vacuum keeps redoing the index, but there is no indication as to why it is doing this. If VACUUM finds a dead tuple, if does not immediately remove index entries pointing to that tuple. It instead collects such tuple ids and later does a bulk delete, i.e. scans the whole index and removes all index items pointing to one of those tuples. The number of tuple ids that can be remembered is controlled by vacuum_mem: it is VacuumMem * 1024 / 6 Whenever this number of dead tuples has been found, VACUUM scans the index (which takes ca. 6 seconds, more than 16 hours), empties the list and continues to scan the heap ... From the number of dead tuples you can estimate how often your index will be scanned. If dead tuples are evenly distributed, expect there to be 15 index scans with your current vacuum_mem setting of 196608. So your VACUUM will run for 11 days :-( OTOH this would mean that there are 500 million dead tuples. Do you think this is possible? Servus Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Why will vacuum not end?
It is set at max_fsm_pages = 150 . We are running a DELL PowerEdge 6650 with 4 CPU's Mem: 3611320k av from top. The database is on a shared device (SAN) raid5, 172 GB. Qlogic Fibre optic cards(desc: QLogic Corp.|QLA2312 Fibre Channel Adapter) connected to the Dell version of an EMC SAN (FC4700 I believe). I have set vacuum_mem = 917504; and started another vacuum verbose on the table in question. Tried to set vacuum_mem to 1114112 and vacuum failed, then tried 917504 and vacuum started. PWFPM_DEV=# set vacuum_mem = '1114112'; SET PWFPM_DEV=# show vacuum_mem; vacuum_mem 1114112 (1 row) PWFPM_DEV=# vacuum verbose forecastelement; INFO: vacuuming public.forecastelement ERROR: invalid memory alloc request size 1140850686 PWFPM_DEV=# set vacuum_mem = 917504; SET PWFPM_DEV=# show vacuum_mem; vacuum_mem 917504 (1 row) PWFPM_DEV=# select now();vacuum verbose forecastelement;select now(); now --- 2004-04-25 01:40:23.367123+00 (1 row) INFO: vacuuming public.forecastelement I performed a query that used a seqscan PWFPM_DEV=# explain analyze select count(*) from forecastelement; QUERY PLAN --- Aggregate (cost=16635987.60..16635987.60 rows=1 width=0) (actual time=1352.844..1352.847 rows=1 loops=1) - Seq Scan on forecastelement (cost=0.00..15403082.88 rows=493161888 width=0) (actual time=243.562..12692714.422 rows=264422681 loops=1) Total runtime: 13111221.978 ms (3 rows) Dan. -Original Message- From: Manfred Koizar [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 8:29 PM To: Shea,Dan [CIS] Cc: 'Josh Berkus'; [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will vacuum not end? On Sat, 24 Apr 2004 15:58:08 -0400, Shea,Dan [CIS] [EMAIL PROTECTED] wrote: There were defintely 219,177,133 deletions. The deletions are most likely from the beginning, it was based on the reception_time of the data. I would rather not use re-index, unless it is faster then using vacuum. I don't know whether it would be faster. But if you decide to reindex, make sure sort_mem is *huge*! What do you think would be the best way to get around this? Increase vacuum_mem to a higher amount 1.5 to 2 GB or try a re-index (rather not re-index so that data can be queried without soing a seqscan). Just out of curiosity: What kind of machine is this running on? And how long does a seq scan take? Once the index is cleaned up, how does vacuum handle the table? If you are lucky VACUUM frees half the index pages. And if we assume that the most time spent scanning an index goes into random page accesses, future VACUUMs will take only 3 seconds per index scan. Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] after using pg_resetxlog, db lost
The pg_resetxlog was run as root. It caused ownership problems of pg_control and xlog files. Now we have no access to the data now through psql. The data is still there under /var/lib/pgsql/data/base/17347 (PWFPM_DEV DB name). But there is no reference to 36 of our tables in pg_class. Also the 18 other tables that are reported in this database have no data in them. Is there anyway to have the database resync or make it aware of the data under /var/lib/pgsql/data/base/17347? How can this problem be resolved? There is actually 346 db files adding up to 134 GB in this database. Below are error messages of when the database trying to be started. I am not sure of the when pg_resetxlog was run. I suspect it was run to get rid ot the invalid primary checkpoint record. The postgresql DB had an error trying to be started up. The error was Jun 22 13:17:53 murphy postgres[27430]: [4-1] LOG: invalid primary checkpoint record Jun 22 13:17:53 murphy postgres[27430]: [5-1] LOG: could not open file /var/lib/pgsql/data/pg_xlog/ (log file 0, segment 0): No such file or directory Jun 22 13:18:49 murphy postgres[28778]: [6-1] LOG: invalid secondary checkpoint record Jun 22 13:18:49 murphy postgres[28778]: [7-1] PANIC: could not locate a valid checkpoint record Jun 22 13:26:01 murphy postgres[30770]: [6-1] LOG: database system is ready Jun 22 13:26:02 murphy postgresql: Starting postgresql service: succeeded Jun 22 13:26:20 murphy postgres[30789]: [2-1] PANIC: could not access status of transaction 553 Jun 22 13:26:20 murphy postgres[30789]: [2-2] DETAIL: could not open file /var/lib/pgsql/data/pg_clog/: No such file or directory Jun 22 13:26:20 murphy postgres[30789]: [2-3] STATEMENT: COMMIT and Jun 22 13:26:20 murphy postgres[30791]: [10-1] LOG: redo starts at 0/250 Jun 22 13:26:20 murphy postgres[30791]: [11-1] LOG: file /var/lib/pgsql/data/pg_clog/ doesn't exist, reading as zeroes Jun 22 13:26:20 murphy postgres[30791]: [12-1] LOG: record with zero length at 0/2000E84 Jun 22 13:26:20 murphy postgres[30791]: [13-1] LOG: redo done at 0/2000E60 Jun 22 13:26:20 murphy postgres[30791]: [14-1] WARNING: xlog flush request 213/7363F354 is not satisfied --- flushed only to 0/2000E84 Jun 22 13:26:20 murphy postgres[30791]: [14-2] CONTEXT: writing block 840074 of relation 17347/356768772 Jun 22 13:26:20 murphy postgres[30791]: [15-1] WARNING: xlog flush request 213/58426648 is not satisfied --- flushed only to 0/2000E84 and Jun 22 13:38:23 murphy postgres[1460]: [2-1] ERROR: xlog flush request 210/E757F150 is not satisfied --- flushed only to 0/2074CA0 Jun 22 13:38:23 murphy postgres[1460]: [2-2] CONTEXT: writing block 824605 of relation 17347/356768772 We are using a san for our storage device. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] after using pg_resetxlog, db lost
Tom I see you from past emails that you reference using -i -f with pg_filedump. I have tried this, but do not know what I am looking at. What would be the the transaction id? What parameter am I supposed to pass to find it? *** * PostgreSQL File/Block Formatted Dump Utility - Version 3.0 * * File: /npmu_base/data/base/17347/1259 * Options used: -i -f * * Dump created on: Thu Jun 24 02:44:59 2004 *** Block0 Header - Block Offset: 0x Offsets: Lower 232 (0x00e8) Block: Size 8192 Version1Upper 268 (0x010c) LSN: logid 0 recoff 0x00632c08 Special 8192 (0x2000) Items: 53 Free Space: 36 Length (including item array): 236 : 082c6300 0b00 e8000c01 .,c. 0010: 00200120 c4908801 00908801 3c8f8801 . . ... 0020: 788e8801 b48d8801 f08c8801 2c8c8801 x...,... 0030: 689f3001 688b8801 a48a8801 e0898801 h.0.h... 0040: 1c898801 5801 94878801 d0868801 X... 0050: 3c862801 a8852801 e4848801 50842801 .(...(.P.(. 0060: bc832801 f8828801 64822801 d0812801 ..(.d.(...(. 0070: 0c818801 6c11 d810 4410 l...D... 0080: b00f 1c0f d49e2801 409e2801 ..([EMAIL PROTECTED](. 0090: ac9d2801 189d2801 849c2801 f09b2801 ..(...(...(...(. 00a0: 5c9b2801 c89a2801 349a2801 a0992801 \.(...(.4.(...(. 00b0: 0c992801 78982801 e4972801 50972801 ..(.x.(...(.P.(. 00c0: bc962801 28962801 94952801 00952801 ..(.(.(...(...(. 00d0: 6c942801 d8932801 44932801 b0922801 l.(...(.D.(...(. 00e0: 1c922801 88912801 ..(...(. Data -- Item 1 -- Length: 196 Offset: 4292 (0x10c4) Flags: USED XID: min (2) CMIN|XMAX: 211 CMAX|XVAC: 469 Block Id: 0 linp Index: 1 Attributes: 24 Size: 28 infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) 10c4: 0200 d300 d501 10d4: 01001800 12291c00 cc42 7461626c .)...B..tabl 10e4: 655f636f 6e737472 61696e74 7300 e_constraints... 10f4: 1104: 1114: 5142 QB.. 1124: cd42 0100 cc42 .B...B.. 1134: 1144: 7600 0900 ..v. 1154: 0100 3000 0100 0... 1164: 0904 0200 0100 1174: 0100 7f803f40 0100 [EMAIL PROTECTED] 1184: 0200 Item 2 -- Length: 196 Offset: 4096 (0x1000) Flags: USED XID: min (2) CMIN|XMAX: 215 CMAX|XVAC: 469 Block Id: 0 linp Index: 2 Attributes: 24 Size: 28 infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) 1000: 0200 d700 d501 1010: 02001800 12291c00 d042 7461626c .)...B..tabl 1020: 655f7072 6976696c 65676573 e_privileges 1030: 1040: 1050: 5142 QB.. 1060: d142 0100 d042 .B...B.. 1070: 1080: 7600 0800 ..v. 1090: 0100 3000 0100 0... 10a0: 0904 0200 0100 10b0: 0100 7f803f40 0100 [EMAIL PROTECTED] 10c0: 0200 Item 3 -- Length: 196 Offset: 3900 (0x0f3c) Flags: USED XID: min (2) CMIN|XMAX: 219 CMAX|XVAC: 469 Block Id: 0 linp Index: 3 Attributes: 24 Size: 28 Dan. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 22, 2004 3:36 PM To: Shea,Dan [CIS] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] after using pg_resetxlog, db lost Shea,Dan [CIS] [EMAIL PROTECTED] writes: The pg_resetxlog was run as root. It caused ownership problems of pg_control and xlog files. Now we have no access to the data now through psql. The data is still there under /var/lib/pgsql/data/base/17347 (PWFPM_DEV DB name). But there is no reference to 36 of our tables in pg_class. Also the 18 other tables that are reported in this database have no data in them. Is there anyway to have the database resync or make it aware of the data under /var/lib/pgsql/data/base/17347? How can this problem be resolved? What this sounds like is that you reset the transaction counter along with the xlog, so that those tables appear to have
Re: [PERFORM] after using pg_resetxlog, db lost
I determined the largest was 12,293,162 and set it to pg_resetxlog -x 1500 /var/lib/pgsql/data I am now able to see all the data. I actually checked the log for the previous successfull startup before it the pg_control file was reset and it reported Jun 22 11:55:44 pascal postgres[24993]: [5-1] LOG: next transaction ID: 14820367; next OID: 727013114 So I entered pg_resetxlog -o 75000 /var/lib/pgsql/data Setting oid value I couldn't set 1/0, so tried below pg_resetxlog -l 1,0 /var/lib/pgsql/data This seems to be wrong because the databse is complaining and shutting down Jun 24 15:02:05 murphy postgres[28061]: [6-1] LOG: checkpoint record is at 2710/150 Jun 24 15:02:05 murphy postgres[28061]: [7-1] LOG: redo record is at 2710/150; undo record is at 0/0; shutdown TRUE Jun 24 15:02:05 murphy postgres[28061]: [8-1] LOG: next transaction ID: 1510; next OID: 75000 Jun 24 15:02:05 murphy postgres[28061]: [9-1] LOG: database system was not properly shut down; automatic recovery in progress Jun 24 15:02:05 murphy postgres[28062]: [5-1] FATAL: the database system is starting up Jun 24 15:02:05 murphy postgres[28063]: [5-1] FATAL: the database system is starting up Jun 24 15:02:05 murphy postgres[28061]: [10-1] LOG: redo starts at 2710/190 Jun 24 15:02:05 murphy postgres[28061]: [11-1] PANIC: could not access status of transaction 1530 Jun 24 15:02:05 murphy postgres[28061]: [11-2] DETAIL: could not read from file /var/lib/pgsql/data/pg_clog/000E at offset 73728: Success Jun 24 15:02:05 murphy postgres[24771]: [5-1] LOG: startup process (PID 28061) was terminated by signal 6 Jun 24 15:02:05 murphy postgres[24771]: [6-1] LOG: aborting startup due to startup process failure Jun 24 15:50:51 murphy sshd(pam_unix)[690]: session opened for user root by (uid=0) Jun 24 15:54:47 murphy su(pam_unix)[1541]: session opened for user postgres by root(uid=0) Jun 24 16:03:47 murphy su(pam_unix)[2911]: session opened for user postgres by root(uid=0) Jun 24 16:03:48 murphy su(pam_unix)[2911]: session closed for user postgres Jun 24 16:03:48 murphy postgres[3182]: [1-1] LOG: could not create IPv6 socket: Address family not supported by protocol Jun 24 16:03:48 murphy postgres[3188]: [2-1] LOG: database system was interrupted while in recovery at 2004-06-24 15:02:05 GMT Jun 24 16:03:48 murphy postgres[3188]: [2-2] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. Jun 24 16:03:48 murphy postgres[3188]: [3-1] LOG: checkpoint record is at 2710/150 Jun 24 16:03:48 murphy postgres[3188]: [4-1] LOG: redo record is at 2710/150; undo record is at 0/0; shutdown TRUE Jun 24 16:03:48 murphy postgres[3188]: [5-1] LOG: next transaction ID: 1510; next OID: 75000 Jun 24 16:03:48 murphy postgres[3188]: [6-1] LOG: database system was not properly shut down; automatic recovery in progress Jun 24 16:03:48 murphy postgres[3188]: [7-1] LOG: redo starts at 2710/190 Jun 24 16:03:48 murphy postgres[3188]: [8-1] PANIC: could not access status of transaction 1530 Jun 24 16:03:48 murphy postgres[3188]: [8-2] DETAIL: could not read from file /var/lib/pgsql/data/pg_clog/000E at offset 73728: Success Jun 24 16:03:48 murphy postgres[3182]: [2-1] LOG: startup process (PID 3188) was terminated by signal 6 Jun 24 16:03:48 murphy postgres[3182]: [3-1] LOG: aborting startup due to startup process failure How do I set the xlog properly, or rather to 1/0? Dan. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 11:41 PM To: Shea,Dan [CIS] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] after using pg_resetxlog, db lost Shea,Dan [CIS] [EMAIL PROTECTED] writes: Tom I see you from past emails that you reference using -i -f with pg_filedump. I have tried this, but do not know what I am looking at. What you want to look at is valid XMIN and XMAX values. In this example: Item 1 -- Length: 196 Offset: 4292 (0x10c4) Flags: USED XID: min (2) CMIN|XMAX: 211 CMAX|XVAC: 469 Block Id: 0 linp Index: 1 Attributes: 24 Size: 28 infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) the infomask shows XMIN_COMMITTED, so xmin (here 2) is valid, but it also shows XMAX_INVALID, so the putative XMAX (211) should be ignored. In general the xmin field should be valid, but xmax shares storage with cmin and so you have to look at the infomask bits to know whether to believe that the cmin/xmax field represents a transaction ID. The cmax/xvac field could also hold a transaction ID. If I had only the above data to go on, I'd guess that the current transaction counter is at least 469. Under normal circumstances, command counter values (cmin or cmax) are unlikely to exceed a few hundred, while the transaction IDs you are looking for are likely to be much larger. So you could get away with just computing the max of *all
Re: [PERFORM] Interest in perf testing?
What is involved, rather what kind of help do you require? Dan. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Josh Berkus Sent: Tuesday, September 28, 2004 1:54 PM To: [EMAIL PROTECTED] Subject: [PERFORM] Interest in perf testing? Folks, I'm beginning a series of tests on OSDL's Scalable Test Platform in order to determine some recommended settings for many of the new PostgreSQL.conf parameters as well as pg_autovacuum. Is anyone else interested in helping me with this? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
