Storing statistics in PostgreSQL

Navigation:  Deprecated > LVE-Stats 0.x >

Storing statistics in PostgreSQL

Previous pageReturn to chapter overviewNext page

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

 

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

 

$ yum install postgresql-python

 

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

 

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

 

createdb <database>
createuser <user>

 

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_max: INTEGER, liops: INTEGER, iops: 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 (now() AT TIME ZONE 'UTC', now() AT TIME ZONE 'UTC', '_SERVER_NAME_', 4);

 

On each server edit file /etc/sysconfig/lvestats and /etc/sysconfig/lvestats as follows:

 

db_type = postgresql
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 history table.

Note. _SERVER_NAME_ should be at most 10 characters

Note. db_port is optional, default PostgreSQL port will 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.

 

You are done!