If you’re running your database on an SSD instead of a spinning disk, you might want to optimize postgres table space cost:
-- 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;
Sometime we want to debug why ant build failed when executing a certain JUnit
Make sure your ant junit task look like the following
:
:
and not like
:
:
#!/bin/bash
sync
echo 1 > /proc/sys/vm/drop_caches
service postgresql-9.3 restart
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'
Create a postgres function to devide-and-conquer (iterate) your big query
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();
# 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
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;
#!/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