1

the Business Intelligence guy is asking for faster machines, latest software etc. and some of his customers are complaining of query times in excess of an hour. The tool is Cognos/Insight and the DB is replicated copy of a master MySQL which we are shortly to migrate to MariaDB.

My gut feel ( before I get down and dirty with the SQL and Execution plans ) is that we are simply dealing with poorly optimised querys coming out of Cognos and an inappropriate collection of indexes to support them.

My Question is ( before I bespoke a solution ) : Are there accepted strategies for realtime slave tables that allow for indexes that are not present on the master table?

1

Enable the slow query log and log_queries_not_using_indexes on the slave server and should be able to confirm what your gut is telling you. You can take the captured queries, run EXPLAIN on them, and have some better documentation of your suspicions.

To more directly answer the question, it's absolutely valid to declare indexes on a slave that are not declared on the master. I would suggest that it's a common practice, and one of many reasons why replicas are useful for so many things. Connect to the slave with an account with sufficient privilege, and alter the table directly on the slave.

The only exceptions to this -- which should be intuitively obvious, since the row data needs to be consistent on master and replica -- is that you cannot safely declare a UNIQUE constraint or a foreign key constraint on a slave when it doesn't exist on the master. This won't work, which is fine, since it doesn't make sense at any rate.

Remember also that indexes in MySQL have names, which are automatically generated if they are not supplied when the index is added. A good practice might be to explicitly name these indexes on the replica so that future indexes added to the master cannot possibly cause an index name collision, which would break replication. My local convention is to prefix the index name with "ix_repl_" which would of course never be used on the master.

ALTER TABLE t1 ADD KEY ix_repl_last_first (last_name,first_name);

Note also that proper duplicate indexes (where exactly the same columns, and no others, are included in more than one index) are deprecated in MySQL 5.6 and disallowed by default in MySQL 5.7, making it apparently possible in later versions to cause replication to stop if you subsequently declare an identical index (even with a different name) on the master. This wouldn't be a critical issue (as long as you're monitoring replication -- you are, right?) since replication would be safe to restart by simply removing the now-redundant index on the replica before restarting the slave SQL thread. The failed event would be retried, and would now be valid, since there's no conflicting index, and the replacement index would be built on the replica.


Side note: remember that MySQL replication requires the version of MySQL on the replica servers to be the same as, or newer than, the version on the master... so when upgrading (or migrating to MariaDB) you will almost certainly want to upgrade the replicas first, and then the master. The reason for this is that a newer replica will understand the capabilities and quirks of an older master, but a newer master may introduce behaviors in the replication stream that an older replica server won't be able to interpret. There are limited exceptions to this rule, but it's most definitely the rule.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.