2
2
20strannik082019-03-08 20:32:45
linux
20strannik08, 2019-03-08 20:32:45

What are the optimal mariaDB settings for opencart 2?

There is a VPS KVM 2 cores, 2GB RAM. Ubuntu 14.04 x32
For the third day I broke the whole head, I can’t figure out how to configure this database for optimal work with opencart (only Myisam engine is used). Through some edits, we managed to reduce the load on the processor, but judging by the monitoring (munin), the cache is used inefficiently...
On the previous settings, when the mysql query cache memory was loaded, it was used by 40%, but now it is at a maximum of 20%, as I understand it, it should be used to the maximum ... Yes, and the rest of the parameters confuse my mind, I can’t understand how they are more effective expose, at least in theory. Tell me, dear ones, how to configure the MariaDB \ Mysql config more correctly? I understand that all this is done in practice and over time. But I ask at least for the theoretically most optimal option, from which I could already build on ...
My config (my.cnf):

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
default-character-set = utf8

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[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		= /run/shm
lc-messages-dir	= /usr/share/mysql
log-error       = /var/log/mysql/error.log
slow_query_log  = /home/strannik/logs/mysql-slow.log # Включает лог медленных запросов
long_query_time = 1 # Минимальное время исполнения запроса для записи в лог

performance_schema = off # Производит мониторинг всей БД, снижает производительность на 25%

innodb = OFF
default-storage-engine = myisam
character_set_server = utf8
collation-server = utf8_bin
init_connect = "SET NAMES utf8 collate utf8_bin"

######### Базовые настройки #########
low-priority-updates
skip-external-locking
skip-name-resolve
skip-networking

#########  Ограничения ######### 
bind-address	        = 127.0.0.1
max-allowed-packet	= 2M
max-connections         = 100
# max_join_size
# max_sort_length

######### Настройки потоков #########  
thread-cache-size = 16
# thread_concurrency

######### Кэширование запросов ######### 
query-cache-limit       = 20M
query-cache-min-res-unit= 1M
query-cache-size        = 64m
# query_cache_wlock_invalidate
key-buffer              = 100M
open-files-limit        = 35000
tmp-table-size          = 32M
max-heap-table-size     = 16M
query-cache-type        = On
thread-stack		= 192K
table-open-cache        = 4096
sort-buffer-size        = 110M
net-buffer-length       = 8K
read-buffer-size        = 256K
read-rnd-buffer-size    = 512K
table-cache             = 1024

[mysqldump]
[mysql]
[isamchk]
!includedir /etc/mysql/conf.d/

Answer the question

In order to leave comments, you need to log in

1 answer(s)
S
Sanes, 2019-03-08
@Sanes

https://ruhighload.com/mycnfexample

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question