Saturday, October 20, 2012

MySQL AutoTuner

After reading a blog post about MySQL Tuning scripts I thought about the possibility of a fully Automatic MySQL Tuner.

This is how it would work:
A daemon which would connect to your database server and then fetch status variables, just like mysqltuner and such. Then the daemon could decide that a parameter would need to be adjusted and then run "SET GLOBAL …" and write a /etc/mysql/autotuner.cf file which should be included in your my.cnf.

It should have a min/max setting for each option and some thresholds.

Why?
  • Not everyone is a DBA
  • It's could better than the default settings is most cases. Luckily many defaults are updated in 5.6.
  • You're not using my-huge.cf, are you?
  • It could help when there are changing workloads
  • It might be sufficient for a developer environment
  • MySQL might be embedded in a 'virtual appliance' which can be deployed on may different kinds of hardware.
Why not?
  • The risk of it taking a wrong decision is too high
  • It might be better then the defaults, but still worse than a trained DBA
  • A few important settings (InnoDB Buffer Pool Size and InnoDB Logs) require a restart. I don't like anything to restart MySQL automatically (does your puppet config do automatic restarts?)
  • It might generates differences between servers with the same role. This might not be an issue when the autotuner would use P2P/multicast to communicate with other autotuners, but that makes it even more scary
  • I don't like bolted-on solutions
The conclusion is that I don't think this is a good solution. 

3 comments:

  1. I think "changing workloads" is the key issue here.
    I do not think there is one single best configuration, since peak hours are associated with one type of workload, and off-peak hours are associated with others (more maintenance, more DWH, nightly large reporting queries etc.).

    In this respect I believe a server should take care of itself (SQL server does this). Otherwise, I don't think it is that bad an idea to let an auto-tune script do its work; it has to have its limitations, the boundaries within which it can operate; it might be told in advance about expected workloads so it can have a more educated guess about "ok, seems like reporting time is started".

    There is much work to be done in this field.

    ReplyDelete
  2. I agree. Changing workloads would be a real attraction for such a process. Perhaps it could be a MySQL Plugin rather than a Daemon. I've always thought "static" tuning variables are a bit off. A system has to be running for some time to tune it for the "Average" load. But what about the late night backup as apposed to full traffic. These are different for sure. Maybe tuning for the Big Load would be better but then backups take all night.

    I have sometimes tuned a slave for backups and the master for load but this is not good for HA.

    You thinking of starting a project?

    ReplyDelete
    Replies
    1. I've thought about starting a project, but I don't think I have enough time and resources for it.

      Delete