← Back to team overview

maria-developers team mailing list archive

Progress (by Hakan): Benchmark suite for sysbench (100)

 

-----------------------------------------------------------------------
                              WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: Benchmark suite for sysbench
CREATION DATE..: Thu, 04 Mar 2010, 17:46
SUPERVISOR.....: Igor
IMPLEMENTOR....: Hakan
COPIES TO......: 
CATEGORY.......: Other
TASK ID........: 100 (http://askmonty.org/worklog/?tid=100)
VERSION........: Benchmarks-3.0
STATUS.........: Assigned
PRIORITY.......: 60
WORKED HOURS...: 20
ESTIMATE.......: 20 (hours remain)
ORIG. ESTIMATE.: 40

PROGRESS NOTES:

-=-=(Hakan - Mon, 12 Apr 2010, 21:06)=-=-
Added:
 * OProfile hook
 * iostat hook
 * mpstat hook
 * Copy away .err file for crash detection
 * mysqladmin --sleep 10 status, for getting in test query statistics

Worked 8 hours and estimate 20 hours remain (original estimate unchanged).

-=-=(Hakan - Mon, 12 Apr 2010, 21:04)=-=-
Low Level Design modified.
--- /tmp/wklog.100.old.13854    2010-04-12 21:04:41.000000000 +0000
+++ /tmp/wklog.100.new.13854    2010-04-12 21:04:41.000000000 +0000
@@ -1,20 +1,24 @@
 All scripts can be found at lp:mariadb-tools/sysbench
 
 * Run sysbench tests on a machine and collect numbers
-This is implemented in run-sysbench.sh. Currently it supports:
- ** Optionally pull of latest source from Launchpad and compile
- ** Starting the server
+This is implemented in run-sysbench.sh. Currently we supports:
+ ** Optionally pulling of latest source from Launchpad and compile.
+ ** Starting and killing the server.
  ** Running each sysbench test for $LOOP_COUNT times and 
  $RUN_TIME time.
- ** Number of concurrent clients is hardcoded
+ ** Number of concurrent clients is currently hardcoded.
  ** Machine specific configuration like location of binaries and
  directories needed are in separate config files located at conf/<hostname>.inc
- ** Set random seed of sysbench to have better comparison
- ** Restart mysqld from scratch for each run and copy away
+ ** Setting random seed of sysbench to have better comparison.
+ ** Restarting mysqld from scratch for each run and copy away
  DATA_DIR of the database for faster starts.
- ** Between each run, run sync and clear file system caches with
- echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc)
- ** Write out mysqld and sysbench options for reference.
+ ** Between each run, we run sync and clear file system caches with
+ echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc).
+ ** Writing out mysqld and sysbench options for reference.
+ ** OProfile hook.
+ ** iostat hook.
+ ** mpstat -u (CPU utilization) hook.
+ ** mysqladmin --sleep 10 status, for getting in test query statistics.
 
 The main loop of run-sysbench.sh is:
 
@@ -32,9 +36,6 @@
 done
 
 Open items:
-  ** OProfile hook
-  ** iostat hook
-  ** sar -u (CPU utilization) hook
   ** Crash detection
   ** Error detection
 
@@ -45,9 +46,10 @@
   ** Read result files from
   ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt
   
-  ** Collect OProfile, iostat, cpu utilization, and machine info
+  ** Collect OProfile, iostat, cpu utilization, sysbench, per test query
+statisticts and machine info.
   ** Detect errors and crashes.
-  ** Generate SQL INSERT strings for presentation usage
+  ** Generate SQL INSERT strings for presentation usage.
 
 The layout for storing the numbers is:
   CREATE TABLE sysbench_run (
@@ -97,7 +99,7 @@
 sysbench test
  delete            121.52  144.77  117.70  115.15  100.48   75.39   66.56
                    mean value of runs
-     1             first run
+     1             first run (hover for in test query statistics)
      2             second run
      3             third run
                    STDEV

-=-=(Hakan - Tue, 09 Mar 2010, 14:10)=-=-
* Added run-sysbench-myisam.sh for running MyISAM related benchmarks with sysbench.

Worked 4 hours and estimate 28 hours remain (original estimate unchanged).

-=-=(Hakan - Tue, 09 Mar 2010, 14:09)=-=-
Low Level Design modified.
--- /tmp/wklog.100.old.18803    2010-03-09 14:09:37.000000000 +0000
+++ /tmp/wklog.100.new.18803    2010-03-09 14:09:37.000000000 +0000
@@ -9,6 +9,12 @@
  ** Number of concurrent clients is hardcoded
  ** Machine specific configuration like location of binaries and
  directories needed are in separate config files located at conf/<hostname>.inc
+ ** Set random seed of sysbench to have better comparison
+ ** Restart mysqld from scratch for each run and copy away
+ DATA_DIR of the database for faster starts.
+ ** Between each run, run sync and clear file system caches with
+ echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc)
+ ** Write out mysqld and sysbench options for reference.
 
 The main loop of run-sysbench.sh is:
 
@@ -31,12 +37,6 @@
   ** sar -u (CPU utilization) hook
   ** Crash detection
   ** Error detection
-  ** Set random seed of sysbench to have better comparision
-  ** Restart mysqld from scratch for each run and copy away
-  DATA_DIR of the database for faster starts.
-  ** Between each run, run sync and clear file system caches with
-  echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc)
-  ** Write out mysqld and sysbench options for reference.
 
 * Analyze numbers
 This is implemented in analyze-sysbench.php

-=-=(Hakan - Tue, 09 Mar 2010, 14:06)=-=-
Added:
  ** Set random seed of sysbench to have better comparison
  ** Restart mysqld from scratch for each run and copy away
  DATA_DIR of the database for faster starts.
  ** Between each run, run sync and clear file system caches with
  echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc)
  ** Write out mysqld and sysbench options for reference.
Worked 8 hours and estimate 32 hours remain (original estimate unchanged).

-=-=(Hakan - Mon, 08 Mar 2010, 12:27)=-=-
Low Level Design modified.
--- /tmp/wklog.100.old.21404    2010-03-08 12:27:08.000000000 +0000
+++ /tmp/wklog.100.new.21404    2010-03-08 12:27:08.000000000 +0000
@@ -36,6 +36,7 @@
   DATA_DIR of the database for faster starts.
   ** Between each run, run sync and clear file system caches with
   echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc)
+  ** Write out mysqld and sysbench options for reference.
 
 * Analyze numbers
 This is implemented in analyze-sysbench.php

-=-=(Guest - Thu, 04 Mar 2010, 18:15)=-=-
Low Level Design modified.
--- /tmp/wklog.100.old.2695     2010-03-04 18:15:46.000000000 +0000
+++ /tmp/wklog.100.new.2695     2010-03-04 18:15:46.000000000 +0000
@@ -1 +1,112 @@
+All scripts can be found at lp:mariadb-tools/sysbench
 
+* Run sysbench tests on a machine and collect numbers
+This is implemented in run-sysbench.sh. Currently it supports:
+ ** Optionally pull of latest source from Launchpad and compile
+ ** Starting the server
+ ** Running each sysbench test for $LOOP_COUNT times and 
+ $RUN_TIME time.
+ ** Number of concurrent clients is hardcoded
+ ** Machine specific configuration like location of binaries and
+ directories needed are in separate config files located at conf/<hostname>.inc
+
+The main loop of run-sysbench.sh is:
+
+start_mysqld
+for SYSBENCH_TEST in $SYSBENCH_TESTS
+    for THREADS in $NUM_THREADS
+        while [ $k -lt $LOOP_COUNT ]
+            drop schema sbtest
+            create schema sbtest
+
+            $SYSBENCH $SYSBENCH_OPTIONS prepare
+            $SYSBENCH $SYSBENCH_OPTIONS run
+        done
+    done
+done
+
+Open items:
+  ** OProfile hook
+  ** iostat hook
+  ** sar -u (CPU utilization) hook
+  ** Crash detection
+  ** Error detection
+  ** Set random seed of sysbench to have better comparision
+  ** Restart mysqld from scratch for each run and copy away
+  DATA_DIR of the database for faster starts.
+  ** Between each run, run sync and clear file system caches with
+  echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc)
+
+* Analyze numbers
+This is implemented in analyze-sysbench.php
+
+Open items:
+  ** Read result files from
+  ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt
+  
+  ** Collect OProfile, iostat, cpu utilization, and machine info
+  ** Detect errors and crashes.
+  ** Generate SQL INSERT strings for presentation usage
+
+The layout for storing the numbers is:
+  CREATE TABLE sysbench_run (
+    id int unsigned NOT NULL auto_increment,
+    host varchar(80),               -- Hostname we ran the test on.
+    run_date date,                  -- The day we ran the test.
+    sysbench_version varchar(32),   -- Version of sysbench we used.
+    test_name varchar(32),          -- Name of the sysbench test.
+    run_time int unsigned,          -- Run time in seconds.
+    runs int unsigned,              -- Number of iterations of the test.
+    PRIMARY KEY (id),
+    KEY (host),
+    KEY (run_date
+  );
+
+  CREATE TABLE sysbench_comment (
+    id int unsigned NOT NULL auto_increment,
+    sysbench_run_id int unsigned NOT NULL,  -- FK pointing to sysbench_run.
+    compile_info text,                      -- Compile options we used.
+    machine_info text,                      -- Details about the hardware.
+    sysbench_options text,                  -- The sysbench options we used.
+    PRIMARY KEY (id),
+    KEY (sysbench_run_id)
+  );
+
+  CREATE TABLE sysbench_result (
+    id int unsigned NOT NULL auto_increment,
+    sysbench_run_id int unsigned NOT NULL,  -- FK pointing to sysbench_run.
+    concurrency int unsigned,               -- Concurrency level we used.
+    result decimal(7,2),                    -- The actual result.
+    io varchar(80),                         -- The IO from iostat.
+    cpu varchar(80),                        -- CPU utilization.
+    profile text,                           -- Profiling information.
+    error text,                             -- Error messages and stack traces.
+    PRIMARY KEY (id),
+    KEY (sysbench_run_id
+  );
+
+* Generate a report out of the numbers
+This script will generate a HTML version for putting up on the web and a
+txt version for email usage.
+
+Open items:
+  ** Generate an overview table in the form of
+                  Number of threads
+                    1       4       8       16      32      64      128
+sysbench test
+ delete            121.52  144.77  117.70  115.15  100.48   75.39   66.56
+                   mean value of runs
+     1             first run
+     2             second run
+     3             third run
+                   STDEV
+                   STDEV in % of mean
+                   CPU utilization (usr/sys/wait/idle)
+                   IO (read/write)
+
+For HTML version additionally generate a graph with JPGraph.
+
+* Get machine(s) and run the test on a weekly basis and for each release
+comparing with the prior release.
+
+* Email weekly reports and blog about it.



DESCRIPTION:

Create a benchmark suite for running sysbench

* Run sysbench tests on a machine and collect numbers
* Analyze numbers
* Generate a report out of the numbers


LOW-LEVEL DESIGN:



All scripts can be found at lp:mariadb-tools/sysbench

* Run sysbench tests on a machine and collect numbers
This is implemented in run-sysbench.sh. Currently we supports:
 ** Optionally pulling of latest source from Launchpad and compile.
 ** Starting and killing the server.
 ** Running each sysbench test for $LOOP_COUNT times and 
 $RUN_TIME time.
 ** Number of concurrent clients is currently hardcoded.
 ** Machine specific configuration like location of binaries and
 directories needed are in separate config files located at conf/<hostname>.inc
 ** Setting random seed of sysbench to have better comparison.
 ** Restarting mysqld from scratch for each run and copy away
 DATA_DIR of the database for faster starts.
 ** Between each run, we run sync and clear file system caches with
 echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc).
 ** Writing out mysqld and sysbench options for reference.
 ** OProfile hook.
 ** iostat hook.
 ** mpstat -u (CPU utilization) hook.
 ** mysqladmin --sleep 10 status, for getting in test query statistics.

The main loop of run-sysbench.sh is:

start_mysqld
for SYSBENCH_TEST in $SYSBENCH_TESTS
    for THREADS in $NUM_THREADS
        while [ $k -lt $LOOP_COUNT ]
            drop schema sbtest
            create schema sbtest

            $SYSBENCH $SYSBENCH_OPTIONS prepare
            $SYSBENCH $SYSBENCH_OPTIONS run
        done
    done
done

Open items:
  ** Crash detection
  ** Error detection

* Analyze numbers
This is implemented in analyze-sysbench.php

Open items:
  ** Read result files from
  ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt
  
  ** Collect OProfile, iostat, cpu utilization, sysbench, per test query
statisticts and machine info.
  ** Detect errors and crashes.
  ** Generate SQL INSERT strings for presentation usage.

The layout for storing the numbers is:
  CREATE TABLE sysbench_run (
    id int unsigned NOT NULL auto_increment,
    host varchar(80),               -- Hostname we ran the test on.
    run_date date,                  -- The day we ran the test.
    sysbench_version varchar(32),   -- Version of sysbench we used.
    test_name varchar(32),          -- Name of the sysbench test.
    run_time int unsigned,          -- Run time in seconds.
    runs int unsigned,              -- Number of iterations of the test.
    PRIMARY KEY (id),
    KEY (host),
    KEY (run_date
  );

  CREATE TABLE sysbench_comment (
    id int unsigned NOT NULL auto_increment,
    sysbench_run_id int unsigned NOT NULL,  -- FK pointing to sysbench_run.
    compile_info text,                      -- Compile options we used.
    machine_info text,                      -- Details about the hardware.
    sysbench_options text,                  -- The sysbench options we used.
    PRIMARY KEY (id),
    KEY (sysbench_run_id)
  );

  CREATE TABLE sysbench_result (
    id int unsigned NOT NULL auto_increment,
    sysbench_run_id int unsigned NOT NULL,  -- FK pointing to sysbench_run.
    concurrency int unsigned,               -- Concurrency level we used.
    result decimal(7,2),                    -- The actual result.
    io varchar(80),                         -- The IO from iostat.
    cpu varchar(80),                        -- CPU utilization.
    profile text,                           -- Profiling information.
    error text,                             -- Error messages and stack traces.
    PRIMARY KEY (id),
    KEY (sysbench_run_id
  );

* Generate a report out of the numbers
This script will generate a HTML version for putting up on the web and a
txt version for email usage.

Open items:
  ** Generate an overview table in the form of
                  Number of threads
                    1       4       8       16      32      64      128
sysbench test
 delete            121.52  144.77  117.70  115.15  100.48   75.39   66.56
                   mean value of runs
     1             first run (hover for in test query statistics)
     2             second run
     3             third run
                   STDEV
                   STDEV in % of mean
                   CPU utilization (usr/sys/wait/idle)
                   IO (read/write)

For HTML version additionally generate a graph with JPGraph.

* Get machine(s) and run the test on a weekly basis and for each release
comparing with the prior release.

* Email weekly reports and blog about it.


ESTIMATED WORK TIME

ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v3.5.9)