0

One of our Aurora PostgreSQL database's(48 cores) CPU usage stuck at 100% for nearly 8 hours. These situation happened 3 times in the past 4 months. We are using AWS's business support plan, in the past few month, we worked closely with the support team to diagnose the issue, but still haven't found what caused the problem. Thus I post this problem in the forum, any advice is very much welcome.

The Problems

Problem 1: As mentioned above, the most obvious appearance of these problem is database's CPU stuck at 100% for a vary long time, then suddenly back to normal without intervention, as we can see from the two screenshot below(the second image is a screenshot from the database's Performance Insight panel): enter image description here

enter image description here

Problem 2: When this incident happens, several empty tables that server as FIFO queues in our service, takes enormous time (roughly 500 ms) to execute a simple query:{code}select * from order by id limit 1;{code} in normal circumstances, a query like might just take several ms. At that time, I check the auto_vacuum execution about these tables, seemed that auto_vacuum service run normally, but every time when it ran, failed to retrieve table's dead_tuple. Then I executed +vacuum + manually, still failed to retrieve dead_tuple.

What we have done

The vacuum problem

The vacuum problem is relatively easier, as Laurenz kindly pointed out, the reason why vacuum not working is there're long running queries.

The CPU problem

After the situation occurred, we check the database's log, didn't found any dead lock or slow query, also during or before that 8 hours, the traffic to our service remain as normal.

From the second image above, we can see that an +update+ SQL take a lot database's resource. AWS's support suspected that this SQL is what responsible for high CPU usage, as it might locked the table. We disagreed, for reasons that:

  • this SQL only locks a row
  • the transaction this SQL belongs to normally takes 3ms to 10 ms to execute, given no huge traffic and the databases uses 48 cores, it's hard to believe this amount of traffic can have the database stuck for such a long time

Also, CPU became busier way before the vacuum situation occurred, the images below show the relationship between max transaction ID and CPU usage(the first image used UTC time zone, the second used UTC+8, which I added according UTC time below, I'll use UTC in this question):

enter image description here

as it shows, the CPU usage steadily grown since 10/12 20:00(a small peak around 10/12 16:00 was we running a background job):

  • from 10/12 20:00 to 10/13 00:00, CPU usage grows from 40% to nearly 100%, however, during that time, auto_vacuum works fine, the max transaction ID never exceeded 200 million
  • after 10/13 00:00, auto_vacuum stopped working, and since then CPU stuck at 100%. But CPU usage back to normal at around 10/13 04:00 (we didn't intervene at that time)
  • after 10/13 4:00, max transaction ID kept growing, now we assume, as mentioned above, is because there're long running transactions.
  • at about 10/13 14:30, max transaction ID back to normal, after we reboot the database

What's next

After few months' effort but still didn't found the root cause, AWS's support team give us following advices:

  • change database's log_statement to ALL, so that when the incident happens again, can trace back what SQLs were executing
  • clean up long queries regularly
  • avoids uncommitted transactions
  • monitoring database's XID
0

There is a long running query that keeps your CPU busy and keeps autovacuum from cleaning up dead tuples. A long queries implies a long transaction.

The query takes long because of all the dead tuples.

Kill the long running query with pg_terminate_backend, and things will go back to normal.

To prevent long running queries, set statement_timeout.

  • Thank you for your reply Laurenz, our conjecture regarding why vacuum not working is the same as yours. My apology that I failed to point this out in the question, I'll update my question. May I ask do you have any though what might cause CPU usage raised, because the CPU started to soar even when vacuum works properly, I'll attach another image in my question which demonstrates that. – chengevo Oct 22 at 13:55
  • 1
    I thought that was obvious - you have one or more long running queries. Consult pg_stat_activity to find out what these queries are and tune them to be faster or make sure that no such queries are launched. If you have identified such a query but don't know what to do, open a new question that contains at least the query and the EXPLAIN output for it. – Laurenz Albe Oct 22 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.