ALTER TABLE is often necessary, but it can be risky in a production environment for many reasons. Let's discuss the problems that one has to take into account.
10 Older Entries »
I just posted an article on the Percona Community Blog. You can access it following this link:
A Nice Feature in MariaDB 10.3: no InnoDB Buffer Pool in Core Dumps
I do not know if I will stop publishing posts on my personal blog or use both, I will see how things go. In the rest of this post, I will share why I published there and how things went in the process.
So there is a Percona
MariaDB 10.3 is now generally available (10.3.7 was released GA on 2018-05-25). The article What’s New in MariaDB Server 10.3 by the MariaDB Corporation lists three key improvements in 10.3: temporal data processing, Oracle compatibility features, and purpose-built storage engines. Even if I am excited about MyRocks and curious on Spider, I am also very interested in less flashy but still very important changes that make running the database in production easier. This post describes such improvement: no InnoDB Buffer Pool in core dumps.
Hidden in the …[Read more]
In this blog, I’m going to investigate why the error below has been appearing in a special environment I’ve been working with on the last few days:
Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
The error provides the right message, and explains what is going on. But sometimes, it can be a bit tricky to solve this issue: you need additional information discovered after some tests and readings. We try and keep Managed Services scripted, in the sense that our advice and best …[Read more]
Last week I had the pleasant opportunity of introducing and discussing the operation of online schema migrations to MySQL's engineering managers, as part of their annual meeting, in London.
Together with Simon J. Mudd of Booking.com, we discussed our perception of what it takes to run online schema migrations on a live, busy system.
While the Oracle/MySQL engineers develop new features or optimize behavior in the MySQL, we of the industry have the operational expertise and understanding of the flow of working with MySQL. In all topics, and in schema migration in particular, there is a gap between what's perceived to be the use case and what the use case actually is. It is the community's task to provide feedback back to Oracle so as to align development to match operations need where possible.
Our meeting included the following:
Need for schema migrations
We presented, based on our …[Read more]
The pt-online-schema-change tool has been a workhorse for years, allowing MySQL DBAs to alter tables with minimal impact to application workload, and before MySQL had native online alter capabilities. Although consistently grateful for the tool’s existence, I never liked the messiness of having to use and clean up triggers, and most DBAs have a horror story or two to tell about using any of the online alter methods.
When Github’s online schema transmogrifer (gh-ost; https://github.com/github/gh-ost) was released, then, I jumped on the chance to test it out.
Following are my testing notes. I tried out the flags available to run gh-ost from different locations in a replication setup, both functionally and under load. The load applied was via mysqlslap, using a …[Read more]
(Almost) another new year by Jewish calendar. What do I wish for the following year?
- World peace
- Good health to all
- Relaxed GTID constraints
I'm still not using GTID, and still see operational issues with
working with GTID. As a latest example, our new schema migration
solution, gh-ost, allows us to test migrations in production, on
replicas. The GTID catch?
gh-ost has to write
something to the binary log. Thus, it "corrupts" the replica with
a bogus GTID entry that will never be met in another server, thus
making said replica unsafe to promote. We can work around this,
I understand the idea and need for the
Set. It will certainly come in handy with
multi-writer InnoDB Cluster. However for most use cases GTID
poses a burden. The reason is that our topologies are imperfect,
and we as humans are imperfect, and operations are …
gh-ost now powers our production schema migrations. We hit some serious limitations using pt-online-schema-change on our large volume, high traffic tables, to the effect of driving our database to a near grinding halt or even to the extent of causing outages. With gh-ost, we are now able to migrate our busiest tables at any time, peak hours and heavy workloads included, without causing impact to our service.
gh-ost supports testing in production. It goes a long …[Read more]
With the unintended impression of becoming live blogging, we now follow up on Solving the non-atomic table swap, Take II and Solving the Facebook-OSC non-atomic table swap problem with a safe, blocking, atomic solution
Why yet another iteration?
The solution presented in Solving the non-atomic table swap, Take II was good, in that it was safe. No data corruption. Optimistic: if no connection is killed throughout the process, then completely blocking.
Two outstanding issues remained:
- If something did go wrong, the solution reverted to a table-outage
- On …
Following up and improving on Solving the Facebook-OSC non-atomic table swap problem, we present a better, safe solution.
Quick, quickest recap:
We are working on a triggerless online schema migration solution. It is based on an asynchronous approach, similarly to the FB osc and as opposed to the synchronous solution as used by pt-online-schema-change.
We asynchronously synchronize (is that even a valid statement?) between some table tbl and a ghost table ghost, and at some time we want to cut-over: swap the two; kick …[Read more]
10 Older Entries »