Edit Archival Description Record opens very slow.

[Johan Pieterse]

Hi

AtoM 2.2.1

5.6.10 MySQL Community Server

PHP 5.6

I have about 7.5 million records and opening a record for view/edit is very slow (25-30 seconds).

Qubit_dev log gives slow query:

SELECT object.CLASS_NAME, object.CREATED_AT, object.UPDATED_AT, object.ID, object.SERIAL_NUMBER, information_object.ID, information_object.IDENTIFIER, information_object.PARTNO, information_object.OAI_LOCAL_IDENTIFIER, information_object.LEVEL_OF_DESCRIPTION_ID, information_object.COLLECTION_TYPE_ID, information_object.REPOSITORY_ID, information_object.REGISTRY_ID, information_object.PARENT_ID, information_object.DESCRIPTION_STATUS_ID, information_object.DESCRIPTION_DETAIL_ID, information_object.DESCRIPTION_IDENTIFIER, information_object.SOURCE_STANDARD, information_object.DISPLAY_STANDARD_ID, information_object.FORMAT_ID, information_object.SIZE_ID, information_object.TYP_ID, information_object.EQUIPMENT_ID, information_object.LFT, information_object.RGT, information_object.SOURCE_CULTURE, information_object.SHELF, information_object.ROW, information_object.BIN, information_object.MOVE_PERMANENT, information_object.IMPORT_ID FROM `object`, `information_object`  WHERE information_object.LFT<'5128360' AND information_object.RGT>'5128361' AND information_object.ID=object.ID;

When running with "FORCE INDEX(information_object_my_index)" it goes far below 1 second.

I cannot set a criteria with force. Any comments?

Regards

Johan 

[Dan Gillean]

Hi Johan,

Wow, 7.5 million records - this might be the biggest AtoM instance we know about! I'll ask our developers to take a look at this thread and see if they have suggestions. Today is a holiday in British Columbia, where Artefactual is based, so many of our developers are taking the day off.

Cheers,

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

On Mon, Feb 13, 2017 at 8:11 AM, Johan Pieterse <pieters...@gmail.com> wrote:

Hi

AtoM 2.2.1

MySQL db

I have about 7.5 million records and opening a record for view/edit is very slow (25-30 seconds). Even directly in the db.

Qubit_dev log gives slow query:

SELECT object.CLASS_NAME, object.CREATED_AT, object.UPDATED_AT, object.ID, object.SERIAL_NUMBER, information_object.ID, information_object.IDENTIFIER, information_object.PARTNO, information_object.OAI_LOCAL_IDENTIFIER, information_object.LEVEL_OF_DESCRIPTION_ID, information_object.COLLECTION_TYPE_ID, information_object.REPOSITORY_ID, information_object.REGISTRY_ID, information_object.PARENT_ID, information_object.DESCRIPTION_STATUS_ID, information_object.DESCRIPTION_DETAIL_ID, information_object.DESCRIPTION_IDENTIFIER, information_object.SOURCE_STANDARD, information_object.DISPLAY_STANDARD_ID, information_object.FORMAT_ID, information_object.SIZE_ID, information_object.TYP_ID, information_object.EQUIPMENT_ID, information_object.LFT, information_object.RGT, information_object.SOURCE_CULTURE, information_object.SHELF, information_object.ROW, information_object.BIN, information_object.MOVE_PERMANENT, information_object.IMPORT_ID FROM `object`, `information_object`  WHERE information_object.LFT<'5128360' AND information_object.RGT>'5128361' AND information_object.ID=object.ID;

When running with "FORCE INDEX(information_object_my_index)" it goes far below 1 second.

I cannot set a criteria with force. Any comments?

Regards

Johan 

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/18c22001-dbae-418c-8d12-35f476d0e001%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jesús García Crespo]

Hi Johan,

That's a huge improvement! I don't think we can hint indexes with Propel, sadly. I've been looking for something similar to sqlalchemy's with_hint() without much luck. I think you only option is to find the snippet responsible for that query and replace it with raw SQL.

Out of curiosity, have you created new indexes? José Raddaoui introduced a few in ca515e7f4c6d - that should be released in AtoM 2.4. In AtoM, we have to declare the indexes in `config/schema.yml` and use the `propel:build-sql` task to generate the new SQL. After that, we create the corresponding migration to match the database schema for users upgrading. If you're working on similar optimizations please send us a pull request! :)

Thank you!

On Mon, Feb 13, 2017 at 5:11 AM, Johan Pieterse <pieters...@gmail.com> wrote:

Hi

AtoM 2.2.1

MySQL db

I have about 7.5 million records and opening a record for view/edit is very slow (25-30 seconds). Even directly in the db.

Qubit_dev log gives slow query:

SELECT object.CLASS_NAME, object.CREATED_AT, object.UPDATED_AT, object.ID, object.SERIAL_NUMBER, information_object.ID, information_object.IDENTIFIER, information_object.PARTNO, information_object.OAI_LOCAL_IDENTIFIER, information_object.LEVEL_OF_DESCRIPTION_ID, information_object.COLLECTION_TYPE_ID, information_object.REPOSITORY_ID, information_object.REGISTRY_ID, information_object.PARENT_ID, information_object.DESCRIPTION_STATUS_ID, information_object.DESCRIPTION_DETAIL_ID, information_object.DESCRIPTION_IDENTIFIER, information_object.SOURCE_STANDARD, information_object.DISPLAY_STANDARD_ID, information_object.FORMAT_ID, information_object.SIZE_ID, information_object.TYP_ID, information_object.EQUIPMENT_ID, information_object.LFT, information_object.RGT, information_object.SOURCE_CULTURE, information_object.SHELF, information_object.ROW, information_object.BIN, information_object.MOVE_PERMANENT, information_object.IMPORT_ID FROM `object`, `information_object`  WHERE information_object.LFT<'5128360' AND information_object.RGT>'5128361' AND information_object.ID=object.ID;

When running with "FORCE INDEX(information_object_my_index)" it goes far below 1 second.

I cannot set a criteria with force. Any comments?

Regards

Johan 

--

You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/18c22001-dbae-418c-8d12-35f476d0e001%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Jesús García Crespo,
Software Engineer, Artefactual Systems Inc.
http://www.artefactual.com | +1.604.527.2056