Storing statistics in MySQL

Navigation:  Deprecated > LVE-Stats 0.x >

Storing statistics in MySQL

Previous pageReturn to chapter overviewNext page

NOTE. LVE-STATS-0.X IS NO LONGER SUPPORTED, PLEASE USE LVE-STATS 2

 

You have to install MySQL-python rpm to store lve-stats on centralized server. Run:

 

$ yum install MySQL-python

 

If you have MySQL 5.3+ installed on CloudLinux 5 server, and there is no  libmysqlclient_r.so.15 on the server, run:

 

$ yum --enablerepo=cloudlinux-updates-testing install mysqlclient15

 

A typical procedure to configure the MySQL database for storing information about multiple servers for lve-stats services looks as follows:

 

Create database and user. You can do it by executing the following commands:

 

create database <database>;
grant all on <database>.* to <user> identified by 'password';
flush privileges;

 

Create database schema:

 

CREATE TABLE history (id INTEGER,
       cpu INTEGER, cpu_limit INTEGER,
       cpu_max INTEGER,
       ncpu INTEGER,
       mep INTEGER, mep_limit INTEGER,
       mep_max INTEGER,
       io INTEGER, io_limit INTEGER,
       mem INTEGER, mem_limit INTEGER,
       mem_max INTEGER,
       mem_fault INTEGER, mep_fault INTEGER,
       created TIMESTAMP, weight INTEGER, server_id CHAR(10),
       lmemphy INTEGER, memphy INTEGER, memphy_max INTEGER, memphy_fault INTEGER,
       lnproc INTEGER, nproc INTEGER, nproc_max INTEGER, nproc_fault INTEGER,
       lcpuw INTEGER, io_max INTEGER,
       iops INTEGER, liops INTEGER, iops_max INTEGER );
CREATE INDEX idx_history_id ON history(id);
CREATE INDEX idx_history_created ON history(created);
CREATE INDEX idx_history_weight ON history(weight);
CREATE INDEX idx_history_server_id ON history(server_id);
CREATE TABLE last_run (hourly TIMESTAMP, daily TIMESTAMP, server_id CHAR(10), lve_version INTEGER);
CREATE TABLE users (server_id CHAR(10), id INTEGER, username CHAR(20));
CREATE INDEX idx_users_server_id ON users(server_id);
CREATE INDEX idx_users_id ON users(id);
 
CREATE TABLE history_gov ( ts INTEGER,
                          username CHAR(64),
                          max_simultaneous_requests INTEGER,
                          sum_cpu FLOAT,
                          sum_write FLOAT,
                          sum_read FLOAT,
                          number_of_iterations INTEGER,
                          max_cpu FLOAT,
                          max_write FLOAT,
                          max_read FLOAT,
                          number_of_restricts INTEGER,
                          limit_cpu_on_period_end INTEGER,
                          limit_read_on_period_end INTEGER,
                          limit_write_on_period_end INTEGER,
                          cause_of_restrict INTEGER,
                          weight INTEGER,
                          server_id char(10));
 
CREATE INDEX idx_history_gov_ts ON history_gov(ts);
CREATE INDEX idx_history_gov_cause_of_restrict ON history_gov(cause_of_restrict);
CREATE INDEX idx_history_gov_number_of_restricts ON history_gov(number_of_restricts);
CREATE INDEX idx_history_gov_max_simultaneous_requests ON history_gov(max_simultaneous_requests);
CREATE INDEX idx_history_gov_server_id ON history_gov(server_id);
CREATE INDEX idx_history_gov_weight ON history_gov(weight);
 
CREATE TABLE last_run_gov (hourly TIMESTAMP, daily TIMESTAMP, server_id CHAR(10), lve_version INTEGER);
 
 
 * Execute following SQL command for each remote server for which you want to store
 statistics in this database (make sure you substitute _SERVER_NAME_ with the same
 servername as used in lvestats config file on remote server:
 
INSERT INTO last_run(hourly, daily, server_id, lve_version) VALUES (UTC_TIMESTAMP(), UTC_TIMESTAMP(), '_SERVER_NAME_', 4);

 

On each server edit file /etc/sysconfig/lvestats & /etc/sysconfig/lvestats.readonly as follows:

 

db_type = mysql
connect_string = host:database:user:password
server_id = _SERVER_NAME_
db_port = _port_

 

Note. lvestats.readonly should have a user that has read only access to all tables from lvestats database.

Note. _SERVER_NAME_ should be at most 10 characters

Note. db_port is an optional parameter. Default port would be used.

 

Select server responsible for compacting database on regular bases by setting COMPACT=master in /etc/sysconfig/lvestats for that server. Set COMPACT=slave on all other servers.

 

Make sure that /etc/sysconfig/lvestats is readable only by root (chmod 600 /etc/sysconfig/lvestats), lvestats.readonly should be readable by anyone

 

Restart service:

 

service lvestats restart

 

If you use central database to store lvestats data, on each server, execute:

 

$ /usr/share/lve-stats/save_users_to_database.py

 

You just need to execute it once, as it will be later executed via cron job. That script will store usernames from each server, so that lve-stats would later be able to correctly identify each user.

 

Updating MySQL & PostgreSQL schema for lve-stats 0.8+

 

If you are using MySQL or PostgreSQL server for lve-stats older then 0.8, make sure to do the following steps to upgrade to latest version:

 

Stop lvestats service on all your servers.

 

Connect to your database server, and execute following commands:

 

ALTER TABLE history ADD lmemphy INTEGER;
ALTER TABLE history ADD memphy INTEGER;
ALTER TABLE history ADD memphy_max INTEGER;
ALTER TABLE history ADD memphy_fault INTEGER;
ALTER TABLE history ADD lnproc INTEGER;
ALTER TABLE history ADD nproc INTEGER;
ALTER TABLE history ADD nproc_max INTEGER;
ALTER TABLE history ADD nproc_fault INTEGER;
ALTER TABLE history ADD lcpuw INTEGER;
ALTER TABLE history ADD io_max INTEGER;
UPDATE history SET lmemphy = 0, memphy = 0, memphy_max = 0, memphy_fault = 0,
      lnproc = 0, nproc = 0, nproc_max = 0, nproc_fault = 0,
      lcpuw = 0, io_max = 0;
 
ALTER TABLE last_run ADD lve_version INTEGER;
UPDATE last_run SET lve_version = 4;
CREATE TABLE last_run_gov (hourly TIMESTAMP, daily TIMESTAMP, server_id CHAR(10), lve_version INTEGER);

 

To upgrade scheme to support MySQL Governor:

 

CREATE TABLE history_gov ( ts INTEGER,
   username char(64),
   max_simultaneous_requests INTEGER,
   sum_cpu float,
   sum_write float,
   sum_read float,
   number_of_iterations INTEGER,
   max_cpu float,
   max_write float,
   max_read float,
   number_of_restricts INTEGER,
   limit_cpu_on_period_end INTEGER,
   limit_read_on_period_end INTEGER,
   limit_write_on_period_end INTEGER,
   cause_of_restrict INTEGER,
   server_id char(10));
 
CREATE INDEX idx_history_gov_ts ON history_gov(ts);
CREATE INDEX idx_history_gov_cause_of_restrict ON history_gov(cause_of_restrict);
CREATE INDEX idx_history_gov_number_of_restricts ON history_gov(number_of_restricts);
CREATE INDEX idx_history_gov_max_simultaneous_requests ON history_gov(max_simultaneous_requests);
CREATE INDEX idx_history_gov_server_id ON history_gov(server_id);
 

 

Upgrading from lve-stats < 0.9-20:

 

ALTER TABLE history_gov ADD weight INTEGER;
CREATE INDEX idx_history_gov_weight ON history_gov(weight);
CREATE TABLE last_run_gov (hourly TIMESTAMP, daily TIMESTAMP, server_id CHAR(10), lve_version INTEGER);

 

Update lve-stats RPM on all your servers.

 

If you use central database to store lvestats data, execute the following commands:

 

CREATE TABLE users (server_id CHAR(10), id INTEGER, username CHAR(20));
CREATE INDEX idx_users_server_id ON users(server_id);
CREATE INDEX idx_users_id ON users(id);

 

On each server execute:

 

$ /usr/share/lve-stats/save_users_to_database.py

 

You just need to execute it once, as it will be later executed via cron job. That script will store usernames from each server, so that lve-stats would later be able to correctly identify each user.