No EIPs for Amazon ELB's

Amazon ELB's are a nice way to front end your applications. However, some of the drawbacks are that you don't have flexibility in assigning elastic ip addresses to them. The reason you would need that at times is when your application's consumers want to whitelist outbound access of your app from their network. Currently, ELB's expose their public ip address as A records (may have multiple if ELB is available on multiple zones). However, those ip addresses will change when ELB's get rebooted or patched.

Amazon is working on a feature request that allows us to assign an EIP to an ELB but that is not available as of today. Today, if we want to whitelist applications that can be accessed via firewall, we will have to whitelist the entire EC2 ip address range as mentioned in the AWS blog:-

https://aws.amazon.com/blogs/aws/aws-ip-ranges-json/

To get the current ip address range, you can run the below curl command:-

$curl -vvv https://ip-ranges.amazonaws.com/ip-ranges.json

...
{
      "ip_prefix": "54.172.0.0/15",
      "region": "us-east-1",
      "service": "AMAZON"
    },
    {
      "ip_prefix": "50.16.0.0/15",
      "region": "us-east-1",
      "service": "AMAZON"

....

Yet another round of EC2 instance reboots coming up!.

If the last rounds of 10% of  EC2 instance fleet reboot hadn't caused so many issues, it wouldn't have been as worrying. But now again about 10% of the fleet would be automatically rebooted in early march and when you login you should see a message like below:-

Additionally, you will also see the instances that will be rebooted under "Events" tab in the console. As per Amazon - "The old-gen instance families (m1, c1, m2, hs1, some m3s) are getting patched and will require a reboot; new-gen families are safe. The reboots start 03/03/2015. Some other Services (ELB, RDS, Redshift, and others that rely on EC2 under the covers) will also need the patch and may be affected.  Same for RDS and any other Services that rely on EC2 under the covers."

P.S - update on 03/02/2015 from Amazon that instance reboots are no longer necessary because they have come up with a way to hot patch the old generation instances. What a relief!.

Using MySQL OPTIMIZE after using DELETE on some of the tables for optimizing table sizes

In some of MySQL DB's, we have tables that grow to 200G in size and frequently we have to prune the data to keep the query retrieval times manageable. However, when we use DELETE statements on tables, we see that table sizes doesn't shrink till we run OPTIMIZE on the tables. For e.g:-

Before:-

+--------------------------+------------+
| Tables                   | Size in MB |
+--------------------------+------------+
| LOG_DETAILS  |  126286.48 |
| LOG_MESSAGES         |       0.02 |
+--------------------------+------------+

After running OPTIMIZE:-

+--------------------------+------------+
| Tables                   | Size in MB |
+--------------------------+------------+
| LOG_DETAILS  |  62565.48 |
| LOG_MESSAGES         |       0.02 |
+--------------------------+------------+

AWS Support also confirmed our observation:-

"MySQL by default uses InnoDB as a database engine. InnoDB is a multi-version system.

This means that each time you update or delete a row, the previous version of the row gets copied to a special place called a rollback segment. The original record is rewritten with the new data (in case of an UPDATE) or marked as deleted (in case of a DELETE).

If you do a transaction rollback or execute a concurrent SELECT statement (which has to see the previous version since the new one is not yet committed), the data is retrieved from the rollback segment.

The problem with this is that InnoDB tablespace never shrinks. If the tablespace is full, new space is allocated. The space occupied by the rollback information from the committed transactions may be reused too, but if the tablespace is full it will be extended and this is permanent.

InnoDB generates undo to support features such as transaction rollback and Multi Versioning Concurrency Control (MVCC) . Since undo is stored in the system tablespace and since the system tablespace never shrinks in size, large data load transactions can cause the system tablespace to become quite large, consuming disk space that cannot be reclaimed without recreating the database from scratch.

There is no supported way to shrink the tablespace files except backing up the RDS instance and restoring from backup. This is a limitation of the MySQL engine.

Note, though, that space occupied by the tables and space occupied by the tablespace are the different things. Tables can be shrunk by running OPTIMIZE and this will make them more compact (within the tablespace), but the tablespace itself cannot be shrunk:

* http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-storage-layout.htm."


There are also other links indicating the same -

• http://dba.stackexchange.com/questions/21615/deleting-entries-from-table-not-reducing-my-db-file
• http://www.percona.com/blog/2014/07/28/what-i-learned-while-migrating-a-customer-mysql-installation-to-amazon-rds

MySQL hanging when switching databases after login with "use" command

Sometimes MySQL hangs after logging in and switching databases using "use" command.

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

As explained in discussion link , MySQL scans the databases, tables and column names for sake of auto-completion. You can disable the scan by using -A switch (or --no-auto-rehash) when logging into mysql. E.g.

$ mysql -h <rds url> -u testuser -p -A

Not to mix EC2_REGION and AWS_REGION environment variables!.

After getting used to AWS CLI, it is sometimes hard to switch back to shell script based cli such as RDSCli . The command line script uses $HOME/.aws/awscredential.txt file to access instead of $HOME/.aws/config file used by AWS CLI. On a similar note, the cli shell script also default the region to us-east-1 and if you would like to query or create assets in a different region, we have to export an environment variable called EC2_REGION. AWS CLI uses AWS_REGION instead.

$ export EC2_REGION=us-west-1
$ rds-describe-db-instances
DBINSTANCE  test  2015-02-14T01:10:43.051Z  db.t2.micro  mysql  5  test  available  test.clulyqjvvos9.us-west-1.rds.amazonaws.com  3306  us-west-1a  7  n  5.6.19b  general-public-license  n
      VPCSECGROUP  sg-5e8e2f3b  active
      PARAMGRP  default.mysql5.6  in-sync
      SUBNETGROUP  default-vpc-1cd71679  Complete
      OPTIONGROUP  default:mysql-5-6  in-sync

MySQL RDS instance in "read-only" mode

When we are doing DB migration or upgrade in MySQL, we may want to set the DB instance in "read-only" mode to prevent any modifications to the tables. Typically, there are two ways to achieve this as described in this link

• Set DB as read-only

mysql>FLUSH TABLES WITH READ LOCK;

mysql>SET GLOBAL read_only = 1;

• Revoke insert and update privileges

mysql>REVOKE INSERT, UPDATE ON `mydb`.* FROM 'apache'@'localhost';

mysql>FLUSH PRIVILEGES;

However, when you are using Amazon RDS, typically, we want to use the "db parameter group" option to change many of such parameters. There is one such parameter called "read_only" as shown in screenshot below:-

This particular parameter takes the value of "{TrueIfReplica}", 1 and 0. So if we set this particular parameter to "1", then DB instance becomes read-only. Additional advantage of this property is that it is "dynamic" which means that no DB reboot is needed. 

NOTE - If you are using a common DB parameter group for a set of DB instances but would like to only apply the read_only parameter to one instance, then you will need to "copy" the DB parameter group, then modify the particular db instance in question to add this newly made copy as it parameter group. Pl. note you will have to select "apply immediately" option in the modify DB step. Once you do, the instance will sync and will say "pending till reboot", which mean you will have to decide whether to reboot right away or can wait the next backup window of RDS instance where it will get automatically rebooted. 

Once the instance is in read-only mode, the insert will fail as below:-

mysql> insert into test.MyGuests (firstname,lastname,email,reg_date) values ('j

oe1','smith1','joe1@test.com',now());

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

To revert, you can simply make the "read_only" parameter "0" , save the changes and wait for the instance to apply the changes. After which you should be able to insert again

mysql> insert into test.MyGuests (firstname,lastname,email,reg_date) values ('joe1','smith1','joe1@test.com',now());

Query OK, 1 row affected (0.02 sec)

Benching marking your site responses using Apache WorkBench

Apache workbench is a dandy tool to quickly benchmark your site's responsiveness as well as detect any application level bottlenecks. Apache workbench depends on, Gnu C compiler (GCC), Apache Runtime (APR) and Apache Runtime utils (APR Utils). Typically, you want to get the latest and greatest of these packages, so we will look at downloading the latest versions and compiling them from source

If you are setting this up in a base linux instance, you may have to install gcc

• sudo yum install -y gcc

Now we can download and install apr and apr-utils

APR 1.5.1:-

• wget http://apache.tradebit.com/pub//apr/apr-1.5.1.tar.gz
• tar xvfz apr-1.5.1.tar.gz
• cd apr-1.5.1
• ./configure
• make
• sudo make install

NOTE - If you have not specified --prefix for the "configure", the pkg-config director for apr-1.pc will be /usr/local/apr/lib/pkgconfig/apr-1.pc. If you have other user installed libraries in /usr/local/lib/pkgconfig/apr-1.pc, then you can link apr-1.pc as below

• sudo ln -s /usr/local/apr/lib/pkgconfig/apr-1.pc /usr/local/lib/pkgconfig/apr-1.pc

APR-UTIL 1.5.4:-

• wget http://apache.tradebit.com/pub//apr/apr-util-1.5.4.tar.gz
• tar xvfz apr-util-1.5.4.tar.gz
• cd apr-util-1.5.4
• ./configure --with-apr=/usr/local/apr
• make
• sudo make install

Apache WorkBench 2.3:-

• wget https://apachebench-standalone.googlecode.com/files/ab-standalone-0.1.tar.bz2
• bzip2 ab-standalone-0.1.tar.bz2
• tar xvf ab-standalone-0.1.tar
• export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
• make ab

Once you have made done the above configuration, you could quickly benchmark your site's responsive by running the below command:-

$ ./ab -c 100 -n 100000 -H "User-Agent:Load-Test" -k http://<your site>/<test page>.html

This is ApacheBench, Version 2.3 <$Revision$>

Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/

Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking <your site> (be patient)

Completed 10000 requests

Completed 20000 requests

Completed 30000 requests

Completed 40000 requests

Completed 50000 requests

Completed 60000 requests

Completed 70000 requests

Completed 80000 requests

Completed 90000 requests

Completed 100000 requests

Finished 100000 requests

Server Software:        Apache-Coyote/1.1

Server Hostname:        <your host name>

Server Port:            80

Document Path:          /<test page>.html

Document Length:        209 bytes

Concurrency Level:      100

Time taken for tests:   134.133 seconds

Complete requests:      100000

Failed requests:        0

Write errors:           0

Keep-Alive requests:    42518

Total transferred:      36005468 bytes

HTML transferred:       20900000 bytes

Requests per second:    745.53 [#/sec] (mean)

Time per request:       134.133 [ms] (mean)

Time per request:       1.341 [ms] (mean, across all concurrent requests)

Transfer rate:          262.14 [Kbytes/sec] received

Connection Times (ms) min  mean[+/-sd] median   max

Connect:        0    1   7.5      1    1002

Processing:    17  133  65.8    119     740

Waiting:       17  133  65.7    119     740

Total:         17  134  66.1    120    1188

Percentage of the requests served within a certain time (ms)

  50%    120

  66%    145

  75%    160

  80%    169

  90%    201

  95%    241

  98%    330

  99%    421

 100%   1188 (longest request)

CVE-2015-0235 - buffer overflow with glibc a.k.a GHOST vulnerability

As you may know, it was determined that glibc was susceptible to buffer overflow vulnerability as reported on below redhat link:-

https://access.redhat.com/security/cve/CVE-2015-0235

​"A heap-based buffer overflow was found in glibc's __nss_hostname_digits_dots() function, which is used by the gethostbyname() and gethostbyname2() glibc function calls. A remote attacker able to make an application call either of these functions could use this flaw to execute arbitrary code with the permissions of the user running the application."

 In order to check if your glibc version is susceptible to this vulnerability you can run the below test:

-- download test c program -

$wget https://gist.githubusercontent.com/koelling/ef9b2b9d0be6d6dbab63/raw/de1730049198c64eaf8f8ab015a3c8b23b63fd34/gistfile1.c

-- compile the code -

$gcc gistfile1.c -o CVE-2015-0235

-- execute the test program

$./CVE-2015-0235

vulnerable

The version of glibc that is currently installed can be confirmed by 

$sudo rpm -q glibc

glibc-2.12-1.149.el6_6.5.x86_64 (for RHEL instance)

Now that you have determined that glibc is vulnerable, you can update the glibc version using "sudo yum update -y glibc" or "sudo yum update -y" to update all the packages. Once you have patched the glibc version, you can run the test program (gistfile1.c) again to see its output

$./CVE-2015-0235

not vulnerable

For additional confirmation you can look at the changelog to see if this particular CVE was fixed:-

$rpm -q glibc --changelog | head (for RHEL instance)

* Mon Jan 19 2015 Siddhesh Poyarekar <siddhesh@redhat.com> - 2.12-1.149.5

- Fix parsing of numeric hosts in gethostbyname_r (CVE-2015-0235, #1183533).

you can go through the same sequence for any other linux distributions including Amazon linux:-

***************

$sudo rpm -q glibc

glibc-2.17-55.87.amzn1.x86_64

[ec2-user@ip-172-x-x-x ~]$ ./CVE-2015-0235

vulnerable

[ec2-user@ip-172-x-x-x ~]$sudo yum update -y glibc

$sudo rpm -q glibc

glibc-2.17-55.93.amzn1.x86_64

[ec2-user@ip-172-x-x-x ~]$ ./CVE-2015-0235

not vulnerable

***************