Buying a Air Purifier

Why? Because I have two cats, and I have allergic problems.  Well, nothing I can't live with, but the worse part is my cat has feline asthma.

After google-ing for a while, it seems those with multiple filters should be better, e.g. front filter for larger particles, active carbon filter in the middle for finer particles, and HEPA filter for PM2.5 particles.  HEPA is the crucial component, but it also comes with a limited lifespan, and it is expensive...  You cannot wash as it would ruin its structure and makes it useless.  Some people mentions you can vacuum clean it, but usually is not very effective.

Some brands have claimed their HEPA filters have 10 years lifespan... Obviously the filter would have a limited capacity for PM2.5 particles, and you can't wash it.  Can it really last that long?  Many brands would let you download their operation manual online.  I found one famous brand has the following assumptions:

Filter life is based on smoking 5 cigarettes/per day (10 years).   But according to the Specification of The Japan Electrical Manufacturer's Association JEM1467 if based on the conidtion of smoking 10 cigarettes per day, the replacement period is about 5 years.

Ok, more google-ing finds that 1 cigarette is around 12 mg of PM2.5 (I'm actually not too sure if this is correct: http://www.myhealthbeijing.com/pollution/is-pm2-5-from-air-pollution-the-same-as-from-smoking/)

The air purifier spec should tell you how much cubic meter of air it would handle per hour, the particular one I'm looking at has 150 cubic meter/hour.  So let's do some math

Air flow volume: 150 cubic meter/hour
PM2.5 in the place I live: ~ 45 μg/cubic meter

150 x 45 = 6750μg = 6.75mg/hour = 0.5625 cigarette

open 24 hour/day = 13.5 cigarettes/day =>  3.7 years
75% of theoretical figure ~ 2.8 years
50% of theoretical fiture ~ 1.85 years

open 12 hour/day = 6.75 cigarettes/day => 7.4 years
75% of theoretical figure ~ 5.6 years
50% of theoretical fiture ~ 3.7 years

Well, the figures doesn't seem too sad although it could quite far off the claimed lifespan under certain conditions and assumptions T_T


I couldn't help to further find out how much PM2.5 I'm inhaling per day though it's off topic...

an average person inhales ~ 0.5 cubic meter/hour at rest, 0.7 cubic meter/hour at light activity (maybe walking)
https://en.wikipedia.org/wiki/Respiratory_minute_volume

Take the average: 0.6 x 45 x 24 = 0.648mg  :D

Cacti - Using Data Query

Cacti's documentation is actually pretty comprehensive, and I would strongly recommend it.  I still write this only because I feel some parts may not be detailed enough in the doc.
Here is the URL for "data queries"
http://docs.cacti.net/manual:100:3a_advanced_topics.3_data_queries#data_queries

Data Query is a very useful and powerful tool to create graphs. There are basically two ways to do it in Cacti - (1) With SNMP, (2) With a script

With an external script, you can virtually do anything with it to accomplish some very complicated tasks.  But whenever it is possible to be done in SNMP query, please do it.  Running spine with pure snmp calls is often much more efficient than calling external scripts.

SNMP Query

(1) XML Template File:

• Define an index
  <oid_index>, <oid_index_parse>, <oid_suffix>
• Define input parameters
• Define output parameters

IF-MIB example:
This is by far the simplest type and is bundled with Cacti in "resource/snmp_queries.interface.xml".  First, it has a OID that describes all its indexes
<oid_index>.1.3.6.1.2.1.2.2.1.1</oid_index>
e.g. in my VM, snmpwalk -v2c -c xxxxxx localhost .1.3.6.1.2.1.2.2.1.1
IF-MIB::ifIndex.1 = INTEGER: 1
IF-MIB::ifIndex.2 = INTEGER: 2

And then we would use the index obtained from <oid_index> to reference other OIDs

e.g. Description

snmpwalk -v2c -c xxxxx localhost .1.3.6.1.2.1.2.2.1.2

IF-MIB::ifDescr.1 = STRING: lo

IF-MIB::ifDescr.2 = STRING: enp0s3

e.g. OutOctets

snmpwalk -v2c -c xxxxx localhost .1.3.6.1.2.1.2.2.1.16
IF-MIB::ifOutOctets.1 = Counter32: 2792348  <= this is "lo" out octets
IF-MIB::ifOutOctets.2 = Counter32: 8936144  <= this is "enp0s3" out octets


However, reality is cruel and the MIB world isn't always so nice...  what if the index OID doesn't exist?  Take IF-MIB as an example, let's pretend that "<oid_index>.1.3.6.1.2.1.2.2.1.1</oid_index>" isn't available, what can we do?  If you look at the "Description" OID again, you may have noticed that it already contains the "index" as part of its OID!
snmpwalk -v2c -c xxxxx localhost .1.3.6.1.2.1.2.2.1.2
IF-MIB::ifDescr.1 = STRING: lo
IF-MIB::ifDescr.2 = STRING: enp0s3

Let's convert it to raw format to have a clearer look (-On)
snmpwalk -v2c -c public -On localhost .1.3.6.1.2.1.2.2.1.2
.1.3.6.1.2.1.2.2.1.2.1 = STRING: lo
.1.3.6.1.2.1.2.2.1.2.2 = STRING: enp0s3

Here comes the "<oid_index_parse>" parameter, we want to extract part of the OID as the index. One thing though, to use this parameter you should at least have some basic knowledge on regular expression.
e.g.
<oid_index>.1.3.6.1.2.1.2.2.1.2</oid_index>
<oid_index_parse>OID/REGEXP:.*\.([0-9]{1,})$</oid_index_parse>

Now, in interface.xml, if you replace:
        <oid_index>.1.3.6.1.2.1.2.2.1.1</oid_index>
        <oid_num_indexes>.1.3.6.1.2.1.2.1.0</oid_num_indexes>

With:

        <oid_index>.1.3.6.1.2.1.2.2.1.2</oid_index>
        <oid_index_parse>OID/REGEXP:.*\.([0-9]{1,})$</oid_index_parse>

It should just work fine with the same behavior.

Below is some further examples from Cacti Doc, strongly recommended to go through it.
http://docs.cacti.net/howto:data_query_templates

After you are done with the index part, the rest is relatively simple. Input are parameters that would help you create and describe your graph, while output are values that would actually appear in your graphs. Read through interface.xml, and create some graphs then you would fully understand.  To do it, just create a new device in "Management->devices", or simply use "Local Linux Machine" as a starting point.  Go to "Associated Data Queries" at the bottom, select "SNMP - Interface Statistics" and click "Add".  Then click "Create Graphs for this Device" near the top right corner.

One more note on the output parameter, some OIDs may not simply append the "index" part to the end... e.g. 1.2.3.4.5.6.index.1
<Sample> 
<name>Sample1</name> 
<method>get</method> 
<source>value</source> 
<direction>output</direction> 
<oid>.1.2.3.4.5.6</oid> 
<oid_suffix>1</oid_suffix> </Sample> 


Script Data Query

The idea is the same, but we do it in a script rather than the built-in snmp engine.  Take "resource/script_server/host_cpu.xml" and "scripts/ss_host_cpu.php" as example.

script to run
<script_path>|path_cacti|/scripts/ss_host_cpu.php</script_path>

function name
<script_function>ss_host_cpu</script_function>

arguments
<arg_prepend>|host_hostname| |host_id| |host_snmp_version|:|host_snmp_port|:|host_snmp_timeout|:|host_ping_retries|:|host_max_oids|:|host_snmp_community|:|host_snmp_username|:|host_snmp_password|:|host_snmp_auth_protocol|:|host_snmp_priv_passphrase|:|host_snmp_priv_protocol|:|host_snmp_context|</arg_prepend>

These refer to "$cmd" in the script
<arg_index>index</arg_index>
<arg_query>query</arg_query>
<arg_get>get</arg_get>
<arg_num_indexes>num_indexes</arg_num_indexes>

The <query_name> paramters refer to "$arg1" in the script
<hrProcessorFrwID>
    <name>Processor Index Number</name>
    <direction>input</direction>
    <query_name>index</query_name>
</hrProcessorFrwID>
<hrProcessorLoad>
    <name>Processor Usage</name>
    <direction>output</direction>
    <query_name>usage</query_name>
</hrProcessorLoad>

Now we look at the "scripts/ss_host_cpu.php", you will find it has a funciton:
function ss_host_cpu($hostname, $host_id, $snmp_auth, $cmd, $arg1 = '', $arg2 = '')

Let's take a look at the argument part, $hostname refers to |hostname|, $hostid to |host_id|, $snmp_auth to the long colon separated snmp parameters. $cmd is the action, $arg1 is the "query_name" and $arg2 is the index obtained from "index" cmd.

Test by calling from command line:
# php -q scripts/ss_host_cpu.php 127.0.0.1 1 1:161:500:1:5:"my_community":"":"":"":"":"":"" index
0
1
2

3

# php -q scripts/ss_host_cpu.php 127.0.0.1 1 1:161:500:1:5:"my_community":"":"":"":"":"":"" num_indexes
4

Query CPU indexes
# php -q scripts/ss_host_cpu.php 127.0.0.1 1 1:161:500:1:5:"my_community":"":"":"":"":"":"" query index
0!0
1!1
2!2
3!3

Query CPU usage
# php -q scripts/ss_host_cpu.php 127.0.0.1 1 1:161:500:1:5:"my_community":"":"":"":"":"":"" query usage
0!1
1!1
2!1
3!1

Well, does it look right?  Let's make the CPU busy and try again.
run a busy loop by
# while [ 1 ]; do echo 123 > /dev/null; done

# php -q scripts/ss_host_cpu.php 127.0.0.1 1 1:161:500:1:5:"my_community":"":"":"":"":"":"" query usage

0!1

1!1
2!41
3!1

Get individual

# php -q scripts/ss_host_cpu.php 127.0.0.1 1 1:161:500:1:5:"my_community":"":"":"":"":"":"" get usage 0

1

Once again, do read the official doc if you have time.
http://docs.cacti.net/manual:100:3a_advanced_topics.3d_script_data_query_walkthrough#script_data_query_walkthrough

After you're done with the xml file, go to "Data Queries" and create a new one that points to your xml file.

Unfortunately, we are still few steps away from creating a graph with "Data Query", but the rest parts are pretty straight forward. The easiest way to understand it is to go through the network interface examples again, or read through Cacti's official doc.  I will leave out the details as I am lazy...
http://docs.cacti.net/manual:100:3a_advanced_topics.3_data_queries#data_queries

(2) Data Source Template

• Interface - Errors/Discards
• Interface - Non-Unicast Packets
• Interface - Traffic
• Interface - Unicast Packets

(3) Graph Template

• Interface - Errors/Discards
• Interface - Non-Unicast Packets
• Interface - Traffic (bits/sec, 95th Percentile)
• Interface - Traffic (bits/sec, Total Bandwidth)
• Interface - Traffic (bits/sec)
• Interface - Traffic (bytes/sec, Total Bandwidth)
• Interface - Traffic (bytes/sec)
• Interface - Unicast Packets

DEBUG: Use the "Verbose Query" feature in Device, it will help a lot when you are stuck.

Install Cacti 1.0, spine in Ubuntu 16 with MySQL 5.7

NOTE:
As of Feb 18, 2017, Ubuntu is still packaged with Cacti 0.8.8f and spine 0.8.8b, so I will show here how to install the latest Cacti 1.0.x manually.  Well, it is not that 0.8.8 is no good anymore, but many of it's useful plugins are quite broken under php 7 and MySQL 5.7... So if you really need to stick with 0.8.8 version, I would suggest not to do that in Ubuntu 16; otherwise, just install the latest 1.0.x.

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

Packages required:
gcc
apache2, libapache2-mod-php
snmpd, snmp, snmp-mibs-downloader, libsnmp, libsnmp-dev
libssl-dev
libc-dev
libc6-dev
php, php-snmp, php-xml, php-gd, php-common, php-gmp, php-ldap, php-mbstring, php-mysql,
mysql-server, mysql-client, libmysqlclient-dev
help2man
rrdtools
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/7.0/apache2/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.3.tar.gz
tar xvzf cacti-1.0.3.tar.gz
mv cacti-1.0.3 /var/www/html/cacti
## make sure apache have full access to the folder
chown -R www-data:www-data /var/www/html/cacti

vi /etc/apache2/conf-enabled/cacti.conf

Alias /cacti /var/www/html/cacti
<Directory /var/www/html/cacti>
        Options +FollowSymLinks
        AllowOverride None
        <IfVersion >= 2.3>
                Require all granted
        </IfVersion>
        <IfVersion < 2.3>
                Order Allow,Deny
                Allow from all
        </IfVersion>

        AddType application/x-httpd-php .php

        <IfModule mod_php.c>
                php_flag magic_quotes_gpc Off
                php_flag short_open_tag On
                php_flag register_globals Off
                php_flag register_argc_argv On
                php_flag track_vars On
                # this setting is necessary for some locales
                php_value mbstring.func_overload 0
                php_value include_path .
        </IfModule>

        DirectoryIndex index.php
</Directory>

## 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 MySQL configurations
## 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/mysql/mysql.conf.d/mysqld.cnf 
[mysqld]

collation-server = utf8_general_ci

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 apt install them.
https://www.youtube.com/watch?v=rqK5OnbF1BY (although this video is done under CentOS 7, but the web setup process is mostly the same)

## Compile spine

wget http://www.cacti.net/downloads/spine/cacti-spine-1.0.3.tar.gz
tar xvzf cacti-spine-1.0.3.tar.gz
cd cacti-spine-1.0.3
./configure
make
make install

## 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.3 to Cacti 1.0.x

# cd /var/www/html

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

# tar xvzf cacti-1.0.3.tar.gz

# mv cacti cacti-1.0.3-old

# mv cacti-1.0.x 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.3-old/rra/* cacti/rra/

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

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.
...

Boost Storage Statistics
Database Engine:	InnoDB
Current Boost Table(s) Size:	16.00 KBytes
Avg Bytes/Record:	2 KBytes
Max Allowed Boost Table Size:	Unlimited
Estimated Maximum Records:	Unlimited Records

...

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. 

Boost Storage Statistics
Database Engine:	MEMORY
Current Boost Table(s) Size:	124.28 KBytes
Avg Bytes/Record:	25 KBytes
Max Record Length:	30 Bytes
Max Allowed Boost Table Size:	120.35 MBytes
Estimated Maximum Records:	4.958 K Records

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

Django, uwsgi, nginx setup in Ubuntu 16

Video URL: https://www.youtube.com/watch?v=TYZfHn0MoXg

Detailed procedures:

apt install python
apt install python-pip
apt install nginx
pip install virtualenv
pip install virtualenvwrapper
pip install uwsgi

adduser deploy
su - deploy
## for virtualenv projects
mkdir projects
## for django webpages
mkdir web

## add virtualenv environment variables to user deploy
vi /home/deploy/.bashrc
...
export WORKON_HOME=~/projects
source /usr/local/bin/virtualenvwrapper.sh

mkvirtualenv p1

pip install django

cd ~/web/

django-admin.py startproject d1

cd d1

./manage.py migrate

vi d1/settings.py

ALLOWED_HOSTS = ['172.25.0.103']

...

STATIC_URL = '/static/'

STATIC_ROOT = os.path.join(BASE_DIR, 'static/')

./manage.py collectstatic

./manage.py runserver 0.0.0.0:5000

##open web brower to test

## quit virtualenv

deactivate

## change back to root or sudo -i

## test uwsgi with django

uwsgi --master --http :5000 --home /home/deploy/projects/p1 --chdir /home/deploy/web/d1 --module d1.wsgi:application

## open web brower to test, and you will find the images and styles are gone, as uwsgi doesn't know where to service the static content from django.  We will get to that in nginx configuration part.

## create a service to start uwsgi automatically

vi /etc/systemd/system/uwsgi.service

[Unit]

Description=uWSGI Emperor

[Service]

ExecStartPre=/bin/bash -c 'mkdir -p /run/uwsgi; chown deploy:www-data /run/uwsgi'

ExecStart=/usr/local/bin/uwsgi --emperor /etc/uwsgi/sites

Restart=always

KillSignal=SIGQUIT

Type=notify

StandardError=syslog

NotifyAccess=all

[Install]

WantedBy=multi-user.target

## create config file for uwsgi

vi /etc/uwsgi/sites/p1.ini

[uwsgi]

uid = deploy

base = /home/%(uid)

chdir = %(base)/web/d1

home = %(base)/projects/p1

module = d1.wsgi:application

master = true

processes = 5

socket = /run/uwsgi/p1.sock

chown-socket = %(uid):www-data

chmod-socket = 660

vacuum = true

## start uwsgi service

systemctl restart uwsgi.service

## create nginx config file

/etc/nginx/sites-available/p1

server {

  listen 9090;

  server_name 172.25.0.103;

  location = /favicon.ico { access_log off; log_not_found off; }

  location /static/ {

    root /home/deploy/web/d1;

  }

  location / {

    include uwsgi_params;

    uwsgi_pass unix:/run/uwsgi/p1.sock;

  }

}

## sym-link to sites-enable

ln -s /etc/nginx/sites-available/p1 /etc/nginx/sites-enable/

systemctl restart nginx

Install cacti 1.x, spine in CentOS 7 with mariadb (right, not mysql)

UPDATE:
Since new release is out, I added the procedures for upgrading cacti 1.0.1 to 1.0.x; and from 1.0.x to 1.1.x

NOTE:
Wouldn't it be better to use Ubuntu as cacti is packaged up and is so easy to install.  Actually yes and no. I have tried that on Ubuntu 16, and yes, it is just a few "apt-get install" and we are good to go.  But there are other problems as of Feb 2017, cacti 0.8.8h (from apt-get) is not very ready for php 7 and MySQL 5.7, especially on the plugins contributed from the community all along the years.  For instance, "split" function is gone in php 7 and it is quite common in the Cacti code base.  Another useful plugin "thold" for sending out alarms has even more serious problems with MySQL 5.7 as the SQL checking have become much more "strict" (I have tried turning off strict mode but still no luck).  But to be fair, it is not MySQL's fault really, "thold" is written in a rather "relax" way, and older MySQL would just let it pass with no problem.
So instead going through all the troubles, why not install the latest 1.x, though there is no yum or apt-get... But it is really nice that the new Cacti 1.0 has absorbed many useful plugins into the core code base, such as boost, autom8, ugroup etc.  I am not sure why thold is left out as it provides the basic alarming functionality that is what Cacti lacks.
After all, I think it would be still interesting to see if Cacti 1.0 can run smoothly in Ubuntu 16 as php 7 is supposed to have a much higher performance than php 5

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/spine Config: /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