|
Hi
>From: Imre
Samu <pella.samu@xxxxxxxxx>
>Maybe you can upgrade to 12.9 ( from 12.6 )
( https://www.postgresql.org/docs/release/12.9/ )
>Sent: Monday, February 7, 2022 8:51 PM >And the next minor release = pg 12.10 is expected on February 10th, 2022 https://www.postgresql.org/developer/roadmap/ >As I see - only a minor fix exists for "system columns": "Don't ignore system columns when estimating the number of groups using extended statistics (Tomas Vondra)" in 12.7 > >I have similar experiences with the system tables - vacuuming is extreme important >in my case - I am calling "vacuum" in every ETL job - cleaning my system tables. >
Thanks we may test upgrade later seems like the problem here was related to both vacuum and set parallel_workers to 0 in this case, see mail for more info.
SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname = 'pg_catalog' ORDER BY pg_relation_size(C.oid) DESC LIMIT 20; relname | pg_size_pretty -----------------------------------+---------------- pg_largeobject | 17 GB pg_attribute | 1452 MB pg_statistic | 1103 MB pg_class | 364 MB pg_attribute_relid_attnam_index | 307 MB pg_depend | 285 MB pg_largeobject_loid_pn_index | 279 MB pg_attribute_relid_attnum_index | 230 MB pg_depend_reference_index | 207 MB pg_depend_depender_index | 198 MB pg_class_relname_nsp_index | 133 MB pg_index | 111 MB pg_statistic_relid_att_inh_index | 101 MB pg_class_oid_index | 52 MB pg_class_tblspc_relfilenode_index | 46 MB pg_shdepend | 38 MB pg_shdepend_depender_index | 25 MB pg_index_indexrelid_index | 24 MB pg_shdepend_reference_index | 21 MB pg_index_indrelid_index | 18 MB (20 rows)
select schemaname ,relname ,n_tup_ins ,n_tup_upd ,n_tup_del ,n_tup_hot_upd ,n_live_tup ,n_dead_tup from pg_stat_all_tables where n_dead_tup > 0 and schemaname='pg_catalog' ; schemaname | relname | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup ------------+--------------------+-----------+-----------+-----------+---------------+------------+------------ pg_catalog | pg_default_acl | 6 | 2 | 1 | 2 | 5 | 3 pg_catalog | pg_shdepend | 10994319 | 53 | 10975090 | 0 | 32982 | 1711 pg_catalog | pg_type | 24820549 | 4558 | 24610078 | 300 | 41619 | 5492 pg_catalog | pg_attribute | 183016129 | 13549029 | 181178505 | 8326103 | 418492 | 46415 pg_catalog | pg_proc | 1406 | 1340 | 1187 | 1122 | 6551 | 1351 pg_catalog | pg_class | 30278004 | 8510013 | 30021392 | 5917849 | 50569 | 6193 pg_catalog | pg_authid | 50 | 7 | 10 | 7 | 887 | 30 pg_catalog | pg_auth_members | 39 | 0 | 1 | 0 | 38 | 2 pg_catalog | pg_sequence | 5101683 | 5100683 | 5087311 | 5045867 | 3250 | 507 pg_catalog | pg_attrdef | 6859893 | 0 | 6683508 | 0 | 3973 | 256 pg_catalog | pg_constraint | 56521 | 4 | 42635 | 0 | 9317 | 1782 pg_catalog | pg_depend | 89540444 | 8 | 88833727 | 0 | 211747 | 21601 pg_catalog | pg_description | 3561 | 4478 | 3528 | 3745 | 8259 | 967 pg_catalog | pg_index | 12360100 | 262429 | 12220917 | 258746 | 40690 | 1003 pg_catalog | pg_namespace | 210 | 122 | 14 | 118 | 841 | 145 pg_catalog | pg_rewrite | 659 | 83 | 573 | 62 | 1757 | 161 pg_catalog | pg_statistic | 2342496 | 25301064 | 2317015 | 2452817 | 732310 | 48825 pg_catalog | pg_trigger | 2495 | 0 | 2085 | 0 | 7367 | 697 pg_catalog | pg_db_role_setting | 0 | 1 | 0 | 1 | 0 | 1 (19 rows)
First I tested vacuum only on the big tables
VACUUM full pg_largeobject; VACUUM full pg_class ; VACUUM full pg_attribute; VACUUM full pg_depend ; VACUUM full pg_depend_reference_index ; VACUUM full pg_index;
But then select count(*) from information_schema.tables started to slow down again.
--select format('vacuum FULL verbose %I.%I;', n.nspname::varchar, t.relname::varchar) FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace WHERE t.relkind = 'r' and n.nspname::varchar = 'pg_catalog' order by 1
Then I did vacuum all tables in pg_catalog and then "select count(*) from information_schema.tables;" is seems to be fast while running the background job.
SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname = 'pg_catalog' ORDER BY pg_relation_size(C.oid) DESC LIMIT 20; relname | pg_size_pretty ----------------------------------+---------------- pg_largeobject | 4624 MB pg_statistic | 76 MB pg_attribute | 61 MB pg_largeobject_loid_pn_index | 42 MB pg_attribute_relid_attnam_index | 13 MB pg_depend | 12 MB pg_class | 9664 kB pg_attribute_relid_attnum_index | 9376 kB pg_type | 7632 kB pg_depend_reference_index | 6592 kB pg_depend_depender_index | 6576 kB pg_index | 4184 kB pg_proc | 3512 kB pg_constraint | 3336 kB pg_statistic_relid_att_inh_index | 3200 kB pg_class_relname_nsp_index | 2568 kB pg_type_typname_nsp_index | 2000 kB pg_shdepend | 1960 kB pg_attrdef | 1800 kB pg_rewrite | 1392 kB (20 rows)
select schemaname ,relname ,n_tup_ins ,n_tup_upd ,n_tup_del ,n_tup_hot_upd ,n_live_tup ,n_dead_tup from pg_stat_all_tables where n_dead_tup > 0 and schemaname='pg_catalog' ; schemaname | relname | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup ------------+--------------------+-----------+-----------+-----------+---------------+------------+------------ pg_catalog | pg_default_acl | 6 | 2 | 1 | 2 | 17 | 10 pg_catalog | pg_shdepend | 10995081 | 53 | 10975244 | 0 | 33155 | 2296 pg_catalog | pg_type | 24822122 | 4558 | 24610467 | 300 | 41907 | 6773 pg_catalog | pg_attribute | 183035424 | 13549029 | 181183256 | 8326103 | 424171 | 13615 pg_catalog | pg_proc | 1406 | 1340 | 1187 | 1122 | 6551 | 1351 pg_catalog | pg_class | 30278894 | 8510613 | 30021642 | 5918109 | 50712 | 1169 pg_catalog | pg_authid | 50 | 7 | 10 | 7 | 887 | 30 pg_catalog | pg_auth_members | 39 | 0 | 1 | 0 | 860 | 2 pg_catalog | pg_database | 0 | 0 | 0 | 0 | 6 | 4 pg_catalog | pg_sequence | 5101683 | 5100683 | 5087311 | 5045867 | 3250 | 507 pg_catalog | pg_shdescription | 0 | 0 | 0 | 0 | 11 | 8 pg_catalog | pg_attrdef | 6859893 | 0 | 6683508 | 0 | 3973 | 256 pg_catalog | pg_constraint | 56521 | 4 | 42635 | 0 | 9317 | 1782 pg_catalog | pg_depend | 89542906 | 8 | 88834333 | 0 | 212177 | 2024 pg_catalog | pg_description | 3561 | 4478 | 3528 | 3745 | 8259 | 967 pg_catalog | pg_index | 12360169 | 262429 | 12220954 | 258746 | 23660 | 69 pg_catalog | pg_namespace | 210 | 122 | 14 | 118 | 841 | 146 pg_catalog | pg_operator | 0 | 0 | 0 | 0 | 840 | 20 pg_catalog | pg_rewrite | 659 | 83 | 573 | 62 | 1757 | 161 pg_catalog | pg_statistic | 2346816 | 25301535 | 2317159 | 2453015 | 144622 | 475 pg_catalog | pg_trigger | 2495 | 0 | 2085 | 0 | 7367 | 697 pg_catalog | pg_db_role_setting | 0 | 1 | 0 | 1 | 4 | 4 pg_catalog | pg_extension | 0 | 0 | 0 | 0 | 10 | 6 pg_catalog | pg_init_privs | 0 | 0 | 0 | 0 | 180 | 1 (24 rows)
And that solved the simple count sql.
BUT "psql -h dbhost -p 5432 -U postgres dbname" login is still becomes slow after a while when running code that creates a lot of unlogged tables in 16 threads.
When I kill the test job it is instantly fast again
What seems to take time was this call triggered by psql (I could not find anything find else related for instance related to this locks)
EXPLAIN ANALYZE SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,6)='pg_sta' AND pg_catalog.pg_table_is_visible(c.oid) UNION SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,6)='pg_sta' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1 UNION SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,6)='pg_sta' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1 LIMIT 1000
Here is slow one https://explain.depesz.com/s/x2Vf the app is running
After the killing the application is fast https://explain.depesz.com/s/h4fK
We also tried to change the code to do this in 2 steps. - First create table - Then insert data into table
But that does not help on login either the time vary from 30 secs to 75 sec. https://explain.depesz.com/s/4SXl
There is no iowait the server the CPU load is 25%, the problem seems to be related to parallel_workers
max_parallel_workers_per_gather --------------------------------- 2 max_parallel_workers ---------------------- 8 max_worker_processes ---------------------- 8
So if we change max_parallel_workers_per_gather = 0
Then https://explain.depesz.com/s/kMEm query is fast.
Thanks for help everybody seems like we have to dig into the parallel_workers world.
(have to wait to test that until we can restart postgres) Lars |
