Using Oracle SYS_GUID() and RAW in SQLAlchemy

This post will show you how to use RAW data types from cx_Oracle dialect in SQLAlchemy to create unique row IDs that are generated by SYS_GUID().

This lets you use Oracle’s SYS_GUID() function to create unique IDs for rows without Sequences. The added advantage is that each ID is unique, even among tables. There are many discussions about system performance regarding this technique, but the general consensus is that this is as fast or faster than Integers and Sequences.

The Setup

First, the RAW dialect is required from the cx_Oracle package. SQLAlchemy has knowledge of most basic cx_Oracle types, but RAW is not there by default.


In [1]: from sqlalchemy.dialects.oracle import RAW

RAW data in Oracle is Binary data. We will have to convert between binary and ascii-hex so humans can use the values.

In [2]: import binascii

Your SQLAlchemy model object will look like this.

This code sample assumes you already have a declarative base and a Base class. For basics on using SQLAlchemy, see their own documentation: http://www.sqlalchemy.org/

class MyTable(Base):
    __tablename__ = 'my_table'
    __table_args__ = (
        Index('my_table_uk1', 'name', 'value', unique=True),
    )

    id = Column(RAW, primary_key=True, server_default=text("SYS_GUID()"))
    name = Column(Unicode(100), nullable=False)
    value = Column(Unicode(100), nullable=False)

That’s all there is to the setup. You have a model that supports RAW with an ‘id’ column that is automatically a call to Oracle’s internal SYS_GUID() function.

Inserting Data

You can insert a row by putting a Binary value in to the id column, but why bother? Just leave it out. If you create this object:

{
    'name': 'Larry',
    'value': 'Programmer'
}

You can query it and see that an ID was generated:

In [3]: for value in Session.query(models.MyTable.id).distinct():
    ...:    print(value)

(b'L\xeaH\xde\x88|#F\xe0S\xae\xa4\xcc\n\xf6@')

It works! However, that value sure is ugly.

Making it Readable

To convert that value to something your users can reference as a unique ID.

In [4]: values = v2_session.read_session.query(models.MyTable).all()
In [5]: values
Out[5]:
[<models.MyTable at 0x2b0bbbaaf518>]

In [6]: v = values[0]

In [7]: v
Out[7]: <models.MyTable at 0x2b0bbbaaf438>

In [8]: v.name
Out[8]: 'Larry'

In [9]: v.value
Out[9]: 'Programmer'

In [10]: v.id
Out[10]: b'L\xeaH\xde\x88|#F\xe0S\xae\xa4\xcc\n\xf6@'

In [11]: v.id.hex()
Out[11]: '4cea48de887c2346e053aea4cc0af640'

The RAW object has a hex() method that translates nicely in to a string GUID your users and APIs can work with.

From Readable Back to RAW

Now lets turn that string back in to a RAW value that SQLAlchemy can use to query the database.

In [12]: column_id = binascii.a2b_hex('4cea48de887c2346e053aea4cc0af640')

In [13]: values = Session.query(models.MyTable).filter_by(id=column_id).all()

In [14]: values
Out[14]: [<models.MyTable at 0x2b0bbbaaf438>]

There you have it.

Did you find this post useful or have questions or comments? Please let me know!

Posted in cx_Oracle, Oracle, Programming, python, SQLAlchemy | Leave a comment

HOWTO Build and Install Mcrouter for RHEL, OL, CENTOS 7.x

UPDATE: Unless you have a very specific environment, Facebook likely now has packages or updated compile instructions that will work better than this: https://github.com/facebook/mcrouter

The instructions below are likely out of date and may no longer work.

This post will show you how to build and install Mcrouter. Once complete, you will have a portable Mcrouter installation that you can apply to multiple machines via RPM.

Building Mcrouter is not easy, but this step-by-step process should get you to a collection of RPMs which make up a functional and upgrade-able Mcrouter environment.

About Mcrouter

Mcrouter is a memcached protocol router for scaling memcached (http://memcached.org/) deployments. It’s a core component of cache infrastructure at Facebook and Instagram where mcrouter handles almost 5 billion requests per second at peak.

Mcrouter is developed and maintained by Facebook.

You can get the source and more information here: https://github.com/facebook/mcrouter

You are going to do a lot of compiling and packaging.  Once you have this perfected, it is not a big stretch to hand it off to Jenkins or some other build tool for automation.

Getting Started

You need to build two environments.

Environment 1: Build Environment for compiling and packaging Mcrouter

  • An EL7 Server (RedHat, Oracle Linux, CentOS)
  • Yum updates to the latest stable everything (yum -y update, then reboot)
  • At LEAST 4GB RAM
  • At LEAST 4GB available disk space
  • A decent internet connection
  • Root access

Environment 2: Operating Environment for actually running Mcrouter

  • An EL7 Server (RedHat, Oracle Linux, CentOS)
  • Yum updates to the latest stable everything (yum -y update, then reboot)
  • Sufficient privilege to install RPMs (sudo, root, etc)

(Env 1) Setting up The Build Environment

NOTE 1: All of the following assumes you are root.  Insert your sudo where required if you aren’t just living and breathing as root.

NOTE 2: You are going to install a lot of crap on this system.  I assume you are OK with that.

Before we begin to build Mcrouter, we need to build a lot of the dependencies and install packages required for both running and building the software.

Install a bunch of packages

NOTE 1: Some of these will not exist. We will compile our own RPMs and grab some from Fedora.  All of it is outlined below.

yum -y install gcc-c++ ruby ruby-devel ragel cyrus-sasl cyrus-sasl-devel cmake bison python-libs python-devel numactl-libs numactl-devel scons snappy snappy-devel gflags gflags-devel glog-devel glog boost-devel automake autoconf libtool libevent libevent-devel openssl-devel libatomic flex rpm-build

Most likely the following packages are missing: ragel, scons, glfags, gflags-devel, glog, glog-devel.  Let’s get building and packaging for those.  If you are missing others, you’ll have to build and package them too.

Create a build location

This is where you need the disk space and where you will download, compile and package everything.

mkdir /build

Install fpm

(Effing package management! https://github.com/jordansissel/fpm)

We are going to use FPM to create RPMs as we go.

gem install fpm

If you see some errors at the end similar to this:
Parsing documentation for fpm-1.5.0
unable to convert "\xF3" from ASCII-8BIT to UTF-8 for lib/fpm/package/pyfpm/__init__.pyc, skipping
unable to convert "\xF3" from ASCII-8BIT to UTF-8 for lib/fpm/package/pyfpm/get_metadata.pyc, skipping

You can ignore them.

Build and package ragel

NOTE: Get the latest version of ragel here: http://www.colm.net/open-source/ragel/

Download

cd /build
curl -O http://www.colm.net/files/ragel/ragel-6.9.tar.gz

Build

cd /build
tar zxf ragel-6.9.tar.gz
cd ragel-6.9
./configure
make
make install

Package

mkdir /build/ragelpkg
make install DESTDIR=/build/ragelpkg
cd /build
fpm --verbose -f -s dir -t rpm -n ragel -v 6.9 -C /build/ragelpkg \
usr/local/bin \
usr/local/share/doc/ragel \
usr/local/share/man/man1

You should have an RPM that will install ragel.

Download and install scons, glfags, gflags-devel, glog, glog-devel

Download

cd /build
curl -O http://tenet.dl.sourceforge.net/project/scons/scons/2.5.0/scons-2.5.0-1.noarch.rpm
curl -O https://kojipkgs.fedoraproject.org//packages/gflags/2.1.1/6.el7/x86_64/gflags-2.1.1-6.el7.x86_64.rpm
curl -O https://kojipkgs.fedoraproject.org//packages/gflags/2.1.1/6.el7/x86_64/gflags-devel-2.1.1-6.el7.x86_64.rpm
curl -O https://kojipkgs.fedoraproject.org//packages/glog/0.3.3/8.el7/x86_64/glog-0.3.3-8.el7.x86_64.rpm
curl -O https://kojipkgs.fedoraproject.org//packages/glog/0.3.3/8.el7/x86_64/glog-devel-0.3.3-8.el7.x86_64.rpm

Install

rpm -ivh ./scons-2.5.0-1.noarch.rpm ./gflags-2.1.1-6.el7.x86_64.rpm ./glog-0.3.3-8.el7.x86_64.rpm ./gflags-devel-2.1.1-6.el7.x86_64.rpm ./glog-devel-0.3.3-8.el7.x86_64.rpm

Build and Package Double-Conversion

NOTE: Double-Conversion is a library from Google which provides binary-decimal and decimal-binary routines for IEEE doubles.  Details and source code here: https://github.com/google/double-conversion

Download

cd /build
git clone https://github.com/google/double-conversion.git

Build

cd /build/double-conversion
make
make test
scons install
chmod 0644 /usr/local/lib/libdouble-conversion*
mkdir /usr/local/include/double-conversion
cp double-conversion/*.h /usr/local/include/double-conversion/
chmod 0755 /usr/local/include/double-conversion/
chmod 0644 /usr/local/include/double-conversion/*

Package

cd /build
mkdir -p /build/dcpkg/usr/local/lib/
mkdir -p /build/dcpkg/usr/local/include/double-conversion/

cp -rp /usr/local/lib/libdouble-conversion* /build/dcpkg/usr/local/lib/
cp -rp /usr/local/include/double-conversion/* /build/dcpkg/usr/local/include/double-conversion/

cd /build/double-conversion
VER=$(git tag|tail -1)
cd /build

fpm --verbose -f -s dir -t rpm -n double-conversion -v $VER -C /build/dcpkg \
usr/local/lib/ \
usr/local/include/double-conversion/

You should have an RPM that will install double-conversion.

Build and Package Folly

Lots of information on Folly: https://github.com/facebook/folly

Folly (acronymed loosely after Facebook Open Source Library) is a library of C++11 components designed with practicality and efficiency in mind. Folly contains a variety of core library components used extensively at Facebook. In particular, it’s often a dependency of Facebook’s other open source C++ efforts and place where those projects can share code.

Download

cd /build
git clone https://github.com/facebook/folly

Build

cd /build/folly/folly
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
VER=$(git tag|tail -1)
autoreconf -ivf
LDFLAGS=-L/usr/local/lib CPPFLAGS=-I/usr/local/include ./configure
make -j2
make install

Package

cd /build/folly/folly
mkdir /build/follypkg
make install DESTDIR=/build/follypkg
cd /build
fpm --verbose -f -s dir -t rpm -n folly -v $VER -C /build/follypkg \
usr/local/include \
usr/local/lib

You should have an RPM that will install Folly.

Upgrade your Automake

NOTE: This is only required if you have a version of Automake lower than 1.15

curl -O https://kojipkgs.fedoraproject.org/packages/automake/1.15/1.fc22/noarch/automake-1.15-1.fc22.noarch.rpm
rpm -Uvh automake-1.15-1.fc22.noarch.rpm

Download and Install jemalloc

curl -O https://kojipkgs.fedoraproject.org/packages/jemalloc/3.6.0/1.el7/x86_64/jemalloc-3.6.0-1.el7.x86_64.rpm
curl -O https://kojipkgs.fedoraproject.org//packages/jemalloc/3.6.0/1.el7/x86_64/jemalloc-devel-3.6.0-1.el7.x86_64.rpm

rpm -ivh jemalloc-devel-3.6.0-1.el7.x86_64.rpm jemalloc-3.6.0-1.el7.x86_64.rpm

Build and Package Wangle

More on Wangle: https://github.com/facebook/wangle

Wangle provides a full featured, high performance C++ futures implementation.

Download

cd /build
git clone https://github.com/facebook/wangle

Build

cd /build/wangle/wangle
VER=$(git tag|tail -1)
sed -i -r -e "s@\ \ -lpthread@\ \ -lgflags\n\ \ -lgflags@" CMakeLists.txt
cmake .
make
make test
make install

Package

cd /build/wangle/wangle
mkdir /build/wanglepkg
make install DESTDIR=/build/wanglepkg
cd /build
fpm --verbose -f -s dir -t rpm -n wangle -v $VER -C /build/wanglepkg \
usr/local/include \
usr/local/lib

You should have an RPM that will install Wangle.

Build and Package FBThrift

Read lots about fbthrift here: https://github.com/facebook/fbthrift

FBThrift: Facebook’s branch of apache thrift

Download

cd /build
git clone https://github.com/facebook/fbthrift

Build

cd /build/fbthrift/thrift
VER=$(git tag|tail -1)
autoreconf -ifv
LDFLAGS=-L/usr/local/lib CPPFLAGS=-I/usr/local/include ./configure
make -j2
make install

Package

cd /build/fbthrift/thrift
mkdir /build/fbthriftpkg
make install DESTDIR=/build/fbthriftpkg
cd /build
fpm --verbose -f -s dir -t rpm -n fbthrift -v $VER -C /build/fbthriftpkg \
usr/local/bin \
usr/local/include \
usr/local/lib

You should have an RPM that will install Thrift.

FINALLY!!! Build and Package McRouter!

Download

cd /build
git clone https://github.com/facebook/mcrouter

Build

cd /build/mcrouter/mcrouter
VER=$(git tag|tail -1)
autoreconf -ifv
export THRIFT2_COMP_DIR=/usr/lib/python2.7/site-packages/thrift_py-0.9.0-py2.7.egg/thrift_compiler
LDFLAGS=-L/usr/local/lib CPPFLAGS=-I/usr/local/include ./configure --prefix=/usr/local/mcrouter
make -j2
make install

Package

cd /build/mcrouter/mcrouter
mkdir /build/mcrouterpkg
make install DESTDIR=/build/mcrouterpkg
cd /build
fpm --verbose -f -s dir -t rpm -n mcrouter -v $VER -C /build/mcrouterpkg \
usr/local/mcrouter

You should have an RPM that will install mcrouter

Defining Your Portable Installation

The following RPMs from this tutorial are required to operate a mcrouter environment. Set them aside.

As of this writing, my specific RPMs and versions are:

  • gflags-2.1.1-6.el7.x86_64.rpm
  • glog-0.3.3-8.el7.x86_64.rpm
  • jemalloc-3.6.0-1.el7.x86_64.rpm
  • ragel-6.9-1.x86_64.rpm
  • double-conversion-v2.0.1-1.x86_64.rpm
  • folly-v0.57.0-1.x86_64.rpm
  • wangle-v0.9.0-1.x86_64.rpm
  • fbthrift-v0.31.0-1.x86_64.rpm
  • mcrouter-v0.9.0-1.x86_64.rpm

(Env 2) Setting up The Operating Environment

In order to run mcrouter on another node, you will need to install a the following system packages and packages you downloaded and created:

Install Packages

System Packages

yum -y install cyrus-sasl python-libs numactl-libs snappy boost libtool libevent openssl libatomic

Downloaded Packages

rpm -ivh gflags-2.1.1-6.el7.x86_64.rpm glog-0.3.3-8.el7.x86_64.rpm jemalloc-3.6.0-1.el7.x86_64.rpm

Built Packages

rpm -ivh ragel-6.9-1.x86_64.rpm double-conversion-v2.0.1-1.x86_64.rpm folly-v0.57.0-1.x86_64.rpm wangle-v0.9.0-1.x86_64.rpm fbthrift-v0.31.0-1.x86_64.rpm mcrouter-v0.9.0-1.x86_64.rpm

Test Mcrouter

/usr/local/mcrouter/bin/mcrouter --version

Done!

Did you find this post useful or have questions or comments? Please let me know!

Posted in Facebook, Folly, fpm, Google, How Tos, linux, Mcrouter, Oracle Linux, redhat, rpm, Software, thrift, Wangle | 5 Comments

HOWTO use PHP getallheaders() under FastCGI (PHP-FPM, nginx, etc.)

If you use Nginx, PHP-FPM or any other FastCGI method of running PHP you’ve probably noticed that the function getallheaders() does not exist. There are many creative workarounds in the wild, but PHP offers two very nice features to ease your pain.

First, PHP has an internal function called function_exists() which lets you see if a function is already declared.  With namespaces and objects this isn’t a very big deal so many people don’t know about this little function, but when a built-in function comes and goes, it can be a lifesaver.

Second, PHP lets you declare a function that has global scope if you are inside a function that has global scope.

If we put them together, we have a few lines we can include before we process any headers that will declare getallheaders() if it does not exist. If it does exist, nothing changes.

if (!function_exists('getallheaders')) {
    function getallheaders() {
    $headers = [];
    foreach ($_SERVER as $name => $value) {
        if (substr($name, 0, 5) == 'HTTP_') {
            $headers[str_replace(' ', '-', ucwords(strtolower(str_replace('_', ' ', substr($name, 5)))))] = $value;
        }
    }
    return $headers;
    }
}

This idea was shamelessly lifted from the user contributed comments at http://php.net/manual/en/function.getallheaders.php. Thanks joyview at gmail dot com

Did you find this post useful or have questions or comments? Please let me know!

Posted in How Tos, php, Programming | 4 Comments