-
New MySQL 5.6 Features by Oli Sennhauser
First talk at FOSDEM MySQL Devroom by Oli Sennhauser, of FromDual. Quick notes/liveblog of the talk, plus links from a quick search.
New Release Model: starts with at least in beta quality, milestone releases are RC quality (every 3-6 months), between milestones new features are allowed, GA releases every 12-18 months, no more than 2 releases in active support. There is also MySQL Labs, in where features can make it into a release model, but not necessarily.
Oli’s guess: MySQL 5.6 GA in April 2012 (Collaborate) or June 2012. As a consequence, MySQL 5.0 and MySQL 5.1 will be EOL probably by April this year.
New improvements in partitioning: explicit partition selection, exchanging partitions (good for ETL jobs).
New improvements in InnoDB: InnoDB INFORMATION_SCHEMA has got some new entries. Buffer (INNODB_BUFFER), Fulltext (INNODB_FT), Metrics (INNODB_METRICS), Data Dictionary (INNODB_SYS). The InnoDB Monitor is now obsolete. innodb_purge_threads can be set > 1. Kernel mutex split which should lead to improved concurrency. Persistent optimizer statistics (since InnoDB used to do random dives, so when you do a mysqldump, things can be different) – you can do SET GLOBAL innodb_analyze_is_persistent = 1; (read more: InnoDB Persistent Statistics at last, InnoDB Persistent Statistics Save the Day).
New improvements in optimizer: ORDER by on non-indexed columns, Multi Range Read (MRR), Index Condition Pushdown (ICP), query execution plan for DML statements, Batched Key Access (BKA). There is also optimizer tracing.
There is now some new instrumentations in Performance Schema (introduced in MySQL 5.5). There is also improved replication features, but that will be covered in a later talk.
Fractional seconds (microseconds) is introduced in MySQL 5.6. GET DIAGNOSTICS for stored procedures. Pluggable authentication (socket). memcached/InnoDB still in labs, hasn’t made it to a milestone release. Some 400+ bugs have been fixed. Lots of cleanup’s (old unused variables, commands removed).
Pay attention to MySQL 5.6 incompatible changes when you plan to upgrade.
Probably some good resources: What’s New in MySQL 5.6, MySQL 5.6 (Early Access Features) – InnoDB & Replication.
Related posts:MariaDB/MySQL users in Paris & Brussels
Ticketmaster thrives on MySQL Replication
Some MySQL-related links
-
Consistent transactions between storage engines
You may not realize it, but in MariaDB 5.2 and earlier and in MySQL up to version 5.5, START TRANSACTION WITH CONSISTENT SNAPSHOT does not give any guarantees of consistency between different storage engines.
For example, suppose you have two transactions which run in parallel:
Transaction T1:
BEGIN;
SET @t = NOW();
UPDATE xtradb_table SET a= @t WHERE id = 5;
UPDATE pbxt_table SET b= @t WHERE id = 5;
COMMIT;
Transaction T2:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT t1.a, t2.b
FROM xtradb_table t1 INNER JOIN pbxt_table t2 ON t1.id=t2.id
WHERE t1.id = 5;
In the above case, it is possible, even with a "consistent" snapshot, to see the changes in a transaction only in InnoDB/XtraDB tables, and not in PBXT tables.
Naturally, it would be much better if the changes were visible no matter the storage engine used by the table. Thankfully this is possible, thanks to the introduction in MariaDB 5.3
of group commit
and an enhanced storage engine API for COMMIT which allows storage engines to coordinate commit ordering and visibility with each other and with the binary log.
With these improvements, the START TRANSACTION WITH CONSISTENT SNAPSHOT statement in MariaDB 5.3 has been enhanced to ensure consistency in-between storage engines which support the new API. Currently the storage engines which support this are XtraDB (MariaDB's enhanced version of InnoDB) and PBXT. In addition, the binary log, while not a storage engine as such, also supports the new API and can provide a binlog position consistent with storage engine transaction snapshots.
This means that with transaction isolation level at least REPEATABLE READ, the START TRANSACTION WITH CONSISTENT SNAPSHOT statement can be used to ensure that queries will see a transaction-consistent view of the database between storage engines.
No longer is it possible for a query to see the changes from some transaction T in XtraDB tables without also seeing the changes T makes to PBXT tables.
More information on this can be found in the AskMonty Knowledgebase
.
-
OurSQL Episode 77: Removing Evil Queries, part 1
Registration for Percona Live: MySQL Conference and Expo is open! The conference is from Tuesday, April 10th through Thursday, April 12th. Early bird pricing ends March 12th, 2012.
Use code PL-pod and save 10% off the early bird prices!.
The 1st Latin American Conference about MySQL, NoSQL and Cloud technologies will be held in Buenos Aires in June. It is called the MariaDB NoSQL & Cloud Latin American Conference and we'll bring you more information as it becomes available.
read more
-
Stripping Comments so Query Cache Works in MariaDB and XtraDB
I recently noticed both MariaDB and XtraDB (not MySQL yet) have a (newer) variable query_cache_strip_comments.
This variable is great for those who want to append comments to various queries, but still want the query cache to be able to serve such queries. Unfortunately, with MySQL, this is not currently possible.
In the past, I wrote a post on using MySQL Proxy which described a technique of monitoring queries through the proxy by appending IP addresses to the queries so one could track where they originated from. However, one pitfall to that was the MySQL query cache *does not* ignore the comment and treats them all as different queries (see the user comments for further discussion). (I did subsequently enhance that functionality implementing the SHOW PROXY PROCESSLIST command (often used in the Proxy Admin module), in large part because of this limitation.)
To enable it (in MariaDB 5.3+ and XtraDB), just add query_cache_strip_comments under the [mysqld] section in your my.cnf file and restart mysqld.
Alternatively, you can also set it dynamically:
mysql> set @@global.query_cache_strip_comments=1;
Query OK, 0 rows affected (0.04 sec)
mysql> show global variables like 'query_cache_strip_comments';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| query_cache_strip_comments | ON |
+----------------------------+-------+
mysql> select version();
+---------------+
| version() |
+---------------+
| 5.3.3-MariaDB |
+---------------+
Fwiw, from examining the source code, both implementations seemed to differ, but the end result is the same, and it’s a welcome addition, if you ask me.
-
Forums.MySQL.Com as a gym for mental exercise
The MySQL Forums seem to be have been around forever. There are groups for announcements, performance, InnoDB, Cluster, Replication, Certification, Events, questions in German, partitioning, and just under a hundred MySQL related subjects in total. Most folks in the MySQLverse seem to regularly lurk in one or more of the forums. There area great source of information and sometimes humor. But are you using the forums to help build your MySQL mental muscle? Ask yourself how would you solve the problem or how would you guide a junior DBA or developer to fix an issue posted in a forum.
Try reading the groups that are not in your regular reading list. Is there a question in there you can not answer even after a peek at the documentation?
After running MySQL for a few years, it is easy to skip over the problems of novices found in the Newbie section. But like the old story of the Emperor’s New Clothes, a new set of eyes can point out things we do not notice. Recently someone in the Newbie group wanted to set up a round robin for inserts over eight disks in an attempt to get 8x performance gain. Hmmm, if that did scale would it scale linearly(1) or close to it? Too bad I do not have the hardware or spare cycles to give it a try.
Or question on how to remove a ‘width=xxx’ pattern from a text field. Hmm, the old GIGO rules would dictate that some sort of ETL tool or script remove the unwanted chaff before it goes into the database. Do you use regular expressions in a PHP, Perl, or Java hack? Could a column oriented editor let you WYSIWYG the data? Maybe a trigger on insert that culls the unwanted width data? Hey, does your data have anything hidden in a text filed that should not be there and how best to get rid of it?
So read through a few forums and use them to exercise you mind. And if you see something in a question that you can answer, please do so.
Nothing scales linearly that you need to scale linearly. At least not on your budget and or schedule.
|
News Feeds