MYSQL на 10 миллионов строк, 3 ГБ объема отвечает на запрос по 2-3 секунды

может DNS настроен криво, без кэширующего сервера, а в какой-то момент происходит разрешение имени и тормоза?

По эксплэйну самых долго выполняющихся запросов

90% времени занимают операции сортировки. Они стандартные от Wordpress.

Можно ли тут что-нибудь предпринять?

Конф такой, с апреля его менял, экспериментировал, прошло 2 месяца и сейчас это самый быстроработающий

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

# временные таблицы – в памяти, перед этим запустить команду df -h и убедиться что что есть shm и она равна 50% озу
tmpdir = /dev/shm

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

max_join_size = 200M

max_allowed_packet = 256M

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

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

innodb_additional_mem_pool_size = 32M #внутренние нужды движка innodb

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 = 0 # кеш при больших нагрузках под вопросом, после долгих экспериментов был выключен 12.06.17. 

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

#query_cache_limit = 128M 

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, который просил увеличить

# эксперимент. не пойму что делает posible memoty usage. жрет не он. оставлено 10к
table_cache = 10000 #из highload-конференции (цитата - Я видел mySQL сервера со значением table_cache равной 100 000)

thread_concurrency = 12 #количество ядер процессора (6 физических, 12 виртуальных)

thread_cache_size = 4000 #равна max connections

table_open_cache = 10000

innodb_buffer_pool_instances = 62 #по рекомендации 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
log-slow-queries = /var/log/mysqld-slow-query.log

я конечно прошу прощения, но по моему общий смысл темы это

До чего дошел прогресс... Вспоминаю 2000 год, сервер размером со стиральную машинку, в котором 128 мегабайт оперативки, 12 гиг база на 6.5 mssql и все летает... а тут дожились. всю базу можно надцать раз запихнуть в оперативку, ядер как раньше серверов на весь регион и... тормоза.

Добро пожаловать в реальный мир, где базы шардят и юзают nosql

а почему не пихнуть ее в озу? Разве так не делают при 128гигах? Я нуб.

Все просто - поставьте SSD, а если есть есть деньги, то ssd raid 0.

ну так человек и спрашивает

а где почитать что не рекомендуется/не используется в хайлоде (тоже есть база на пару лимонов записей, долгие запросы).

Вообще, что-то слишком медленно обрабатывается. У меня база была на 6 млн. строк, там COUNT(*) занимала 0.2 сек, при этом дефолтный конфиг.

Я вообще не понимаю, зачем постоянно лазить в базу за данными? Настройте себе nginx fastcgi_cache, пишите в файлы. Отдавайте раз в час с базы, все остальное - статический html. Для блогов и новостных сайтов этого с лихвой хватает.

Так все давно закешировано в Варнише.

Но в дни перестройки кеша сервер просто падает и я боюсь что он скоро перестанет подниматься уже без моего участия...

Также настанет писец если он перезагрузится из-за сбоя электропитания или ватчдога - пока кеш не прогреется будут тормоза жуткие...

Тут многие говорят оптимизировать запросы, базы и т.п.

Отвечу по порядку.

1. Используется CMS WORDPRESS. "Оптимизировать" ее - это значить переписать ЯДРО. Это будет стоить кучи денег, и еще нужно найти кодеров реально высокого уровня, потому что ни мне, ни большинству исполнителей такое не под силу.

2. Вертикальное/горизонтальное масштабирование. Есть варианты, допустим базу можно разбить на несколько СУБД, типо: Пользователи, сессии, статьи, все поместить на разных серверах. Но зачем строить такую АРХИТЕКТУРУ, если проект сильно не растет? Он же не станет вторым Фейсбуком или ВК. Тут задачи поддерживать текущую нагрузку, а которой понятно что мешает что-то фундаментальное, а что, понять не могу. Пока все подозрение на дисковую подсистему, тут советуют SSD, я бы готов поставить, но сомневаюсь что дело в ней, iowait ведь на нуле, да и так все закешировано максимально в ОЗУ и кеше raid-контролера...

Сможете технически обосновать (для себя) выбор innodb вместо myisam?

raid 6 для бд не самый лучший вариант - в смешанном режиме операция записи будет сильно сказываться на производительности.

Для бд лучше использовать райд 10.

innodb_buffer_pool_size лучше ставить 70-80% (если все таблицы innodb).

Возможно оптимизация БД без изменения кода сайта.

Проверьте наличие покрывающих запрос индексов.

Скиньте сюда скрипты создания таблиц и explain "медленных" запросов.