Como identificar tabelas e índices inchados no PostgreSQL?

Como? Com essa query:

  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
      (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
      (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
          SELECT 1+COUNT(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
          (SELECT current_setting('block_size')::NUMERIC) AS bs,
          CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

Converter vídeo x265/HEVC para x264

X265_CodecPorque converter um vídeo encodado em x265/HEVC para x264? O x265 é mais novo, o arquivo fica bem menor praticamente metade do tamanho de um x264. Bom, se você pretende assistir o vídeo em um Raspberry PI você vai precisar fazer isso, pois a CPU/GPU do Raspberry Pi não dá conta de tocar o x265.

Este simples script converte todos os x265 com extensão .mkv no diretório para x264. Enjoy!

for i in *.mkv ; do
    ffmpeg -i "$i" -bsf:v h264_mp4toannexb -sn -map 0:0 -map 0:1 -vcodec libx264 "$i.ts"
    mv "$i.ts" "$i.mpg"
    sleep 3


Como instalar o ffmpeg no Centos7

Neste post irei explicar como instalar o ffmpeg (e mais algumas coisas) no Centos7, usando repositórios não oficiais. Não garanto que eles estejam atualizados, use por conta e risco.

yum -y install epel-release
yum -y install
yum -y install

yum install -y gstreamer gstreamer1 gstreamer-ffmpeg gstreamer-plugins-good gstreamer-plugins-ugly
yum install -y vlc smplayer ffmpeg HandBrake-{gui,cli}
yum install -y libdvdcss gstreamer{,1}-plugins-ugly gstreamer-plugins-bad-nonfree gstreamer1-plugins-bad-freeworld

yum install -y
yum install -y flash-plugin
yum install -y icedtea-web


Como limpar o filtro de ar da Yamaha DT 180

IMG_20150723_192343 IMG_20150723_192437 IMG_20150723_192455IMG_20150723_192510IMG_20150723_192522

É bem simples. Basta remover os 4 parafusos da tampa para ter acesso ao filtro. Depois remova os outros 4 parafusos que seguram o suporte da espuma, que faz o papel de filtrar o ar. Pronto, agora limpe todas as peças e a parte interna da cavidade do filtro de ar. A espuma você deve limpá-la com tinner/aguarrás, que toda a sujeira misturada com óleo sai facilmente. Depois monte tudo no lugar. As fotos abaixo detalham todo o processo.


Howto VPN L2TP Pre-Shared Key

Tested on: CentOS 6.6
Tools used: Strongswan ( for IPSec tunnel, Xl2tpd ( for Layer 2 Tunneling Protocol (L2TP) daemon and ppp.


[root@centos02 ~]# yum install epel-release
[root@centos02 ~]# yum install strongswan ppp xl2tpd

Part 1: Configure Strongswan

Edit the following files:

[root@centos02 ~]# vi /etc/strongswan/ipsec.conf
# ipsec.conf – strongSwan IPsec configuration file
config setup
        #charondebug=”ike 4, knl 4, cfg 2″    #useful debugs
conn %default
        right=        # IP of your VPN Server

Add your pre-shared key here:

[root@centos02 ~]# vi /etc/strongswan/ipsec.secrets
# /etc/ipsec.secrets – strongSwan IPsec secrets file
: PSK “minhapresharedkey”                 # Pre-Shared Key

Set strongswan to start on boot:
[root@centos02 ~]# chkconfig strongswan on

Start strongswan service:
[root@centos02 ~]# /etc/init.d/strongswan start

Try the ipsec:
[root@centos02 ~]# strongswan up L2TP-PSK-CLIENT

If you get the line below, your IPSec tunnel is working:
connection ‘L2TP-PSK-CLIENT’ established successfully

To shutdown the IPSec tunnel, run:
[root@centos02 ~]# strongswan down L2TP-PSK-CLIENT

Part 2: Configure Xl2tpd

Edite the config file:

[root@centos02 ~]# vi /etc/xl2tpd/xl2tpd.conf
force userspace = yes
;debug tunnel = yes
; Connect as a client to a server at
[lac L2TPserver]
lns =
require chap = yes
refuse pap = yes
require authentication = yes
; Name should be the same as the username in the PPP authentication!
name = gustfn
;ppp debug = yes
pppoptfile = /etc/ppp/options.l2tpd.client
length bit = yes

And this one:

[root@centos02 ~]# vi /etc/ppp/options.l2tpd.client
idle 1800
mtu 1200
mru 1200
connect-delay 5000

name gustfn
password MinhaSenhaDaVpn

Add here your user and password for VPN:

[root@centos01 ~]# vi /etc/ppp/chap-secrets
# Secrets for authentication using CHAP
# client    server    secret            IP addresses
gustfn        *        MinhaSenhaDaVpn

To make things easier, let’s create two scripts: one for up the VPN and other for down the VPN:

[root@centos02 ~]# vi
# Create a tunnel IPSec with Pre-Shared Key
strongswan up L2TP-PSK-CLIENT | grep “established successfully”
# start the ppp connection and autenticate with your user/pass
echo “c L2TPserver” > /var/run/xl2tpd/l2tp-control
sleep 5
# Important: You need to add here the routes of your VPN network
route add -net netmask dev ppp0
# And delete this one manually
route del

[root@centos02 ~]# vi
echo “d L2TPserver” > /var/run/xl2tpd/l2tp-control
strongswan down L2TP-PSK-CLIENT

And make both scripts executable:
root@centos02 ~]# chmod +x
root@centos02 ~]# chmod +x

Set xl2tpd to start on boot:
[root@centos02 ~]# chkconfig xl2tpd on

Start the Xl2tpd daemon:
[root@centos02 ~]# /etc/init.d/xl2tpd start

Done! Now let’s try to check if the VPN is working!
[root@centos02 ~]# ./
connection ‘L2TP-PSK-CLIENT’ established successfully

Great!! Now I’m just trying to ping an IP from the other side:
[root@centos02 ~]# ping
PING ( 56(84) bytes of data.
64 bytes from icmp_seq=1 ttl=63 time=232 ms
64 bytes from icmp_seq=2 ttl=63 time=181 ms
64 bytes from icmp_seq=3 ttl=63 time=197 ms

The VPN L2TP is working, good job! To shutdown the VPN, just run:
[root@centos02 ~]# ./

How to build a simple affiliate API using OpenResty


In this post I’ll discuss about how to create a simple API to provide simple access to a common affiliate partner program. The idea is to have a URL that accepts a few arguments (in our case, we will take the args called partner, subid, gender and route.

To do that, we’ll use the powerful nGinx-with-steroids called OpenResty. My compliments to Yichun Zhang (agentzh), the head of the project.

First of all, download and unpack the OpenResty. (I’ll suppress some simple steps in this doc.)


tar zxvfp ngx_openresty-
cd ngx_openresty-
 ./configure --prefix=/usr/local/openresty --with-http_postgres_module
gmake -j4
gmake install
vi /usr/local/openresty/nginx/conf/nginx.conf

# Content of file nginx.conf
worker_processes 4;
events {}
error_log logs/error.log debug;

http {
 upstream database {
 postgres_server dbname=mydatabase user=postgres password=mypassword123;
 server {
 server_name localhost;
 root /usr/local/openresty/nginx/html;

 location /postgresquery {
 postgres_pass database;
 set_unescape_uri $id $arg_id;
 set_unescape_uri $subid $arg_subid;
 postgres_escape $id;
 postgres_escape $subid;
 postgres_escape $referencia $http_referer;

 GET "INSERT INTO mytable01 (id, subid, referer) VALUES ($id, $subid, $referencia) 
RETURNING clickid";
 postgres_output value;
 postgres_rewrite changes 200;

 location /campaign {
 content_by_lua ' 
 local res = ngx.location.capture("/postgresquery", { args = { id = ngx.var.arg_id, 
subid = ngx.var.arg_subid, http_referer = ngx.var.http_referer } } )
 if res.status == 200 and res.body then

 local cookie_name_click = "COOKIE_CLICK="
 local cookie_value_click = res.body
 local cookie_click = cookie_name_click .. cookie_value_click

 local cookie_name_id = "COOKIE_ID="
 local cookie_value_id = ngx.var.arg_id
 local cookie_id = cookie_name_id .. cookie_value_id

 if ngx.var.arg_subid then

 local cookie_name_subid = "COOKIE_SUBID="
 local cookie_value_subid = ngx.var.arg_subid
 local cookie_subid = cookie_name_subid .. cookie_value_subid

 if ngx.var.arg_gender then

 local cookie_name_gender = "COOKIE_GENDER=" 
 local cookie_value_gender = ngx.var.arg_gender
 local cookie_gender = cookie_name_gender .. cookie_value_gender

 ngx.header["Set-Cookie"] = {cookie_click, cookie_id, cookie_subid, cookie_gender}
 ngx.header["Set-Cookie"] = {cookie_click, cookie_id, cookie_subid}
 if ngx.var.arg_gender then

 local cookie_name_gender = "COOKIE_GENDER="
 local cookie_value_gender = ngx.var.arg_gender
 local cookie_gender = cookie_name_gender .. cookie_value_gender

 ngx.header["Set-Cookie"] = {cookie_click, cookie_id, cookie_gender}
 ngx.header["Set-Cookie"] = {cookie_click, cookie_id}

 if ngx.var.arg_route == "photos" then
 return ngx.redirect("http://mywebsite.priv/photos")
 elseif ngx.var.arg_route == "videos" then
 return ngx.redirect("http://mywebsite.priv/videos")
 return ngx.redirect("http://mywebsite.priv/")

# End of file nginx.conf

I’ll explain what this webservice does. It accept an URL of this kind:

Where is the IP of my OpenResty server running on port 8080.

When this URL reaches the server, the nGinx Lua capture the request and make an internal request, to /postgresquery, passing the arguments id, subid and http_referer. The location /postgresquery, that can be accessed only internally, treats the arguments and makes the insert in the database. And return as output the result of column named clickid.

Next step is to check if the response of the request to /postgresquery has returned 200 (if res.status == 200) and if there is any value returned by the request (res.value). So the program creates two cookies, called COOKIE_CLICK and COOKIE_ID, which contains the number of the click of the origin request, inserted on our table called mytable01, and the ID of the partner, that was given by query string argument $id.

Following we check if there are two opcional arguments called subid and gender and if exists, create the proper cookies.

At last the check if there is an argument called route and redirect the user to the proper location on our website, after processed the origin click from a partner website.

The next image shows the whole process.

Screen Shot 2015-03-10 at 16.44.59

Why the choose to do that on OpenResty? Because this process is very fast and this is important when you are dealing with a high number of requests concurrently. The user will not be noticed of this process and your server will be grateful for little-used resources.

Factory Reset of Apache CloudStack 4.x on CentOS/RH

Just do it.

/etc/init.d/cloudstack-management stop
mysql -e ‘drop database cloud’
mysql -e ‘drop database cloud_usage’
cloudstack-setup-databases cloud:password@localhost –deploy-as=root
/etc/init.d/cloudstack-management start

Exemplo de vlan tagging no Centos6/7

[root@cloudstack01 network-scripts]# cat ifcfg-eth0:1





















Ensuring the maximum performance of your CPU on XenServer environment.

I’ll be straight forward on this article. If you want to ensure your application respond quicker than possible, one of (many) things you need to be sure is if you CPU is running at the maximum clock possible.

Today almost all CPUs have a lot of ‘states’, created basically to save energy lowering the clock (and the current) of the CPU. The CPU rises the clock just only it detect a significant load/demand over it.

If you have a XenServer, you can change the governor algorithm from the default ‘on demand’ to ‘performance’. Let’s see an example below. On the Dom-0, run the command:

# xenpm get-cpufreq-states
cpu id : 1
total P-states : 16
usable P-states : 16
current frequency : 1200 MHz
P0 : freq [2901 MHz]
P1 : freq [2900 MHz]
P2 : freq [2800 MHz]
P3 : freq [2700 MHz]
P4 : freq [2500 MHz]
P5 : freq [2400 MHz]
P6 : freq [2300 MHz]
P7 : freq [2200 MHz]
P8 : freq [2000 MHz]
P9 : freq [1900 MHz]
P10 : freq [1800 MHz]
P11 : freq [1700 MHz]
P12 : freq [1600 MHz]
P13 : freq [1400 MHz]
P14 : freq [1300 MHz]
*P15 : freq [1200 MHz]
I suppressed a lot of lines just to focus where is important. As we can see, the CPU1 (core) is running on 1200 Mhz, but the highest clock speed possible is 2901 Mhz (P0 state). Let’s see the clock of all cores:

# xenpm get-cpufreq-states | grep current
current frequency : 1200 MHz
current frequency : 1200 MHz
current frequency : 1200 MHz
current frequency : 1300 MHz
current frequency : 1200 MHz
current frequency : 1200 MHz
current frequency : 1200 MHz
current frequency : 2901 MHz

Most of the cores are running at the lowest speed, affecting the performance of all virtual machines and services running over it. To change the value to the maximum clock speed of all cores at same time, simple run the following command:

# xenpm set-scaling-governor performance
And to make this persistent to next reboot:

# /opt/xensource/libexec/xen-cmdline –set-xen cpufreq=xen:performance
Let’s check the clock of all cores again.

# xenpm get-cpufreq-states|grep current
current frequency : 2901 MHz
current frequency : 2901 MHz
current frequency : 2901 MHz
current frequency : 2901 MHz
current frequency : 2901 MHz
current frequency : 2901 MHz
current frequency : 2901 MHz
current frequency : 2901 MHz

Now we are running at top speed! Usually you can see an immediate benefit on database servers. Be aware of changing this characteristic your server will consume more energy, will produce more noise, will rise the temperature and will demand more from air conditioning. But if you host your server on a good datacenter you do not have to worry about it. 🙂

First published on

Ten Rules for Web Startups

If you think you have a good idea and want to open a business, please read this text. Keep in mind these few fundamental characteristics when you started to plan and build your product and company.


#1: Be Narrow
Focus on the smallest possible problem you could solve that would potentially be useful. Most companies start out trying to do too many things, which makes life difficult and turns you into a me-too. Focusing on a small niche has so many advantages: With much less work, you can be the best at what you do. Small things, like a microscopic world, almost always turn out to be bigger than you think when you zoom in. You can much more easily position and market yourself when more focused. And when it comes to partnering, or being acquired, there’s less chance for conflict. This is all so logical and, yet, there’s a resistance to focusing. I think it comes from a fear of being trivial. Just remember: If you get to be #1 in your category, but your category is too small, then you can broaden your scope—and you can do so with leverage.

#2: Be Different
Ideas are in the air. There are lots of people thinking about—and probably working on—the same thing you are. And one of them is Google. Deal with it. How? First of all, realize that no sufficiently interesting space will be limited to one player. In a sense, competition actually is good—especially to legitimize new markets. Second, see #1—the specialist will almost always kick the generalist’s ass. Third, consider doing something that’s not so cutting edge. Many highly successful companies—the aforementioned big G being one—have thrived by taking on areas that everyone thought were done and redoing them right. Also? Get a good, non-generic name. Easier said than done, granted. But the most common mistake in naming is trying to be too descriptive, which leads to lots of hard-to-distinguish names. How many blogging companies have “blog” in their name, RSS companies “feed,” or podcasting companies “pod” or “cast”? Rarely are they the ones that stand out.

#3: Be Casual
We’re moving into what I call the era of the “Casual Web” (and casual content creation). This is much bigger than the hobbyist web or the professional web. Why? Because people have lives. And now, people with lives also have broadband. If you want to hit the really big home runs, create services that fit in with—and, indeed, help—people’s everyday lives without requiring lots of commitment or identity change. Flickr enables personal publishing among millions of folks who would never consider themselves personal publishers—they’re just sharing pictures with friends and family, a casual activity. Casual games are huge. Skype enables casual conversations.

#4: Be Picky
Another perennial business rule, and it applies to everything you do: features, employees, investors, partners, press opportunities. Startups are often too eager to accept people or ideas into their world. You can almost always afford to wait if something doesn’t feel just right, and false negatives are usually better than false positives. One of Google’s biggest strengths—and sources of frustration for outsiders—was their willingness to say no to opportunities, easy money, potential employees, and deals.

#5: Be User-Centric
User experience is everything. It always has been, but it’s still undervalued and under-invested in. If you don’t know user-centered design, study it. Hire people who know it. Obsess over it. Live and breathe it. Get your whole company on board. Better to iterate a hundred times to get the right feature right than to add a hundred more. The point of Ajax is that it can make a site more responsive, not that it’s sexy. Tags can make things easier to find and classify, but maybe not in your application. The point of an API is so developers can add value for users, not to impress the geeks. Don’t get sidetracked by technologies or the blog-worthiness of your next feature. Always focus on the user and all will be well.

#6: Be Self-Centered
Great products almost always come from someone scratching their own itch. Create something you want to exist in the world. Be a user of your own product. Hire people who are users of your product. Make it better based on your own desires. (But don’t trick yourself into thinking you areyour user, when it comes to usability.) Another aspect of this is to not get seduced into doing deals with big companies at the expense or your users or at the expense of making your product better. When you’re small and they’re big, it’s hard to say no, but see #4.

#7: Be Greedy
It’s always good to have options. One of the best ways to do that is to have income. While it’s true that traffic is now again actually worth something, the give-everything-away-and-make-it-up-on-volume strategy stamps an expiration date on your company’s ass. In other words, design something to charge for into your product and start taking money within 6 months (and do it with PayPal). Done right, charging money can actually accelerate growth, not impede it, because then you have something to fuel marketing costs with. More importantly, having money coming in the door puts you in a much more powerful position when it comes to your next round of funding or acquisition talks. In fact, consider whether you need to have a free version at all. The TypePadapproach—taking the high-end position in the market—makes for a great business model in the right market. Less support. Less scalability concerns. Less abuse. And much higher margins.

#8: Be Tiny
It’s standard web startup wisdom by now that with the substantially lower costs to starting something on the web, the difficulty of IPOs, and the willingness of the big guys to shell out for small teams doing innovative stuff, the most likely end game if you’re successful is acquisition. Acquisitions are much easier if they’re small. And small acquisitions are possible if valuations are kept low from the get go. And keeping valuations low is possible because it doesn’t cost much to start something anymore (especially if you keep the scope narrow). Besides the obvious techniques, one way to do this is to use turnkey services to lower your overhead—Administaff,ServerBeach, web apps, maybe even Elance.

#9: Be Agile
You know that old saw about a plane flying from California to Hawaii being off course 99% of the time—but constantly correcting? The same is true of successful startups—except they may start out heading toward Alaska. Many dot-com bubble companies that died could have eventually been successful had they been able to adjust and change their plans instead of running as fast as they could until they burned out, based on their initial assumptions. Pyra was started to build a project-management app, not Blogger. Flickr’s company was building a game. Ebay was going to sell auction software. Initial assumptions are almost always wrong. That’s why the waterfall approach to building software is obsolete in favor agile techniques. The same philosophy should be applied to building a company.

#10: Be Balanced
What is a startup without bleary-eyed, junk-food-fueled, balls-to-the-wall days and sleepless, caffeine-fueled, relationship-stressing nights? Answer?: A lot more enjoyable place to work. Yes, high levels of commitment are crucial. And yes, crunch times come and sometimes require an inordinate, painful, apologies-to-the-SO amount of work. But it can’t be all the time. Nature requires balance for health—as do the bodies and minds who work for you and, without which, your company will be worthless. There is no better way to maintain balance and lower your stress that I’ve found than David Allen’s GTD process. Learn it. Live it. Make it a part of your company, and you’ll have a secret weapon.

#11 (bonus!): Be Wary
Overgeneralized lists of business “rules” are not to be taken too literally. There are exceptions to everything.