How to Configure a Large MySQL thread_stack
While on Hypernode you have full admin privileges on your entire MySQL database, it is not possible to change root-owned MySQL related config files. This means that you can configure any runtime setting, but settings that go in the
mysqld.conf and need to be defined at start-up time or settings that you don’t want to re-apply every time MySQL is restarted sometimes can’t be set by the unprivileged
To facilitate some type of flexibility regarding these settings anyway we have a set of MySQL related opt-in settings that can be set using the hypernode-api or using the
hypernode-systemctl command-line tool (which implements this API).
Enabling a Larger thread_stack
On a Hypernode you can enable the larger
thread_stack by running this command:
$ hypernode-systemctl settings mysql_enable_large_thread_stack --value True
You can then check the progress of your change by running:
hypernode-log # or 'livelog' for an auto-updating variant of that command
Once the setting has been activated you will see the configuration file change on disk:
# Before $ cat /etc/mysql/conf.d/mysql-master.cnf | grep thread thread_cache_size = 256 innodb_read_io_threads = 8 innodb_write_io_threads = 8 # After $ cat /etc/mysql/conf.d/mysql-master.cnf | grep thread thread_cache_size = 256 thread_stack = 512K innodb_read_io_threads = 8 innodb_write_io_threads = 8
You might need to restart MySQL to load the new configuration:
$ hypernode-servicectl restart mysql
To check the size of your active
thread_stack you can run this MySQL query:
# Before $ mysql -e "SHOW GLOBAL VARIABLES LIKE 'thread_stack';" +---------------+--------+ | Variable_name | Value | +---------------+--------+ | thread_stack | 196608 | +---------------+--------+ # After $ mysql -e "SHOW GLOBAL VARIABLES LIKE 'thread_stack';" +---------------+--------+ | Variable_name | Value | +---------------+--------+ | thread_stack | 524288 | +---------------+--------+
Note that if you want to change this setting in a hypernode-docker instead of on your production Hypernode you will not be able to use the
hypernode-systemctl command-line tool, as that tool talks to the
hypernode-api which for obvious reasons is not connected to your local Docker environment. If you would like to change this setting in your local Docker as well, you can simply edit
/etc/mysql/conf.d/mysql-master.cnf as the
root user and add the
thread_stack line and restart the services.
We’ve noticed that for some larger Shopware installations the default
thread_stack size can become an issue. If you have the memory to spare changing the MySQL thread_stack from the default
512K might be a good solution (or at least quick fix to identify your problem).
In Shopware the search index & keywords tables have been seen to require a larger
thread_stack in some configurations. We’ve seen issues where queries on these aggregate tables started to cause issues in shops with north of 40K SKU’s during a search reindexation.
If you encounter these types of errors:
SQLSTATE[HY000]: General error: 1436 Thread stack overrun: 180608 bytes used of a 196608 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.
It might be a good idea to enable this feature and see if this setting addresses your issue.