Re: Autoanalyze CPU usage
Perhaps consider running manual vacuum analyze at low load times daily if you have that opportunity. This may stop autovacuums from hitting thresholds during high load times or do the normal/aggressive autovacuum tuning to make it more aggressive during low load times and less aggressive during high load times. Sent from my iPad > On Dec 19, 2017, at 5:03 PM, Tomas Vondra > wrote: > > > >> On 12/19/2017 05:47 PM, Habib Nahas wrote: >> Hi, >> >> We operate an RDS postgres 9.5 instance and have periodic CPU spikes to >> 100%. These spikes appear to be due to autoanalyze kicking on our larger >> tables. >> >> Our largest table has 75 million rows and the autoanalyze scale factor >> is set to 0.05. >> >> The documentation I've read suggests that the analyze always operates on >> the entire table and is not incremental. Given that supposition are >> there ways to control cost(especially CPU) of the autoanalyze operation? >> Would a more aggressive autoanalyze scale factor (0.01) help. With the >> current scale factor we see an autoanalyze once a week, query >> performance has been acceptable so far, which could imply that scale >> factor could be increased if necessary. >> > > No, reducing the scale factor to 0.01 will not help at all, it will > actually make the issue worse. The only thing autoanalyze does is > running ANALYZE, which *always* collects a fixed-size sample. Making it > more frequent will not reduce the amount of work done on each run. > > So the first question is if you are not using the default (0.1), i.e. > have you reduced it to 0.05. > > The other question is why it's so CPU-intensive. Are you using the > default statistics_target value (100), or have you increased that too? > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: Batch insert heavily affecting query performance.
Are the inserts being done through one connection or multiple connections concurrently? Sent from my iPhone > On Dec 24, 2017, at 2:51 PM, Jean Baro wrote: > > Hi there, > > We are testing a new application to try to find performance issues. > > AWS RDS m4.large 500GB storage (SSD) > > One table only, called Messages: > > Uuid > Country (ISO) > Role (Text) > User id (Text) > GroupId (integer) > Channel (text) > Title (Text) > Payload (JSON, up to 20kb) > Starts_in (UTC) > Expires_in (UTC) > Seen (boolean) > Deleted (boolean) > LastUpdate (UTC) > Created_by (UTC) > Created_in (UTC) > > Indexes: > > UUID (PK) > UserID + Country (main index) > LastUpdate > GroupID > > > We inserted 160MM rows, around 2KB each. No partitioning. > > Insert started at around 3.000 inserts per second, but (as expected) started > to slow down as the number of rows increased. In the end we got around 500 > inserts per second. > > Queries by Userd_ID + Country took less than 2 seconds, but while the batch > insert was running the queries took over 20 seconds!!! > > We had 20 Lambda getting messages from SQS and bulk inserting them into > Postgresql. > > The insert performance is important, but we would slow it down if needed in > order to ensure a more flat query performance. (Below 2 seconds). Each query > (userId + country) returns around 100 diferent messages, which are filtered > and order by the synchronous Lambda function. So we don't do any special > filtering, sorting, ordering or full text search in Postgres. In some ways we > use it more like a glorified file system. :) > > We are going to limit the number of lambda workers to 1 or 2, and then run > some queries concurrently to see if the query performance is not affect too > much. We aim to get at least 50 queries per second (returning 100 messages > each) under 2 seconds, even when there is millions of messages on SQS being > inserted into PG. > > We haven't done any performance tuning in the DB. > > With all that said, the question is: > > What can be done to ensure good query performance (UserID+ country) even when > the bulk insert is running (low priority). > > We are limited to use AWS RDS at the moment. > > Cheers > >
Re: Advice on best way to store a large amount of data in postgresql
That’s crazy only having 8GB memory when you have tables with over 100GBs. One general rule of thumb is have enough memory to hold the biggest index. Sent from my iPad > On Jan 9, 2023, at 3:23 AM, spiral wrote: > > Hello, > > We have a table containing ~1.75 billion rows, using 170GB storage. > The table schema is the following: > > messages=# \d messages > Table "public.messages" >Column| Type | Collation | Nullable | Default > --+-+---+--+- > mid | bigint | | not null | > channel | bigint | | not null | > member | integer | | | > sender | bigint | | not null | > original_mid | bigint | | | > guild| bigint | | | > Indexes: >"messages_pkey" PRIMARY KEY, btree (mid) > > > This table is used essentially as a key-value store; rows are accessed > only with `mid` primary key. Additionally, inserted rows may only be > deleted, but never updated. > > We only run the following queries: > - INSERT INTO messages VALUES (...data...); > - SELECT * FROM messages WHERE mid = $1; > - DELETE FROM messages WHERE mid = $1; > - DELETE FROM messages WHERE mid IN ($1...$n); > - SELECT count(*) FROM messages; > > For the "IN" query, it is possible for there to be up to 100 > parameters, and it is possible that none of them will match an existing > row. > > So, the problem: I don't know how to best store this data in > postgres, or what system requirements would be needed. > Originally, this table did not contain a substantial amount of data, > and so I stored it in the same database as our CRUD user data. However, > as the table became larger, cache was being allocated to (mostly > unused) historical data from the `messages` table, and I decided to > move the large table to its own postgres instance. > > At the same time, I partitioned the table, with TimescaleDB's automatic > time-series partitioning, because our data is essentially time-series > (`mid` values are Twitter-style snowflakes) and it was said that > partitioning would improve performance. > This ended up being a mistake... shared_buffers memory usage went way > up, from the 20GB of the previous combined database to 28GB for just > the messages database, and trying to lower shared_buffers at all made > the database start throwing "out of shared memory" errors when running > DELETE queries. A TimescaleDB update did improve this, but 28GB is way > more memory than I can afford to allocate to this database - instead of > "out of shared memory", it gets OOM killed by the system. > > What is the best course of action here? > - Ideally, I would like to host this database on a machine with 4 > (Ryzen) cores, 8GB RAM, and tiered storage (our cloud provider doesn't > support adding additional local storage to a VPS plan). Of course, > this seems very unrealistic, so it's not a requirement, but the > closer we can get to this, the better. > - Is it a good idea to use table partitioning? I heard advice that one > should partition tables with above a couple million rows, but I don't > know how true this is. We have a table with ~6mil rows in our main > database that has somewhat slow lookups, but we also have a table > with ~13mil rows that has fast lookups, so I'm not sure. > > Thanks > spiral > >
Re: Postgres not using correct indices for views.
What a nice catch! Sent from my iPad On Aug 10, 2019, at 6:05 AM, Thomas Rosenstein wrote: >> [ re-adding list ] >> >> "Thomas Rosenstein" writes: On 9 Aug 2019, at 0:45, Tom Lane wrote: However ... it sort of looks like the planner didn't even consider the second plan shape in the "wrong" case. If it had, then even if it costed it 3X more than it did in the "right" case, the second plan would still have won out by orders of magnitude. So there's something else going on. Can you show the actual query and table and view definitions? >> >>> View definition: >>> SELECT l.id, >>> l.created_at, >>> ... >>> togdpr(l.comment) AS comment, >>> ... >>>FROM loans l; >> >> Ah-hah. I'd been thinking about permissions on the table and >> view, but here's the other moving part: functions in the view. >> I bet you were incautious about making this function definition >> and allowed togdpr() to be marked volatile --- which it will >> be by default. That inhibits a lot of optimizations. >> >> I'm guessing about what that function does, but if you could >> safely mark it stable or even immutable, I bet this view would >> behave better. >> >>regards, tom lane > > Yep that was IT! Perfect, thank you soo much! > > Why does it inhibit functionalities like using the correct index, if the > function is only in the select? > Could that still be improved from pg side? > > Thanks again! > >
Re: much slower query in production
Vacuum everything that you restored Sent from my iPhone > On Feb 26, 2020, at 1:19 PM, Michael Lewis wrote: > > >> UPDATE multicards >>SET defacements = COALESCE( count, 0 ) >> FROM ( SELECT multicard_uid, COUNT(*) AS count FROM tickets GROUP BY >> multicard_uid ) AS sub >> WHERE uid = multicard_uid OR multicard_uid is null; > > I expect this should work. Not sure of performance of course.
Re: When to use PARTITION BY HASH?
Wow! That is good to know! Sent from my iPad > On Jun 7, 2020, at 5:23 PM, David Rowley wrote: > >> On Sun, 7 Jun 2020 at 23:41, MichaelDBA wrote: >> The article referenced below assumes a worst case scenario for bulk-loading >> with hash partitioned tables. It assumes that the values being inserted are >> in strict ascending or descending order with no gaps (like a sequence number >> incrementing by 1), thereby ensuring every partition is hit in order before >> repeating the process. If the values being inserted are not strictly >> sequential with no gaps, then the performance is much better. Obviously, >> what part of the tables and indexes are in memory has a lot to do with it as >> well. > > In PostgreSQL 12, COPY was modified to support bulk-inserts for > partitioned tables. This did speed up many scenarios. Internally, how > this works is that we maintain a series of multi insert buffers, one > per partition. We generally only flush those buffers to the table when > the buffer for the partition fills. However, there is a sort of > sanity limit [1] on the number of multi insert buffers we maintain at > once and currently, that is 32. Technically we could increase that > limit, but there would still need to be a limit. Unfortunately, for > this particular case, since we're most likely touching between 199-799 > other partitions before hitting the first one again, that will mean > that we really don't get any multi-inserts, which is likely the reason > why the performance is worse for hash partitioning. > > With PG12 and for this particular case, you're likely to see COPY > performance drop quite drastically when going from 32 to 33 > partitions. The code was more designed for hitting partitions more > randomly rather than in this sort-of round-robin way that we're likely > to get from hash partitioning on a serial column. > > David > > [1] > https://github.com/postgres/postgres/blob/master/src/backend/commands/copy.c#L2569
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
Mladen, Shame on u lecturing a top notch guy in the PostgreSQL world, Laurenz Albe. I think Laurenz knows “a little bit” about Oracle having written the popular extension, fdw_oracle, among his many other contributions to the PG world. So ironic that Laurenz was just named “PostgReSQL person of the week”, and then has to be subjected to this “tirade” of yours! Follow the PG protocol in submitting your change requests to core PG and stop your Bitchin! Michael Vitale Sent from my iPad > On Oct 4, 2021, at 9:51 PM, Mladen Gogala wrote: > > >> On 10/4/21 02:34, Laurenz Albe wrote: >>> On Fri, 2021-10-01 at 15:06 -0500, Jeff Holt wrote: >>> TLDR; If I spend the time necessary to instrument the many functions that >>> are the equivalent >>> of the Oracle counterparts, would anyone pull those changes and use them? >>> Specifically, for those who know Oracle, I'm talking about implementing: >>>1. The portion of the ALTER SESSION that enables extended SQL trace >>>2. Most of the DBMS_MONITOR and DBMS_APPLICATION_INFO packages >>>3. Instrument the thousand or so functions that are the equivalent of >>> those found in Oracle's V$EVENT_NAME >>>4. Dynamic performance view V$DIAG_INFO >>> For the last 35 years, I've made my living helping people solve Oracle >>> performance problems by looking at it >>> >> [...] >>> Now looking closely at postgreSQL, I see an opportunity to more quickly >>> implement Oracle's current feature list. >> Anything that improves user experience in that respect is welcome, but >> consider >> that each database has different approaches to solve the same problems. >> >> Before you go to the length of implementing a lot of stuff, check in with >> the -hackers list and discuss your ideas. >> >> Please be a lot more specific than in this e-mail. While it is certainly >> fine to sketch your ambitios vision, focus on one specific thing you can >> imagine implementing and come up with a design for that. >> >> Note that "Oracle has it" is not a good enough reason for a PostgreSQL >> feature. We think we can do better than they do (at least in many respects). >> Also, don't assume that everyone on the -hackers list will be familiar with >> certain PostgreSQL features. >> >> One think that you should keep in mind is that Oracle has to provide >> different >> features in that area because they are not open source. In PostgreSQL, I can >> simply read the code or attach a debugger to a backend, and when it comes to >> profiling, "perf" works pretty well. So there is less need for these things. >> >> I don't want to discourage you, but contributing to PostgreSQL can be a >> lengthy >> and tedious process. On the upside, things that make it into core are >> usually >> fairly mature. >> >> Yours, >> Laurenz Albe > > Laurenz, you are obviously not aware who are you talking to. Let me introduce > you: Cary Millsap and Jeff Holt are authors of the "Optimizing Oracle for > Performance", one of the most influential books in the entire realm of > Oracle literature. The book describes the method of tuning Oracle > applications by examining where are they spending time and what are they > waiting for. The book can be found on Amazon and I would seriously advise you > to read it: > > https://www.amazon.com/Optimizing-Oracle-Performance-Practitioners-Response-ebook/dp/B00BJ9A8SU/ref=sr_1_1?dchild=1&keywords=Optimizing+Oracle+for+Performance&qid=1633395886&s=books&sr=1-1 > > Haughty lectures about "Oracle has it" not being good enough could hardly be > more out of place here. To put it as politely as is possible in this case, > shut your pie hole. What Jeff is asking for is not something that "Oracle > has", it's something that customers want. That was the case few years ago > when I was asking for the optimizer hints. I was castigated by the former > pastry baker turned Postgres guru and my reaction was simple: I threw > Postgres out of the company that I was a working for as the lead DBA. You > see, customer is always right, whether the database is open source or not. > Needless to say, Postgres has optimizer hints these days. It still has them > in "we do not want" part of the Wiki, which is hilarious. > > You see, without proper event instrumentation, and knowing where the > application spends time, it is not possible to exactly tune that application. > Oracle used to have a witchcraft based lore like that, where the performance > was estimated, based on buffer cache hit ratio, the famous "BCHR". That was > known as "Method C". The name comes from Cary's and Jeff's book. Jeff and > Cary are the ones who made the BCHR based black magic - obsolete. > > In other words, Jeff is asking for a method to fine tune the applications > with precision. Instead of being an arrogant person, you should have > given him the answer: > > https://github.com/postgrespro/pg_wait_sampling > > Postgres already has an extension which implements around 60%
Re: PostgreSQL and a Catch-22 Issue related to dead rows
You could always turn off vacuuming at the table level and then resume laterSent from my iPhoneOn Dec 9, 2024, at 6:03 AM, Lars Aksel Opsahl wrote: Hi When processing multiple simple feature layers through PostGIS Topology to perform overlays and eliminate small areas/slivers, we face a complex workflow. To manage the workload, we split the input into smaller jobs using the Content Balanced Grid. These jobs are then executed in parallel using Postgres Execute Parallel. In one case, we processed a total of 750 cells, with an overall runtime of 40 hours. However, one specific cell took over 12 hours to complete, most of which was spent on removing small areas by deleting edges in PostGIS Topology. The root cause of this delay is related to removal of dead rows. By introducing periodic COMMIT statements and VACUUM (FULL) operations, we managed to reduce the processing time for that single cell to approximately 3 hours. However, when we scaled this updated code to use 100 parallel threads, we encountered the expected “LWLock | SubtransControlLock” bottleneck, leading to an overall increase in runtime for all the cells. One issue is the inability to accurately estimate the size of a job before execution, making it difficult to optimize job splitting in advance. Currently, the only viable solution seems to be implementing a timer for each job. The job would terminate after a predefined time, committing its progress and returning control to the caller. The caller must then trigger new jobs to complete the remaining tasks until done. If there were a way to remove dead rows without requiring a commit from totally unrelated jobs, it would be much easier. The tables in use are unlogged and I already control the vacuum process from the caller. I can't use temp tables. Such a mechanism would also prevent unrelated long running jobs to cause problems for my jobs. To avoid that problem now, I have to move my jobs to a server not used by others also. Yes there are very good reason for the way removal for dead rows work now, but is there any chance of adding an option when creating table to disable this behavior for instance for unlogged tables ? Thanks Lars
