Re: [PERFORM] very long updates very small tables
Hello Kevin, On 03/29/2011 09:28 PM, Kevin Grittner wrote: Lars Feistnerfeist...@uni-heidelberg.de wrote: The log tells me that certain update statements take sometimes about 3-10 minutes. But we are talking about updates on tables with 1000 to 1 rows and updates that are supposed to update 1 row. The top possibilities that come to my mind are: (1) The tables are horribly bloated. If autovacuum is off or not aggressive enough, things can degenerate to this level. Some tables are auto vacuumed regularly others are not. The specific table extjs_recentlist was never autovacuumed. So i would think that updates on this table should be always very slow, but they are not. Only every 4 or 5th day for maybe half an hour and then everything is fine again. And;-) there is no anti virus installed. (2) Memory is over-committed and your machine is thrashing. We can rule this out. There is enough memory installed and the database is less than 500MB. (3) There are explicit LOCK commands in the software which is contributing to the blocking. We use the the jdbc driver. The jdbc driver might do some locking but we don't. (4) There is some external delay within the transaction, such as waiting for user input while the transaction is open. No, no user interaction within a transaction. Maybe there's a combination of the above at play. Can you rule any of these out? -Kevin So, i will try to get the autovacuum to be more aggressive and will report again if nothing changes. Thanks a lot. Lars -- ~~~ Lars Feistner Kompetenzzentrum für Prüfungen in der Medizin Medizinische Fakultät Heidelberg, Im Neuenheimer Feld 346, Raum 013 69120 Heidelberg E-Mail: feist...@uni-heidelberg.de Fon: +49-6221-56-8269 Fax: +49-6221-56-7175 WWW: http://www.ims-m.de http://www.kompmed.de ~~~ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] very long updates very small tables
2011/3/30, Lars Feistner feist...@uni-heidelberg.de: Hello Kevin, On 03/29/2011 09:28 PM, Kevin Grittner wrote: Lars Feistnerfeist...@uni-heidelberg.de wrote: The log tells me that certain update statements take sometimes about 3-10 minutes. But we are talking about updates on tables with 1000 to 1 rows and updates that are supposed to update 1 row. The top possibilities that come to my mind are: (1) The tables are horribly bloated. If autovacuum is off or not aggressive enough, things can degenerate to this level. Some tables are auto vacuumed regularly others are not. The specific table extjs_recentlist was never autovacuumed. So i would think that updates on this table should be always very slow, but they are not. Only every 4 or 5th day for maybe half an hour and then everything is fine again. And;-) there is no anti virus installed. (2) Memory is over-committed and your machine is thrashing. We can rule this out. There is enough memory installed and the database is less than 500MB. (3) There are explicit LOCK commands in the software which is contributing to the blocking. We use the the jdbc driver. The jdbc driver might do some locking but we don't. (4) There is some external delay within the transaction, such as waiting for user input while the transaction is open. No, no user interaction within a transaction. Maybe there's a combination of the above at play. Can you rule any of these out? -Kevin So, i will try to get the autovacuum to be more aggressive and will report again if nothing changes. Thanks a lot. Lars -- ~~~ Lars Feistner Kompetenzzentrum für Prüfungen in der Medizin Medizinische Fakultät Heidelberg, Im Neuenheimer Feld 346, Raum 013 69120 Heidelberg E-Mail: feist...@uni-heidelberg.de Fon: +49-6221-56-8269 Fax: +49-6221-56-7175 WWW: http://www.ims-m.de http://www.kompmed.de ~~~ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance Hi. try to log all statements for an hour and show us it. And Postgresql.conf . pasman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] very long updates very small tables
Lars Feistner feist...@uni-heidelberg.de wrote: On 03/29/2011 09:28 PM, Kevin Grittner wrote: Lars Feistnerfeist...@uni-heidelberg.de wrote: The log tells me that certain update statements take sometimes about 3-10 minutes. But we are talking about updates on tables with 1000 to 1 rows and updates that are supposed to update 1 row. The top possibilities that come to my mind are: [all eliminated as possibilities] If you haven't already done so, you should probably turn on checkpoint logging to see if this corresponds to checkpoint activity. If it does, you can try cranking up how aggressive your background writer is, and perhaps limiting your shared_buffers to something around the size of your RAID controller's BBU cache. (I hope you have a RAID controller with BBU cache configured for write-back, anyway.) -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] COPY with high # of clients, partitioned table locking issues?
Just some information on our setup: - HP DL585 G6 - 4 x AMD Opteron 8435 (24 cores) - 256GB RAM - 2 FusionIO 640GB PCI-SSD (RAID0) - dual 10GB ethernet. - we have several tables that we store calculated values in. - these are inserted by a compute farm that calculates the results and stores them into a partitioned schema (schema listed below) - whenever we do a lot of inserts we seem to get exclusive locks. Is there something we can do to improve the performance around locking when doing a lot of parallel inserts with COPY into? We are not IO bound, what happens is that the copies start to slow down and continue to come in and cause the client to swap, we had hit over 800+ COPYS were in a waiting state, which forced us to start paging heavily creating an issue. If we can figure out the locking issue the copys should clear faster requiring less memory in use. [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 LOG: process 14405 still waiting for ExclusiveLock on extension of relation 470273 of database 16384 after 5001.894 ms [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 CONTEXT: COPY reportvalues_part_1931, line 1: 660250 4197795911917 584573.43642105709 [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 STATEMENT: COPY reportvalues_part_1931 FROM stdin USING DELIMITERS ' ' [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e LOG: process 7294 still waiting for ExclusiveLock on extension of relation 470606 of database 16384 after 5062.968 ms [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e CONTEXT: COPY reportvalues_part_1932, line 158: 660729 41998839887 45000.0 [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e STATEMENT: COPY reportvalues_part_1932 FROM stdin USING DELIMITERS '' [ 2011-03-30 15:54:56.077 EDT ] 25781 [local] asgprod:4d938556.64b5 LOG: process 25781 still waiting for ExclusiveLock on extension of relation 470606 of database 16384 after 5124.463 ms relation |16384 | 470606 | | || | | | | 93/677526 | 14354 | RowExclusiveLock | t relation |16384 | 470606 | | || | | | | 1047/4 | 27451 | RowExclusiveLock | t relation |16384 | 470606 | | || | | | | 724/58891 | 20721 | RowExclusiveLock | t transactionid | | | | || 94673393 | | | | 110/502566 | 1506 | ExclusiveLock| t virtualxid| | | | | 975/92 | | | | | 975/92 | 25751 | ExclusiveLock | t extend|16384 | 470606 | | || | | | | 672/102043 | 20669 | ExclusiveLock | f extend|16384 | 470606 | | || | | | | 1178/10| 6074 | ExclusiveLock | f virtualxid| | | | | 37/889225 | | | | | 37/889225 | 4623 | ExclusiveLock | t relation |16384 | 405725 | | || | | | | 39/822056 | 32502 | AccessShareLock | t transactionid | | | | || 94673831 | | | | 917/278| 23134 | ExclusiveLock| t relation |16384 | 470609 | | || | | | | 537/157021 | 11863 | RowExclusiveLock | t relation |16384 | 470609 | | || | | | | 532/91114 | 7282 | RowExclusiveLock | t virtualxid| | | | | 920/8 | | | | | 920/8 | 23137 | ExclusiveLock | t relation |16384 | 42 | | || | | | | 39/822056 | 32502 | AccessShareLock | t relation |16384 | 470606 | | || | | | | 915/10 | 22619 | RowExclusiveLock | t relation |16384 | 470606 | | || | | | | 344/387563 | 30343 | RowExclusiveLock | tNumber of child tables: 406 (Use \d+ to list them.) riskresults=# \d reportvalues_part_1932; Table public.reportvalues_part_1932 Column| Type
Re: [PERFORM] COPY with high # of clients, partitioned table locking issues?
John, Sorry to hear you're struggling with such underpowered hardware. ;-) A little more information would be helpful, though: 1. What version of PG are you running? 2. What are the constraints on the child tables? 3. How many rows does each copy insert? 4. Are these wrapped in transactions? 5. are the child tables created at the same time the copies are taking place? In the same transaction? 6. Are the indexes in place on the child table(s) when the copies are running? Do they have to be to validate the data? 7. What are the configuration settings for the database? (Just the ones changed from the default, please.) 8. Which file system are you running for the database files? Mount options? 9. Are the WAL files on the same file system? Bob Lunney --- On Wed, 3/30/11, Strange, John W john.w.stra...@jpmchase.com wrote: From: Strange, John W john.w.stra...@jpmchase.com Subject: [PERFORM] COPY with high # of clients, partitioned table locking issues? To: pgsql-performance@postgresql.org pgsql-performance@postgresql.org Date: Wednesday, March 30, 2011, 4:56 PM Just some information on our setup: - HP DL585 G6 - 4 x AMD Opteron 8435 (24 cores) - 256GB RAM - 2 FusionIO 640GB PCI-SSD (RAID0) - dual 10GB ethernet. - we have several tables that we store calculated values in. - these are inserted by a compute farm that calculates the results and stores them into a partitioned schema (schema listed below) - whenever we do a lot of inserts we seem to get exclusive locks. Is there something we can do to improve the performance around locking when doing a lot of parallel inserts with COPY into? We are not IO bound, what happens is that the copies start to slow down and continue to come in and cause the client to swap, we had hit over 800+ COPYS were in a waiting state, which forced us to start paging heavily creating an issue. If we can figure out the locking issue the copys should clear faster requiring less memory in use. [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 LOG: process 14405 still waiting for ExclusiveLock on extension of relation 470273 of database 16384 after 5001.894 ms [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 CONTEXT: COPY reportvalues_part_1931, line 1: 660250 41977959 11917 584573.43642105709 [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 STATEMENT: COPY reportvalues_part_1931 FROM stdin USING DELIMITERS ' ' [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e LOG: process 7294 still waiting for ExclusiveLock on extension of relation 470606 of database 16384 after 5062.968 ms [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e CONTEXT: COPY reportvalues_part_1932, line 158: 660729 41998839 887 45000.0 [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e STATEMENT: COPY reportvalues_part_1932 FROM stdin USING DELIMITERS ' ' [ 2011-03-30 15:54:56.077 EDT ] 25781 [local] asgprod:4d938556.64b5 LOG: process 25781 still waiting for ExclusiveLock on extension of relation 470606 of database 16384 after 5124.463 ms relation | 16384 | 470606 | | | | | | | | 93/677526 | 14354 | RowExclusiveLock | t relation | 16384 | 470606 | | | | | | | | 1047/4 | 27451 | RowExclusiveLock | t relation | 16384 | 470606 | | | | | | | | 724/58891 | 20721 | RowExclusiveLock | t transactionid | | | | | | 94673393 | | | | 110/502566 | 1506 | ExclusiveLock | t virtualxid | | | | | 975/92 | | | | | 975/92 | 25751 | ExclusiveLock | t extend | 16384 | 470606 | | | | | | | | 672/102043 | 20669 | ExclusiveLock | f extend | 16384 | 470606 | | | | | | | | 1178/10 | 6074 | ExclusiveLock | f virtualxid | | | | | 37/889225 | | | | | 37/889225 | 4623 | ExclusiveLock | t relation | 16384 | 405725 | | | | | | | | 39/822056 | 32502 | AccessShareLock | t transactionid | | | | | | 94673831 | | | | 917/278 | 23134 | ExclusiveLock
[PERFORM] Slow deleting tables with foreign keys
Hi All, I'm trying to delete one row from a table and it's taking an extremely long time. This parent table is referenced by other table's foreign keys, but the particular row I'm trying to delete is not referenced any other rows in the associative tables. This table has the following structure: CREATE TABLE revision ( id serial NOT NULL, revision_time timestamp without time zone NOT NULL DEFAULT now(), start_time timestamp without time zone NOT NULL DEFAULT clock_timestamp(), schema_change boolean NOT NULL, comment text, CONSTRAINT revision_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); This table is referenced from foreign key by 130 odd other tables. The total number of rows from these referencing tables goes into the hundreds of millions. Each of these tables has been automatically created by script and has the same _revision_created, _revision_expired fields, foreign keys and indexes. Here is an example of one: CREATE TABLE table_version.bde_crs_action_revision ( _revision_created integer NOT NULL, _revision_expired integer, tin_id integer NOT NULL, id integer NOT NULL, sequence integer NOT NULL, att_type character varying(4) NOT NULL, system_action character(1) NOT NULL, audit_id integer NOT NULL, CONSTRAINT pkey_table_version.bde_crs_action_revision PRIMARY KEY (_revision_created, audit_id), CONSTRAINT bde_crs_action_revision__revision_created_fkey FOREIGN KEY (_revision_created) REFERENCES table_version.revision (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT bde_crs_action_revision__revision_expired_fkey FOREIGN KEY (_revision_expired) REFERENCES table_version.revision (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE ); ALTER TABLE table_version.bde_crs_action_revision OWNER TO bde_dba; ALTER TABLE table_version.bde_crs_action_revision ALTER COLUMN audit_id SET STATISTICS 500; CREATE INDEX idx_crs_action_audit_id ON table_version.bde_crs_action_revision USING btree (audit_id); CREATE INDEX idx_crs_action_created ON table_version.bde_crs_action_revision USING btree (_revision_created); CREATE INDEX idx_crs_action_expired ON table_version.bde_crs_action_revision USING btree (_revision_expired); CREATE INDEX idx_crs_action_expired_created ON table_version.bde_crs_action_revision USING btree (_revision_expired, _revision_created); CREATE INDEX idx_crs_action_expired_key ON table_version.bde_crs_action_revision USING btree (_revision_expired, audit_id); All of the table have been analysed before I tried to run the query. The fact the all of the foreign keys have a covering index makes me wonder why this delete is taking so long. The explain for delete from table_version.revision where id = 1003 Delete (cost=0.00..1.02 rows=1 width=6) - Seq Scan on revision (cost=0.00..1.02 rows=1 width=6) Filter: (id = 100) I'm running POstgreSQL 9.0.2 on Ubuntu 10.4 Cheers Jeremy __ This message contains information, which is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank you. __ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
