Vesta Control Panel - Forum

Posted: **Mon Jun 13, 2016 11:48 am**

Hello,

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

my.cnf

[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.

Posted: **Tue Jun 14, 2016 7:35 am**

Hello, thank you.

I am sure that mysql problem.

what should I do?

Posted: **Tue Jun 14, 2016 8:13 am**

XoXiLhJ0mn wrote: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                                                                                     |
+---------+-------------+-----------+-------------+---------+------+--------------+-----------------------------------------------

Posted: **Tue Jun 14, 2016 10:36 am**

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.

Vesta Control Panel - Forum

mysql sometimes high cpu.

mysql sometimes high cpu.

Re: mysql sometimes high cpu.

Re: mysql sometimes high cpu.

Re: mysql sometimes high cpu.