In a recent blog post, Zenoss Community member Scott Haskell pointed to an entry he had made in the Zenoss wiki for How to Convert MySQL to use InnoDB Tables. This would prove useful if you had initially installed Zenoss with MySQL configured for another engine like MyISAM, probably from a source build. InnoDB gives you the distinct advantage of ACID compliance and greater reliability.
Here are Scott’s steps to enable InnoDB, in MySQL and convert the tables.
Backup your existing events database, just in case.
As root:
mysqldump events > events.sql
Stop Zenoss and MySQL
/etc/init.d/zenoss stop
/etc/init.d/mysql stop
Verify your existing tables for the Zenoss events database
mysql events -e 'show table status \G'
*************************** 1. row ***************************
Name: alert_state
Engine: MyISAM
...
...
Edit your appropriate (existing or not) my.cnf file and enable Innodb tables. I use my-huge.cnf as I have plenty of memory in my system (8 gigs). The cnf files can be found in the support folder of the mysql source tar file.
Un-comment the lines below in /etc/my.cnf.
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/var/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var/
innodb_log_arch_dir = /usr/local/mysql/var/
Start MySQL
/etc/init.d/mysql start
Verify that InnoDB Engine is supported
mysql -e 'show engines'
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+----------------------------------------------------------------+
If InnoDB support is set to ‘NO’, you’ll need to remove the ibdata* and ib_log* files and restart MySQL. The restart might take a while due to the initial index build.
/etc/init.d/mysql stop
cd /path/to/mysql/var
rm ib_log*
rm ibdata*
/etc/init.d/mysql start
Copy the text below and save it to a file on your zenoss server. Call it something like innodb_events.
alter table alert_state type = innodb;
alter table detail type = innodb;
alter table heartbeat type = innodb;
alter table history type = innodb;
alter table log type = innodb;
alter table status type = innodb;
As root:
mysql events < innodb_events
This will alter the tables and convert them to InnoDB.
Restart Zenoss
/etc/init.d/zenoss start
Verify that the tables are now innodb
mysql events -e 'show table status \G'
*************************** 1. row ***************************
Name: alert_state
Engine: InnoDB
Version: 10
...
...
You’re all set! If you mess up, you can always revert with your backed up events.sql file.
