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