AWS: PostgreSQL on Graviton2
Tags: aws, postgres, graviton2, arm
Published: 2021-03-08 19:44:05

---
title: AWS: PostgreSQL on Graviton2
published: true
tags: AWS, PostgreSQL, Graviton2, ARM
canonical_url: https://blog.dbi-services.com/aws-postgresql-on-graviton2/
---
<a href="https://aws.amazon.com/ec2/instance-types/t4/"><img src="https://blog.dbi-services.com/wp-insides/uploads/sites/2/2021/03/Site-Merch_Graviton_T4g_trial_Sign-in_EN.b703bb5939c5e5d7c045f6ef5c45a66955e036ff.jpg" alt="" width="285" height="225" class="alignright size-full wp-image-48021" /></a>On the AWS free tier, you can run a t2.micro instance for 750 hours per month during the first 12 month after sign-up date. And currently, until June 2021, you can also run a T4g.micro. But be careful, when the free trial ends, or if your usage exceeds the free trial restrictions, you’ll pay the standard pay-as-you-go rates. This is a good occasion to test the Graviton2 ARM processors, and you can do the same as I do in this blog post on those instances. However, as I want to compare the CPU performance during a long run I'll use larger (and non-burstable) instances: m5d.2xlarge for x86_64 and m6gd.2xlarge aarch64 which have both 8 vCPUs and 32 GB or RAM. 

I've installed "Amazon Linux 2 AMI (HVM)" on m5d.2xlarge (x86_64) and "Amazon ECS-Optimized Amazon Linux 2 AMI (ARM)" for m6gd.2xlarge ARM64 (aarch64)
<a href="https://blog.dbi-services.com/wp-insides/uploads/sites/2/2021/03/Screenshot-2021-03-07-230532.jpg"><img src="https://blog.dbi-services.com/wp-insides/uploads/sites/2/2021/03/Screenshot-2021-03-07-230532.jpg" alt="" width="1816" height="1210" class="aligncenter size-full wp-image-48104" /></a>
<h3>PostgreSQL</h3>

I'll install PostgreSQL here and measure LIOPS with PGIO (<a href="https://github.com/therealkevinc/pgio">https://github.com/therealkevinc/pgio</a>)

<pre><code>sudo yum install -y git gcc readline-devel zlib-devel bison bison-devel flex
git clone https://github.com/postgres/postgres.git
sudo yum install -y gcc readline-devel zlib-devel bison-devel
time ( cd postgres &amp;&amp; ./configure &amp;&amp; make all &amp;&amp; sudo make install )
( cd postgres/contrib &amp;&amp; sudo make install )</code></pre>

This compiles PostgreSQL from the community source (version 14devel). I can already get an idea about the CPU performance:
<ul>
<li>m5d.2xlarge x86_64 Xeon time: real 3m32.192s, user 3m14.176s, sys 0m18.400s</li>
<li>m6gd.2xlarge aarch64 ARM  time: real 3m54.493s, user 3m39.324s, sys 0m15.373s</li>
</ul>







<pre><code>
export PGDATA=~/pgdata
echo "$PATH" | grep /usr/local/pgsql/bin || export PATH="$PATH:/usr/local/pgsql/bin"
initdb
pg_ctl -l postgres.log start
top -bn1 -cp $(pgrep -xd, postgres)
</code></pre>
Environment is set and instance started

<pre><code>
sed -ie "/shared_buffers/s/^.*=.*/shared_buffers= 8500MB/" $PGDATA/postgresql.conf
sed -ie "/huge_pages/s/^.*=.*/huge_pages= true/" $PGDATA/postgresql.conf
awk '/Hugepagesize.*kB/{print 1 + int(1024 * MB / $2)}' MB=9000 /proc/meminfo | sudo bash -c  "cat &gt; /proc/sys/vm/nr_hugepages" 
pg_ctl -l postgres.log restart
</code></pre>
I set 8GB of shared buffers to be sure to measure logical I/O from the database shared memory.

<h3>PGIO</h3>

<pre><code>
git clone https://github.com/therealkevinc/pgio
tar -zxf pgio/pgio*tar.gz
cat &gt; pgio/pgio.conf &lt;&lt;CAT
 UPDATE_PCT=0
 RUN_TIME=$(( 60 * 600 ))
 NUM_SCHEMAS=4
 NUM_THREADS=1
 WORK_UNIT=255
 UPDATE_WORK_UNIT=8
 SCALE=1024M
 DBNAME=pgio
 CONNECT_STRING=&quot;pgio&quot;
 CREATE_BASE_TABLE=TRUE
CAT
cat pgio/pgio.conf
psql postgres &lt;&lt;&lt;&#039;create database pgio;&#039;
time ( cd pgio &amp;&amp; sh setup.sh )
</code></pre>
I initialized PGIO for four 1GB schemas

<pre><code>
echo $(curl -s http://169.254.169.254/latest/meta-data/instance-type) $(uname -m)
time ( cd pgio &amp;&amp; sh runit.sh )
uptime
</code></pre>
This will run PGIO as configured (4 threads on 1GB for 1 hour)

<h4>4 threads on ARM</h4>

<pre><code>[ec2-user@ip-172-31-46-196 ~]$ echo $(curl -s http://169.254.169.254/latest/meta-data/instance-type) $(uname -m)

m6gd.2xlarge aarch64                    

[ec2-user@ip-172-31-46-196 ~]$ time ( cd pgio &amp;&amp; sh runit.sh )

Date: Fri Mar  5 11:35:18 UTC 2021
Database connect string: "pgio".
Shared buffers: 8GB.
Testing 4 schemas with 1 thread(s) accessing 1024M (131072 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 4 schema(s) will be accessed by 1 thread(s) each.
pg_stat_database stats:
          datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE:  pgio    | 12676581 |   2797475 |      2663112 |       26277 |         142
AFTER:   pgio    | 11254053367 |   2797673 |  11063125066 | 11057981670 |         162
DBNAME:  pgio. 4 schemas, 1 threads(each). Run time: 3600 seconds. RIOPS &gt;0&lt; CACHE_HITS/s &gt;3122604&lt;
</code></pre>
This is about 780651 LIOPS / thread.

<h4>4 threads on x86</h4>

<pre><code>
[ec2-user@ip-172-31-29-57 ~]$ echo $(curl -s http://169.254.169.254/latest/meta-data/instance-type) $(uname -m)

m5d.2xlarge x86_64

[ec2-user@ip-172-31-29-57 ~]$ time ( cd pgio &amp;&amp; sh runit.sh )
uptimeDate: Fri Mar  5 13:20:10 UTC 2021
Database connect string: "pgio".
Shared buffers: 8500MB.
Testing 4 schemas with 1 thread(s) accessing 1024M (131072 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 4 schema(s) will be accessed by 1 thread(s) each.
pg_stat_database stats:
          datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE:  pgio    | 109879603 | 2696860921 |   2759130206 |  2757898526 |          20
AFTER:   pgio    | 13016322621 | 2697387365 |  15459965084 | 15455884319 |          20
DBNAME:  pgio. 4 schemas, 1 threads(each). Run time: 3600 seconds. RIOPS &gt;146&lt; CACHE_HITS/s &gt;3585123&lt;
</code></pre>
This is about 896280 LIOPS / thread.

<h3>pgbench</h3>

For pgbench test you may want to read <a href="https://www.percona.com/blog/2021/01/22/postgresql-on-arm-based-aws-ec2-instances-is-it-any-good/" rel="noopener" target="_blank">https://www.percona.com/blog/2021/01/22/postgresql-on-arm-based-aws-ec2-instances-is-it-any-good/</a>  where Jobin Augustine and Sergey Kuzmichev have run long tests, read-only and read-write, with and without checksum. And also sysbench-tpcc. I'm doing only a very simple test here to show that result depend on what you test.

<pre><code>
time pgbench -i -s 100 postgres
</code></pre>

<h4>pgbench simple protocol</h4>
<pre><code>
time pgbench -T 600 -c 4 --protocol=simple postgres
</code></pre>

Result:
<ul>
<li>m5d.2xlarge x86_64 Xeon: tps = 2035.794849 (excluding connections establishing)</li>
<li>m6gd.2xlarge aarch64 ARM:  tps = 2109.661869 (excluding connections establishing)</li>
</ul>

<h4>pgbench prepared statements</h4>

<pre><code>
time pgbench -T 600 -c 4 --protocol=prepared postgres
</code></pre>
Result:
<ul>
<li>m5d.2xlarge x86_64 Xeon: tps = 2107.070647 (excluding connections establishing)</li>
<li>m6gd.2xlarge aarch64 ARM:  tps = 2121.966940 (excluding connections establishing)</li>
</ul>

<h3>Price/Performance</h3>
Here is the price of the instance I used. The software is free and the EC2 running hours for Graviton2 is 20% cheaper:
<ul>
<li>m5d.2xlarge x86_64 Xeon 	EC2 cost:	$0.504/hr</li>
<li>m6gd.2xlarge aarch64 ARM	EC2 cost:	$0.403/hr</li>
</ul>

The compilation time for the PostgreSQL sources was 11% slower on ARM: 3 minutes 39 seconds vs. 3 minutes 14 seconds vs.
The PostgreSQL shared buffer cache hits were 13% faster x86: 896280 LIOPS / thread vs. 780651 LIOPS / thread, but that is the most optimal database work: all in shared buffers, limited calls, roundtrips and context switch. All is in CPU and RAM access.
However, where running pgbench, ARM had nearly the same performance for the prepared statement protocol and even a bit faster with the simple protocol. And that's finally what most of database applications are doing. So, finally, the big difference is the price as the Graviton2 m6gd.2xlarge is 20% cheaper than the m5d.2xlarge x86. Here I installed PostgreSQL on EC2, but Graviton2 is available on RDS as well (in preview) with db.r6g

I've given more details about the GCC version and compilation flags in the <a href="https://dev.to/aws-heroes/aws-postgresql-on-graviton2-with-newer-gcc-3aha" rel="noopener" target="_blank">next post</a>