Re: [PERFORM] pg_repack solves alter table set tablespace lock
Thank you Josh. Won't double post again. Just thought reorg mailing list is quite inactive. best, Ying On Friday, January 24, 2014 4:43 PM, Josh Kupershmidt schmi...@gmail.com wrote: On Fri, Jan 24, 2014 at 3:48 PM, Ying He yinghe0...@yahoo.com wrote: I looked at the pg_repack usage and in release 1.2 http://reorg.github.io/pg_repack/. there is -s tablespace that claims to be an online version of ALTER TABLE ... SET TABLESPACE is this the functionality that solves the alter table set tablespace lock issue? Cross-posting to multiple lists in quick succession is generally considered rude; I see you have posted to the reorg-general list already, which is the right forum for questions about pg_repack. (And yes, that -s flag sounds like what you are after.) Josh
Re: [PERFORM] Slow query (wrong index used maybe)
Stelian Iancu stel...@iancu.ch writes: I have Postrgres 9.3 running on a Linux machine with 32GB RAM. I have a fairly large database (some tables with approx. 1 mil. records) and I have the following query: [ 13-way join joined to a 3-way join ] Think you'll need to raise join_collapse_limit and from_collapse_limit to get the best plan here. The planning time might hurt, though. TBH that schema looks designed for inefficiency; you'd be better off rethinking the design rather than hoping the planner is smart enough to save you from it. regards, tom lane -- 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] pg_repack solves alter table set tablespace lock
Ying He escribió: Thank you Josh. Won't double post again. Just thought reorg mailing list is quite inactive. Well, that tells you something about its maintenance state and what sort of help you can expect if you find yourself in trouble with it. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Slow query (wrong index used maybe)
Hello, I have Postrgres 9.3 running on a Linux machine with 32GB RAM. I have a fairly large database (some tables with approx. 1 mil. records) and I have the following query: SELECT * FROM ( SELECT DISTINCT c.ext_content_id AS type_1_id, substring(c.ext_content_id::text, 1, 13) AS type_1_album_id, cm1.value AS type_1_artist, cm2.value AS type_1_title, cm4.value AS type_1_duration, pm1.value AS type_1_icpn, cm3.value AS type_1_isrc, c.provider AS type_1_provider, to_number(cm5.value::text, '99'::text) AS type_2_set_number, to_number(cm6.value::text, '99'::text) AS type_2_track_number, cm7.value AS type_6_availability_ppd, cm12.value AS type_6_availability_sub, cm9.value AS type_1_language, cm11.value AS type_1_label_reporting_id, cm13.value AS type_1_parent_isrc FROM content c LEFT JOIN content_metadata cm1 ON c.content_id = cm1.content_id AND cm1.name::text = 'track_artist'::text LEFT JOIN content_metadata cm2 ON c.content_id = cm2.content_id AND cm2.name::text = 'track_title'::text LEFT JOIN content_metadata cm3 ON c.content_id = cm3.content_id AND cm3.name::text = 'track_isrc'::text LEFT JOIN content_metadata cm4 ON c.content_id = cm4.content_id AND cm4.name::text = 'track_duration'::text LEFT JOIN content_metadata cm5 ON c.content_id = cm5.content_id AND cm5.name::text = 'set_number'::text LEFT JOIN content_metadata cm6 ON c.content_id = cm6.content_id AND cm6.name::text = 'track_number'::text LEFT JOIN content_metadata cm7 ON c.content_id = cm7.content_id AND cm7.name::text = 'unlimited'::text LEFT JOIN content_metadata cm9 ON c.content_id = cm9.content_id AND cm9.name::text = 'language'::text LEFT JOIN content_metadata cm10 ON c.content_id = cm10.content_id AND cm10.name::text = 'import_date'::text LEFT JOIN content_metadata cm11 ON c.content_id = cm11.content_id AND cm11.name::text = 'label_reporting_id'::text LEFT JOIN content_metadata cm12 ON c.content_id = cm12.content_id AND cm12.name::text = 'subscription'::text LEFT JOIN content_metadata cm13 ON c.content_id = cm13.content_id AND cm13.name::text = 'parent_isrc'::text, product p LEFT JOIN product_metadata pm4 ON p.product_id = pm4.product_id AND pm4.name::text = 'product_title'::text LEFT JOIN product_metadata pm1 ON p.product_id = pm1.product_id AND pm1.name::text = 'upc'::text WHERE p.ext_product_id::text = substr(c.ext_content_id::text, 1, 13) ) view WHERE type_1_id='1-111-1027897-01-001'; Below are the definitions of the tables involved. Content: Table public.content Column |Type | Modifiers -+-+--- content_id | bigint | not null status | character varying(3)| not null display_name| character varying(1024) | not null ext_content_id | character varying(64) | not null provider| character varying(128) | not null last_updated_by | character varying(30) | not null last_updated_on | timestamp without time zone | not null created_by | character varying(30) | not null created_on | timestamp without time zone | not null Indexes: content_pkey PRIMARY KEY, btree (content_id) ak_key_2_content UNIQUE, btree (ext_content_id, provider) index_content_01 UNIQUE, btree (ext_content_id) Foreign-key constraints: fk_content_01 FOREIGN KEY (provider) REFERENCES provider(ext_provider_id) Referenced by: TABLE content_metadata CONSTRAINT fk_content_metadata_01 FOREIGN KEY (content_id) REFERENCES content(content_id) TABLE packaged CONSTRAINT fk_packaged_reference_content FOREIGN KEY (content_id) REFERENCES content(content_id) TABLE product_content CONSTRAINT fk_product_content_01 FOREIGN KEY (content_id) REFERENCES content(content_id) Triggers: td_content BEFORE DELETE ON content FOR EACH ROW EXECUTE PROCEDURE trigger_fct_td_content() ti_content BEFORE INSERT ON content FOR EACH ROW EXECUTE PROCEDURE trigger_fct_ti_content() tu_content BEFORE UPDATE ON content FOR EACH ROW EXECUTE PROCEDURE trigger_fct_tu_content() tu_content_tree BEFORE UPDATE ON content FOR EACH ROW EXECUTE PROCEDURE trigger_fct_tu_content_tree() Product: Table public.product Column |Type | Modifiers -+-+--- product_id | bigint | not null status | character varying(3)| not null display_name| character varying(1024) | not null ext_product_id | character varying(64) | not null
Re: [PERFORM] Slow query (wrong index used maybe)
On Mon, Jan 27, 2014, at 9:20, salah jubeh wrote: Hello Stelian, Hello, Have you tried to use func_table module?, I think it will help you to eliminate all the joins. No, I haven't. I can have a look later, thanks. Regards -- 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] Slow query (wrong index used maybe)
On Mon, Jan 27, 2014, at 7:06, Tom Lane wrote: Stelian Iancu stel...@iancu.ch writes: I have Postrgres 9.3 running on a Linux machine with 32GB RAM. I have a fairly large database (some tables with approx. 1 mil. records) and I have the following query: [ 13-way join joined to a 3-way join ] Think you'll need to raise join_collapse_limit and from_collapse_limit to get the best plan here. The planning time might hurt, though. I did raise both to 40 and it works flawless (for now). I got the response time to less than a second. However I don't know what the implications are for the future. TBH that schema looks designed for inefficiency; you'd be better off rethinking the design rather than hoping the planner is smart enough to save you from it. Heh, I wish it was this easy. This whole thing is part of us moving away from Oracle to Postgres. We already have this huge DB with this schema in Oracle (which was successfully imported into Postgres, minus these performance issues we're seeing now) and I don't know how feasible it is to even start thinking about a redesign. But I appreciate your input regarding this. Maybe one of these days I will have success in convincing my boss to even start taking a look at the design of the DB (you know the saying it works, don't fix it). regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] Slow query (wrong index used maybe)
Hello Stelian, Have you tried to use func_table module?, I think it will help you to eliminate all the joins. Regards On Monday, January 27, 2014 5:54 PM, Stelian Iancu stel...@iancu.ch wrote: Hello, I have Postrgres 9.3 running on a Linux machine with 32GB RAM. I have a fairly large database (some tables with approx. 1 mil. records) and I have the following query: SELECT * FROM ( SELECT DISTINCT c.ext_content_id AS type_1_id, substring(c.ext_content_id::text, 1, 13) AS type_1_album_id, cm1.value AS type_1_artist, cm2.value AS type_1_title, cm4.value AS type_1_duration, pm1.value AS type_1_icpn, cm3.value AS type_1_isrc, c.provider AS type_1_provider, to_number(cm5.value::text, '99'::text) AS type_2_set_number, to_number(cm6.value::text, '99'::text) AS type_2_track_number, cm7.value AS type_6_availability_ppd, cm12.value AS type_6_availability_sub, cm9.value AS type_1_language, cm11.value AS type_1_label_reporting_id, cm13.value AS type_1_parent_isrc FROM content c LEFT JOIN content_metadata cm1 ON c.content_id = cm1.content_id AND cm1.name::text = 'track_artist'::text LEFT JOIN content_metadata cm2 ON c.content_id = cm2.content_id AND cm2.name::text = 'track_title'::text LEFT JOIN content_metadata cm3 ON c.content_id = cm3.content_id AND cm3.name::text = 'track_isrc'::text LEFT JOIN content_metadata cm4 ON c.content_id = cm4.content_id AND cm4.name::text = 'track_duration'::text LEFT JOIN content_metadata cm5 ON c.content_id = cm5.content_id AND cm5.name::text = 'set_number'::text LEFT JOIN content_metadata cm6 ON c.content_id = cm6.content_id AND cm6.name::text = 'track_number'::text LEFT JOIN content_metadata cm7 ON c.content_id = cm7.content_id AND cm7.name::text = 'unlimited'::text LEFT JOIN content_metadata cm9 ON c.content_id = cm9.content_id AND cm9.name::text = 'language'::text LEFT JOIN content_metadata cm10 ON c.content_id = cm10.content_id AND cm10.name::text = 'import_date'::text LEFT JOIN content_metadata cm11 ON c.content_id = cm11.content_id AND cm11.name::text = 'label_reporting_id'::text LEFT JOIN content_metadata cm12 ON c.content_id = cm12.content_id AND cm12.name::text = 'subscription'::text LEFT JOIN content_metadata cm13 ON c.content_id = cm13.content_id AND cm13.name::text = 'parent_isrc'::text, product p LEFT JOIN product_metadata pm4 ON p.product_id = pm4.product_id AND pm4.name::text = 'product_title'::text LEFT JOIN product_metadata pm1 ON p.product_id = pm1.product_id AND pm1.name::text = 'upc'::text WHERE p.ext_product_id::text = substr(c.ext_content_id::text, 1, 13) ) view WHERE type_1_id='1-111-1027897-01-001'; Below are the definitions of the tables involved. Content: Table public.content Column | Type | Modifiers -+-+--- content_id | bigint | not null status | character varying(3) | not null display_name | character varying(1024) | not null ext_content_id | character varying(64) | not null provider | character varying(128) | not null last_updated_by | character varying(30) | not null last_updated_on | timestamp without time zone | not null created_by | character varying(30) | not null created_on | timestamp without time zone | not null Indexes: content_pkey PRIMARY KEY, btree (content_id) ak_key_2_content UNIQUE, btree (ext_content_id, provider) index_content_01 UNIQUE, btree (ext_content_id) Foreign-key constraints: fk_content_01 FOREIGN KEY (provider) REFERENCES provider(ext_provider_id) Referenced by: TABLE content_metadata CONSTRAINT fk_content_metadata_01 FOREIGN KEY (content_id) REFERENCES content(content_id) TABLE packaged CONSTRAINT fk_packaged_reference_content FOREIGN KEY (content_id) REFERENCES content(content_id) TABLE product_content CONSTRAINT fk_product_content_01 FOREIGN KEY (content_id) REFERENCES content(content_id) Triggers: td_content BEFORE DELETE ON content FOR EACH ROW EXECUTE PROCEDURE trigger_fct_td_content() ti_content BEFORE INSERT ON content FOR EACH ROW EXECUTE PROCEDURE trigger_fct_ti_content() tu_content BEFORE UPDATE ON content FOR EACH ROW EXECUTE PROCEDURE trigger_fct_tu_content() tu_content_tree BEFORE UPDATE ON content FOR EACH ROW EXECUTE PROCEDURE trigger_fct_tu_content_tree() Product: Table public.product Column | Type | Modifiers -+-+--- product_id | bigint | not null
Re: [PERFORM] Slow query (wrong index used maybe)
My developers have had the same issue. Postgres 9.2.3 on Linux 5.6. The query planner estimates (for 27 table join SQL) that using the nestloop is faster, when in fact it is not. A hashjoin returns results faster. We've set enable_nestloop = false and have gotten good results. The problem is, nestoop would be faster for other types of queries. Maybe ones with fewer joins. Recently we made a change that forced our multi join queires to slow down. We now build temp views for each user session. To speed these queries up, we up'd geqo_effort = 10. This has also given us good results; but again, we don't know if there will be another impact down the road. Same issue here with redesign. There is some simple denormalization we could do that would minimize our joins. Instead if link tables, we would utilize hstore, json or array columns types. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-wrong-index-used-maybe-tp5788979p5789045.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- 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] Slow query (wrong index used maybe)
On 28/01/14 08:10, bobJobS wrote: My developers have had the same issue. Postgres 9.2.3 on Linux 5.6. The latest Linux kernel is 3.13 (https://www.kernel.org), so I assume 5.6 is a distribution version. So which distribution of Linux are you using? Cheers, Gavin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Select hangs and there are lots of files in table and index directories.
Have a problem where a stored procedure is taking a week to run. The stored procedure should take less than a second to run. In researching a select hanging problem, three things are suggested; an autovacuum problem, a resource is locked, or there is something wrong with the stored procedure. · Autovacuum is running. A 'ps -elf | grep postgres' shows: 00:00:43 postgres: logger process 00:5:50 postgres: writer process 00:3:04 postgres: wal writer process 00:00:48 postgres: autovacuum launcher process 00:00:50 postgres: stats collector process 00:01:28 postgres: operstions OPDB [local] idle 154.11.29 postgres: operstions OPDB [local] select · The select is from running a select of a stored procedure from a 'c' program using the PqsendQuery function. · Postgres.conf has both autovacuum and track_counts set to 'on'. All other autovacuum values are left as delivered (commented out). · A 'select * from pg_stats_activity;' shows no query is blocked. · We have recently changed from using Oracle 10g (running on Red Hat AS 4.5) to PostgreSQL 9.1.2 (running on CentOS 6.3). The only differences between the two versions are: o Syntax changes between Oracle and Postgres. o In Oracle a commit was executed after each 'chuck' of work was done. A commit is no longer used in Postgres because the Postgres documentation indicates that a commit has no affect until the end of the transaction (i.e., the end of the stored procedure). o The same stored procedure is running just fine on all of our test systems and at one of our two customer sites. All of the systems are configured the same (same operating system and software). Lastly, in the directories used to store the tables and indexes, there are 918896 files in the tables directory and 921291 files in the indexes directory. All of the file names are just numbers (no extensions). About 60 files are added to each directory every second. On our test systems and at our other customer site, there are only about 50 files in each directory. Why are there so many files? Thank you everyone for your time. Peter Blair
Re: [PERFORM] Select hangs and there are lots of files in table and index directories.
Peter Blair petertbl...@gmail.com writes: Have a problem where a stored procedure is taking a week to run. The stored procedure should take less than a second to run. Is that it's known to terminate if you give it a week, or we've let it run for a week and it shows no sign of ever terminating? In researching a select hanging problem, three things are suggested; an autovacuum problem, a resource is locked, or there is something wrong with the stored procedure. I'd bet on the last, given that you're apparently working with an immature port from Oracle. The error recovery semantics, in particular, are enough different in PL/SQL and PL/pgSQL that it's not too hard to credit having accidentally written an infinite loop via careless translation. Lastly, in the directories used to store the tables and indexes, there are 918896 files in the tables directory and 921291 files in the indexes directory. All of the file names are just numbers (no extensions). About 60 files are added to each directory every second. On our test systems and at our other customer site, there are only about 50 files in each directory. Why are there so many files? If the filenames are just numbers, then they must be actual tables or indexes, not temp files. (You could cross-check that theory by noting whether the system catalogs, such as pg_class, are bloating at a proportional rate.) I'm guessing that there's some loop in your procedure that's creating new temp tables, or maybe even non-temp tables. You would not be able to see them via select * from pg_class in another session because they're not committed yet, but they'd be taking up filesystem entries. The loop might or might not be dropping the tables again; IIRC the filesystem entries wouldn't get cleaned up till end of transaction even if the tables are nominally dropped. Not much to go on, but I'd look for a loop that includes a CREATE TABLE and a BEGIN ... EXCEPT block, and take a close look at the conditions under which the EXCEPT allows the loop to continue. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
