Python conf in Kharkiv, Nov 16 with Intel, Elastic engineering leaders. Prices go up 21.10

Ищу специалиста по оптимизации MySQL

Привет!

Наша команда сейчас работает над проектом «Волонтер». Идея проекта: создать общенациональную информационную систему, которая бы объединила в режиме онлайн украинские волонтерские организации и группы, поставщиков, производителей, «добровольцев» (помощь индивидуальными услугами) и «доноров» (финансирование работы волонтеров). Основная цель — подключить все волонтерские организации и благотворительные фонды Украины к единой информационной системе национального уровня.

Онлайн платформа предполагает создание трех типов «проектов»: донорские (сбор денег по принципу краудфандинговых платформ), дискуссионные (OpenIDEO) и презентационные (презентация социальных инициатив). На каждый проект прикручивается система комментариев, новостей, обмена сообщениями, корректировки ценовых предложений в режиме онлайн (волонтер нашел аптечки за 50 грн./шт., пользователь — поставщик или производитель сможет бросать под этим товаром свои предложения). Т.е. insert’ов и select’ов будет очень много. И это только в «публичной» части системы. А еще есть «закрытая» часть — для волонтеров, благотворительных фондов и инициаторов социальных проектов. Если, как планируем, объединим всех — это несколько десятков тысяч групп/организаций.

Запуск бета-версии намечен на 17 ноября. Но, из нашей команды вчера «выпал» человек, который отвечал за MySQL (php-кодинг + оптимизация MySQL на стороне сервера).

Сейчас нам нужен спец, который бы мог правильно настроить MySQL под серьезные нагрузки (переменные back_log, max_connections, table_cache и тд.) и проконсультировать по вопросу оптимальной реализации соединений с MySQL из наших модулей. Просто ужас, как нужен такой специалист.

upd 08.11.2014
Ребята, спасибо за ваши комментарии. Мне, барану в БД, — это дополнительный стимул начать изучать тему, чтобы в будущем можно было сразу оценить «правильность» предлагаемых решений.

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

LinkedIn
Допустимые теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Допустимые теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Даже если вы не используете транзакции и внешние ключи — все равно ваш выбор InnoDB (XtraDB). Возьмите Percona Server (улучшенная версия MySQL), который из коробки вам даст отличную систему бекапов — XtraBackup. Для начальной настройки вам хватит и онлайн визарда — tools.percona.com/wizard .

В MySQL основная проблема с запросами типа INSERT/DELETE/UPDATE в том, что при сколь угодно коротком запросе происходит блокировка всей таблицы. А это в свою очередь означает, что пока будет выполняться любой из этих запросов никакие другие происходить не будут.
В целом, если запросов такого плана мало и они происходят не очень часто, то проблема с конкурентным доступом в таблицы легко решается с LOW PRIORITY.
В таком случае все INSERT/DELETE/UPDATE будут дожидаться, пока выполнятся все запросы типа SELECT и потом будут выполняться сами, а если в очереди появится другой SELECT пока INSERT/DELETE/UPDATE ждет, то он тоже пойдет на конвеер. Тут можно ориентироваться на продолжительность самого длинного и среднего запроса в БД. Как правило, максимальная задержка INSERT/DELETE/UPDATE может составлять около 1-2 секунд.

Однако надо учитывать один существенный момент:
Если у Вас по каким-либо причинам происходит чередование SELECT и INSERT/DELETE/UPDATE, то никакие LOW PRIORITY Вас не спасут, очередь будет очень быстро заполняться из череды коротких запросов, а все остальное будет ожидать. На фронте это, как правило приводит к тому, что сайт долго генерирует страничку, т.к. сравнительно тяжелые пользовательские запросы постоянно стоят в очереди, дожидаясь снятия блокировки таблиц. А при коротком снятии блокировки их выполнение не успевает завершиться, и запрос снова попадает в очередь на ожидание.

С моей точки зрения я бы предложил перейти на PostgreSQL с тем, чтобы на корню решить проблему конкурентности. С точки зрения PHP это не должно быть сложным ввиду того, что базовые запросы примерно совпадают. Надо будет только чуть чуть подшаманить запросы.

В случае, если — же вопрос стоит таким образом, что переход не предусматривается — тут на мой взгляд вариант только один — избегать чередования запросов типа SELECT и INSERT/DELETE/UPDATE.

Относительно оптимизации СУБД
По моей практике оптимизация СУБД в MySQL дело не сложное. Работы по оптимизации надо вести под нагрузкой. Идеально — когда нагрузка растет по мере роста посещаемости сайта.
А все потому, что Вам будет понятно поведение СУБД только тогда, когда начнут формироваться очереди из запросов и их можно будет спрофилировать с тем, чтобы далее уже настраивать и оптимизировать.
А на стадии разработки оптимизация СУБД есть оптимизация PHP кода и запросов.

В MySQL основная проблема с запросами типа INSERT/DELETE/UPDATE в том, что при сколь угодно коротком запросе происходит блокировка всей таблицы.
Где такое написано?

есть два типа баз innodb и myisam. Иннодб будет локать только запись а не всю таблицу.

Не согласен. Не получается так.
Лок на строку все равно не даст сделать select ... where

Павел, правильно ли я понимаю: есть таблица в которой 98% операций — это INSERT и SELECT (чтение) и, если один пользователь вставляет инфу с помощью INSERT другой пользователь не сможет прочитать данные из таблицы с помощью SELECT WHERE? Если это так, не могли бы вы дать линк на соответствующий раздел из руководства MySQL?

Вот прямо так взять и выдать ссылку на статью затрудняюсь.

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

Тестировал.
Если просто сделать в цикле короткие select и короткие update много раз — все чтение с изменяемой таблицы приостанавливается, т.к. пока идут короткие селекты большие не успевают отработать.

Пришел к выводу, короткие апдейты часто — не плохо, как и селекты.
Плохо чередование много раз.

всегда думал что это случится только если строка входит в результат выдачи запроса

MyISAM — Locking granularity: Table

dev.mysql.com/...al-locking.html

MySQL grants table read locks as follows:
If there are no write locks on the table, put a read lock on it.
Otherwise, put the lock request in the read lock queue.

Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue.

Поэтому MyISAM хорош только если у Вас преимущественно SELECT-ы.
Ну или только INSERT-ы и SELECT-ы — без UPDATE, DELETE:

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks.

але в любому випадку MyISAM вже не актуально починаючи з MySQL >= 5.5 — вони нехіло попрацювали над InnoDB і, як результат, InnoDB — the Default MySQL Storage Engine
dev.mysql.com/...default-se.html

А вы не оказываете платную помощь mysql? Если да, то свяжитесь пожалуйста со мной iss2004@ukr.net

чего вы решили что к вам вообще кто то прийдет ? ну я понимаю — все хотят что у них было 1000 человек онлайн, но вам для 1000 онлай еще расти и расти. Не то что бы я подрезаю вам крылья но мы видели уже много национальных социальных сетей которые так и не смогли никого объединить, поэтому е парьтесь осособо с производительностью счас, начните подключать клиентов и посмотрите в процессе как все идет. Я бы вообще поддал сомнению что к вам кто то прийдет, потому что даже те кто в фейсбуке создал страницу — они работали над ее брендом, их знают люди, люди к ним идут на страницу и у них уже налаженны процессы. И тут приходите вы и говорите — давайте мы счас все переделаем и вы свой раскрученный бренд перенесете под нашу крышу, наврядли кто то сходу согласится.

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

MyISAM
сразу нах@й вместе с тем кто предложил

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

вообще не партесь за ранее тем более безо всяких бенчмарков
у вас нагрузок серьезных не будет — масштабы не те
вот эту книгу достаньте www.amazon.com/...ds=mysql tuning там все очень хорошо разжевано

Шикарная книга! Спасибо за подсказку.

и да стучитесь в личку если будут конкретные вопросы (у меня gmt+11 -так что могу тормозить с ответами)

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

У вас в дистрибутиве должны быть готовые конфиги, вроде my-small.cnf, my-medium.cnf, my-large.cnf, my-huge.cnf. Используйте их, а специалиста привлекайте, когда это потребуется, так как без нагрузки любой сетап будет отличным :)

Зачем MySQL под высокие нагрузки. Под высокие нагрузки нужна NoSql.
Прирост производительности обычно не менее чем на два порядка.

Я сегодня сравнивал ее в бенчмарках с Kyoto Cabinet. Упоротые японцы писали ее 12 лет и ты не поверишь моя на 30% быстрее. А в инмемори режиме с буфером и потом флаш на диск, в 10 раз быстрее. Речь идет о нагрузке в 8-10 млн вставок\чтений 16 байтных рандом ключей в секунду.
MySQL приляжет в сон при нагрузке уже на 50-100 тысячах операций в секунду.

Уже поздно что-то менять. 80% системы готово. И она заточена под MySQL. Возможно, позже придется пересаживаться

Возможно не прийдется пересаживаться. Кешировать наиболее тяжелые запросы. Как полумера.

Таблицы будут MyISAM.
Шел 2014 год... y u no use InnoDB (XtraDB etc)?

Преобладать будут SELECT и INSERT. Удаление, обновление информации будет происходить в «закрытой» части системы. Конфликтов из-за блокировки таблиц не будет: на одну волонтерскую группу, например, будет 1-2 администратора. В чем тогда преимущество InnoDB?

Конфликтов из-за блокировки таблиц не будет: на одну волонтерскую группу, например, будет 1-2 администратора
Я бы понял преимущество, если бы на одну волонтерскую группу было бы 1-2 таблицы, а не администратора — тогда бы манипуляции не мешались друг с дружкой.
Но вы сами понимаете, что это бред, все равно они все пойдут в одну структуру.
чем тогда преимущество InnoDB?
Транзакции, поддержка целостности данных (foreign keys).

Благо, еще есть время все перестроить

Допустим нужно удалить юзера (или вообще любой объект, какие в системе предусмотрены) и всю-всю информацию, которая только его. В случае InnoDB — достаточно написать один запрос на удаление юзера. Остальные таблицы связаны по полю userid, поэтому из них данные удалятся автоматически. Дополнительный код нужен только для тех систем, где нужно удалять файлы с диска. С MyISAM же: программист забыл код удаления чего-либо и база быстро становится мусорной свалкой.

Я уже понял, что наш выбор — InnoDB )

Подписаться на комментарии