Skip to content

Instantly share code, notes, and snippets.

🦉
silently correcting your grammar.

Eric Silva ericjsilva

🦉
silently correcting your grammar.
Block or report user

Report or block ericjsilva

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@ericjsilva
ericjsilva / table_size.sql
Created Jul 3, 2018
List the size of tables in MySQL database
View table_size.sql
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;
@ericjsilva
ericjsilva / query_free_space.sql
Created May 18, 2018
Query Free Space in Redshift
View query_free_space.sql
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
ericjsilva / optimize_tables.sql
Created May 4, 2018
Create MySQL Optimize statements
View optimize_tables.sql
SELECT CONCAT('OPTIMIZE TABLE ',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='my_schema';
@ericjsilva
ericjsilva / aws_vpc_check.md
Created Apr 26, 2018
Determine if AWS VPC is in use
View aws_vpc_check.md

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
ericjsilva / s3_bucket_size.md
Created Apr 26, 2018
Determine size of AWS S3 Bucket
View s3_bucket_size.md

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
ericjsilva / update-gems.md
Last active Sep 10, 2017
Update All Ruby Gems at once
View update-gems.md

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
ericjsilva / encrypt_keys.sh
Created May 5, 2017
Encrypt AWS keys and secrets in Travis-CI
View encrypt_keys.sh
$ travis encrypt AWS_ACCESS_KEY_ID=$AWS_ACCESS_KEY_ID
$ travis encrypt AWS_SECRET_ACCESS_KEY=$AWS_SECRET_ACCESS_KEY
@ericjsilva
ericjsilva / create_redshift_readonly_user.sql
Last active May 4, 2017
AWS Redshift: Create read-only user
View create_redshift_readonly_user.sql
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
ericjsilva / optimized-rds-mysql56.sh
Last active May 25, 2016
AWS CLI script to modify RDS MySQL 5.6 parameter group to use optimized settings
View optimized-rds-mysql56.sh
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
ericjsilva / cloudhub-redshift-proxy.md
Last active Feb 7, 2019
How to create and configure a proxy between MuleSoft CloudHub and Amazon AWS Redshift using an Amazon AWS EC2 instance.
View cloudhub-redshift-proxy.md

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

You can’t perform that action at this time.