Large "eventlog" table & re-indexing

[ppouchin]

Hi again,

I've upgraded our server to OMERO 5, and I saw that re-indexing could be beneficial, so I tried it. However, it's still running (I started the re-indexing procedure on the 18th...) and it has currently indexed 2 486 297 objects.

I've noticed quite a few messages of this kind:

Code: Select all

Null returned! Purging since cannot index ome.model.containers.Project:Id_1 for ome.model.meta.EventLog:Id_59

I suppose it's because the object has been removed since then ?

How many objects will it index ?
Is it 1 for each row in the table "eventlog" (which has 8 522 503 rows in my case) ?

Also, during the upgrade, I've noticed that optimizing the database takes quite some time, due to the processing of the "eventlog" table, so I was wondering if this table could be cleared...
But if the indexer is using it, I guess it cannot be done without hindering the search function...
So I guess I should keep this table with 8 522 503 rows (and counting...) as it is now ?

[jmoore]

Hi again

Hi

I've upgraded our server to OMERO 5, and I saw that re-indexing could be beneficial, so I tried it. However, it's still running (I started the re-indexing procedure on the 18th...) and it has currently indexed 2 486 297 objects.

Yikes.

I've noticed quite a few messages of this kind:

Code: Select all

Null returned! Purging since cannot index ome.model.containers.Project:Id_1 for ome.model.meta.EventLog:Id_59

I suppose it's because the object has been removed since then ?

Exactly.

How many objects will it index ?
Is it 1 for each row in the table "eventlog" (which has 8 522 503 rows in my case) ?

That's right.

Also, during the upgrade, I've noticed that optimizing the database takes quite some time, due to the processing of the "eventlog" table, so I was wondering if this table could be cleared...

There has been at least one other question about the eventlog table on the forums: http://www.openmicroscopy.org/community/viewtopic.php?f=4&t=3085

In summary, it is possible to clear some of the table, but it could have an impact on your searching.

But if the indexer is using it, I guess it cannot be done without hindering the search function...
So I guess I should keep this table with 8 522 503 rows (and counting...) as it is now ?

We're working on improving the searching situation at the moment, but there are a couple options as of today:

• let the current reindex complete and tell us how horribly long it was!
• provide us with some details on your eventlog table and we come up with a clean-up script (likely "remove all UPDATE-eventlogs older than X"). You might want to start with SQL1 below.
• you could try a different reindex method. `bin/omero admin reindex --full` doesn't use the eventlog table, but you'll need to manually set the last eventlog ID in the configuration table so that the indexer doesn't start again from the beginning, though. (See SQL2)

Cheers,
~Josh.

SQL1:

Code: Select all

select entitytype, action, count(*) from eventlog group by entitytype, action order by count(*);

SQL2:

Code: Select all

update configuration set value = (select id from eventlog order by id desc limit 1) where name = 'PersistentEventLogLoader.v2.current_id';


[kennethgillen]

Hi Pierre,

I'm also in the process of search reindexing a large server.

Here's a bit of psql which generates a percentage completion I thought you may find useful.

Code: Select all

select 'At ' ||  current_timestamp(0) || ', Percent indexed: ' || trunc(((select count(*) from eventlog el, configuration c where el.id < cast(c.value as int) and (c.name like 'PersistentEventLogLoader%')) * 1.0) / (select count(*) from eventlog) * 100, 2) || '%';

Best,

Kenny

[ppouchin]

Thank you for this command.

[sbesson]

Hi,

a Pull Request has been opened to include this command to the Re-indexing section of the OMERO documentation.

Cheers,
Sebastien