我有个问题。我的网站很慢,我觉得是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
我怎么做这个问题?
谢谢。
3条答案
按热度按时间yqhsw0fo1#
rate per second=rps建议,供您的my.cnf[mysqld]部分考虑,以提高性能,
aras和furkan,如果你仍然没有创建索引,请发表评论,指出你需要帮助这个请求,通过索引管理来提高性能。
lf5gs5x22#
一句话:添加一些索引会有很大帮助:
细节
你需要
INDEX(islemkey)
在odemeler
.如果
expire
是一个DATE
或者DATETIME
,则应宣布为这样。如果是非标准日期格式,则需要转换。同上islem_baslangic
以及任何其他包含日期的列。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
ix0qys7i3#
使用root用户登录whm,从multi-php-manager,将网站切换到php7.1,为特定网站启用phpfpm。
其次,下载mysql tuner脚本,它将帮助您调整/etc/my.cnf设置以获得最佳mysql性能。
如果在所有这些更改之后仍然无法工作,那么您必须优化mysql数据库。