centos 7,whm高mysql和mariadb cpu使用率%600

uttx8gqw  于 2021-06-17  发布在  Mysql
关注(0)|答案(3)|浏览(425)

我有个问题。我的网站很慢,我觉得是mysql。最多用户连接100个用户。大多数情况下30-40人。
我的服务器:
英特尔至强e3-1230v3
16 gb ddr3内存
240 gb ocz ssd磁盘
centos 7 whm公司
...

PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
12496 mysql   20   0   14.1g   1.3g  12228 S 576.1  8.2 116:38.62

/usr/sbin/mysqld公司
/etc/my.cnf公司:

port        = 1905
socket      = /var/lib/mysql/mysql.sock

# The MySQL Safe server

[mysqld_safe]
open_files_limit = 58000

# The MySQL server

[mysqld]
max_connections = 300
local-infile    = 0
socket      = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 1024M
table_open_cache = 4096
open_files_limit = 40960
max_allowed_packet=1073741824
sort_buffer_size = 4M
read_buffer_size = 4M
join_buffer_size = 4M
sort_buffer_size = 4M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 128M
thread_cache_size = 16

# query_cache_size= 32M

tmp_table_size = 64M
max_heap_table_size = 64M

# table_cache    = 1024

# Slowly log

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

# Uncomment the following if you are using InnoDB tables

innodb_file_per_table= 1

# innodb_data_home_dir = /var/lib/mysql

# innodb_data_file_path = ibdata1:50M:autoextend

# innodb_log_group_home_dir = /var/lib/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 8096M

# innodb_additional_mem_pool_size = 64M

# Set .._log_file_size to 25 % of buffer pool size

# innodb_log_file_size = 64M

# innodb_log_buffer_size = 8M

# innodb_flush_log_at_trx_commit = 1

# innodb_lock_wait_timeout = 50

# Connection Settings

wait_timeout = 60
skip-external-locking

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

# safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 256M
read_buffer = 8M
write_buffer = 8M

# Query Cache Settings

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M
skip-external-locking

[mysqlhotcopy]
interactive-timeout

我怎么做这个问题?
谢谢。

yqhsw0fo

yqhsw0fo1#

rate per second=rps建议,供您的my.cnf[mysqld]部分考虑,以提高性能,

read_rnd_buffer_size=256K  # from 128M to reduce handler_read_rnd_next RPS from 6 million +
innodb_io_capacity=1900  # from 200 to enable more of your SSD io capacity
thread_cache_size=100  # from 16 to reduce threads_created count of 24,136 in 8 hrs
aria_pagecache_buffer_size=64M  # from 128M to conserve RAM, only 2 blocks used in 8 hrs
key_buffer_size=128M  # from 1G to conserve RAM, 15M used was the High Water Mark

aras和furkan,如果你仍然没有创建索引,请发表评论,指出你需要帮助这个请求,通过索引管理来提高性能。

lf5gs5x2

lf5gs5x22#

一句话:添加一些索引会有很大帮助:

INDEX(islemkey)
INDEX(site, durum, islem_baslangic)
INDEX(durum, site, site_durum, id)

细节

UPDATE odemeler SET step='odeme_kontrol' WHERE islemkey = '78TNgyacgjwt'

你需要 INDEX(islemkey)odemeler .

`expire` varchar(255) DEFAULT NULL,
AND islem_baslangic LIKE '2018-11-29%'

如果 expire 是一个 DATE 或者 DATETIME ,则应宣布为这样。如果是非标准日期格式,则需要转换。同上 islem_baslangic 以及任何其他包含日期的列。

SELECT * FROM odemeler WHERE site = '71' AND durum = '1'
``` `odemeler` 需要这个复合材料 `INDEX(site, durum)` ; 也可以按相反的顺序列出。

SELECT id,tcno, miktar,durum,islem_baslangic,site,site_durum
FROM odemeler
WHERE site = '74' AND durum = '1' AND site_durum = '0'
ORDER BY id DESC LIMIT 20

需要

INDEX(site, durum, site_durum, -- in any order
id) -- last

原因
如果没有可用的索引,则 `PROCESSLIST` 必须扫描整个表。这需要大量的cpu。它会减慢每个查询的速度。然后这些查询就会堆积起来(这是一个很大的问题,在未来 `PROCESSLIST` 立刻。)
有了可用的索引,每个查询的执行速度会更快,占用的cpu也会更少。这就是索引的目的!
ix0qys7i

ix0qys7i3#

使用root用户登录whm,从multi-php-manager,将网站切换到php7.1,为特定网站启用phpfpm。
其次,下载mysql tuner脚本,它将帮助您调整/etc/my.cnf设置以获得最佳mysql性能。
如果在所有这些更改之后仍然无法工作,那么您必须优化mysql数据库。

相关问题