I am having issues with MySQL 5.7 using a lot of memory and a lot of threads on my prod server, and having some issues getting much results out of any changes I have made.
- Is there a reason so many threads are in use?
- How can I reduce the memory consumption down as its slowing the server a lot.
This is MySQL install on a fresh copy of ubuntu20. Nothing else was installed except mysql.
After adding performance_schema = 0 to /etc/nginx/my.cnf
Best Answer
By default, MySQL allows up to 150 connections, and each connection is handled by a separate thread. Reduce max_connections to, say, 50 and you'll see the number of threads go down.
Some memory is allocated per thread, other in a pool shared between all the threads. The defaults are generally sensible. If you are not using MyISAM tables, you can reduce key_buffer_size to 1M. Adjust innodb_buffer_pool_size based on the size of your InnoDB data. As a rule of thumb, at the very least your primary keys should fit into the buffer pool, but normally you would want more buffer pool than that.