Name: Monitoring and Managing Autovacuum
Start: 2019-04-30T18:30:00-07:00
End: 2019-04-30T20:30:00-07:00
Location: 10326 Whalley Blvd

These slow queries are reported to me by my developers when they start taking minutes to hours to days, in their test and/or production databases. When they stop the application pr load up a fresh copy of the database, the same queries run in milliseconds. I have found that this is caused by the AutoVacuum/Analyze system or lack there of.

Here is a list of some of the symptoms that you could be seeing.

Small table that gets thousands of rows added and deleted. This table is then joined to a large table to view a set of results.

Effect: Your queries get slower and slower until a query that was 10 ms now takes 5 seconds or more, but if you start and stop your application, then the problem self fixes itself.

Large table with tens of millions of rows where you are deleting and adding millions of rows per hour.

Effect: You find that your queries are slowing down and when you look at your AutoVacuum it seems to be AutoVacuum this table 24 hours per day, but if you stop your application for a few day or dump and restore your database it fixes itself.

Large table with tens of millions of rows where you are deleting and then adding millions of rows per hour with the same index values inside of a transaction.

Effect: Your queries get slower and slower, but if you dump and restore your database, everything is fast again.

Speaker:
Lloyd Albin

I have been a PostgreSQL administrator since 2002 and have worked for Fred Hutchinson Cancer Research Center for the last 10+ years. Within Fred Hutch, I work for the largest group called SCHARP, Statistical Center for HIV/AIDS Research and Prevention.

In 2010 I started SeaPUG, Seattle Postgres Users Group, at the request of Josh Drake. I present at least half the presentations there every year. I started the PostgreSQL track at LinuxFest Northwest in 2014 after my GIS presentation in 2013 was standing room only. In 2017 I got a booth at the SeaGL, Seattle GNU Linux, conference with the idea of having a booth there in 2018 along with also doing a PostgreSQL presentation at the conference. In 2017 I started presenting at various PostgresConf Conferences.

I have also discovered several PostgreSQL bugs which have been fixed, some of them affected every version of PostgreSQL.

Bug numbers:

7553 - Variant of the what alias to use after a rename bug in views - Fixed in 9.3

8173 - Inserting heap tuples in bulk in COPY patch return wrong line on failure 999 out of 1000 times. - Fixed in 9.2.5 & 9.3

8257 - Multi-Core Restore fails when containing index comments - Fixed in 8.4, 9.0, 9.1, 9.2, 9.3

8291 - postgres_fdw does not re-read USER MAPING after change - Updated Docs in 9.3

8545 - pg_dump does not backup database level globals - Fixed in 11

15182 - Major Bug - Patch in the works for all versions of PostgreSQL aka
9.3 - 10. Partially fixed in 10 and fully fixed in 11

'15316 - Creation of check constraint functions that rely on data loaded alphabetically after your primary data will fail to restore properly.
I host the Seattle Postgres Users Group where I give many of the presentations.

Joshua Drake

Vancouver Postgres

PostgresWorld

Command Prompt

Redgate

Food, Facilities and Financial assistance

Command Prompt, Inc.

Technology

Open Source

Python

Database Professionals

Software Development

PostgreSQL

Web Development

Hadoop

Big Data

High Scalability Computing

Computer Programming

Data Mining

Data Science

Grant Zhou

Sam Zhi

Manish Kumar

Hadi Moshayedi

Ares Ray Vidalis

Lloyd Albin

Monitoring and Managing Autovacuum

10326 Whalley Blvd

Share

Vancouver Postgres

Monitoring and Managing Autovacuum

Vancouver Postgres

Details

Sponsors

Members are also interested in