innodb_buffer_pool_size: Not configurable and default is not suitable for all the deployments.

Configuring innodb_buffer_pool_size based on the dataset size is pointless - the automatic configuration should relay on the available amount of RAM. (Otherwise you could say your dataset would be 100GB, and you're deploying on 32GB machine, which would make mysql use more RAM than there is available).

So, a proper way to calculate innodb_buffer_pool_size would be in the percentage of the RAM you have available on the box.

Mario,

I understand your suggestion, but I think we should not enforce that decision, if a given user
wants to use a dataset-size of 100GB on a 32GB machine, then is up to the operator, there are many assumptions
that we could be doing wrong ( perhaps is adding Swap memory? or stressing an installation?).

I think the best we can do on that case is to warn the user about his decision.

        if dataset_bytes > self.get_mem_total():
            log("Dataset has been set to a value greater than the available RAM",
                level=WARN)

Also offering the option to manually adjust the dataset-size and innodb_buffer_pool_size to a specific memory amount
would be enough for covering most of the use cases.

Opinions?

I'm not sure why dataset-size option is even there - one should configure innodb_buffer_pool_size based on the amout of RAM the machine has - the datasize is pretty irrelevant. If the dataset is smaller than the configured innodb_buffer_pool, then mysql will not use 'extra' configured memory. If it is larger (which is not uncommon on large datasets) only portion of the dataset will fit inside buffer pool.

Ideally you'd want to set innodb_buffer_pool_size cca 10-15% larger than your dataset. But, as explained above, there is no harm making it larger than the dataset.

The limiting factor is the amount of RAM, not the dataset size. That is why I'm suggesting to completely remove that option from the charm, as it is misleading.

What a operator should take into consideration is are there going to be other services deployed on the unit where mysql is runnig? If yes, then mysql should be given less memory.

I'd say sane default should be 50% of available RAM - although a operator should be aware of the option and tune it.

Mario

After discussing this on this thread we concluded a few things:

- Using 50% of available memory for setting innodb buffers seems to best setting altogether with
having this as a configuration directive.
- Removed query cache ( not supported on percona-cluster )
- Removed the preferred engine setting because only InnoDB is supported

Quick 2 cents, Mario with regards to removing config options, we don't yet have a way to completely remove config options while still being able to support upgrades for people that may still be using them. So, for the moment we have to keep all existing config options but there is no harm in updating the config.yaml description to indicate that this option should no longer be used and recommend that it be set a null setting or suchlike. We are aiming to have a solution for deprecating config options soon though.

Adding my thoughts here:

First, settings such as this are tuning parameters, which probably isn't best to decide what a good value for it is at install time as there's no data. I think we should definitely create a juju action which can make some recommendations for tunable values. There's a few interesting posts out there on this, but this one has my attention at this point is [0], although the mentioned link in the bug is definitely an interesting read.

[0] http://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

Secondly, I'm not sure I agree with comment indicating that it will only use what it needs to use as I believe there's been a sighting in which it was using 100/128 GB RAM for a 1.2 GB database.