We use PostgreSQL a lot in our work. In fact PostgreSQL is our preferred relational database for most of the projects. Recently we were interested in how mid-level cloud servers perform when running PostgreSQL. Here we are sharing the results we found.
We used AWS and Rackspace Cloud servers for these benchmarks. For this phase of our test we used standard storage for both the clouds. In our next test we plan to use the higher performing IOPS enabled EBS storage in AWS and SSD storage in Rackspace. This way we will also come to know how much these advanced storage options add to performance.
For each cloud we created 2 servers:
This ran Ubuntu 12.04. The version of PostgreSQL was 9.1 and was installed using apt-get.
This ran Amazon Linux AMI on AWS and Cent OS 6.4 on Rackspace. Ideally we would have preferred to run Ubuntu for this too. But PgBench has some problems running on Ubuntu as of now because of some libraries required by the package. We tried it out. But instead of wasting too much time to get it to run we just opted for a CentOS based distribution to run PgBench.
PgBench was installed on the benchmark server using yum via the postgresql-contrib package.
For both the Database Server and the Benchmark server identical server types were used in each of the clouds.
a. M1 Standard Extra Large
i. 15GB RAM
ii. High Network Performance
i. DB Server : 100 GB standard EBS volume (IOPS not enabled)
ii. Benchmark Server : 8 GB standard EBS volume
a. 15GB Standard Instance
i. 15GB RAM
i. 620 GB – RAID 10 protected SATA hard Disk Drive
Monthly Pricing for the Database Servers
Around $365 – $370 per month
Around $660 per month
First we ran the pgbench command with a scale of 100, which generates 10 million rows in the accounts table. The size of database thus created was around 1.5GB.
After setting up the server we changed the postgresql.conf for max connections upto 400. Also we modified /etc/sysctl.conf and set the kernel.shmmax value to around 7GB.
We ran the benchmark 2 times on each of the servers. In the first run (henceforth referred as ‘without optimization’) we used the default values for ‘shared_buffers’ and ‘effective_cache_size’ configuration values. In the second run (henceforth referred as ‘with optimization’) we changed ‘shared_buffers’ to 4GB and ‘effective_cache_size’ to 8GB.
Also for each run we did 4 sub tests with 96, 128, 256 and 392 clients in read-write (RW), read only (R), Write only (W) modes. Each of these tests were run for 10 minutes (600 seconds.) We know for a database benchmark 10 minutes runtime is very low. But we had no idea on how these servers will perform and we wanted to do a lot of runs (Total 48 of 10 minute runs). In near future we would like to do the runs with number of clients in the range of 256 to 400 with 2 hours run time.
We realize that in many ways this is an Apples to Oranges comparison. These benchmarks were done on a whim, just to satisfy our curiosity on an idle Sunday. (Love the cloud – pay only for what you use. – Total experiment ran for around 13 hours and our combined cost was around $50. Sweet!) However we do hope that these numbers will help you to get at least some idea on the performance of PostgreSQL on these cloud servers if you don’t have the time or budget to perform such experiments.
For any database, disk performance is one of the major factors. From the above experiment we think that the network based EBS storage makes the AWS numbers almost 2.5 times less than that of Rackspace, which uses disks attached directly to the instance.
On the other hand the Rackspace instance costs roughly twice that of the AWS instance.
In the next part of this series we want to run these benchmark on the EBS Optimized IOPS storage on AWS and on SSD storage on Rackspace.
Meanwhile if you have any inputs, insights and results of your own to share, you are welcome!
Next post related to this article can be found here.