A more configurable MySQL appliance

[agathongroup]

I'm posting this with what is possibly an incomplete understanding of all of the ins and outs of the MYSQL5 appliance. Please be gracious if I've missed a critical piece of information! :)

We've frequently had to deal with branching the MySQL appliance on client grids to allow us to tweak /etc/my.cnf. MYSQL5 out of AppLogic 2.4.7 helps somewhat; /appliance/start_mysqld.pl generates a mysql.incl file that /etc/my.cnf includes. Unfortunately, this still falls somewhat short of being able to really tweak the database on a couple of different levels:


Changes to mysql.incl are overwritten, so you have to actually get into the Perl in start_mysqld.pl (meaning you have to know Perl in order to tweak the database).

Changes to ANYTHING in /appliance are unique to the instantiated appliance, and still require branching to commit to the catalogs.


I'm proposing that the !include in /etc/my.cnf be changed to use !includedir instead. Specifically:


Change /etc/my.cnf from "!include /appliance/mysql.incl" to "!includedir /mnt/data/.cnf-includes". (The name ".cnf-includes" was chosen in hopes of avoiding a data name collision.)

Change /appliance/start_mysqld.pl from using /appliance/mysql.incl to using /mnt/data/.cnf-includes/applogic.cnf (or similar) as its auto-generated config file. (The file extension ".cnf" is required by the !includedir directive as per http://dev.mysql.com/doc/refman/5.0/en/option-files.html.)

Change the documentation to encourage people to put their configuration directives into a file (ending in ".cnf") in the /mnt/data/.cnf-includes/ directory.


Alternatively, steps 1 and 2 can be achieved by altering start_mysqld.pl to add a line, "!includedir /mnt/data/.cnf-includes", to the mysql.incl file it generates.

Whatever the method, my understanding of the appliance is that some change is necessary to allow users to tweak their database servers with greater flexibility. Am I missing something? If not, is this a reasonable approach?

Thanks!
Peter

[PeterNic]

Peter,

Thanks for the suggestions. Yes, adding such an escape mechansim is perfectly reasonable; for example, we have introduced a similar approach in the WEBxxx appliances, by having both an optional config file and an optional .rc.local script that's executed on appliance startup. Since then, most people can use the WEBxxx appliances without having to branch it; typical config settings added have been cron job configs, rewrite commands, etc.

We will open a request to add this or similar capability to MYSQLxxx appliances -- and maybe the PGSQLxxx (PostgreSQL) appliances.

What we need to ensure is that we don't end up with all MySQL instances having to use this escape mechanism. The drawback is highly visible -- if you use this feature, you are tying yourself to the particular software version, possibly getting side effects, etc. Also, such tweaks are not visible in the editor (you can use the new Notes tab to describe what is required). So, when a custom config is necessary, all this is worth it (even this way, appliances are 100 times easier to use than standard server installations, right?).

What will help is: what do you find you have to modify frequently? If a few parameter / parameter combos are frequently used, it will be better to define these as properties of the appliance -- so that they will be easy to tweak.

It may be done straight -- just exposing a few .cnf parameters as properties, or it can be based on best practices. For example, in our PGSQL appliance, instead of exposing 3-4 pool size configuration parameters (which few people know how to set and must be changed whenever you change the amount of memory -- ouch!) -- we instead found a good best practices article that defined the ratios for these parameters based on the query complexity (which, in turn is driven by the intent -- OLTP, data mining, etc.). I like this better because it codifies the best practices of using the particular software and makes it available even to those who don't have 3 PhDs (and for those with 3 PhDs leaves enough brain cells to worry about more important things).

The best case is to add the parameters that are frequently changed/important and leave the escape option as an escape mechanism when special requirements are in place. At least the 70/30 rule should apply here: 70% of the instances shouldn't need the escape -- otherwise it is not really an escape mechanism :)

Any suggestions for the common/frequently changed parameters -- by Agathon and anyone else who uses MySQL -- will be highly appreciated, so we can make MySQL easier and better to use both for the casual users and the experts.

Best regards,
-- Peter

[earthgecko]

Just a very quick suggestion, seeing as I have some pressure to get a MySQL config for a production build.

Could 3tera not just add ALL the server system variables (permissible ones that is do not effect the fundamental appliance function from an AppLogic point of view) to the Property Values of the MYSQL and MYSQLR appliances, http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html.

This means that a uesr could change any of the defaults with the Class Editor or via the package descriptor. Just seems like the most functional and logical resolution to this problem.. yet 2 years later...

[PeterNic]

Earthgecko

That's quite a list!

Our goal is to make appliances easy to use -- and adding all the config parameters will not achieve that. I am also concerned with parameters that are not simple values but part of a group (apache is notorious for these, MySQL I think less so). If one has to get to that level, I think the config file is more expressive (with all the comments and examples that are usually embedded).

Peter:

If many experience the same need to branch MYSQL for extra tuning, we may have violated the second part of Einstein's simplicity edict ("Everything should be made as simple as possible, but not simpler.")

What I would propose to Agathon is one of the two options (and Peter, we can cover this tomorrow in person):

if there is a finite set of tuning parameter, just branch MySQL and create the Agathon version; if those are generic enough, I will be happy to include them in the base MYSQL appliance going forward
if these are all over the map, create a custom MYSQL appliance that takes an overrides file from the database volume, similar to the way the WEB5 apache allows extra config in apache.


Will this work better than what you have to do now?


Eathgecko: In any case, you can easily create such a MySQL appliance with all parameters exposed as properties. It would be a valid and useful appliance, I agree.



To all: We're trying to balance the set of appliances we can possibly maintain -- and always looking for ISVs and those who want to provide additional appliances, including variations on appliances already in the standard catalog. We do have a partner program for licensed appliances -- anyone with interest should contact me or info at 3tera.



Best regards,
-- Peter

[Karl]

I've not checked the default .cnf values for a while (not had to, only doing dev work with the mySQL appliances at the moment), but I'm guessing it is pretty stripped back. Ideally I think you need to to at least expose:

- InnoDB Settings
- Cache Settings
- Connection Settings
- Key Buffers/Temp/Heap Table sizes etc.

What I'd actually suggest, to keep things simple in such situations, is a secondary configuration tab on the settings page, called "Advanced" so that 99% of users don't see it, but those who need it don't have to mess about with branching - which is still more time consuming and requires more maintenance than using a stock appliance. If that were to happen, then I'd suggest exposing all possible variables.

At the end of the day, whilst simple is nice, the kind of people designing applications made up from the building blocks of appliances are going to be pretty techy and able to cope with these settings - If they aren't, then they are going to be even worse off if this doesn't happen. They ask a question, "How can I improve my app performance", to which they get a reply, "Check your mySQL config, change XXXX variable to Y" - They look, they find they have to branch an appliance to do so, they are then up the creak without a paddle. If the advanced tab is there, they can make that change at their own risk - without having to get someone else involved.

Thanks,

[agathongroup]

Maybe I'm not the target audience, but I sure prefer the user-definable config files instead of a parameter list as long as my arm. In fact, we've branched a few MYSQL appliances and are happily using them to search /mnt/data/.cnf/ for include-able config files. If we need another InnoDB tweak, we just edit a file on there. No branching, no searching a laundry list of parameters... just the .cnf editing we've been using for years.

I dunno, I find that easier (and so does our client that asked for this in the first place). YMMV. :-)

Peter