• Call: +1 (858) 429-9131

Archive for December, 2010

MySQL Optimization

Database optimization is the process of configuring database to use system resource efficiently and perform tasks quickly. To optimize mysql you should know the work flow of entire system, your hardware, operating system, disk I/O performance etc.
Why to Optimize
You can do more with less. The default mysql setup is optimized for a minimal system because it should work well on a minimal hardware. But when you use a dedicated mysql server with high traffic and complex queries you have to optimize mysql.
MySQL Server tuning Considerations
Here you will find some common optimization parameters.

  • MySQL variables
  • Hardware
  • Disk
  • Application

MySQL Optimization
MySQL global variables don’t have any predefined optimum values. It is a trial and monitor process. It depends on all the above parameters. Here you will see some of the common parameters.
Key-buffer-size
It is size of the buffer used to index blocks for MyISAM tables. On a dedicated mysql server with MyISAM storage engine 25-30% of systems total memory you can allocate for key_buffer_size. To fine tune key_buffer_size you can compare the variables key_reads and the key_read_requests.
This ratio should be at least 1:100.

SHOW STATUS LIKE ‘%key_read%’;
+——————-+————-+
| Variable_name | Value |
+——————-+————-+
| Key_read_requests | 10726813161 |
| Key_reads | 92790146 |
+——————-+————-+
Here the ratio is 1:115 which is acceptable.
But suppose you get a ratio 1: 10 then you need to add more key buffer and upgrade hardware accordingly.
Query Cache
“My website is too slow while loading dynamic pages”. If it is a mysql database related issue, following MySQL variables will be your solution.
query_cache_type
Set the query cache type. There are 3 values 0 ,1 or 2

0 Do not cache any query result
1 Cache query results.
2 Cache results ondemand. Cacheable queries that begin with SELECT SQL_CACHE.

query_cache_size
The amount of memory used to cache query result. Default is 0 which disable query cache.
The optimum value is depend on your application.
query_cache_limit
Do not cache results that are larger than this number of bytes. The default value is 1MB.
Status checking
SHOW STATUS LIKE ‘%qcache%’;
+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 8371272 |
| Qcache_hits | 23547551 |
| Qcache_inserts | 46909131 |
| Qcache_lowmem_prunes | 5110536 |
| Qcache_not_cached | 2760196 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+————————-+———-+
There were 46909131 queries and out which 23547551 queries cached and remaining not cached. Here the issue will either the result is greater than query_cache_limit or greater than query_cache_size itself. You have to trial and monitor 🙂
Qcache_lowmem_prunes.
When a query is removed from the query cache, this value will be incremented. If it increases quickly, and you still have memory to spare, you can set query_cache_size high, If it never increases, you can reduce the cache size.

sort_buffer
The sort_buffer is a useful for speed up myisamchk operations. It can also be useful when performing large numbers of sorts.

tmp_table_size

This variable determines the maximum size for a temporary table in memory. The maximum in memory size is minimum of tmp_table_size and max_heap_table_size. You can compare
Created_tmp_disk_tables and Created_tmp_tables to optimize tmp_table_size.

innodb_buffer_pool_size

This variable is target for innodb table and it is similar to key_buffer_size in MyISAM table.
On a dedicated mysql server using innodb you can set this upto 80% of RAM.
Hardware for mysql
If you have large tables(>3GB), you should consider 64 bit hardware as mysql uses a lots of 64bit integers internally.

You need more memory(RAM) if you want mysql to handle large number of connections simultaneously. More RAM will speed up key updates by keeping most of the pages in RAM

Another consideration is Ethernet device, You can use a 1G Ethernet for a dedicated mysql server for fast remote connections.

Disk performance is also an important parameter.
Disk Optimization
Striping disk (RAID 0) will increase both read and write throughput.

Don’t use RAID 1 or mirroring on disk for temporary files.

On Linux, mount the disks with async (default) and noatime.
Optimizing your application
Cache process in your application

Specify the column name in queries(eg dont use SELECT * FROM……)

Use persistent connections

USE EXPLAIN to explain!!.You will see detail below.

Queries and Indexes

Let us start with a simple query SELECT firstname FROM student WHERE id=’145870′;
MySQL start searching from the beginning row to find the student with id 145870. It does not even know it exist or not. An index is a sorted file which have an entry for each row.MySQL can find the corresponding record very quickly by referring this index.
EXPLAIN is a nice tool to understand your queries

EXPLAIN SELECT firstname,lastname FROM student WHERE id=’145870′;

+———-+——+—————+——+———+——+——+————+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+———-+——+—————+——+———+——+——+————+
| student | ALL | NULL | NULL | NULL | NULL |10000 | where used |
+———-+——+—————+——+———+——+——+————+
The possible_keys is null. In this case mysql will check all the 10000 rows. We can say this query(or table) is not optimized.

Now suppose we have use index for above table and run EXPLAIN again then we will get
+———-+——-+—————+———+———+——-+——+——-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+———-+——-+—————+———+———+——-+——+——-+
| employee | const | PRIMARY | PRIMARY | 10 | const | 1 | |
+———-+——-+—————+———+———+——-+——+——-+
The type is “const”, which means that the table has only one matching row. The primary key is being used to find this particular record.

There are many more optimization variables and indxing methods. It is difficult include everything in a single article. But you can start mysql fine tuning while you database is underperfoming.

Creating phusion passenger AMI on Amazon EC2

Phusion Passenger is an Apache and Nginx module for deploying Ruby web applications.(such as those built on the Ruby on Rails web framework). Phusion Passenger works on any POSIX-compliant operating system,which means practically any operating system , except Microsoft Windows.

Here we are not going to discuss much about ruby on rails applications as our aim is creating an ami of an ubuntu aws instance from which we can launch an instance for developing and deploying rails applications pre-built.

Install apache2 web-server

[bash]
sudo apt-get install apache2 ( By default its DocumentRoot is /var/www/ )
[/bash]

 

Install mysql-server and mysql-client ( To support rails applications that access database )

 

 

[bash]sudo apt-get install mysql-server mysql-client[/bash]

 

 

 

Install Ruby from repository

The default ruby1.8 is missing some important files. So install ruby1.8-dev. Otherwise at some stage when using gem install, it may end up with “ Error : Failed to build gem native extensions “.

[bash]sudo apt-get install ruby1.8-dev[/bash]

 

Install RubyGems

Install rubygems >= 1.3.6

The package can be downloaded from here

wget http://rubyforge.org/frs/download.php/70696/rubygems-1.3.7.tgz

 

[bash]
tar xvzf rubygems-1.3.7.tgz
cd rubygems-1.3.7
sudo ruby setup.rb
sudo ln -s /usr/bin/gem1.8 /usr/bin/gem
[/bash]

Install Rails via rubygems

 

 

Once rubygems is installed use it to install Rails :

 

[bash]sudo gem install rails[/bash]

 

 

 

Installing Phusion Passenger

 

There are three ways to install Phusion Passenger :

1. By installing the Phusion Passenger gem.

2. By Downloading the source tarball from the PhusionPassenger website(passenger-x.x.x.tar.gz).

3. By installing the native Linux package (eg: Debian package)

Before installing, you will probably need to switch to the root user first. The Phusion Passenger installer will attempt to automatically detect Apache, and compile Phusion Passenger against that Apache version. It does this by looking for the apxs or apxs2 command in the PATH environment variable.

Apache installed in a non-standard location, prevent the Phusion Passenger installer from detecting Apache.To solve this, become root user and export the path of apxs.

Easiest way to install Passenger is installing via the gem

Please install the rubygems and then run the Phusion Passenger installer, by typing the following commands as root.

1.Open a terminal, and type:

[bash]gem install passenger[/bash]

2.Type:

[bash]passenger-install-apache2-module[/bash]

and follow the instructions from the installer.

The installer will :

1. Install the Apache2 module.

2. instruct how to configure Apache.

3. inform how to deploy a Ruby on Rails application.

If anything goes wrong, this installer will advise you on how to solve any problems.

The installer will ask to add the following lines to the apache2.conf file.

[bash] LoadModule passenger_module /usr/lib/ruby/gems/1.8/gems/passenger-3.0.0/

ext/apache2/mod_passenger.so PassengerRoot /usr/lib/ruby/gems/1.8/

gems/passenger-3.0.0

PassengerRuby /usr/bin/ruby1.8 [/bash]


Now consider, you have a rails application in directory /var/www/RPF_tool/. Add the following virtualhost entry to your apache configuration file

[bash]
<VirtualHost *:80>

ServerName  www.yoursite.com

DocumentRoot  /home/RFP_tool/public

<Directory  /var/www/RFP_tool/public>

AllowOverride  all

Options  -MultiViews

</Directory>

</VirtualHost>
[/bash]

Restart your apache server.

Phusion Passenger installation is finished.

Installation via the source tarball

Extract  the tarball to whatever location you prefer

[bash]
cd /usr/local/passenger/tar xzvf passenger-x.x.x.tar.gz
/usr/local/passenger/ passenger-x.x.x/bin/passenger-install-apache2-module
[/bash]

Please follow the instructions given by the installer. Do not remove the passenger-x.x.x folder after installation. Furthermore, the passenger-x.x.x folder must be accessible by Apache.

CREATING AN AMI OF AN EC2 INSTANCE

First you will have to install ec2-api-tools.zip from

http://www.amazon.com/gp/redirect.html/ref=aws_rc_ec2tools?location=http://s3.amazonaws.com/ec2-downloads/ec2-api-tools.zip&token=A80325AA4DAB186C80828ED5138633E3F49160D9

[bash]
unzip ec2-api-tools.zip
mkdir ~/ec2
cp -rf ec2-api-tools/* ~/ec2
[/bash]

Upload your aws certificate and private-key to /mnt of the instance.

 

Then add the following to ~/.bashrc

[bash]
export EC2_HOME=~/ec2
export PATH=$PATH:$EC2_HOME/bin
export EC2_PRIVATE_KEY=/mnt/pk-xxxxxxxxxxxxxxxxxxx.pem
export EC2_CERT=/mnt/cert-xxxxxxxxxxxxxxxx.pem
export JAVA_HOME=/usr/local/java/ ( your JAVA_HOME here)
export PATH=~/ec2/bin:$PATH
[/bash]

If your EC2 instance is an EBS-backed one, you can use the following command to create an AMI

[bash]ec2-create-image -n your-image-name instance-id[/bash]

If your instance is an s3-backed ( instance store ) one, you will have to install ec2-ami-tools first. It can be downloaded from

 

http://s3.amazonaws.com/ec2-downloads/ec2-ami-tools.zip

[bash]
unzip ec2-ami-tools.zip
cp ec2-ami-tools-x.x-xxxxx/bin/* ~/ec2/bin
[/bash]

vim ~/.bashrc

export EC2_AMITOOL_HOME=~/ec2/ec2-ami-tools-1.3-56066/

Now you can use the following commands to create an AMI of your s3-backed instance

[bash] mkdir /mnt/bundle-vol/
ec2-bundle-vol -u USER-ID -c /mnt/cert-xxxxxxx.pem -k
/mnt/pk-xxxx.pem -d /mnt/bundle-vol [/bash]

( Login to your AWS account; your USER-ID is available from Account–> Security Credentials )

[bash] ec2-upload-bundle -u s3-bucket-name -a aws-access-key -s aws-secret-key -d
/mnt/bundle-vol/ -m
/mnt/bundle-vol/image.manifest.xml
ec2-register -K  /mnt/pk-xxxxxx.pem -C/mnt/cert-xxxxxxx.pem s3-bucket-name/image.manifest.xml -n name-of-the-image [/bash]

To see the created images

[bash]ec2-describe-images [/bash]