I have a problem.
My web site very slowly
I think it’s mysql.
Max user connection instant 100 users.
30-40 people in most cases.
My server :
-
Intel Xeon E3-1230v3
-
16 GB DDR3 Ram
-
240 GB OCZ SSD Disk
-
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
How Can I do this problem?
Thanks.
3
Answers
Bottom line: Adding a few indexes will help a lot:
Details
You need
INDEX(islemkey)
onodemeler
.If
expire
is aDATE
orDATETIME
, then it should be declared such. If it is in some non-standard date format, then it needs converting. Ditto forislem_baslangic
and any other columns that contain dates.odemeler
needs this compositeINDEX(site, durum)
; optionally they could be listed in the opposite order.Needs
Reason
Without a usable index, most of the queries showing in the
PROCESSLIST
must scan the entire table. This takes a lot of CPU. And it slows down each query. And then the queries stack up. (That was a lot of queries to have in thePROCESSLIST
at once.)With usable indexes, each query will come and go much faster and use much less CPU. That’s the purpose of indexes!
Login to the WHM with root user , From Multi PHP manager , switch the website to PHP 7.1
enable php-fpm for that particular website.
Secondly , download the mysql tuner script which will help you to tweak /etc/my.cnf settings for the best mysql performance.
if the things are not working even after all these changes then you will have to optimize your mysql database.
Rate Per Second=RPS Suggestions to consider for your my.cnf [mysqld] section for performance improvement,
Aras and Furkan, If you still do not have your indexes created, suggested by Rick James, please post a comment indicating you need help with this request to improve performance by index management.