Rajaneeshs

WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans

Rajaneeshs

SELECT 
  (heap_blks_hit::decimal / (heap_blks_hit + heap_blks_read)) as cache_hit_ratio
FROM 
  pg_statio_user_tables
WHERE relname = 'large_table';

Rajaneeshs

# See the 10 slowest queries with over a 1000 calls
SELECT query, calls, (total_time/calls)::integer AS avg_time_ms 
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY avg_time_ms DESC
LIMIT 10;

#  query     |  calls  | avg_time_ms
#  ----------+---------+-------------
#  INSERT .. |   52323 |          12

Rajaneeshs

# See the 10 SELECT queries which touch the most number of rows.
# These queries may benefit from adding indexes to reduce the number
# of rows retrieved/affected.
SELECT
  rows, # rows is the number of rows retrieved/affected by the query
  query
FROM pg_stat_statements
WHERE query iLIKE '%SELECT%'
ORDER BY rows DESC
LIMIT 10;

Rajaneeshs

  SELECT blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

Rajaneeshs

#Query LB using tag by jq
aws resourcegroupstaggingapi get-resources --resource-type-filters "elasticloadbalancing:loadbalancer" --tag-filters "Key=elbv2.k8s.aws/cluster, Values=test"  --tags-per-page 100 --region eu-west-1 --query "ResourceTagMappingList[].ResourceARN"

#Query LB using tag by native filter
aws resourcegroupstaggingapi get-resources --tag-filters "Key=elbv2.k8s.aws/cluster, Values=test"  --tags-per-page 100 --region eu-west-1|jq '.ResourceTagMappingList| .[].ResourceARN|select (test("targetgrou."))'


#list ubuntu latest image by filter in all available regiosn 

for region in `aws ec2 describe-regions --output text  | cut -f4`                                                                                                                [11:21:11]

Rajaneeshs

#terraform plan -destroy -refresh=true -no-color -var-file var.tfvars  -out plan
Acquiring state lock. This may take a few moments...
data.terraform_remote_state.shared: Reading...
module.iam_roles_for_serviceaccount.data.aws_iam_policy_document.external_dns_policy[0]: Reading...
module.iam_roles_for_serviceaccount.data.aws_iam_policy_document.cloudwatch-agent-policy[0]: Reading...
module.iam_roles_for_serviceaccount.data.aws_iam_policy_document.cluster_autoscaler_policy: Reading...
module.iam_roles_for_serviceaccount.data.aws_iam_policy_document.external_dns_policy[0]: Read complete after 0s [id=*]
module.iam_roles_for_serviceaccount.data.aws_iam_policy_document.lb_controller_policy: Reading...
module.iam_roles_for_serviceaccount.data.aws_iam_policy_document.cloudwatch-agent-policy[0]: Read complete after 0s [id=*]
module.iam_roles_for_serviceaccount.data.aws_iam_policy_document.cluster_autoscaler_policy: Read complete after 0s [id=*]