A quick way to change index tablespace

On one of the database servers, I had to quickly move all indexes from all the databases on to a different disk in order to free up some space from the main disk. I thought of sharing it so that someone else wanting to do the same thing can get an idea for doing the same….

A quick how to,

First made a query to get all the indexes within a database using the following:

select pg_namespace.nspname||'.'||pg_class.relname  from pg_class, pg_namespace
where pg_class.relnamespace  = pg_namespace.oid and pg_class.relkind = 'i' and pg_class.relname
not like 'pg_%'

and then using a simple script, plugging in the the above mentioned query to do an ALTER INDEX for each index by setting the new table space:

#!/bin/bash
DNAME_LIST=$(psql postgres -A -t -c "select datname from pg_database where
not datistemplate and datname != 'postgres'")
for DATABASE in $DNAME_LIST; do
INAME_LIST=$($PSQL $DATABASE -A -t -c "select pg_namespace.nspname||'.'||pg_class.relname
from pg_class, pg_namespace where
pg_class.relnamespace  = pg_namespace.oid and pg_class.relkind = 'i' and pg_class.relname
not like 'pg_%'")
    ( for INDEX in $INAME_LIST; do echo "ALTER INDEX $INDEX SET TABLESPACE
new_disk_index;"; done ) | psql $DATABASE
done

–
Shoaib Mir
shoaibmir[@]gmail.com

Row size and Btree index

At work today saw an error while inserting data into a table:

ERROR: index row size 2962 exceeds btree maximum, 2713

Looking at the HINT in the error, found out that you cant have the row size of more then 1/3th of the PostgreSQL block size (8K by default) for a btree index on the column.

The best is to start using PostgreSQL full text search option for these kind of scenarios as it will be much faster and easier… but in my case that was not an option so had to somehow get around this problem without using text search engine.

After a little bit of research two possible solutions I could find out were (where column was of text data type):

-  Create a hash index on the same column as it will let you store more then 1/3rd of the block size, but a couple of things you need to consider before you go that route…

• It lets you get the data inserted for more then the 1/3rd of the block size but that again has limitation, you cant go above the PG block size and even closer to that.
• With Hash indexing you wont be able to use the text pattern matching functions “LIKE” and “ILIKE”, you are left with “=”

-  The other solution is to use the partial index where CREATE INDEX  will look something like this…

        create index .... where length(clm_name) < 500

…this will require changing the queries as well, now you will have to add “where length(clm_name) < 500″ while doing SELECTS.

–
Shoaib Mir
shoaibmir[@]gmail.com

Finding top read tables in the database

A quick and good way to find the most used tables in a database with regards to reads can be:

PostgreSQL provides a system catalog view “pg_stat_all_tables” for helping DBAs with finding such statistics.

The view looks like this:

Columns
-------------
relid
relname
seq_scan
seq_tup_read
idx_scan
idx_tup_fetch
n_tup_ins
n_tup_upd
n_tup_del

This view has one entry for each table in the database. The seq_scan column tells you how many sequential scans have been performed for a given table and seq_tup_read tells you how many rows were used during those sequence scan. The idx_scan and idx_tup_fetch gives the same kind of information for the index scan usage. The n_tup_ins, n_tup_upd and n_tup_del gives you information on how many rows were inserted, update and deleted.

We can use this view in the following way to get the top 10 most read tables…

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from pg_stat_all_tables
WHERE idx_tup_fetch + seq_tup_read != 0
order by TotalReads desc
LIMIT 10;

(With thanks to my favorite book "PostgreSQL by Korry Douglas")

–
Shoaib Mir
shoaibmir[@]gmail.com

Converting IMG files to Virtual Box VDI format

In order to avoid installing the whole VM myself, I got an IMG file from a colleague for the CentOS VM. Now once I had the IMG file, but had not in the past converted it before to a VirtualBox compatible VDI format… after doing a little bit of research I found that to be a very simple process, so thought of sharing it with anyone who will like to do the same….

Once you have the .img file just run the following from command line:

VBoxManage convertfromraw -format VDI [filename].img [filename].vdi

Depending on the size of .img file it might take a little while as for my desktop machine it took like around 5 minutes for converting a 4GB img file to vdi format. Once its all done just add the VDI file as another disk, attach it to a VM in VirtualBox… start the VM and you just got it all working

–
Shoaib Mir
shoaibmir[@]gmail.com

Configuring SSH access for VirtualBox Guest Host

A few days back at work, I had to configure a CentOS VM and after I was done with installation on the host itself under VirtualBox I tried doing SSH to the CentOS VM and it didnt let me do that. I had it all configured with NAT and never thought that it will give me such a problem, googling it for a little while for an answer I found the solution… here is a quick howto for setting up SSH access to a VirutalBox VM:

Let <VMname> be the guest machine name (use quotes if it contains spaces), here are the commands that you have to execute for host machine console:

$ VBoxManage setextradata <VMname> "VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/HostPort" 2222
$ VBoxManage setextradata <VMname> "VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/GuestPort" 22
$ VBoxManage setextradata <VMname> "VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/Protocol" TCP

The HostPort must be greater than or equal to 1024 since listening on ports 0-1023 needs root permssions (and Virtualbox usually doesn’t).

Once all goes good with the commands, you need to exit out (close it) of the guest Machine (different from a reboot as a reboot will not be enough in this case), restart it and then connect via ssh with:

$ ssh -l username -p 2222 localhost

Replace “localhost” with the host machine IP address if you are connecting from another computer.

–
Shoaib Mir
shoaibmir[@]gmail.com