Какие книги по серверной оптимизации связки mysql + apache на *.nix системах стоящие существуют?

Question

[Daeamon]

Необходимо провести оптимизацию работы MySql в связке с апачем на Ubuntu сервере.
Так как моих знаний в этой теме недостаточно хотел бы их пополнить из книг, какие книги мне помогут на этом пути?

Нашел пока эту:
www.spbdk.ru/catalog/765687.html

Размер базы:
Статистика запросов
TOP


Самая жирная таблица весит 314.5 МБ
Вот ее структура:

Answer 1

[Алексей Черемисин]

Вопрос немного некорректен. Есть отдельно оптимизация mysql, отдельно apache ( который в связке например с php) , отдельно php. Вот по всему этому есть отдельные книги.
Но я бы порекомендовал снести индейца, поставить nginx, поставить php-fpm, поставить redis. Прикрутить redis к php, а php-fpm к nginx. Настроить nginx, mysql, php-fpm по отдельности и кешировать результаты работы mysql и php через редис.
Ну и посмотреть на яву, питон и прочие альтернативы, вдруг понравится :-)

Comments

[Daeamon]

Проблем с кешированием у нас нет. Проблема именно в настройке базы. У нас свой сервак(арендованный) сервак ставили и настраивали не совсем квалифицированные специалисты(я и мой коллега), хочется обучиться и оптимизировать все настройки базы. База довольно массивная

[Алексей Черемисин]

А точно дело в настройках базы? А не в ее архитектуре? Ну тогда как минимум вам не нужна книжка по индейцу, просто прочитайте онлайн документацию по мускулу. Ее достаточно, да и самих настроек там штук десять, которые на скорость влияют.

[Алексей Черемисин]

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

[Алексей Черемисин]

Вот например, но все это есть в интернете - https://habrahabr.ru/post/254773/

[Daeamon]

Алексей Черемисин: в чем там дело я не знаю, потому что в этом особо не смыслю, сайт в принципе грузится нормально, но на серваке все 16 гигов оперативы забито полностью а проц при запросах шибко напрягается что странно.
Что за книжка по архитектурам и нормализации и почему тут не поможет книжка по оптимизации мускула?

[Алексей Черемисин]

0) смотрите статистику использования базы данных, это есть в доках! И есть ли индексы для ваших таблиц!
1) посмотрите, какие запросы грузят базу, для этого смотрите статистику по базе, например список медленных запросов, гуглить по "slow queries", это есть в доках по мускулу!
2) смотрите использование индексов ваших запросов, так называемый план исполнения запроса, гуглить по "mysql show explain"
3) все предыдущее вам ничего не даст, если архитектура базы у вас не нормализована под ваши запросы!

[Алексей Черемисин]

Ну и хотя бы выложили характеристики базы, сервера, приложения, частоты запросов, и хотя бы статистику top.

[Daeamon]

Алексей Черемисин: добавил в шапку скрины статистики, top и данные о размере базы

[Алексей Черемисин]

Daeamon: ух! Даже слово подобрать не могу правильного... Мрак! С таблицей! Нафига вы в нее столько блобов засунули?! Бейте эту таблицу на две-три. Да и не нужно блобы в базе хранить типа картинок и видео! Делайте это в файлах. И где список индексов и их использования? Перенесите все set option в файл конфигурации. В общем, с такой таблицей любой сервер не сдюжит. Нормализуйте базу.

[Алексей Черемисин]

Daeamon: и примеры запросов к этой таблице бы посмотреть! Наверняка же все поля скопом дергаете!

[Daeamon]

Алексей Черемисин: Я вас еще больше удивлю - это джумла(правда тут от нее осталось уже совсем мало).
Как тут все разбить? Это все запросы перелопачивать придется и запись в таблицу и вывод из нее.
На сайте куча переписанных и написанных модулей с нуля, куча скриптов которые взаимодействуют с этой таблицей. Запросы да тянут сразу все. Таблицу эту проэктировал не я.
Я бы вообще сделал пару полей и хватило бы. Мне кажется тут проще сайт полностью переписать с нуля чем эту таблицу разбить.

[Алексей Черемисин]

Индексы на эту таблицу есть?!

[Алексей Черемисин]

Ну и джомла не лучшая cms для нагруженных сайтов. И хотелось бы увидеть статистику медленных запросов. И установите наконец nginx и php-fpm и php-redis-sessions!

[Daeamon]

Алексей Черемисин: да индекс есть.

[Daeamon]

Да стоит nginx уже давно проблема не в этом.

[Daeamon]

В каком виде вам предоставить статистику медленных запросов? Есть за прошлый год лог.

[Алексей Черемисин]

А что тогда апач в топе делает?! Первые 5-10 медленных запросов достаточно, естественно в виде sql. И еще, если сесии php хранятся в файлах, то иногда этих файлов становится очень много и доступ к ним начинает сильно тормозить систему. Посмотрите phpinfo() и проверьте каталог с файлами сессий, затем поставьте редис и храните сессии в нем!

[Daeamon]

Алексей Черемисин:

0.028055
(SELECT a.id as id, a.title as title, a.alias as alias, a.catid as catid, a.introtext as introtext, a.fulltext as ftext, a.created as created, b.alias as calias, b.name as cname FROM k2_items as a left join k2_categories as b on a.catid = b.id WHERE a.published = 1 AND a.trash = 0 AND a.featured = 1 AND a.catid IN(138,137,129,114,113,97,54,40,39,37,9) AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-02-04 18:51:00' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-02-04 18:51:00' ) ORDER BY a.created DESC LIMIT 4) UNION ALL (SELECT a.id as id, a.title as title, a.alias as alias, a.catid as catid, a.introtext as introtext, a.fulltext as ftext, a.created as created, b.alias as calias, b.name as cname FROM k2_items as a left join k2_categories as b on a.catid = b.id WHERE a.published = 1 AND a.trash = 0 AND a.featured = 1 AND a.catid IN(39) AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-02-04 18:51:00' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-02-04 18:51:00' ) ORDER BY a.created DESC LIMIT 4) UNION ALL (SELECT a.id as id, a.title as title, a.alias as alias, a.catid as catid, a.introtext as introtext, a.fulltext as ftext, a.created as created, b.alias as calias, b.name as cname FROM k2_items as a left join k2_categories as b on a.catid = b.id WHERE a.published = 1 AND a.trash = 0 AND a.featured = 1 AND a.catid IN(40) AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-02-04 18:51:00' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-02-04 18:51:00' ) ORDER BY a.created DESC LIMIT 4) UNION ALL (SELECT a.id as id, a.title as title, a.alias as alias, a.catid as catid, a.introtext as introtext, a.fulltext as ftext, a.created as created, b.alias as calias, b.name as cname FROM k2_items as a left join k2_categories as b on a.catid = b.id WHERE a.published = 1 AND a.trash = 0 AND a.featured = 1 AND a.catid IN(138) AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-02-04 18:51:00' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-02-04 18:51:00' ) ORDER BY a.created DESC LIMIT 4) LIMIT 16;

0.024428
SELECT DISTINCT a.* FROM `persons_table` AS a LEFT JOIN users AS created_by ON created_by.id = a.created_by WHERE a.state = 1;

0.023117
SELECT a.*,uc.name AS editor FROM `infographics_data` AS a LEFT JOIN users AS uc ON uc.id=a.checked_out LEFT JOIN users AS created_by ON created_by.id = a.created_by LEFT JOIN categories AS catid ON catid.id = a.catid WHERE a.state = 1 AND a.catid = '100';

0.008889
SELECT b.rules FROM assets AS a LEFT JOIN assets AS b ON b.lft <= a.lft AND b.rgt >= a.rgt WHERE (a.name = 'com_infographics.data.22704' OR a.parent_id=0) GROUP BY b.id, b.rules, b.lft ORDER BY b.lft;

[Алексей Черемисин]

Я здесь криминала не вижу, 30 милисекунд на запрос нормально.

[Алексей Черемисин]

А что за процессоры и сколько их? И апач снесите, а...

[Daeamon]

Алексей Черемисин: файлов в папке с сессиями немного.

[Алексей Черемисин]

Апааач!!!!

[Daeamon]

Алексей Черемисин: как апач снесите ?) как же без него быть) вообще машина стоит с двумя ксенонами на ней стоит esxi, немного меньше половины ядер выделено на эту виртуалку.

[Алексей Черемисин]

https://docs.joomla.org/Nginx Вот так! Все запросы идут в nginx, который их передает в php-fpm напрямую. Апач не нужен!

[Алексей Черемисин]

И проверьте производительность файловой системы, есть грешок у esxi, когда у него производительность сильно падает при наличии снапшотов.

[Daeamon]

Алексей Черемисин: пробовал я эту связку, что то с меня с ней не срослось, Так на продакшен и не выкатил

[Алексей Черемисин]

Еще раз попробуйте, у меня и джомла и вордпресс и , прости господи, битрикс, и друпал так крутятся на десятке серверов.

[Алексей Черемисин]

Кое где приходится поднастроить php-fpm, обычно, но это дело 30 минут.

[Daeamon]

Спасибо, буду пробовать. Книжка как выяснилось и не нужна

Answer 2

[Сергей]

Книги? Достаточно пары статей из интернета, конфиги поправить и все

Comments

[Daeamon]

www.books.ru/books/mysql-optimizatsiya-proizvodite...

[Daeamon]

Да статей не достаточно. Я лично не нашел никаких более менее нормальных. Написан бред про буфер и другие параметры, а про профилирование бд, подстройку значений конфига конкретной базы в частном случае нет.

Answer 3

[Михаил Конюхов]

> mysql
если хотете делать это профессионально - изучите как mysql устроена. Это сделает Вам огромную пользу в оптимизации.

> php, apache
php - сам по себе не особо оптимизируется. Есть добавочные средства - кеши (APC, OpCache), есть некоторые опции в php.ini - изучите просто "что вообще можно настроить в php"

В целом - в целом оптимизация web - это оптимизация архитектуры - замена apache+php на php5-fpm+nginx.
Оптимизация дискового IO, оптимизация сети, etc...

Т.е. вопрос задан слишком общий, чтобы на него ответить в одном посте

Comments

[Daeamon]

меня интересует именно оптимизация MySql. Т.е. первый пункт в вашем сообщении. Да я хочу делать это профессионально. Что мне может в этом помочь? Хочу заказать для начала эту книжку www.spbdk.ru/catalog/765687.html

[Михаил Конюхов]

Смотрите - начать стоит с "поверхности". Что такое MyISAM , InnoDB, XtraDB, Aria - как устроенны эти хранилища? Как mysql работает с различными хранилищами?
По всем им имеется хорошее описание тут:
https://mariadb.com/kb/en/mariadb/xtradb-and-innodb/
https://mariadb.com/kb/en/mariadb/myisam-storage-e...
https://mariadb.com/kb/en/mariadb/aria-storage-engine/

Да, Вы увидели здесь слово MariaDB, а не mysql. Сразу оцените что еще есть помимо mysql? - percona, mariadb. Они разные и у всех свои плюсы и все sql-совместимы с mysql, т.е. не требуют переписывания кода.

Изучив какие есть стораджи данных - изучите, как mysql работает с соединениями - по потоку на соединения (+ опции настройки сего), узнайте что процедуры и фукнции mysql/maria/percona использовать неоптимально и почему.
Узнайте о блокировках, транзакция, режимах работы с вводом выводом на диск в mysql.
Узнайте о том, как работает оптимизатор запросов в mysql и что можно узнать из explain.

Потом узнайте о репликации Galera, это поможет Вам балансировать нагрузку на несколько нод.

Обязательно изучите вопрос блокировок и разных storage engine.

Вот такой список тезисов. Проблема в том что централизованно его сложно найти в одном месте.

P.S. используйте persistent connection - чтобы не производить авторизацию при каждом коннекте

[Daeamon]

Perfect Solutions: т.е. подобной литературы на русском языке в бумажном виде как я понял не существует?