Chris CalenderMariaDB 10.1.2 Overview and Highlights (17.1.2015, 17:03 UTC)

MariaDB 10.1.2 was recently released, and is available for download here:

This is the third alpha release of MariaDB 10.1, so there are still a lot of new changes, functionalities added, defaults changed, and many bugs fixed (I counted 117, which is *way* down from the 637 fixed in 10.1.1). Since it’s alpha, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

Truncated by Planet PHP, read more at the original (another 1892 bytes)

Chris CalenderMariaDB 10.0.15 Overview and Highlights (17.1.2015, 03:20 UTC)

MariaDB 10.0.15 was recently released, and is available for download here:

This is the sixth GA release of MariaDB 10.0, and 16th overall release of MariaDB 10.0.

This release has an important InnoDB/XtraDB fix, a new addition, security enhancements (and improvement) – all related to yaSSL, so be sure to check out these fixes if you’re running MariaDB 10.0, and not up to 10.0.15 yet. (MariaDB 10.0 is the current stable series of MariaDB. It is an evolution of the MariaDB 5.5 with several entirely new features not found anywhere else and with backported and reimplemented features from MySQL 5.6.)

Here are the main items of note:

  1. This release fixes a serious bug in InnoDB and XtraDB that sometimes could cause a hard lock up of the server (MDEV-7026)
  2. This is the first release that includes Mroonga full-text search storage engine.
  3. When compiled with OpenSSL, MariaDB now supports TLSv1.2 protocol. Limit it to TLSv1.2 ciphers only with –ssl_cipher=TLSv1.2. Limit it to SSLv3 ciphers with –ssl-cipher=SSLv3. RPM and DEB packages from are built with OpenSSL, others (for Windows and generic Linux) are built with yaSSL.
  4. Fixes for the following security vulnerabilities:
  5. Bundled PCRE is upgraded to 8.36
  6. InnoDB upgraded to 5.6.21
  7. XtraDB upgraded to 5.6.21-70.0
  8. TokuDB upgraded to 7.5.3
  9. SphinxSE upgraded to 2.2.6
  10. Updates to the CONNECT handler including:
  11. We now offer openSUSE repos, see the repository configuration tool for details on how to use it.

Given the severe InnoDB/XtraDB bug, if you’re running a prior MariaDB 10.0 version, I’d recommend upgrading (assuming you’re using InnoDB). Likewise, if you’re using SSL. And then there were a number of fixes to other fixes for TokuDB, CONNECT, and Sphinx, so if you’re using those technologies, you may want to consider upgrading as well.

You can read more about the 10.0.15 release here:

And if interested, you can review the full list of changes in 10.0.15 (changelogs) here:

Hope this helps.


Peter ZaitsevFOSDEM 2015: MySQL and Friends Community Dinner! (16.1.2015, 08:00 UTC)

As you may already be aware, FOSDEM and its wonderful MySQL and Friends track are once again descending upon the campus of the ULB in Brussels, Belgium. Logically, a traditional MySQL and Friends Community Dinner must follow! Signups for that dinner, on January 31, are now available, so be sure to get your tickets before they run out.

As 2015 heralds the 20th anniversary of MySQL, it stands to reason we would love to mark the occasion with our biggest and best edition yet!

FOSDEM 2015: MySQL and Friends Community DinnerWith the help of some wonderful sponsors, your hosts are looking forward to welcoming you for a night to remember. As such, brace yourselves for a feast prepared for the community, by the community. That’s right, we will be firing up the pizza ovens and serve you all with the best pizzas Belgium has to offer.

The listed ticket price includes all-you-can-eat pizza (or until we run out… go ahead, we dare you), as well as a great selection of Belgian specialty beers and other beverages. We’ll make sure that vegetarians have plenty of options, but anyone intending to join us with special dietary requirements, please let us know ahead of time!
We’re looking forward to meeting you all again at FOSDEM 2015 and the Community Dinner. See you then!
From the Party-Squad – Liz van Dijk, Dimitri Vanoverbeke and Kenny Gryp

Once again, we want to thank our generous sponsors, whose help makes this affordable at such a great price.
On that subject, we would like to point out that we will not be able to get an exact fix on the full cost until we have a full list of attendees, so with that in mind, the price we’ve set is based on the assumption of a certain amount of people attending. If the amount of people ends lower than our target, any remaining funds will be donated to the FOSDEM organization. FOSDEM, by the way, is a free, developer-oriented annual event attracting more than 5,000 geeks from around the world.
Community Sponsors:

Wondering how to get there from FOSDEM?

The venue itself is located very close to the VUB. You can find the route to get there right here.

The total distance from the ULB campus is about 2.3km, so you could walk there, but it might be more comfortable to take the tram.

– Tram 25 departs from the “ULB” stop regularly, and will take you up to “Hansen-Soulie”, where you need to get out, and walk the remaining 200m. The tram ride takes about 11 minutes.

The post FOSDEM 2015: MySQL and Friends Community Dinner! appeared first on MySQL Performance Blog.

Peter ZaitsevHyper-threading – how does it double CPU throughput? (15.1.2015, 08:00 UTC)

Computer CPUThe other day a customer asked me to do capacity planning for their web server farm. I was looking at the CPU graph for one of the web servers that had Hyper-threading switched ON and thought to myself: “This must be quite a misleading graph – it shows 30% CPU usage. It can’t really be that this server can handle 3 times more work?”

Or can it?

I decided to do what we usually do in such case – I decided to test it and find out the truth. Turns out – there’s more to it than meets the eye.

How Intel Hyper-Threading works

Before we get to my benchmark results, let’s talk a little bit about hyper-threading. According to Intel, Intel® Hyper-Threading Technology (Intel® HT Technology) uses processor resources more efficiently, enabling multiple threads to run on each core. As a performance feature, Intel HT Technology also increases processor throughput, improving overall performance on threaded software.

Sounds almost like magic, but in reality (and correct me if I’m wrong), what HT does essentially is – by presenting one CPU core as two CPUs (threads rather), it allows you to offload task scheduling from kernel to CPU.

So for example if you just had one physical CPU core and two tasks with the same priority running in parallel, the kernel would have to constantly switch the context so that both tasks get a fair amount of CPU time. If, however, you have the CPU presented to you as two CPUs, the kernel can give each task a CPU and take a vacation.

On the hardware level, it will still be one CPU doing the same amount of work, but there maybe some optimization to how that work is going to be executed.

My hypothesis

Here’s the problem that was driving me nuts: if HT does NOT actually give you twice more power and yet the system represents statistics for each CPU thread separately, then at 50% CPU utilization (as per mpstat on Linux), the CPU should be maxed out.

So if I tried to model the scalability of that web server – a 12-core system with HT enabled (represented as 24 CPUs on a system), assuming perfect linear scalability, here’s how it should look:

(requests per second)
9 |         ,+++++++++++++++
  |        +
  |       +
6 |      +
  |     +
  |    +
3 |   +
  |  +
  | +
0 '-----+----+----+----+----
    1   6   12   18   24

In the example above, single CPU thread could process the request in 1.2s, which is why you see it max out at 9-10 requests/sec (12/1.2).

From the user perspective, this limitation would hit VERY unexpectedly, as one would expect 50% utilization to be… well, exactly that – 50% utilization.

In fact, the CPU utilization graph would look even more frustrating. For example if I were increasing the number of parallel requests linearly from 1 to 24, here’s how that relationship should look:

CPU utilization:
100% |         ++++++++++++++
     |         .
     |         .
     |         .
     |         .
 50% |         .
     |       +
     |     +
     |   +
     | +
  0% '----+----+----+----+----
    0     6   12   18   24

Hence CPU utilization would skyrocket right at 12 cores from 50% to 100%, because in fact the system CPU would be 100% utilized at this point.

What happens in reality

Naturally, I decided to run a benchmark and see if my assumptions are correct. The benchmark was pretty basic – I wrote a CPU-intensive php script, that took 1.2s to execute on the CPU I was testing this, and bashed it over http (apache) with ab at increasing concurrency. Here’s the result:

Requests per secondRaw data can be found here.

If this does not blow your mind, please go over the facts again and then back at the graph.

Still not sure why do I find this interesting? Let me explain. If you look carefully, initially – at concurrency of 1 through 8 – it sc

Truncated by Planet PHP, read more at the original (another 1982 bytes)

Peter ZaitsevPercona XtraBackup 2.2.8 is now available (14.1.2015, 14:36 UTC)

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.2.8 on January 14, 2015. Downloads are available from our download site or Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

New Features:

  • Percona XtraBackup has been rebased on MySQL 5.6.22.

Bugs Fixed:

  • Incremental backups would fail if the number of undo tablespaces (innodb_undo_tablespaces) was more than 1. This was caused by innobackupex removing the undo tablespaces during the prepare phase. Bug fixed #1363234.
  • Fixed multiple memory leaks detected by AddressSanitizer. Bug fixed #1395143.
  • innobackupex could fail when preparing backup that was taken from Percona Server 5.5 with log files (innodb_log_file_size) bigger than 4G. The root cause was that the last checkpoint LSN offset in log group is stored at different offsets in ibdata1 for Percona Server 5.5 and MySQL 5.6 when the total size of log files is greater than 4G. Bug fixed #1403237.
  • Percona XtraBackup out-of-source builds failed. Bug fixed #1402450.

Release notes with all the bugfixes for Percona XtraBackup 2.2.8 are available in our online documentation. Bugs can be reported on the launchpad bug tracker. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

The post Percona XtraBackup 2.2.8 is now available appeared first on MySQL Performance Blog.

Peter ZaitsevMySQL performance implications of InnoDB isolation modes (14.1.2015, 11:00 UTC)

Over the past few months I’ve written a couple of posts about dangerous debt of InnoDB Transactional History and about the fact MVCC can be the cause of severe MySQL performance issues. In this post I will cover a related topic – InnoDB Transaction Isolation Modes, their relationship with MVCC (multi-version concurrency control) and how they impact MySQL performance.

The MySQL Manual provides a decent description of transaction isolation modes supported by MySQL – I will not repeat it here but rather focus on performance implications.

SERIALIZABLE – This is the strongest isolation mode to the point it essentially defeats Multi-Versioning making all SELECTs locking causing significant overhead both in terms of lock management (setting locks is expensive) and the concurrency you can get. This mode is only used in very special circumstances among MySQL Applications.

REPEATABLE READ – This is default isolation level and generally it is quite nice and convenient for the application. It sees all the data at the time of the first read (assuming using standard non-locking reads). This however comes at high cost – InnoDB needs to maintain transaction history up to the point of transaction start, which can be very expensive. The worse-case scenario being applications with a high level of updates and hot rows – you really do not want InnoDB to deal with rows which have hundreds of thousands of versions.

In terms of performance impact, both reads and writes can be impacted. For select queries traversing multiple row versions is very expensive but so it is also for updates, especially as version control seems to cause severe contention issues in MySQL 5.6

Here is example: I ran sysbench for a completely in-memory data set and when start transaction and run full table scan query couple of times while keeping transaction open:

sysbench  --num-threads=64 --report-interval=10 --max-time=0 --max-requests=0 --rand-type=pareto --oltp-table-size=80000000 --mysql-user=root --mysql-password= --mysql-db=sbinnodb  --test=/usr/share/doc/sysbench/tests/db/update_index.lua run

As you can see the write throughput drops drastically and stays low at all times when transaction is open, and not only when the query is running. This is perhaps the worse-case scenario I could find which happens when you have select outside of transaction followed by a long transaction in repeatable-read isolation mode. Though you can see regression in other cases, too.

Here is the set of queries I used if someone wants to repeat the test:

select avg(length(c)) from sbtest1;
select avg(length(c)) from sbtest1;
select sleep(300);

Not only is Repeatable Read the default isolation level, it is also used for logical backups with InnoDB – think mydumper or mysqldump –single-transaction
These results show such backup methods not only can’t be used with large data sets due to long recovery time but also can’t be used with some high write environments due to their performance impact.

READ COMMITTED mode is similar to REPEATABLE READ with the essential difference being what versions are not kept to the start of first read in transaction, but instead to the start of the current statement. As such using this mode allows InnoDB to maintain a lot less versions, especially if you do not have very long running statements. If you have some long running selects – such as reporting queries performance impact can be still severe.

In general I think good practice is to use READ COMITTED isolation mode as default and change to REPEATABLE READ for those applications or transactions which require it.

READ UNCOMMITTED – I think this is the least understood isolation mode (not a surprise as it only has 2 lines of documentation about it) which only describe it from the logical point of view. If you’re using this isolation mode you will see all the changes done in the database as they happen, even those by transactions which have not been yet committed. One nice use case for this isolation mode is what you can “watch” as some large scale UPDATE statements are happening with dirty reads showing which rows have already been changes and which have not.

So this st

Truncated by Planet PHP, read more at the original (another 2330 bytes)

Chris CalenderMySQL 5.6.22 Overview and Highlights (14.1.2015, 01:35 UTC)

MySQL 5.6.22 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Note”, 2 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them:

  1. Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1h to version 1.0.1j. Issues fixed in the new version are described at
  2. Functionality Changed: Replication: The variable binlogging_impossible_mode has been renamed binlog_error_action. binlogging_impossible_mode is now deprecated. (Bug #19507567)
  3. Functionality Changed: Security: yaSSL was upgraded to version 2.3.5. (Bug #19695101)
  4. Compilation Notes: These are all rather minor, so I’ll spare the full entries here. However, if you build MySQL from source, it would be worth several minutes to read the 5 notes in the changelogs.

In addition to those, there were 46 other bug fixes:

  • 17 InnoDB
  •   9 Replication
  • 19 Miscellaneous
  •   1 Partitioning

The highlights for me are 6 of the InnoDB bugs, as 2 were regression bugs (crashing/corruption), 2 potentially corruption causing, another crashing, and 1 halting:

  1. InnoDB: An ALTER TABLE operation raised an assertion. When a foreign key object was removed from the dictionary cache, an incorrect foreign key object was removed from the rb-tree. (Bug #19908343) References: This bug is a regression of Bug #18806829.
  2. InnoDB: Pages with a checksum value of zero were incorrectly treated as empty pages. A page should only be considered empty if its checksum value and LSN field values are zero. (Bug #19500258, Bug #73689) References: This bug is a regression of Bug #17335427.
  3. InnoDB: The dict_set_corrupted() function attempted to update the clustered index of the SYS_INDEXES data dictionary table incorrectly. (Bug #19584379).
  4. InnoDB: The InnoDB data dictionary was not updated when a ALTER TABLE … CHANGE COLUMN operation changed the case of the column name. (Bug #19465984).
  5. InnoDB: A memory access violation caused fts_optimize_thread and mysqld to terminate. (Bug #19314480).
  6. InnoDB: A procedure, called from a function to perform an operation on a temporary table, caused the server to halt/stall. (Bug #19306524)


So while there were no major changes, those 6 InnoDB bugs (2 being regression bugs) are definitely of concern, so I’d be sure to review these to see if you’re running an affected version, and consider upgrading if so.

And with the yaSSL updates, if you use SSL connections, you may want to consider upgrading as well.

The full 5.6.22 changelogs can be viewed here (which has more details about all of the bugs listed above):

Hope this helps. :)


Chris CalenderMariaDB 5.5.41 Overview and Highlights (14.1.2015, 00:25 UTC)

MariaDB 5.5.41 was recently released (it is the latest MariaDB 5.5), and is available for download here:

This is a maintenance release, and so there were not too many changes, *but* please take notice as there are 2 very important bug fixes:

Thus if you’re running pre-5.5.41 and have encountered, or even if you may be prone to, either bug #7026 or #7100, you should plan to upgrade.

If interested, the official MariaDB 5.5.41 release notes are here:

And the full list of fixed bugs and changes in MariaDB 5.5.41 can be found here:

Hope this helps.


Monty SaysFoundation report for 2014 (13.1.2015, 21:19 UTC)
2014 was a productive year for the MariaDB Foundation.

Here is a list of some of the things MariaDB Foundation employees have
accomplished during 2014:

The 3 full-time MariaDB Foundation developers have worked hard to make MariaDB better:
  • Some 260 commits
  • Some 25 reviews of code from the MariaDB community.
  • Fixed some 170 bugs and new features. For a full list, please check Jira.
  • Reported some 160 bugs.
Some of the main new features Foundation developers have worked on in 2014 are:
  • Porting and improving MariaDB on IBM Power8.
  • Porting Galera to MariaDB 10.1 as a standard feature.
  • Query timeouts (MDEV-4427)
  • Some coding and reviews of Parallel replication in MariaDB 10.1.
  • Working with code from Google and Eperi to get table space and table level encryption for InnoDB and XtraDB.
  • Allowing storage engines to shortcut group by queries (for ScaleDB) (MDEV-6080).
  • Moronga storage engine (reviews and porting help)
  • Connect storage engine (reviews and porting help)
  • Spider storage engine (merging code with MariaDB)
  • Query timeouts (MDEV-4427)
  • Merge INET6_ATON() and INET6_NTOA() from MySQL-5.6 (MDEV-4051)
  • Make "CAST(time_expr AS DATETIME)" compatible...SQL Standard) (MDEV-5372)
  • Command line variable to choose MariaDB-5.3 vs MySQL-5.6 temporal data formats (MDEV-5528)
  • Added syntax CREATE OR REPLACE to tables, databases, stored procedures, UDF:s and Views (MDEV-5491. The original TABLE code was done by Monty, other parts was done as a Google Summer Of Code project by Sriram Patil with Alexander Barkov as a mentor.
  • Upgraded the bundled Perl Compatible Regular Expression library (PCRE) to 8.34 (MDEV-5304)
  • Reduced usage of LOCK_open (MDEV-5403) (MDEV-5492) (MDEV-5587)
  • Ported patches from WebScaleSQL to MariaDB (MDEV-6039)
  • Better preallocation of memory (MDEV-7004)
  • Lock-free hash for table definition cache (MDEV-7324)
  • A lot of speed optimizations (changing mutex usage, better memory allocations, optimized bottlenecks, memory barriers etc).
The MariaDB documentation/knowledgebase:
has now 3685 articles about MariaDB and MySQL. Foundation employees added during 2014 223 new ones and did 6045 edits.

Some of the main new articles from us are:

Truncated by Planet PHP, read more at the original (another 4148 bytes)

Peter ZaitsevPercona Live 2015 conference sessions announced! (13.1.2015, 17:01 UTC)

Today we announced the full conference sessions schedule for April’s Percona Live MySQL Conference & Expo 2015 and this year’s event, once again at the Hyatt Regency Santa Clara and Santa Clara Convention Center, looks to be the biggest yet with networking and learning opportunities for MySQL professionals and enthusiasts at all levels.

Conference sessions will run April 14-16 following each morning’s keynote addresses (the keynotes have yet to be announced). The 2015 conference features a variety of formal tracks and sessions related to high availability, DevOps, programming, performance optimization, replication and backup. They’ll also cover MySQL in the cloud, MySQL and NoSQL, MySQL case studies, security (a very hot topic), and “what’s new” in MySQL.

The sessions will be delivered by top MySQL practitioners at some of the world’s leading MySQL vendors and users, including Oracle, Facebook, Google, LinkedIn, Twitter, Yelp, Percona and MariaDB.

Percona Live 2014 conference attendees Sessions will include:

  • “Better DevOps with MySQL and Docker,” Sunny Gleason, Founder, SunnyCloud
  • “Big Transactions on Galera Cluster,” Seppo Jaakola, CEO, Codership
  • “Database Defense in Depth,” Geoffrey Anderson, Database Operations Engineer, Box, Inc.
  • “The Database is Down, Now What?” Jeremy Tinley, Senior MySQL Operations Engineer,
  • “Encrypting MySQL data at Google,” Jeremy Cole, Sr. Systems Engineer, and Jonas Oreland, Software Developer, Google
  • “High-Availability using MySQL Fabric,” Mats Kindahl, Senior Principal Software Developer, MySQL Group, Oracle
  • “High Performance MySQL choices in Amazon Web Services: Beyond RDS,” Andrew Shieh, Director of Operations, SmugMug
  • “How to Analyze and Tune MySQL Queries for Better Performance,” Øystein Grøvlen, Senior Principal Software Engineer, Oracle
  • “InnoDB: A journey to the core III,” Davi Arnaut, Sr. Software Engineer, LinkedIn, and Jeremy Cole, Sr. Systems Engineer, Google, Inc.
  • “Meet MariaDB 10.1,” Sergei Golubchik, Chief Architect, MariaDB
  • “MySQL 5.7 Performance: Scalability & Benchmarks,” Dimitri Kravtchuk, MySQL Performance Architect, Oracle
  • “MySQL at Twitter – 2015,” Calvin Sun, Sr. Engineering Manager, and Inaam Rana, Staff Software Engineer, Twitter
  • “MySQL Automation at Facebook Scale,” Shlomo Priymak, MySQL Database Engineer, Facebook
  • “MySQL Cluster Performance Tuning – The 7.4.x Talk,” Johan Andersson CTO and Alex Yu, Vice President of Products, Severalnines AB
  • “MySQL for Facebook Messenger,” Domas Mituzas, Database Engineer, Facebook
  • “MySQL Indexing, How Does It Really Work?” Tim Callaghan, Vice President of Engineering, Tokutek
  • “MySQL in the Hosted Cloud,” Colin Charles, Chief Evangelist, MariaDB
  • “MySQL Security Essentials,” Ronald Bradford, Founder & CEO, EffectiveMySQL
  • “Scaling MySQL in Amazon Web Services,” Mark Filipi, MySQL Team Lead, Pythian
  • “Online schema changes for maximizing uptime,” David Turner, DBA, Dropbox, and Ben Black, DBA, Tango
  • “Upgrading to MySQL 5.6 @ scale,” Tom Krouper, Staff Database Administrator , Twitter

Of course Percona Live 2015 will also include several hours of hands-on, intensive tutorials – led by some of the top minds in MySQL. We had a post talking about the tutorials in more detail last month. Since then we added two more: “MySQL devops: initiation on how to automate MySQL deployment” and “Deploying MySQL HA with Ansible and Vagrant.” And of course Dimitri Vanoverbeke, Liz van Dijk and Kenny Gryp will once again this year host the ever-popular “Operational DBA in a Nutshell! Hands On Tutorial!

Yahoo, VMWare, Box and Yelp are among the industry leaders sponsoring the event, and additional

Truncated by Planet PHP, read more at the original (another 1637 bytes)

LinksRSS 0.92   RDF 1.
Atom Feed