MySQL Connection
The SHOW FULL PROCESSLIST statement shows all threads that are currently running. It is very handy to use if you get the “too many connections” error message and want to find out what is going on.
SHOW FULL PROCESSLIST;
Sometimes some connections are hanging and taking up resources that other threads might need. When you come accross this issue you can kill that thread by using the KILL command.
KILL [CONNECTION | QUERY ] process_id
KILL CONNECTION process_id or KILL process_id terminates two things in this order:
1. Terminate statement that the connection is executing
2. Terminate the connection associated with the process_id
process_id comes from the id column.
KILL QUERY process_id terminates the statement that the connection is executing but leaves the connection intact.
AWS Lambda to stop an rds instance
April 22, 2019AWS Lambda to start an rds instance
April 22, 2019AWS Lambda to start an ec2 instance
First you need to set up a role to execute Lambda functions and ec2 instance functions.
Second you need to get your ec2 instance ids.
Python 2.7
import boto3
# Enter the region your instances are in. Include only the region without specifying Availability Zone; e.g.; 'us-east-1'
region = 'us-west-2'
# Enter your instances here: ex. ['X-XXXXXXXX']
instances = ['i-057fc46fb31c7214e']
def lambda_handler(event, context):
ec2 = boto3.client('ec2', region_name=region)
ec2.start_instances(InstanceIds=instances)
print 'started your instances: ' + str(instances)
Java 8
public class ActionHandler implements RequestHandler<Object, String> {
AmazonEc2Service amazonEc2Service = new AmazonEc2Service();
@Override
public String handleRequest(Object input, Context context) {
context.getLogger().log("Input: " + input);
amazonEc2Service.startSilverInstances();
return "Hello from Lambda!";
}
}
public class AmazonEc2Service {
private AwsConfig awsConfig;
private final List<String> instanceIds = Arrays.asList("i-028f37b8a9749dd9a","i-057fc46fb31c7214e");
public AmazonEc2Service() {
awsConfig = new AwsConfig();
}
public void startSilverInstances() {
System.out.println("startSilverInstances()");
AmazonEC2 amazonEC2 = awsConfig.getAmazonEC2();
StartInstancesRequest startInstancesRequest = new StartInstancesRequest().withInstanceIds(instanceIds);
ObjectNode msg = ObjectUtils.getObjectNode();
StartInstancesResult result = amazonEC2.startInstances(startInstancesRequest);
System.out.println("done startSilverInstances!");
}
}
public class AwsConfig {
private final String amazonAWSAccessKey = "";
private final String amazonAWSSecretKey = "";
private AWSCredentialsProvider amazonAWSCredentialsProvider;
public AwsConfig() {
amazonAWSCredentialsProvider = new AWSStaticCredentialsProvider(new BasicAWSCredentials(amazonAWSAccessKey, amazonAWSSecretKey));
}
public AmazonEC2 getAmazonEC2() {
AmazonEC2 client = AmazonEC2ClientBuilder.standard().withCredentials(amazonAWSCredentialsProvider)
.withRegion(Regions.US_WEST_2).build();
return client;
}
}
April 22, 2019 AWS Lambda to stop an ec2 instance
First you need to set up a role to execute lambda functions and also ec2 instance functions.
Second you need to get your ec2 instance ids.
Python 2.7
import boto3
# Enter the region your instances are in. Include only the region without specifying Availability Zone; e.g., 'us-east-1'
region = 'us-west-2'
# Enter your instances here: ex. ['X-XXXXXXXX', 'X-XXXXXXXX']
instances = ['i-028f37b8a9749dd9a']
def lambda_handler(event, context):
ec2 = boto3.client('ec2', region_name=region)
ec2.stop_instances(InstanceIds=instances)
print 'stopped your instances: ' + str(instances)
Java 8
public class ActionHandler implements RequestHandler<Object, String> {
AmazonEc2Service amazonEc2Service = new AmazonEc2Service();
@Override
public String handleRequest(Object input, Context context) {
context.getLogger().log("Input: " + input);
amazonEc2Service.stopSilverInstances();
return "Hello from Lambda!";
}
}
public class AmazonEc2Service {
private AwsConfig awsConfig;
private final List<String> ids = Arrays.asList("i-028f37b8a9749dd9a","i-057fc46fb31c7214e");
public AmazonEc2Service() {
awsConfig = new AwsConfig();
}
public void stopSilverInstances() {
System.out.println("stopSilverInstances()");
AmazonEC2 amazonEC2 = awsConfig.getAmazonEC2();
StopInstancesRequest request = new StopInstancesRequest()
.withInstanceIds(ids);
ObjectNode msg = ObjectUtils.getObjectNode();
StopInstancesResult result = amazonEC2.stopInstances(request);
System.out.println("done stopSilverInstances!");
}
}
public class AwsConfig {
private final String amazonAWSAccessKey = "";
private final String amazonAWSSecretKey = "";
private AWSCredentialsProvider amazonAWSCredentialsProvider;
public AwsConfig() {
amazonAWSCredentialsProvider = new AWSStaticCredentialsProvider(new BasicAWSCredentials(amazonAWSAccessKey, amazonAWSSecretKey));
}
public AmazonEC2 getAmazonEC2() {
AmazonEC2 client = AmazonEC2ClientBuilder.standard().withCredentials(amazonAWSCredentialsProvider)
.withRegion(Regions.US_WEST_2).build();
return client;
}
}
Bring down ecs instances for off hours
#!/usr/bin/env python
"""
A Lambda Function to set the desired count of running tasks
in a service based on a cluster's containter instances.
Designed to be triggered by a CloudWatch Event rule.
"""
from __future__ import print_function
import os
import json
import boto3
def ecs_client():
return boto3.client("ecs")
def adjust_service_desired_count(ecs_client, cluster, service, desiredCount):
running_service = ecs_client.describe_services(cluster=cluster, services=[service])
if not running_service["services"]:
print("SKIP: Service not found in cluster {}".format(cluster))
return
desired_task_count = running_service["services"][0]["desiredCount"]
clusters = ecs_client.describe_clusters(clusters=[cluster])
registered_instances = clusters["clusters"][0]["registeredContainerInstancesCount"]
if desired_task_count != desiredCount:
print("Adjusting cluster '{}' to run {} tasks of service '{}'".format(
cluster, desiredCount, service
))
response = ecs_client.update_service(
cluster=cluster,
service=service,
desiredCount=desiredCount,
)
print(response)
return response
# Do nothing otherwise
print("SKIP: Cluster {} has {} desired tasks for {} registered instances.".format(
cluster, desired_task_count, registered_instances
))
return
def lambda_handler(event, context):
cluster_service_mapping = {
'api-cluster': [
'arn:aws:ecs:us-east-1:{account-id}:service/api-auth-service',
'arn:aws:ecs:us-east-1:{account-id}:service/api-account-service'
]
}
desired_count = int(os.getenv('DESIRED_COUNT'))
for cluster, services in cluster_service_mapping.items():
for service in services:
adjust_service_desired_count(ecs_client(), cluster, service, desired_count)
print("DONE")
return {
"statusCode": 200,
"body": json.dumps({ "message": "success" })
}