thruk logcache duplicated plugin outputs

  • Hi,

    i have some problems with the logcache.
    Due to problems descripbed here: Issues with Livestatus LMD and Thruk
    i used a nightly version to build logcache and then went back to 2.16-2.

    Mysql = Server version: 5.6.37-82.2 Percona Server (GPL), Release 82.2

    Storage-engine = myisam (due to heavy string search)

    On logcacheupdate/import the slowes querys are always on plugin_output table:

    SQL
    1. SELECT output_id FROM `a6897_plugin_output` WHERE output = 'Warning: Return code of 255 for check of service \'check_ntp_peer\' on host \'testhost\' was out of bounds.' LIMIT 1;
    2. # Time: 170920 17:57:18
    3. # User@Host: thruk[thruk] @ localhost [] Id: 194
    4. # Schema: thruk Last_errno: 1317 Killed: 1053
    5. # Query_time: 41.417401 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0
    6. # Bytes_sent: 44


    We have around 280k services and 9k hosts... so of course this table is very very big.
    But...
    Why are plugin outputs stored redundant in table?

    I just did a query and most of the output_id's are not inside of the log table.


    SQL
    1. SELECT count(*) FROM `a6897_plugin_output` WHERE output = 'Warning: Return code of 255 for check of service \'check_ntp_peer\' on host \'testhost\' was out of bounds.';
    2. +----------+
    3. | count(*) |
    4. +----------+
    5. | 370 |
    6. +----------+
    7. 1 row in set (23.63 sec)

    Code
    1. select count(*) from a6897_plugin_output;
    2. +----------+
    3. | count(*) |
    4. +----------+
    5. | 40944609 |
    6. +----------+
    7. 1 row in set (0.00 sec)

    Is there any need to store things redundant in the same table?

  • I can confirm that we have the same "issue" (although everything works ok here):

    SQL
    1. SELECT count(*) FROM 7215e_plugin_output WHERE output = 'OK - everything is fine';
    2. +----------+
    3. | count(*) |
    4. +----------+
    5. | 759 |
    6. +----------+
    7. 1 row in set (0.15 sec)

    --

    D/\N

  • any news here?

    Wouldn't it make sense to make the plugin_output somehow unique? maybe adding md5 field or something?

  • No, it doesn't make sense to make plugin_output uniq. This would lead to huge overhead for the mysql database on inserts. In fact, the table should be almost uniq already and the worst thing that would happen are a few extra used bytes on the disk.


    I will have a look into the logcache after my vacation and see if i find something.