Postgres SSD optimization

Posted on April 13, 2016 by ronaldpringadi

If you’re running your database on an SSD instead of a spinning disk, you might want to optimize postgres table space cost:

1
2
3
4
5
6
7
8
9
10
-- Change the tablespace cost
ALTER TABLESPACE pg_default SET ( seq_page_cost = 20,  random_page_cost = 1 );
-- Verifiy the change
SELECT * FROM pg_tablespace;


-- Undo the tablespace cost
ALTER TABLESPACE pg_default RESET  ( seq_page_cost,  random_page_cost);
-- Verifiy the change
SELECT * FROM pg_tablespace;
Posted in Database, PostgreSQL | Leave a comment

Ant Junit debugging

Posted on April 13, 2016 by ronaldpringadi

Sometime we want to debug why ant build failed when executing a certain JUnit
Make sure your ant junit task look like the following

1
2
3
4
<junit printsummary="withOutAndErr" haltonfailure="yes">
:
:
</junit>

and not like

1
2
3
4
<junit printsummary="yes" haltonfailure="yes">
:
:
</junit>
Posted in Uncategorized | Leave a comment

Delete Postgres Cache

Posted on April 13, 2016 by ronaldpringadi
1
2
3
4
#!/bin/bash
sync
echo 1 > /proc/sys/vm/drop_caches
service postgresql-9.3 restart
Posted in Database, PostgreSQL | Leave a comment

Print call stack in Java from anywhere

Posted on March 22, 2016 by ronaldpringadi
1
 LOG.trace(ExceptionUtils.getStackTrace(new Throwable()));
Posted in java | Leave a comment

Check which database object depends on (has reference to) your table

Posted on February 12, 2016 by ronaldpringadi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT R.TABLE_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    ON U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    AND U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    AND U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE
-- THIS IS WHERE YOUR INTEREST IS (YOUR TABLE)
U.COLUMN_NAME = 'THE_COLUMN_NAME'
AND U.TABLE_CATALOG = 'THE_DB_NAME'
AND U.TABLE_SCHEMA = 'THE_SCHEMA_NAME'
AND U.TABLE_NAME = 'THE_TABLE_NAME'
Posted in Database, PostgreSQL | Leave a comment

Postgres function to devide-and-conquer (iterate) your big query

Posted on February 8, 2016 by ronaldpringadi

Create a postgres function to devide-and-conquer (iterate) your big query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
TRUNCATE TABLE YOUR_NEW_BIG_TABLE;

DROP FUNCTION IF EXISTS pg_iterator();

CREATE OR REPLACE FUNCTION pg_iterator()
  RETURNS void AS
$BODY$
DECLARE
    vOffsetRecord INT;
    vTotal INT;
    vLimit INT;
    vOffset INT;
BEGIN
   
    -- How many?
    -- select count(*) INTO currentRecord from YOUR_BIG_TABLE; -- 346,879,848
    vTotal := 346879848;
    --  vTotal := 5000;
    vLimit  := 100000;
    vOffset :=0;
   
    RAISE NOTICE 'DEBUG vTotal : %', vTotal;
    RAISE NOTICE 'DEBUG vLimit : %', vLimit;
    RAISE NOTICE 'DEBUG vOffset: %', vOffset;

    LOOP
        IF (vOffset >=  vTotal) THEN
            EXIT;
        END IF;
        EXECUTE format('INSERT INTO YOUR_NEW_BIG_TABLE (id, language, hash) SELECT id, language, hash FROM YOUR_BIG_TABLE ORDER BY id LIMIT %s OFFSET %s;', vLimit, vOffset);
        RAISE NOTICE 'DEBUG vOffset: %', vOffset;
        vOffset := vOffset + vLimit;
    END LOOP;
   
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pg_iterator()
  OWNER TO postgres;

SELECT pg_iterator();
Posted in Database, PostgreSQL | Leave a comment

Crontab header

Posted on February 1, 2016 by ronaldpringadi
1
2
3
4
5
6
# minute (0-59),
# |      hour (0-23),
# |      |       day of the month (1-31),
# |      |       |       month of the year (1-12),
# |      |       |       |       day of the week (0=Sunday 1=Monday 2=Tuesday 3=Wednesday 4=Thursday 5=Friday 6=Saturday).
# |      |       |       |       |       commands
Posted in Bash, Linux | Leave a comment

Add user in mysql

Posted on January 17, 2016 by ronaldpringadi
1
2
3
CREATE USER 'YOUR_USER_NAME'@'CONNECTING_FROM_WHERE' IDENTIFIED BY 'THIS_USER_PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'YOUR_USER_NAME'@'CONNECTING_FROM_WHERE' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Posted in MySQL | Leave a comment

Using grep as highlighter

Posted on December 18, 2015 by ronaldpringadi
1
$ grep --color -E '^|pattern1|pattern2' file name
Posted in Uncategorized | Leave a comment

TCL programming

Posted on November 12, 2015 by ronaldpringadi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
#!/usr/bin/expect -f
# TCL script. Beware whitespace matter!

# To set multi-line comments do the following
# set comment {
#      your multi-line comment
# }

# ###################################################
# ALL functions Start
# ###################################################

if {[llength $argv] == 0} {
  send_user "Usage: scriptname FUNCTION_NAME\n"
  send_user "Example script.exp updateSomething\n"
  exit 1
}
set functionName [lindex $argv 0]

proc simpleTest {} {
    set aProductionServers  {your_server_4 your_server_5 your_server_6 your_server_1 your_server_2 your_server_3}
    for {set i 0} {$i < [llength $aProductionServers]} {incr i}  {
        send_user "Processing ... '[lindex $aProductionServers $i]'\n"
        set timeout 60
        spawn ssh root@[lindex $aProductionServers $i]

        expect "*assword: "
        send "\r"

        send "hostname\r"
        expect "[lindex $aProductionServers $i]"

        send "date\r"
        expect "2015"
        send "exit\r"
    }
}
# simpleTest

proc printPass {} {
    send_user "\n\[PASS\] \n"
}

proc printFail {} {
    send_user "\n\[FAIL\] \n"
    exit 1
}

#Untar Lucene indexes
proc UntarLuceneindexes {} {
    set aLuceneServers  {your_server_1 your_server_2 your_server_3}
    set sCurrentLuceneTar "index2_20151009.tar.bz2"
    for {set i 0} {$i < [llength $aLuceneServers]} {incr i}  {
        send_user "\n\n"
        send_user "================================================\n"
        send_user "Processing ... '[lindex $aLuceneServers $i]'\n"
        send_user "================================================\n"
        set timeout 60
        spawn ssh root@[lindex $aLuceneServers $i]

        expect "*assword: "
        send "YOUR_SERVER_PASSWORD\r"
       
        send "hostname\r"
        expect "[lindex $aLuceneServers $i]"
       
        send "su - path\r"
        send "pwd\r"
        expect "/home/path"
        send "id\r"
        expect "path"
       
        send "ls $sCurrentLuceneTar|wc -l\r"
        expect "1"
       
        send "tar -xjvf $sCurrentLuceneTar &\r"
        send "sleep 2\r"
        send "ps aux|grep index2|grep tar|wc -l\r"
        send "disown %1"
        expect "1"
       
        send_user "Done processing ... '[lindex $aLuceneServers $i]'"
    }
}


proc updateDBRef {} {
    set apathServers  {your_server_6}
    set newDB "some_db_20150925"
    for {set i 0} {$i < [llength $apathServers]} {incr i}  {
        send_user "\n\n"
        send_user "================================================\n"
        send_user "Processing ... '[lindex $apathServers $i]'\n"
        send_user "================================================\n"
        set timeout 60
        spawn ssh root@[lindex $apathServers $i]

        expect "*assword: "
        send "YOUR_SERVER_PASSWORD\r"
       
        send "hostname\r"
        expect "[lindex $apathServers $i]"
       
        send "su - path\r"
        send "pwd\r"
        expect "/home/path"
        send "id\r"
        expect "path"
       
        send "cat /home/path/path-current/SomeConfig.properties |grep -v 'com.somecompany.setting.server.db.pathdb2.url=' > /home/path/path-current/SomeConfig.properties.new"
        send "echo 'com.somecompany.setting.server.db.pathdb2.url=jdbc:postgresql://127.0.0.1:5432/$newDB' >> /home/path/path-current/SomeConfig.properties.new"
       
        #  ps aux | grep jar | grep 8806
        #  kill xxxxxx
       
       
        send_user "Done processing ... '[lindex $apathServers $i]'"
    }
}

set aLuceneServers  {your_server_1 your_server_2 your_server_3}
proc stopLuceneServices {} {
    global aLuceneServers
    for {set i 0} {$i < [llength $aLuceneServers]} {incr i}  {
        send_user "\n\n"
        send_user "================================================\n"
        send_user "Processing ... '[lindex $aLuceneServers $i]'\n"
        send_user "================================================\n"
        set timeout 60
        spawn ssh root@[lindex $aLuceneServers $i]

        expect "*assword: "
        send "YOUR_SERVER_PASSWORD\r"
       
        send "hostname\r"
        expect "[lindex $aLuceneServers $i]"
       
        send "service lucene stop\r"
        expect {
            "stopped PID" { printPass }
            "lucene is not running" { printFail }
            "lucene is not running x" { printPass }
            timeout { exit 1 }
            }
       
        send_user "Done processing ... '[lindex $aLuceneServers $i]' \n"
    }
}

proc startLuceneServices {} {
    global aLuceneServers
   
    for {set i 0} {$i < [llength $aLuceneServers]} {incr i}  {
        send_user "\n\n"
        send_user "================================================\n"
        send_user "Processing ... '[lindex $aLuceneServers $i]'\n"
        send_user "================================================\n"
        set timeout 60
        spawn ssh root@[lindex $aLuceneServers $i]

        expect "*assword: "
        send "YOUR_SERVER_PASSWORD\r"
       
        send "hostname\r"
        expect "[lindex $aLuceneServers $i]"
       
        send "service lucene start\r"
        expect "started PID"
       
        send_user "Done processing ... '[lindex $aLuceneServers $i]'"
    }
}

# ###################################################
# ALL functions End
# ###################################################

set timeout 60
log_file -noappend expect.log ;# Default to append to a file. To disable append use: log_file -noappend


# ###################################################
# Calling functionName from, which supplied from command line
# ###################################################

$functionName
Posted in Uncategorized | Leave a comment