Wednesday, May 30, 2012

Basic Performance Tuning Concepts for MySQL

Here is the some common performance tuning concepts/process that I follow to tune the MySQL Server. This is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage.

Below is the steps I follow to tune MySQL Server.

1.    Query optimization

2.    Data type Optimization

3.    MySQL Server variables optimization

4.    Hardware optimization





1.    Query optimization/Tuning

First, though probably the most important, we look at tuning queries. We check whether they are executing smoothly or not. In particular, we make sure that they’re using indexes, joining on appropriate columns and they’re running quickly. To get the list of those queries which are not using index, executing smoothly or taking more the expected time in execution, we turn on the Slow Query Log for some time, with proper setting of long query time and variables.  At the end we will got the log file with queries which have taken more than expected time in execution.  Run the resulting log through mysqldumpslow, which will produce a summary of the log. This summary will have the detail of query, query execution time, occurrence of query.   This will help you prioritize which queries to tackle first. Then, you can use EXPLAIN to find out what they’re doing, and adjust your indexes accordingly.



Note: - Adding a new index is overhead on I/O. Think before you are adding any index/indexes on the table.



2.    Data type Optimization

In this step we will check weather data type for all columns has been declared correctly or not. We will check it with procedure analyze function. Remember it will calculate/Suggest based on current database size/data.  Before changing it also think about future prospects.



3.    MySQL Server variables optimization

For tuning Innodb performance, the  primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data, indexes and various pieces of information about your database. The bigger, the better. If you can cache all of your data in memory, you’ll see significant performance improvements.

For MyISAM, there is a similar buffer defined by key_buffer_size, though this is only used for indexes, not data. Again, the bigger, the better.

Other variables that are worth investigating for performance tuning are:

query_cache_size - This can be very useful if you have a small number of read queries that are repeated frequently, with no write queries in between. There have been problems with too large a query cache locking up the server, so you will need to experiment to find a value that’s right for you.

innodb_log_file_size - Don’t fall into the trap of setting this to be too large. A large InnoDB log file group is necessary if you have lots of large, concurrent transactions, but comes at the expense of slowing down InnoDB recover, in event of a crash.

sort_buffer_size - Another one that shouldn’t be set too large.

innodb_flush_log_at_trx_commit—How innoDB will commit


4.    Hardware optimization

There are a few recommendations for improving the performance of MySQL by upgrading your hardware:

·         Use a 64-bit processor, operating system and MySQL binary. This will allow you to address lots of RAM.

·         Speaking of RAM, buy lots of it. Enough to fit all of your data and indexes, if you can.

·         If you can’t fit all of your data into RAM, you’ll need fast disks, RAID if you can. Have multiple disks, so you can separate your data files, OS files and log files onto different physical disks.

Maximum MySQL Database Size

While working as DBA , we think what is the maximum size of database/file MySQL/File system can support.  While looking answer for same I found some interesting details and thought to share.

Below are the estimated maximum file sizes per operating system:

Operating System
File-size Limit
Win32 w/ FAT/FAT32
2GB/4GB
Win32 w/ NTFS
2TB (possibly larger)
Linux 2.2-Intel 32-bit
2GB (LFS: 4GB)
Linux 2.4+
(using ext3 file system) 4TB
Solaris 9/10
16TB
MacOS X w/ HFS+
2TB
NetWare w/NSS file system
8TB

This information was taken directly off MySQL.com but this is not necessarily the maximum limitations of your database. A number of methods can help to increase your max file size:

LFS (Large File Support) in Linux
To support files larger than 2GiB on 32-bit Linux systems you would have to use LFS. The standard max file size limitations without LFS enabled are 2^31 bytes(2GiB), but enabling LFS can enable your maximum file size to reach 2^63 bytes (9 223 372 036 854 775 808 bytes).

Using the “Alter Table” command
This will come in handy when using the MyISAM storage engine. The simple “Alter Table” in the mysql prompt command can extend your database capacity dramatically.
Example: “alter table ‘weather’ max_rows = 200000000000″
Although keep in mind, the maximum amount of rows in a MySQL table can only be 4.2billion (not so good if you’re thinking of making a search engine!)

Most of this researched information is very old so I decided to run a little check (which I should have done right in the beginning), and by doing so on my local machine, I literally nearly fell of my chair:

mysql> show table status like ‘blog_hits’ \G
*************************** 1. row ***************************
Name: blog_hits
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 15497
Avg_row_length: 247
Data_length: 3835852
Max_data_length: 281474976710655
Index_length: 366592
Data_free: 0
Auto_increment: 15509
Create_time: 2009-11-24 16:53:38
Update_time: 2009-11-24 16:53:38
Check_time: 2009-11-24 16:53:38
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

This is telling me my max database size can be as much as 281474976710655 bytes which If I’m not mistaken, equates to 256 terabytes. With this being said, I think we are going to run into system limitations rather than MySQL limitations, so therefore, revert back to the table at the top of this post.

I have taken reference from below blog posts.


Tuesday, May 22, 2012

MySQL basic utility


Whenever you install MySQL inside the bin folder you can see some file other then mysqld and mysqld. These are utilities which used to different purpose. Here is the list some of the utilities. 

1.    mysqladmin: This utility can do all admin related. The exception is MySQL Service Start.  It can stop MySQL service but will not be able to start it. Below is the list for options available for it.
  create databasename   Create a new database
  debug                 Instruct server to write debug information to log
  drop databasename     Delete a database and all its tables
  extended-status       Gives an extended status message from the server
  flush-hosts           Flush all cached hosts
  flush-logs            Flush all logs
  flush-status          Clear status variables
  flush-tables          Flush all tables
  flush-threads         Flush the thread cache
  flush-privileges      Reload grant tables (same as reload)
  kill id,id,...        Kill mysql threads
  password [new-password] Change old password to new-password in current format
  old-password [new-password] Change old password to new-password in old format
  ping                  Check if mysqld is alive
  processlist           Show list of active threads in server
  reload                Reload grant tables
  refresh               Flush all tables and close and open logfiles
  shutdown              Take server down
  status                Gives a short status message from the server
  start-slave           Start slave
  stop-slave            Stop slave
  variables             Prints variables available
  version               Get version info from server
         
Below is example for same.
[root@cluster1 ~]# mysqladmin ping
mysqld is alive
[root@cluster1 ~]# mysqladmin processlist
+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+
| Id | User | Host      | db | Command | Time | State | Info             | Rows_sent | Rows_examined | Rows_read |
+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+
| 11 | root | localhost |    | Query   | 0    |       | show processlist | 0         | 0             | 1         |
+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+
2.    mysqlcheck: This command will check the status of all MySQL Tables and show you the output. In case of any error you can check/repair that table further. Below is the example for same.
[root@cluster1 ~]# mysqlcheck -B sakila -a
sakila.actor                                       OK
sakila.address                                     OK
sakila.category                                    OK
sakila.city                                        OK
sakila.country                                     OK
sakila.customer                                    OK
sakila.film                                        OK
sakila.film_actor                                  OK
sakila.film_category                               OK
sakila.film_text                                   Table is already up to date
sakila.inventory                                   OK
sakila.language                                    OK
sakila.payment                                     OK
sakila.rental                                      OK
sakila.staff                                       OK
sakila.store                                       OK
3.    mysqlshow: This command will show the list of all created database on MySQL instance. Below is the example.
[root@cluster1 ~]# mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakil              |
| sakila             |
| test               |
+--------------------+

Execute Linux command from MySQL prompt


When we are working on MySQL running on Linux, We also need to execute some Linux command.  As we need to see the listing the file, clear the MySQL prompt screen. There is no command in MySQL to clear the screen.  In MySQL help you will find the command “\c”, it will show you to clear the current input. It will just cancel the last statement on MySQL Prompt.

Now there are two ways to execute Linux commands on MySQL prompt.
1.    Using the keyword “system”
2.    Using the shortcut “\!”
If you want to clear the MySQL screen running on Linux, execute below command on MySQL prompt
mysql> system clear
or
mysql> \! Clear
These both command will clear the MySQL console.
To execute any other Linux add these keywords before the commands.  See the below example.
mysql> system ls -lrt /home/mysql/cluster
total 12
drwxrwxr-x.  2 mysql mysql 4096 Apr 24 23:46 ndb_data
drwxrwxr-x.  2 mysql mysql 4096 May  4 22:21 config
drwxrwxr-x. 16 mysql mysql 4096 May 16 20:28 MySQL_Data
mysql>
mysql> \! ls -lrt /home/mysql/cluster
total 12
drwxrwxr-x.  2 mysql mysql 4096 Apr 24 23:46 ndb_data
drwxrwxr-x.  2 mysql mysql 4096 May  4 22:21 config
drwxrwxr-x. 16 mysql mysql 4096 May 16 20:28 MySQL_Data
mysql>
The above example is showing the list of file in folder.
You can also execute MySQL specific commands (Taking backup etc). See below example.

mysql> system mysqldump -uroot  sakila>/home/mysql/sakila.sql
mysql>
mysql> system ls -lrt /home/mysql/
total 3280
drwxr-xr-x. 13 mysql mysql    4096 Apr 20 03:21 mysql-cluster-gpl-7.2.5-linux2.6-i686
lrwxrwxrwx.  1 mysql mysql      37 Apr 20 22:18 mysqlc -> mysql-cluster-gpl-7.2.5-linux2.6-i686
drwxrwxr-x.  5 mysql mysql    4096 Apr 24 23:46 cluster
-rw-r--r--.  1 root  root  3350468 May 21 02:00 sakila.sql
mysql>
mysql> \! tail  /home/mysql/sakila.sql

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-05-21  2:00:47

For taking backup I have executed below command on MySQL Prompt.

 mysql> system mysqldump -uroot  sakila>/home/mysql/sakila.sql
Once the execution of this statement is complete, I see the listing of file using below command.
mysql> system ls -lrt /home/mysql/
total 3280
drwxr-xr-x. 13 mysql mysql    4096 Apr 20 03:21 mysql-cluster-gpl-7.2.5-linux2.6-i686
lrwxrwxrwx.  1 mysql mysql      37 Apr 20 22:18 mysqlc -> mysql-cluster-gpl-7.2.5-linux2.6-i686
drwxrwxr-x.  5 mysql mysql    4096 Apr 24 23:46 cluster
-rw-r--r--.  1 root  root  3350468 May 21 02:00 sakila.sql

To check the backup file content

mysql> \! tail  /home/mysql/sakila.sql
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2012-05-21  2:00:47

See the below image for detail


Monday, May 21, 2012

How to Reset MySQL Root User Password


When you working on Linux system.  Sometimes you forget MySQL root user password.  As a DBA you need to recover/Reset it. As recovery of password is not possible, you need to reset it. To reset the password you need to follow below steps.



1.    Log on to your system as the UNIX mysql user (User by which your MySQL Service is running).

2.    Locate the .pid (Process ID file) file that contains the server's process ID (MySQL process ID). The exact location and name of this file depend on your installation (RPM/Binary/Source), host name, and configuration (my.cnf ). Some common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. File name has an extension of .pid and begins with either mysqld or your system's host name.

3.    Stop the MySQL Service. You can stop MySQL Service by using different way.

1.    Service mysql stop: It will work if your mysql is installed as a service

2.    /etc/init.d/mysql stop : It will work on some system

3.    You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command

shell> kill `cat /mysql-data-directory/host_name.pid`

                       

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

4.    Search running MySQL Process  on server using below command

 shell> ps –aef|grep mysql



5.    The above command will return the detail about MySQL Running process, copy the process id and kill the process by executing below command

shell>kill -9 ‘Process id of MySQL Process’



4.    Once you have stopped the MySQL service you have 2 ways to resetting the root password.

1.    Using the init file

2.    Using --skip-grant-tables options



1.    Using the init file: Create a text file and place the following statements in it. Replace the password with the password that you want to use.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;


The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory.

Save the file. For this example, the file will be named /home/mysql/myl-init. Now you can start MySQL Service using 2 ways.

a. Start the MySQL server with the special --init-file option:

shell> mysqld_safe --init-file=/home/mysql/myl-init &

The server executes the contents of the file named by the --init-file option at startup, changing each root account password.

Now login to MySQL instance and start using it



2.    Using --skip-grant-tables options



shell> mysqld_safe  --skip-grant-table &



Log in to MySQL using no password



shell> mysql –uroot



On MySQL prompt execute below command



UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

                                                                                                       

Restart the MySQL process and use it with newly configured password







5.    Now your MySQL root password has been changed. You can use new password.



Happy Working.. J