Code Snippets

From Wiki

Useful code-snippets

Contents 1 Database-ish 1.1 linked table (use PG table in H2) 1.2 Postgresql stats for io 1.3 Postgresql obtain sizes of all databases: 2 System admin 2.1 Move all processes to cpus 0 and 1, freeing all others 2.2 foxit/wine as default pdf opening tool under ubuntu/linux 2.3 aliases for ubuntu (mc, mcedit, ls) 2.4 from file collection, extract and aggregate info 2.5 Sort and unify data from multiple files 2.6 remove .svn folders 2.7 cat, sort and sed result files 3 DB Benchmarking 3.1 aggregating CLT results from database for plotting 3.2 insert cpu on config in TPC-Tools results db (h2) 3.3 PyxPlot - format output: 3.4 Warm-up PostgreSQL before querying.

Database-ish

linked table (use PG table in H2)

create linked table link('org.postgresql.Driver', 'jdbc:postgresql://localhost:5000/tpcw100_10000', 'postgres', 'pass', 'temp_src');
CREATE TABLE temp_dest (a integer, b text);
insert into temp_dest (select * from link);
drop table link;
SELECT * FROM TEMP_DEST;

Postgresql stats for io

Compute just the block reads and hits for heaps and indexes:

select sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit from pg_statio_user_tables;

Postgresql obtain sizes of all databases:

SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

System admin

Move all processes to cpus 0 and 1, freeing all others

Linux Taskset based approach - Thanks to Ionut Subasu.

#!/bin/bash
for p in `ps -A -o pid`;
do
        taskset -pc 0-1 $p
done

Updated - I wrote a new version that moves only processes with PIDS > 2000 (or maybe 1000???) s.t. it will not cause any errors on critical process

#!/bin/bash
for p in `ps -A -o pid`;
do
    TEMP=$p
    if [[ $TEMP -gt 2000 ]]
    then
        taskset -pc 0 $p
    fi
done

foxit/wine as default pdf opening tool under ubuntu/linux

Save under

sudo gedit /usr/bin/foxit

chmod a+x /usr/bin/foxit

Considering that you placed the binary (re)named Foxit.exe in

C:\foxit\Foxit.exe

Then right click on a pdf and select /usr/bin/foxit as appl to use

#!/bin/sh
cd ~/.wine/drive_c/foxit
if [ "$1" != "" ]; then
var1="`echo $1 | sed 's/\//\\\/g'`"
echo $var1
var2="Z:${var1}"
wine Foxit.exe "$var2"
else
wine Foxit.exe
fi

aliases for ubuntu (mc, mcedit, ls)

From ~/.bashrc

alias ls='ls -al --color=yes'
alias gaga='screen mcedit'
alias mc='screen mc'

from file collection, extract and aggregate info

for i in {1..75}
do
    cat output2_"$i".html | grep img | grep -o 't:[0-9]*\.[0-9]*'>> output2_grouped.html
done

Sort and unify data from multiple files

cat *.data | sort > new.data

remove .svn folders

find . -name .svn -print0 | xargs -0 rm -rf

cat, sort and sed result files

cat tpcw_txRun_* | sed -e 's/[      ]/:/g' | sort -g -t : -k 1 -k 3 -k 2 | sed -e 's/[:]/   /g'

Remark: the TAB character under bash can be inputted using CTRL+V and the TAB key.

DB Benchmarking

aggregating CLT results from database for plotting

SELECT DATA_ID as ID, L, T, RT_in_MS, AL, DBSIZE, CLIENTS, DEPLOYMENTDETAILS FROM (
	    SELECT ID as DATA_ID, L, T, RTIMS as RT_in_MS, AL from(
		SELECT config_id as ID, sum (load)/(avg(duration)/1000) as L, sum(throughput)/(avg(duration)/1000) as T, avg(duration)/1000 as D, sum(rt)/1000 as R, sum(rt)/sum(throughput) as RTIMS, (SUM(LOAD)+SUM(INQUEUE)+SUM(UNOBTAINED))/avg(duration)*1000 as AL FROM 
		    POSTGRESQL_TPC_WB.PRESSURE group by config_id)) as DATA
	join POSTGRESQL_TPC_WB.CONFIG as CONF on DATA.DATA_ID = CONF.ID;

set @cid_min_incl = 1;
set @cid_max_incl = 448;
SELECT ID, AL, T, RT_in_MS, DEPLOYMENTDETAILS  FROM(
	SELECT DATA_ID as ID, L, T, RT_in_MS, AL, DBSIZE, CLIENTS, DEPLOYMENTDETAILS FROM (
	    SELECT ID as DATA_ID, L, T, RTIMS as RT_in_MS, AL from(
		SELECT config_id as ID, sum (load)/(avg(duration)/1000) as L, sum(throughput)/(avg(duration)/1000) as T, avg(duration)/1000 as D, sum(rt)/1000 as R, sum(rt)/sum(throughput) as RTIMS, (SUM(LOAD)+SUM(INQUEUE)+SUM(UNOBTAINED))/avg(duration)*1000 as AL FROM 
		    POSTGRESQL_TPC_WB.PRESSURE group by config_id)) as DATA
	join POSTGRESQL_TPC_WB.CONFIG as CONF on DATA.DATA_ID = CONF.ID)
WHERE DEPLOYMENTDETAILS = 1 and ID >= @cid_min_incl and ID <= @cid_max_incl;

insert cpu on config in TPC-Tools results db (h2)

set @step=28;                   -- how many in one run
set @cpus=4;                    -- cpu number to insert
set @it = 1;                    -- which iteration - this grows for each db size
set @offset = 16 * @step * @it; -- adjust 16 to number of CPUs 
--select @step, @cpus, @offset;
update POSTGRESQL_TPC_WB.CONFIG set deploymentdetails=@cpus where id > @offset + (@cpus-1)*@step and id <= @offset + @cpus*@step;
SELECT * FROM POSTGRESQL_TPC_WB.CONFIG order by id;

PyxPlot - format output:

set papersize a4

set terminal postscript colour
set output 'output.eps'

set xlabel 'X-Axis Label'
set ylabel 'Y-Axis Label'
set title 'Title'

scale = 1.5
set size ratio 1.0
set width 10*scale

Warm-up PostgreSQL before querying.

For this we push the data directory through the disk cache of the OS. If the size of the database is smaller than that of the RAM we can push the entire DB in disk cache. Otherwise a more selective cache warmup should be done. Still remember that the shared_buffers of the PostgreSQL are still not warmed up.

def warmupDatabase(dbName)
  dirName = ''
  dbRoot = '/path/to/database/cluster/pgdata/'
  dbName2Dir = File.open(dbRoot + 'global/pg_database', 'r')
  dbName2Dir.each_line do |l|
    if l.include?(dbName)
      dirName = l.split()[1]
      break
    end
  end
  dbName2Dir.close
  if dirName != ''
    cmd = 'cat ' + dbRoot + 'base/' + dirName + '/* > /dev/null'
    puts cmd
    system(cmd)
  end
end