percona-toolkit | Percona Toolkit

PARTITION BY HASH is virtually useless. I don't expect it to help you with 100 partitions, nor with 1000.

You get more bang for your buck by arranging to have venue_id as the first column in the PRIMARY KEY.

Does the query always have a single venue_id? (If not the options get messier.) For now, I will assume you always have WHERE venue_id = constant.

You have a multi-dimensional indexing problem. INDEXes are only one dimension, so things get tricky. However, partitioning can be used to sort of get a two-dimensional index.

Let's pick day_epoch as the partition key and use PARTITION BY RANGE(day_epoch). (If you change that from a 4-byte INT to a 3-byte DATE, then use PARTITION BY RANGE(TO_DAYS(day_epoch))).

Then let's decide on the PRIMARY KEY. Note: When adding or removing partitioning, the PK should be re-thought. Keep in mind that a PK is a unique index. And the data is clustered on the PK. (However, uniqueness is not guaranteed across partitions.)

So...

Plan A: Use Percona's tool: pt-online-schema-change.

Plan B: Use a competing product: gh-ost.

Plan C: Don't use UPDATE, that is the killer. Instead, rebuild the table(s) in a straightforward way, then use RENAME TABLE to swap the new version into place.

Partitioning is unlikely to help in any way. Daniel's link helps with doing a lengthy UPDATE, but trades off time (it takes longer) versus invasiveness (which is not an issue because you have a maintenance window).

Some more details into Plan C (which I prefer for this case):

Yes, you're right, the order of columns in the select-list of a query makes no difference to performance.

But treating those two queries as the same fingerprint could obscure the source of the query.

Suppose you have those two queries, each one is in a different part of your application. You might like to know which one is responsible for 40% of your query load and which is responsible for only 2% of your query load.

It would also be much more complex to produce the fingerprint of a query if it had to detect the commutativity of columns as you describe. That would also apply to boolean terms in the WHERE clause, and to some extent order of joined tables in the FROM clause, and order of unioned queries in a UNION, too.

The code for fingerprint() is only about 100 lines of code to do pattern-matching implemented with regular expressions. Doing what you describe would require a full-blown SQL parser. See the code here: https://github.com/percona/percona-toolkit/blob/3.0/lib/QueryRewriter.pm

You can temporarily disable foreign key checks on your local dev database:

I noticed a new bug-report during the weekend, and I have confirmed today that this is indeed the problem I am experiencing.

The workaround is to add --set-vars binlog_format=statement.

When I set this option, the difference reveals itself after the second run.

During the first run the checksum table on the slave changes from: