mysql sometimes high cpu.

[turquaze]

Hello,

my server 4 thread 8 core.
64gb.
user increases, mysql sometimes %99.
few user, no problem.

my.cnf

Code: Select all

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
lc-messages-dir=/usr/share/mysql
log_error=/var/log/mysql/error.log
max_connections=110
max_user_connections=30
wait_timeout=30
interactive_timeout=50
long_query_time=5
query_cache_size = 1024M
tmp_table_size = 8G
max_heap_table_size = 512M
innodb_buffer_pool_size = 512M
thread_cache_size = 32
log=/tmp/mysql.log
!includedir /etc/mysql/conf.d/

help me.

[turquaze]

Hello, thank you.

I am sure that mysql problem.

what should I do?

[skurudo]

In Webmin, you could see from the webinterface many details of each connection. From this, you need to trace which SQL queries are being executed.

Well, here you can see mysql status too:
https://ip:8083/list/server/?db

ExampleShow

Code: Select all

MySQL localhost status
Uptime: 1565989
Threads: 2
Questions: 464457847
Slow queries: 230
Opens: 1619607
Flush tables: 1
Open tables: 4096
Queries per second avg: 296.590

+---------+-------------+-----------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id      | User        | Host      | db          | Command | Time | State        | Info                                                                                                 |
+---------+-------------+-----------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 3105102 | rnt_shp | localhost | rnt_shp | Sleep   | 0    |              |                                                                                                      |
| 3105103 | gld_ru    | localhost | gld_ru    | Query   | 0    | Sending data | SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.p |
| 3105105 | root        | localhost |             | Query   | 0    |              | show processlist                                                                                     |
+---------+-------------+-----------+-------------+---------+------+--------------+-----------------------------------------------

[Elizine]

Enabling the query cache can possibly help, like switching query_cache_type to ON and setting the query_cache_size to 1G.

If that helps, you could also try to play with it's amount, because different values can give different result, even "more is better" rule sometimes doesn't apply.