Following up on Morgan Tocker’s What would you like to see in MySQL 5.8?, having attended and participated at the brainstorming at Percona Live Amsterdam, and publishing this post while failing to comply with any of Morgan’s suggested media, these are the features I would like to see in MySQL 5.8:
- Dynamicly enable/disable log-bin and log-slave-updates
Today, when changing chef/puppet role of a server from a simple slave to an intermediate master and vice versa, a MySQL restart is required. This is a very big pain which makes replication automation complex, not to mention warmup times. - “nice“.
I want to be able to execute a query that is nice, i.e has lower priority; will not consume all resources; will stall/throttle so as to allow other queries to complete. Luis asked and I said this could be on a per statement basis, e.g. add a SQL_NICE query hint. But I’m unsure that would be the correct behavior. It also makes sense to do so on a per connection basis (perhaps provide connection attributed to hint niceness?). - Online-ier ALTER TABLE. I would in particular want it to apply the nice feature, above. Otherwise throttle by user defined metrics.
- Online-ier ALTER TABLE in replication stream. Can the slaves run the ALTER statement in parallel?
- Re-Group Commit: in MTS, and when intermediate masters involved, copy+paste the group commit as applied on master as working downstream. I suspect this is easily achievable. The result: same parallelism for replicating slaves in all levels, whether they replicate directly from master or from 2nd, 3rd tier intermediate masters. Today parallelism decreases as we go downstream.
- Global user-defined-variables. I want to be able to define arbitrary (global) variables that I can later query via SELECT @@global.arbitrary. This would be similar to HTML 5‘s “data-*” attributes. I often wish I could tell & ask MySQL my puppet role; or the server status (is it live? Is it offline? Does it belong to a specific pool? etc.). Similar to “loose-*” syntax, this could be a “data-*” or “user-*” name prefix system.
I will follow up on new statements I would like to see in MySQL 5.8.
The brainstorming session at PerconaLive, I should note, was pure joy, and apart from getting two nice furry dolphins I enjoyed the engagement, the diversity of ideas, and the fact Oracle engineers (Mark in particular) were very busy taking notes or otherwise openly discussing the viability of some requested features.
Thank you Shlomi! Suggestions noted.
My vague memory is that many years ago when we did GTID in the Google patch that online change from master to slave and back again was added for to support online changing of role.
Using “nice” to slow some queries is an interesting problem. Some queries that run too slow can be less than nice. A long open snapshot blocks purge in InnoDB and causes undo to grow. A long running UPDATE statement might hold row locks longer than desired. Can you be more specific about how you will use this feature?
Mark,
Re: “nice”, I would throttle an operation based on disk I/O utilization or number of processes waiting on CPU. Under the assumption that your machine is (most of the time) not completely busy, this should give IMHO good behavior.
Niceness would be interesting for multi-minute running queries, such as bulk loads or ALTERs; for seconds-scale queries the problem is not so interesting (or interesting in another way).
Therefore one can re-caliber niceness on a second-granularity basis, e.g. every 1 sec. Perhaps for a second or two an operation would be intense, but would afterwards throttle/reduce-priority for the duration of a couple more seconds. Morgan suggests implementation might use tickets-like mechanism; I need to read more about it.
For system that run full power all the time, perhaps the correct solution is to pre-allocate resources; like max IOps.
I personally prefer state-based throttling and not configuration-based throttling, but I guess different workloads would do better with either.
If niceness is set per connection or per query, it might then be possible to ‘CANCEL NICENESS FOR QUERY 12345’ or similar. If niceness is as I suggested above, revisited every 1sec or whatever, then change to niceness would apply in reasonable time and you would feel safer having this escape route.
Throttling IO read rates for such operations is interesting and feasible. In the past we had problems from create index using buffered IO and generating too many dirty pages. Linux can cause problems with IO read stalls when there is as little as 1G of dirty pages to write back. Domas might remember how we solved that.