Pulling data from mySQL between log messages

[Wes Vaske]

Hi,

I'm using dim_STAT to capture system information while I'm running benchmark tests. My benchmark currently adds a log message to /var/tmp/pgSTAT when it starts and stops a test.

These messages show up in the Analyze section properly.

I'm trying to write SQL queries to automatically pull some statistics from the mySQL database that dim_STAT uses for each test so I can do automated aggregated results. However, when I look at the data in the DB, I can't figure out how to correlate a log message to a range of datapoints in other tables.

EG: In dim_LOG I get:

mysql> select * from dim_LOG;

+----+-----+------------+----------------------------------------------------+

| ID | Sno | dt_stamp   | Message                                            |

+----+-----+------------+----------------------------------------------------+

| 13 | 265 | 1424986729 | Starting Run hammerdb_54EF9267510003E263331383.log |

| 13 | 266 | 1424986789 | Stopping Run hammerdb_54EF9267510003E263331383.log |

+----+-----+------------+----------------------------------------------------+

And in dim_ANY_LioSTAT_v10 I get:

mysql> select id, sno, v_device, v_r_s, v_w_s 

    -> from dim_ANY_LioSTAT_v10 

    -> where id = 13 

    -> AND sno between 265 and 266 

    -> AND v_device = "sdc";

+------+------+----------+-----------+------------+

| id   | sno  | v_device | v_r_s     | v_w_s      |

+------+------+----------+-----------+------------+

|   13 |  265 | sdc      | 327.79999 | 1362.00000 |

|   13 |  266 | sdc      | 324.20001 | 1360.00000 |

+------+------+----------+-----------+------------+

2 rows in set (0.00 sec)

My collection interval is 10s so the two rows on the bottom represent 20 seconds of data but the log messages on the top show a difference of 60 seconds.

When I do the same 'query' via the web interface I get:

   r/s           w/s

 16.30000    1484.19995

 19.80000    1403.00000

 23.00000    1507.30005

232.60001    1385.00000

233.000001    416.69995

226.500001    390.90002

230.899991    352.19995

234.899991    343.80005

221.100011    384.30005

Not only do I have quite a few more datapoints, but the datapoints are quite different than those above.

I'm assuming that the "sno" fields between the two tables do not correspond to the same thing. Is this the case? How can I query the DB for the data I'm looking for here?

Thanks!

[Alan Impink]

Hi Wes,

Each collection has an row in the dim_STAT table.  The dt_start field contains the timestamp of when the collection was created, and the timeout field contains the collection interval.  To compute the timestamp for data in dim_ANY_LioSTAT you would do this computation:    

timestamp = dim_STAT.dt_start + (dim_ANY_LioSTAT.sno * dim_STAT.timeout);

Hope this helps you to correlate your data.

Alan

---

From: Wes Vaske <wes....@gmail.com>
To: dim...@googlegroups.com
Sent: Thursday, February 26, 2015 5:38 PM
Subject: [dim_STAT] Pulling data from mySQL between log messages

--
You received this message because you are subscribed to the Google Groups "dim_STAT" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dimstat+u...@googlegroups.com.
To post to this group, send email to dim...@googlegroups.com.
Visit this group at http://groups.google.com/group/dimstat.
For more options, visit https://groups.google.com/d/optout.

[Dimitri]

May only add to Alan's answer that the "sno" column in dim_LOG table
is used only to avoid collisions between several log messages coming
on the same second -- they will have then still the same "timestamp"
(dt_stamp), but their "sno" will be different (in incremental order)..

Then, none of "stat" tables in dim_STAT are using "timestamps" to
order collected data by time.. -- the problem here is related to
delays in time you may get from various command inputs; that's why
instead the "SNO" is used (incremental serial number), and then the
corresponding time is obtained as: starting time + SNO * Timeout

then, if you have 2 log messages with, say, timestamps dt_stamp1 and
dt_stamp2, then the corresponding SNO interval will be between:
(dt_stamp1 - dt_start) / Timeout and (dt_stamp2 - dt_start) / Timeout

BTW, not forget that you may also obtain from any collects the graph
*and* the raw data from a command line via dim_STAT-CLI ;-)

Rgds,
-Dimitri

On 2/27/15, 'Alan Impink' via dim_STAT <dim...@googlegroups.com> wrote:
> Hi Wes,
> Each collection has an row in the dim_STAT table.  The dt_start field
> contains the timestamp of when the collection was created, and the timeout
> field contains the collection interval.  To compute the timestamp for data
> in dim_ANY_LioSTAT you would do this computation:
> timestamp = dim_STAT.dt_start + (dim_ANY_LioSTAT.sno * dim_STAT.timeout);
> Hope this helps you to correlate your data.
> Alan
>
>

> |+------+------+----------+-----------+------------+2 rows in set (0.00

[Wes Vaske]

Thanks Alan,

timestamp = dim_STAT.dt_start + (dim_ANY_LioSTAT.sno * dim_STAT.timeout);

 This was exactly what I was looking for!

Thanks, too, dim.

BTW, not forget that you may also obtain from any collects the graph 
*and* the raw data from a command line via dim_STAT-CLI  ;-) 

I had looked briefly at dim_STAT-CLI and hadn't realized that it would export data with the graph. That might end up being an easier way to go.

-Wes