Experimental Physics and
Industrial Control System

"Kasemir, Kay" <[email protected]> · Wed, 01 Jun 2011 12:34:31 -0400

Hello:

On 6/1/11 12:00 , "John Dobbins" <[email protected]> wrote:
> # NOTE:
> # The msghist tool uses subselect queries as shown below
> # to do a full search for properties that are not optimized
> # as MESSAGE columns.
> # Those subselect statements only work in MySQL 6.x.
> # MySQL 5.x will just hang forever in them as soon as there
> # are a few rows in the MESSAGE* tables
> 
> I am confused about MySQL versions referenced here. The latest stable
> version appears to be 5.5? Can you clarify?

The original CSS message database schema that I got from DESY looked like
this:

table message: message 'id' and 'datum' (time stamp)
message_content: message id, property id, value

That schema can store messages with any number of property/value pairs for
content.

One such message property might be "name" for a PV name and "severity" for
the alarm severity of alarm related messages.

Operationally, at SNS we are often interested in locating all messages that
mention a certain PV, or all messages with INVALID alarm severity.
In the original schema, that meant locating all messages where the
message_content had a 'name' entry with the proper PV name,
and/or a 'severity' entry with the desired severity.
To do that, you can for example use a sub-select like this:

SELECT m.id, m.datum, c.msg_property_type_id p, c.value
  FROM message m, message_content c
  WHERE m.datum BETWEEN ? AND ? AND m.id=c.message_id
    AND m.id IN
      ( SELECT message_id FROM message_content WHERE msg_property_type_id=4
AND value LIKE 'MyPVName')

... assuming that 4 is the property ID for 'name' and you're looking for
messages concerning some PV name.

That lookup in both message and message_content is slow.
With MySQL 5.5, I found it to be extremely slow.
With MySQL 6, which at that time and maybe still was not a 'stable' version
but already available for download, it worked, but still slow.
Operationally, we use Oracle, where again it works, but is not fast.

So we ended up adding message properties that we consider important
to the main message table: type, name, severity.

The message history viewer (msghist plugin) that we use can automatically
determine which message properties are in the 'message' table and thus
optimize the query, but it should still work with the original DESY schema
that keeps all message properties in the message_content table.

> BTW: I was able to download and build SNS CSS sources for the purpose of
> trying out BEAST. Considering the number of steps involved
> 
> 1) operational mysql RDB configured with Alarm tables
> 2) operational Java Message Service
> 3) working Eclipse installation
> 4) download Control System Studio source and build
> 5) configure Alarm RDB using AlarmConfigTool (a CSS product)
> 6) run AlarmServer (another CSS product)
> 7) run CSS with AlarmTree and Alarm Table plug-ins
> 
> I thought things went remarkably smoothly.
It's actually easy once you get through step 0:
Have accelerator and EPICS IOCs that produce anything worth alarming on.

Thanks,
Kay

Experimental Physics and Industrial Control System

Experimental Physics and
Industrial Control System