Important queries based formulas to watch performance of a MySQL based application

(1) Session Vs global status variables value
select G.variable_name,G.variable_value GlobalValue,S.variable_value SessionValue from (select * from information_schema.global_status) G inner join (select * from information_schema.session_status) S using (variable_name) where G.variable_name like '$status_variable_name';
Please replace $status_variable_name with relative status variable name.
If you want to list down all similar status variable just prefix and suffix % with $status_variable_name.
Example:- If we want to list down all the status variables (Session and Global) related with threads :
select G.variable_name,G.variable_value GlobalValue,S.variable_value SessionValue from (select * from information_schema.global_status) G inner join (select * from information_schema.session_status) S using (variable_name) where G.variable_name like '%Threads%';

Variable_name	GlobalValue	SessionValue
DELAYED_INSERT_THREADS	0	0
SLOW_LAUNCH_THREADS	0	0
THREADS_CACHED	101	101
THREADS_CONNECTED	3	3
THREADS_CREATED	149	149
THREADS_RUNNING	2	2

(2) Cache miss rate: If this value should be <=0.01
select format(TC*100/CN,2) CacheMissRate from (select variable_value TC from information_schema.global_status where variable_name='Threads_created') A,(select variable_value CN from information_schema.global_status where variable_name='Connections') B;

(3) Cache hit rate: This value should be >=90
select format((QH/(QH+QI+QnC))*100,2) CacheHitRate from
(select variable_value QH from information_schema.global_status where variable_name='Qcache_hits') A,
(select variable_value QI from information_schema.global_status where variable_name='Qcache_inserts') B,
(select variable_value QnC from information_schema.global_status where variable_name='Qcache_not_cached') C;

(4) Key Buffer Used: if result is (<=100 ) then your all indexes are cached into key_buffer
if result is (>100) then your all indexes are not cached into key_buffer you may gain performance boost by increasing key_buffer_size.

select format((Index_Size)/(kbsmb/(1024*1024)) * 100,2) as key_buffer_used
from
(SELECT SUM(INDEX_LENGTH)/(1024*1024) 'Index_Size' FROM information_schema.TABLES where ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema')) A,
(select variable_value kbsmb from information_schema.global_variables where variable_name='key_buffer_size') B;

(5) Table_open_cache optimum value: open_files_limit should also higer than this value.
(max_connections * (maximum number of innodb tables per join + 2*maximum number of MyISAM tables per join))+ $x
Where $x is few reserved value. As per the size of Database and uses, we should assign $x value.

(6) List down table properties: We can add more column names in the select list
SELECT table_name,table_type,Engine,version,table_rows FROM information_schema.tables WHERE table_schema = 'DBname';

(7) List down slow queries: N is the maximum number in seconds. After this value, query will be treated as slow query.
SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST  where command !='sleep' and time>=N and command !='Binlog Dump'  order by TIME desc;

(8) List down command wise processlist:
select command, count(command) as CNT from information_schema.processlist group by command;
Example of result:

Command	CNT
Binlog  Dump	1
Query	2
Sleep	12

(9) List down state wise processlist:
select state, count(state) from information_schema.processlist group by state;
Example of result:

State	Count (state)
executing	1
Reading from net	1
Writing to net	1
Sorting	1

(10) List locked tables: List down all the locked tables
show open tables where name_locked>0;
  

Start with MongoDB - A new experience with NoSQL database

I will not discuss about what MongoDB is?
MongoDB official website itself provides all the information what you need. Also, there is a lot of information available on Internet when you search Why there is a need of NoSQL database?
Below are the steps to start with MongoDB in Windows:
1. Download the appropriate version from Download page.
2. Double click on installer and follow the installation wizard.
By default, you will not see any service named as MongoDB in Services window.
You have to create a configuration file, log file and data directory and mention the path of log file and datapath in the configuration file.
3. Locate the path of installed MongoDB in your system.
4. MongoDB is not restricted with any specific path for data directory or log path. It depends upon you what path you assigned in configuration file.
For example, MongoDB has been installed in C:\Program Files\MongoDB 2.6 Standard\
Important note: if directory name contains space then we must put the path within double quote.
5. Open the Command Prompt as administrator. (Right click on the CMD link and select "Run as administrator").
6.  Create data directory. 
C:\Program Files\MongoDB 2.6 Standard\bin>md data
C:\Program Files\MongoDB 2.6 Standard\bin>cd data
C:\Program Files\MongoDB 2.6 Standard\bin\data>md db
C:\Program Files\MongoDB 2.6 Standard\bin\data>cd..
C:\Program Files\MongoDB 2.6 Standard\bin>cd..
7. Create log path
C:\Program Files\MongoDB 2.6 Standard>md log
C:\Program Files\MongoDB 2.6 Standard>cd log
C:\Program Files\MongoDB 2.6 Standard\log>type NUL > mongo.log
C:\Program Files\MongoDB 2.6 Standard\log>cd..
8. Create configuration file
C:\Program Files\MongoDB 2.6 Standard>echo logpath="C:\Program Files\MongoDB 2.6
 Standard\log\mongo.log" > "C:\Program Files\MongoDB 2.6 Standard\mongod.cfg"
Concatenate below line in your configuration file:-
dbpath="C:\Program Files\MongoDB 2.6 Standard\bin\data\db"
9. Start MongoDB server
C:\Program Files\MongoDB 2.6 Standard>"C:\Program Files\MongoDB 2.6 Standard\bin\mongod.exe" --dbpath="C:\Program Files\MongoDB 2.6 Standard\bin\data\db"
MongoDB server will be started and you will see that MongoDB server is waiting for connections. Please do not close this window.
10. Open MongoDB shell 
Open another Command prompt with  administrator privileges.
C:\Program Files\MongoDB 2.6 Standard\bin>mongo.exe
Shell prompt will display where you can execute MongoDB commands and statements just like this one.

If you want to avoid to start MongoDB server each time, you can install this as a service too.
11. Install MongoDB sevice in windows system:
C:\Program Files\MongoDB 2.6 Standard>"C:\Program Files\MongoDB 2.6 Standard\bin
\mongod.exe" --config "C:\Program Files\MongoDB 2.6 Standard\mongod.cfg" --dbpat
h "C:\Program Files\MongoDB 2.6 Standard\bin\data\db" --logpath "C:\Program File
s\MongoDB 2.6 Standard\log\mongo.log"  --install

12. Start MongoDB service:
You will see that MongoDB is also listed in your services window ( Windws button+R -> type services.msc). From here , you can click on start.
Alternatively, you can issue below line from command line:
C:\>net start MongoDB
You will see the MongDB shell.

13. Stop MongoDB service:
First execute exit command in MongoDB shell.
C:\>net stop MongoDB

14. Uninstall MongoDB sevice:
C:\Program Files\MongoDB 2.6 Standard>"C:\Program Files\MongoDB 2.6 Standard\bin
\mongod.exe" --remove

To proceed with DB operations like creating database, select database and other operations, please visit MongoDB official tutorial.

Must read topics before working on performance improvement of MySQL based applications

Are you going to work on improving performance of an MySQL based applications? If yes, this post is for you.
MySQL has a very good documentation. On this left panel of this page, you will find reference manual list for each available version. If you are reading something in MySQL-5.1 and want to check the same topic in MySQL-5.6 you have to just change a single value in URL. Below is the example:
Below page describe the server status variable in MySQL-5.1
http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html
while below page describe the server status variable in MySQL-5.6
http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html
There is a single difference between these two URLs (5.1 Vs 5.6)

I am providing below URL list which must be covered before or during performance improvement project. I am mentioning for 5.6, if required you can change the version in URL:

1. List of URLs for MySQL Performance Tuning: Benchmarks, Best Practices, FAQs
http://forums.mysql.com/read.php?24,92131,92131#msg-92131
2. Find information about all status variables
http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html
3. Find information about all server system variables
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
4. MySQL Glossary - very important to understand the database terms
http://dev.mysql.com/doc/refman/5.6/en/glossary.html
5. Optimization - contains multiple chapters
http://dev.mysql.com/doc/refman/5.6/en/optimization.html
6. Understanding Query by Explain command
http://dev.mysql.com/doc/refman/5.6/en/using-explain.html
7. Partition Management
http://dev.mysql.com/doc/refman/5.6/en/partitioning-management.html
8. Server SQL modes
http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
9. How MySQL Uses Threads for Client Connections
http://dev.mysql.com/doc/refman/5.6/en/connection-threads.html
10. How MySQL Opens and Closes Tables
http://dev.mysql.com/doc/refman/5.6/en/table-cache.html
11. General Thread States
http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html
12. Thread Command Values
http://dev.mysql.com/doc/refman/5.6/en/thread-commands.html
13. Query Cache Thread States
http://dev.mysql.com/doc/refman/5.6/en/query-cache-thread-states.html
14. The MySQL Query Cache
http://dev.mysql.com/doc/refman/5.6/en/query-cache.html
15. MySQL Cluster Overview - only if you have implemented  MySQL cluster database
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-overview.html
16. MySQL option files - information about configuration file and group mentioned in that file
http://dev.mysql.com/doc/refman/5.6/en/option-files.html
17. Query Cache Status and Maintenance
http://dev.mysql.com/doc/refman/5.6/en/query-cache-status-and-maintenance.html
18. Query Cache Configuration
http://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html
19. How MySQL Uses Internal Temporary Tables
http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html
20. Optimizing Disk I/O
https://dev.mysql.com/doc/refman/5.6/en/disk-issues.html

Hopefully you would have enough understanding after going through these document pages of MySQL. Feel free to drop your valuable comment if you want to discuss about anything.

Install and uninstall of MySQL server and client on RHEL / Fedora

Please note that yum is case sensitive and it maintain its own database.

Download MySQL installer and placed into a directory. Unzip that file. For example, you have placed all installer into mysql_installer directory within /u000.
For installation of MySQL-5.6 in RHEL / Fedora server, Prashanth written a very good post.
1. Install shared library first
 sudo rpm -ivh /u000/mysql_installer/MySQL-shared-community-5.x.y-1.rhel5.x86_64.rpm
If you face any compatibility issue, try to install another library:
sudo rpm -ivh /u000/mysql_installer/MySQL-shared-compat-5.1.73-1.rhel5.x86_64.rpm
2. Install mysql server
sudo rpm -ivh /u000/mysql_installer/MySQL-server-community-5.x.y-1.rhel5.x86_64.rpm
Preparing...                ########################################### [100%]
1:MySQL-server-community ########################################### [100%]
You will get message for further instructions required to follow.
MySQL-5.1 has empty password while for MySQL-5.5 and MySQL-5.6 stored default password in a file /root/.mysql_secret

3. Install mysql client
sudo rpm -ivh /u000/mysql_installer/MySQL-client-community-5.1.73-1.rhel5.x86_64.rpm
Only after installing the client, you would be able to use MySQL prompt - mysql>

User management
Login with default credentials ( username root and password mentioned in file for MySQL-5.6, empty for MySQL-5.1)
$ mysql -u root -p****
Now, you will get mysql> prompt

mysql> create user 'newuser'@'hostname' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

If you want to create user for external access like from application or from any client tool.
mysql> create user 'newuser'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Grant permission (all means - insert, update, delete, select, drop, shutdown, reload, process, file, grant, refrences, indexes, alter etc.)
mysql> GRANT ALL ON *.* TO 'newuser'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
Above command is not secure. You are granting all permissions to this user and that user can make connection from everywhere. So, take precaution when granting permissions.

Now try to access from outside ( from your application or from tool by using hostname, username and password provided in previous mysql command by you).

In the installation information, you will find the location of my.cnf file. There is a single my.cnf file in MySQ-5.6 (/usr/my.cnf) while there are multiple my.cnf files available in different location for MySQL-5.1. To find all location, please run below command
mysql --verbose --help | grep -C3 my.cnf

Below is the command to start / stop the mysql service:

1. Stop MySQL service  - sudo /etc/init.d/mysql stop
2. Start MySQL service - sudo /etc/init.d/mysql start
3. Restart (stop + start) - sudo /etc/init.d/mysql restart 

You can update the parameters values in my.cnf file. After change, don't forget to restart the mysql service.

    Uninstall MySQL completely

Remove mysql server - please note that server version. It should be same as mentioned during installation.

sudo yum remove MySQL-server-5.x.y-1.rhel5.x86_64 MySQL-server

Now, remove the MySQL client:

sudo yum remove MySQL-client-community-5.1.73-1.rhel5.x86_64.rpm

In the same way, uninstall MySQL library:

sudo yum remove MySQL-shared-community-5.1.73-1.rhel5.x86_64.rpm

Clean db cache

sudo yum clean dbcache

About Linux and some useful commands

The most popular Linux distribution (and very good for newbies) is Mint, followed by Ubuntu. Both are based on the Debian packaging system (Ubuntu is based on Debain and Mint is based on Ubuntu). On the other side of the divide, you have the RPM systems, the most popular one for end users are Fedora and openSuSe.
Then you have the various source based ones like Arch and Gentoo.
As a new user, I would recommend you try one of Mint, Ubuntu or openSuSe or perhaps Manjaro.
there is a Mint Debian Edition you could try.

Debian is not user friendly. It is not very scary either but Mint actually actively tries to be user friendly while Debian does not.
Well, Debian is a distribution. RPM is a package format. The two main package formats in the Linux world are .deb (used by Debian and its derivatives) and RPM (used by RedHat/Fedora and their derivatives).
That is true for all Debian based distributions. Kind of true for RPM-based ones too but they often have fewer choices immediately available from their repositories.
Well, Linux is just a kernel. Think of that as the core of the operating system. A distribution is a collection of software (kernel+tools) that all together make a working operating system. Different distributions have set up a different ecosystem around the core Linux kernel.
 from the perspective of a new user, the most important differences are 1) the package management system (which defines how you can install/manage your software) and 2) the default graphical environment (the Desktop Environment, or DE).
 While any DE can be installed on any distribution, different distros have chosen different DEs as default and that can affect your choice if you don't feel comfortable installing one yourself.

1. Start, stop, restart  a service:
sudo service service_name start
For example, if want to start mysql service, execute command like: sudo service mysql start
sudo service service_name stop
sudo service service_name restart

2. Find a file or directory:
locate xyz
find directory_path -name "file_name / directory_name"
you can also use wildcard characters. For example sudo find /bin -name "mysql*"
If you want to search a file in all the sub-directories, run below command:
find . -name "filename" - you can also use wildcard characters.
For example: find . -name 'abhishek_xyz*.csv'

3. To collect memory / CPU information of Linux server
 cat /proc/meminfo
cat /proc/cpuinfo

4. display the contents of the fstab configuration file, which contains all partitions and storage devices connected to the computer, both internal and external:
sudo cat /etc/fstab

5. Describe Filesystem,Size,Used,Avail,Use%, Mounted on
df -h

6. Describe Filesystem,Size,Used,Avail,Use%, Mounted on
df -h

7. find the filesystem of below directory:
df /home

8. List all the installed applications:
rpm -qa | less or
yum list installed

9. Know all the processor running status
mpstat -P ALL

10. Know CPU running history:
sar

11. Find out running Linux kernel version:
uname -mrs  or
uname -a  or
cat /proc/version

12. Display list of all open ports
netstat -tulpn

13. List PID of all the HTTP applications running on Port 80
 fuser 80/tcp

14. Display list of all running tasks likewise task manager of windows server
top

15. Unzip a tar  and zip file
tar -zxvf data.tar.gz
unzip sourcefilename destinationfilename

15. List directories under a file system

tree -LP levelnumber partitionNumber
The -P command stands for pattern and L is level Max display depth of the directory tree.
Make sure your in the / directory when running this command
For example:
If you have 4 partitions of your hard disk likewise sda1,sda2,sda3,sda4 and you want to list all the files and directories till 2 levels only under partition sda2: run the below command:
tree -LP 2 /dev/sda2

16. Copy a directory from source to destination
cp -avr sourceDIR destinationDir
For example:
cp -avr /var/lib/mysql/proddb /var/lib/mysql
-a : Preserve the specified attributes such as directory an file mode, ownership, timestamps, if possible additional attributes: context, links, xattr, all.
-v : Explain what is being done.
-r : Copy directories recursively.

MySQL status variables: Difference between Created_tmp_tables and Created_tmp_disk_tables

MySQL has two very important types of variables: -

1. Global variables
2. Status variables

For optimum performance, global variables should be tuned as per the application usage and available resources. It is recommended to tune (increase or decrease the value) one parameter (global variable) a time and then use the system with peak load and check the value of respective status variables. These status variables indicate positive and negative affect of tuned global variable.
Usually, status variables are counter variable since start of MySQL service.

There are two status variable - created_tmp_tables and created_tmp_disk tables.
These variables are related with  tmp_table_size and  max_heap_table_size
Since max_heap_table_size is specially for memory storage engine. If your database hasn't any table which storage engine type is not memory, we can leave it.
Created_tmp_tables is increased when server creates an internal temporary tables either in memory or in disk. Temporary tables created in memory if size is small and placed on disk when size increased. This size is determined by tmp_table_size and max_heap_table_size global variables.
Created_tmp_disk_tables is increased when server creates an internal temporary tables only on disk.
This is the reason we always find value of created_tmp_tables is always higher than created_tmp_disk_tables.

Created_tmp_tables = Created_tmp_disk_tables + Temporary tables open in memory

Please feel free to input your comments...

MySQL Query Optimization - avoid temporary tables in query execution

Hey folks,

After a long interval, I have again returned to my blog. Sorry for this long gap.

I am going to share a very important part of web development. It is the query optimization. For any web application irrespective of their technologies, SQL is query required to insert the records as well as fetch the records from database. If the number of tables/records are less, there is no major effect on the performance of the website. But, if we have to fetch the data by joining multiple tables and one or more tables contains huge amount of records, site performance degrade and it increase a heavy load on the web server as well as database server. So, writing optimized query is very important. We must follow the RDBMS rules while designing the database for an application.
Starting out improving performance with MySQL (assuming you don't already know what the problem query is) is to check the slow query log - it logs to a file all queries taking longer than x seconds.

Lets focus on optimization of queries:
During the work, I found a keyword "Explain" and "Explain Extended".
Please visit below urls to get the details knowledge about these:
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html (Optimizing Queries with Explain)
http://dev.mysql.com/doc/refman/5.0/en/explain-extended.html (Explain Extended Ouptput Format)
http://stackoverflow.com/questions/362223/best-mysql-performance-tuning-tool
explain is anonymous of describe. But, it works magically when we use this keyword before any select query. After execution, it returned following fields:
ID,
select_type,
table,
type,
possible_keys,
key,
key_len,
ref,
rows,
extra

Below is the best tutorial which will help you to understand in and out of Explain:
http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

Below are some common things which must be taken care while writing / optimizing queries:

• Try to avoid In clause
• Add Index for those columns which are frequently used by the query
• Arrange the WHERE clause properly - In WHERE clause write those conditional statements first which are going to filter more records.
• Setup MySQL query cache and give it more RAM if possible
• Put a timestamp instead of now() function in mysql query
• If required add new columns
• If your query has Group by and /or order by clause on a column, then try to place the table of that respective column at first place in joining of multiple tables. For example, if you have applied Group by and /or order by clause on column X of table XYZ then try to place XYZ table as first table in join. This effort will avoid creation of temporary tables.
• Try to avoid Blob or Text column in your column. Use these data types as a last option. If it happens - Temporary tables will create on Disk only. 
•  Place Group by or distinct clause on smaller columns (size <=512 bytes).
•  Try to avoid Union / Union ALL in your queries.
• GROUP_CONCAT() or COUNT(DISTINCT) evaluation also tend to create temporary tables. If possible, you can also try to avoid such clauses.
• If executing update statement which will update multiple tables also tend to create temporary tables.