Как оптимизировать RunCMS? или Оптимизация CMS для начинающих

Решил перевыложить свою старую статью - она местами уже потеряла актуальность, да и RunCMS практически уже умерла, к сожалению - оставив после себя несколько веток-форков -на одной из которых и работает ScaleModels.ru, но люди спрашивают. Увы - некоторые вещи, приведенные тут уже устарели за несколько лет, некоторые - спорны, но  пусть будет в этом блоге - до кучи. 

RunCMS - достаточно неплохая система, дружественная для новичка в Web, которая позволяет худо-бедно, но решить большинство типовых задач для небольшого сайта одним приемом - «из коробки». Однако сайты имеют свойство либо умирать, либо развиваться, и в случае когда ваш ресурс выходит из категории «homepage про меня и моего хомячка Вову» (c) - начинаются проблемы с производительностью. Ситуация осложняется тем, что большие и серьезные CMS (такие как Drupal или Joomla) давно прошли этот этап «детских болезней» и способы решения подобных проблем для них давно известны и доступны. К сожалению, крупных проектов на RunCMS встречается не много, и информации о ней очень мало. На правах бывшего coreteam разработчика RunCMS я хочу рассказать о способах увеличения ее производительности.

Данная статья не предназначена для полных новичков - подразумевается, что вы в состоянии отличить PHP от Perl, способны разбираться в чужом коде, и примерно представляете архитектуру RunCMS (или XOOPS-like систем). Желательно, чтобы вы знали что такое my.cnf и где его искать ;-)

Есть два варианта хостинга - обычный и VPS/VDS.

Обычный хостинг

В плане оптимизаций - это жопа (если вкратце). Особенно если у вас провайдер, который не хочет ставить дополнительный софт и его настраивать. Начиная этак от 500 уников в день - готовьтесь к прессингу с его стороны - «давайте-ка переедем на выделенный сервер» (с) Один мой прошлый хостер на просьбу установить поддержку mbstring на свой шаред-хостинг спросил - «А зачем он вам нужен?» Но в принципе, мигрировать на свой сервер - это логичный вариант.

Что можно сделать для оптимизации RunCMS непосредственно в ней самой на SHARED хостинге?

MySQL таблицы для сессий

Находим MySQL таблицу sessions и конвертируем ее в тип MEMORY (если стоит PHPBB - то такая судьба должна постигнуть и phpbb_session) Этим мы выиграем небольшое количество скорости - но на КАЖДОЙ страницы сайта. Единственный минус - после перезагрузки сервера всем пользователям прийдется перелогиниваться, но результат того стоит.

Кэш страниц для гостей-анонимусов

Включаем кэширование страниц для гостей на как можно большее время - однако учитывайте, что для этого нужно хотя бы 50-100 мб свободного места на диске?

Включаем кэширование MySQL средствами класса DB

class/database/mysql.php

<?php

    var $file_cache = true;

?>

Это то, что можно было сделать нормальными ШТАТНЫМИ способами RunCMS, а теперь начинаем экспериментировать

Файл-кэш SELECT запросов

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

<?php

$db->query($sql, false, false, ‘имя_файла’,  время_кэширования)

?>

ставя подходящие значения. Учитывайте только то, что кэш отрабатает только в том случае если результат запроса будет прогнан через fetch_row или fetch_array , а вот fetch_object в текущей версии не кэшируется?

Скомпилированное ядро

Включаем экспериментальное компилированное ядро (класс RCCoreApi) файл class/core.php

<?php

var $compiling = true;

?>

Данное «ядро» была введено мной в версии 1.6.1 - оно позволяет сэкономить примерно 6 SQL запросов на каждой странице, но несколько повысит нагрузку на PHP.

Побочный эффект - не будут задействованы новые изменения в таблицах блоков, модулей и т.д. так что перед подобными действиями эту опцию нужно выключать. Логика работы следующая - поставили модули, настроили блоки, сайт работает нормально - включили скомпилированное ядро. Нужно поставить модуль - выключили компиляцию, поставили модуль, удалили файл кэша, включили заново.

Вообще если вы любознательны и способны экспериментировать и разбираться с чужим кодом - внимательно изучайте стандартные классы RunCMS 1.6.1 и 1.6.2.

Немного яда и кидания какашек

Несмотря на то, что netshark просто вышвырнул меня из разработки RunCMS и вычеркнул из списка живых team, а впоследствии - передал код в лапы Farsus, который ее успешно и угробил :-) - в коде осталось много экспериментальных неофициальных настроек для увеличения производительности сайта

Например -

Кэш деревьев

Установить в классе xoopstree.php

<?php

define('RC_TREE_CACHE', 1); 

?>

это сильно поможет при работе с большими деревьями категорий - в таких модулях, как news, например

Кэширование блоков

Если внимательно посмотреть на табличку newblocks - вы увидите поле cache_time. При установке его в целое положительное значение N, блок будет закэширован на N минут. Обратите внимание - кэшировать имеет смысл только те блоки, которые выглядят ОДИНАКОВО для всех пользователей - если содержимое блока будет разным, то возможен конфликт.

RCCache и RCCachedPage

Использование классов RCCache и RCCachedPage, написанных совместно с Shurik2k5. В репозитории RunLiveCMS можно найти версии этих классов не только для файлового кэша (как в RunCMS), так и в базу MySQL и Memcache - что предпочтительней, с (или без) serializing данных

Настройка выделенного сервера (VPS/VDS)

Если же вы понимаете, что сайт будет расти дальше - то предстоит часть вторая - настройка выделенного сервера.

Ниже только ОБЩИЕ рекомендации, куда стоит смотреть

• Имеет смысл поставить PHP акселератор - на моем сервере стоит Zend Optimizer + eAccelerator. 
• Тщательно настроить сервер MySQL - не забывайте про кэш запросов самого сервера и ограничение по времени медленных запросов
• После настройки Apache - отключите access логи на картинках, оставив для них только error
• Имеет смысл избавиться от .htaccess файлов, снеся все редиректы и настройки непосредственно в httpd.conf
• Повесить на front-end (перед Apache) nginx и настройте его на отдачу статических файлов (таких как картинки, CSS, JS) - либо вообще отказаться в пользу Nginx + PHP-FASTCGI.
• Используйте RAM-диск для критичных файловых кэшей - в моем случае туда сохраняется файловый кэш класса DB, compiled_kernel, блоки и кэши некоторых страниц
• Обязательно используйте программы top, mtop и apachetop для мониторинга своей системы - ведь лучше вас ее не знает никто

По настройке выделенных серверов написано достаточно много, в частности тут - про Nginx, PHP, Memcache и тут про MySQL и насморки.

В моем случае - самый сильный выигрыш был от введения (по порядку важности)

• Гостевое кэширование
• Nginx
• настройка MySQL
• RAM диски на кэши кусков страниц, блоков и темплейтов форума /modules/forum(ну или phpBB2)/cache (всего около 200МБ RAM) - у меня форум, статьи и главная страница - основная нагрузка
• eAccelerator
• Таблицы сессии в MySQL MEMORY

Все остальное, честно говоря - из разряда "экономить на спичках" - не помешает, но без этого можно обойтись

Возможно, кому-то эти советы покажутся детским садом и «мурзилкой», но кому-то - сэкономят несколько минут «гугления» и чесания в затылке.

Изначально было опубликовано тут.

Twisted или Tornado - без разницы - все идиоты

Внимание - Важное сообщение для блоггеров-питонистов!

Если у вас появилась блестящая идея попробовать новый сфероконический тест, который показывает как Tornado уделывает Twisted - то выдохните. Выключите компьютер, погуляйте на улице и заодно - пересмотрите главную цель вашей жизни. Интернету не нужен еще один бессмысленный график производительности.

Похоже, что уже всем стало ясно что "матч века" - Twisted.web против Tornado от Friendfeed показал, что ни одна из сторон не особо победила, но и не особо проиграла - и в тоже время стопудово - что обе стороны выглядят достаточно глупо.

Во-первых, Twisted. Сейчас моя компания использует его за небольшую часть функциональности, потому что ТОГДА - это был самый простой способ, что мы нашли для отправки трафика через различные сетевые интерфейсы на Linux машинах. Мы никогда не имели с ним никаких проблем. Единственная причина, по которой мне необходимо его когда-нибудь тронуть - это чтобы увидеть, как что работает.

Тем не менее, Twisted, вероятно, самая запарная программная библиотека. Каждый раз, когда я открываю этот код, я чувствую, что я забрел в ночной бар на берегу Джерси, где все пьяные в хлам и уже давно сорвали свои рубашки. Twisted классная библиотека, но в тоже время - НЕДОСТАТОЧНО классная, чтобы действительно называться "Twisted". Это Python- программистская версия одежды и бейсболок Ed Hardy, которая все еще висит в стороне (модная вещь, которая не используется). Когда я копался в этом коде и мои коллеги спрашивают меня, что случилось, единственным адекватным ответом было я "НЕ СЕЙЧАС, шеф - я запускаю этот хренов реактор ".

Теперь вы можете понять, почему появилась и существует такая хрень, как Tornado.

Хотя я постоянно не рекомендую делать такие вещи как Tornado - Friendfeed все-таки ее сделали. Из тех графиков, которые я видел, Tornado просто незначительно быстрее, чем Twisted на обслуживании большого количества одновременных запросов. НЕЗНАЧИТЕЛЬНО. Очевидно, в Friendfeed полагали, что достаточно небольшая разница в скорости была достаточным основанием тратить свое время и что-то переписывать заново- что обычно и делает каждый разработчик, которому становится скучно на работе. Веб-фреймворк на Python? О боже - как это оригинально! Я думаю, что это один из последних уроков книжки "Изучи Python за 24 часа".

В Friendfeed потратили много времени, пытаясь оптимизировать количество запросов в секунду, отображаемого на графике, но, возможно, им следовало бы тратить больше времени на оптимизацию ВОТ ЭТОГО графика - вместо первого:

Во всяком случае, когда речь идет о выборе Twisted vs Торнадо для веб-фреймворка, я использую Django. Почему? Потому что это работает, и мое время ценно.

Достаточно вольный перевод вот этой заметки. Спасибо автору

Кстати, достаточно интересно пишет, хотя и многие слова приходится долго думать - перед тем как перевести на русский.

Как оптимизировать сервер? - реальные данные - теория без практики мертва

Это судьба - вслед за вчерашней заметкой про оптимизацию выделенного сервера на сайте были опубликованы весьма интересные материалы, которые привели к росту просмотров страниц на 54 тысячи - это составляет рост примерно 140%.

Итак, график LI

Скачок достаточно большой, но давайте посмотрим, как машина, настроенная по описанным ранее методикам с этим справилась - на графиках Munin еще видны вчерашние значения, так что можно сравнить с ними (хотя бы на глаз).


Трафик, прокачанный через firewall - разумеется, вырос, причем - значительно



Процессы и потоки - увеличились

MySQL немного изменился в пределах погрешности; коннекты - не изменились.





Memcache немного подрос, но незначительно


А самое главное - ЦПУ и Load Average практически не изменились

Вывод

nginx с кешом HTML страниц сильно спасает от резких скачков нагрузки от незарегестрированных пользователей.

Рискну предположить, что создатели многих супер-пупер-мега-стартапов, постоянно падающих от пресловутого великого и ужасного Хабраэффекта, об этом не знают ;-)

З.Ы. Люблю, когда теоретические выкладки подтверждаются практическими данными.

Как настроить сервер? или оптимизация сайтов на PHP (Nginx, PHP, MySQL, Memcache)

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

Если же вы еще и не профессиональный HighLoad Linux-админ, а, например, разработчик (как я), то в процессе настройки прийдется ловить много-много граблей...  ;-)

Хочу немного улучшить состояние вашего лба, рассказав про те шишки, что набивал самостоятельно или с помощью более "администых" друзей. Специальные оптимизации на уровне правок PHP-кода не рассматриваю, это отдельная тема, сейчас - только сервер, консоль и вы, ну может где пару PHP скриптов немного поправить понадобится - буду считать, что свою CMS вы чуть-чуть знаете.

Ниже рассматривается достаточно часто встречаемая комбинация для сайтов: nginx + php (nginx+apache c php немного отличается - вместо fastcgi_xxx будет proxy_xxx).

Да, и считаем, что сейчас все компоненты вашей системы (nginx, php, mysql, memcache) крутятся на одной машине - как оно обычно и бывает на частных ресурсах.

PHP

• Пускаем как fastcgi через сокет
• Pconnect к базе - спорно
• Не забываем акселератор (моя юзает eAccelerator) - хотя тоже есть спорные моменты

eAccelerator

Только для production машин можно отключить проверку Modified time - это абсолютно не подходит, если на сервере идет отладка скриптов - иначе будете ловить постоянные глюки и проблемы - включать только после того как сайт стал стабилен.

eaccelerator.enable="1"
eaccelerator.optimizer="1"
eaccelerator.check_mtime="0"
eaccelerator.shm_only="1"

Временые диски

tmpfs для /tmp и сессий PHP - все это сносится в память, если ее достаточно. В /etc/fstab добавляем

tmpfs                   /tmp                    tmpfs   defaults        0 0
tmpfs /var/lib/php tmpfs size=200M,nr_inodes=1m,nosuid 0 0

MySQL

• Проверяем, включен ли query кэш
• Также используем сокет
• Если в вашей CMS используется таблица сессий - ставим для них тип MEMORY (если нет текстовых полей) - это приведет к сбросу всех залогиненных пользователей при перезагрузке сервера, но добавит немного скорости. Ну или если это неприемлемо - то надеюсь, она уже переведена в InnoDB? ;-)
• Определяем оптимальное количество коннектов - статистика хотя бы за месяц (я юзаю munin). Ставим в 1.5-2 раза больше среднего.

Более подробно про оптимизацию MySQL можно прочитать тут.

Memcache

Если вся солянка крутится на одной машине - то также используем коннект через сокеты (по умолчанию не используется - стоит ip адрес). В таком варианте работы memcache возможны проблемы с некоторыми средствами мониторинга memcache (которые работают также по IP).

Не забываем в PHP использовать сжатие при работе с MEMCACHE.

Nginx

Ограничиваем число коннектов и запросов, особенно на ресурсоемкие скрипты

http {
...
    limit_req_zone $binary_remote_addr zone=one:10m rate=5r/s;
    limit_zone three $binary_remote_addr 10m;

Включаем кэширование для неавторизованных гостей

http {
...
fastcgi_cache_path /var/cache/nginx levels=1:2 keys_zone=wholepage:50m inactive=15d max_size=5000m;
        location ~ .*\.php$ {
            limit_conn three 20;
            include         /etc/nginx/fastcgi.conf;
            fastcgi_param   SCRIPT_FILENAME $document_root$fastcgi_script_name;
            fastcgi_pass    unix:/var/run/php-fastcgi/php.socket;
            #access_log  /var/log/nginx/domain.access.log  my_combined;
            fastcgi_cache wholepage_guest;
            fastcgi_cache_valid 301 302 304 10m;
            fastcgi_cache_valid 200 360m;
            fastcgi_cache_valid 404 5s;
            fastcgi_cache_key "$request_method|$host|$request_uri";
            fastcgi_pass_header "Set-Cookie";
            fastcgi_ignore_headers "Cache-Control" "Expires";
            fastcgi_cache_bypass $cookie_phpbb_session $arg_nocache $arg_PHPSESSID;
            fastcgi_no_cache $cookie_phpbb_session $arg_nocache $arg_PHPSESSID;
    }

Где phpbb_session - имя куки по которой идет авторизация

Все запросы с ?nocache=1 и PHPSESSID попадают на PHP-бэкенд сразу без кэша не забываем открыть скрипт, по которому идет авторизация (в том числе и всякие капчи - их можно переписать на ?nocache=1) - тут возможно прийдется немного поковыряться и в коде CMS тоже.

        location /register.php {
                limit_conn three 20;
                include         /etc/nginx/fastcgi.conf;
                fastcgi_param   SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_pass    unix:/var/run/php-fastcgi/php.socket;
        }

Nginx - общие рекомендации

Очень нежелательно использовать location с регекспами - только простые префиксы вида

location /mymodule {}

Если от регекспов никак не избавится - то лучше их обрамлять простым location (Причина - сложноуловимые перехлесты в логике парсинга URL - кто за кем будет следовать)

location /mymodule {
  location ~ \/mymodule\/(.*)\.php$ {
    чего-то
  }

}

Отрубаем access лог для картинок

Разумеется - это только основные моменты оптимизации, на самом деле их гораздо больше. А какие трюки для уменьшения нагрузки на сервер знаете вы? Пишите в комментах.

HeadHunt: нужны Highload Python, AppManager и тестеры

Мы ищем несколько человек - специалистов по highload Python, один application manager и хороших тестировщиков в СПб.

"Мы" - это ООО «Стрим», дочерняя компания ОАО «МТС»

Разработчик платформенных решений ( Python, Highload)

Обязанности:

• разработка и дальнейшее сопровождение серверной части медийных сервисов для проекта Омлет.ру (в первую очередь сервиса управления и распространения легального видео-контента);
• консультирование по вопросам высоконагруженных систем.

Требования:

• опыт разработки на Python от 2 лет;
• хорошее знание SQL, опыт работы, а так же оптимизации и тюнинга СУБД PostgreSQL или MySQL;
• опыт работы с no-SQL решениями,  в проектах по запуску высоконагруженных веб-сервисов (от 500 тыс показов в сутки);
• аналитический склад ума, коммуникабельность, знание основ ООП, хорошая математическая база;
• дополнительными плюсами являются: знание и опыт применения различных языков программирования, новейших технологий, самообучение и саморазвитие, английский язык, опыт создания несложных UI

Если будет практический опыт Tornado/Twisted - вам большой такой плюс.

Специалист по передаче в эксплуатацию (application manager)

позиция подразумевает деятельность на стыке разработка ПО – эксплуатация, т.е. задачи связанные с передачей в эксплуатацию после разработки и тестирования. Подразумевает навыки системного администрирования, навыки написания документации, разумную дотошность.

Задачи:

• написание скриптов деплоя приложений или настройка специализированной среды развертывания приложений;
• приемка серверных приложений после тестирования;
• передача приложения в эксплуатацию;
• валидация документации по установке приложений;
• написание документации по эксплуатации;
• анализ поведения приложений при эксплуатации, выявление сложностей эксплуатации приложений; 

Требования к кандидату:

• владение linux на уровне базового администрирования;
• владение windows на уровне продвинутого пользователя;
• опыт написания скриптов на shell;
• знание одного из языков: php, ruby, python;
• опыт написания документации;
• аналитический склад ума;

Специалист по тестированию

Обязанности:

• тестирование серверных компонент видео-сервиса и всего сервиса в целом, в том числе - автоматизация тестирования и нагрузочное тестирование

Требования:

• образование в сфере ИТ, желательно понимание основ программирования;
• опыт функционального и нагрузочного тестирования от 2 лет;
• хорошее знание SQL;
• опыт работы в проектах по запуску высоконагруженных веб-сервисов (от 500 тыс. показов в сутки);
• аналитический склад ума, коммуникабельность, знание основ ООП;
• владение инструментами JMeter или аналогичными;
• дополнительными плюсами являются: знание и опыт применения различных языков программирования, знание английского языка.

Условия для всех вакансий:

• вся белая ЗП, оформление по ТК;
• ненормированный рабочий день;
• 31 день оплачиваемого отпуска;
• испытательный срок 3 мес.
• ДМС после прохождения испытательного срока;
• корпоративная мобильная связь;
• квартальные и годовые бонусы;

Если ваше резюме удовлетворяет требованиям на 3/4 - смело шлите его на michael(dot)neradkov(dog)gmail(dot)com. 

Буду признателен, если поделитесь информацией со своими знакомыми.

Заранее спасибо!

Как сделать sharding картинок в PHP между несколькими серверами?

Сегодня небольшой хак - боюсь, что скоро их будет тут больше, чем методически грамотных решений. ;-(

Когда вам нужно разшардить картинки/статические ресурсы по разным поддоменам для ускорения загрузки - можно использовать очень-очень грязный хак.

Дано

4 поддомена:

• http://st1.xxx.ru
• http://st2.xxx.ru
• http://st3.xxx.ru
• http://s4.xxx.ru

По ним и нужно раскидывать превьюшки - особенно это актуально, когда их много на страницах.

Функция получения url для превьюшки картинки после небольшой доработки выглядит так

 public function thumb_url($file = false) {
  $path = $this->path_by_file($file);
  $url = CMS_URL.'/'.$path.'/thumbs/'.$file;
  
  $key = md5($url) & 3;
  
  $static_url = 'http://st'.($key+1).'.xxx.ru/'.$path.'/thumbs/'.$file;
  
  return $static_url;
 }

Чем плох этот вариант?

В случае если добавится еще сервер, то из-за изменения функции распределения - пропадут все кеши у пользователей - и все картинки будет грузиться заново. Неприятно, но несмертельно.

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

Короче - не делайте так.

А как правильно?

А как в том случае сделать подобное распределение методически грамотно  - это тема для отдельного поста. Может  быть и напишу, но позже.

Сравнение нагрузки PyCurl vs HTTPClientFactory+Deferred в сервисе на Twisted

Решил перевыложить с хабры свое небольшое старое исследование по поводу производительности Twisted при использовании  PyCurl и HTTPClientFactory - пусть лежит все в одном месте, раз уж начал активно писать в блог.

В процессе разработки одного проекта на Python+Twisted (распределителя запросов между несколькими СМС гейтами Kannel) пришлось переписать вызов URL различных сервисов с разных серверов c блокирующего вызова через PyCurl на неблокирующий (client.HTTPClientFactory + deferred). Чтобы иметь перед глазами реальные данные о нагрузке, решил дать много запросов и посмотреть на результаты

Описание теста

Использовался Jakarta/Apache Jmeter - очень полезная софтинка, к сожалению, почему-то применяют ее для тестов достаточно редко (может, просто не знают о ее существовании?), но мне она весьма нравится - ибо возможности у нее большие и даже краткое описание наврядли влезет в несколько постов - если кому интересно, могу потом пробежаться по ней вкратце.

В 200 потоков прогнано 100 запросов на один и тот же сервер, но со случайными данными (чтобы исключить кэширование). Прогонялось на обыкновенной рабочей машине с GUI под Ubunta — так, для примерной оценки.

Результаты теста

Одна ошибка — не справилась база, так что погрешностью можно пренебречь.

Графики распределения

Блокирующий (PyCurl)

Неблокирующий (deferred)

Итоги

Как видно из результата теста — при одинаковой нагрузке при использовании defered минимальное значение отклика сервера почти в 2.5 раза меньше (из-за отказа базы), а максимальное — меньше в два раза, чем у блокирующего. Среднее значение - также меньше в 2 раза.

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

Настройка медленного MySQL - о насморках и гильотине

Последнее время часто вижу советы по оптимизации MySQL, в которых авторы сразу (не глядя на ситуацию) сразу рекомендуют - поставить Percona, поставить SSD.

Нет, конечно - варианты обновления софта и железа помогут - но этот как-то... лечить насморк гильотиной - быстро и точно пройдет. ;-) Давайте вспомним стоимость SSD - для компаний как бы пофиг, а для частного вебмастера - сумма в 500$ на дороге не валяется.

Мониторинг - всему голова

Разговор об оптимизации, на мой взгляд совершенно неразумно вести без мониторинга и построения графиков нагрузки по времени - я использую munin, кто-то любит zabbix, есть и другие. Ну да выбор - вопрос в большей степени религиозный, суть-то в следующем - некоторые админы очень любят поднять преждевременную панику при затормозившем сервере - дескать, вот я зашел!! у меня все тормозит !!!! и срочно кидаются все оптимизировать.

Стоять! ;-) Выдыхаем и думаем, теперь вдыхаем и продолжаем думать.

Вот если вы временно заболели насморком - следует ли вам прямо сейчас делать операцию по удалению гайморита? Вот и с сервером - не нужно делать скоропалительных выводов, основываясь на единичном срезе времени. В текущий момент может быть все что угодно - случайный всплеск активности ботов, забытый кем-то cron скрипт бекапа и так далее. "Ширше надо смотреть, товарищи" (с) Так что ставим мониторинг и смотрим как себя машина ведет в течении нескольких суток/недели.

Теперь немного советов для тех, кто не торопится с брутальными методами "а-ля гильотина" (с)

Прежде всего - запускаем скрипт-анализатор mysqld, мне известны два основных

• http://www.opennet.ru/tips/info/1754.shtml
• https://launchpad.net/mysql-tuning-primer

После выполнения они дают рекомендации - что стоит подкрутить/изменить. Обязательно - сервер mysql перед анализом должен проработать под нагрузкой не менее 2 суток, а вообще-то - чем дольше, тем точнее будут выборки и рекомендации.

Дальше список типовых проблем mysqld

• мало памяти на индексы (особенно критична нехватка памяти для таблиц InnoDB, MyISAM переживает ее более спокойно). Как косвенный признак - сильная загруженность диска - из-за нехватки памяти mysql отчаянно свапится/пишет tmp таблички на диск
• много постоянно открытых коннектов к базе - уменьшаем - но опять таки - смотрим по времени - сколько РЕАЛЬНО используется.
• для PHP - часто рекомендуют включать pconnect, на мой взгляд - оно того не стоит.
• для InnoDB включаем innodb_table_per_file. Процесс миграции описан тут.
• СПОРНО - если памяти много - рекомендуют загнать /tmp в RAM -> улучшится скорость работы с таблицами tmp, хотя мое мнение - если памяти хватает лучше просто дать базе памяти побольше - и временные таблички будут не нужны
• смотрим внимательно на CurrentLockRation в результатах скрипта анализатора
  Current Lock Wait ratio = 1 : 8943
  Чем больше второе число - тем лучше.

Как увеличить Current Lock Wait? 

Тщательно изучать структуру своих таблиц и запросы - если часто идет запись, которая лочит таблицу - переводим на InnoDB, MyISAM оставляем для редко обновляющихся табличек, добавляем индексы и т.д.

Медленные запросы SQL

Ну и понятное дело - не забываем включить slow_query_log и просто оптимизировать кривые запросы - поверьте, их больше, чем можно предположить. 

У меня был случай, когда хороший грамотный фриленсер сделал мне задачу с некоторыми запросами - причем программист действительно неплохой, просто с нормальной посещаемостью  не работал. 

Через некоторое время сервер начал загибаться. При просмотре запросов к базе у меня зашевелились волосы на всех участках тела - там было что-то в стиле  JOIN ... GROUP BY CONCAT(SUBSTR(), SUBSTR())  и т.д. - уже сейчас точно не вспомню. 

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

Ну и пришлось переписывать код самому - с помошью нескольких простых запросов и мемкеша.

Итог

• Мониторинг по времени
• Изучение
• И только потом - оптимизация