Feed aggregator

While Domas may have rather effictively trolled the discussion with his post on howto configure table/user statistics (which gave me a good chuckle I do have to say), it’s at least incorrect for Percona Server as you have to enable the “userstat” server option :)

That being said, once enabled there are no extra configuration variables to think about. This is a huge advantage over configuring PERFORMANCE_SCHEMA - which has a total of THIRTY configuration options (31 if you include the global enable/disable option).

Some of these thirty odd configuration variables are only going to matter if you’re loading your own plugins, and even then, it’s probably only going to matter if they use the MySQL mutex implementations rather than, say, the standard pthread ones or even other synchronization primitives. It helps that the vast majority of non-InnoDB storage engines are dead. Go on – name one that’s in any form of usage (MyISAM doesn’t count – it’s effectively on death row).

This really makes me want to go and resurrect and finish the perf integration with Drizzle. The operating system provides a whole bunch of performance monitoring tools already, just expose them via SQL and be done with it.

In 2001 I created a PHP 4 web application framework from scratch as the backbone of a sophisticated application. Back then frameworks weren’t cool. Smarty templates were the hotness.

My framework had URL routing, templates with a capable templating syntax similar to mustache, loosely coupled and tightly cohesive object-oriented design, an elegant way to access the database without dumbing it down, and nicely separated business logic and presentation layers — among many other nice things you find in good frameworks. As the application grew more and more complex, the framework continued to serve well with only occasional enhancements.

I mention this because it illustrates that I’ve been aware of how to design maintainable systems for a long time. But the so-called MVC paradigm (model, view, controller) has never made sense to me.

The design goals make sense. See above. It’s just that I find MVC itself — the abstraction and implementation of those goals — to be unhelpful at best, on a day I’m being charitable. On a day I’m telling it like it is, I’ll just bluntly say MVC is the most confusing pile of acronym bulls**t I can think of.

Tell me, without knowing what MVC is, what would you guess the model represents? Controller? View? It would be bad enough if these were abstract terms like “node” or “resource,” but they’re not abstract. They hint at meaning, and then you discover that MVC doesn’t align with the hinted-at meaning. Instead, the M, V, and C represent concepts that are vague and hard to understand, and are kind of a stretch to mentally fit with the terms. This is worse than abstract, it’s misleading. It’s like bait-and-switch for someone who’s trying to grasp what it means.

I don’t know where MVC came from and I’m sure I won’t change my mind if I find out. I have a hard time believing that I’m the only one who wishes someone had formed a committee[1] to discuss what kind of names to represent the concepts. Especially since there are so many frameworks that abstract things slightly differently, because MVC apparently doesn’t suit their designers either, and thus it’s necessary to explain the relationship between Framework X’s view of the world, and how that loosely maps to the MVC view of the world. It becomes a sloppy comparison to a sloppy analogy.

Even more fun: some folks try to invent an MVC framework, like the original ASP.NET, and “get it wrong,” to disdainful criticism. Then they have to reinvent it, sometimes several times.

It’s kind of like the OSI 7-layer model, which everyone agrees doesn’t actually represent modern networking technology stacks cleanly, and yet everyone talks about the OSI layers. There’s a clean way to think about networking layers, but the OSI model ain’t it. Or NoSQL — the most unhelpful, undescriptive name for an … amorphous category of technologies or worldviews or design patterns or whatever… ever.

The lesson: great concepts that teach great truths, and could result in great advances in engineering due to a common mental model around which other things can be built, can be sidetracked by slapping an ill-fitting monicker or set of analogies on them. And sometimes we never seem to get another chance. NoSQL believers can’t seem to think of anything better than NoSQL as a name (and neither can I, as much as I deplore the NoSQL name). We’re stuck.

I’m sure my strong opinions on this are a) somewhat ignorant and b) not shared by everyone. Discuss! :-)

[1] Yes, I’m aware that awful things come from committees, like SQL. But I have faith that it would have been better than MVC.

It’s not uncommon to promote a server from slave to master. One of the key things to protect your data integrity is to make sure that the promoted slave is permanently disconnected from its old master. If not, it may get writes from the old master, which can cause all kinds of data corruption. MySQL provides the handy RESET SLAVE command. But as we’ll see, its behavior has changed along with the MySQL versions and it’s easy to shoot yourself in the foot if you use it incorrectly. So how do you safely disconnect a replication slave?

In short

• For MySQL 5.0 and 5.1, run STOP SLAVE, CHANGE MASTER TO MASTER_HOST='' and then RESET SLAVE.
• For MySQL 5.5 and 5.6, run STOP SLAVE and then RESET SLAVE ALL.
• For all versions, ban master-user, master-host and master-password settings in my.cnf, this may cause huge problems (it’s anyway no longer supported from MySQL 5.5).

If you want to know more details, please read on!

MySQL 5.0/5.1

First let’s consider MySQL 5.0 and 5.1. RESET SLAVE will remove the master.info and relay-log.info files as well as all the relay log files. This looks great, but does it ensure the replica is disconnected from its master?
Let’s try:

mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> reset slave; Query OK, 0 rows affected (0.03 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mysql_sandbox35302-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No [...]

This is not expected: instead of removing all settings, some of them are reset to default values. This means that if you run START SLAVE (or if it’s done automatically, for instance when restarting the server without the skip-slave-start option), replication may start again. But as the master position has been deleted, replication will restart at the beginning of the first available binary log, which is very likely to corrupt your data by reexecuting some queries.

Here’s a trick to make RESET SLAVE work as expected: use CHANGE MASTER TO MASTER_HOST='':

mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host=''; Query OK, 0 rows affected (0.02 sec) mysql> reset slave; Query OK, 0 rows affected (0.04 sec) mysql> show slave status\G Empty set (0.00 sec) mysql> start slave; ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Much better! If we try to restart replication, it fails. However, I don’t like the error message, specifically the ‘fix in config file’ part. What happens if we specify the master-user, master-password, master-host and master-port in the my.cnf file?

# cat my.cnf [...] master-user=rsandbox master-password=rsandbox master-host=127.0.0.1 master-port=35301 [...]

Let’s disconnect the slave:

mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host=''; Query OK, 0 rows affected (0.03 sec) mysql> reset slave; Query OK, 0 rows affected (0.03 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 35301 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mysql_sandbox35302-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No [...]

Connection settings are automatically restored, which makes disconnecting the replica impossible. And again, if you restart replication, it will read events from the first available binary log file on the master, which is probably not what you want. So never set master-xxx variables in my.cnf!

From MySQL 5.5

Starting with MySQL 5.5, the situation has slightly changed. First the master-xxx variables are no longer supported, which is a great improvement. But the RESET SLAVE statement also behaves differently:

mysql> stop slave; Query OK, 0 rows affected (0,01 sec) mysql > reset slave; Query OK, 0 rows affected (0,11 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 18675 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mysql_sandbox18676-relay-bin.000001 Relay_Log_Pos: 4 [...]

As stated in the documentation, the connection parameters are still held in memory. In any case, you will be able to restart replication, but again as no replication coordinate is specified, replication will start at the beginning of the first available binary log file, with all the nasty consequences we can imagine.

Even worse, the CHANGE MASTER TO MASTER_HOST='' trick no longer works:

mysql> stop slave; Query OK, 0 rows affected (0,01 sec) mysql> change master to master_host=''; ERROR 1210 (HY000): Incorrect arguments to MASTER_HOST

Fortunately, the documentation also specifies that we can use RESET SLAVE ALL to remove all replication-related configuration:

mysql> stop slave; Query OK, 0 rows affected (0,00 sec) mysql> reset slave all; Query OK, 0 rows affected (0,04 sec) mysql> show slave status\G Empty set (0,00 sec)

Very good! The command does work as expected without any additional tricks. As soon as you are aware of the difference between RESET SLAVE and RESET SLAVE ALL, disconnecting a replication slave is much easier with MySQL 5.5+.

The post Disconnecting a replication slave is easier with MySQL 5.5+ (RESET SLAVE vs. RESET SLAVE ALL) appeared first on MySQL Performance Blog.

Percona is glad to announce the release of Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL on April 17, 2013. Binaries are available from the downloads area or from our software repositories.

New Features:

• Percona XtraDB Cluster has implemented initial implementation of weighted quorum. Weight for node can be assigned via pc.weight option in the wsrep_provider_options variable. Accepted values are in the range [0, 255] (inclusive). Quorum is computed using weighted sum over group members.
• Percona XtraDB Cluster binary will now be bundled with the libjemalloc library. For RPM/deb packages, this library will be available for download from our repositories. Benchmark showing the impact of memory allocators on MySQL performance can be found in this blogpost.
• This release of Percona XtraDB Cluster has fixed number of foreign key and packaging bugs.

Bug Fixes:

• Fixed yum dependencies that were causing conflicts in CentOS 6.3 during installation. Bug fixed #1031427 (Ignacio Nin).
• In case the Percona XtraDB Cluster was built from the source rpm, wsrep revision information would be missing. Bug fixed #1128906 (Alexey Bychko).
• The method of generating md5 digest over tuples in a table with no primary key was not deterministic which could lead to a node failure. Bug fixed #1019473 (Seppo Jaakola).
• Percona XtraDB Cluster was built with YaSSL which could cause some of the programs that use it to crash. Fixed by building packages with OpenSSL support rather than the bundled YaSSL library. Bug fixed #1104977 (Raghavendra D Prabhu).
• Clustercheck script would hang in case the MySQL server on a node is hung. As a consequence clustercheck script would never fail-over that server. Bug fixed #1035927 (Raghavendra D Prabhu).
• High values in variables evs.send_window and evs.user_send_window could trigger cluster crash under high load. Bug fixed #1080539 (Teemu Ollakka).
• Standard MySQL port would be used when port number wasn’t explicitly defined in the wsrep_node_incoming_address. Bug fixed #1082406 (Alex Yurchenko).
• Dropping a non-existing temporary table would be replicated when TOI was used in wsrep_OSU_method variable. This bug was fixed for the case when DROP TEMPORARY TABLE statement was used, but it will still replicate in case DROP TABLE statement is used on a temporary table. Bug fixed #1084702 (Seppo Jaakola).
• In case two nodes in a 3-node cluster had to abort due to inconsistency, one wouldn’t correctly notify the surviving node which would lead to surviving node to loose the primary component and cause subsequent downtime. Bug fixed #1108165 (Alex Yurchenko).
• In some cases non-uniform foreign key reference could cause a slave crash. Fixed by using primary key of the child table when appending exclusive key for cascading delete operation. Bug fixed #1089490 (Seppo Jaakola).
• Parallel applying would fail in case mixed CHAR and VARCHAR columns would be used in foreign key definitions. Bug fixed #1100496 (Seppo Jaakola).
• Debian packages included the old version of innotop. Fixed by removing innotop and its InnoDBParser Perl package from source and Debian installation. Bug fixed #1032139 (Alexey Bychko).
• The mysqld_safe script would fail to retrieve the Galera replication position on Ubuntu 10.04, because the different shell was used. Bug fixed #1108431 (Alex Yurchenko).
• Cascading foreign key constraint could lead to unresolved replication conflict and leave a slave hanging. Bug fixed #1130888 (Seppo Jaakola).
• If MySQL replication threads were started before running wsrep recovery, this would lead to memory corruption and server crash. Bug fixed #1132974 (Seppo Jaakola).
• Conflicting prepared statements in multi-master use case could cause node to hang. This was happening due to prepared statement execution loop, which does not honor wsrep status codes correctly. Bug fixed #1144911 (Seppo Jaakola).
• State Snapshot Transfer with Xtrabackup would fail if the tmpdir was specified more than once in the MySQL configuration file (my.cnf). Bugs fixed #1160047 and #1086978 (Raghavendra D Prabhu).
• Donor node would run XtraBackup indefinitely when xtrabackup tmpdir was set up on tmpfs. Bug fixed #1086978 (Alex Yurchenko).
• Issues with compiling Galera on the ARM architecture has been fixed. Bug fixed #1133047 (Alex Yurchenko).
• Upstream bugfix for bug #59354 triggered a regression that could cause transaction conflicts. Bug fixed #1158221 (Seppo Jaakola).
• Galera builds would fail when they were built with the new boost library. Bug fixed #1131736 (Alex Yurchenko).
• Folder lost+found wasn’t included in the rsync SST filter, this caused the SST failure due to insufficient privileges. Fixed by excluding lost+found folder if found. Bug fixed #1154095 (Alex Yurchenko).
• If variable innodb_thread_concurrency has been defined to throttle InnoDB access, and work load contained DDL statements, a cluster node could remain hanging for unresolved MDL conflict. Fixed by adding a new method to cancel a thread waiting for InnoDB concurrency. Bug fixed #1155183 (Seppo Jaakola).
• Handling of the network issues in Galera has been improved. Bug fixed #1153727 (Teemu Ollakka).
• Fixed the wrong path in the /etc/xinetd.d/mysqlchk script. Bugs fixed #1000761 and #1132934 (Raghavendra D Prabhu).
• When upgrading the Percona-XtraDB-Cluster-server package, /usr/bin/clustercheck script would get overwritten, and any changes (such as username and password) would be lost. Bug fixed #1158443 (Raghavendra D Prabhu).
• In case CREATE TABLE AS SELECT statement was running in parallel with the DDL statement on the selected table, in some cases first statement could be left hanging. Bug fixed #1164893 (Seppo Jaakola).
• Galera builds would fail when gcc 4.8 was used. Bug fixed #1164992 (Alex Yurchenko).
• Percona-XtraDB-Cluster-galera package version number didn’t match the wsrep_provider_version one. Bug fixed #1111672 (Alexey Bychko).
• Only rpm debug build was available for Percona XtraDB Cluster, fixed by providing the deb debug build as well. Bug fixed #1096123 (Ignacio Nin).

Other bug fixes: bug fixed #1162421 (Seppo Jaakola), bug fixed #1093054 (Alex Yurchenko), bug fixed #1166060 (Teemu Ollakka), bug fixed #1166065 (Teemu Ollakka).

Based on Percona Server 5.5.30-30.2 including all the bug fixes in it and on Codership wsrep API 5.5.30-23.7.4, Percona XtraDB Cluster 5.5.30-23.7.4 is now the current stable release. All of Percona’s software is open-source and free. Release notes for Percona XtraDB Cluster 5.5.30-23.7.4 are available in our online documentation.

We did our best to eliminate bugs and problems, but this is a software, so bugs are expected. If you encounter them, please report them to our bug tracking system.

UPDATE[18-04-2013]: There was a RPM packaging regression introduced with the fix for bug #710799. This regression only affected clean RPM installations and not upgrades. We have pushed the fixed packages to the repositories. Bug fixed #1170024.

The post Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available appeared first on MySQL Performance Blog.

I’m really glad to come back from vacation to discover that we’ve been accepted into GSoC 2013. If you’re interested in being a student or mentoring, head over to: Drizzle Database in GSOC 2013

I’m scheduled to talk about Go a couple times in the upcoming weeks.

The first is May 4th in Arlington at A Day Of Foster.ly. I’ll be a participant in a panel titled Add To Your Programming Toolkit: Languages You Should Know About (Erlang, Clojure, R, Go, etc…). I’m talking about Go, naturally.

The second May 7th is at my local technology guild, the Neon Guild. I’m going to be giving a “gentle introduction” to Go. I’ve been programming in Go for less than a year, so my first impressions are still fresh. If you are in the Charlottesville area and you haven’t been to a Neon Guild meeting, do yourself a favor and show up. It’s free and no RSVP is required. Description:

It seems that every time I tell someone we’ve chosen Go as our primary language at VividCortex, they respond with something like “I’ve heard about Go! Neat! What is it like? Why did you choose it? Are you happy with it?” The answer is that Go is a fascinating language that’s working great for us. After many years of programming in everything from C to Java to C# to Perl to LISP to Shell, I’m pretty much in love with Go. There are particular reasons I chose it for our special use case, but beyond just niche considerations, it’s working great as a general-purpose, high-performance, very productive systems language. I’ll try to contain my enthusiasm and tell a story about evaluating it, learning it, and then learning it more deeply.

I hope to see you at one of these events. If you see me, wave and introduce yourself, and give me a business card so I can stalk connect with you on LinkedIn!

When a query uses a construct like

SELECT ... FROM a JOIN b ON (...) WHERE a.c1 = X OR b.c2 = Y

execution will inevitably degrade as the dataset grows.

The optimiser can choose to use an index merge when dealing with two relevant indexes over a single table, but that’s obviously of no use in this scenario as the optimiser has to choose which table to access first. And regardless of which table is accessed first, the other one might yield a result. Thus the query will never be efficient.

The real answer is that the query construct is wrong, a JOIN is used inappropriately. The correct approach for this type of query is using a UNION:

SELECT ... FROM a WHERE a.c1 = X UNION [ALL] SELECT ... FROM b WHERE b.c2 = Y

This mistake occurs relatively often, because while on the one hand people try to reduce the number of queries necessary to achieve their objective, they are (somewhat) familiar with JOINs and either don’t know about UNION or use it so rarely that they don’t think of it when the question calls for its use. So, this is your reminder

li, ol { font-size: 12px !important }

Today’s blog post diving into the waters of the MySQL buffer pool is a cross-post from Groupon’s engineering blog, and is Part 1 of 2. Thank you to Kyle Oppenheim at Groupon for contributing to this project and post. We’ll be posting Part 2 on Thursday. I’ll be at the Percona Live MySQL Conference and Expo next week in Santa Clara, California so look for me there – I’d love to connect and talk more about MySQL buffer pools or anything else that’s on your mind!

There are numerous solutions for MySQL high availability. Many rely on MySQL’s asynchronous replication to maintain a warm standby server which is flipped into service if the active master server has an issue. At Groupon, our standard MySQL database configuration follows this active/passive pattern. There is a virtual IP that points to the active server of a pair. The passive server has mysqld running and replicating from the active master. In theory, failing over is the simple matter of moving the virtual IP. In practice, it’s slightly more complicated. To manage this process, we use a tool co-developed with Percona.

“Warm standby server”? Did you catch that? What does that mean? In most of these high-availability solutions it means that mysqld is running on the standby server and that replication is not lagging. Unfortunately, that’s often not sufficient for failover during peak traffic. The standby server does not process query traffic except for replication. The buffer pool and adaptive hash index on the standby server will not have recently accessed pages. When it starts handling queries after failover, the lower cache hit rates can lead to outages. Specifically, at Groupon, our servers would be heavily I/O bound after failover as the buffer pool pages were loaded from disk.

Replaying Queries

Working with Groupon, we have developed a solution to keep the standby server’s caches hot. (See my Fosdem 2013 slides for information about discarded designs and benchmarks.)

First, we set long_query_time to 0 in order to log every query. (See part two for handling massive slow log volume.) The slow logs are served, via HTTP, by mysql_slowlogd. This daemon is similar to running tail -f slow.log, except that it knows how to follow the log stream across log rotation events. On the standby server, the logs are replayed with Percona Playback by streaming the slow log from the active server.

wget -q -O - http://master_server:3307/slow | percona-playback --mysql-host 127.0.0.1 --mysql-username playback --mysql-password PaSSwOrd --mysql-schema schema_name --query-log-stdin --dispatcher-plugin thread-pool --thread-pool-threads-count 100 --session-init-query \"set innodb_fake_changes=1\" > /var/log/playback.log 2>&1 &

Our awesome development team added a few features to Percona Playback to make it work better for this use case. You will need version 0.6 or later to get these features. Be aware that playback output is really verbose, in production, most likely you want it to redirect to /dev/null, and only have a log file for debugging purposes.

1. Streaming logs from stdin Percona Playback now supports the –query-log-stdin command-line option for accepting a never-ending stream of queries to playback.
2. Read-only playback Using the –session-init-query command-line option, we set the option innodb_fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting the data on the standby server. You will need Percona Server in order to use innodb_fake_changes.
3. Thread pool Percona Playback added a connection pool option via –dispatcher-plugin-thread-pool that will allow connection reuse. This is necessary when running a large stream of queries.

Benchmarks

We benchmarked with slow query logs captured from our production systems. We restored a production database backup to our test database so that our test database was consistent before applying the captured query traffic. This is an important step because update statements that match no rows or insert statements that have duplicate key errors may be faster than an actual database write.

The slow logs were split into chunks, each containing roughly 1M queries. We warmed the cold database with the first chunk and replayed the second chunk after the warmup.

The y axis is logarithmic, so the difference between the IO usage is 2 orders of magnitude. All graphs looked like this (we did 39 measurements), the next graph shows chunk 4’s workload warmed up with chunk 3.

The result is similar for every single graph, each chunk warmed up the buffer pool for the next one.

As an additional experiment we tried replaying the same chunk again. We expected everything to be cached if we warmed the cache with the exact same data. All the graphs from such self-warming experiments look like this one. The green part of the graph lines up with the blue part.

Check back Thursday for Part 2!

The post Is your MySQL buffer pool warm? Make it sweat! appeared first on MySQL Performance Blog.

The Micron P320h SSD is an SLC-based PCIe solid-state storage device which claims to provide the highest read throughput of any server-grade SSD, and at Micron’s request, I recently took some time to put the card through its paces, and the numbers are indeed quite impressive.

For reference, the benchmarks for this device were performed primarily on a Dell R720 with 192GB of RAM and two Xeon E5-2660 processors that yield a total of 32 virtual cores. This is the same machine which was used in my previous benchmark run. A small handful of additional tests were also performed using the Cisco UCS C250. The operating system in use was CentOS 6.3, and for the sysbench fileIO tests, the EXT4 filesystem was used. The card itself is the 700GB model.

So let’s take a look at the data.

With the sysbench fileIO test in asynchronous mode, read performance is an extremely steady 3202MiB/sec with almost no deviation. Write performance is also both very strong and very steady, coming in at a bit over 1730MiB/sec with a standard deviation of a bit less than 13MiB/sec.

 

When we calculate in the fact that the block size in use here is 16KiB, these numbers equate to over 110,000 write IOPS and almost 205,000 read IOPS.

When we switch over to synchronous IO, we find that the card is quite capable of matching the asynchronous performance:

Synchronous read reaches peak capacity somewhere between 32 and 64 threads, and synchronous write tops out somewhere between 64 and 128 threads. The latency numbers are equally impressive; the next two graphs show 95th and 99th-percentile response time, but there really isn’t much difference between the two.

At 64 read threads, we reach peak performance with latency of roughly 0.5 milliseconds; and at 128 write threads we have maximum throughput with latency just over 3ms.

How well does it perform with MySQL? Exact results vary, depending upon the usual factors (read/write ratio, working set size, buffer pool size, etc.) but overall the card is extremely quick and handily outperforms the other cards that it was tested against. For example, in the graph below we compare the performance of the P320h on a standard TPCC-MySQL test to the original FusionIO and the Intel i910 with assorted buffer pool sizes:

 

And in this graph we look at the card’s performance on sysbench OLTP:

It is worth noting here that EXT4 outperforms XFS by a fairly significant margin. The approximate raw numbers, in tabular format, are:

-EXT4XFS13GiB BP22000750025GiB BP17000900050GiB BP210001100075GiB BP2500015000100GiB BP3100019000125GiB BP3600025000

In the final analysis, there may or may not be faster cards out there, but the Micron P320h is the fastest one that I have personally seen to date.

The post Testing the Micron P320h appeared first on MySQL Performance Blog.

The Percona Live MySQL Conference and Expo begins next Monday and runs April 22-25, 2013. Attendees will see great keynotes from leaders in the industry including representatives from Oracle, Amazon Web Services, HP, Continuent, and Percona. They can also participate in thought provoking Birds of a Feather sessions on Tuesday night and the Wednesday night Community Networking Reception will be fun and entertaining with the presentation of the Community Awards and the Lightning Talks.

If you cannot attend the entire Percona Live MySQL Conference but want to take advantage of the keynotes, BOFs, and Community Networking Reception, I’m pleased to offer a limited number of $5 Expo Only passes. Use discount code “KEY” when registering for the Percona Live MySQL Conference. Hurry, though, as only 100 passes are available at this price! This discount is only available for new ticket purchases. The regular price for Expo Only passes is $50 prior to the conference and $100 onsite.

I’m personally looking forward to Matt Aslett’s keynote on Thursday morning of the Percona Live MySQL Conference. Matt is the Research Director, Data Management and Analytics for 451 Research. The description for his talk, “The State of the MySQL Ecosystem“, summarizes what we’re all coming to understand:

“It is now over three years since Oracle acquired MySQL along with Sun Microsystems. Fears for the open source database’s survival appear to have been misplaced as Oracle has increased investment in MySQL development. At the same time, a thriving ecosystem of potential alternatives and complementary products has emerged to provide MySQL users with greater choice in terms of both functionality and support. As a result of that choice, we are seeing the increasing independence of the ecosystem of MySQL-related products and services from MySQL itself – both in terms of a commercial product, and also a development project. The continued maturity of vendors such as Percona and SkySQL, as well as the formation of the MariaDB Foundation, has the potential to accelerate that trend. The MySQL ecosystem is far from fragmenting, but 451 Research’s updated survey of database users indicates that the center of gravity has begun to shift towards an increased state of independence.”

Mirroring the growing diversity in the MySQL ecosystem, Percona Live MySQL Conference attendees have an opportunity to hear from a variety of server projects in both the keynotes and breakout sessions including presentations on Oracle MySQL, Percona Server, and MariaDB during the Percona Live MySQL Conference.

Percona Live MySQL Conference Keynotes

Tuesday

• Peter Zaitsev, Co-founder and CEO, Percona
  “Our Ever-Evolving MySQL Ecosystem”
• Simone Brunozzi, Senior Technology Evangelist, Amazon Web Services
  “Databases in the Cloud: Present and Future”
• Tomas Ulin, Vice President of MySQL Engineering, Oracle
  Keynote Topic: “Driving MySQL Innovation”

Wednesday

• Robert Hodges, CEO, Continuent
  “How MySQL Can Thrive in the World of Massive Data Hype”
• Keynote Panel from Amazon Web Services, Continuent, HP and Percona
  “The Impact of MySQL 5.6 and Its Future in the Cloud”

Thursday

• Matt Aslett, Research Manager, Data Management and Analytics, 451 Research
  “The State of the MySQL Ecosystem”
• Brian Aker, Fellow, HP Cloud Division
  “The MySQL Ecosystem and Cloud Computing”

Breakout Sessions on Oracle MySQL

• Sunny Bains, Senior Principal Software Engineer, Oracle
  Topic: “MySQL 5.6: What’s New in InnoDB”
• Dmitri Kravtchuk, MySQL Performance Architect, Oracle
  Topic: “MySQL 5.6: Performance Benchmarks, Tuning, and ‘Best’ Practices”
• Luís Soares, Senior Software Engineer, Oracle
  Topic: “MySQL 5.6: Redefining Replication”

Breakout Sessions on Percona Server and Related Projects

• Stewart Smith, Director of Server Development, Percona
  Topic: “Percona Server 5.6”
• Jay Janssen, Consulting Lead, Percona
  Topic: “Migrating to Percona XtraDB Cluster”
• Mark Atwood, Vipul Sabhaya, and Jim Cooley, HP
  Topic: “Using Percona Server as Database-as-a-Service on OpenStack”

Breakout Sessions on MariaDB

• Monty Widenius and Colin Charles, Chief Evangelist for MariaDB for Monty Program
  Topic: “MariaDB 10.0 & What’s New With the Project”
• Sergei Petrunia, Optimizer Developer, and Colin Charles, Chief Evangelist, MariaDB, for Monty Program Ab
  Topic: “MariaDB Cassandra Interoperability”
• Monty Widenius and Sergei Golubchik, VP Architecture, Monty Program Ab
  Topic: “Replication Changes in MariaDB”
• Sergei Golubchik, VP Architecture, Monty Program Ab
  Topic: “Storage Engines and Other Plugins: What’s New?”

The Percona Live MySQL Conference includes a Diamond Sponsor Keynote panel on Wednesday morning on the “Impact of MySQL 5.6 and its Future in the Cloud”. Moderated by me, the panel will include MySQL industry leaders Simone Brunozzi, senior technology evangelist at Amazon Web Services; Robert Hodges, CEO of Continuent; Brian Aker, fellow, HP Cloud Division; and Peter Zaitsev, co-founder and CEO of Percona. The discussion will focus on MySQL 5.6 and how MySQL must evolve if it is to remain competitive in the new world order of the cloud and big data.

If you can join us in Santa Clara for the Percona Live MySQL Conference and Expo, use discount code “Percona15″ to receive 15% off your full conference pass. If you can only make it to the keynotes, BOFs, or Community Networking Reception, use discount code “KEY” for a $5 Expo Only pass. And if you cannot make it this year, watch this blog following the conference and we’ll announce when and where the keynote recordings and breakout session slide decks can be found.

The post Keynotes, BOFs, and the Community Networking Reception at Percona Live MySQL Conference and Expo appeared first on MySQL Performance Blog.

In addition to the problem with trx_list scan we discussed in Friday’s post, there is another issue in InnoDB transaction processing that notably affects MySQL performance – for every transaction InnoDB creates a read view and allocates memory for this structure from heap. The problem is that the heap for that allocation is destroyed on each commit and thus the read view memory is reallocated on the next transaction.

There are two aspects of this problem:

1) memory allocation is an costly operation and if memory allocator has scalability problems (like allocator from glibc) this will notably slowdown MySQL-transaction creation and many threads will get stuck on glibc/kernel syscalls, which will in turn result in contention on kernel_mutex (trx_sys->mutex in 5.6), as memory allocation occurs under that mutex. See an example of such an issue. Related bugs: BUG#54982, BUG#49169.

2) memory allocation for read-view structure is not a direct malloc() call, but rather goes through the InnoDB heap layer – so InnoDB allocates heap area and then creates requested block(s) there. That optimization helps to avoid fragmentation in case of many small allocations and allows to free all blocks from specific heap at once. But in the case when we need memory only for a single block this 2 layers approach is quite inefficient and in some cases can be the reason for notable MySQL performance drop.

Now in Percona Server, for each connection we use a preallocated read view structure, reuse that memory during the entire connection lifetime and free it at disconnect. If some transactions require a larger amount of memory – we just reallocate memory to fulfill it needs.

To demonstrate the difference we have run sysbench POINT_SELECT test for glibc and jemalloc allocators.

Observations:

= MySQL 5.5.30
- throughput of MySQL 5.5.30 with glibc is limited first of all by inefficiency of transaction list handling (see our previous post) and also by bad scalability of glibc malloc itself
- jemalloc helps MySQL 5.5.30 to fix issues with malloc scalability but still scanning of the transaction list
causes performance drop

= MySQL 5.6.10
- in autocommit mode 5.6.10 has no problem with transaction list scanning (due to the read-only transactions optimization), but it still allocates/frees memory for read view structure and that causes drops at high threads with glibc. Jemalloc helps to solve that.

= Percona Server 5.5.30-30.2
- both issues are solved in our recent release and such we have almost no difference in results between runs either with glibc or jemalloc

The post Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2 appeared first on MySQL Performance Blog.

One major problem in terms of MySQL performance that still stands in the way of InnoDB scalability is the trx_list scan on consistent read view creation. It was originally reported as a part of MySQL bug #49169 and can be described as follows. Whenever a connection wants to create a consistent read, it has to make a snapshot of the transaction states to determine which transactions are seen in the view later. To this end, InnoDB scans trx_list (i.e. the list of currently open transactions) and copies IDs of transactions that have not yet been committed at the current point in time, and thus should not be visible in the consistent read. For the REPEATABLE_READ isolation level, the snapshot is created on the first SELECT for each transaction. For lower isolation levels it is created for each SELECT, even within the same transaction.

Why is that a major problem for MySQL scalability? The scan is performed under kernel_mutex (or trx_sys->mutex in 5.6) and that is one of the top reasons for contention on that mutex.

Percona Server as of version 5.5.30-30.2 significantly reduces the impact of the list scan by replacing it with more efficient operations. In a nutshell, it maintains an array of active transactions which we call the trx descriptors array. Creating a snapshot of transaction states is then a matter of copying a relatively small memory block which, depending on many factors, can be from few times to orders of magnitudes faster than a list scan.

You may be curious if MySQL 5.6 brings any notable improvements in this area. It does, but only for a special case. Read-only transactions, while a great optimization in itself, applies only to the following kinds of transactions:

• MySQL transactions started with START TRANSACTION READ ONLY
• MySQL transactions created by a non-locking SELECT statement in the autocommit mode.

The relevant optimization in MySQL 5.6 boils down to the fact that read-only transactions are not added to the list of open transactions. Since they don’t modify any data, it is irrelevant whether a consistent read view sees them or not.

The main limitation is obvious: if you want to benefit from that optimization, you basically want all or most of your transactions to be read-only. Sometimes you may also want to modify your applications to issue START TRANSACTION READ ONLY appropriately. Which is not always applicable or possible in practice.

The “trx descriptors” optimization in Percona Server attacks this problem from a different (and a wider!) angle. It requires neither read-only server nor rewriting application queries. Furthermore, it can be combined with the read-only optimization in MySQL 5.6 to achieve even better scalability while removing the read-only limitations.

Let’s take a look at the following benchmark:

This is the so-called QPS-mode POINT_SELECT benchmark, i.e. all queries are single-statement SELECT statements in autocommit. Which is the perfect case for read-only optimization in MySQL 5.6 and as you can see MySQL 5.6 shines in this benchmark. This is also the case that Oracle seems to focus the most in their MySQL 5.6 benchmarks. However, the “trx descriptors” optimization in Percona Server 5.5 is fairly close in terms of scalability, while the cost of the trx_list scan in MySQL 5.5 basically kills throughput on high concurrency.

The picture changes dramatically when the read-only optimization in MySQL 5.6 does not kick in. The default sysbench POINT_SELECT mode benchmark does PK lookup SELECT queries wrapped into regular (i.e. not marked as read-only) transactions.

In this case MySQL 5.6 scalability goes back to the good old MySQL 5.5 times and we see identical results with both, i.e. there’s a notable drop in throughput starting from 256 threads.

But Percona Server’s scalability is the same as in the previous benchmark. The overhead of maintaining the descriptors array is negligible as compared to the list scan and it provides stable throughput even at 4096 concurrent connections.

So both the general “trx descriptors” optimization introduced in Percona Server 5.5.30-30.2 and the special-case “read-only transactions” optimization in MySQL 5.6 are nice ways to improve scalability in highly concurrent workloads. We also have plans to combine them in the future releases of Percona Server 5.6, which is currently in the alpha stage.

We are going to blog about other MySQL performance improvements introduced in our latest Percona Server release, stay tuned.

The post trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2 appeared first on MySQL Performance Blog.

If you are looking for a class that is designed to jump-start your knowledge on MySQL 5.6 features, a class that provides hands-on labs, and a class that shows various migration methods – look no further.

We have been hard at work building a new class to ensure you have the knowledge and skills needed to verify your applications, and plan for the migration to MySQL 5.6. The class is called Moving to MySQL 5.6 and is a 2-day workshop.

The Moving to MySQL 5.6 workshop is being offered over the summer in numerous European countries and throughout the United States. Our goal is to provide you with the most up to date knowledge on MySQL 5.6, show you how to verify your application, and plan for a successful migration.

You may also want to check out our other MySQL training events. All of our workshops have been updated to MySQL 5.6–so you know you will be learning about the latest MySQL features and best practices. Just go to Percona Training for the full list of upcoming workshops. If you use discount code mpb10 you can save 10% when you register.

Last, I would be remiss if I did not remind you that is is not too late to purchase tickets for Percona Live MySQL Conference and Expo Community in Santa Clara, California, April 22-25. We have discounts available, just drop us line and we can help you out.

 

 

The post Moving to MySQL 5.6? We can help appeared first on MySQL Performance Blog.

In preparing for this month’s Percona Live MySQL Conference and Expo, I’ve been reminiscing about the annual MySQL User Conference’s history – the 9 times it previously took place in its various reincarnations – and there are a lot of good things, fun things to remember.

2003 was the year that marked the first MySQL user conference independently organized by MySQL AB. It was called the “MySQL Users Conference” and took place at the Double Tree hotel near the San Jose airport with an estimated 500-600 people attending. Monty and David kicked-off the show with keynotes and I recall one of them saying something like, “12 full-time engineers are working on MySQL server.” The MySQL Certification program was announced. MySQL 4.0.12 was just released as its first GA release in the MySQL 4.0 series, and for the first and only time in a MySQL version you could buy MySQL in a box with a CD inside, the same way you’d have bought any other software at the time. MySQL 4.1.0 was also available as an early alpha.

2004 was the second year the event was independently organized by MySQL AB. It took place in Orlando, Florida at the Peabody hotel. It was a fun place to visit though it attracted a smaller crowd and this is why I think the conference never left California again afterward. Partnership with SAP was hot at this event and MaxDB database looked like it had a solid future. MySQL Cluster and MySQL Press were announced during this event; “High Performance MySQL” 1st edition written by Jeremy and Derek was also available. MySQL 4.0.18 was GA and 4.1.1 as alpha – perhaps the smallest-ever change in MySQL versions for the year.

2005 was the first year O’Reilly partnered with MySQL to put on an amazing conference with O’Reilly’s style of mixing fun with business. The conference name changes to “MySQL Conference and Expo” that year and moves to its current location at the convention center in Santa Clara, California. MySQL celebrated its 10th birthday at the conference. Some 1,300 reported to have attended. This is perhaps the time of the brightest hopes for MySQL to date at the time, and everyone had a “lets change the world” mentality. MySQL 4.1.11 was available as GA at the time on the conference and a lot of hopes were pinned on MySQL 5.0, which was supposed to target the enterprise customer, and which was available as non-GA 5.0.4 version at the time of event.

2006 was the last year that I was attending the conference as a MySQL employee. I would attend all others, too, but now as part of Percona. It was the conference following the first shock to the MySQL Ecosystem – Oracle acquired Innodb in October of 2005. It was not very clear what that would mean for the MySQL ecosystem. A lot of focus was placed on the storage engine API diminishing importance of the Innodb storage engine. The Falcon storage engine was announced as a potential Innodb successor. A “MySQL Network” commercial subscription product was announced in what later evolved into “MySQL Enterprise,” and MySQL 5.0 is finally available as GA with version 5.0.20a… MySQL 5.1 is in development (5.1.9)

2007 is the year when the “State of the Dolphin” previously delivered by Monty and David is gone, signaling some early tensions… the State of MySQL AB is delivered by Marten Mickos. It is truly a year of storage engines, focused on showing what is on top is what really matters. Falcon, SolidDB, NitroSecurity, PBXT, StorageEngine for S3 and IBM DB2 integration were all present at this show. Marten reports revenues of almost $50 million in 2006, preparing for an IPO, and MySQL 6.0 was to ship the next year. MySQL releases are split between the MySQL Community, and MySQL Enterprise is released with different versions on different schedules. MySQL 5.0.37 GA and 5.1.17 nonGA were the latest version at the conference.

2008 Here comes the second shock to the MySQL ecosystem: No IPO. MySQL is now owned by Sun Microsystems. Monty goes public with his critique of MySQL Management. Monty is working on the Maria storage engine to replace Innodb for a number of use cases. The first “open core” plans are announced for MySQL Server – the online backup in MySQL 6.0, it’s to ship in commercial version only. An Innodb Plugin is announced and will be separate from MySQL Server, providing a more scalable Innodb version with more features. Gearman and MemcacheD storage engine plugins are available for MySQL. Facebook mentions they have over 1,800 MySQL Servers. MySQL Enterprise provides Monthly Rapid Updates [MRU] while Community MySQL versions updated every quarter or so. MySQL 5.0.51a is GA and 5.1.24 is nonGA

2009 The first and the only conference organized by Sun and O’Reilly from the ground up. The third shock comes to the MySQL ecosystem when Oracle announces the purchase of Sun during the conference. Both Monty and Marten are no longer with the company (having left in February), and the State of the Dolphin talk is back but now delivered by Karen Padir, the new manager responsible for MySQL business at Sun. A lot of Sun hardware and Solaris focused talks this year. It is the first year that the Drizzle project participates in the conference. Percona runs Percona Performance Conference as a free event going concurrently with the conference and announced Percona XtraDB as improved and extended Innodb Plugin version, MySQL 5.1 is FA with 5.1.34 and MySQL 5.4.0 is in a nonGA version. The MySQL Enterprise model is changed… now MySQL Community versions are released monthly and Enterprise versions come in form of Quarterly Service Packs [QSP]

2010 The deal between Oracle and Sun is now closed after overcoming a number of bottlenecks, including strong opposition by Monty. There is a lot of FUD and Drama over the conference, with the conference’s future itself now uncertain. The State of the Dolphin is now delivered by Edward Screven, the new boss of the MySQL business, now at Oracle. Monty changes his focus from Maria (storage engine) to MariaDB (complete MySQL replacement). There is a lot of “MySQL Community” focused keynotes. Good coverage of MySQL Alternatives including keynotes for Drizzle and MariaDB. MySQL 5.5 replaced MySQL 5.4 as next planned GA release. Percona releases GA version of Percona Xtrabackup – open source alternative to Innodb Hot backup. MySQL 5.1.46 is GA and MySQL 5.5.4 is not GA at the time of the conference.

2011 The conference is now independently organized by O’Reilly with Oracle providing a keynote and a few sessions. The conference is extended to include a lot of other databases and technologies in NoSQL and BigData space – PostgreSQL, EnterpriseDB, Hadoop,Hbase, MongoDB, CouchBase providing compliments and alternatives to MySQL. The MySQL Community awards are presented by Monty and David in the conference keynote. Percona XtraDB becomes Percona Server and is now a full-scale MySQL alternative and not just replacement for the Innodb Plugin. MySQL 5.5 is now GA with no more funky version differences for MySQL Enterprise, just certain features available only in Enterprise edition as plugins. Early preview of plans for MySQL 5.6 more in the form of a code drop than promises what the final version will contain. MySQL 5.5.11 is GA and MySQL 5.6.2 is nonGA.

2012 marks the first year the annual conference is organized by Percona, with technical content selected by an independent committee. The conference is refocused back on MySQL and the needs of the MySQL community covering other technologies, as it is relevant to MySQL users. MySQL, MariaDB, Percona Server, Drizzle are all well represented at the conference. No talks from Oracle. Marten Mickos is back with a keynote, “Making LAMP a Cloud.” He’s now CEO of Eucalyptus. Percona announces Percona XtraDB Cluster as GA – the new approach to MySQL High Availability in and out of the Cloud. MySQL 5.5.23 is GA and 5.6.5 is nonGA at the time of the conference.

The event’s 10th anniversary begins in less than two weeks. We’re yet to make history about what will be remembered the most about it. It’s great so see momentum going stronger and strong, and I’m very happy to see the very broad participation by the MySQL ecosystem – with Oracle coming back to the conference, providing a keynote from Tomas Ulin, vice president of Oracle’s MySQL engineering team, along with some technical talks directly from the Oracle MySQL engineers who brought us MySQL 5.6. MariaDB and Percona products are also very well covered at this year’s Percona Live MySQL Conference and Expo.

Forgot to register ? You can do it now with “Peter15″ discount code and get 15% off.

P.S I also would very much like to know what memorable moments do you have from you attending MySQL Conference over last 10 years. Please leave comment and share your experiences! Thank you.

Additionally, I’d like to offer readers the chance to win a free pass to this year’s Percona Live MySQL Conference and Expo in Santa Clara, Calif., April 22-25. Share a memory you have from past MySQL user conferences on Twitter – directing it to @Percona and with hashtag #mysqlmemories. We’ll pick a winner at random next Tuesday.Good luck!

The post 10 years of MySQL User Conferences appeared first on MySQL Performance Blog.

Percona Server for MySQL version 5.5.30-30.2

Percona is glad to announce the release of Percona Server for MySQL 5.5.30-30.2 on April 10, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.30, including all the bug fixes in it, Percona Server 5.5.30-30.2 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.30-30.2 milestone at Launchpad.

New Features:

• Percona Server for MySQL has implemented priority connection scheduling for the Thread Pool. (Alexey Kopytov)
• Percona Server for MySQL will now be shipped with the libjemalloc library. Benchmark showing the impact of memory allocators on MySQL performance can be found in this blogpost. (Ignacio Nin)
• This release of Percona Server for MySQL has fixed a number of performance bugs. (Alexey Kopytov)
• Drop table performance feature has been removed and its controlling variable innodb_lazy_drop_table has been deprecated. Feature has been removed because the upstream DROP TABLE implementation has been improved. (Laurynas Biveinis)

Bugs Fixed:

• Due to parse error in the percona-server.spec Percona Server rpm packages couldn’t be built on RHEL 5 and CentOS 5. Bug fixed #1144777 (Ignacio Nin).
• When mysqldump was used with --innodb-optimize-keys option it produced invalid SQL for cases when there was an explicitly named foreign key constraint which implied an implicit secondary index with the same name. Fixed by detecting such cases and omitting the corresponding secondary keys from deferred key creation optimization. Bug fixed #1081016 (Alexey Kopytov).
• Percona Server for MySQL was built with YaSSL which could cause some of the programs that use it to crash. Fixed by building packages with OpenSSL support rather than the bundled YaSSL library. Bug fixed #1104977 (Ignacio Nin).
• Running the DDL statement while variable innodb_lazy_drop_table was enabled could cause assertion failure. Bugs fixed #1086227 and #1128848 (Laurynas Biveinis).
• Fixed yum dependencies that were causing conflicts in CentOS 6.3 during installation. Bugs fixed #1031427 and #1051874 (Ignacio Nin).
• The log tracker thread was unaware of the situation when the oldest untracked log records are overwritten by the new log data. In some corner cases this could lead to assertion errors in the log parser or bad changed page data. Bug fixed #1108613 (Laurynas Biveinis).
• Ported a fix from MariaDB for the upstream bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on an existing table could wait on a metadata lock instead of failing or returning immediately if there is a transaction that executed a query which opened that table. Bug fixed #1127008 (Sergei Glushchenko).
• Fix for bug #1070856 introduced a regression in Percona Server for MySQL 5.5.28-29.3 which could cause a server to hang when binary log is enabled. Bug fixed #1162085 (Alexey Kopytov).
• Fixed upstream bug #49169 by avoiding the malloc call in the read_view_create_low() in most cases. This significantly improves InnoDB scalability on read-only workloads, especially when the default glibc memory allocator is used. Bug fixed #1131187 (Alexey Kopytov).
• Removed trx_list scan in read_view_open_now() which is another problem originally reported as upstream bug #49169. This also provides much better scalability in InnoDB high-concurrent workloads. Bugs fixed #1131189 (Alexey Kopytov).
• In the event that a slave was disconnected from the master, under certain conditions, upon reconnect, it would report that it received a packet larger than the slave_max_allowed_packet variable. Bug fixed #1135097 (George Ormond Lorch III).
• Fixed the upstream bug #62578 which caused MySQL client to abort the connections on terminal resize. Bug fixed #925343 (Sergei Glushchenko).
• Percona Server for MySQL would re-create the test database when using rpm on server upgrade, even if the database was previously removed. Bug fixed #710799 (Alexey Bychko).
• Debian packages included the old version of innotop. Fixed by removing innotop and its InnoDBParser Perl package from source and Debian installation. Bug fixed #1032139 (Alexey Bychko).
• UDF/configure.ac was incompatible with automake 1.12. Bug fixed #1099387 (Alexey Bychko).
• Reduced the overhead from innodb_pass_corrupt_table value checks by optimizing them for better CPU branch prediction. Bug fixed #1125248 (Alexey Kopytov).
• dialog.so used by the PAM Authentication Plugin couldn’t be loaded with Perl and Python clients when plugin-dir option was set in the [client] section of the my.cnf. Bug fixed #1155859 (Sergei Glushchenko).
• Fixed the upstream bug #68845 which could unnecessarily increase contention on log_sys->mutex in write-intensive workloads. Bug fixed #1163439 (Alexey Kopytov).
• Ported back from the upstream MySQL 5.6 the fix for unnecessary log_flush_order_mutex acquisition. Bug fixed #1163262 (Alexey Kopytov).
• When mysqldump was used with --innodb-optimize-keys and --no-data options, all secondary key definitions would be lost. Bug fixed #989253 (Alexey Kopytov).
• Warning about the Percona Toolkit UDFs was omitted when installing from Percona’s Debian repositories. Bug fixed #1015506 (Alexey Bychko).
• Percona Server for MySQL was missing help texts in the MySQL client because the help tables were missing. Bug fixed #1041981 (Alexey Bychko).
• Fixed the upstream bug #68197 that caused InnoDB to misclassify internal read operations as synchronous when they were actually asynchronous when Thread Pool feature was used. Bug fixed #1107539 (Sergei Glushchenko).
• Suboptimal code for User Statistics feature has been optimized to make sure no additional work is done when userstat is disabled. Bug fixed #1128066 (Alexey Kopytov).

Other bug fixes: bug fixed #1146621 (Laurynas Biveinis), bug fixed #1050536 (Alexey Bychko), bug fixed #1144059 (Roel Van de Paar), bug fixed #1154962 (Hrvoje Matijakovic), bug fixed #1154959 (Hrvoje Matijakovic), bug fixed #1154957 (Hrvoje Matijakovic), bug fixed #1154954 (Hrvoje Matijakovic).

Release notes for Percona Server for MySQL 5.5.30-30.2 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

UPDATE[18-04-2013]: There was a RPM packaging regression introduced with the fix for bug #710799. This regression only affected clean RPM installations and not upgrades. We have pushed the fixed packages to the repositories. Bug fixed #1170024.

The post Percona Server for MySQL 5.5.30-30.2 now available appeared first on MySQL Performance Blog.

We just released our first alpha of Percona XtraBackup 2.1 for MySQL and with it we included the ability to encrypt backups on the fly (full documentation here). This feature is different than simply piping the backup stream through the openssl or gpg binaries, which is what some people have used in the past. A big benefit of using the built-in encryption is that multiple CPU cores can be used for encryption (with the --encrypt-threads option). You can also combine compression and encryption, each using multiple CPU cores.

One advantage of encrypting your backups is it means you can place less trust in the place where you store your backups. Our databases hold a variety of information, some of which is rather sensitive and should be kept private. One attack vector for an adversary gaining access to all of your data is via a backup.

By using Percona XtraBackup encryption your data is private by design rather than private by placing trust in your backup storage provider. This is especially an issue if you are using cloud storage to store your backups but also applies to simple off-site backups.

Percona XtraBackup 2.1.0-alpha1 supports industry standard AES encryption and we use an existing software library (libgcrypt) to do the heavy lifting so we are in no way re-inventing the wheel. You can download it here for free.

The post Why use encrypted backup with Percona XtraBackup 2.1 for MySQL? appeared first on MySQL Performance Blog.

Over at the work blog, I wrote about what the true maximum number of columns in MySQL is as well as the minimum maximum. Basically, the FRM file is ass and places bizarre arbitrary limits on things due to what can only be seen as limitations in 1980s computing and automatically generated interfaces for entering rows on a 80×24 character VT100 terminal.

Full post here: http://www.mysqlperformanceblog.com/?p=13501

Percona is glad to announce the release of Percona Playback 0.6 for MySQL on April 9, 2013. Downloads are available from our download site and Percona Software Repositories.

Percona Playback for MySQL is a tool for replaying the load of one database server to another. Currently it can read queries from MySQL query-log and MySQL tcpdump files and run them on other MySQL server. With Percona Playback you can measure how a server or database upgrade, change in my.cnf or schema change can affect the overall performance of your MySQL database server.

Percona Playback for MySQL can also help evaluate new server versions, patches and hardware against existing setups to allow users to make an informed choice about deploying changes to their database infrastructure.

This release should be considered BETA quality.

New features:

• New Pool of threads feature has been implemented that can be used for dispatching queries to MySQL database servers. This feature introduces new –thread-pool-threads-count option, which can be used to specify the number of threads in the thread pool dispatcher plugin.

Bugs fixed:

• Percona Playback for MySQL now shows error when it’s unable to connect to the MySQL database server. Bug fixed #1070824 (Vlad Lesin).
• Multi-line query parsing has been improved. Bug fixed #1080648 (Vlad Lesin).
• Missing build dependences have been added to the README file. Bug fixed #1072845 (Stewart Smith).
• Percona Playback for MySQL wouldn’t reconnect or log the error if the connection was broken during the query. Bug fixed #1080654 (Vlad Lesin).

Release notes with all the bugfixes for Percona Playback for MySQL are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Playback 0.6 for MySQL now available appeared first on MySQL Performance Blog.

This post was initially going to be two sets of polls: “What is the maximum number of columns in MySQL?” and “What is the minimum maximum number of columns in MySQL?”. Before you read on, ponder those questions and come up with your own answers… and see if you’re right or can prove me wrong!

Back in 2009, I finished what seemed an epic task in the Drizzle code base: banishing the FRM file. Why? We felt it was not a good idea to keep arbitrary and obscure limitations from the 1980s alive in the 21st century and instead wanted a modular system where the storage engines themselves owned their own metadata. This was a radical departure from the MySQL philosophy, and one that has truly paid off in the Drizzle code base. However… for those using MySQL, Percona Server, MariaDB or any other of the MySQL branches/forks, you get to have these odd limitations.

Why do I discuss the FRM file? If we look at the MAX_FIELDS define in MySQL, we’ll see that it’s defined as 4096. This, however, is not the true limit. To find out what the actual limit is, we must delve further into the FRM file.

What is an FRM file? It’s the FoRM file from UNIREG. It’s FRM rather than FORM as, well, you used to only get three characters after a dot in a file name. Back in 1979, Monty developed an in-house database named UNIREG. UNIREG was a text-based program for entering records into an ISAM-style database. It would have an 80×24 text UI for entering data and a separate UI for generating reports. This evolved into the SQL based MySQL, with MySQL 1.0 being released in 1995.

The FoRM file specified what fields where on what screen for entering, as well as any constraints as to what could be entered. For example, if you had more than a certain number of fields, you were going to need more than one 80×24 screen to enter in all the data! You could also have things like NEXT_NUMBER fields (what we know today as auto_increment), CASEUP and CASEDN fields which although not implemented in MySQL, the defines can still found in the source. Basically, it’s why we can’t have nice things (like default values other than NOW()).

It also has certain limits which by any modern standard are purely arbitrary. One of those is the limit that a certain part of the FRM file cannot be greater than 64kb. The bit of code in question that comes into play around the maximum number of columns is this:

/* Hack to avoid bugs with small static rows in MySQL */ reclength=max(file->min_record_length(table_options),reclength); if (info_length+(ulong) create_fields.elements*FCOMP+288+ n_length+int_length+com_length > 65535L || int_count > 255) { my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0)); DBUG_RETURN(1); }

Which is, of course, obvious! Various parts of this limit are:

• info_length is roughly 2+strlen(field_name) for each field. Unless you have many columns, and then it’s something else (as in that case you don’t get your 80×24 terminal UI in your FRM file, you instead get some bunch of bytes per 19 columns).
• create_fields.elements*FCOMP is just number of fields multiplied by 17
• 288 is static and is always needed
• int_length is the interval length. This isn’t the normal meaning of the word interval, we can only guess that it’s called this due to either something UNIREG specific or it’s just Monty’s English language skills in the 1980s. We’ll come back to this one.
• com_length is the length of all the comments for each field (but not the table)

An interval in UNIREG speak is a set of strings that are the options for ENUM or SET columns. The tricky bit is that it’s unique intervals, not actual intervals, so two ENUM columns both having the options ‘Y’ and ‘N’ will use less space in the FRM than if you had one with ‘Y’ and ‘N’ and the other with ‘A’ and ‘B’.

If you noticed that if you have a long comment on each field you reduce the number of different ENUM elements you can have, you are correct. There is also a limit of 255 unique intervals, so while you can have many more ENUM(‘Y’,'N’) columns, you can only have 255 ENUM columns with unique values.

If you were looking for a very basic formula that is mostly accurate, I present this:

foreach field: 17+2*(strlen(field_name)+2) (bytes) + length of all comments (in bytes) + length of all intervals (for ENUM, SET) in bytes.

If you use that as a rule of thumb, with that not being able to exceed 64k, you’re roughly on the right track to working out the maximum number of columns in a MySQL table.

So what’s the maximum number of columns in a MySQL table? Well.. I tried a few things before I settled on the following (perl) program (accepts command line parameter of number of columns to create) to produce the CREATE TABLE sql statement:

sub cname ($) { my $c=shift; my $name=""; while($c > 0) { my $n=$c%36; $name.=chr(ord('0')+$n) if $n < 10; $name.=chr(ord('a')+($n-10)) if $n >= 10; $c= int $c/36; } return $name } my $sql= "CREATE TABLE t ("; foreach(1..shift @ARGV) { my $n=cname($_); $sql.="`$n`"; $sql.=" ENUM('Y','N','M','0','1','2')\n"; } chop $sql; chop $sql; $sql.=");"; print $sql;

This gets you a 46kb CREATE TABLE statement and a 76kb FRM file for a table with 2,829 columns. I believe this to be the maximum number of columns you can create.

If you try setting the storage engine to InnoDB however, you will get an error message! The exact error message you get is not very interesting and just says “Can’t create table ‘test.t’ (errno: 139)”. This is because InnoDB has a hard limit of 1,000 columns. This is the code from ha_innodb.cc that enforces that limit:

if (form->s->fields > 1000) { /* The limit probably should be REC_MAX_N_FIELDS - 3 = 1020, but we play safe here */ DBUG_RETURN(HA_ERR_TO_BIG_ROW); }

Which is curiously gone from MySQL 5.6, it’s replaced by the following set of defines

#define DATA_N_SYS_COLS 3 /* data0type.h */ /* from rem0types.h */ #define REC_MAX_N_FIELDS (1024 - 1) #define REC_MAX_N_USER_FIELDS (REC_MAX_N_FIELDS - DATA_N_SYS_COLS * 2)

Which adds up to (1024-1)-3*2=1017 which is exactly the same as what I can create. That’s right folks, in MySQL 5.6 you can create a table with a few more columns in InnoDB!

This led me on another idea… what is the minimum maximum number of columns you can create? You may think that it is 255 based on the limit of the number of intervals above, but can you get any lower? Why yes you can! With this bit of perl code I was able to hit a too many columns error with only 192 columns (i.e. 191 worked):

sub cname ($$) { my $c=shift; my $name=""; while($c > 0) { my $n=$c%36; $name.=chr(ord('0')+$n) if $n < 10; $name.=chr(ord('a')+($n-10)) if $n >= 10; $c= int $c/36; } $name.='0' foreach(length $name .. shift); return $name } my $sql= "CREATE TABLE `".cname(16,63)."` ("; foreach(1..shift @ARGV) { my $n=cname($_,63); $sql.="`$n`"; $sql.=" ENUM('".cname(0,64)."') COMMENT '".cname($_,254)."',\n"; } chop $sql; chop $sql; $sql.=");"; print $sql;

So the maximum number of columns for a table in MySQL is somewhere between 191 and 2829, depending on a number of factors. I’d be interested to hear if you’ve been able to beat my minimum/maximums!

The post Understanding the maximum number of columns in a MySQL table appeared first on MySQL Performance Blog.

I use LinkedIn a lot. I also use various Google properties a lot. Both of them have a bunch of distracting and annoying UI features that just get in my way. I know Google and LinkedIn have a vested interest in catching my attention and trying to get me to use their products in the ways that will make them the most money, but as a user, I don’t care what they want. I care what I want.

So I’ve used AdBlock to clean up the elements I don’t want. Here’s how.

I’m assuming you use one of the many excellent AdBlock extensions for whatever browser you use. If not, you need to do that — reclaim your browser and view the web the way you want it. Every time I use someone else’s computer and they don’t have an AdBlock extension it blows my mind.

LinkedIn Endorsements

LinkedIn has a new endorsement feature. They show me a big banner at the top of the page every time I view a connection’s profile page.

I don’t care. I don’t know enough about most of my connections to endorse them publicly, and I don’t want to see that nonsense. So I AdBlock it. This is easy to do — just right-click and select the context menu item to block that whole banner.

I’ve also hidden the endorsements on my profile. It’s nice that people want to endorse me (although I think LinkedIn is almost bullying them into it by putting the obnoxious endorsement box in their faces), but most of the endorsements are for things I’m not actually expert in. Some of them I literally know nothing about. It’s interesting to see the difference between what people think I know and what I actually know.

Google+ Notifications

Somewhere I read that Google+ has quietly become a significantly large social network, with a large fraction of the number of users that Facebook has. I know why that is: it’s because Google has tied a Google+ account into nearly all of their services, and most people I know have multiple Google profiles. I have seven, and a couple months ago I had eight. Google can count me multiple times if they wish; that’s their business.

What’s my business is whether I let them annoy me with notifications. You know that irritating notification bar at the top of the page in all of the pages on Google’s domains? This one?

As far as I can tell, Google doesn’t allow you to disable notifications. The only way I know to get rid of that irritating red animated 1 button is to click on it so it turns back into a faint gray 0:

No offense, guys, I do consider you friends, but I don’t care that you added me to your circles :-\ I don’t use Google+, regardless of what Google tries to make you believe. I bet you were probably bullied into adding me, just to make some annoying suggestion box go away.

The Google+ notifications box is a little harder to hide, because it uses CSS classes to generate the numbers in the box. But a little digging into the page source yielded the following custom AdBlock rules:

google.com##DIV[id*="gbgs"] google.com##SPAN[id*="gbgs"]

And now all of my Google pages are free of one more time-wasting, attention-disrupting, productivity-killing distraction.