\nThis follows the previous post<\/a> about the Oracle Cloud ARM compute shape just announced on May 25th. The processor is ARM v8.2 with LSE (atomic instructions) and PostgreSQL can benefit from it (see Dramatical Effect of LSE Instructions for PostgreSQL on Graviton2 Instances<\/a>).<\/p>\n
I have installed GCC 11 in the previous post, on a Oracle Linux 7.9 image with comes with GCC 7. If you installed the Ubuntu 20.4 image, you have GCC 9 and I also recommand to install the latest version. Anyway, if you use a version before GCC 11 you must set CFLAGS as below to benefit from ARM v8.1 LSE.<\/p>\n
I’ll install PostgreSQL 14 which is currently in beta1:<\/p>\n
\n(\nsudo yum install -y git gcc readline-devel bison-devel zlib-devel\ncurl -s https:\/\/ftp.postgresql.org\/pub\/snapshot\/dev\/postgresql-snapshot.tar.gz | tar -zxvf -\nsudo rm -rf \/usr\/local\/pgsql ; sudo mkdir -p \/usr\/local\/pgsql\ncd postgresql-14beta1\nmake clean\nCFLAGS=\"-march=armv8.2-a+fp16\" .\/configure --enable-debug\nmake\nsudo make install\ncd contrib\nmake\nsudo make install\n)\n<\/code><\/pre>\nI initially tried to use CFLAGS=”-mcpu=neoverse-n1″ to compile for this processor only (GCC 11 defaults to -m_ouline_atomics to build binaries compatible with ARM v8 but this adds the very small overhead of runtime detection) but I got “unknown architectural extension `rcpc+dotprod+profile'” and that’s why I used CFLAGS=”-march=armv8.2-a+fp16″. Actually installing GCC11 here is not sufficient, the assemble should also be upgraded for Neoverse-N1 instructions. See below for the right usage of GCC in this distribution (devtoolset-10).<\/p>\n
\n[opc@arm-lon ~]$ nm \/usr\/local\/pgsql\/bin\/postgres | grep -E \"aarch64(_have_lse_atomics)?\" \n\n[opc@arm-lon ~]$ objdump -d \/usr\/local\/pgsql\/bin\/postgres | awk '\/\\t(ldxr|ldaxr|stxr|stlxr)\/{print $3\"\\t(load and store exclusives)\"}\/\\t(cas|casp|swp|ldadd|stadd|ldclr|stclr|ldeor|steor|ldset|stset|ldsmax|stsmax|ldsmin|stsmin|ldumax|stumax|ldumin|stumin)\/{print $3\"\\t(large-system extensions)\"}' | sort | uniq -c | sort -n\n\n 1 ldclral (large-system extensions)\n 3 ldsetal (large-system extensions)\n 12 ldaddal (large-system extensions)\n 15 casal (large-system extensions)\n 31 swpa (large-system extensions)\n<\/code><\/pre>\nI see no __aarch64_ functions to outline atomics and no load and store for pre-v8.1 ARM because I disabled the default -mno-outline-atomics runtime detection. That’s what I wanted to be optimized for this processor. I’ll compile differently later to check the difference.<\/p>\n
\n\/usr\/local\/pgsql\/bin\/psql --version\n\/usr\/local\/pgsql\/bin\/initdb -D \/var\/tmp\/pgdata\n\/usr\/local\/pgsql\/bin\/pg_ctl -D \/var\/tmp\/pgdata start\n<\/code><\/pre>\nThis creates a database and starts the instance<\/p>\n
[opc@arm-lon contrib]$ free -h\n\n total used free shared buff\/cache available\nMem: 22G 1.0G 4.9G 50M 16G 18G\nSwap: 8.0G 0B 8.0G\n\nsed -ie \"\/huge_pages\/s\/^.*=.*\/huge_pages= true\/\" \/var\/tmp\/pgdata\/postgresql.conf\nsed -ie \"\/shared_buffers\/s\/^.*=.*\/shared_buffers= 4800MB\/\" \/var\/tmp\/pgdata\/postgresql.conf\n\/usr\/local\/pgsql\/bin\/pg_ctl -D \/var\/tmp\/pgdata -l postgres.log stop\n<\/code><\/pre>\nI have 24 GB RAM here (which is what Oracle gives you for free – this is quite awesome, isn’t it?)
\nI’ll use enough shared buffers to fit my working set (4 schemas of 1GB) because I want to measure shared buffer cache access (this is where atimics are used).21<\/p>\n
[opc@arm-lon contrib]$ $ grep Hugepagesize \/proc\/meminfo\n\nHugepagesize: 524288 kB\n<\/code><\/pre>\nThe huge pages are 512MB here. This processor is build for large systems and that’s perfect for a database. Of course, I’ll define enough huge pages for the shared buffers.<\/p>\n
\nawk '\/Hugepagesize.*kB\/{print ( 1 + 1024 * MB \/ $2 ) }' MB=8192 \/proc\/meminfo | tee \/dev\/stderr | sudo bash -c \"cat > \/proc\/sys\/vm\/nr_hugepages\"\n\n\/usr\/local\/pgsql\/bin\/pg_ctl -D \/var\/tmp\/pgdata -l postgres.log restart\n\n[opc@arm-lon ~]$ free -h\n\n total used free shared buff\/cache available\nMem: 22G 9.1G 4.0G 24M 9.4G 10G\nSwap: 8.0G 112M 7.9G\n<\/code><\/pre>\nHere we are. 8GB for the postgresql shared buffers. 10GB available for the filesystem cache. You need this double buffering because PostgreSQL has no pre-fetching and write ordering optimizations, but still need its own cache to avoid a system call per page access.<\/p>\n
I’ll run PGIO <\/a>to look at the CPU performance, as I did on AWS Graviton2<\/a>. Because it measures exactly what I want to measure: access to the shared buffers. Without spending all time on client-server calls (see https:\/\/amitdkhan-pg.blogspot.com\/2020\/05\/postgresql-on-arm.html<\/a> for other ways to do it with a pgbench-like workload).<\/p>\n\ngit clone https:\/\/github.com\/therealkevinc\/pgio\ntar -zxf pgio\/pgio*tar.gz\ncat > pgio\/pgio.conf <<CAT\n UPDATE_PCT=0\n RUN_TIME=$(( 60 * 60))\n NUM_SCHEMAS=4\n NUM_THREADS=1\n WORK_UNIT=255\n UPDATE_WORK_UNIT=8\n SCALE=1024M\n DBNAME=pgio\n CONNECT_STRING=\"pgio\"\n CREATE_BASE_TABLE=TRUE\nCAT\n\/usr\/local\/pgsql\/bin\/psql postgres <<<'create database pgio;'\ntime ( PATH=\/usr\/local\/pgsql\/bin:$PATH ; cd pgio && bash setup.sh )\n<\/code><\/pre>\nThis setup a database for a read-only workload from 4 sessions reading in their own schema. If you are interesting by atomics improvement for AWL lightweight locks, you can set the PCT_UPDATE. But I like to show something that is often overlooked: a read-only workload on the database is still a read-write workload on the shared memory. And there is no connection, parsing, vacuuming, analyze… here. I use Kevin Closson PGIO, the “SLOB method” to run only what I want to measure: pin and read shared buffers.<\/p>\n
\n( PATH=\/usr\/local\/pgsql\/bin:$PATH ; cd pgio && bash runit.sh | sed -e \"s\/^\/$n|\\t\/\" )\n\nDate: Sun May 23 16:56:49 GMT 2021\nDatabase connect string: \"pgio\".\nShared buffers: 4800MB.\nTesting 4 schemas with 1 thread(s) accessing 1024M (131072 blocks) of each schema.\nRunning iostat, vmstat and mpstat on current host--in background.\nLaunching sessions. 4 schema(s) will be accessed by 1 thread(s) each.\npg_stat_database stats:\n datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated\nBEFORE: pgio | 10070507688 | 6920153 | 9870736490 | 9862382424 | 988185\nAFTER: pgio | 10936832180 | 7446586 | 10723766109 | 10715208260 | 988185\nDBNAME: pgio. 4 schemas, 1 threads(each). Run time: 600 seconds. RIOPS >877< CACHE_HITS\/s >1443874<\n\nobjdump -d \/usr\/local\/pgsql\/bin\/postgres | awk '\/\\t(ldxr|ldaxr|stxr|stlxr)\/{print $3\"\\t(load and store exclusives)\"}\/\\t(cas|casp|swp|ldadd|stadd|ldclr|stclr|ldeor|steor|ldset|stset|ldsmax|stsmax|ldsmin|stsmin|ldumax|stumax|ldumin|stumin)\/{print $3\"\\t(large-system extensions)\"}' | sort | uniq -c | sort -n\n\n 1 ldclral (large-system extensions)\n 3 ldsetal (large-system extensions)\n 12 ldaddal (large-system extensions)\n 15 casal (large-system extensions)\n 31 swpa (large-system extensions)\n\n<\/code><\/pre>\n1443874 page read per second from the 4 sessions. This is 360969 LIOPS\/thread, with CFLAGS=”-march=armv8.2-a” which calls the CASAL atomic in order to acquire the share mode LWLock to lookup for the block in the shared buffer partition.<\/p>\n
I mentioned that I compiled for ARM v8.2 only rather than relying on the defaults that try to build an ARM v8 compatible binary. Let’s do the same with CFLAGS=”-march=armv8-a -moutline-atomics”.<\/p>\n
\n( PATH=\/usr\/local\/pgsql\/bin:$PATH ; cd pgio && bash runit.sh | sed -e \"s\/^\/$n|\\t\/\" )\n\nDate: Sun May 23 16:31:43 GMT 2021\nDatabase connect string: \"pgio\".\nShared buffers: 4800MB.\nTesting 4 schemas with 1 thread(s) accessing 1024M (131072 blocks) of each schema.\nRunning iostat, vmstat and mpstat on current host--in background.\nLaunching sessions. 4 schema(s) will be accessed by 1 thread(s) each.\npg_stat_database stats:\n datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated\nBEFORE: pgio | 9196454698 | 6919962 | 9010603761 | 9002477779 | 988150\nAFTER: pgio | 10070502579 | 6919975 | 9870712088 | 9862380498 | 988183\nDBNAME: pgio. 4 schemas, 1 threads(each). Run time: 600 seconds. RIOPS >0< CACHE_HITS\/s >1456746<\n\nobjdump -d \/usr\/local\/pgsql\/bin\/postgres | awk '\/\\t(ldxr|ldaxr|stxr|stlxr)\/{print $3\"\\t(load and store exclusives)\"}\/\\t(cas|casp|swp|ldadd|stadd|ldclr|stclr|ldeor|steor|ldset|stset|ldsmax|stsmax|ldsmin|stsmin|ldumax|stumax|ldumin|stumin)\/{print $3\"\\t(large-system extensions)\"}' | sort | uniq -c | sort -n\n\n 1 ldclral (large-system extensions)\n 1 ldsetal (large-system extensions)\n 1 stxr (load and store exclusives)\n 1 swpa (large-system extensions)\n 2 casal (large-system extensions)\n 2 ldaddal (large-system extensions)\n 6 stlxr (load and store exclusives)\n 7 ldaxr (load and store exclusives)\n\n<\/code><\/pre>\n1456746 page read per second from the 4 sessions. This is 364187 LIOPS\/thread, with CFLAGS=”-march=armv8 -moutline-atomics” but with LSE outlined (which is the default in GCC 11). This uses a a runtime detection to use CASAL is LSE are available or LDAXR\/STXX if not. I would expect a little overhead for this detection but my test shows that I was able to read nearly 1% more buffers with there. Surprising, isn’t it?<\/p>\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/arm-oci.png\")
<\/a><\/p>\n