Mysqld is taking too much CPU

Have a look at phpMyAdmin (if you have it installed) to see how many questions are being processed etc. It also gives hints to improve performance if it sees anything.

You can also log queries into a file and "tail" it to see live data.

The show processlist command returns this :

mysql> show processlist;
+-----+-----------------+-----------+---------------------+---------+------+----------+------------------+
| Id  | User            | Host      | db                  | Command | Time | State    | Info             |
+-----+-----------------+-----------+---------------------+---------+------+----------+------------------+
|  90 | clubin_comeback | localhost | clubin_comeback     | Sleep   |  830 |          | NULL             |
|  96 | proevents       | localhost | proevents_proevents | Sleep   |  652 |          | NULL             |
|  99 | phpninja        | localhost | phpninja_proevents  | Sleep   |  660 |          | NULL             |
| 112 | forge           | localhost | NULL                | Query   |    0 | starting | show processlist |
+-----+-----------------+-----------+---------------------+---------+------+----------+------------------+
4 rows in set (0.00 sec)

And the top command returns this for mysqld :

3484 mysql 20 0 2223528 875888 9800 S 0.7 42.7 0:04.33 mysqld

I'll look into logging queries, it might be a good thing.

That really doesn't show anything.

I would turn on logging or view the Monitor page on phpMyAdmin. Just don't leave logging on as it uses a lot of disk/cpu to do it.

Enable logs at runtime

Since MySQL 5.1 you can enable and disable logs at runtime.

To enable logs at runtime, login to mysql client (mysql -u root -p ) and give:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

This method works on any platform and does not require a server restart.

Display log results

Error log

With the above settings, you can display Error log using

tail -f /var/log/syslog

REMARK: If you do not specify Error log file, MySQL keeps Error log at data dir (usually /var/lib/mysql in a file named {host_name}.err).

General Query log

With the above settings, you can display General log using

tail -f /var/log/mysql/mysql.log

REMARK: If you do not define General log file, MySQL keeps General log at data dir (usually /var/lib/mysql in a file named {host_name}.log).

Slow Query log

With the above settings, you can display Slow Query log using

tail -f /var/log/mysql/mysql-slow.log

REMARK: If you do not specify Slow Query log file, MySQL keeps Slow Query log at data dir (usually /var/lib/mysql in a file named {host_name}-slow.log).

Ref: http://www.pontikis.net/blog/how-and-when-to-enable-mysql-logs

I enabled the logs and here's the result after a few minutes (/var/lib/mysql/{hostname}.log) : [removed link]

Here's the result of the /var/log/syslog for the same period of time : [removed link]

As you can see, there is a cron executed every minute that does not run any slow query.

And finally the {hostname}-slow.log shows only this :

/usr/sbin/mysqld, Version: 5.7.11 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument

Maybe I should restart the server to see what happens in the logs ?

I ran mysqltuner, maybe this'll help. Here're the results.

7m 55s after reboot: (~12% Memory)

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.11
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Data in InnoDB tables: 2M (Tables: 105)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 87)
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1054 (42S22) at line 1: Unknown column 'password' in 'where clause'
[OK] All database users have passwords assigned
mysql: [Warning] Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7m 55s (356 q [0.749 qps], 42 conn, TX: 308K, RX: 36K)
[--] Reads / Writes: 84% / 16%
[--] Total buffers: 172.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 341.9M (17% of installed RAM)
[OK] Slow queries: 0% (0/356)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/41.0K
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[OK] Query cache efficiency: 54.3% (76 cached / 140 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 25 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 19 total)
[OK] Thread cache hit rate: 85% (6 created / 42 connections)
[OK] Table cache hit rate: 54% (104 open / 191 opened)
[OK] Open file limit used: 1% (12/1K)
[OK] Table locks acquired immediately: 100% (103 immediate / 103 locks)
[OK] InnoDB data size / buffer pool: 2.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate

14m 15s after reboot : (~ 43% Memory)

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.11
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Data in InnoDB tables: 2M (Tables: 105)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 87)
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1054 (42S22) at line 1: Unknown column 'password' in 'where clause'
[OK] All database users have passwords assigned
mysql: [Warning] Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14m 15s (455 q [0.532 qps], 80 conn, TX: 412K, RX: 48K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 172.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 341.9M (17% of installed RAM)
[OK] Slow queries: 0% (0/455)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/41.0K
[!!] Key buffer hit rate: 74.0% (150 cached / 39 reads)
[OK] Query cache efficiency: 41.1% (76 cached / 185 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 34 sorts)
[OK] Temporary tables created on disk: 3% (63 on disk / 1K total)
[OK] Thread cache hit rate: 92% (6 created / 80 connections)
[!!] Table cache hit rate: 9% (271 open / 2K opened)
[OK] Open file limit used: 1% (12/1K)
[OK] Table locks acquired immediately: 100% (181 immediate / 181 locks)
[!!] Connections aborted: 7%
[OK] InnoDB data size / buffer pool: 2.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    table_cache (> 431)

Then it stabilizes to ~43%.

@depsimon I had the same problems after updating to MySQL 5.7. The solution was to change the database to MariaDB. After I switched to MariaDB everything was working super fast and no memory over 30%. It seems the majority of the people is using MariaDB instead of MySQL.

Forge default installation now comes with MariaDB selected by default.

@AlexRo I'll read about MariaDB. How did you proceed to make the switch?

I've a pretty recent Forge server (1st of February) but I didn't see anything related to MariaDB yet. Do you recommend some documentation?

Also I intend to use specific functions of MySQL such as st_distance and json, will that be compatible with MariaDB?

Thanks for your answer

@depsimon MariaDB is just a wrapper for MySQL https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/

Okay thanks all for your answers. Taylor suggested that I create a new server with MariaDB.