Topics marked with * relate to HQ Enterprise-only features.

This section provides instructions for setting up MySQL as your external HQ database. 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 HQ for evaluation, you can use HQ's built-in PostgreSQL, rather than set up an external database.

If you are new to MySQL, the introduction to MySQL at http://dev.mysql.com/tech-resources/articles/mysql_intro.html may be of interest.

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;
mysql> grant all on HQ.* to 'hqadmin'@'hq_server_host';

Configure MySQL Startup Options and System Variables

In this step, you configure the MySQL database, by editing the settings in its configuration file. In Unix and Linux, the file is /etc/my.cnf. In 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 = /home/mysql/log/mysqld.err
log = /home/mysql/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
server-id       = 1

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 log is flushed to the disk. Setting this value 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, use a value of 2 instead. 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---this is 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 here.

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 improve 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 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 OSs, the O_DIRECT flush method is good. Learn about other innodb flush methods.

innodb_flush_method=O_DIRECT
innodb_rollback_on_timeout=1

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

# sysctl -w vm.swappiness=30

or

# echo 30 >/proc/sys/vm/swappiness

 
10. Set the size of the query cache. 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. The default value here is 1M. If the qcache_hits-to-qcache_inserts ratio is low, raise this value.

query_cache_limit = 8M

 
12. HQ recommends a char encoding of utf-8.

default-character-set=utf8
collation_server=utf8_bin

Install the HQ Server

For instructions, see Installing HQ.

Tune the Batch Aggregate Inserter for MySQL*

NOTE:  Perform these steps only after installing the HQ Server.

These tuning recommendations are based on a performance tuning exercise in an environment with 700 HQ Agents reporting to an HQ 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';

Solve Problems with MySQL Configuration

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.

MySQL Maintenance Examples

Here are examples of regular maintenance for mysql

1. Simple MySQL Backup Script

   #!/bin/sh
   
   START=`date '+%A %Y/%m/%d %H:%M:%S'`
   DAY=`date +%A`
   MYSQLADMIN="/usr/bin/mysqladmin"
   MYSQLDUMP="/usr/bin/mysqldump"
   USER="root"
   PASSWORD="mysql"
   DBNAME="hqdb"
   DEST="/home/mysql/dumps/$DBNAME-$DAY.sql.gz"
   flushCmd="$MYSQLADMIN -u $USER -p$PASSWORD flush-logs"
   dumpCmd="$MYSQLDUMP -u $USER -p$PASSWORD --quick --single-transaction $DBNAME"
   gzip="gzip"
   echo "Starting backup: $START"
   echo "$flushCmd && $dumpCmd | $gzip > $DEST"
   $flushCmd && $dumpCmd | $gzip > $DEST
   END=`date '+%A %Y/%m/%d %H:%M:%S'`
   echo "Backup completed: $END"

2. Simple Log Rollover Scheme. This may be done with error files, log files, etc.

   cp /path/to/mysql/log/mysqld.err /path/to/mysql/log/mysqld-`date '+%w'`.err ;
   cp /dev/null /path/to/mysql/log/mysqld.err

3. Sample Unix Cron Entries (empty lines will fail in cron, beware)

   #
   #       Field 1: (0-59) minute
   #       Field 2: (0-23) hour
   #       Field 3: (1-31) day  of  the month
   #       Field 4: (1-12) month of the year
   #       Field 5: (0-6)  day of the week - 1=Monday
   # ----------------------------------------------------------------------------
   #
   0 2 * * * backup.sh
   0 1 * * * cp /path/to/mysql/log/mysqld.err /path/to/mysql/log/mysqld-`date '+%w'`.err ;
   cp /dev/null /path/to/mysql/log/mysqld.err