Suggested changes to MySQL for ZM

[bbunge]

I've been testing some changes to MySQL. Here is a write up I added to the Easy How to for ZM 1.28.0 on Ubuntu 14.04:

Note: If you already have ZM running on a system adding this setting will not change where MySQL stores ZM data. I have successfully converted a running ZM installation to "innodb_file_per_table" and plan to publish my procedure soon.

Suggested changes to MySQL
For most of you Zoneminder will run just fine with the default MySQL settings. There are a couple of settings that may, in time, provide beneficial especially if you have a number of cameras and many events with a lot of files. One setting I recommend is the "innodb_file_per_table" This will be a default setting in MySQL 5.6 but should be added in MySQL 5.5 which comes with Ubuntu 14.04. A description can be found here:

http://dev.mysql.com/doc/refman/5.5/en/ ... paces.html

To add "innodb_file_per_table" edit the my.cnf file:

nano /etc/mysql/my.cnf

Under [mysqld] add

innodb_file_per_table

Ctrl+o Enter to save

CTRL+x to exit

Restart MySQL

service mysql restart

As you add cameras to your system and the events build up you may need to increase the size of the "innodb_buffer_pool_size" when this setting runs out of space your system may act like it is out of disk space. One way to check this is to install mysqltuner and run it from the command line. When the results are shown you can edit the my.cnf file and change the buffer size. The default "innodb_buffer_pool_size" is 128M. In the ZM forum I have
found a recommended setting of 256M. My production server required a setting greater than 410M. Install and run mysqltuner to determine the recommended size for your system. Be sure to restart MySQL after making changes.

[bbunge]

If you have already installed ZM here is a procedure to convert the database:
http://www.zoneminder.com/wiki/index.ph ... Zoneminder