Как ускорить большую базу?

Всем спасибо, кое-как исправил, собрав по кусочкам конфиг из разных мест, пока вроде устраивает работа...

[mysqld]

collation-server = utf8_general_ci

character-set-server = utf8

 

 skip-networking  #не использовать сеть, т.е. вообще не обрабатывать TCP/IP соединения.



 tmpdir = /dev/shm

 

 max_connections = 1000 # иначе много обслужить не может одновременно, но не больше maxclients апача

 

 max_join_size = 200M

 

 max_allowed_packet = 256M

 

 key_buffer_size = 20M #под неиспользуемый MyISAm, можно сократить до минимума

 

 

 innodb_buffer_pool_size = 20000M #Ставить строго 40% от ОЗУ (рассчитано опытным путем, т.е. есть еще затраты на каждый поток помимо буфера). 128гб*40%=52гб

 

 innodb_log_file_size = 512M #перед изменением сделать бекапы, после чего удалить старые логи



innodb_read_io_threads = 8



innodb_write_io_threads = 8



innodb_log_buffer_size = 16M



innodb_flush_log_at_trx_commit = 0 #бросает лог непрерывно, но есть угроза утраты записывающихся данных при аварии



innodb_flush_method = O_DIRECT



query_cache_type = 1 # кеш при больших нагрузках под вопросом, после долгих экспериментов был выключен 12.06.17. 

 

#Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение.



query_cache_limit = 1M 



query_cache_size = 0



# из статьи под миллионы запросов в секундку https://habrahabr.ru/company/pgdayrussia/blog/326204/

back_log=5000



innodb_open_files=4000 



innodb_file_per_table=1 #остановить сервер, сделать бекапы, залить заново

innodb_file_format=barracuda 



max_heap_table_size = 512M



tmp_table_size = 512M #musqltiner просил уменьшить, а потом уравнять с max_heap_table_size, который просил увеличить



 







thread_cache_size = 1000 #равна max connections



table_open_cache = 10000



innodb_buffer_pool_instances = 20 #по рекомендации mysqltiner



join_buffer_size = 8M



open_files_limit = 1024000 # иначе многопоток не тянет



sort_buffer_size = 2M



read_buffer_size = 2M #сильно тратят потенциальную память сервера по mysqltuner,  поэтому уменьшены вопреки рекомендациям



read_rnd_buffer_size = 2M



transaction-isolation = READ-COMMITTED #рекомендация Битрикс



long_query_time = 10







   

pid-file = /var/run/mysqld/mysqld.pid

datadir=/var/lib/mysql

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

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

bind-address = 127.0.0.1

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd



[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid



#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

Ставить нужно не 40%, а по объёму innodb таблиц + запас, и при условии наличии этой памяти, т.е. нужно учитывать ещё и другие опции mysql и другое ПО сервера (веб сервер и т.д.)

ну так я не говорю засрать кэш мелкими запросами, в кэш мускула можно помещать минимум и максимум запросы.

Если есть тяжелые запросы, то их лучше в память убрать чем они на диске будут!

В том то и дело, что query cache работает прозрачно и неинтуитивно. Он помещает в себя ВСЕ. И очищает когда наступит необходимость, а не как бы хотелось спустя N минут. И управлять этим нельзя.

Расскажите, как ограничить минимальный размер запроса? Хотя бы. Я уже не говорю о том, что тяжесть от объема данных может не зависеть.

можно перевести query_cache_type в 2

И кешировать только определённые запросы типа SELECT SQL_CACHE....

Хотя я уверен, что Вам этот трюк известен :)

query_cache_limit

query_cache_min_res_unit

query_cache_min_res_unit не ограничит размер запроса

Это минимально возможный размер блока памяти, который выделяется для помещения результата запроса в кеше

Это то верно, но человек, который не разбирается в настройках MySQL вряд ли знает, какие конкретно ему запросы нужно кешировать ;)

---------- Добавлено 02.08.2017 в 23:20 ----------

baas, Может быть мегасложный запрос, возвращающий небольшой ответ. И очень простой запрос, который вернёт много данных. Тут всё не так просто.