Related

Product Now Available: Managed MySQL Databases Product
Mariadb replication to DO VPS from on-premise Question Question
Auto Increment problems MariaDB Question Question

Question

MySQL using high CPU

Posted April 14, 2020 362 views
MySQLMariaDB

Hello,

I have a problem with the MySQL service. I’m looking at “Load Averages” and there is a process with mysql owner.

It is using CPU %100 over. I’m doing kill that. It coming again after 5 seconds. What is the problem?

I’m getting the error when I try to restart MySQL. You can see error message below.

Startup Log
Apr 14 14:33:23 server.display-sales.de systemd[1]: Starting MariaDB 10.3.22 database server…
Apr 14 14:33:23 server.display-sales.de mysqld[5598]: 2020-04-14 14:33:23 0 [Warning] option ‘tableopencache’: unsigned value 1073741824 adjusted to 1048576
Apr 14 14:33:23 server.display-sales.de mysqld[5598]: 2020-04-14 14:33:23 0 [Warning] option 'tabledefinitioncache’: unsigned value 1073741824 adjusted to 2097152
Apr 14 14:33:23 server.display-sales.de mysqld[5598]: 2020-04-14 14:33:23 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB) starting as process 5598 …
Apr 14 14:33:23 server.display-sales.de mysqld[5598]: 2020-04-14 14:33:23 0 [Warning] Could not increase number of maxopenfiles to more than 10000 (request: 2097337)
Apr 14 14:33:23 server.display-sales.de mysqld[5598]: 2020-04-14 14:33:23 0 [Warning] Changed limits: maxopenfiles: 10000 maxconnections: 150 (was 150) tablecache: 4910 (was 1048576)
Apr 14 14:33:25 server.display-sales.de systemd[1]: Started MariaDB 10.3.22 database server.

Can you help?

Thanks
Best regards,
Koray ONELKE

Add a comment
wcshop
By:
wcshop

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer
KFSys April 14, 2020

Hi @wcshop,

The error 

 [Warning] Could not increase number of maxopenfiles to more than 10000 (request: 2097337)

means the limit is being hit somewhere. Let’s resolve that by editing any configured limits. The limits for the MySQL service are defined in the Systemd configuration file, which you should copy from its default location into /etc/systemd and then edit the copy.


sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service # or your editor of choice

Add the following lines to the bottom of the file:

LimitNOFILE=2500000
LimitMEMLOCK=2500000

Now reload the Systemd configuration with:

sudo systemctl daemon-reload

Restart MySQL and it should now obey the max_connections directive and your MySQL should start without a problem.

Regards,
KDSys

Reply Report
  • wcshop April 14, 2020

    Hello,

    Thanks for the answer. But there is no mysql.service file in this path “/lib/systemd/system/”.

    How can I find it?

    Thanks
    Best regards,
    Koray

    Reply Report
    • KFSys April 14, 2020

      Hi @wcshop,

      That’s odd. In that case let’s do it the other way. Run the following command

      locate mysql.service

      On your droplet. It will show all files and directories that have the ‘mysql.service’ string in them. From there you should be able to find the mentioned filed.

      Regards,
      KDSys

      Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help