This is the first entry in a series of random articles about some useful internals-to-know of the awesome Oracle Database In-Memory column store. I intend to write about Oracle’s IM stuff that’s not already covered somewhere else and also about some general CPU topics (that are well covered elsewhere, but not always so well known in the Oracle DBA/developer world).
Before going into further details, you might want to review the Part 0 of this series and also our recent Oracle Database In-Memory Option in Action presentation with some examples. And then read this doc by Intel if you want more info on how the SIMD registers and instructions get used.
There’s a lot of talk about the use of your CPUs’ SIMD vector processing capabilities in the Oracle inmemory module, let’s start by checking if it’s enabled in your database at all. We’ll look into Linux/Intel examples here.
The first generation of SIMD extensions in Intel Pentium world was called MMX. It added 8 new XMMn registers, 64 bits each. Over time the registers got widened, more registers and new features were added. The extensions were called Streaming SIMD Extensions (SSE, SSE2, SSSE3, SSE4.1, SSE4.2) and Advanced Vector Extensions (AVX and AVX2).
The currently available AVX2 extensions provide 16 x 256 bit YMMn registers and the AVX-512 in upcoming King’s Landing microarchitecture (year 2015) will provide 32 x 512 bit ZMMn registers for vector processing.
So how to check which extensions does your CPU support? On Linux, the “flags” column in /proc/cpuinfo easily provides this info.
Let’s check the Exadatas in our research lab:
Exadata V2:
$ grep "^model name" /proc/cpuinfo | sort | uniq model name : Intel(R) Xeon(R) CPU E5540 @ 2.53GHz $ grep ^flags /proc/cpuinfo | egrep "avx|sse|popcnt" | sed 's/ /\n/g' | egrep "avx|sse|popcnt" | sort | uniq popcnt sse sse2 sse4_1 sse4_2 ssse3
So the highest SIMD extension support on this Exadata V2 is SSE4.2 (No AVX!)
Exadata X2:
$ grep "^model name" /proc/cpuinfo | sort | uniq model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz $ grep ^flags /proc/cpuinfo | egrep "avx|sse|popcnt" | sed 's/ /\n/g' | egrep "avx|sse|popcnt" | sort | uniq popcnt sse sse2 sse4_1 sse4_2 ssse3
Exadata X2 also has SSE4.2 but no AVX.
Exadata X3:
$ grep "^model name" /proc/cpuinfo | sort | uniq model name : Intel(R) Xeon(R) CPU E5-2690 0 @ 2.90GHz $ grep ^flags /proc/cpuinfo | egrep "avx|sse|popcnt" | sed 's/ /\n/g' | egrep "avx|sse|popcnt" | sort | uniq avx popcnt sse sse2 sse4_1 sse4_2 ssse3
The Exadata X3 supports the newer AVX too.
My laptop (Macbook Pro late 2013):
The Exadata X4 has not yet arrived to our lab, so I’m using my laptop as an example of a latest available CPU with AVX2:
Update: Jason Arneil commented that the X4 does not have AVX2 capable CPUs (but the X5 will)
$ grep "^model name" /proc/cpuinfo | sort | uniq model name : Intel(R) Core(TM) i7-4960HQ CPU @ 2.60GHz $ grep ^flags /proc/cpuinfo | egrep "avx|sse|popcnt" | sed 's/ /\n/g' | egrep "avx|sse|popcnt" | sort | uniq avx avx2 popcnt sse sse2 sse4_1 sse4_2 ssse3
The Core-i7 generation supports everything up to the current AVX2 extension set.
So, which extensions is Oracle actually using? Let’s check!
As Oracle needs to run different binary code on CPUs with different capabilities, some of the In-Memory Data (kdm) layer code has been duplicated into separate external libraries – and then gets dynamically loaded into Oracle executable address space as needed. You can run pmap on one of your Oracle server processes and grep for libshpk:
$ pmap 21401 | grep libshpk
00007f0368594000 1604K r-x-- /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libshpksse4212.so
00007f0368725000 2044K ----- /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libshpksse4212.so
00007f0368924000 72K rw--- /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libshpksse4212.so
My (educated) guess is that the “shpk” in libshpk above stands for oS dependent High Performance [K]ompression. “s” prefix normally means platform dependent (OSD) code and this low-level SIMD code sure is platform and CPU microarchitecture version dependent stuff.
Anyway, the above output from an Exadata X2 shows that SSE4.2 SIMD HPK libraries are used on this platform (and indeed, X2 CPUs do support SSE4.2, but not AVX).
Let’s list similar files from $ORACLE_HOME/lib:
$ cd $ORACLE_HOME/lib $ ls -l libshpk*.so -rw-r--r-- 1 oracle oinstall 1818445 Jul 7 04:16 libshpkavx12.so -rw-r--r-- 1 oracle oinstall 8813 Jul 7 04:16 libshpkavx212.so -rw-r--r-- 1 oracle oinstall 1863576 Jul 7 04:16 libshpksse4212.so
So, there are libraries for AVX and AVX2 in the lib directory too (the “12” suffix for all file names just means Oracle version 12). The AVX2 library is almost empty though (and the nm/objdump commands don’t show any Oracle functions in it, unlike in the other files).
Let’s run pmap on a process in my new laptop (which supports AVX and AVX2 ) to see if the AVX2 library gets used:
$ pmap 18969 | grep libshpk
00007f85741b1000 1560K r-x-- libshpkavx12.so
00007f8574337000 2044K ----- libshpkavx12.so
00007f8574536000 72K rw--- libshpkavx12.so
Despite my new laptop supporting AVX2, only the AVX library is used (the AVX2 library is named libshpkavx212.so). So it looks like the AVX2 extensions are not used yet in this version (it’s the first Oracle 12.1.0.2 GA release without any patches). I’m sure this will be added soon, along with more features and bugfixes.
To be continued …
