ericjsilva

Overview

Below are the steps needed to create and configure a proxy between MuleSoft CloudHub and Amazon AWS Redshift using an Amazon AWS EC2 instance.

The Problem

EC2 and Redshift instances are configured to support jumbo frames (MTU for ethernet interfaces is 9001). However, some routers between endpoints have a standard Ethernet MTU size (1500), which causes an inability to communicate with announced TCP MSS size (8961). The reason for this issue is that the PATH MTU discovery process relies on ICMP, specifically Type 3 Code 4 / Fragmentation Needed, and currently on Redshift ALL ICMP traffic is denied (regardless of Security Group configuration).

MuleSoft CloudHub uses the standard ethernet MTU (1500), and cannot connect to a RedShift cluster by default. The steps below document how to create a lightweight IP proxy using an EC2 instance.

Configuration Details

ericjsilva

aws rds modify-db-parameter-group --db-parameter-group={parameter-group-name} --region={region} \
--parameters="ParameterName=character_set_server, ParameterValue=utf8, ApplyMethod=pending-reboot" \
--parameters="ParameterName=collation_server, ParameterValue=utf8_general_ci, ApplyMethod=pending-reboot" \
--parameters="ParameterName=tmp_table_size, ParameterValue={DBInstanceClassMemory/16}, ApplyMethod=pending-reboot" \
--parameters="ParameterName=max_heap_table_size, ParameterValue={DBInstanceClassMemory/16}, ApplyMethod=pending-reboot" \
--parameters="ParameterName=query_cache_type, ParameterValue=1, ApplyMethod=pending-reboot" \
--parameters="ParameterName=query_cache_size, ParameterValue=3048576, ApplyMethod=pending-reboot" \
--parameters="ParameterName=table_open_cache, ParameterValue=2500, ApplyMethod=pending-reboot" \
--parameters="ParameterName=join_buffer_size, ParameterValue={DBInstanceClassMemory/64}, ApplyMethod=pending-reboot" \
--parameters="ParameterName=thread_cache_size, ParameterValue={DBIns

ericjsilva

create user [username] password 'password';
grant usage on schema [schema] to [username];
alter user [username] set search_path to [schema];
grant select on all tables in schema [schema] to [username];

commit;

select * from pg_user;

ericjsilva

$ travis encrypt AWS_ACCESS_KEY_ID=$AWS_ACCESS_KEY_ID
$ travis encrypt AWS_SECRET_ACCESS_KEY=$AWS_SECRET_ACCESS_KEY

ericjsilva

How to Update All Your Ruby Gems At Once

If you want to update every gem on your system, and don’t want to sit and do it one-by-one (and why would you want to do it one-by-one!?), here’s one way to do it. (I’m sure there are better ways, and I’d love to hear about them in the comments.)

sudo gem update `gem list | cut -d ' ' -f 1

ericjsilva

Determine size of S3 bucket

Below are AWS CLI commands that can be used for quickly determining the current size of an S3 bucket.

in MB

aws s3 ls s3://[bucket_name] --recursive | awk 'BEGIN {total=0}{total+=$3}END{print total/1024/1024" MB"}'

in GB

ericjsilva

The following AWS CLI command will determine if there are any network interfaces attached to a given VPC ID.

aws ec2 describe-network-interfaces --region eu-west-1 --filters 'Name=vpc-id,Values=vpc-12345678' --query 'NetworkInterfaces[*].NetworkInterfaceId'

If the array returns empty (e.g. []), there are no active network interfaces attached. Otherwise, a list of interface IDs will be returned.

ericjsilva

SELECT CONCAT('OPTIMIZE TABLE ',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='my_schema';

ericjsilva

SELECT
  SUM(capacity) / 1024 as capacity_gbytes, 
  SUM(used) / 1024 as used_gbytes, 
  (SUM(capacity) - SUM(used)) / 1024 as free_gbytes 
FROM 
  stv_partitions WHERE part_begin = 0;

ericjsilva

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round((data_length / 1024 / 1024), 2) `Data Size in MB`,
     round((index_length / 1024 / 1024), 2) `Index Size in MB`,
     round(((data_length + index_length) / 1024 / 1024), 2) `Total Size in MB`,
     round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Total Size in GB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;