why index refuse to work??

We never thought primary key refuse to work in a query even if the key included in where clause. yes, it happens. I got a a mail from developer team saying a very simple query is taking minutes to fetch the records although it should take milliseconds. initially i execute the query and check with explain.

mysql> explain select * from ad_detail where ad_id=5029;
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | ad_details | ALL  | PRIMARY       | NULL | NULL    | NULL | 3926237 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

ad_id is primary key, query should use this key. however, it not happening here. where is the problem?

lets check the table structure.

mysql> desc ad_details;
+------------------+----------------+------+-----+-------------------+-----------------------------+
| Field            | Type           | Null | Key | Default           | Extra                       |
+------------------+----------------+------+-----+-------------------+-----------------------------+
| AD_ID            | varchar(50)    | NO   | PRI | NULL              |                             |
| SA_ID           | varchar(50)    | YES  | MUL | NULL              |                             |
| BOOKING_C   | varchar(20)    | YES  | MUL | INT               |                             |
| CT_ID           | varchar(10)    | YES  |     | NULL              |                             |
| SB_CT_ID       | varchar(10)    | YES  |     | NULL              |                             |
| AD_TITLE         | varchar(110)   | YES  |     | NULL              |                             |
| AD_DESCRIPTION   | varchar(10100) | YES  |     | NULL              |                             |
| EMAIL            | varchar(50)    | YES  |     | NULL              |                             |
| MOBILE           | varchar(15)    | YES  |     | NULL              |                             |
| LAND_LINE        | varchar(20)    | YES  |     | NULL              |                             |
| AD_POST_DATE     | date           | YES  |     | NULL              |                             |
| AD_EXPIRY_DATE   | date           | YES  |     | NULL              |                             |
-----------------------------------------------------------------------------------------------

wow!!! here is the problem, ad_id is varchar. comparing string/varchar to integer not going to use the index ad_id. put the ad_id value in " "

change the query to select * from ad_detail where ad_id="5029";

lets the explain output again

mysql> explain select * from ad_detail where ad_id="5029";
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | ad_details | const | PRIMARY       | PRIMARY | 52      | const |    1 |       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+----

Now we can see the query is using PRIMARY as key and just scanning 1 record to execute it.
This is very simple mistake we sometimes do referring a varchar to integer value in code/application.

I think for better utilization of index we should keep primary key as integer rather than a varchar.

Is MySQL 5.6 slower than 5.5?

Since MySQL 5.6 released there are so many benchmarks reports posted using the sysbench OLTP workload. Most of the benchmarks are surprising and totally disagree with the claim MySQL posted about performance gain of more than 200% with MySQL 5.6. So I did sysbench OLTP benchmark on old system which show MySQL 5.5 has performance edge over MySQL 5.6. Its very early now to make any assumptions on performance because the new version just starting in production use or in QA/Staging. So after 5-6 month we might have real time performance benchmark.
One important point we should remember that MySQL 5.6 has performance schema enabled by default and the latest version has lots of improvement in performance schema which reduced overhead so i have added benchmark with and without performance schema feature.
My Hardware configuration is:
Red Hat Enterprise Linux Server release 5.2 2.6.18-92.el5
Dual-Core AMD Opteron(tm) Processor 2216 4 CPU
8 GB RAM
Without any mysql tuning parameter.

sysbench --test=oltp --mysql-user=root --mysql-password=root --oltp-table-size=1000000 --mysql-db=test --max-time=60 --max-requests=0 --num-threads=? run

                                                          Read Only Benchmark

The graphs are clearly showing better MySQL 5.5 performance over 5.6, also we could see 5.6 without performance schema is better than using performance schema, i think performance schema has little overhead.

The Read Write benchmark also showing MySQL 5.5 performance is better than 5.6, however 5.6 with out performance schema is performing better than MySQL 5.5

                                       Read Write Benchmark

Lets tweak some parameters on MySQL 5.6 and see the graph. I think Buffer Pool might make a difference in performance so i did another benchmark with Buffer Pool 4 GB, again results are not encouraging.
There are two more important parameters which affect performances suddenly if configured properly according to hardware and OS configuration. These are : innodb_adaptive_hash_index and innodb_spin_wait_delay. I see some performance gain if  innodb_spin_wait_delay is disabled but still performance is below MySQL 5.5. No performance impact can be seen if innodb_adaptive_hash_index is OFF.

See the cumulative graph for Read Only Benchmark.

Intrepreting MySQL Binary Logs using mysqlbinlog

MySQL Database Server logs each change data statement in binary logs. Binary logs generally used for replicating all changes on master database server to slave database server. These logs can be used for disaster recovery, to perform some specific database maintenance activities or for database incremental backup. Binary logging can be enabled using log-bin parameter in my.cnf ( the MySQL configuration  file ). So to analyze and process logs in binary format mysqlbinlog utility is used. This tools convert the binary logs into plain text or human readable format depending upon the binlog format set in my.cnf.
There are three binary log format STATEMENT : queries logged in plain text format, ROW : queries logged on binary format and MIXED. Default binary log format is STATEMENT.

To start with binary logs lets execute some data change queries on mysql :

create table test ( id int );
insert into test values (1);
insert into test values (2);
alter table test add column( id2 int);

$ mysqlbinlog mysql-bin.000004

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130325 16:20:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.30-log created 130325 16:20:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
DyxQUQ8BAAAAZwAAAGsAAAABAAQANS41LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAPLFBREzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#130325 16:21:08 server id 1  end_log_pos 198   Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1364208668/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test ( id int )
/*!*/;
# at 198
#130325 16:21:17 server id 1  end_log_pos 266   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364208677/*!*/;
BEGIN
/*!*/;
# at 266
#130325 16:21:17 server id 1  end_log_pos 356   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364208677/*!*/;
insert into test values (1)
/*!*/;
# at 356
#130325 16:21:17 server id 1  end_log_pos 383   Xid = 8
COMMIT/*!*/;
# at 383
#130325 16:21:18 server id 1  end_log_pos 451   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364208678/*!*/;
BEGIN
/*!*/;
# at 451
#130325 16:21:18 server id 1  end_log_pos 541   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364208678/*!*/;
insert into test values (2)
/*!*/;
# at 541
#130325 16:21:18 server id 1  end_log_pos 568   Xid = 9
COMMIT/*!*/;
# at 568
#130325 16:21:26 server id 1  end_log_pos 668   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364208686/*!*/;
alter table test add column( id2 int)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

We could see all queries in plain text from above mysqlbinlog output when binary log format is STATEMENT.

mysqlbinlog displays row events encoded as base-64 string which is not human readable.
Here is mysqlbinlog output with ROW format:

$ mysqlbinlog mysql-bin.000004 

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130325 16:42:34 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.30-log created 130325 16:42:34 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IjFQUQ8BAAAAZwAAAGsAAAABAAQANS41LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAiMVBREzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#130325 16:42:52 server id 1  end_log_pos 198   Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1364209972/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test ( id int )
/*!*/;
# at 198
#130325 16:42:57 server id 1  end_log_pos 266   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209977/*!*/;
BEGIN
/*!*/;
# at 266
#130325 16:42:57 server id 1  end_log_pos 309   Table_map: `test`.`test` mapped to number 34
# at 309
#130325 16:42:57 server id 1  end_log_pos 343   Write_rows: table id 34 flags: STMT_END_F

BINLOG '
OTFQURMBAAAAKwAAADUBAAAAACIAAAAAAAEABHRlc3QABHRlc3QAAQMAAQ==
OTFQURcBAAAAIgAAAFcBAAAAACIAAAAAAAEAAf/+AQAAAA==
'/*!*/;
# at 343
#130325 16:42:57 server id 1  end_log_pos 370   Xid = 8
COMMIT/*!*/;
# at 370
#130325 16:43:00 server id 1  end_log_pos 438   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209980/*!*/;
BEGIN
/*!*/;
# at 438
#130325 16:43:00 server id 1  end_log_pos 481   Table_map: `test`.`test` mapped to number 34
# at 481
#130325 16:43:00 server id 1  end_log_pos 515   Write_rows: table id 34 flags: STMT_END_F

BINLOG '
PDFQURMBAAAAKwAAAOEBAAAAACIAAAAAAAEABHRlc3QABHRlc3QAAQMAAQ==
PDFQURcBAAAAIgAAAAMCAAAAACIAAAAAAAEAAf/+AgAAAA==
'/*!*/;
# at 515
#130325 16:43:00 server id 1  end_log_pos 542   Xid = 9
COMMIT/*!*/;
# at 542
#130325 16:43:03 server id 1  end_log_pos 642   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209983/*!*/;
alter table test add column( id2 int)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 To display row events as comments run mysqlbinlog with -v option:

$mysqlbinlog SM-Couple1-bin.000004 -v
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130325 16:42:34 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.30-log created 130325 16:42:34 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IjFQUQ8BAAAAZwAAAGsAAAABAAQANS41LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAiMVBREzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#130325 16:42:52 server id 1  end_log_pos 198   Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1364209972/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test ( id int )
/*!*/;
# at 198
#130325 16:42:57 server id 1  end_log_pos 266   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209977/*!*/;
BEGIN
/*!*/;
# at 266
#130325 16:42:57 server id 1  end_log_pos 309   Table_map: `test`.`test` mapped to number 34
# at 309
#130325 16:42:57 server id 1  end_log_pos 343   Write_rows: table id 34 flags: STMT_END_F

BINLOG '
OTFQURMBAAAAKwAAADUBAAAAACIAAAAAAAEABHRlc3QABHRlc3QAAQMAAQ==
OTFQURcBAAAAIgAAAFcBAAAAACIAAAAAAAEAAf/+AQAAAA==
'/*!*/;
### INSERT INTO `test`.`test`
### SET
###   @1=1
# at 343
#130325 16:42:57 server id 1  end_log_pos 370   Xid = 8
COMMIT/*!*/;
# at 370
#130325 16:43:00 server id 1  end_log_pos 438   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209980/*!*/;
BEGIN
/*!*/;
# at 438
#130325 16:43:00 server id 1  end_log_pos 481   Table_map: `test`.`test` mapped to number 34
# at 481
#130325 16:43:00 server id 1  end_log_pos 515   Write_rows: table id 34 flags: STMT_END_F

BINLOG '
PDFQURMBAAAAKwAAAOEBAAAAACIAAAAAAAEABHRlc3QABHRlc3QAAQMAAQ==
PDFQURcBAAAAIgAAAAMCAAAAACIAAAAAAAEAAf/+AgAAAA==
'/*!*/;
### INSERT INTO `test`.`test`
### SET
###   @1=2
# at 515
#130325 16:43:00 server id 1  end_log_pos 542   Xid = 9
COMMIT/*!*/;
# at 542
#130325 16:43:03 server id 1  end_log_pos 642   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209983/*!*/;
alter table test add column( id2 int)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

We can suppress BINLOG for row events using

$  mysqlbinlog SM-Couple1-bin.000005 -v --base64-output=DECODE-ROWS

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130325 16:42:34 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.30-log created 130325 16:42:34 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 107
#130325 16:42:52 server id 1  end_log_pos 198   Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1364209972/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test ( id int )
/*!*/;
# at 198
#130325 16:42:57 server id 1  end_log_pos 266   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209977/*!*/;
BEGIN
/*!*/;
# at 266
#130325 16:42:57 server id 1  end_log_pos 309   Table_map: `test`.`test` mapped to number 34
# at 309
#130325 16:42:57 server id 1  end_log_pos 343   Write_rows: table id 34 flags: STMT_END_F
### INSERT INTO `test`.`test`
### SET
###   @1=1
# at 343
#130325 16:42:57 server id 1  end_log_pos 370   Xid = 8
COMMIT/*!*/;
# at 370
#130325 16:43:00 server id 1  end_log_pos 438   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209980/*!*/;
BEGIN
/*!*/;
# at 438
#130325 16:43:00 server id 1  end_log_pos 481   Table_map: `test`.`test` mapped to number 34
# at 481
#130325 16:43:00 server id 1  end_log_pos 515   Write_rows: table id 34 flags: STMT_END_F
### INSERT INTO `test`.`test`
### SET
###   @1=2
# at 515
#130325 16:43:00 server id 1  end_log_pos 542   Xid = 9
COMMIT/*!*/;
# at 542
#130325 16:43:03 server id 1  end_log_pos 642   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1364209983/*!*/;
alter table test add column( id2 int)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

You should not suppress BINLOG statements if you intend to re-execute mysqlbinlog output.

Compairing MySQL Enterprise and MySQL Community Edition

MySQL Community Edition have been used by many organization in production and as well as in QA/Development and staging setup. Reason is very simple: it is simple to install, configure and monitor, it is scalable when required and provide high availability if configured in that way. These feature makes it worlds first widely industry accepted open source database server.

Then why Enterprise Edition is available and what additional features enterprise edition have???
YES, Enterprise Edition has additional feature, what are those features???

1. Support : support for mission critical issues and hot fixes.
2. Backup : MEB ( MySQL Enterprise Backup ), online InnoDB,MyISAM backups.
3. Monitoring Tools : MEM ( MySQL Enterprise Monitor ), Query Analyzer, Web Based configuration
4. Plugins : Additional plugins for performance improvement i.e. thread pool, PAM authentication etc

Do we really need above features depends upon company financial policies, however in startups Community Edition is used and monitoring or analysis is carried out using shell/perl scripts.

The most important feature of Enterprise Edition is its thread pool plugin which boost the performance at optimal configuration and traffic on database server.

What is Thread Pool in MySQL and how it enhance the performance??

The MySQL Thread Pool is a MySQL server plugin that extends the default connection-handling capabilities of the MySQL server to limit the number of concurrently executing statements/queries and transactions to ensure that each has sufficient CPU and memory resources to fulfill its task.
The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The Thread Pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The Thread Pool plugin increases server performance by efficiently managing statement execution threads for large numbers of client connections, especially on modern multi-CPU/Core systems
The Thread Pool uses a “divide and conquer” approach to limiting and balancing concurrency. Unlike the default connection handling of the MySQL Server, the Thread Pool separates connections and threads, so there is no fixed relationship between connections and the threads that execute statements received from those connections. The Thread Pool then manages client connections within configurable thread groups, where they are prioritized and queued based on the nature of the work they were submitted to accomplish. 

With the new thread pool plugin, there is now an alternative way to handle connection threads in MySQL Enterprise Edition.  With the plugin, MySQL connection threads are shared like an extraordinarily well managed timeshare in Hawaii.  When one connection is “idle”, asking nothing of and expecting nothing from the database, another connection can use that same thread for its database requests.  Threads are released by each connection as soon as the request is completed and  go back into the pool for re-use – just like the theoretical timeshare is up for grabs on the weeks you are not there.
In the older, and still default connection thread model, threads are dedicated to a single client  for the life of the connection and there are as many threads as there are clients currently connected to the database.  This has some disadvantages when the server workload must scale to handle large numbers of connections, and the overhead can be signficant. This occurs for several reasons:

• Lots of threads use lots of memory and can make the CPU cache ineffective
• Too many active threads trying to execute in parallel may cause a high level of resource contention and be inappropriate for the amount of parallelism available

The new thread pool plugin offers an alternative thread pool implementation, and focuses on limiting the number of concurrent, short running statements to mazimize performance and reduce overhead.  By limiting the number of concurrent, short running statements and sharing threads, we can control the number of active threads at any one time.  Thread management has been revamped and by managing these threads in a highly efficient manner, we end up reducing overhead and often increasing performance. 
Here are the mechanics:  In the new plugin, threads are organized into groups (16 by default but configurable up to 64 on server startup).  Each group starts with one thread and can increase to a maximum of 4096 threads.  Additional threads are created only when necessary.  Each incoming connection request is assigned to a group by round robin. Each group has one listener thread that listens for incoming statement requests.
When a statement request comes in, it is executed immediately by the group’s listener thread if it is not busy and there are no other statement requests waiting.  If the statement request finishes quickly, the listener thread then efficiently returns to listening and is available to execute the next incoming request, preventing the need for a new thread to be created.   If the request does not finish quickly, it runs to completion but another thread is  created as the new listener.
If the listener thread is busy, the request is queued.  There will be a very brief time (configurable with the thread_pool_stall_limit system variable which defaults to 60 ms) while we wait to see if the currently executing statement will finish quickly or not. If it finishes quickly (under thread_pool_stall_limit), we can re-use this thread for the next request in the queue, eliminating the overhead of creating a new thread or having too many short statement trying to execute in parallel .
You can see how this thread pool design strives to have one thread executing per group at any time . The number of groups (thread_pool_size_variable) is very important, because it approximates the number of short running statements that will be executing concurrently at any one time.  Long running statements are prevented from causing other statements to wait, since if they go beyond the thread_pool_stall_limit, another thread will be started and the next request in the queue will execute on it.
Your predominant storage engine will help determine the number of groups you should have.  For InnoDB, between 16 and 36 groups seems to work well in many cases, but for MyISAM set it much lower (4-8).
There are two queues for waiting statements, low and high priority.  The low priority queue contains:

• all statements for non-transactional storage engines
• all statements if autocommit is enabled
• the first statement in  an InnoDB transaction

These statements do not languish in the low priority queue forever since they will get kicked over to the high priority queue when the thread_pool_kickup_timer times them out. However, there is a maximum number of statements that can be moved per time period to keep things under control.
The high priority queue contains

• any subsequent statements in InnoDB transactions, and
• any statements kicked up from the low priority queue.

 
 From all benchmarks we've seen that the performance of the thread pool
when operated with less than the optimal number of active connections is
about 1-3% slower than without thread pool since the behaviour is the same
and the thread pool adds a little bit more overhead. More or less all of
this overhead is to handle KILL query correctly.

When operated in the region of the optimal number of active connections
the performance is very similar. We have seen though that the thread pool
benefits very much from locking the MySQL Server to a number of CPUs
equal to the setting of the thread_pool_size configuration parameter.
When not locked to CPUs the performance is similar, when locked to CPUs
the thread pool gives 10-15% higher performance when using the optimal
number of active connections. The MySQL Server operated without thread
pool and locked to CPUs have no significant change of throughput compared
to not locking to CPUs.

When operating above optimal number of connections the thread pool
provides a great benefit, we've seen numbers all the way up to 100x
better performance when operating with a few thousand concurrently
active connections.

Whats new in MySQL 5.6

MySQL 5.6 released last month downloadable for general with massive features added for better performance, scalability, high availability and seciruty.

Here are most important feature and drastic change in MySQL 5.6.

1. Online DDL/Schema changes
2. Full Text Search in InnoDB Storage Engine
3. Introduction of GTID Global Transaction Identifiers in Replication and High Availability.
4. New transportable Tablespace
5. Better optimizer diagnostics : EXPLAIN for insert, update, delete operations.

All features in a Nutshell.

Scalability
- Scalable Read Only Transactions
- Concurrent Innodb data file extension
- Non-Recursive Deadlock Detection
- Faster Locking Primitives
- Improved Innodb Thread Concurrency
- Multiple background Purge Threads
- Improved Purge lag control (now works)
- Split of “Kernel Mutex”
- Data Dictionary Cache
- Improved Adaptive Flushing
- Page Cleaner/Separate Flush Thread
- Group Commit for Binary Log
- Fight Cache Coherence and False Sharing issues
- Reduced Innodb Memory Fragmentation
- Reduced Locking for Partitioned tables
- Reduced Contention for LOCK_open
- Support for multiple table_open_cache instances
- Large (over 4GB) redo logs support
Optimizer and Execution
- Index Condition pushdown (ICP)
- Multi-Range-Read (MRR)
- Faster ORDER BY nidxcol LIMIT N
- Persistent Statistics for Innodb
- Improvements to Innodb Compression
- Fast Page Checksums (CRC32)
- 4K and 8K Page sizes for Innodb
- Improvement to Buffer Pool Flushing
- Subquery Optimizations
- More efficient Optimizer
Replication
- Optimized ROW Based Replication
- Multi-Threaded Slave
- Global Transaction Identifiers
- Crash Safe Slave and Binlog
- Replication Event Checksums
- Time Delayed Replication
- Server UUID
- Improved Logging for Row based Replication
- Replication Utilities for Failover and Admin
Transparency
- Many new INFORMATION_SCHEMA Tables
- – INNODB_METRICS
- – Meta Data Information Tables
- – Buffer Pool Information Tables
- Improved PERFORMANCE_SCHEMA
- – Reduced Overhead
- – Simplified Configuration
- – Table Access instrumentation
- – Statements instrumentation
- – Stages Instrumentation
- – Aggregations by User, Host etc
- – Network IO Instrumentation
- – Show Host Cache Contents
- – Improved File I/O Instrumentation
- Improved EXPLAIN
- – Explain for UPDATE/DELETE queries
- – JSON output with more information
- Optimizer Tracing
- Deadlock Logging
- GET DIAGNOSTICS
Operational Improvements
- Separate Tablespaces for Innodb Undo Logs
- Fast Restart – Preloading Innodb Buffer Pool
- Online DDL
- Import/Export for Partitioned Tables
- Remote Binlog Backup
- Innodb Transportable Tablespaces
- New configuration files defaults
- User Defined DATA DIRECTORY for Innodb Tables
- Connection Attributes
New Functionality for Developers
- MemcacheD API in Innodb
- Explicit Partition Selection in queries
- Full Text Search index for Innodb
- Microsecond TIME precision
- Precise spatial operations in GIS
Security
- Password hashes instead of plain passwords in Query Logs
- SHA256 hashing with Salt for Authentication
- Use obfuscated password storage for command line tools
- Policy Based password validation
- Plugin authentication support for Replication

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

MySQL 5.6 added comprehensive password security mechanism how password and internally handled and encrypted.
One major feature is auto encrypted password generation when mysql first time installed on your system. you can find the encrypted password in /root/.mysql_secret. Once you are into mysql using encrypted password you will get this error until unless root password not rest using :
SET PASSWORD = PASSWORD('new_password');

[root@SM-Couple1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> use mysql
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> show grants;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement


Now reset the pssword:

mysql> SET PASSWORD = PASSWORD('root');
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>


Another way to reset password is to run /usr/bin/mysql_secure_installation

[ERROR] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction'

In situation of table/row locking or deadlock on slave server, slave server let the transaction wait for time configured with  innodb_lock_wait_timeout and retry the transaction for the times configured in  slave_transaction_retries.  After all tries and lock timeout save server throws following error in logs.

130125 13:24:30 [ERROR] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'live'. Query: 'INSERT into users (user_id, login, password, first_name, last_name, email, is, created, changed, picture, last_login, token) values (1, 'asad9@yahoo.com', '35ce1d4eb0', 'Prince A', 'S', 'asad9@yahoo.com', 1, 13, 13, 'photo_13.jpg', 13, NULL)', Error_code: 1205

Get all the deadlocks and locking transaction details and tables involved in locking using:

#mysql >show engine innodb status\G;

To avoid further locking you need to do changes at application to prevent bulk updates and select simultaneously. Also change storage engine MyISAM which use table locking to InnoDB which use row level locking.

The simple solution for this problem is to increase the value for innodb_lock_wait_timeout and slave_transaction_retries.

 There are other possible reason for this error: table is corrupt so repair the table.

http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-innodb_lock_wait_timeout.html
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#sysvar_slave_transaction_retries

mysqldump: Got error: 1146: Table 'tablename' doesn’t exist when doing LOCK TABLES

if you receive the error message while mysql database backup

There are few possible reason of this error:

• permission and ownership on tables files in /var/lib/mysql is not correct, ideal ownership should be to mysql user and permission 660.
• table not exist, check with show tables.
• table created in uppercase, set lower_case_table_names as per your application requirement.
• table is corrupt, repair the table.

if all above points are ok then use --skip-lock-tables parameter with mysqldump.

I am sure this will resolve the error.

 

mysqldump: Got error: 1066: Not unique table/alias: foo when using LOCK TABLES

mysqldump trying to lock lock table before starting a database dump but not able to lock and throwing this error.
After analyzing table names carefully i found few tables duplicate table exist with names in Uppercase and lowercase when lower_case_table_names=1

mysql> show tables;
+-----------------------------------+
| Tables_in_power         |
+-----------------------------------+
| Message_seq                       |
| User_seq                          |
| message_seq                |
| user_seq                |
+-----------------------------------+ 

Resolved by removing duplicate table message_seq  and user_seq.

This issue of duplicate tables occurs when application issue a CREATE TABLE command with table name in upper case or lower case  other than the existing tables with same name and lower_case_table_names=1.

For permanent resolution change  lower_case_table_names=0 if application don't have any dependency on it.

 

native code=1030 ** Got error 139 from storage engine

Recently in one of my application logs I see insert failing with "native code=1030 ** Got error 139 from storage engine" error, database was in production for more than 2 years and never see this error. This seems to be simple problem but due to its nature it may affect after you already have a running database in production which is in my case. This is storage engine specific error, InnoDB.
This is problem related to innodb row size limitations and storage of tablespace on disks.
In short InnoDB gives this error when it can't store all variable length column for a given row on single database page. So,

What is database page?
What is InnoDB row size limit?
How database page relate to row length?

Database pages are the internal basic structure to organize the data in the database files. The data file you define in configuration file logically concatenated to form the tablespace, The tablespace consists of database pages with default size 16 K. ( In Mysql 5.6 it is possible to change the page size to 4k or 8k in addition to original size 16k by putting  innodb_page_size=n variable in configuration file or you can set –innodb-page-size=n when starting mysqld, Previously, it could be done by recompiling the engine with a different value for UNIV_PAGE_SIZE_SHIFT and UNIV_PAGE_SIZE.) 

 Mysql store two rows on single page of 16 K, So the maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.
If a row length is less than half a page long ~ 8000 bytes, all of it is stored locally within the single database page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page. For a column chosen for off-page storage, InnoDB stores the first 768 bytes locally in the row, and the rest externally into overflow pages.

Now the real problem comes now, if you have more than 10 columns of variable length type TEXT,BLOB and each column exceeding database page size then InnoDB needs 768 x 11 = 8448 bytes to store variable lenght columns not counting other fixed length columns. This exceeds the limit and therefore you get the error. 

Few suggestions to solve this issue:

1. Upgrade to Barracuda format : Barracuda file format use 20 bytes of variables length columns on single database page rather than 768 bytes. so now more data in a row can be stored on a page without any size error.

SET GLOBAL innodb_file_format=Barracuda; 

SET GLOBAL innodb_file_per_table=ON; 

ALTER TABLE (your table) ROW_FORMAT=COMPRESSED;

mysql> show table status like 'company_info'\G
*************************** 1. row ***************************
           Name: company_info
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 13
 Avg_row_length: 32768
    Data_length: 425984
Max_data_length: 0
   Index_length: 32768
      Data_free: 0
 Auto_increment: 39
    Create_time: 2013-01-11 17:49:55
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 0 kB
1 row in set (0.00 sec)


mysql> alter table company_info row_format=compressed;
Query OK, 22 rows affected (0.83 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> show table status like 'company_info'\G
*************************** 1. row ***************************
           Name: company_info
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 13
 Avg_row_length: 31507
    Data_length: 409600
Max_data_length: 0
   Index_length: 32768
      Data_free: 0
 Auto_increment: 39
    Create_time: 2013-01-11 18:36:45
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: InnoDB free: 0 kB
1 row in set (0.00 sec)

2. Limit the seize of variable length column, that might need change at application side.
3. Limit table to have upto 10 columns of variable length type.
4. Upgrade to latest mysql version 5.6 which have flexibility setting default database page size of 16K with innodb_page_size variable.

MySQL Performance Analysis

I have been facing mysql database server slowness, application slow response, database cpu load shoot up all of a sudden and sometimes reboot needed to recover from high load. 

How we can analyze database performance and how we can fine tune it. 
Here are few important analysis we need to do to improve database performance:

• Slow query analysis (http://mysqlyogi.blogspot.in/2013/01/slow-query-analysis.html)
• Worker thread analysis (http://mysqlyogi.blogspot.in/2013/01/worker-thread-analysis.html)
• Connections usage analysis
• Innodb status/data size analysis
• Memory usage analysis
• Key buffer analysis
• Query cache analysis
• Sort/join operation analysis
• Open file analysis
• Table cache analysis
• Temp table analysis
• Table scan analysis
• Table locking analysis
• Queries (insert/select/delete/update) analysis 
• Storage engine analysis 

Slow query analysis

Slow query analysis is very important to dig low mysql performance, high cpu utilization, slow query response and application performance. The process of analyzing slow query includes enabling slow query in mysql server and then process the logs using scripts or query analysis tools.

How to Enable Slow Query log:

Add following line in /etc/my.cnf ( mysql configuration file )
slow_query_log=ON
slow_query_log_file=slowquery_logfile_path
stop start mysql

versions 5.1 and later allow to enable slow query log without restarting mysql.

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

If no file_name value is given for --log-slow-queries, the default name is host_name-slow.log. The server creates the file in the data directory unless 
an absolute path name is given to specify a different directory. 

By default mysql logs slow queries taking more than 10 seconds, we can reduce query log time by setting variable long_query_time in /etc/my.cnf or at run time on mysql console

mysql> set global long_query_time=5.000000;
Query OK, 0 rows affected (0.00 sec)

Now all sql queries/statement taking more than 5 seconds will be logged into log file in plain text with additional information.

Mysql Worker thread analysis

Every connection to mysql database needs a thread to process the request and send back results. Each connections use some part of cpu resource to execute the thread. If you application makes lots of separate connections to your database over short periods of time or create and close  lot of connections  every seconds, the process of spawning new threads at high rate cause a lot of overhead on cpu which allocate and de allocate associated stacks for each connections or threads. To resolve this problem mysql implements thread cache, which allow it to save threads from connections that are being closed and reuse them for new connections. The parameter thread_cache_size defined how many unused threads can be kept in memory so that application can reuse them without creating a new thread.

To check what is current value of thread_cache_size configured:

 mysql> select @@thread_cache_size;

+---------------------+

| @@thread_cache_size |

+---------------------+

|                 512 | 

+---------------------+

1 row in set (0.00 sec)

 Or

mysql> show variables like '%thread%';

+----------------------------+--------+

| Variable_name              | Value  |

+----------------------------+--------+

| max_delayed_threads        | 20     | 

| max_insert_delayed_threads | 20     | 

| myisam_repair_threads      | 1      | 

| thread_cache_size          | 512    | 

+----------------------------+--------+

To determine how much the thread_cache_size variable should be set, we need to check thread status

mysql>show status like '%thread%';

+------------------------------------------+-------+

| Variable_name             | Value |

+------------------------------------------+-------+

| Slow_launch_thread        | 0     |

| Threads_cached            | 88    |

| Threads_connected         | 27    |

| Threads_created           | 115   |

| Threads_running           | 5     |

+------------------------------------------+-------+

8 rows in set (0.00 sec)

mysql> show status like '%connections%';

+----------------------+--------+

| Variable_name        | Value  |

+----------------------+--------+

| Connections          | 291200 |

| Max_used_connections | 115    |

+----------------------+--------+

2 rows in set (0.00 sec)

Thread_cache_size should be atleast equal to max_connections configured, 150 in above case.

Or we need to monitor the beheaviour of thread creation on mysql for some time and then get following ratios/values:

thread cache hit rate  = 100 - ((Threads_created / Connections) * 100)

current thread per second =  Threads_Created  - Threads_Created_after_one_second

historical thread per second = Threads_Created / Uptime

Given the above value of 115 for Threads_created, and the value of 291200  that Connections showed, My Thread Cache Hit Ratio is 99% which is good.
The ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% hit ratio as you can

What is thread_concurrency variable in mysql : after goggling and from mysql manual i found  it does nothing on GNU/Linux so dont waste time in using this variable. There are two problem with this variable, One is it is deprecated in latest mysql version 5.6 and second it only works on old Solaris Versions .

From mysql manual

This variable is specific to Solaris systems, for which mysqld invokes the thr_setconcurrency() with the variable value. This function enables applications to give the threads system a hint about the desired number of threads that should be run at the same time. This variable was added in MySQL 3.23.7.

 What is initial memory taken by a thread when a client connect to mysql? 
mysql allocate memory to each thread efficiently whenever a new connection is made to mysql server. Initially a connection use memory specified by three specific variables thread_stack, a connection buffer and result buffer variable net_buffer_length. The connection buffer and result buffer begin with size equal to net_buffer_length and dynamically expand up to max_allowed_packed when needed and memory released after the results sent to the client. Thread kept initial memory until released by the client, after the thread disconnected its memory released to the system. in case thread moved to thread cache, memory remains allocated to thread.

A thread consume memory additionally to initial buffer whenever query is performing specific function/operation i.e. sorting, joining, caching, temporary tables creation, scanning, updating etc. For these operations a thread allocate memory defined in other buffer read_buffer_size, join_buffer_size, sort_buffer_size etc.

We can calculate how much maximum memory can be allocated to a thread when  we assume thread is doing all operations:

total per-thread buffer usage = (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size + net_buffer_length ) 

Lets Calculate minimum memory assigned to threads at any point of time:

Get the current running/connected thread connected and thread cached.


mysql> show status like '%thread%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+|     
| Threads_cached                           | 136   |
| Threads_connected                        | 4     |
| Threads_created                          | 140   |
| Threads_running                          | 2     |
+------------------------------------------+-------+

Get the thread value for thread_stack and net_buffer_length

mysql> show variables like '%thread_stack%';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| thread_stack  | 262144 |
+---------------+--------+ 

mysql> show variables like '%net_buffer_length%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+

So minimum memory allocated to threads is  : (262144+16384)*140=38993920=37MB