date:20220323

Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

2022-03-23 Thread Lars Aksel Opsahl

Hi

We are running
postgres server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" 
SFCGAL="1.3.7" PROJ="7.2.1" GDAL="GDAL 3.2.1, released 2020/12/29" 
LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" 
TOPOLOGY RASTER

The problem is that it takes more than 10 hours (duration: 36885527.039) to 
browse tables geometry from qgis https://explain.depesz.com/s/MxAN#bquery with 
high load on the server.
We have at least 45 jobs running and around 70% CPU load on the server.

Then I started to check views/tables involved and found that the view 
geometry_columns seems to be using a very long time
'explain analyze select * from geometry_columns' have been waiting for more 
than 2 hours now, will paste the result to https://explain.depesz.com when done.

While waiting I created temp table for the system tables involved in view 
geometry_columns like this

create temp table pg_attribute_temp as select 
attcollation,attrelid,attname,atttypid,attstattarget,attlen,attnum,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,attnotnull,atthasdef,atthasmissing,attidentity,attgenerated,attisdropped,attislocal,attinhcount,attacl,attoptions,attfdwoptions
 from pg_attribute;
create temp table pg_namespace_temp as select * from pg_namespace;
create temp table pg_type_temp as select * from pg_type;
create temp table pg_constraint_temp as select * from pg_constraint;

SELECT 1702623
Time: 42552.899 ms (00:42.553)
SELECT 841
Time: 132.595 ms
SELECT 245239
Time: 3378.395 ms (00:03.378)
SELECT 9575
Time: 205.036 ms

That did not take very long time.

Then created geometry_columns_temp_no_rules using those new temp tables.

explain analyze select * from geometry_columns_temp_no_rules

And that takes less than 6 seconds with no indexes. Here is the explain from 
https://explain.depesz.com/s/yBSd

Why is temp tables with no indexes much faster system tables with indexes ?

(I do not think it's related to not having rules I tested to crated a view 
using system tables with but with no rules and that hanged for more that 15 
minuttes an dthen I gave up)

Here is the view def that I used.

CREATE VIEW geometry_columns_temp_no_rules AS
SELECT current_database()::character varying(256) AS f_table_catalog,
n.nspname AS f_table_schema,
c.relname AS f_table_name,
a.attname AS f_geometry_column,
COALESCE(postgis_typmod_dims(a.atttypmod), sn.ndims, 2) AS coord_dimension,
COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS srid,
replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 
'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), 
'Z'::text, ''::text)::character varying(30) AS type
   FROM pg_class c
 JOIN pg_attribute_temp a ON a.attrelid = c.oid AND NOT a.attisdropped
 JOIN pg_namespace_temp n ON c.relnamespace = n.oid
 JOIN pg_type_temp t ON a.atttypid = t.oid
 LEFT JOIN ( SELECT s.connamespace,
s.conrelid,
s.conkey,
replace(split_part(s.consrc, ::text, 2), ')'::text, ''::text) 
AS type
   FROM ( SELECT pg_constraint_temp.connamespace,
pg_constraint_temp.conrelid,
pg_constraint_temp.conkey,
pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
   FROM pg_constraint_temp) s
  WHERE s.consrc ~~* '%geometrytype(% = %'::text) st ON st.connamespace 
= n.oid AND st.conrelid = c.oid AND (a.attnum = ANY (st.conkey))
 LEFT JOIN ( SELECT s.connamespace,
s.conrelid,
s.conkey,
replace(split_part(s.consrc, ' = '::text, 2), ')'::text, 
''::text)::integer AS ndims
   FROM ( SELECT pg_constraint_temp.connamespace,
pg_constraint_temp.conrelid,
pg_constraint_temp.conkey,
pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
   FROM pg_constraint_temp) s
  WHERE s.consrc ~~* '%ndims(% = %'::text) sn ON sn.connamespace = 
n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey))
 LEFT JOIN ( SELECT s.connamespace,
s.conrelid,
s.conkey,
replace(replace(split_part(s.consrc, ' = '::text, 2), ')'::text, 
''::text), '('::text, ''::text)::integer AS srid
   FROM ( SELECT pg_constraint_temp.connamespace,
pg_constraint_temp.conrelid,
pg_constraint_temp.conkey,
pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
   FROM pg_constraint_temp) s
  WHERE s.consrc ~~* '%srid(% = %'::text) sr ON sr.connamespace = n.oid 
AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey))
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 
'f'::"char", 'p'::"char"])) AND NOT c.relname = 'raster_columns'::name AND 
t.typname = 'geometry'::name AND NOT pg_is_other_temp_schema(c.reln

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

2022-03-23 Thread Lars Aksel Opsahl

>Hi
>
>We are running
>postgres server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
>POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" 
>SFCGAL="1.3.7" PROJ="7.2.1" GDAL="GDAL 3.2.1, released 2020/12/29" 
>LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" 
>TOPOLOGY RASTER
>
>The problem is that it takes more than 10 hours (duration: 36885527.039) to 
>browse tables geometry from qgis https://explain.depesz.com/s/MxAN#bquery with 
>high load on the server.
>We have at least 45 jobs running and around 70% CPU load on the server.
>
>Then I started to check views/tables involved and found that the view 
>geometry_columns seems to be using a very long time
>'explain analyze select * from geometry_columns' have been waiting for more 
>than 2 hours now, will paste the result to https://explain.depesz.com when 
>done.
>
>While waiting I created temp table for the system tables involved in view 
>geometry_columns like this
>
>create temp table pg_attribute_temp as select 
>attcollation,attrelid,attname,atttypid,attstattarget,attlen,attnum,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,attnotnull,atthasdef,atthasmissing,attidentity,attgenerated,attisdropped,attislocal,attinhcount,attacl,attoptions,attfdwoptions
> from pg_attribute;
>create temp table pg_namespace_temp as select * from pg_namespace;
>create temp table pg_type_temp as select * from pg_type;
>create temp table pg_constraint_temp as select * from pg_constraint;
>
>SELECT 1702623
>Time: 42552.899 ms (00:42.553)
>SELECT 841
>Time: 132.595 ms
>SELECT 245239
>Time: 3378.395 ms (00:03.378)
>SELECT 9575
>Time: 205.036 ms
>
>That did not take very long time.
>
>Then created geometry_columns_temp_no_rules using those new temp tables.
>
>explain analyze select * from geometry_columns_temp_no_rules
>
>And that takes less than 6 seconds with no indexes. Here is the explain from 
>https://explain.depesz.com/s/yBSd
>
>Why is temp tables with no indexes much faster system tables with indexes ?
>
>(I do not think it's related to not having rules I tested to crated a view 
>using system tables with but with no rules and that hanged for more that 15 
>minuttes an dthen I gave up)
>
>Here is the view def that I used.
>
>CREATE VIEW geometry_columns_temp_no_rules AS
>SELECT current_database()::character varying(256) AS f_table_catalog,
>n.nspname AS f_table_schema,
>c.relname AS f_table_name,
>a.attname AS f_geometry_column,
>COALESCE(postgis_typmod_dims(a.atttypmod), sn.ndims, 2) AS coord_dimension,
>COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS srid,
>replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 
> 'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), 
> 'Z'::text, ''::text)::character varying(30) AS type
>   FROM pg_class c
> JOIN pg_attribute_temp a ON a.attrelid = c.oid AND NOT a.attisdropped
> JOIN pg_namespace_temp n ON c.relnamespace = n.oid
> JOIN pg_type_temp t ON a.atttypid = t.oid
> LEFT JOIN ( SELECT s.connamespace,
>s.conrelid,
>s.conkey,
>replace(split_part(s.consrc, ::text, 2), ')'::text, ''::text) 
> AS type
>   FROM ( SELECT pg_constraint_temp.connamespace,
>pg_constraint_temp.conrelid,
>pg_constraint_temp.conkey,
>pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
>   FROM pg_constraint_temp) s
>  WHERE s.consrc ~~* '%geometrytype(% = %'::text) st ON 
> st.connamespace = n.oid AND st.conrelid = c.oid AND (a.attnum = ANY 
> (st.conkey))
> LEFT JOIN ( SELECT s.connamespace,
>s.conrelid,
>s.conkey,
>replace(split_part(s.consrc, ' = '::text, 2), ')'::text, 
> ''::text)::integer AS ndims
>   FROM ( SELECT pg_constraint_temp.connamespace,
>pg_constraint_temp.conrelid,
>pg_constraint_temp.conkey,
>pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
>   FROM pg_constraint_temp) s
>  WHERE s.consrc ~~* '%ndims(% = %'::text) sn ON sn.connamespace = 
> n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey))
> LEFT JOIN ( SELECT s.connamespace,
>s.conrelid,
>s.conkey,
>replace(replace(split_part(s.consrc, ' = '::text, 2), ')'::text, 
> ''::text), '('::text, ''::text)::integer AS srid
>   FROM ( SELECT pg_constraint_temp.connamespace,
>pg_constraint_temp.conrelid,
>pg_constraint_temp.conkey,
>pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
>   FROM pg_constraint_temp) s
>  WHERE s.consrc ~~* '%srid(% = %'::text) sr ON sr.connamespace = 
> n.oid AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey))
>  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 
> 'f'::"char", 'p'::"cha

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

2022-03-23 Thread Justin Pryzby

On Wed, Mar 23, 2022 at 09:44:09AM +, Lars Aksel Opsahl wrote:
> Why is temp tables with no indexes much faster system tables with indexes ?

I think the "temp table" way is accidentally faster due to having no
statistics, not because it has no indexes.  If you run ANALYZE, you may hit the
same issue (or, maybe you just need to VACUUM ANALYZE your system catalogs).

-- 
Justin

High process memory consumption when running sort

2022-03-23 Thread Shai Shapira

Hi,

When running our application, we noticed that some processes are taking a lot 
of memory ( 10, 15, 20GB or so, of RSS ).
It is also reproduced when running in psql.

PG version is 12.6

2 examples:

  *   Common table, PG_BUFFERCACHE, when doing group by, session takes 140MB, 
which is not a lot, but still more than the 20MB that set for work_mem.
  *   Application table, ~15M rows, grouping by a smallint columns - takes 
~1000 MB.

As I wrote before, application processes reached tens of GB.

In the first case, PG also used temp files, at the second case, when more 
memory was used and also in the application case, temp files were not created.

I will try to add as much details as possible, please let me know if there is 
something additional that is required.

Thanks,
Shai


More details:

First what I see, and then versions, parameters, etc.
Note: this DB is set with Patroni, replication, etc. but the scenario was 
reproduce ( up to few hundreds MB, not tens of GB ) on other environment, 
without it.

Queries:

  1.  PG_BUFFERCACHE  :

  *   INSERT INTO PGAWR_BUFFERCACHE_SUMMARY( SELECT 1, NOW(), RELFILENODE, 
RELDATABASE, COUNT(*) AS BUFFERS_COUNT FROM PG_BUFFERCACHE GROUP BY 
RELFILENODE, RELDATABASE) ;
  *   Insert 12309 rows.
  *   Table has 2097152 rows.


  1.  Application table:


  *   Query: select cycle_code, count(*) from ape1_subscr_offers group by 
cycle_code ;


  *   table has 15318725 rows.


  *   The cycle_code column is the first column of an index.



  *   Table is partitioned, 176 partitions.



  *   The result
cycle_code |  count
+-
  1 | 3824276
  2 | 3824745
  3 | 3834609
  9 | 3835095
(4 rows)

paaspg=> show work_mem;
work_mem
--
20MB
(1 row)


Table structure:

paaspg=> \d ape1_subscr_offers
Partitioned table "vm1app.ape1_subscr_offers"
Column |Type | Collation | Nullable | 
Default
---+-+---+--+-
cycle_code| smallint|   | not null |
 customer_segment  | smallint|   | not null |
 subscriber_id | bigint  |   | not null |
 offer_id  | integer |   | not null |
 offer_instance| bigint  |   | not null |
 offer_eff_date| timestamp without time zone |   | not null |
 sys_creation_date | timestamp without time zone |   | not null |
 sys_update_date   | timestamp without time zone |   |  |
 operator_id   | integer |   |  |
 application_id| character(6)|   |  |
 dl_service_code   | character(5)|   |  |
 dl_update_stamp   | smallint|   |  | 0
update_id | bigint  |   |  |
 offer_exp_date| timestamp without time zone |   |  |
 source_offer_agr_id   | bigint  |   |  |
 source_offer_instance | bigint  |   |  |
 eff_act_code_pror | character varying(25)   |   |  |
 exp_act_code_pror | character varying(25)   |   |  |
 load_ind  | character(1)|   |  |
Partition key: RANGE (cycle_code, customer_segment)
Indexes:
"ape1_subscr_offers_pkey" PRIMARY KEY, btree (cycle_code, customer_segment, 
subscriber_id, offer_id, offer_instance, offer_eff_date)
"ape1_subscr_offers_1ix" btree (update_id)
Number of partitions: 176 (Use \d+ to list them.)

Explain:
paaspg=> explain select cycle_code, count(*) from ape1_subscr_offers group by 
cycle_code ;
  QUERY PLAN
--
Finalize GroupAggregate  (cost=385331.98..385382.65 rows=200 width=10)
   Group Key: ape1_subscr_offers_p40.cycle_code
   ->  Gather Merge  (cost=385331.98..385378.65 rows=400 width=10)
 Workers Planned: 2
 ->  Sort  (cost=384331.96..384332.46 rows=200 width=10)
   Sort Key: ape1_subscr_offers_p40.cycle_code
   ->  Partial HashAggregate  (cost=384322.31..384324.31 rows=200 
width=10)
 Group Key: ape1_subscr_offers_p40.cycle_code
 ->  Parallel Append  (cost=0.00..352347.81 rows=6394900 
width=2)
   ->  Parallel Seq Scan on ape1_subscr_offers_p40  
(cost=0.00..5052.94 rows=101094 width=2)
   ->  Parallel Seq Scan on ape1_subscr_offers_p46  
(cost=0.00..5042.73 rows=100972 width=2)
   ->  Parallel Seq Scan on ape1_subsc

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

2022-03-23 Thread Lars Aksel Opsahl

>From: Justin Pryzby Sent: Wednesday, March 23, 2022 2:19 
>PMTo: Lars Aksel Opsahl Cc: 
>[email protected] 
>Subject: Re: Using system tables 
>directly takes many hours, using temp tables with no indexes takes a few 
>seconds for geometry_columns view.
>
>On Wed, Mar 23, 2022 at 09:44:09AM +, Lars Aksel Opsahl wrote:
>> Why is temp tables with no indexes much faster system tables with indexes ?
>
>I think the "temp table" way is accidentally faster due to having no
>statistics, not because it has no indexes.  If you run ANALYZE, you may hit the
>same issue (or, maybe you just need to VACUUM ANALYZE your system catalogs).

Hi

I had tested this in the morning and it did not work  (VACUUM ANALYZE pg_class; 
VACUUM ANALYZE pg_attribute; VACUUM ANALYZE pg_namespace; VACUUM ANALYZE 
raster_columns; VACUUM ANALYZE pg_type; )

But now it seemed to work maybe one time, the 50 secs query 
(https://explain.depesz.com/s/Op7i)  
was down to 6 secs, but just to be sure I rerun the query one more time and we 
where where back to execution time of 50 seconds.

It seems like stats may be valid for just some few seconds  before having to 
run analyze again and that takes a long time.

The 45 jobs running on the server are creating a lot temp tables and maybe some 
unlogged tables

We can not run run analyze in every job because this may be many hundred 
thounsed jobs that we need to run.

Does this mean that we can not use temp tables in this extent and in stead use 
https://www.postgresql.org/docs/12/queries-with.html ?
But the problem with "with" is that we can not create indexes.

Or is a option to exclude temp tables geometry_columns in effective way , but 
that will probably cause problems if we create temp table in jobs where we use 
postgis.so that not a good solution either,

Thanks

Lars

Re: High process memory consumption when running sort

2022-03-23 Thread Justin Pryzby

On Wed, Mar 23, 2022 at 02:42:06PM +, Shai Shapira wrote:
> Hi,
> 
> When running our application, we noticed that some processes are taking a lot 
> of memory ( 10, 15, 20GB or so, of RSS ).
> It is also reproduced when running in psql.

Note that RSS can include shared_buffers read by that backend.
That's a linux behavior, not specific to postgres.  It's what Andres was
describing here:
https://www.postgresql.org/message-id/flat/[email protected]

You have effective_cache_size = 48GB, so this seems to be working as intended.
(ecc is expected to include data cached not only by postgres but by the OS page
cache, too).

> Memory consumption: ( of case 2, application table, using system_stats )

I'm not sure, but I guess this is just a postgres view of whatever the OS
shows.

> Using top:
>   PID USER  PR  NIVIRTRESSHR S  %CPU %MEM TIME+ COMMAND
> 15298 postgres  20   0   16.8g   1.1g   1.1g S   0.0  1.7   0:02.63 postgres

> PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 
> (Red Hat 4.8.5-44), 64-bit
> Linux illin7504 3.10.0-1127.19.1.el7.x86_64 #1 SMP Tue Aug 11 19:12:04 EDT 
> 2020 x86_64 x86_64 x86_64 GNU/Linux

> shared_buffers  | configuration file   | postmaster   
>  | 2097152| 8kB  | 1024
> effective_cache_size| configuration file   | user 
>  | 6291456| 8kB  | 524288
> work_mem| configuration file   | user 
>  | 20480  | kB   | 4096

Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

High process memory consumption when running sort

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

Re: High process memory consumption when running sort

6 matches

Site Navigation

Mail list logo

Footer information