Useful MySQL Command to troubleshoot Database

1. Display available databases

Below command will show all database available on this instance.

> mysqlshow  -uroot –p

2. Display all tables in a database

Below command will display all the tables located under test database

> mysqlshow  -u root –p test

3. Display tables along with number of columns in a database

> mysqlshow  -v -u root -p test

4. Display total number of columns and rows of all tables in a database

Please note there are two -v in the following command.

> mysqlshow  -v -v -u root -p test

5. Display all columns of a table

In the following example, it displays the entire available column name along with additional column information for accounts table in test database.

> mysqlshow  -u root -p test test

6. Display details about a specific column from a table

In this example, it displays information about id column from test table.

> mysqlshow  -u root –p test test id

7. Show all metadata information about a table

> mysqlshow  -i  -uroot -p test test

8. Display both indexes and columns of a table

Please note that the indexes are listed at the bottom of the display after the column information.

> mysqlshow -k -u root -p test test

Database: test  Table: test

9. Display only indexes and not columns of a table

This can be done by tricking the mysqlshow, by giving an invalid column name. Since invalid_col_name doesn’t exist on accounts table, the following command will display only the indexes of test table.

> mysqlshow -k -u root -p test test invalid_col_name

  Database: test  Table: test Wildcard: invalid_col_name

How to Boost MySQL Scalability

We face a lot of scalability challenges with clients again and again. The list of point I cannot limit to numbers. However I am giving some basic ideas to scale MySQL Server.

1. Tune queries which are causing performance degradation

Till now the biggest cause of performance buck is unoptimized query. Queries can be functionally correct and meet business requirements, but if they are used without stress tested for high traffic and high load can cause pain.   This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn’t necessarily be a good use of time to tune a query for some page off in a remote corner of your site, that didn’t receive real-world traffic. So some amount of reactive tuning is common and appropriate.

Enable the slow query log, watch it, analyze it and try to optimize slow queries. Also make sure the log_queries_not_using_indexes flag is set.  Once you’ve found a heavy resource intensive query, optimize it!  Use the EXPLAIN facility, use a profiler, look at index usage and create missing indexes, and understand how it is joining and/or sorting.

2. Employ Master-Master Replication

Master-master active-passive replication, otherwise known as circular replication, can be a boon for high availability, but also for scalability.  That’s because you immediately have a read-only slave for your application to hit as well.  Many web applications exhibit an 80/20 split, where 80% of activity is read or SELECT and the remainder is INSERT and UPDATE.  Configure your application to send read traffic to the slave or rearchitect so this is possible.  This type of horizontal scalability can then be extended further, adding additional read-only slaves to the infrastructure as necessary.

But use it only Active-Passive mode. If you want to use Active-Active replication, make sure you are not using same database on both server at same time. It may cause data inconsistency as MySQL does not having ability for Remote Locking.

3. Use Your Memory

It sounds very basic and straightforward, yet there are often details overlooked.  At minimum be sure to set these:

• innodb_buffer_pool_size
• key_buffer_size (MyISAM index caching)
• query_cache_size
• thread_cache & table_cache
• innodb_log_file_size & innodb_log_buffer_size
• sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size
• tmp_table_size & max_heap_table_size

4. RAID Your Disk I/O

What is underneath your database?  You don’t know?  Well please find out!  Are you using RAID 5?  This is a big performance hit.  RAID5 is slow for inserts and updates.  It is also almost non-functional during a rebuild if you lose a disk.  Very very slow performance.  What should I use instead?  RAID 10 mirroring and striping, with as many disks as you can fit in your server or raid cabinet.  A database does a lot of disk I/O even if you have enough memory to hold the entire database.  Why?  Sorting requires rearranging rows, as does group by, joins, and so forth.  Plus the transaction log is disk I/O as well!

Are you running on EC2?  In that case EBS is already fault tolerant and redundant.  So give your performance a boost by striping-only across a number of EBS volumes using the Linux md software raid.

5. Tune Key Parameters

These additional parameters can also help a lot with performance.

innodb_flush_log_at_trx_commit=2

This speeds up inserts & updates dramatically by being a little bit lazy about flushing the innodb log buffer.  You can do more research yourself but for most environments this setting is recommended.

innodb_file_per_table

Innodb was developed like Oracle with the tablespace model for storage.  Apparently the kernel developers didn’t do a very good job.  That’s because the default setting to use a single tablespace turns out to be a performance bottleneck.  Contention for file descriptors and so forth.  This setting makes innodb create tablespace and underlying datafile for each table, just like MyISAM does.

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.

1.    http://nickduncan.co.za/maximum-mysql-database-size/

2.    http://mysqldump.azundris.com/index.php?url=archives/98-How-large-can-a-MySQL-database-become.html

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