mysql - Centos 7 , WHM 高 Mysql&MariaDB CPU 使用率 %600

Question

我有一个问题。我的网站很慢我认为是mysql。最大用户连接即时 100 个用户。大多数情况下 30-40 人。

我的服务器:

• 英特尔至强 E3-1230v3

• 16GB DDR3 内存

• 240 GB OCZ SSD 磁盘

• Centos 7 WHM-cPanel MariaDB

...

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

我该如何做这道题？

谢谢。

Answer 1

底线:添加一些索引会有很大帮助:

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

详情

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

您需要在 odemeler 上使用 INDEX(islemkey)。

`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 也少得多。这就是索引的目的!