Re: QUERY: autovacuum: VACUUM ANALYZE table versus QUERY: autovacuum: VACUUM table
On 11/1/19 7:49 PM, Jason Ralph wrote: Hello list, I am trying to find out if the naming convention from autovacuum does what its command line equivalent does, or at least what I think it does. QUERY: autovacuum: VACUUM ANALYZE table versus autovacuum: VACUUM ANALYZE table. The above are the same, I'm guessing you are referring to 1) and 2) below. I have been getting my autovacuum tuned and I have the settings where I see 2 types of queries running now and doing their jobs. I know vacuum alone will mark dead tuples ready for use again, and analyze alone will update statistics for the query planner. 1. QUERY: autovacuum: VACUUM ANALYZE table 2. QUERY: autovacuum: VACUUM table My question is do we need both? Why wouldn't QUERY: autovacuum: VACUUM ANALYZE table perform both, I always thought when issuing VACUUM ANALYZE table it would do both. Why wouldn't we just want vacuum analyze to run? Or is QUERY: autovacuum: VACUUM ANALYZE table different from the command line and it only analyzes and QUERY: autovacuum: VACUUM table only vacuums which would make sense. See here: https://www.postgresql.org/docs/11/routine-vacuuming.html#AUTOVACUUM "Each worker process will check each table within its database and execute VACUUM and/or ANALYZE as needed" In same section read sections on vacuum and analyze thresholds, which determine the 'and/or' above. The commands are the same. Autovacuum is just a background process that calls the regular VACUUM/ANALYZE commands as needed. Thanks as always and hope this is clear. Jason Ralph This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -- Adrian Klaver adrian.kla...@aklaver.com
Re: QUERY: autovacuum: VACUUM ANALYZE table versus QUERY: autovacuum: VACUUM table
Jason Ralph writes: > I am trying to find out if the naming convention from autovacuum does what > its command line equivalent does, or at least what I think it does. > QUERY: autovacuum: VACUUM ANALYZE table versus autovacuum: VACUUM ANALYZE > table. > I have been getting my autovacuum tuned and I have the settings where I see 2 > types of queries running now and doing their jobs. I know vacuum alone will > mark dead tuples ready for use again, and analyze alone will update > statistics for the query planner. > 1. QUERY: autovacuum: VACUUM ANALYZE table > 2. QUERY: autovacuum: VACUUM table > My question is do we need both? Some autovacuum runs will do only VACUUM on a given table, some will do only ANALYZE, and some will do both. The decisions about which of these operations are needed are related but not identical (one's based on n_dead_tup and the other on n_mod_since_analyze, plus you can set different threshold parameters to compare those to). The pg_stat_activity report of what's happening does match the command-line syntax. regards, tom lane
RE: Getting following error in using cursor to fetch the records from a large table in c language(current transaction is aborted, commands ignored until end of transaction block)
Thanks Yes it is in c++. Actually we just written this code. Due to vaccum full cursor query failing on a connection and all the subsequent queries are failing and we found shared errors in /var/logs of the postgres installed machine. We also last query sent by the client application is: replicateDB=# select pid, state, backend_start, query_start, query from pg_stat_activity; pid | state | backend_start | query_start |query ---+---+---+---+- 18604 | idle in transaction (aborted) | 2019-11-01 13:18:07.919162+01 | 2019-11-01 13:23:19.92045+01 | BEGIN -Original Message- From: Francisco Olarte Sent: Friday, November 1, 2019 10:38 PM To: M Tarkeshwar Rao Cc: pgsql-gene...@postgresql.org Subject: Re: Getting following error in using cursor to fetch the records from a large table in c language(current transaction is aborted, commands ignored until end of transaction block) On Fri, Nov 1, 2019 at 1:44 PM M Tarkeshwar Rao wrote: > Getting following error in using cursor to fetch the records from a large > table in c language. Regarding this, "c language", I'll comment later > Can you please suggest why it is coming and what is the remedy for this. > Nov 1 13:21:54 sprintstd2 postgres[18604]: [10-1] < 2019-11-01 > 13:21:54.212 CET > ERROR: current transaction is aborted, commands > ignored until end of transaction block Nov 1 13:21:54 sprintstd2 > postgres[18604]: [10-2] < 2019-11-01 13:21:54.212 CET > STATEMENT: > BEGIN Nov 1 13:21:54 sprintstd2 postgres[18604]: [11-1] < 2019-11-01 > 13:21:54.324 CET > ERROR: current transaction is aborted, commands > ignored until end of transaction block Nov 1 13:21:54 sprintstd2 > postgres[18604]: [11-2] < 2019-11-01 13:21:54.324 CET > STATEMENT: > BEGIN Nov 1 13:21:54 sprintstd2 postgres[18604]: [12-1] < 2019-11-01 > 13:21:54.356 CET > ERROR: current transaction is aborted, commands > ignored until end of transaction block Nov 1 13:21:54 sprintstd2 > postgres[18604]: [12-2] < 2019-11-01 13:21:54.356 CET > STATEMENT: > BEGIN Nov 1 13:21:54 sprintstd2 postgres[18604]: [13-1] < 2019-11-01 > 13:21:54.360 CET > ERROR: current transaction is aborted, commands > ignored until end of transaction block Nov 1 13:21:54 sprintstd2 > postgres[18604]: [13-2] < 2019-11-01 13:21:54.360 CET > STATEMENT This seems incomplete, but I's telling you the cause. You had an error, you need to terminate the transaction before issuing a new one, i.e., do a commit ( which, IIRC, will rollback if the transaction is in error ) or rollback. > Sample Code snippet used As you stated C I cannot comment too much, but notice: > theCursorDec = (RWCString)"DECLARE " + mySqlCursor + " CURSOR FOR " > + theSql; > myFetchSql = "FETCH " + fetchStr + " IN " + mySqlCursor; Neither of these are C, they COULD be C++ > // Begin the cursor Same as this comment. > PQexec(connection, ,"BEGIN")) > PQexec(connection, ,"myFetchSql”) And these are definitely not C ( no ; ) and, if you generated them by editing, myfetchsql is quoted which smells fishy. I won't comment more until you confirm that is the real code, but anyway it seems to me you issue transaction start queries without properly terminating them with a transaction end one. Francisco Olarte
Re: Can you please suggest how to configure hot_standby_feedback?
I have a follow up question on *hot_standby_feedback*. If I have 3 read replicas that all have this set to ON, and someone runs a very long query on *one* of the replicas, can all three replicas fall behind until the query is finished? This is the behavior I observed more than once, but I can’t seem to be able to explain it based on the documented behavior of this parameter. A related question is — *how can you avoid doing auto vacuum during the busy peak hours, and do more aggressive auto vacuuming at night during low traffic?* Is that possible? I tried achieving that with a manual vacuum, but that causes all replicas to get far behind while it’s running, which is also not acceptable. Thanks so much! On Thu, Oct 31, 2019 at 1:02 AM Andreas Kretschmer wrote: > > > Am 31.10.19 um 06:21 schrieb M Tarkeshwar Rao: > > > > Can you please suggest how to configure hot_standby_feedback? > > > > > > turn it on if you want execute long running queries on the standby, keep > in mind it can lead to more bloat on the master. > > > Regards, Andreas > > -- > 2ndQuadrant - The PostgreSQL Support Company. > www.2ndQuadrant.com > > > > -- Konstantin Gredeskoul CEO & Principal Consultant ● ReinventONE Inc ● https://reinvent.one/ ● (415) 265-1054
Re: Can you please suggest how to configure hot_standby_feedback?
On Sat, Nov 2, 2019 at 8:52 PM Konstantin Gredeskoul wrote: > A related question is — *how can you avoid doing auto vacuum during the > busy peak hours, and do more aggressive auto vacuuming at night during low > traffic?* Is that possible? > > I tried achieving that with a manual vacuum, but that causes all replicas > to get far behind while it’s running, which is also not acceptable. > What is the main concern here? Disk IO? So we want to allow autovacuum workers to hit our disks less during busy hours and more during quiet hours, right? If so, then one of the ideas here could be the changing quotas for the autovacuum workers dynamically using cron or its alternative: 1) throttle autovacuum before peak hours, changing autovacuum_vacuum_cost_delay (or autovacuum_vacuum_cost_limit). The pre-12 default settings (_cost_delay 20ms and cost_limit 200) will give all the workers the shared "quota" that means, roughly, 8 MiB/s for reads, less for writes (good article: https://www.2ndquadrant.com/en/blog/autovacuum-tuning-basics/), and in 12 (where _cost_delay was reduced to 2ms) it will be ~80 MiB/s. Monitoring + logs analysis is needed to understand real IO though. Individually tuned tables will have individual quotas. 2) change those settings back to normal after busy hours. In any case, I highly recommend to monitor "autovacuum queue" -- the list of tables that should be already autovacuumed, but they are not, due to some reasons. Having proper graphs in monitoring and alerts if the size of this "queue" exceeds some reasonable number (say, 2 * autovacuum_max_workers) will help a lot to understand the autovacuum behavior for the concrete system. Here is an excellent example of how to do it, from Avito: https://github.com/avito-tech/dba-utils/blob/master/munin/vacuum_queue, and here is my version, with some improvements: https://gitlab.com/snippets/1889668.
