Set Up MySQL

Topics marked with * relate to features available only in vFabric Hyperic.

For information about maintaining a MySQL Hyperic database, see MySQL Maintenance Examples.

Set Up Hyperic Database on MySQL

This section has instructions for setting up MySQL as your external Hyperic database.

This task corresponds to Step 2 – Set Up Hyperic Database of Hyperic Installation and Startup Process.

It is assumed that you have already installed MySQL and are either familiar with MySQL or have the support of someone who is.

Note: If you are installing Hyperic for evaluation, you can use Hyperic's built-in PostgreSQL database, rather than set up an external database.

Step 1 - Create a MySQL Database Instance

Run these commands at the mysql prompt, as the root user:

mysql> create user 'hqadmin'@'<hq_server_host>' identified by '<passwd>';
mysql> create database HQ CHARACTER SET utf8 COLLATE utf8_bin;
mysql> grant all on HQ.* to 'hqadmin'@'<hq_server_host>';

UTF8 is required for encoding.

Step 2 - Configure MySQL Startup Options and System Variables

In this step, you configure the MySQL database by editing the settings in its configuration file. For Unix-like environments, the file is /etc/my.cnf. On Windows the file is my.ini, located in the MySQL installation base directory.

For more information about InnoDB startup options and system variables, see http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html.

  1. Enable the full query log. Every query (even ones with incorrect syntax) that the database server receives will be logged. This is useful for debugging, but it is usually disabled in production use. Be sure to change the paths given here to match your environment.

    [mysqld]
    log-error = mysqld.err
    log = mysql_general.log
  2. Print warnings to the error log file. If you have any problem with MySQL, you should enable logging of warnings and examine the error log for possible explanations.

    log_warnings
  3. Configure buffer pool size. The size of the MySQL buffer pool is has a significant impact on MySQL performance. If your database is on a dedicated machine, make the buffer pool about 80% of total memory.

    innodb_buffer_pool_size = 256M
  4. Configure the frequency with which the log buffer is written to the log, and the the point at which the log is flushed to the disk. Setting innodb_flush_log_at_trx_commit to 0 dramatically increases MySQL performance, but with this setting, you are likely to lose data in the event of a server crash. If loss of data is unacceptable, set innodb_flush_log_at_trx_commit to 2. Hyperic does not recommend setting the value to 1.

    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 64M
    innodb_log_file_size = 256M
  5. Configure innodb as the default storage engine. (Required.)

    default-storage-engine=innodb
    bulk_insert_buffer_size = 32M
    join_buffer_size = 8M
    max_heap_table_size = 256M
    tmp_table_size = 256M
    max_tmp_tables = 48
    myisam_sort_buffer_size = 256M
  6. Configure the sort buffer size. MySQL recommends a sort_buffer_size larger than the one suggested her.

    sort_buffer_size = 64K

    An article on experimenting with sort buffer size is available at http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-data-with-mysql.

  7. Configure the read buffer size.Because Hyperic does a significant volume of sequential reads, a large read buffer improves performance.

    read_buffer_size = 1M
    read_rnd_buffer_size = 10M
    table_cache = 2048
    set-variable = max_connections=400
    key_buffer_size = 256M
    thread_cache_size = 32
  8. Configure the number of threads that can run in the InnoDB kernel. A starting point for setting this value is to set a value equal to 2 times the number of CPUs times the number of disks.

    innodb_thread_concurrency = 8
  9. Set the method that is used to flush data and log files. For battery-backed-up storage with write-back cache mode on Linux systems, the O_DIRECT flush method is good. For information other other innodb flush methods see http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_flush_method.

    innodb_flush_method=O_DIRECT
    innodb_rollback_on_timeout=1

    On Windows the flush method is always async_unbuffered. You do not need to set innodb_flush_method=O_DIRECT on Windows platforms.

    • In this situation, tune your Linux OS (version 2.6 or higher) to favor the use of main memory rather than file caches with either:

      # sysctl -w vm.swappiness=30
    • or

      # echo 30 >/proc/sys/vm/swappiness
  10. Set query cache size. Generally, the higher this value, the better the performance. However, in MySQL versions older than 5.0.50, beware of setting this variable too high, as it may cause the database to pause. For more information, see the bug description at http://bugs.mysql.com/bug.php?id=21074.

    query_cache_size = 0
  11. Set query cache limit. The default value here is 1M. If the qcache_hits-to-qcache_inserts ratio is low, raise this value.

    query_cache_limit = 8M
  12. Set character encoding. Hyperic requires a char encoding of utf-8.

    default-character-set=utf8
    collation_server=utf8_bin
    character_set_system=utf8

Step 3 - Verify Database Setup

instructions tbd

After the database is created, you can set up Hyperic Server following the instructions in Step 3 - Set Up Hyperic Server of the Hyperic Installation and Startup Process.

Solve Problems with MySQL Startup

If MySQL fails to start and issues a message similar to this:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
080403  8:06:13 ERROR Default storage engine (InnoDB) is not available
080403  8:06:13 ERROR Aborting

the actual log size does not match the configured log size.   

Delete the log files in /var/lib/mysql/ and restart MySQL.

Tune the Batch Aggregate Inserter for MySQL

Do this tuning after installing the Hyperic Server.

These tuning recommendations are based on a performance tuning exercise in an environment with 700 Hyperic Agents reporting to an Hyperic Server on an 8 way / 16 GB host with an MySQL database running on an 8 way / 8 GB host, each running CentOS 5, with

  • Workers: 4

  • QueueSize: 4000000

  • BatchSize: 2000

With 7 hours of backfilled data the server peaked out at 2.2 million rows inserted.

This intent of the strategy was to keep the Batch Aggregate Inserter (BAI) on "cruise control", instead throwing threads at the queued metrics all at once and causing CPU spikes.

It was found that the BAI workers had no trouble keeping up with the "normal" incoming load, and in a catchup scenario (after backfilling) the high Queue Size allowed them plenty of time to catch up.

For a smaller deployment, consider only tweaking the number of workers down to 1 or 2. This will ease random CPU spikes and MySQL should have no problem keeping up with the incoming traffic.

Please NOTE these settings may not be applicable to PostgreSQL and Oracle since MySQL handles catchup scenarios much more gracefully.

To update the Batch Aggregate Inserter settings for MySQL run these commands at the mysql prompt as the hqadmin user:

mysql> update HQ.EAM_CONFIG_PROPS set propvalue = 4 where propkey = 'BATCH_AGGREGATE_WORKERS';
mysql> update HQ.EAM_CONFIG_PROPS set propvalue = 2000 where propkey = 'BATCH_AGGREGATE_BATCHSIZE';
mysql> update HQ.EAM_CONFIG_PROPS set propvalue = 4000000 where propkey = 'BATCH_AGGREGATE_QUEUE';