Database engineering for MySQL

Tables keep changing,
the app still answers

We look after MySQL databases and the changes they need: each table change made the way that keeps writes going, slow queries found and fixed, and each long-term release taken in turn, so you know how a change will run before it starts.

Thanks, we’ll be in touch soon.

{{ heroStatus }}

You’re in good company

A few of the companies we’ve worked with.

When a quick change meets the real table

A MySQL database keeps an app’s data in tables: rows that each hold the same columns, each row found by its primary key, and indexes, which keep chosen columns in order so a query, an app’s request for data, can find its rows without reading every one.

A change that took a moment on a test copy can run differently on the real table. How large it has grown, and the work already running on it, often decide how long the change takes and what waits for it.

The server makes many changes at once, or in place while reads and writes carry on. Some still build a copy of the whole table, and writes wait until it’s done. A change can also wait for work already open on the table, and new work can queue behind it while it waits.

Moving the server to its next long-term release, a version supported for years, is a change of its own, taken in order.

So the real work is knowing how each change will run before it starts, and planning it around the app.

What we build

Table changes planned before they run, queries kept quick, text kept as it’s typed, and databases kept running and moved one long-term release at a time.

  • Table changes planned before they run

    Each change checked for how the server will make it, asked for that way by name, tried first on a copy of the real table, and the ones that copy a table planned on their own.

  • Queries that stay quick

    The slowest queries found in the server’s own record of them, their plans read, the indexes they need added, and indexes no query uses set aside first, then removed.

  • Text kept as it’s typed

    Tables moved to the full character set, the encoding that keeps every character people type, emoji included. A conversion rewrites every row while writes wait, so it’s planned like a change that copies the table.

  • Set up to keep running

    Replicas, copies that follow their source server; where it fits, a group of servers that copy each change among themselves, with failover built in, and a router that sends each app to the right one; backups restored to a separate server on a schedule; and the binary log, the server’s record of changes, kept for restores to a chosen moment, each restore timed.

  • Moved from the setup you have now

    Between a managed service from your cloud and your own servers, or onto Oracle’s own cloud service, the new server catching up by replication before the switch, and each table checked against the old one.

  • Upgrades, one long-term release at a time

    Each server checked with the upgrade checker first, each move rehearsed on a copy, the replicas before the source, and never a series skipped.

  • Access kept to what each app needs

    An account for each app with only the privileges it needs, roles for the people who run the database, encrypted connections and password rules.

  • Watched once it’s running

    Slow queries, how far each replica trails, connections and disk use, with alerts your team sees and acts on.

  • AI where it helps

    The data an AI feature reads, given through views and a read-only account, under the same rights as everything else.

    AI Services

Changed while the app keeps writing

A change to a table’s columns or indexes is made in one of three ways: at once, by changing only the table’s description; in place, where the table already is, while reads carry on, and writes too for most changes; or by building a copy of the whole table, while writes wait.

The server takes the lightest way the change allows, and which way that is depends on the change. Every way can also wait a moment, at its start and at its end, for work already open on the table.

A change made in place can take longer overall than a copy, but the app keeps writing while it runs.

So we name the way we want, at once or in place, each time we ask the server for a change, and a change that would copy the table stops before it starts. The ones that copy are planned on their own, tried first on a small copy of the table, as the manual suggests, then run in a planned window or on one replica at a time.

Choose a change, and see how the server makes it.

Changes to the Sightings table at Siskin Birders

Choose a change

Add a column
Rename a column
Drop a column
Add an index
Change a column’s type
Convert the table’s character set

Made at once

Reads
Carry on
Writes
Carry on

Only the table’s description changes, so the rows aren’t touched.

Made at once

Reads
Carry on
Writes
Carry on

Only the column’s name changes, as long as its type stays the same.

Made at once

Reads
Carry on
Writes
Carry on

The column leaves the table’s description, and the rows aren’t touched.

Made in place

Reads
Carry on
Writes
Carry on

The index is built beside the rows, and the writes made meanwhile are added at the end.

Copied whole

Reads
Carry on
Writes
Wait until it’s done

A new copy of the whole table is built, then put in place of the old one.

Made in place

Reads
Carry on
Writes
Wait until it’s done

Every row is rewritten in the new character set, where the table already is.

Each change can also wait a moment, at its start and its end, for work already open on the table.

{{ cgSaid }}

One long-term release at a time

MySQL releases come in two tracks: long-term releases, each series supported for years under Oracle’s Lifetime Support Policy, and Innovation releases, each supported until the next one arrives.

Within a long-term series, features and the way data is stored stay the same, so moving within it is a small step, and so is stepping back.

Moving to the next long-term series is supported, but skipping one isn’t, so an older server moves one series at a time. Stepping back across a series means reloading the data or using replication, and only while nothing new has been used.

Before each move, the upgrade checker in the MySQL Shell utilities lists what would stop it. The move itself is made in place, by dumping the data and loading it into the new release, or by replication to a new server, the replicas before the source. Each app’s connector, the library it reaches the database through, is checked against the new release too.

So we keep a list of each server’s release and the end of support date of its series, run the checker well ahead, and rehearse each move on a copy, replicas before the source.

Start with each server’s release and its next change

The review covers each server’s release and the end of support date of its series; what the upgrade checker finds, run on a copy; the tables, with their character sets, their primary keys and the indexes no query uses; the changes planned for them, and the way each would run; the slowest queries and their plans; the replicas, how far each trails, and when a failover was last tried; the backups, where they go, when one was last restored and how long that took, and how far back the binary log reaches; the accounts each app uses, their privileges and their encrypted connections; each app’s connector; how changes reach the tables today, and who makes them; and where it all runs, on your own servers, on Oracle’s own cloud service or on a managed service from your cloud.

From the review we suggest a first step, tried on a copy before anything changes: the move to the next long-term series, after the upgrade checker; one planned table change, run on a copy of the real table; the slowest queries fixed; the move to the full character set, planned table by table; or a restore to a chosen moment, timed.

What you receive is a written review and a proposal for that first step.

Questions worth asking

  1. Can we change a busy table without stopping the app?

    Often, yes. Many changes are made at once, or in place while reads and writes carry on. The ones that copy the table are planned on their own, tried first on a copy and run when the app can spare its writes for a while.

  2. Our server is past its end of support date. What now?

    Move it to the next long-term series first, after the upgrade checker, rehearsed on a copy. A series can’t be skipped, so an older server may take more than one move, each planned the same way.

  3. Can it keep every character people type?

    Yes, with the full character set, the one the manual recommends for every new application. Older tables are converted, which rewrites every row while writes wait, so each is planned like a change that copies the table.

  4. Do reads from a replica always show the latest change?

    Not always. By default a replica copies each change after its source has made it, so it can trail. Reads that must show a change just made go to the server that took it.

  5. What does it cost to work with you?

    It depends on the servers you run and the first step you choose. Before any work starts, you get a written proposal with the scope, the price or rate, and how you pay.

    Pricing

  6. Can we hire developers for our own team?

    Yes, through a dedicated team: Webair developers who stay with your product over time and work beside your own people.

    Dedicated team

Oracle, Java, MySQL, and NetSuite are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. Webair is independent of Oracle, and isn’t sponsored by, endorsed by or affiliated with it.

Let’s build it better

Tell us which tables are changing next, and which release your servers run, and we’ll suggest where to begin, in a short call.

Thanks, we’ll be in touch soon.

{{ ctaStatus }}