Kushnerov.com ~ motion in emotion

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

PROCEDURE ANALYSE()

Стандартная функция, которая анализирует поля таблицы в запросе и выводит советы по оптимальной длине/типу полей. Например SELECT * FROM table PROCEDURE ANALYSE().

Известно, что использование типа полей занимающего большую память делает более медленной работу с таблицами. Enum быстрее чем Varchar, varchar быстрее чем text, medium int быстрее чем int и т.д. Часто можно оптимизировать типы в зависимости от данных в таблице.

Определение не эффективных индексов

Индексы в таблицах — большое благо, но не стоит забывать что кроме того, что индексы ускоряют выборки из таблицы (SELECT) они замедляют обновление таблиц (UPDATE) и добавление новых полей (INSERT), так как при каждом обновлении данных все индексы перестраиваются. Также индексы занимают место на диске. В таблицах где данные часто обновляются/добавляются использование индексов должно быть сбалансированным.

С помощью этого хитрого запроса можно увидеть 10 самых малоэффективных индексов во всей базе данных:

SELECT t.TABLE_SCHEMA AS `db`, t.TABLE_NAME AS `table`, s.INDEX_NAME AS `inde name`, s.COLUMN_NAME AS `field name`, s.SEQ_IN_INDEX `seq in index`, s2.max_columns AS `# cols`, s.CARDINALITY AS `card`, t.TABLE_ROWS AS `est rows`, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != ‘mysql’ GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != ‘mysql’ AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME LIMIT 10;

Взято отсюда: http://pastebin.com/f6b1c381c. Уберите LIMIT 10, чтобы увидеть статистику по всем индексам.

EXPLAIN – Определение используемых индексов и строк, которые нужно обработать MySQL базе данных для выполнения запроса.

Очень просто, добавляем к нашему запросу слово EXPLAIN. Например: EXPLAIN SELECT * FROM table WHERE var=value ORDER BY field. Очень полезно для просмотра эффективности индексов.

Полный отказ от ORDER BY rand();

Конструкция ORDER BY rand(); отдаёт поля из таблицы в случайном порядке. Основная засада в том, что при выполнении этой конструкции не используются индексы и осуществляется полное сканирование таблицы. Уже при нескольких тысячах записей могут начаться проблемы. Разумно переписать код, чтобы все записи брались из базы данных, добавлялись в массив и сортировались в массиве (shuffle в PHP). Если записей очень много, имеет смысл сделать кеширование — например делать случайную сортировку раз в час.

Отключить InnoDB если он не нужен

Это движки таблиц. MyISAM – быстрее и проще, InnoDB – умеет много полезного, например транзакции, отсутствие блокировок. Если все эти фишки не нужны, то кто-то советует оставаться на MyISAM, кто-то наоборот переходить на InnoDB. Я пока ещё этот вопрос для себя не решил и использую MyISAM.

В любом случае если InnoDB не используется стоит отключить его поддержку, т.к. сервер кушает лишнюю память и работает медленее. Для отключения добавляем skip-innodb в конфиг (/etc/mysql/my.cnf).

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

Некоторые запросы выполняются очень медленно. Скорей всего о большинстве них вы и не подозреваете, т.к. вы тестировали когда в таблице было несколько сотен записей, а сейчас там несколько десятков тысяч и т.д. Найти такие запросы, на которые стоит обратить самое пристальное внимание, можно если включить логгирование Slow Queries. В конфиг файле (/etc/mysql/my.cnf) прописываем:

log_slow_queries = /var/log/mysql/mysql-bin.log
long_query_time = 2

2 – это количество секунд, больше которых выполняемый запрос будет считаться медленным и добавляться в лог. Если добавить ещё строчку log-queries-not-using-indexes в логи будут добавляться все запросы, для которых не использованы индексы.

Затем перегружаем MySQL сервер (/etc/init.d/mysql restart) и в /var/log/mysql будут добавлятся файлы логов с медленными запросами за последние 7 дней (по умолчанию).

Кеширование в MySQL

Стоит конечно писать скрпиты так, чтобы запросов в базу данных было как можно меньше, но часть ваших проблем на себя может взять MySQL. Правильная настройка кеширования приведёт к тому, что повторяющиеся запросы будут браться из кеша и выполняться очень быстро. У меня например 75% запросов выполняется из кеша. Конечно, этот процент зависит от типа запросов, от размера самого кеша, от частоты запросов к базе данных и т.д. Обычно стандартная конфигурация не всегда подходит, т.к. для разной нагрузки нужны разные настройки. Чтобы узнать что менять, смотрим следующий пункт.

Автоматический тюнинг

С помощью простой утилитки mysqltuner можно обнаружить самые основные узкие места в конфигурации MySQL.

Выполняем на сервере:

wget http://mysqltuner.com/mysqltuner.pl
chmod u+x mysqltuner.pl
./mysqltuner.pl

Далее нужно ввести root логин и пароль к MySQL серверу, а скрипт проанализирует данные и выведет советы в автоматическом режиме. Я меняю параметры в конфиге, на которые нужно обратить внимание, а затем через несколько дней смотрю опять.

Также полезные советы можно посмотреть в phpMyAdmin если нажать ссылку “Текущее состояние MySQL” на главной странице.

Минимизирование изменения таблиц

Добавление или изменения данных в таблицы приводит к тому, что перестраиваются все индексы и сбрасывается кэш таблицы. Хорошо, если в таблице немного записей. У меня в нескольких проектах есть таблицы в которых несколько миллионов записей, благодаря индексам выборки из таблиц происходят быстро, но при изменении таблицы и при одновременном обращении к ней, происходит резкое замедление выполнения выборок (запросы SELECT).

Это я вот к чему, иногда для оптимизации работы с базой данных имеет смысл переделать логику работы скриптов. Например, я сейчас делаю кеширование, чтобы все обновления сохранялись в небольшой временной таблице, а добавление всех данных в большую таблицу (и её изменение) происходило только раз в сутки.

Оптимизация таблиц

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

Полезные ссылки по Оптимизации MySQL:

20 Советов по оптимизации MySQL
Сервер на стероидах
MySQL Optimization

Ну что же. Свершилось. Наконец-то добрались и до Минска.

Google Maps обновил карту пятилетней давности. Раньше снимки были настолько старые, что на них не было множество новых домов. Сейчас спутниковые снимки примерно прошлого лета (2009). Что приятно прописали все улицы и на карте стали появляться номера домов. Но пока далеко не все, примерно процентов 20-30 от общего количества в Минске. Но стоит сразу отметить качество карты выше чем у Яндекса – т.к. в той части где присутствуют дома, есть все дворовые проезды и есть названия различных строений (рынки, школы..).

Сегодня Минск опубликовал и Yandex. Yandex чуть опередил Google, они сразу опубликовали достаточно актуальную карту.  На карте есть все дома и работает поиск по номеру дома. Спутниковые снимки чуть более ранние, чем у Google – примерно середина-конец весны 2009. И там и там снимки примерно одинакового качества, но у Google слегка более чёткие и контрастные (возможно просто потому что больше зелени).

Сводная таблица:

Карта от Google более функциональная и лучше, но не закончена, поэтому пока нужно пользоваться картой от Яндекс. Радует, что теперь для поиска нужного дома можно не пользоваться старой программой CityInfo, с кучей рекламы и кошмарной пародией на карту map.by.

Вчера у моего модема (Zyxel 660 HTW2 EE) вдруг пропал Wi-fi. Ноутбук и коммуникатор просто перестали видеть сеть, хотя до этого никаких проблем не было.

Начал разбираться, потратил два часа — пробовал так и эдак, заново настраивал wi-fi, сбрасывал настройки на заводские, установил три разных прошивки, менял схемы подключения, степень шифрования и частотный диапазон wi-fi, ворочал модем, крутил антеннку в разные стороны. Перепробовал всё, что только можно. Не работает, хоть тресни.  Собирался уже в начале недели везти модем в сервис, а потом решил в последний раз отключить все провода от модема, и подключить заново. Начал подключать постепенно и вдруг чудо, заработало!

Оказывается, каким-то странным образом, на работу Wi-fi влияет сетевой кабель проведённый к телевизору. Когда сетевой кабель от телевизора подключен, то Wi-fi работает нестабильно через раз, когда отключен — работает без вопросов. В общем проблема крайне странная, наверное причина в какой-то хитрой помехе или в каком-то статическом напряжении. Для себя сделал вывод, что к настройке сети нужно подходить по-особому, т.к. ждать можно чего угодно =)

Зажарили и съели!

На сайте CNN — видео с разрушенного Гаити. Видео примечательно тем, что оно не ограничивается узкими рамками камеры, а полностью включает всё окружение на 360 градусов (и небо в том числе).

http://www.cnn.com/interactive/2010/01/world/haiti.360/index.html

Подобная технология для фотографий уже давно используется, но для видео не видел такого ещё раньше. Эффект погружения гораздо сильнее. В обычных видеороликах оператор всегда фокусирует внимание на каком-то объекте, а тут можно рассматривать отдельных персонажей, интересно смотреть ролик несколько раз.

Пишут, что вчера наблюдали паргелий в Гродно: http://habrahabr.ru/blogs/popular_science/80869/.

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

Фото с википедии:

Вчера паргелий наблюдал и я. Заметил поздно, когда солнце уже садилось. Свечение было только с одной стороны солнца (справа). Сразу понял, что это какой-то вид солнечного гало, но из-за того, что свечение было частично за недостроенным собором и была не ясна его форма, в википедии сразу его не нашёл. Выглядит феерично, особенно, когда я подумал, что солнце вроде обычно садится чуть правее в это время, а потом уже заметил настоящее солнце =) Жаль только что частично закрыто строением, в фотоаппарате подсел аккумулятор, а бежать на улицу было уже лень (к тому же нет рядом высокой точки).

Хорошо видно, что свечение ЗА собором.

Красивое природное явление, которое можно увидеть в морозный день с чистым небом, перед закатом. Нужно всего почаще смотреть на солнце… =)

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

Интересно, это ингридиент чая или нет? =)

Оформлял подарок супруге к Новому году, заклеил подарок белой бумагой, чтобы не было видно что внутри, нашёл карандаши в пыльной коробке на антресоли. Затем начал зарисовывать фрагменты, т.к. название подарка просвечивалось сквозь бумагу, срисовал оленей и Санту с какой то картинки, чуть-чуть увлёкся… Последний раз рисовал цветными карандашами в классе шестом, наверное…

Столпы моего мировоззрения рухнули, когда я обнаружил в коробке белый карандаш. В младших классах, в наборах советских карандашей или фломастеров было всего шесть цветов, очень редко восемь. Кроме основных цветов: красного, жёлтого, зелёного, синего и чёрного был только один дополнительный цвет. Чаще всего это был коричневый (чтобы деревья рисовать), но у кого-то был уникальный голубой, а у кого-то редчайший фиолетовый. Помню, как мне показали настоящий артефакт — фломастер серого цвета. Каждый владелец уникального цвета, карандаша не жалел, ярко выделяя свой рисунок этим цветом.  А сейчас одних синих и красных по семь штук, 24-битный цвет знаете ли =)

P.s. Жена ещё не видела, боюсь что отправит в детский сад =)

Наши беларуские телевизионщики начали копировать один из моих любимых сериалов “Теория большого взрыва”, называется “Теоретики”. Смотреть на это без слёз невозможно (скопировали сцену с кроссвордом один в один). Плагиаторы.

Раньше копировали “Наша Раша”,  если в оригинале там много смешных сцен, то “Наша Белораша” очевидно ориентирована на молодых наркоманов =)

Сегодня закупались к новому году в супермаркете, и меня уже несколько недель мучает простой вопрос.

Бананы – из Коста-Рики, стоят чуть больше 3000 бел. рублей за кг (примерно 1.1$)
Апельсины из Эквадора – 4500 за кг (~1.6$)
Памело из Китая – 5000 за кг (~1.7$)
Мандарины из Турции –  6000 за кг (~2.2$)

А наши беларуские огурцы стоят дороже всех фруктов и даже дороже курятины! Целых 9100 за кг (3.25 $). Загадка получается, ведь их не нужно вести кораблями через океан, цепочка посредников в несколько раз меньше и их просто выращивать (нужно только поливать и следить за температурным режимом).

Интересно, они действительно золотые, или просто все импортные дешёвые огурцы запретили ввозить? =)