JVM Analysis [For cassandra]

Command to know inside memory utilization in jvm.

java -XX:+PrintFlagsFinal -version | grep -iE 'HeapSize|PermSize|ThreadStackSize'
    uintx AdaptivePermSizeWeight                    = 20              {product}        
     intx CompilerThreadStackSize                   = 0               {pd product}      
    uintx ErgoHeapSizeLimit                         = 0               {product}        
    uintx HeapSizePerGCThread                       = 87241520        {product}        
    uintx InitialHeapSize                          := 1057516032      {product}        
    uintx LargePageHeapSizeThreshold                = 134217728       {product}        
    uintx MaxHeapSize                              := 16921919488     {product}        
    uintx MaxPermSize                               = 85983232        {pd product}      
    uintx PermSize                                  = 21757952        {pd product}      
     intx ThreadStackSize                           = 1024            {pd product}      
     intx VMThreadStackSize                         = 1024            {pd product}      
java version "1.7.0_60"
Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) 64-Bit Server VM (build 24.60-b09, mixed mode)

Cassandra Handy commands

Some handy commands of Cassandra:

To make vertical output from cqlsh.

Use EXPAND ON;

Example :

cqlsh:loadtest> expand on;
Now printing expanded output
cqlsh:loadtest> select * from cmdtest;
@ Row 1
------+------
 id   | 1
 name | amit
(1 rows)

To execute Query from file

SOURCE

cqlsh> use loadtest;
cqlsh:loadtest> source '/tmp/query.sql'
 id | name
----+---------
  1 |    amit
  2 | testing
(2 rows)

CAPTURE :
Captures command output and appends it to a file.

To start capturing the output of a query, specify the path of the file relative to the current directory. Enclose the file name in single quotation marks

cqlsh:loadtest> capture '/tmp/a'
Now capturing query output to '/tmp/a'.
cqlsh:loadtest> select * from cmdtest;
cqlsh:loadtest> exit
cassandra@dell27:~> cat /tmp/a
 id | name
----+---------
  1 |    amit
  2 | testing
(2 rows)

Cassandra os-tuning.

OS TUNING - Cassandra

Below settings should be applied to all node of Cassandra cluster.

File Descriptors : Cassandra needs more file descriptor than default settings of 1024, below is how you can change it.

#echo "* soft nofile 32768" | sudo tee -a /etc/security/limits.conf

#echo "* hard nofile 32768" | sudo tee -a /etc/security/limits.conf

#echo "root soft nofile 32768" | sudo tee -a /etc/security/limits.conf

#echo "root hard nofile 32768" | sudo tee -a /etc/security/limits.conf

User Resource Limits :

Added the following entries in /etc/security/limits.conf file.

# End of file

* soft nofile 32768

* hard nofile 32768

root soft nofile 32768

root hard nofile 32768

* soft memlock unlimited

* hard memlock unlimited

root soft memlock unlimited

root hard memlock unlimited

* soft as unlimited

* hard as unlimited

root soft as unlimited

root hard as unlimited

Run command

#sysctl -w vm.max_map_count=131072

Disable Swap - Lets not jvm gets swapped rather get it killed by oom killer. As even swapped jvm eventually will killed by oom. Better node to down then slow.

# sudo swapoff --all

Synchronize Clocks : The column will only get overwrite if recent version has latest time compare to old value.

NTP is already running on server

One liner linux

Linux -  makes life easy.. lets inspired with Linux

To see a line from big file.

         sed -n 'Xp' theFile, where X is your line number.

e.g          sed -n '42401p' /tmp/bd/soted_voter2.csv

To get more lines.

sed -n  '1127062,+10p' /tmp/gujrat/dhana_gujrat5

To remove a line from big file

sed -i '42401d' /tmp/bd/soted_voter2.csv

Note- If you dont put -i it will show whole output after replacement.

To get Non ascii/foreign characters of a file in red color.

grep --color='auto' -P -n "[\x80-\xFF]" /tmp/bd/soted_voter2.csv

To remove non-ascii chars from a file

tr -cd '\11\12\15\40-\176'  < /tmp/vr_votersorted2.copy > /tmp/bd/cleansortedvoter.copy

To grep pattern wise.

LANG=c;grep INSERT postgresql.log |grep 'Mar  10'|grep -P 'duration: \d\d\d' | cut -d' ' -f4 | cut -d':' -f1,2 | sort | uniq -c

To load data into cdb (only tab supported. )

cdbmake-12 jh jh.tmp < /tmp/jhepic1

To append some text at the end of line in a file.  

awk -F '`' '{print $1 "~2"}' epicmeta_stripped.csv > /tmp/jhepic

To move 1st 15 files from one dir to another.

find . maxdepth 1 -name '*voice*' | head -n 20 | xargs mv -t /tmp/p5

time perl -pi -e 's/0000-00-00 00:00:00//;' *2014*

To configure ip

ifconfig eth0 192.168.19.200/16 up

To Add gateway via command.

`route add default gw 192.168.2.234

To check which process is eating memory. 
ps -e -o pid,vsz,comm= | sort -n -k 2

Cassandra-startups

Trying to brief Cassandra :  important stuff to learn to manage it. 



Cassandra -  In Greek mythology :   A Princess who can see the future but got cursed that nobody will believe on her.

Excellent nosql technology through which can you scale the writes and nail down all BCP and Disaster recovery issues.

Some of the important feature are listed below.

• Multi Data center support
• Multi master mode - peer to peer communication
• High Availability
• Linear scalability
• Collection datatypes
• Faster updates
• Standard Monitoring Dashboard

Out of Scope

•      joins
•      group by
•      order by
•      secondary index - not performan

Important concepts

• seed-node
• Gossips
• peer to peer
• initial_token
• listen_address
• rpc_address
• Compaction
• tombstone
• Quorum
• consistency
• replication factor
• snitch

Need to learn:

Installation 

Connect to Cassandra (via cql)

create key space/table/index

How to add node

How to remove node

How to repair a cluster

How to backup c* cluster
How to setup monitoring  (OPS center)

How to set replication factor
How to set consistency Level (via Driver)
How to set authenticaion in connection

       

Helping Query for PostgreSQL DBA

How to get column list of a table. 

BEGIN;
CREATE AGGREGATE core.array_acc(anyelement) (
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}'
);

 SELECT relname, array_acc(attname)
    FROM pg_attribute a join pg_class c on a.attrelid = c.oid
  WHERE relname in ('session', 'hits', 'conversion_hits')
        and attname not in ('tableoid','cmax','xmax','cmin','xmin','ctid')
GROUP BY relname;
commit;

relname   | conversion_hits
array_acc |{session_id,permanent_id,conversion_id,sale,price,iso_currency_id,order_code,description,sku,supersedes,user_defined1,user_defined2,user_defined3,user_defined4,document_url,referrer,domain_id,rdate,rtime,last_modified,client_biz_unit,client_section_name,old_price,old_currency_id}
-[ RECORD 2 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
relname   | hits
array_acc | {session_id,document_url,referrer,domain_id,rdate,rtime,last_modified,client_biz_unit,client_section_name}
-[ RECORD 3 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
relname   | sessions
array_acc | {session_id,permanent_id,entry_domain_id,host_ip,actual_ip,geo_worldregion_id,geo_country_id,geo_region_id,geo_location_id,campaign_id,account,category,item,traffic_source_id,keyword,paid_keyword,browser_id,browser_string,os_id,locale,language,returning_visitor,rdate,rtime,referrer,entry_url,latency_days,device_id,device_type_id,match_type_id,search_type_id,b2a_keyword_id,last_modified,redirect_id,media_published_site,media_creative_id,media_landing_page_id,client_section_name,client_biz_unit,track_url,search_engine_result_page,search_engine_position,ad_copy_id}

How to extract schema difference in pgSQL DB

There is a interesting tool available in postgresql to get the differences of  various schema of seperate databas. The name of tool is "apgdiff", can be download from this web-link.

Its a java based tool. You need to provide it 2 argument which is schema file location for which you want to get the difference.

Pre-requiste

1. Java installed on your server where apgdiff needs to be run.

2. You need to have both schema file, one would be original where changes needs to be apply, second schema file which is already having latest change.


1st file would be the original schema where you want to make changes
2nd file would be the schema file location where latest changes exist.

Step by step example :

1. Create two database on test machine. 

[pgsql@## ~]$ createdb original;
[pgsql@## ~]$ createdb dev;

2. Create a test table at both above defined databases. 

[pgsql@## ~]$ psql -Upgsql original -c "create table public.test(id integer)"
CREATE TABLE
[pgsql@## ~]$ psql -Upgsql dev -c "create table public.test(id integer)"
CREATE TABLE

3. Modify the schema at dev databases. 
[pgsql@## ~]$ psql -Upgsql dev -c "ALTER TABLE public.test ADD COLUMN name text;"
ALTER TABLE

4. Dump the schema of both database for comparision. 
[pgsql@##]$ pg_dump -Upgsql -s original -f original_schema.dump
[pgsql@##]$ pg_dump -Upgsql -s dev -f dev_schema.dump

5. Run the apgdiff tool to get the DIFF sql in below format. 
[pgsql@##]$ cd apgdiff-2.3/

[pgsql@i2adb20 apgdiff-2.3]$ java -jar apgdiff-2.3.jar original_schema.dump dev_schema.dump

ALTER TABLE test
        ADD COLUMN name text;

[pgsql@##]$


Catch : This tool only extract the schema difference not the permission one.