Out of sort memory

[Zachary Spalding]

On a test system I was doing an upgrade and I reference the steps

posted to the group last month.

https://groups.google.com/g/archipelago-commons/c/YQmtyMRLrE0

Everything ran fine and I am able to log into my upgraded system.
But when I try to view the content I get the following MySQL error

Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size:

I am a little weak with working with docker, where or what is the best way to change the my.conf file for mysql?

[Diego Pino]

Hi Zack,

Sorry to hear this. First time I see that error. 

Which if the docker-compose files (I assume you are on https://github.com/esmero/archipelago-deployment/tree/1.0.0-RC1 ?) are you using?

What are the specs of your machine? If you are running out of memory (a very small EC2) you can disable the NLP container. It uses some resources you may want to recover.

Just to narrow this down I went and looked at your server:

Its seems like one of your custom views is involved in the issue since the general Content one is not throwing that error.

I also see Drupal is still on 8.9.2. I would recommend (so all matches) that you move to 8.9.13 first.

But also I see you just upgraded from mysql 5.17 to Mysql 8. (Which is done automatically by the container). Because of that, I would recommend checking on the mysql error logs first when that view error happens

docker logs -f esmero-db and see if there is some info

I would also do a docker-compose down and then a docker-compose up -d and check the logs for initialization issues. 

Lastly:

Using docker-compose you can affect almost any setting directly without editing the my.cnf

So for adding a larger buffer, try adding this.

Assuming you copied this file docker-compose https://github.com/esmero/archipelago-deployment/blob/1.0.0-RC1/docker-compose-linux.yml#L58

command: mysqld --default-authentication-plugin=mysql_native_password --max_allowed_packet=256M

 

You will notice that MYSQL has a flag for the sort buffer

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sort_buffer_size

So you can add at the end of the command

--sort-buffer-size=262144   (262144 is the default)

docker-compose down and then up -d

OR

docker-compose db restart (works sometimes but not sure it will get settings so down/up is a safer approach)

That said

You may want to check how much space you have left on your hard drive for mysql and if the permissions of the DB folder are the correct. Since may times buffer issues are because MYSQL can not write back to storage.

Keep me updated. If you do not solve this I can give it a try later tonight

PS: sorry, I wanted to share the instructions via email before noon but got derailed but a calls.

Diego Pino Navarro
Assistant Director for Digital Strategy

Archipelago architect
Metropolitan New York Library Council
PO Box 2084
New York, NY 10108 

--
You received this message because you are subscribed to the Google Groups "archipelago commons" group.
To unsubscribe from this group and stop receiving emails from it, send an email to archipelago-com...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/archipelago-commons/1975a97e-8723-4e9d-9cfb-7c7566a93110n%40googlegroups.com.

[Diego Pino]

Hi, me again

You can also use Kbyes in the command

So 

--sort_buffer_size=512K

Another update: Strange thing here is that that offending view is based on default/normal Drupal fields/SQL joins, users and Node owners. 

This is your query:

SELECT node_field_data.nid AS nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid

FROM

{node_field_data} node_field_data

INNER JOIN {users_field_data} users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid

WHERE (node_field_data.status = 1 OR (node_field_data.uid = 3 AND 3 <> 0 AND 1 = 1) OR 1 = 1 OR 1 = 1)

ORDER BY node_field_data.changed DESC

LIMIT 50 OFFSET 0

So updating Core (which will include the views module) may help too. 

Note: no SBF involved at all. Just pure drupal SQL strange-ness

If you have no luck, again, I offer some nightly check if you want me to.

Diego Pino Navarro
Assistant Director for Digital Strategy

Archipelago architect
Metropolitan New York Library Council
PO Box 2084
New York, NY 10108 

On Jan 25, 2021, at 4:50 PM, 'Zachary Spalding' via archipelago commons <archipela...@googlegroups.com> wrote:

[Zachary Spalding]

Add the --sort-buffer-size= to the docker-compose.yml worked for me.  After I restarted docker, test system was working beautifully

Zack

Zachary Spalding (he/him/his)
Systems Manager
Southeastern NY Library Resources Council
21 S. Elting Corners Rd.
Highland, NY 12528

Phone: (845) 883-9065 ext. 111
www.senylrc.org
Like us on Facebook! facebook.com/SENYLRC
Follow us on Twitter! @SENYLRC