add tuning options to MySQL for performance

[Matthew Mosesohn]

We need to make the following changes to MySQL deployment with Galera:
wsrep_slave_threads=4
innodb_buffer_pool_size=((30% of memory))
innodb_thread_concurrency=0
innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_io_capacity=500
max_connections=3000
table_cache=10000

It greatly improves performance under heavy load.

[Jason Venner (jvenner-e)]

From an operations/health check perspective, can we build in a health check that lets us know if MySQL or Galera is having a performance issue?

[Bogdan Dobrelya (bogdando)]

Please consider the following tuning as well
http://www.percona.com/live/nyc-2012/sessions/galera-cluster-best-practices
- use wsrep_sst_xtrabackup instead of wsrep_sst_mysqldump we are using atm (fuel/deployment/puppet/galera/templates/wsrep.cnf.erb:wsrep_sst_method=mysqldump)
- use dedicated reference node for full-state transfer donor and backup activities

[Matthew Mosesohn (raytrac3r)]

We don't have percona tools built so we can't use xtrabackup yet.

[Dmitry Borodaenko (angdraug)]

I think we should add precona tools to the distro if we are to stay with MySQL.

[Matthew Mosesohn (raytrac3r)]

Dmitry, it looks like we will start researching Postgres starting next week. I don't see the value of moving over to Percona if we are looking into postgres. These changes in this change request are really quite tiny, but helpful.

Dmitry, if you want to pursue Percona XtraDB and their tools, you can start a blueprint for it.

[Dmitry Borodaenko (angdraug)]

No, researching Percona is only worthwhile if we end up NOT moving away from MySQL altogether.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/70959
Committed: https://git.openstack.org/cgit/stackforge/fuel-library/commit/?id=e4a76f20ae8cc39fa672347f95c5995f8baa6d08
Submitter: Jenkins
Branch: master

commit e4a76f20ae8cc39fa672347f95c5995f8baa6d08
Author: Matthew Mosesohn <email address hidden>
Date: Mon Jan 20 14:03:15 2014 +0400

    Added MySQL tuning parameters for galera

    Change-Id: I33c72eacbce8c333fa7e05aca61294ba07901a5d
    Closes-Bug: #1270875

[Nikolay Fedotov (nfedotov)]

Verified on ISO {"build_id": "2014-02-20_12-38-56", "mirantis": "no", "build_number": "169", "nailgun_sha": "1cafb7c9a81946a056dcaa6554d48bf396c90e9e", "ostf_sha": "380d376b8f16d1cf040b7cabbe9133fd0dcbeadd", "fuelmain_sha": "15637d29a59f299ee8ffe6560245a6884e954cbe", "astute_sha": "3d43abeefb60677ce6cae83d31ebbba1ff3cdbe2", "release": "4.1", "fuellib_sha": "35299a0aa5c9f75ee20c5b05003403a3d51af11c"}