Postgresql – High Postgres-RDS CPU usage after migration

amazon-web-servicesmigrationpostgresqlrds

We deployed an update of our software today (3 EC2 instances) and made a migration to our RDS (db.t2.medium, Postgres) which added one column to a table with ~15 rows. Before migrating, we had a CPU usage of around "2". After the migration the CPU usage increased to "60". After a db reboot, it dropped, but went up again.

As a sidenote: After migration we tried to connect using a GUI and it hanged while SSL-verification. We restartet the GUI and it worked again.

Any advice on this? Here is a screenshot with current metrics. You can clearly see the increase in usage.

We currently use PostgreSQL 9.6.2!

Best Answer

We fixed this ourselves:

The problem was, we didn't shut down our background service, which made several transactions while updating our software and the db. These transactions polluted the database. We found these transactions using this snippet:

SELECT pid, age(query_start, clock_timestamp()), usename, query,state 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

After flushing our redis queue and restarting the background services, the CPU went down to 2% again.

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Mysql – Why the time taken to fetch results of a query for 10,000 times from a RDS is so uneven? (experiment)

I noticed you're using the db.micro instance. As with EC2, the micro instances are designed to be budget-friendly, but at a cost of performance. That said, you'll get a much worse performance when loading these types of servers versus the normal instances because the CPU time is given to the instance "last" compared to other instances sharing the same hardware.

To prove the point, run your tests again against a db.medium instance and you'll find it much more consistent.

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

Mysql – Why the time taken to fetch results of a query for 10,000 times from a RDS is so uneven? (experiment)

Related Topic