Category: Database

Get database version in Rails
For one of my applications, I recently created a status report page which shows crucial information for debugging, including software versions, service connectivity and filesystem path writability. One of the items was a database version string, and the standard ActiveRecord interface does not support this concept. While version methods usually exist in the connection adapters, they are usually protected/private and have different naming conventions.

As the application can be run against multiple backends as well as on MRI or JRuby, I needed to check what adapter was in use, then query the database:
```
adapter = ActiveRecord::Base.connection.adapter_name
sql = case adapter
  when 'MSSQL'
    'SELECT @@VERSION'
  when 'MySQL', 'Mysql2', 'PostgreSQL'
    'SELECT VERSION()'
  when 'OracleEnhanced'
    'SELECT * FROM V$VERSION'
  when 'SQLite'
    'SELECT SQLITE_VERSION()'
end
ActiveRecord::Base.connection.select_value(sql)
```
3 Jun 2015
Optimising index view performance
I recently came across a scenario where my index views in Rails were taking a long time to render when there were large data sets. The problem stemmed having a lot of data presented that were from models/tables other than the primary model/table of interest.

For example, an “asset” index view may have the following columns:
- Name (from asset.name)
- Type (from asset.type.name)
- Site (from asset.site.name)
- Organisation (from asset.site.organisation.name)
- System (from asset.system.name)
- Status (from asset.status.name)
The first (naive) approach I used was to query using eager loading:
```
Asset.includes(:type, {site: :organisation}, :system, :status)
.references(:type)
.search_by('types.name', 'Pump')
.order(:name).page(1).per_page(1000)
```
Using a data set of around 500,000 items, rack-mini-profiler indicated this took about 6.7 seconds to render the action with 5.6 seconds of that consumed by the SQL query.

When investigating, I noticed this was using a single query with LEFT OUTER JOINs. I then forced ActiveRecord to use separate queries to load the related models by using Rails 4’s preload instead of includes and used joins instead of references to force ActiveRecord to use a more performant INNER JOIN:
```
Asset.preload(:type, {site: :organisation}, :system, :status)
.joins(:type)
.search_by('types.name', 'Pump')
.order(:name).page(1).per_page(1000)
```
This dropped the action render time to 1.7 seconds with 0.7 seconds consumed by the six SQL queries.

For my index views, I actually don’t need the entire object graph to be hydrated – I only really needed the names of the related object. So I tried using pluck, which returns an 2-dimensional array of values:
```
Asset.includes(:type, {site: :organisation}, :system, :status)
.joins(:type).search_by('types.name', 'Pump')
.order(:name).page(1).per_page(1000)
.pluck(:name, 'types.name', 'sites.name', 'organisations.name', 'systems.name', 'statuses.name')
```
This actually increased the action render time to 2.1 seconds with 1.7 seconds consumed by ActiveRecord and the SQL query. The SQL query now uses LEFT OUTER JOINs again, which as we saw in the original case, is slow. But the differential between the total and SQL had decreased since ActiveRecord models did not have to be created.

While using the second approach would provide a significant speed boost without changing any code, I wondered if we could use a combination of the second and third approaches – use separate SQL queries instead of a single with LEFT OUTER JOINs and simply pass back an array and not have to hydrate ActiveRecord models.

The result: Preload Pluck.

On the same data set, Preload Pluck drops the action render time to 1.5 second with 0.7 seconds consumed by the SQL queries.

YMMV as the results above are for that particular data set, the queries I’ve used and the way my PostgreSQL database is setup.
22 May 2015
Install Oracle XE 11g R2 on Ubuntu 12.04
I’ve recently had a task of enabling Oracle database support for a JRuby on Rails application. To set up an Oracle database for use on a development environment, there were two preferred options – using Oracle DB on a virtual machine or installing Oracle XE locally.

1. Oracle DB on a virtual machine
Oracle kindly provide several VirtualBox images for download – I used the Database App Development VM, which comes with Enterprise Edition and SQL Developer, and it’s straightforward to get this running. The only catch is to enable a second network adapter (either Bridged or Host-only) in the VirtualBox settings so the VM is externally addressable. From there, keep in mind the the instance name is orcl, and sys and system passwords are oracle.

2. Installing Oracle XE locally
Oracle XE is officially supported on Windows and Red Hat Linux-based systems (the Developer Days VMs are Oracle Linux). Debian Linux-based systems don’t seem to be officially supported, but there have been efforts on installing Oracle XE on Ubuntu (my particular interest). I primarily followed Manish’s guide for the majority of the install process, but had to bring forward the step of setting environment variables. This is because Oracle XE starts immediately after the configuration step (oracle-xe configure). I also referred to another blog for guidance on setting kernel parameters. Here are my modified installation steps:

Download Oracle Database Express Edition 11g R2

Unzip archive:
```
unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
```
Install the required packages (note I omitted unixodbc as I didn’t require it):
```
sudo apt-get install alien libaio1
```
Convert the RPM package to a DEB package:
```
cd Disk1
sudo alien --scripts oracle-xe-11.2.0-1.0.x86_64.rpm
```
Create a /sbin/chkconfig file with the following contents (requires root):
```
#!/bin/bash
# Oracle 11gR2 XE installer chkconfig hack for Ubuntu
file=/etc/init.d/oracle-xe
if [[ ! `tail -n1 $file | grep INIT` ]]; then
echo >> $file
echo '### BEGIN INIT INFO' >> $file
echo '# Provides: OracleXE' >> $file
echo '# Required-Start: $remote_fs $syslog' >> $file
echo '# Required-Stop: $remote_fs $syslog' >> $file
echo '# Default-Start: 2 3 4 5' >> $file
echo '# Default-Stop: 0 1 6' >> $file
echo '# Short-Description: Oracle 11g Express Edition' >> $file
echo '### END INIT INFO' >> $file
fi
update-rc.d oracle-xe defaults 80 01
```
Set execute privileges for the file:
```
sudo chmod 755 /sbin/chkconfig
```
Create a /etc/sysctl.d/60-oracle.conf file with the following contents (requires root):
```
# Oracle 11g XE kernel parameters
fs.file-max=6815744
kernel.sem=250 32000 100 128
kernel.shmmax=1073741824
net.ipv4.ip_local_port_range=9000 65000
```
Load the new kernel parameters:
```
sudo service procps start
```
Run the following commands:
```
sudo ln -s /usr/bin/awk /bin/awk
sudo mkdir /var/lock/subsys
sudo touch /var/lock/subsys/listener
```
To avoid MEMORY_TARGET errors:
```
sudo rm -rf /dev/shm
sudo mkdir /dev/shm
sudo mount -t tmpfs shmfs -o size=2048m /dev/shm
```
Create a /etc/rc2.d/S01shm_load file with the following contents (requires root):
```
#!/bin/sh
case "$1" in
start) mkdir /var/lock/subsys 2>/dev/null
	   touch /var/lock/subsys/listener
	   rm /dev/shm 2>/dev/null
	   mkdir /dev/shm 2>/dev/null
	   mount -t tmpfs shmfs -o size=2048m /dev/shm ;;
*) echo error
   exit 1 ;;
esac
```
Set execute privileges for the file:
```
sudo chmod 755 /etc/rc2.d/S01shm_load
```
Install the Oracle XE package:
```
sudo dpkg --install oracle-xe_11.2.0-2_amd64.deb
```
Add the following environment variables to .bashrc:
```
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export ORACLE_BASE=/u01/app/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
```
Reload the bash profile:
```
source ~/.bashrc
```
Run the Oracle XE configuration:
```
sudo /etc/init.d/oracle-xe configure
```
Oracle XE should now be installed.

If anything goes wrong during installation, to uninstall:
```
sudo -s
/etc/init.d/oracle-xe stop
ps -ef | grep oracle | grep -v grep | awk '{print $2}' | xargs kill
dpkg --purge oracle-xe
rm -r /u01
rm /etc/default/oracle-xe
update-rc.d -f oracle-xe remove
```
I’ve used these instructions on two Ubuntu environments (my own VM and an Amazon EC2 instance) and Oracle XE is running happily.
23 Feb 2013