GCC tricks for the Linux Kernel

November 3, 2009 shoaibmir Leave a comment

While debugging some GCC specific linking problems, came across this very handy resource for gcc tips and tricks… very helpful indeed!

Check it out at –> http://www.ibm.com/developerworks/linux/library/l-gcc-hacks/index.html


Shoaib Mir
shoaibmir[@]gmail.com

Categories: C/C++ Tags: , ,

Disable notification system in Ubuntu

November 3, 2009 shoaibmir Leave a comment

I dont know about everyone else but for some reason I just found the notification system really annoying with my Ubuntu setup, after struggling with a few files and settings finally was able to get it all disabled.

Here is a quick how-to in case someone wants to disable/enable it….

For disabling use the following:

sudo mv /usr/share/dbus-1/services/org.freedesktop.Notifications.service
/usr/share/dbus-1/services/org.freedesktop.Notifications.service.disabled

To re-enable it:

sudo mv /usr/share/dbus-1/services/org.freedesktop.Notifications.service.
disabled /usr/share/dbus-1/services/org.freedesktop.Notifications.service


Shoaib Mir
shoaibmir[@]gmail.com

Scripting within psql

November 2, 2009 shoaibmir 1 comment

In one of my previous posts I was told of using PSQL instead of shell scripting to make things much simpler. That turned out to a good tip and with some help from my colleague at work I am now so used to PSQL in these kind of scenarios.

Just today I had to get the dead tuple percentages (using pgstattuple contrib) for tables within all databases on the server and here is how I was to get it done using PSQL and a simple shell script….

#!/bin/bash
PSQL="psql -A -t"
DBLIST=$($PSQL postgres -c "select datname from pg_database
where not datistemplate and datname not in ('postgres')")
for DB in $DBLIST; do
 echo $DB;
 psql $DB  < gettupd.sql > $DB.out
done
exit 0

gettupd.sql

\a
\t
\o /tmp/tupd.sql
select 'SELECT \'' || relname || '\', * from pgstattuple('''||schemaname||
'.'||relname||''');' from pg_stat_user_tables ;
\o
\i /tmp/tupd.sql
\! rm /tmp/tupd.sql
\a
\t

PSQL is just the way to go…  :)


Shoaib Mir
shoaibmir[@]gmail.com

A quick way to change index tablespace

September 29, 2009 shoaibmir 2 comments

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

September 23, 2009 shoaibmir 2 comments

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

September 13, 2009 shoaibmir 2 comments

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

September 13, 2009 shoaibmir Leave a comment

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

Categories: Operating Systems Tags: , , ,

Configuring SSH access for VirtualBox Guest Host

September 13, 2009 shoaibmir 3 comments

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

Categories: Operating Systems Tags: , , ,

Building Boost under Solaris

August 12, 2009 shoaibmir Leave a comment

Boost provides free peer-reviewed portable C++ source libraries. I have been using another library “libiqxmlrpc” in my application and that used Boost, now when porting my application to Solaris I had to build Boost from source which I thought would be very simple but it wasnt…

Here is what I had to do:

- Once you have got the latest source for boost, build bjam (build environment setup scripts) from within the Boost source code folder…that is very straight-forward
- After bjam is successfully build, add it to your PATH to make things simpler
- No go to the Boost source root folder and run the following

bjam toolset=sun stdlib=sun-stlport instruction-set=i386 address-model=32
--prefix=/opt/boost/ --libdir=/opt/boost/lib/ --includedir=/opt/boost/include/

Which is good for 32 bit Solaris and with Sun Studio installed. In case you are using 64 bit change the address-model accordingly and same goes for instruction-set that if you are on AMD use the option as “amd” instead of i386.

The build process will not work just with the default option so these options have to be added. Once the build is started it sure does take quite a while so enjoy a coffee during that time :)


Shoaib Mir
shoaibmir[@]gmail.com

Categories: C/C++ Tags: , ,

Setting up Slony cluster with PerlTools

August 5, 2009 shoaibmir 2 comments

A few days back while I was giving a training on Slony and was showing the students steps to building a Slony cluster using Perltools, they asked me to write down the steps for them as it was very hard to memorize everything. I just did that and thought of sharing it with everyone as it might help.

First of all make sure you have the perltools available for Slony and once you have confirmed that, follow the steps given below:

- Edit the slon_tools.conf file according to your cluster needs, i.e. change the nodes, sets information and tables to be replicated. This file by default is located in /usr/local/etc/slon_tools.conf and the Perltools will by default look into this directory for the configuration file.

- Now first of all you need to initialize the cluster

slonik_init_cluster | slonik

- Once that step is successful, start the Slon processes on each of the nodes involved

- Create the Sets, in our case we just had one set so the process goes as

slonik_create_set 1 | slonik

(Where 1 is the ID of the first set which we had set in the slon_tools.conf file)

- Subscribe the sets now, in our case we had just two nodes and one set

slonik_subscribe_set 1 2 | slonik

(Where 1 is the ID of the set and 2 is the node to which you want to subscribe)

- Once the subscription is successfull you should have replication all working now, which can be tested by adding a row in one of the replicated tables on master and verifying the change in your slave node.

Perltools really make life easy and takes rid of those extra steps for creating Slonik scripts yourself . Once you have the slon_tools.conf properly configured it just takes a few minutes generating all the required scripts.

Just make sure when you make any changes to your Slony cluster like adding new sets, new tables, merging sets and doing switchovers or failover… keep on updating the slon_tools.conf file regularly as then it will not take much time rebuilding all scripts for the latest cluster in case that is needed.


Shoaib Mir
shoaibmir[@]gmail.com

Categories: PostgreSQL Tags: , ,