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.
Run these commands at the mysql prompt, as the root user:
UTF8 is required for encoding.
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.
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.
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.
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.
5. Configure innodb as the default storage engine---this is required.
6. Configure the sort buffer size. MySQL recommends a sort_buffer_size larger than the one suggested her.
An article on experimenting with sort buffer size is available here.
7. Configure the read buffer size. Because Hyperic does a significant volume of sequential reads, a large read buffer improve performance.
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.
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.
In this situation, tune your Linux OS (version 2.6 or higher) to favor the use of main memory rather than file caches:
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.
11. The default value here is 1M. If the qcache_hits-to-qcache_inserts ratio is low, raise this value.
12. HQ requires a char encoding of utf-8.
For instructions, see Installing HQ.
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:
If MySQL fails to start and issues a message similar to this:
the actual log size does not match the configured log size.
Delete the log files in /var/lib/mysql/ and restart MySQL.
Here are examples of regular maintenance for mysql
- Simple MySQL Backup Script
- Simple Log Rollover Scheme. This may be done with error files, log files, etc.
- Sample Unix Cron Entries (empty lines will fail in cron, beware)