4 posts tagged “mysql”

What we noticed is a large number of small disk writes affects the whole disk performance, for read transactions as well. These writes are coming from log file entries - apache & tomcat logs. Once these are moved out of EBS the performance is back to normal and in some cases better than before. Looks like it is better to have a separate EBS instance for the database - database writes do not seem to have such adverse impact.

Scalr seems to be decent option (and the only one I could find) for EC2 environments. Server side clustering which also spawns new instances as load increases. Terracota has an offering on EC2 environment where the cache serves as the intermediate database for all practical purposes.

In our solution there is no http session or cache to hold data, every read and write operate on the database. There is only a small portion of read only configuration data that is cached in the server. So java server operates in a stateless mode - meaning a browser client has no affinity to a server. If there are 3 servers then client can utilize all 3 servers for its requests. So client itself can take of clustering and failover. When client first comes to server thru a url server loads the client and gives all server urls to it. Client discovers an available server out of this and sticks to it till server is either down or too loaded. Client switches to another server at that point from its list. No need for a gatekeeper on server.

Failover is largely needed at database layer. As long as database is replicated realtime (master-master replication) failover from a database perspective will be seamless. We are exploring solutions for this:

1) mysql clustering. There are some trials of this but none seem to be in a production EC2 setup.

2) C-JDBC or Sequoia: JDBC level clustering, seems the most transparent way. Have to check if JGroups etc can talk to EC2 servers only visible on http

3) HA-JDBC: Similar to C-JDBC.

Will check out in the next couple of weeks and post the results.

See our profile for the motivation behind the blog

How to build a Linux install running on EC2

For information on how to start and logging-in to Ec2 instance from desktop, refer to amazon webservices

In this blog, I will share my experiences on baking a machine image on Ec2 with necessary application servers for a java web stack - Apache, Tomcat, Mysql and pma (php based mysql admin tool)

OK, first what problems I faced;

• When I built my first image, the server quickly shooted up of disk usage to 90%. This is because we put all our deployments in sda1. Amazon only gives around 2GB in sda1 and rest on /mnt 158GB. Anything I put on mnt is going to wash away and can't be in the image.
• Finding right executables to install.
  

To address this problem the strategy is to install java and hadoop in sda1 and rest under mnt. Hadoop helps on getting the rest of the deployment pack from S3 to local /mnt. I assumed this will get the sda1 disk usage to minimum. However there are more surprises waiting in store. First let me take you though steps of building an image.

1. All available base instances ec2-describe-images.cmd -o amazon

2. I chose ec2-public-images/fedora-8-i386-base-v1.06.manifest.xml amazon

3. Started instance ec2-run-instances fedora-8-i386-base-v1.06.manifest.xml -k gsg-keypair

4. mkdir /mnt/install
   cd /mnt/install
   yum install perl-DBI
   

5. echo "Get Mysql"
   wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-server-community-5.1.24-0....
   wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-client-community-5.1.24-0....
   wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-shared-community-5.1.24-0....
   wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-devel-community-5.1.24-0.r...

6. rpm -i MySQL-server-community-5.1.24-0.rhel3.i386.rpm
   rpm -i MySQL-client-community-5.1.24-0.rhel3.i386.rpm
   rpm -i MySQL-devel-community-5.1.24-0.rhel3.i386.rpm
   rpm -i MySQL-shared-community-5.1.24-0.rhel3.i386.rpm

7. echo "Now add it to the path"
   PATH=$PATH:/usr/bin

8. Hardening mysql - /usr/bin/mysql_secure_installation

9. Mysql by default gets configured to start during startup. This is not optimal. Why? I want to have mysql installation directory to mnt and once EC2 starts, I get rest of image ball from S3 and host in mnt. So I went ahead and deleted mysql from startup

10. cd /etc/init.d; mv mysql ~

11. Time to shutdown mysql and move this to mnt
    mysqladmin --user=root --password=jskfk shutdown

12. cp -R /var/lib/mysql /mnt/install
    rm -rf /var/lib/mysql
    ln -s /mnt/install/mysql /var/lib/mysql
    mysqld --user=root &
    Time to take care of Apache

13. yum install openssl-devel

    cd /mnt/install/downloads
    wget http://mirror.nyi.net/apache/httpd/httpd-2.2.6.tar.gz
    tar -zxvf httpd-2.2.6.tar.gz
    cd /mnt/install/downloads/httpd-2.2.6

14. ./configure --prefix=/mnt/install/apache --with-mpm=worker \
    --enable-so \
    --disable-actions \
    --disable-alias \
    --disable-asis \
    --disable-auth \
    --disable-autoindex\
    --disable-cgi \
    --disable-env \
    --disable-imap \
    --disable-include \
    --disable-negotiation \
    --disable-status \
    --disable-userdir \
    --enable-cache=shared \
    --enable-disk_cache=shared \
    --enable-mem_cache=shared \
    --enable-deflate=shared \
    --enable-expires=shared \
    --enable-filter=shared \
    --enable-ext_filter=shared \
    --enable-headers=shared \
    --enable-proxy=shared \
    --enable-rewrite=shared \
    --enable-ssl=shared \
    --enable-usertrack=shared \
    --enable-vhost_alias=shared \
    --enable-auth_digest=shared \
    --enable-authn_core=shared \
    --enable-authz_core=shared \
    --enable-authn-dbm=shared \
    --enable-authn-anon=shared \
    --enable-authn-dbd=shared \
    --enable-authn-alias=shared \
    --enable-authz-host=shared \
    --enable-authz-groupfile=shared \
    --enable-authz-user=shared \
    --enable-authz-dbm=shared \
    --enable-authz-owner=shared \
    --enable-setenvif=shared \
    --enable-dumpio=shared \
    --enable-log_forensic=shared \
    --enable-mods-shared=all \
    --with-z=/usr/lib \
    --with-openssl-libs=/usr/lib
    

15. make
    make install
    PATH=$PATH:/mnt/install/apache/bin

16. The JK connector installation from Tomcat to Apache. You may see the links are not working. There could be a potential release of a new version.

17. echo "Install JK"
    cd /mnt/install/downloads
    wget http://www.apache.org/dist/tomcat/tomcat-connectors/jk/source/tomcat-connecto...
    tar -zxvf tomcat-connectors-1.2.26-src.tar.gz
    cd /mnt/install/downloads/tomcat-connectors-1.2.26-src/native
    ./configure --with-apxs=/mnt/install/apache/bin/apxs --enable-EAPI
    make
    make install

18. Install and configure Php stuffs

19. echo "Get Php"
    cd /mnt/install/downloads
    wget http://in2.php.net/get/php-5.2.4.tar.gz/from/us.php.net/mirror
    tar -zxvf php-5.2.4.tar.gz
    cd /mnt/install/downloads/php-5.2.4
    yum install libxml2-devel
    ./configure --with-apxs2=/mnt/install/apache/bin/apxs --with-mysql=/usr/bin/mysql --with-config-file-path=/mnt/install/apache/conf --with-libxml-dir=./ext/libxml

    ln -s /usr/lib/libmysqlclient.so /usr/lib/mysql/libmysqlclient.so
    make
    make install

20. Tomcat runs in Java. Java we are going to install in sda1 as hadoop also relies on Java. Why do we need hadoop and java in the main image?? Without this we can't go to S3 and get the tar ball (mnt folder). Hadoop needs Java internally. So we need both :)

21. echo "Java installation"
    Goto http://java.sun.com/javase/downloads/index_jdk5.jsp
    Download JDK 5.0 Update 15, Accept the License
    Copy the link for jdk-1_5_0_15-linux-i586.bin
    cd /mnt/install/downloads

    wget -O java.bin http://cds.sun.com/is-bin/INTERSHOP.enfinity/WFS/CDS-CDS_Developer-Site/en_US/-/USD/VerifyItem-Start/jdk-1_5_0_15-linux-i586.bin?BundledLineItemUUID=The KEY GOES HERE&FileName=/jdk-1_5_0_15-linux-i586.bin

    cd /usr/local
    sh /mnt/install/downloads/java.bin
    mv jdk1.5.0_15 jdk1.5
    export JAVA_HOME=/usr/local/jdk1.5
    export PATH=$PATH:$JAVA_HOME/bin

22. Install tomcat now

23. cd /mnt/install/downloads

    wget http://apache.mirror.facebook.com/tomcat/tomcat-6/v6.0.14/bin/apache-tomcat-6.0.14.tar.gz

    tar -zxvf apache-tomcat-6.0.14.tar.gz

    cd /mnt/install

    mv /mnt/install/downloads/apache-tomcat-6.0.14 /mnt/install/tomcat

24. Time to set all configuration files

25. Make sure follwing lines are there in httpd.conf
    LoadModule php5_module modules/libphp5.so
    LoadModule jk_module modules/mod_jk.so

    # Restrict Access to sensitive files
    
    Deny from all
    
    
    
    
    JkWorkersFile /mnt/install/apache/conf/workers.properties
    JkShmFile /mnt/logs/apache.mod_jk.shm
    JkLogFile /mnt/logs/apache.mod_jk.log
    JkLogLevel error
    JkLogStampFormat "[%a %b %d %H:%M:%S %Y] "
    JkMount /*/*.xml ajp13
    
    
    
    # mod_deflate (compress output for browsers that support it)
    AddOutputFilterByType DEFLATE text/html text/plain text/xml text/css application/x-javascript text/javascript text/x-js
    BrowserMatch ^Mozilla/4 gzip-only-text/html
    BrowserMatch ^Mozilla/4\.[0678] no-gzip
    BrowserMatch \bMSIE\s7 !no-gzip !gzip-only-text/html

26. workers.properties settings
    worker.list=ajp13
    worker.ajp13.port=8009
    worker.ajp13.host=localhost
    worker.ajp13.type=ajp13

    worker.loadbalancer.type=lb
    worker.loadbalancer.balance_workers=ajp13

27. I took out all my logs - mysql, tomcat, apache to /mnt/logs. That requires proper settings on the files present in the conf directory.

28. Bouncing once the servers:
    

    /mnt/install/tomcat/bin/shutdown.sh

    /mnt/install/apache/bin/apachectl stop

    /mnt/install/tomcat/bin/startup.sh

    /mnt/install/apache/bin/apachectl start

29. Well phpMyAdmin a must have to aminister the mysql database. here is what I have done

30. cd /mnt/install/downloads

    wget http://prdownloads.sourceforge.net/phpmyadmin/phpMyAdmin-2.11.1-all-languages.tar.gz?download

    tar -zxvf phpMyAdmin-2.11.1-all-languages.tar.gz

    cd /mnt/install/downloads/phpMyAdmin-2.11.1-all-languages

    mkdir /mnt/install/apache/htdocs/pma

    cd /mnt/install/apache/htdocs/pma

    cp -R /mnt/install/downloads/phpMyAdmin-2.11.1-all-languages/* .

    cp config.sample.inc.php config.inc.php

    vi config.inc.php

    change $cfg['blowfish_secret'] =

    mkdir config

    chmod o+rw config

    cp config.inc.php config/

    chmod o+w config/config.inc.php

    mkdir data

    chmod o+rw data

31. Start mysql if not running -

    mysql --password=whrshfuurw

32. GRANT USAGE ON mysql.* TO 'support'@'localhost' IDENTIFIED BY 'xxuser'

    ;

    GRANT SELECT (

    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,

    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,

    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,

    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,

    Execute_priv, Repl_slave_priv, Repl_client_priv

    ) ON mysql.user TO 'support'@'localhost';

    GRANT SELECT ON mysql.db TO 'support'@'localhost';

    GRANT SELECT ON mysql.host TO 'support'@'localhost';

    GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)

    ON mysql.tables_priv TO 'support'@'localhost';

    http://website/pma/scripts/setup.php

    Config server: port 3306, authentication = http controluser = support passwd

    Upload/Download data directory

    mv config/config.inc.php . # move file to current directory

    chmod o-w config.inc.php # remove world write permissions

33. Login to the website and ec2-XXXX-XXX-XXX-XXX/pma

34. Let's do some support accounts created:
    

    groupadd support

    useradd -g support supportadmin

    passwd supportadmin

    supportadminpass

    ssh supportadmin@localhost

35. Never forget to set the .bashrc. This is the place, I set the timing to India. Believe me, other wise the date thing just kills due to little slippage in the development. The servers run in USA and users sitting in India.. Wow.. Got to be really careful in each and every aspect of the application.

    export JAVA_OPTS="-Duser.timezone=IST"

    export JAVA_HOME=/usr/local/jdk1.5

    export PATH=/mnt/install/tomcat/bin:/mnt/install/apache/bin/:$JAVA_HOME/bin:/usr/local/hadoop/bin:$PATH

    set -o vi

    alias servers='netstat -l -n -p -t -u -w'

    alias stop="apachectl stop; shutdown.sh; netstat -l -n -p -t -u -w | grep 8080 | grep LISTEN | sed 's/ [ ]*/ /g' | cut -d' ' -f7 | cut -d'/' -f1 | xargs kill"

    alias start="startup.sh;apachectl start"

    echo "Welcome abord.."

36. Little bit hardening of the servers

37. netstat -l -n -p -t -u -w

    iptables --list

    iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT

    iptables -A OUTPUT -m state --state NEW,RELATED,ESTABLISHED -j ACCEPT

    iptables -A INPUT -p tcp -m tcp --dport 22 -j ACCEPT

    iptables -A INPUT -p tcp -m tcp --dport 80 -j ACCEPT

    iptables -A INPUT -p tcp -m tcp --dport 443 -j ACCEPT

    iptables -A INPUT -s 127.0.0.1 -j ACCEPT

    iptables -A OUTPUT -p tcp -m tcp --dport 22 -j DROP

    iptables -A INPUT -j DROP

    iptables -A FORWARD -j DROP

38. vi /mnt/install/apache/htdocs/php.ini

    expose_php = Off

    display_erros = Off

    log_errors = On

    error_log = /mnt/logs

39. Put the Hadoop in sda1.
    

    cd /mnt/install/downloads

    wget http://apache.tradebit.com/pub/hadoop/core/stable/hadoop-0.15.3.tar.gz

    gunzip hadoop-0.15.3.tar.gz

    cd /usr/local

    tar -xvf /mnt/install/downloads/hadoop-0.15.3.tar

    rm docs and src

I deleted the download directories and log files. Tarred up the whole /mnt/install and sent this to s3 using hadoop. After this I stopped servers, cleaned up mnt and cut an image.

Next time I built as java and hadoop were in /usr/local, I was able to retrieve my deploy ball from s3 and unpackaging it at /mnt. Well I was ready to go with sucessful start of all servers. However another surprise was just waitting for me. I suddenly saw, the disk usage is shotting up. I couldn't figure out why.. It was from /usr/bin

Now It is time to take that out. Softlink was my solution

echo "Moving out the /usr directory from sda1 to mnt"

cp -R /usr /mnt

rm -rf /usr

ln -s /mnt/usr /usr

cd /usr/bin

This created another set of issues though reduced my disk usage to 25%. Inside /usr, there were many relative soft links like ../../. So finding wach one which is missing and fixing it was my job :(

rm -f env; ln -s /bin/env env

rm -f Mail; ln -s /bin/mail Mail

rm -f awk; ln -s /bin/gawk awk

rm -f gettext; ln -s /bin/gettext gettext

rm -f gunzip; ln -s /bin/gunzip gunzip

rm -f gzip; ln -s /bin/gzip gzip

rm -f cut; ln -s /bin/cut cut

rm -f kill; ln -s /bin/kill kill

rm -f gawk; ln -s /bin/gawk gawk

rm -f libdb-4.6.so; ln -s /lib/libdb-4.6.so libdb-4.6.so

rm -f libselinux.so; ln -s /lib/libselinux.so.1 libselinux.so

rm -f libsepol.so; ln -s /lib/libsepol.so.1 libsepol.so

rm -f libdl.so; ln -s /lib/libdl.so.2 libdl.so

rm -f libm.so; ln -s /lib/libm.so.6 libm.so

rm -f libssl.so; ln -s /lib/libssl.so.0.9.8b libssl.so

rm -f libBrokenLocale.so; ln -s /lib/libBrokenLocale.so.1 libBrokenLocale.so

rm -f libthread_db.so; ln -s /lib/libthread_db.so.1 libthread_db.so

rm -f libutil.so; ln -s /lib/libutil.so.1 libutil.so

rm -f libnsl.so; ln -s /lib/libnsl.so.1 libnsl.so

rm -f libnss_compat.so; ln -s /lib/libnss_compat.so.2 libnss_compat.so

rm -f libnss_db.so; ln -s /lib/libnss_db.so.2 libnss_db.so

rm -f libnss_dns.so; ln -s /lib/libnss_dns.so.2 libnss_dns.so

rm -f libnss_files.so; ln -s /lib/libnss_files.so.2 libnss_files.so

rm -f libnss_hesiod.so; ln -s /lib/libnss_hesiod.so.2 libnss_hesiod.so

rm -f libnss_nis.so; ln -s /lib/libnss_nis.so.2 libnss_nis.so

rm -f libnss_nisplus.so; ln -s /lib/libnss_nisplus.so.2 libnss_nisplus.so

rm -f libanl.so; ln -s /lib/libanl.so.1 libanl.so

rm -f libz.so; ln -s /lib/libz.so.1.2.3 libz.so

rm -f libcidn.so; ln -s /lib/libcidn.so.1 libcidn.so

rm -f libresolv.so; ln -s /lib/libresolv.so.2 libresolv.so

rm -f libcrypt.so; ln -s /lib/libcrypt.so.1 libcrypt.so

rm -f libcrypto.so; ln -s /lib/libcrypto.so.0.9.8b libcrypto.so

rm -f librt.so; ln -s /lib/librt.so.1 librt.so

cd /usr/lib/lsb/

rm -f install_initd; ln -s /sbin/chkconfig install_initd

rm -f remove_initd; ln -s /sbin/chkconfig remove_initd

cd /usr/sbin

rm -f accton; ln -s /sbin/accton accton

rm -f kudzu; ln -s /sbin/kudzu kudzu

rm -f rcmysql; ln -s /etc/init.d/mysql rcmysql

rm -f hwclock; ln -s /sbin/hwclock hwclock

cd /usr/share/terminfo/a

rm -f ansi; ln -s /lib/terminfo/a/ansi ansi

cd /usr/share/terminfo/d

rm -f dumb; ln -s /lib/terminfo/d/dumb dumb

cd /usr/share/terminfo/l

rm -f linux; ln -s /lib/terminfo/l/linux linux

cd /usr/share/terminfo/v

rm -f vt100-am; ln -s /lib/terminfo/v/vt100-am vt100-am

rm -f vt100; ln -s /lib/terminfo/v/vt100 vt100

rm -f vt100-nav; ln -s /lib/terminfo/v/vt100-nav vt100-nav

rm -f vt200; ln -s /lib/terminfo/v/vt200 vt200

rm -f vt220; ln -s /lib/terminfo/v/vt220 vt220

cd /usr/share/terminfo/x

rm -f xterm; ln -s /lib/terminfo/x/xterm xterm

rm -f xxterm; ln -s /lib/terminfo/x/xxterm xxterm

---------------------------------------

Now things are good. Let's grab the ball from s3

/usr/local/hadoop/bin/hadoop fs -ls /image

/usr/local/hadoop/bin/hadoop fs -copyToLocal /image/nn.tar .

tar xvf nn.tar

Finally, if you need the mail server and not running, start it,

echo "start the mail server if not running"

listeningports=`netstat -l -n -p -t -u -w | sed 's/ [ ]*/ / g' | cut -d' ' -f4`

isListening=`echo $listeningports | grep 25 | wc -l`

if [ $isListening -ne 1 ]; then

sendmail -bd &

fi

---------------------------------------

Was this blog little too detailed? Well, in this I wanted to give enough details for those wanting to start an EC2 instance can do cut n paste.

Further on I will blog on what to do when an instance is lost, hung up and other failures like that. Interestingly we were able to recover an instance after Amazon support team gave up.

If you are trying out these commands, cool. Let us know how you got on. If you are reading so far I acknowledge your patience. Let me know your view points.

To avoid spam, I am giving our mail address on a image. See below or in the top. Write a comment here or send a mail. Tomorrow I will share how we use EC2 for production hosting rather than for non critical activities such as testing, backup etc.