MySQL backend logimport becoming slower and slower

[smetj]

Hi Sven,

I've come to the point that the plugin_output column grew beyond 17921446 records.
The result of that is that every time logcache update is performed it becomes slower and slower until the point it inserts around 1 record per second(!).

The reason for that is the _plugin_lookup() function in https://github.com/sni/Thruk/blob/58c79bfdc71db180fed88092699b403f390d08f8/lib/Thruk/Backend/Provider/Mysql.pm#L1285

It seems to verify whether the plugin's output has already been stored and if not it inserts the output into the column.
Probably, the idea behind this is not storing the same data twice or more.

Since the output column is of type medium_text you can't create an index on it nor give it a unique constraint.
Wouldn't it be better to add an extra column with a unique constraint containing the hash value of the log and let the client calculate an md5sum for each insert?

Granted there will be a penalty on the client side (calculating the sums) but I'm pretty sure it's much better than a complete tablescan on each insert!

PS: Is table plugin_output cleaned up by invoking  /usr/bin/thruk -a logcacheclean=75 ?

Cheers,

Jelle

 

[smetj]

I've come to the point that the plugin_output column grew beyond 17921446 records.
The result of that is that every time logcache update is performed it becomes slower and slower until the point it inserts around 1 record per second(!).

When the reports are invoked to run manually, it seems that after unsuccessfully running for a long time they get removed from the list of available reports. (I had to restore from backup).

Is there some sort of system in place which deletes scheduled reports if they fail to run or timeout?

We're running Thruk 2.00

Tnx,

Jelle

[Sven Nierlein]

On 15.09.2015 10:01, smetj wrote:
>
> I've come to the point that the /plugin_output/ column grew beyond 17921446 records.

> The result of that is that every time logcache update is performed it becomes slower and slower until the point it inserts around 1 record per second(!).
>
>
> When the reports are invoked to run manually, it seems that after unsuccessfully running for a long time they get removed from the list of available reports. (I had to restore from backup).
> Is there some sort of system in place which deletes scheduled reports if they fail to run or timeout?
>

Hi Jelle,

no, Thruk never removes reports automatically (if you are talking about the report configuration). Thruk will however remove
the old report pdf files when starting a new report run.
And yes, i know the logcache is not yet perfect but right now, i don't have the time to wrap my head around it. It's probably not
fixed with a few tweaks here and there.

Cheers,
Sven

[smetj]

no, Thruk never removes reports automatically (if you are talking about the report configuration).

From what I've seen the report definition stored in /var/lib/thruk/reports is removed when you manually select it to run and it never pulls through because of the state of the DB.
I had to restore the report definition in question back to /var/lib/thruk/reports.

 

And yes, i know the logcache is not yet perfect but right now, i don't have the time to wrap my head around it. It's probably not
fixed with a few tweaks here and there. 

Yeah fair enough.  For now I think it's important to keep the plugin_output table under control.
It seems logcacheclean doesn't cleanup that table from what I see so I'll look into scheduling a separate cleanup job for that.

Cheers, 
Sven 

 

Thanks Sven!