Thursday, October 26, 2017

Amazon Aurora Postgres: First thoughts

Well, I have to say that this was a bit frustrating. I never actually got my database installed into Aurora Postgres because of some serious limits of Amazon's implementation. Once I found those limits, I found that they limited my operational flexibility to the point where, for my workload, it simply doesn't work.

The biggest limits are based on the fact that Aurora Postgres doesn't use a filesystem. Rather, Amazon has created a block-based back end for Postgres that allows clustered access to the data store. The data store itself, like EBS, is replicated for performance and redundancy. This has some interesting side effects. Postgres was built around the assumption that the filesystem cache was the primary block cache. You allocate a fairly limited amount of memory to the internal Postgres shared memory pool and leave the rest to be used by the filesystem block cache. Aurora Postgres, on the other hand, must assign that memory to the internal Postgres shared memory pool in order to serve as cache since there is no filesystem and thus no filesystem block cache. Unlike the filesystem block cache pool, Postgres jobs cannot take memory away from the internal shared memory pool in order to accomplish whatever task they are doing. The end result is that internal jobs that require a lot of memory can die with out of memory errors since there's not enough memory outside the Postgres shared memory pool to allocate for that job.

The other big limitation is that Aurora Postgres has limited space for handling large sorts or indexing operations. Regular Postgres uses a directory, pgsql_tmp, in a tablespace to store temporary heap results for sorts and indexes too big to fit in work_mem (which by default is 2gb). This can be as big as your filesystem allows. If, for example, I have 500gb free in my tablespace, I have no trouble sorting an entire 150gb table into an arbitrary order then exporting it to an external consumer.

But remember, Aurora Postgres doesn't have a filesystem for its tablespace. It has a block store. Instead, Aurora Postgres instances that are doing large sorts or indexing large files use local storage, which is currently 2x the size of memory. That is, if an Aurora database instance has 72gb of memory, you only have 144gb of temporary space. Good luck sorting that 150gb table.

What this means for me is that Aurora Postgres has some interesting scalability limits when dealing with very large data sets. I'm currently managing about 2 billion rows in Postgres. Needless to say, this requires a lot of very large indexes in order to segment this data space into usable consumable subsets. Creating these indexes is a slow and tedious problem on Aurora Postgres because you have to do them one at a time, you can't do them in parallel, due to the lack of temporary space to use for the sort heaps. And if I'm querying and sorting significant subsets of this database, again Aurora Postgres has some serious limits due to the inability to expand pgsql_tmp.

My guess is that people who are dealing with much fewer rows, but who are querying those rows with much greater frequency, will have a more successful experience with Aurora Postgres. But then they'll run into the IOPS costs. Basically, to get the same IOPS that would cost me $1200/month on EBS, I'd end up paying around $4,000/month on Aurora.

So: What's the point of Aurora? Aurora does have a couple of positives. You can create additional read replicas virtually instantly, since they're just pointed at the same shared block storage. Failover simply happens, and happens almost instantly. And from a management point of view, Aurora makes the database administrator's job far simpler since you no longer have to closely monitor your tablespaces and expand your block storage as needed (and reallocate tables and indexes across multiple tablespaces using pg_repack) in order to handle growing your dataset. Still, in its current state of development, given its limitations and high costs compared to running your own cluster, I really cannot recommend Aurora Postgres.

-ELG

Saturday, May 20, 2017

Yes, Virginia, there is a Cloud

So a pundit, attempting to be clever, said "there is no `Cloud,' it’s just a computer sitting in a rack somewhere that you can’t see."

Except that's not true. There is a cloud, and it has nothing to do with that computer sitting in a rack somewhere that you can't see. Rather, it has to do with manageability and services that allow you to ignore the reality of that computer sitting in a rack somewhere and treat infrastructure as a service rather than as a physical piece of hardware.

Look: There's been dedicated and shared hosting for literally decades now where you could rent time on somebody else's computer that was somewhere out on the Internet. But nobody who had any sense used those for production environments once they got more than a few dozen users, because it made far more sense to host your own hardware at a data center where you could go hands-on in order to manage it. You could make sure your hardware met your durability and performance requirements, you could reconfigure your hardware as needed to add additional capability, and so forth.

Thing is, all of that is a royal pain in the butt to deal with. Been there, done that, got the four racks of gear in the back room of our shop to prove it. What AWS and other cloud services give us is usable infrastructure as a service, reconfigurable via an easy-to-use web console to meet whatever performance requirements we have. I have constellations of computers on two sides of the continent now, provisioned with whatever combination of CPU and disk space that I need to fulfill my workloads, all done via point and click from my desk in Mountain View, California. I didn't have to go out and spec hardware and purchase it. I didn't have to rack hardware. When I need to burst hardware to process some additional data, I don't need to go out and buy more hardware, then decommission it until the next time I need it, at which point it's just sitting around doing nothing. When I need infrastructure, I provision it. When I don't need it, or I want to upgrade to more performant infrastructure, I de-provision it and provision new infrastructure as needed. And all of this is happening in data centers that are put together with far more redundancy than anything I could afford to put together myself.

That's what cloud means to me. Yes, it's computers I can't see sitting in racks somewhere, but that's not the part that makes it cloud. It's the infrastructure as a service that makes it cloud. For that matter, it's Internet-connected services, period, that makes it cloud. If it's a service sitting out on the Internet somewhere out of sight of me where I don't have to manually configure hardware and can easily scale as needed, it's cloud. Claiming "it's just computers, dude!" overlooks the point entirely.

-ELG