Re: DB running out of memory issues after upgrade
On Tue, Feb 18, 2020 at 1:10 PM Nagaraj Raj wrote: > > Hi Merlin, > > Its configured high value for max_conn, but active and idle session have > never crossed the count 50. > > DB Size: 20 GB > Table size: 30MB > RAM: 16GB > vC: 4 > > > yes, its view earlier I posted and here is there query planner for new actual > view, > > "Append (cost=0.00..47979735.57 rows=3194327000 width=288)" > " -> Seq Scan on msghist (cost=0.00..15847101.30 rows=316270 > width=288)" > " -> Seq Scan on msghist msghist_1 (cost=0.00..189364.27 rows=31627000 > width=288)" Database size of 20GB is not believable; you have table with 3Bil rows, this ought to be 60GB+ mill+ all by itself. How did you get 20GB figure? merlin
Re: PostgreSQL 11 higher Planning time on Partitioned table
Hi Justin, >I didn't hear how large the tables and indexes >are.+---+--++| > table_name | pg_relation_size | >pg_total_relation_size - pg_relation_size >|+---+--++| > TransactionLog_20200213 | 95646646272 | 4175699968 > || TransactionLog_20200212 | > 95573344256 | 4133617664 || >TransactionLog_20200211 | 91477336064 | 3956457472 > || TransactionLog_20200210 | > 819200 | 354344960 || >TransactionLog_20200214 | 6826672128 | 295288832 > || TransactionLog_20200220 | > 1081393152 | 89497600 || >pg_catalogpg_attribute | 3088384 | 2220032 > || TransactionLog_20190925 | > 1368064 | 90112 (174 such partitions) >|+---+--++ > > Do you mean that a given query is only going to hit 2 partitions ? Or do >you> mean that all but the most recent 2 partitions are "archival" and won't be > needed by future queries ? Yes all queries will hit only 2 partitions (e.g. if we do daily partition, queries will hit only today's and yesterday's partition). > You should determine what an acceptable planning speed is, or the best > balance> of planning/execution time. Try to detach half your current > partitions and, if> that gives acceptable performance, then partition by > day/2 or more. You could> make a graph of (planning and total) time vs > npartitions, since I think it's> likely to be nonlinear.> I believe others > have reported improved performance under v11 with larger> numbers of > partitions, by using "partitions of partitions". So you could try> making > partitions by month themselves partitioned by day. FYI, these are the observations I am getting with various number of partition and a multilevel partition with respect to Un-Partitioned.+---+--++---+--+---+--+---+--+| Testcase | Partition Count | Records in | Select | Select | Update | Update | insert | insert || | | each Partition | planning (ms) | execute (ms) | planning (ms) | execute (ms) | planning (ms) | execute (ms) |+---+--++---+--+---+--+---+--+| Single Level | 6 | 1000 | 1.162 | 0.045 | 2.112 | 0.115 | 1.261 | 0.178 || Partition | 30 | 1000 | 2.879 | 0.049 | 5.146 | 0.13 | 1.243 | 0.211 || | 200 | 1000 | 18.479 | 0.087 | 31.385 | 0.18 | 1.253 | 0.468 |+---+--++---+--+---+--+---+--+| Multi Level | 6 Partition having | 1000 | 3.6032 | 0.0695 | x | x | x | x || Partition | 30 subpartition each | | | | | | | |+---+--++---+--+---+--+---+--+| UnPartitioned | NA | 430 Million | 0.0875 | 0.0655 | x | x | x | x |+---+--++---+--+---+--+---+--+ > If you care about INSERT performance, you probably need to make at least a> > single partition's index fit within shared_buffers (or set shared_buffers > such> that it fits). Use transactions around your inserts. If your speed is > not> limited by I/O, you could further use multiple VALUES(),() inserts, or > maybe> prepared statements. Maybe synchronous_commit=off.> > If you care > about (consistent) SELECT performance, you should consider> VACUUMing the > tables after bulk inserts, to set hint bits (and since> non-updated tuples > won't be hit by autovacuum). Or
Re: PostgreSQL 11 higher Planning time on Partitioned table
> IF txnid is real UUID , then you can test the > https://www.postgresql.org/docs/11/datatype-uuid.html performance> see > https://stackoverflow.com/questions/29880083/postgresql-uuid-type-performance> > imho: it should be better. Sure, thanks Imre Thanks and Regards, Ravi Garg On Sunday, 23 February, 2020, 09:49:00 pm IST, Imre Samu wrote: > ... txid character varying(36) NOT NULL, > ... WHERE txnid = 'febd139d-1b7f-4564-a004-1b3474e51756'> There is only one > index (unique index btree) on 'txnID' (i.e. transaction ID) character > varying(36). Which we are creating on each partition. IF txnid is real UUID , then you can test the https://www.postgresql.org/docs/11/datatype-uuid.html performancesee https://stackoverflow.com/questions/29880083/postgresql-uuid-type-performanceimho: it should be better. best, Imre Ravi Garg ezt írta (időpont: 2020. febr. 23., V, 11:57): Hi Justin, Thanks for response. Unfortunately we will not be able to migrate to PG12 any time soon. - There is only one index (unique index btree) on 'txnID' (i.e. transaction ID) character varying(36). Which we are creating on each partition. - Our use case is limited to simple selects (we don't join with the other tables) however, we are expecting ~70 million records inserted per day and there would be couple of updates on each records where average record size would be ~ 1.5 KB. - Currently we are thinking to have Daily partitions and as we need to keep 6 months of data thus 180 Partitions.However we have liberty to reduce the number of partitions to weekly/fortnightly/monthly, If we get comparable performance. - We need to look current partition and previous partition for all of our use-cases/queries. Can you please suggest what sort of combinations/partition strategy we can test considering data-volume/vacuum etc. Also let me know if some of the pg_settings can help us tuning this (I have attached my pg_settings). Thanks and Regards, Ravi Garg, Mob : +91-98930-66610 On Sunday, 23 February, 2020, 03:42:13 pm IST, Justin Pryzby wrote: On Sun, Feb 23, 2020 at 09:56:30AM +, Ravi Garg wrote: > Hi, > I am looking to Range Partition one of my table (i.e. TransactionLog) in > PostgreSQL 11.While evaluating query performance difference between the > un-partitioned and partitioned table I am getting huge difference in planning > time. Planning time is very high on partitioned table.Similarly when I query > by specifying partition name directly in query the planning time is much less > **0.081 ms** as compared to when I query based on partition table (parent > table) name in query, where planning time **6.231 ms** (Samples below). That's probably to be expected under pg11: https://www.postgresql.org/docs/11/ddl-partitioning.html |Too many partitions can mean longer query planning times... |It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher as more partitions are added > There are around ~200 child partitions. Partition pruning enabled.PostgreSQL > Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 > 20150623 (Red Hat 4.8.5-39), 64-bit How large are the partitions and how many indexes each, and how large are they? Each partition will be stat()ed and each index will be open()ed and read() for every query. This was resolved in pg12: https://commitfest.postgresql.org/21/1778/ -- Justin
LDAP with TLS is taking more time in Postgresql 11.5
Hi All, We have recently upgraded our postgres servers from 9.4 version to 11.5 version. Post upgrade we are see delay in authentication. Issue is when we are using ldaptls=1 the authentication takes 1 second or greater than that. But if I disable ldaptls it's getting authenticated within milliseconds. But in 9.4 even if I enable ldaptls it's getting authenticated within milliseconds any idea why we are facing the issue? Regards, Mani.
Re: LDAP with TLS is taking more time in Postgresql 11.5
On 2/24/20 11:50 AM, Mani Sankar wrote: Hi All, We have recently upgraded our postgres servers from 9.4 version to 11.5 version. Post upgrade we are see delay in authentication. Issue is when we are using ldaptls=1 the authentication takes 1 second or greater than that. But if I disable ldaptls it's getting authenticated within milliseconds. But in 9.4 even if I enable ldaptls it's getting authenticated within milliseconds any idea why we are facing the issue? This is going to need a good deal more information: 1) OS the server is running on and did the OS or OS version change with the upgrade? 2) How was the server installed from packages(if so from where?) or from source? 3) The configuration for LDAP in pg_hba.conf. 4) Pertinent information from the Postgres log. 5) Pertinent information from the system log. Regards, Mani. -- Adrian Klaver [email protected]
