RDS: log vacuum messages

Last year I commented that “autovacuum log messages are still missing in [RDS] 9.4“.

Amazon fixed that for versions 9.4.5 and up with the custom GUC rds.force_autovacuum_logging_level.

I discovered this week that it’s also available on earlier versions of RDS – I have it enabled on a 9.3.10 instance. The default value in the config is one of the debug levels; just change it to ‘log’. No restart required (assuming you already have logging configured… and you do, don’t you?)

Works as expected:

2016-06-16 18:36:41 UTC::@:[31403]:LOG: automatic vacuum of table "my_db.public.my_dimension": index scans: 1
 pages: 0 removed, 29457 remain
 tuples: 3454 removed, 429481 remain, 0 are dead but not yet removable
 buffer usage: 64215 hits, 8056 misses, 22588 dirtied
 avg read rate: 1.018 MB/s, avg write rate: 2.855 MB/s
 system usage: CPU 0.10s/0.88u sec elapsed 61.80 sec

This makes me very happy – no more cron job to monitor vacuums!

This month’s RDS tip, a pgloader exercise, and bonus recruiter spam

1. Several months back, AWS re-worked the web console. You can get sparkline-style graphs for CPU usage, memory, storage, and database connections at the top level, instead of having to drill down to the Cloudwatch metrics for the instance.

I find this really handy – but for one quirk with the graphs:

There’s white space to the right of the red line. Therefore, I was interpreting the red line to mean “trouble is brewing & I should do something about this”.

Turns out that red line is the limit; there’s nowhere else to go. Whoops!

2. This week I finally had a reason to try out pgloader: One of my analysts needed some help loading some ugly fixed-width data¹.

Installing was super-easy on my mac (`brew install pgloader`).  I worked through the examples before starting to work with my actual dataset².

The data to be loaded came bundled as several text files: the data, plus three or four additional files describing the layout.  I wrote a truly glorious string of cut, sed, paste, and awk to create a pgloader control file that would work. And it did!

Except:

field1 | field2 | field3 
--------+--------+----------
 [null] | D | IMITRI
 [null] | G | ABRIELLE
 [null] | M | ARK
 [null] | S | ELENA

Uh, what?

The data descriptions had character counts starting at 1, and pgloader expects them to start at 0 (as they should).  (For extra fun, the first column in all records of this dataset was nothing but spaces.)

3. This week’s hilarious recruiter spam:
“Hey there Gabrielle! I was doing my homework on sites like Meetup, GitHub, etc. and I noticed your Java skills.”

I’m pretty sure you didn’t.

—

1 – Is that redundant?
2 – 600 columns of fixed-width data?  Who does that?!
3 – Why couldn’t it all be in one file? Again: who does this?!⁴
4 – People who hate DBAs, that’s who.

RDS OS update

I’ve some more info for the AWS RDS OS update I mentioned last week.

The announcement states that this update will take an outage. In my experience, it’ll be the usual failover time for a Multi-AZ instance (a minute or so), and 8-10 minutes for no-Multi-AZ instances. According to my event logs, the entire update process takes 20-25 minutes.

Since I had n > 1 instances to handle, I preferred to review & schedule updates via the cli, because I can then write a quick bash wrapper for each piece & handle them in bulk.

Here’s the command:

aws rds describe-pending-maintenance-actions

To review upcoming maintenance for only specific instances, pass it a comma-separated list of instance names to the filters switch, like so:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb1,mydb2

Here’s an example of what you’ll get back:

PENDINGMAINTENANCEACTIONS arn:aws:rds:[az]:[acct_no]:db:[dbname]
PENDINGMAINTENANCEACTIONDETAILS system-update 2016-02-26T19:41:22Z 2016-02-26T19:41:22Z Performance improvements and security updates

(The timestamps are in UTC, so you may have to do some math.)

Neither of those timestamps were in my maintenance window, so I dug into the docs  a bit further.

There are three possible dates available:
AutoAppliedAfterDate – AWS will apply the maintenance in the next maint window after this date
ForcedApplyDate – AWS will apply the maintenance at this time, regardless of maintenance window (eeeek!)
CurrentApplyDate – When AWS will apply it, if you’ve opted-in.

Without field headers, I can’t really figure out which two of the possible three dates are displayed. That “ForcedApplyDate” is a bit concerning, so I used the ‘output to JSON’ option to get some more info:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb1 \
--output json

(excerpt):

"PendingMaintenanceActionDetails": [
  {
    "Action": "system-update",
    "Description": "Performance improvements and security updates",
    "CurrentApplyDate": "2016-02-26T19:41:22Z",
    "AutoAppliedAfterDate": "2016-02-26T19:41:22Z",
    "OptInStatus": ""
  }
],

So that clears that up, a little. Now to get this stuff scheduled!

aws rds apply-pending-maintenance-action \
--resource-identifier arn:aws:rds:[region]:[account]:db:[db-name] \
--apply-action system-update \ 
--opt-in-type next-maintenance

Since I now know which fields I want, I can use ‘output as text’ to verify my scheduling action, filtered with the –query switch:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb1 \
--output text \
--query 'PendingMaintenanceActions[*].{OptIn:PendingMaintenanceActionDetails[*].OptInStatus,Date:PendingMaintenanceActionDetails[*].CurrentApplyDate}'

Output:

DATE 2016-01-22T17:04:00Z
OPTIN next-maintenance

(If you are wondering “how in Sam Hill did you create that query filter from that JSON output?”, wonder no more: here’s a link to the docs.  Getting the –query filter correct usually takes some back & forth with reviewing the JSON output, so don’t be discouraged if it takes a few tries.)

Scale 14x recap

I went to SCALE mainly for PostgreSQL@Scale – Joe Conway’s grown this annual add-on into a two-day, two-track event.

The new venue is a huge improvement, even though the walk from the hotel was a bit longer than I expected. There are a lot more accessible-on-foot food options.

—

For my session, I gave an updated version of last year’s “RDS Postgres: A Journey down the Amazon”.

If you’re using RDS and aren’t keeping up with your monitoring, please be aware of the current required OS update.  You can view & schedule required updates via the web console; here are the corresponding cli commands:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb,mydb1,mydb2

aws rds apply-pending-maintenance-action \
--resource-identifier arn:aws:rds:[region]:[account]:db:[db-name] \
--apply-action system-update \ 
--opt-in-type next-maintenance

I find JSON and text output more useful for these types of tasks; I’ll cover that in a later post.

Accessing RDS Pg logs is kind of a sticking point for some people, me included. (I _really_ want an easy way to get them into Splunk.)
Here’s Denish Patel’s work using the rds cli, and a quick bash script I threw together, which uses the aws cli.

Based on the quick headcount I took at the beginning of the session, attendees were about 2/3 devs, 1/3 DBAs. We also had a couple of people from the Amazon RDS team; it’s always nice to know I have backup in the audience! ;)

A couple of folks had questions after my talk and I didn’t catch up with them in the booth. If that is you, feel free to leave a comment here, or email me.

—

Other talks I attended:
Jim Mlodgenski’s Debugging PL/PgSQL contained some good tips on use of RAISE NOTICE and what not to do. I installed pl_profiler and started using it the next afternoon.

Xof’s JSON Home Improvement included advice on when to use JSON vs JSONB, or maybe even neither :) If you’ve been reading my blog for any length of time, you know that these kinds of discussions just end in me making plans for more benchmarking…

Peter Geoghegan’s UPSERT use cases  covered a good range of material from beginning to “We can do WHAT now?” and made me realize I can always improve my SQL skills.

I’ve been hearing about ToroDB  but hadn’t attended one of Alvaro’s talks yet. Very basically, it’s a Pg-backed Mongo interface. They take the json data and explode it out into sub-documents. Then there’s a table that stores the structure _in json_. It both blew my mind & had me smacking my forehead “of course!”

It is in alpha, so this applies.
—

Slides for talks are (or will be) on the Pg wiki.  We do rely on speakers to load their own slides, so if you are after something in particular, please ask & I will remind them. :)

—

I’m already looking forward to next year, and hope I get to go ice skating then!

SCALE 13x and “You are not the database Superuser”

Another followup from my talk at SCALE 13x.

This isn’t to pick on RDS in particular; you’ll likely have these or similar issues with other “database as managed service” options.

Specific problems you should be aware of:

1a – As I discussed in my post about upgrading an RDS db to Pg 9.4, you can’t pg_dumpall; access to roles & credentials is restricted.
1b – … which means you can’t extract roles & credentials. (I’m told the RDS devs are working on a way to handle this.)

2 – Lots of in pg_stat_activity. You can’t see what the actual superuser is doing, of course; so far this is an annoyance more than a true problem, because I wouldn’t be able to smash a query the actual superuser is running, anyway.

3 – You can’t VACUUM a database, only individual tables.

4 – Oops: “FATAL: remaining connection slots are reserved for non-replication superuser connections”. This does not include you :), so you are effectively locked out of your instance. You can try calling Amazon support for this; my experience with this was that even a request for information about existing connections had to be escalated, and I was told they wouldn’t terminate connections for me. So you’re looking at killing them from the app side, or restarting the instance (with a higher max_connections, perhaps). This makes troubleshooting this issue particularly challenging.

5 – If you’re fond of using SET ROLE [rolename] to troubleshoot, you have to do the extra step of adding yourself to the role:
gabrielle=> SET ROLE db_rw;
ERROR: permission denied to set role "db_rw"
gabrielle=> GRANT db_rw TO gabrielle ;
GRANT ROLE
gabrielle=> SET ROLE db_rw;

RDS: Three weeks in

I’ve spent the past few weeks learning my way around Amazon’s RDS offering (specifically Postgres, and a bit of elasticache). It’s a mixed bag so far; for every feature I think “Hey, this is neat!” I find at least one or two others that are not so thrilling.

One of the things that may annoy you if you’re used to running your own Pg server is not having “real” superuser access to your own cluster. There’s an rdsadmin database which you won’t have access to, and a couple of rds users as well. This is part of Amazon’s security implementation to protect all their users from destroying each other.

You need to exclude the rdsadmin database out of any management queries you run, like so:
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
WHERE datname NOT IN ('postgres','template0','template1', 'rdsadmin')
ORDER BY datname;

Otherwise you’ll get a permissions error.

Today, I had this bit of fun:
pg_dumpall --globals-only
ERROR: permission denied for relation pg_authid

So no dumping your roles. (No pg_dumpall, period.) I don’t have high hopes for an alternative.

I also haven’t found a place to report bugs – the “Feedback” button only allows you to contact tech support if you have a contract. So far I’m relying on the “I know someone who knows someone” method and the RDS user forums; AWS team members respond fairly quickly to posts there.

Aside from all that, I’m primarily interested in automating my instance management, so I’ve focused on the CLI tools.

My first thought was “Egads, I can’t get away from Java, can I”, but installing the toolkit turned out to be the easiest part. I set my $JAVA_HOME as outlined here to avoid that annoying “Unable to find $JAVA_HOME” error.

The CLI support is much more extensive than I expected – you can manage pretty much everything, very easily, once you find your way around and learn the appropriate incantation. Which is my biggest complaint: the docs on the web and the cli help don’t always match each other, and sometimes neither are correct. It cost me a significant amount of startup time flipping back and forth between the cli help, the web help, and just flat-out experimenting and cursing that mysterious “Malformed input” error message. (I probably have unrealistically high standards after working with the Pg docs for so many years.)

Fun stuff you can do:
RDS offers “event subscriptions” to help you keep tabs on your instance health (failover, storage, etc)¹. They’re pretty easy to configure from the web console, but once you’ve done so, there’s no way to view or edit them except from the CLI. (At least, not that I can find.)

You can grab your Cloudwatch metrics, if you need to “roll your own” and integrate them into an existing monitoring system. (Yes, I briefly considered this!)

Log files are also accessible through the CLI for watching or downloading. It’s a huge improvement on the tiny green-text-on-black background on the web console. There’s no glob expression matching, so you have to request them one at a time. If you request a log file that doesn’t exist, you don’t get an error.

log_line_prefix is one of the unconfigurable GUCs on RDS, so if you are planning to use pgbadger ², specify Amazon’s format as outlined at the bottom of this page.

—

1 – Be warned that restoring a snapshot will generate an “instance deleted” alert for your newly-restored instance. Your on-call person may not appreciate this.

2 – Alternatively, the pg_stat_statements extension is available on RDS, so you could get query stats this way.

PDXPUGDay Recap

Last weekend we held the biggest PDXPUGDay we’ve had in a while! 5 speakers + a few lightning talks added up to a fun lineup. About 1/3 of the ~50 attendees were in town for FOSS4G; I think the guy from New Zealand will be holding the “visitor farthest from PDXPUG” for a good long while. Some folks from SEAPUG daytripped down (hi!) and we made plans for PDXPUG to road trip up there, probably for next year’s LinuxFestNW.

My highlights:
HSTORE, XML, JSON, and JSONB – David Wheeler
– Pg’s XML features are pretty neat, but I still think XML needs to DIAF. Perhaps that’s just my previous experience speaking.
– We renamed the HSTORE containment operator (@>) to “ice cream cone operator”, courtesy Mark Wong.
– Operations on JSON are slower than on HSTORE. That’s interesting.
– The storage overhead for JSONB is higher than for regular JSON, because it doesn’t compress very well. Josh B took an audience vote on improving compression at the expense of slowing down operations, and it was pretty evenly split.
– As usual, David included benchmarks and gave good overviews of when to use which data type.

Snapshotted Data Versioning – Eric Hanson
Eric gave a talk about this at PDXPUG last year and was showing an updated version of what Aquameta’s up to. Eric’s philosophy is “make everything data, and then make a UI for it”.
– Implemented FUSE for Pg, bidirectional, so you can change your data by making updates directly in the database or by editing a text file on the filesystem. I believe this was described as “perverse” by a certain audience member.

Data Near Here – Veronika Megler
– Another update to a previous PDXPUG talk
– Scientists report that they spend up to 80% of their time just finding data relevant to their research. Not collecting – locating previously saved data. What a time sink.
– Parsers for each data format have to be custom coded.

Portal Update – Kristin Tufte
– Another example of pulling data from many different sources in many “unique” formats!
– Current research on pedestrian counts uses the crosswalk buttons as a potential method to count pedestrians.
– I’d like to get ahold of the traffic light data, to see if the light at 32nd and Powell really is the longest light in Portland, or if that’s just my imagination.

AWS Faceoff (Cloud Shootout!) – Josh Berkus
I don’t care too much about Postgres on AWS – if I’m going to go that route, I’ll buy my own hardware, TYVM.
– RDS has a limited number of extensions installed, and PL/R isn’t one of them.* They did just add pg_stat_statements, which is cool. The Amazon support people are taking requests, and are attentive to the community, according to Josh. (I don’t have enough experience with that to have an opinion.)
– performance on RDS just isn’t that great; Josh got 325 TPS read/write, and 1430 TPS read-only.
– Then there was the cost comparison; RDS and Heroku don’t look that great compared to hosting it yourself, but you’d need to factor in the cost of support staff there.

Thanks for a great event!

—
* I decided to see for myself what extensions were available. Mark warned me “don’t shed too many tears for what they don’t have”. To my surprise, many of my favorites are available – pgperl, plpgsql, postgis, and tablefunc! (SO EXCITE MUCH PIVOT)

Check what’s available on your instance with this command:
SHOW rds.extensions;

Note that “SELECT * FROM pg_available_extensions ORDER BY name;” will show you a bunch of stuff that’s not necessarily available on RDS. (Something I wish they’d fix.)