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.
Monday, March 25, 2013
Wednesday, March 20, 2013
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???
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:
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:
The high priority queue contains
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.
Then why Enterprise Edition is available and what additional features enterprise edition have???
YES, Enterprise Edition has additional feature, what are those features???
- Support : support for mission critical issues and hot fixes.
- Backup : MEB ( MySQL Enterprise Backup ), online InnoDB,MyISAM backups.
- Monitoring Tools : MEM ( MySQL Enterprise Monitor ), Query Analyzer, Web Based configuration
- 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
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
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.
Thursday, March 7, 2013
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.
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
Here are most important feature and drastic change in MySQL 5.6.
- Online DDL/Schema changes
- Full Text Search in InnoDB Storage Engine
- Introduction of GTID Global Transaction Identifiers in Replication and High Availability.
- New transportable Tablespace
- 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
Subscribe to:
Posts (Atom)