enKryptik observations - SYSSTAT followup

I wrote an article that was published out in the tutorials of Linux.com called SYSSTAT Howto: A Deployment and Configuration Guide for Linux Servers and received some positive feedback by a few readers. In my guide I listed an example sql script to use when building database tables for SYSSTAT. A reader from Linux.com, Will Harris, was kind enough to send me a copy of the sql code he uses. It has a full set up-script setting up the tables for the elements that are recorded by SYSSTAT.

The Linux community is about contributing. The contributions we make give others solutions to problems or education in the various techniques of what Linux can do. So thanks to those who have read the article and took the time to make a comment, but a big thank you to Mr. Harris for being willing to share his sql code. I’ve posted the code below my signature for your review. Feel free to use it. If you have other suggestions let me know.

Cheers – Kryptikos

MySQL dump 10.13 Distrib 5.1.36, for redhat-linux-gnu (i386)
—
— Host: localhost    Database: sysstat
— ——————————————————
— Server version   5.1.36

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

—
— Table structure for table `cpuutilization`
—

DROP TABLE IF EXISTS `cpuutilization`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cpuutilization` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(8) DEFAULT NULL,
`cpu` varchar(3) DEFAULT NULL,
`pct_user` decimal(10,2) DEFAULT NULL,
`pct_nice` decimal(10,2) DEFAULT NULL,
`pct_system` decimal(10,2) DEFAULT NULL,
`pct_iowait` decimal(10,2) DEFAULT NULL,
`pct_steal` decimal(10,2) DEFAULT NULL,
`pct_idle` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `inodefilekerneltable`
—

DROP TABLE IF EXISTS `inodefilekerneltable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `inodefilekerneltable` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`dentunusd` decimal(10,2) DEFAULT NULL,
`file_nr` decimal(10,2) DEFAULT NULL,
`inode_nr` decimal(10,2) DEFAULT NULL,
`pty_nr` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `inputactivityperprocperirq`
—

DROP TABLE IF EXISTS `inputactivityperprocperirq`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `inputactivityperprocperirq` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`CPU` decimal(10,2) DEFAULT NULL,
`i000_s` decimal(10,2) DEFAULT NULL,
`i001_s` decimal(10,2) DEFAULT NULL,
`i004_s` decimal(10,2) DEFAULT NULL,
`i007_s` decimal(10,2) DEFAULT NULL,
`i008_s` decimal(10,2) DEFAULT NULL,
`i009_s` decimal(10,2) DEFAULT NULL,
`i012_s` decimal(10,2) DEFAULT NULL,
`i014_s` decimal(10,2) DEFAULT NULL,
`i015_s` decimal(10,2) DEFAULT NULL,
`i016_s` decimal(10,2) DEFAULT NULL,
`i018_s` decimal(10,2) DEFAULT NULL,
`i020_s` decimal(10,2) DEFAULT NULL,
`i021_s` decimal(10,2) DEFAULT NULL,
`i022_s` decimal(10,2) DEFAULT NULL,
`i023_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `iotransferrate`
—

DROP TABLE IF EXISTS `iotransferrate`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `iotransferrate` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`tps` decimal(10,2) DEFAULT NULL,
`rtps` decimal(10,2) DEFAULT NULL,
`wtps` decimal(10,2) DEFAULT NULL,
`bread_s` decimal(10,2) DEFAULT NULL,
`bwrtn_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `irqinterrupts`
—

DROP TABLE IF EXISTS `irqinterrupts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `irqinterrupts` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`INTR` varchar(20) DEFAULT NULL,
`intr_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `memorystatistics`
—

DROP TABLE IF EXISTS `memorystatistics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `memorystatistics` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`frmpg` decimal(10,2) DEFAULT NULL,
`bufpg` decimal(10,2) DEFAULT NULL,
`campg` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `memoryswapspaceutilization`
—

DROP TABLE IF EXISTS `memoryswapspaceutilization`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `memoryswapspaceutilization` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`kbmemfree` decimal(10,2) DEFAULT NULL,
`kbmemused` decimal(10,2) DEFAULT NULL,
`pct_memused` decimal(10,2) DEFAULT NULL,
`kbbuffers` decimal(10,2) DEFAULT NULL,
`kbcached` decimal(10,2) DEFAULT NULL,
`kbswpfree` decimal(10,2) DEFAULT NULL,
`kbswpused` decimal(10,2) DEFAULT NULL,
`pct_swpused` decimal(10,2) DEFAULT NULL,
`kbswpcad` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `networkstatistics`
—

DROP TABLE IF EXISTS `networkstatistics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `networkstatistics` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`IFACE` varchar(20) DEFAULT NULL,
`rxpck_s` decimal(10,2) DEFAULT NULL,
`txpck_s` decimal(10,2) DEFAULT NULL,
`rxkB_s` decimal(10,2) DEFAULT NULL,
`txkB_s` decimal(10,2) DEFAULT NULL,
`rxcmp_s` decimal(10,2) DEFAULT NULL,
`txcmp_s` decimal(10,2) DEFAULT NULL,
`rxmcst_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `networkstatisticserrors`
—

DROP TABLE IF EXISTS `networkstatisticserrors`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `networkstatisticserrors` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`IFACE` varchar(20) DEFAULT NULL,
`rxerr_s` decimal(10,2) DEFAULT NULL,
`txerr_s` decimal(10,2) DEFAULT NULL,
`coll_s` decimal(10,2) DEFAULT NULL,
`rxdrop_s` decimal(10,2) DEFAULT NULL,
`txdrop_s` decimal(10,2) DEFAULT NULL,
`txcarr_s` decimal(10,2) DEFAULT NULL,
`rxfram_s` decimal(10,2) DEFAULT NULL,
`rxfifo_s` decimal(10,2) DEFAULT NULL,
`txfifo_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `networkstatisticsnfsclientactvty`
—

DROP TABLE IF EXISTS `networkstatisticsnfsclientactvty`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `networkstatisticsnfsclientactvty` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`call_s` decimal(10,2) DEFAULT NULL,
`retrans_s` decimal(10,2) DEFAULT NULL,
`read_s` decimal(10,2) DEFAULT NULL,
`write_s` decimal(10,2) DEFAULT NULL,
`access_s` decimal(10,2) DEFAULT NULL,
`getatt_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `networkstatisticsnfsserveractvty`
—

DROP TABLE IF EXISTS `networkstatisticsnfsserveractvty`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `networkstatisticsnfsserveractvty` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`scall_s` decimal(10,2) DEFAULT NULL,
`badcall_s` decimal(10,2) DEFAULT NULL,
`packet_s` decimal(10,2) DEFAULT NULL,
`udp_s` decimal(10,2) DEFAULT NULL,
`tcp_s` decimal(10,2) DEFAULT NULL,
`hit_s` decimal(10,2) DEFAULT NULL,
`miss_s` decimal(10,2) DEFAULT NULL,
`sread_s` decimal(10,2) DEFAULT NULL,
`swrite_s` decimal(10,2) DEFAULT NULL,
`saccess_s` decimal(10,2) DEFAULT NULL,
`sgetatt_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `networkstatisticssocket`
—

DROP TABLE IF EXISTS `networkstatisticssocket`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `networkstatisticssocket` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`totsck` decimal(10,2) DEFAULT NULL,
`tcpsck` decimal(10,2) DEFAULT NULL,
`udpsck` decimal(10,2) DEFAULT NULL,
`rawsck` decimal(10,2) DEFAULT NULL,
`ip_frag` decimal(10,2) DEFAULT NULL,
`tcp_tw` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `pagingstatistics`
—

DROP TABLE IF EXISTS `pagingstatistics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pagingstatistics` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`page_in_s` decimal(10,2) DEFAULT NULL,
`page_out_s` decimal(10,2) DEFAULT NULL,
`faults_s` decimal(10,2) DEFAULT NULL,
`major_faults_s` decimal(10,2) DEFAULT NULL,
`page_free_s` decimal(10,2) DEFAULT NULL,
`page_scank_s` decimal(10,2) DEFAULT NULL,
`page_scand_s` decimal(10,2) DEFAULT NULL,
`page_steal_s` decimal(10,2) DEFAULT NULL,
`pct_vmeff` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `queuelengthloadavgs`
—

DROP TABLE IF EXISTS `queuelengthloadavgs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `queuelengthloadavgs` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`runq_sz` decimal(10,2) DEFAULT NULL,
`plist_sz` decimal(10,2) DEFAULT NULL,
`ldavg_1` decimal(10,2) DEFAULT NULL,
`ldavg_5` decimal(10,2) DEFAULT NULL,
`ldavg_15` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `swappingstatistics`
—

DROP TABLE IF EXISTS `swappingstatistics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `swappingstatistics` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`pswpin_s` decimal(10,2) DEFAULT NULL,
`pswpout_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `systemswitchingactivity`
—

DROP TABLE IF EXISTS `systemswitchingactivity`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `systemswitchingactivity` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`cswch_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `taskcreation`
—

DROP TABLE IF EXISTS `taskcreation`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `taskcreation` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`proc_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

—
— Table structure for table `ttydeviceactivity`
—

DROP TABLE IF EXISTS `ttydeviceactivity`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttydeviceactivity` (
`hostname` varchar(100) DEFAULT NULL,
`datestamp` date DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`TTY` varchar(20) DEFAULT NULL,
`rcvin_s` decimal(10,2) DEFAULT NULL,
`xmtin_s` decimal(10,2) DEFAULT NULL,
`framerr_s` decimal(10,2) DEFAULT NULL,
`prtyerr_s` decimal(10,2) DEFAULT NULL,
`brk_s` decimal(10,2) DEFAULT NULL,
`ovrun_s` decimal(10,2) DEFAULT NULL,
KEY `hostname_idx` (`hostname`),
KEY `date_time_idx` (`datestamp`,`time`),
KEY `date_idx` (`datestamp`),
KEY `time_idx` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

— Dump completed on 2009-08-11 15:48:1

RELATED ARTICLESMORE FROM AUTHOR

Webinar: Harden Your Security Mindset: Break Down the Critical Security Risks for Web Apps

Kickstart Your Career & Save Up To 40%!

Further Your Education with Courses & Certifications

Save BIG on Earth Day Deals with Sitewide Savings!

Leap into Learning and SAVE up to 50% off!

RELATED ARTICLES MORE FROM AUTHOR