Monthly Archive for March, 2016

SQL: get the database size

I’m going on writing primitive posts about the fascinating world of relational database management systems. :)

MySQL, size of all databases:

mysql> SELECT table_schema \
       "Database name", \
       sum( data_length + index_length ) / 1024 / 1024 \
       "Data Base Size in MB" FROM \
       information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------------+
| Database name      | Data Base Size in MB |
+--------------------+----------------------+
| fluxbb             |           0.04585648 |
| information_schema |           0.00781250 |
| mysql              |           0.60614872 |
| wordpress          |           2.22493362 |
+--------------------+----------------------+
4 rows in set (0.13 sec)

PostgreSQL, size of all databases:

cdr=> SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) \
      AS size FROM pg_database;
  datname  |  size   
-----------+---------
 template1 | 6705 kB
 template0 | 6697 kB
 postgres  | 6820 kB
 cdr       | 28 GB

Size of ‘acme_cdr’ table:

cdr=> SELECT pg_size_pretty(pg_total_relation_size('acme_cdr'));
 pg_size_pretty 
----------------
 28 GB

ngrep: SIP traffic analyze

tcpdump is a nice tool, but some filters seem to be too complicated for usage.

It’s quite simple to look at SIP traffic between our server and remote server with tcpdump:

tcpdump -pni eth0 udp and port 5060 and host 1.2.3.4

… or a little bit more verbose:

tcpdump -pni eth0 -v udp and port 5060 and host 1.2.3.4

… or even:

tcpdump -pni eth0 -v -As0 udp and port 5060 and host 1.2.3.4

But how to capture only INVITE messages?
This is the case to use ngrep:

root@voip-ge:~# ngrep -W byline "INVITE sip" port 5060 and host zz.nn.159.114
interface: eth0 (10.219.3.0/255.255.255.0)
filter: (ip or ip6) and ( port 5060 and host zz.nn.159.114 )
match: INVITE sip
#
U xx.yy.94.130:5060 -> zz.nn.159.114:5060
INVITE sip:412753@zz.nn.159.114 SIP/2.0.
v: SIP/2.0/UDP xx.yy.94.130:5060;branch=z9hG4bK51d42193.
Max-Forwards: 70.
f: "SomeCallerID" sip:0606@xx.yy.94.130;tag=as07e569d2.
t: sip:412753@zz.nn.159.114.
m: sip:0606@xx.yy.94.130:5060.
i: 795031de44fe066e3751fdc6218368e7@xx.yy.94.130:5060.
CSeq: 102 INVITE.
User-Agent: Cisco-SIPGateway/IOS-12.x.
Date: Tue, 01 Mar 2016 07:05:13 GMT.
Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY, INFO, PUBLISH, MESSAGE.
k: replaces.
c: application/sdp.
l: 299.
.
v=0.
o=CiscoSystemsSIP-GW-UserAgent 886157825 886157825 IN IP4 xx.yy.94.130.
s=SIP Call.
c=IN IP4 xx.yy.94.130.
t=0 0.
m=audio 19504 RTP/AVP 8 0 101.
a=rtpmap:8 PCMA/8000.
a=rtpmap:0 PCMU/8000.
a=rtpmap:101 telephone-event/8000.
a=fmtp:101 0-16.
a=silenceSupp:off - - - -.
a=ptime:20.
a=sendrecv.