High Availability With MySQL – Jay Janssen of Percona

Monday, August 4, 2014
6:00 PM

GrubHub
111 W. Washington St.
Suite 2100
Chicago, IL

Come join Jay Janssen, Principal Consultant at Percona as he speaks about High Availability with MySQL. Jay has been with Percona since 2011. Before that, spent 7 years working for Yahoo in a variety of fields including High Availability architectures, MySQL training, tool building, global server load balancing, multi-datacenter environments, operationalization, and monitoring.

Pizza and beverages will be provided.

If you've ever tried to add an index that includes a long varchar column to an InnoDB table in MySQL, you may have seen this error:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

The character limit depends on the character set you use. For example if you use latin1 then the largest column you can index is varchar(767), but if you use utf8 then the limit is varchar(255). There is also a separate 3072 byte limit per index. The 767 byte limit is per column, so you can include multiple columns (each 767 bytes or smaller) up to 3072 total bytes per index, but no column longer than 767 bytes. (MyISAM is a little different. It has a 1000 byte index length limit, but no separate column length limit within that).

One workaround for these limits is to only index a prefix of the longer columns, but what if you want to index more than 767 …

Hm, how about option 5 to have a pool of pre-warmed EBS volumes ready in each AZ? As a new instance comes up, attach the volume, ‘mkfs’ to wipe the volume and then deploy the current version of the DB.

Having EBS sitting around is a lot cheaper than more EC2 running 24/7 (also, no time or IO penalty waiting for the ‘dd’ each time)

Cheers,
Gavin.

Register before August 1st for early bird pricing!

Millions of organizations around the world trust MySQL to power their business-critical web, cloud, and embedded applications. Want to learn best practices to develop next-generation applications with MySQL? Joins us at MySQL Central @ OpenWorld.

Highlights
Learn new skills
Share and network with the global MySQL community
Hear about new MySQL features directly from Oracle
Get insight on product roadmaps
Have fun

Tue, 2014-07-29 19:36Marc Sherwood

Another OSCON has been wrapped up. While these year was slightly smaller than last year it was still an amazing event. The interesting part was that MySQL and MariaDB seemed to be bigger topics than in years gone by.

Now that I have been back in the office for a few days, I am getting caught up on my overloaded inbox, and have had some time to reflect on the event.

The first thing that comes to mind is that MariaDB adoption rate is growing rapidly, and we hear about more amazing deployments at each event we attend. This year we met with a number of people who have made the switch from MySQL to MariaDB for their corporate use, as well as a good number of hosting companies who have migrated to MariaDB. We are working on getting some of these stories ready to share so stay tuned for more on that!

We aim to have fun in the MariaDB booth - how else could we stay semi-sane during …

One of the common causes of downtime with MySQL is running out of connections. Have you ever seen this error? “ERROR 1040 (00000): Too many connections.” If you’re working with MySQL long enough you surely have. This is quite a nasty error as it might cause complete downtime… transient errors with successful transactions mixed with failing ones as well as only some processes stopping to run properly causing various kinds of effects if not monitored properly.

There are number of causes for running out of connections, the most common ones involving when the Web/App server is creating unexpectedly large numbers of connections due to a miss-configuration or some script/application leaking connections or creating too many connections in error.

The solution I see some people employ is just to increase max_connections to some very high number so MySQL “never” runs out of connections. This however can cause …

I recently hosted hosting a webinar which explained what MySQL Clusrter is, what it can deliver and what the latest developments were. The “Discover the latest MySQL Cluster Developments” webinar is now available to view here. At the end of this article you’ll find a full transcript of the Q&A from the live session.

Details:

View this webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by making it far simpler and faster to build your products and web-based applications with MySQL Cluster. You’ll also learn how MySQL Cluster and its linear scalability, 99.999% uptime, real-time responsiveness, and ability to perform over 1 BILLION Writes per Minute can …

XA transactions are an open standard for distributed transactions. A distributed or global transaction can spawn multiple databases. XA transactions come handy, for example, when data sets are sharded over multiple servers and a business transaction affects multiple shards. The free MySQL Fabric tools help to setup and manage a sharded MySQL cluster. The development version of PECL/mysqlnd_ms 1.6 helps with XA transactions. It abstracts SQL details and acts as a transaction manager. The PHP MySQL driver kicks in when things go wrong and XA transactions show their nasty side: blocked servers. Good news: this is a rare case. Bad news: a deep dive …

Tue, 2014-07-29 10:00mriddoch

Part 1 of this blog post told the story of creating a binlog router for MaxScale that could connect to a MySQL Replication Master, download the binlog file from that master and store them locally on the MaxScale server. This post will concentrate on the other side of the router, the interaction with the MySQL slaves that will see MaxScale as the replication master.

Router Structure

In some ways the role of master for MaxScale is much closer to the more expected style of interaction that MaxScale was designed to deliver; a connection originates from a client to a MaxScale service. MaxScale then processes that requirement and returns a result back to the client. The most obvious difference is of course that the processing does not involve forwarding the request on to another server, …

In the past I have used pt-slave-delay when I want to maintain an intentionally delayed replica. Now that I have upgraded to MySQL 5.6 I am switching over to use MASTER_DELAY, which is a built-in feature that does the same thing.

For example I can replace this:

``` pt-slave-delay --delay 7d --interval 1m --daemonize

```

With this:

STOP SLAVE; CHANGE MASTER TO MASTER_DELAY = 604800; START SLAVE;

The implementation is similar: the IO thread copies the events to the relay log as fast as normal, but the SQL thread only executes events older than the defined lag. The process to …