3

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.

  1. Is there a reason so many threads are in use?
  2. 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. Fresh install of ubuntu20

After adding performance_schema = 0 to /etc/nginx/my.cnf

performance_schema changes

Improve this question
5
  • This question has nothing to do with programming, therefore it is off topic here on SO. The DBA sister site of SO offers help in database admin questions like this one.
    – Shadow
    Sep 18, 2020 at 23:55
  • Yes. performance schema uses a little bit of memory. Because these are threads the memory listed is the total memory for the process. SHOW PROCESSLIST on the mysql prompt would be a more useful indicator as to what it is doing.
    – danblack
    Sep 19, 2020 at 0:15
  • 1
    What other configuration changes have you got? How much memory do you have? 1G isn't a lot of memory.
    – danblack
    Sep 19, 2020 at 0:17
  • Yeah this was a quick cheap setup for some testing to do. Nothing else installed except mysql :|
    – NZSteve
    Sep 19, 2020 at 3:42
  • Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Sep 28, 2020 at 15:00

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • innodb_buffer_pool_size should not be so big that it leads to swapping. 70% of RAM is a simple Rule of Thumb.
    – Rick James
    Sep 19, 2020 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.