very slow mysql on AtoM 2.6

[elizabet...@mcgill.ca]

Hello, 

We are migrating from AtoM 2.4 to a new server running 2.6.

AtoM 2.4 is running on RHEL 7.7 with mysql 5.6 and PHP 5.5

AtoM 2.6 is running on RHEL 7.9 with mysql 8.0 and PHP 7.4

We have about 74K information objects all told (data on atom 2.6 is the same as atom 2.4).

A CSV update file containing approx. 860 records was submitted yesterday at around 11am. It still has not completed now (28 hours later).  Shortly after the update CSV was submitted, an export of 900 records was submitted. Would this cause a slow down?

I don't see any errors in the logs, and when I check running mysql processes it appears that the update job is progressing : I see select queries, commits, and sleep statements executed in succession under the same process ID.  There are no recent entries in the mysql error log either.  It is simply slow.

Any suggestions on where to look for causes of this slow down?  Are there any recommended mysql settings we can review?

Thanks!

[Dan Gillean]

Hi Elizabeth, 

As you likely know, we don't test or develop AtoM against CentOS or RHEL, so there may be other dependencies at play here. In terms of MySQL, the one thing you might want to check are the SQL modes set up - see the end of this section of the installation docs, where we recommend what modes to set: 

• https://www.accesstomemory.org/docs/latest/admin-manual/installation/linux/ubuntu-bionic/#mysql
  

One of our developers pointed out that the 'block_nested_loop=off' setting can have a significant impact on performance. See: 

• https://groups.google.com/g/ica-atom-users/c/Ay0A2pWNAgs/m/DKwWYoMGCQAJ
  

We have some instructions on how to check and modify the SQL modes, here: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#check-server-sql-modes
  

Unless you've purposefully deployed multiple atom-workers, then a second CSV export being launched should affect things - that job will likely just be queued until the update import completes. It is possible that other factors in your installation environment (disk space, execution limits, memory) could be slowing things down - without knowing more about your installation environment, it's hard to say. Maybe try using top or htop to see if there are any noticeable process bottlenecks? See: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/troubleshooting/#monitoring-active-processes-and-checking-execution-limits
  

Let us know what you find, and hopefully if you haven't resolved the issue we can help provide further suggestions. 

Cheers, 

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

@accesstomemory

he / him

--
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-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/a9d54dae-f019-4460-b4b3-02086affa0dfn%40googlegroups.com.

[Jim Adamson]

Hello,

One thing you could also try is disabling MySQL binary logging. We disabled it because it was consuming lots of disk space, and didn't anticipate making use of it anyway (it's generally used for replication purposes). It might also have an impact on performance.

To also point out the block_nested_loop=off setting is removed from MySQL Server 8.0.20 and later, which, I think, effectively means it's off by default for recent versions:

MySQL :: MySQL 8.0 Reference Manual :: 8.9.2 Switchable Optimizations

As Dan suggests it would be good to see your system spec. When we moved to AtoM 2.6 in February we saw a weird issue where Elasticsearch would lose its index, so searching was broken. It turned out I'd specced the new server exactly as per the old one, with just 4GB RAM. 2.4's elasticsearch (1.7.6) worked without a problem with this amount of RAM, but with elasticsearch 5.x it needed the recommended 7GB to hold onto its index. We haven't seen a recurrence of this problem since increasing the RAM.

Thanks, Jim

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CAC1FhZK0_3D1xp9MEyODRbMBKv8SyPTpu38JmCXr0M%2BoAvnmMw%40mail.gmail.com.

--

Jim Adamson

Systems Administrator/Developer
Facilities Management Systems
IT Services

LFA/237 | Harry Fairhurst building | University of York | Heslington | York | YO10 5DD

+44 (0)1904 323859

Email disclaimer: https://www.york.ac.uk/docs/disclaimer/email.htm

[elizabet...@mcgill.ca]

Hello Dan and Jim,

Thanks very much indeed for the follow up.

Re our specs : we currently have 2CPU and 8GB RAM (PHP has been allocated 4GB). We are running mysql 8.0.23. Please let me know if you need other info.

I checked our mysql settings and this is what we have:

sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=off,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

So we have block_nested_loop=off in our set up.

I suspect that for mysql_mode  NO_ZERO_IN_DATE and NO_ZERO_DATE settings are causing problems because I am seeing date related errors in the logs.  We will change sql_mode to the two suggested settings.  Would either ONLY_FULL_GROUP or STRICT_TRANS_TABLES be causing problems also?  

Should any of the settings for optimizer_switch be adjusted?

We only have one atom-worker set up, so the second job was indeed just stuck waiting in the queue. 

Thanks again for your assistance.

[Dan Gillean]

Hi Elizabeth, 

I'm not sure about ONLY_FULL_GROUP, but STRICT_TRANS_TABLES is known to cause issues in AtoM at the moment, and we definitely recommend disabling that for now. We currently recommend only the following be placed in the SQL modes configuration file for AtoM 2.6 releases: 

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
optimizer_switch='block_nested_loop=off'

See: https://www.accesstomemory.org/docs/2.6/admin-manual/installation/linux/ubuntu-bionic/#mysql

In terms of clearing the stalled job: 

I've recently shared basic tips on managing the job scheduler in this thread - be sure to note the part about the fail counter! 

• https://groups.google.com/g/ica-atom-users/c/bI6eRWWb-zY/m/p-x-yuqVBQAJ
  

You can either use a task to clear ALL queued jobs, or else target a specific job via SQL, to see if the job scheduler can pick up on the remaining queued tasks if you have multiple tasks waiting on the blocked one. 

To clear all queued jobs: 

• php symfony jobs:clear

To target a specific job via SQL: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#sql-kill-job
  

Cheers, 

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

@accesstomemory

he / him

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/9b9f2c2d-a35e-4152-ab60-ae78cb4c847an%40googlegroups.com.

[elizabet...@mcgill.ca]

Thanks again Dan.

We will take a closer look at our mysql settings and let you know if that solves the issue.

Thanks much!

[Jojo Thomas]

Just came across this while searching for use_invisible_indexes. FYI - we had a strange issue where MySQL was not using the right Index on a Fact table. Setting SET GLOBAL optimizer_switch='use_invisible_indexes=on'; has resolved the issue and the correct index is being used. A query that was taking 30+ minutes is since returning in ~2 seconds. This might not be the case for you, but just putting it out here in case it can help someone. We are on MySQL Persona Port 8.0.25.

[Dan Gillean]

Interesting, thank you for taking the time to share what worked for you, Jojo! Hopefully this might help other users in the future. 

Cheers, 

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

@accesstomemory

he / him

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/d68fad85-1bf8-4921-8b73-584c8f043cdcn%40googlegroups.com.