Friday, February 27, 2015

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!.

Saturday, February 21, 2015

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 -





Friday, February 13, 2015

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

Thursday, February 12, 2015

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)

    Monday, February 9, 2015

    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)



    Monday, February 2, 2015

    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:-


    ​"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
    ***************