Edit-MySQL-WEB

---------                                                                                                                                         http://logch.blogspot.com/2017/02/install-cacti-spine-in-centos-7-with.html

Cacti official setup documentation: http://docs.cacti.net/manual:100:1_installation#requirements

Pre-req:

yum install net-snmp

yum install netsnmp-devel

yum install net-snmp-utils

yum install mariadb

yum install mariadb-devel

yum install gcc

yum install help2man

yum install rrdtools

yum install php

yum install php-snmp

yum install php-process

yum install php-mysql

yum install php-pdo

yum install php-ldap

yum install php-mbstring

yum install php-xml

yum install git

NOTE: make sure your system timezone and php.ini timezone is setup correctly (I have carelessly set a wrong system timezone and took me a lot of time to find out my data in rrd file is set in the wrong timeslot...)

In my case, type the following command "timedatectl set-timezone Asia/Hong_Kong"

vi /etc/php.ini

  => date.timezone = Asia/Hong_Kong

Download the sources

Visit Cacti official home and check on latest versions and patches: http://www.cacti.net/

wget http://www.cacti.net/downloads/cacti-1.0.1.tar.gz

tar xvzf cacti-1.0.1.tar.gz

mv cacti-1.0.1 /var/www/html/cacti

## make sure apache have full access to the folder

chown -R apache:apache /var/www/html/cacti

## Configure database and install cacti

# set root password

mysqladmin password

cd /var/www/html/cacti

vi include/config.php  ## change username/password as you like, which you would be using for database privileges setup

mysql -p

  > CREATE DATABASE cacti

  > grant all privileges on cacti.* to cactiuser@'localhost' identified by 'cactiuser';

  > grant select on mysql.time_zone_name to cactiuser@'localhost' identified by 'cactiuser';

## tuning Maria DB configurations (the syntax is just like mysql, so it is quite easy)

/etc/my.cnf.d/client.cnf

[client]

default-character-set=utf8

## this is just an example, you may need much larger values if you have a large site

## heap table size is specifically for performance enhancement, please refer to the following URL for details

http://logch.blogspot.hk/2017/02/tuning-cacti-10-performance-for-medium.html

/etc/my.cnf.d/server.cnf  [mysqld]

collation-server = utf8_general_ci

init-connect='SET NAMES utf8'

character-set-server = utf8

max_heap_table_size = 256M

max_allowed_packet = 16777216

tmp_table_size = 64M

join_buffer_size = 64M

innodb_file_per_table = on

innodb_doublewrite = off

innodb_additional_mem_pool_size = 80M

innodb_flush_log_at_trx_commit = 2

## create tables

mysql -p cacti < cacti.sql

## populate mysql time_zone_name table

mysql_tzinfo_to_sql  /usr/share/zoneinfo | mysql -p mysql

## now open a browser and open "http://your_server_ip/cacti/" and just follow the instructions.  If it says you are missing some modules, just yum install them.

https://www.youtube.com/watch?v=rqK5OnbF1BY

## Compile spine

wget http://www.cacti.net/downloads/spine/cacti-spine-1.0.1.tar.gz

tar xvzf cacti-spine-1.0.1.tar.gz

cd cacti-spine-1.0.1

./configure

make

make install

## Use spine

cd /usr/local/spine/etc/

(1) copy the "spine.conf.dist" to "spine.conf", and modify the DB related parameters inside, e.g. db name, user/pass(2) Then in Cacti UI -> Settings -> Paths, input the spine directories, e.g.Binary: /usr/local/spine/bin/spineConfig: /usr/local/spine/etc/spine.conf(3) Settings -> Poller, choose spine instead of cmd.php

## Download thold plugin

# chdir to cacti plugins folder

cd /var/www/html/cacti/plugins

# download the source

git clone -b master https://github.com/Cacti/plugin_thold.git

# rename it to thold, yes it matters...

mv plugin_thold thold

Install Thold from the UI and a simple example can be found in video below (start at around 9:15)

https://www.youtube.com/watch?v=rqK5OnbF1BY

Upgrade from Cacti 1.0.1 to Cacti 1.0.x

# cd /var/www/html

# wget http://www.cacti.net/downloads/cacti-1.0.3.tar.gz

# tar xvzf cacti-1.0.3.tar.gz

# mv cacti cacti-1.0.1-old

# mv cacti-1.0.3 cacti

Update cacti/include/config.php on the database username and password

Open a web browser and open cacti url, e.g. http://x.x.x.x/cacti/, follow the steps and choose upgrade.

# cp -p cacti-1.0.1-old/rra/* cacti/rra/

Also copy whatever you have added by yourself from cacti-1.0.1-old/scripts/* and cacti-1.0.1-old/resource/* to cacti/scripts and cacti/resource.  That's it.

Upgrade from Cacti 1.0.x to Cacti 1.1.x

It should be pretty much the same

# cd /var/www/html

# wget http://www.cacti.net/downloads/cacti-1.0.3.tar.gz

# tar xvzf cacti-1.1.2.tar.gz

# mv cacti cacti-1.0.3-old

# mv cacti-1.1.2 cacti

Update cacti/include/config.php on the database username and password

Open a web browser and open cacti url, e.g. http://x.x.x.x/cacti/, follow the steps and choose upgrade.

Sample screen you would see in browser

-------------

Tuning Cacti 1.0.x performance for large sites, running on CentOS 7 with MariaDB

Menu => Settings => Poller

Poller Type => spine

Menu => Settings => Paths

Spine Binary File Location => /usr/local/spine/bin/spine

Spine Config File Path => /usr/local/spine/etc/spine.conf

We don't have a config file yet, so

# cp /usr/local/spine/etc/spine.conf.dist /usr/local/spine/etc/spine.conf

# vi /usr/local/spine/etc/spine.conf

... modify DB_User, DB_Pass etc

Menu => System Utilities => View Boost Status

By default, cacti 1.0.1 has chosen InnoDB engine for Boost Storage, and that is why it shows unlimited on table size and maximum records (well, unlimited as long as your hard disk has enough free space).  As I remember, the DB engine used to be "MEMORY" in Cacti 0.8.x with the "boost" plugin, which would give maximum performance with some limitations. First, the maximum allowed table size and records would have a limit, i.e. the amount of RAM, and you really don't want to go over that limit.  Second, you will have to do some calculations in order to have the configuration set correctly.

...

...

Steps:

Goto Menu => Settings => Performance

My initial settings are

- Enable On-demand RRD Updating => Checked

- How Often Should Boost Update All RRDs => 1 hour

- Maximum Records  => 100000

- Enable direct population of poller_output_boost table => Checked

- Enable Image Caching => Checked

- Location for Image Files  => /var/www/html/cacti/cache/boost/

Configure database

Configure heap table size, and restart MariaDB.  "128M" is just an example, it is likely to be on gigabyte level for large sites.

#vi /etc/my.cnf.d/server.cnf

[mysqld]

max_heap_table_size = 128M

systemctl restart mariadb

# mysql -p cacti

> alter table poller_output_boost ENGINE=MEMORY;

Now go back to Menu => System Utilities => View Boost Status

The engine type has become "MEMORY" and the table size is no longer unlimited. 

If you do some math, you may notice that the data is taking more storage than it should.  This is because in MySQL/MariaDB, MEMORY tables use fixed-length row-storage format. So the default varchar(512) in the output column would just become char(512) and thus waste a lot of space.  If you know your polling results would not exceed a certain length, please do adjust the varchar length for the output column.

e.g. alter table poller_output_boost modify column output varchar(128);

So we have "Maximum records" set to 100,000 and update interval set to hourly.  Now you have to calculate whether your heap table can hold 100000 records or not.  You see the "Estimated Maximum Records" is only 4.957K here, and say "oh no it's gonna blow up my heap table".  Well this is not exactly true.  The "Avg bytes/record" is 25K bytes, which is not possible if you look at the poller_output_boost table structure seriously.   This is only because database system is allocating memory in chunks, in this case ~128K a time.

From the "show table..." statement below, we know that the maximum data length is 109506040, and the average row length is 452. So we have 109506040 / 452 bytes => 200,000+ records

> desc poller_output_boost;

+---------------+-----------------------+------+-----+---------------------+-------+

| Field         | Type                  | Null | Key | Default             | Extra |

+---------------+-----------------------+------+-----+---------------------+-------+

| local_data_id | mediumint(8) unsigned | NO   | PRI | 0                   |       |

| rrd_name      | varchar(19)           | NO   | PRI |                     |       |

| time          | timestamp             | NO   | PRI | 0000-00-00 00:00:00 |       |

| output        | varchar(128)          | YES  |     | NULL                |       |

+---------------+-----------------------+------+-----+---------------------+-------+

> show table status where name ='poller_output_boost';

+---------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| Name                | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |

+---------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| poller_output_boost | MEMORY |      10 | Fixed      |   35 |            452 |      127224 |      109506040 |         1596 |         0 |           NULL | 2017-02-12 23:50:52 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         |

Finally, since the intermediate polling results are in memory, please remember to flush the data when you need to restart the database or the server.

# cd /var/www/html/cacti

# php poller_boost.php  --force

--