zabivator16 мар 2013 в 12:14

Проблемы MySQL оптимизатора

3 мин

29K

MySQL *

+82

Комментарии 64

80x86 16 мар 2013 в 12:28

В Марии с этим такая же неопределённость?

zabivator 16 мар 2013 в 12:30

Я давно не общался с разработчиками MariaDB, но если кто и перепишет оптимизатор — так это они.
Но это будет уже не совсем MySQL.
Про проблемы эти они знают.

80x86 16 мар 2013 в 12:34

Ну, я с надеждой на такие ответы и спросил.

Спасибо.

jetman 16 мар 2013 в 13:12

А в Drizzle?

zabivator 16 мар 2013 в 14:36

Drizzle… У него куча других проблем.
Не смотрел, но не думаю, что они решали проблемы описанные в посте.
У них другие цели.

eexo 19 мар 2013 в 12:14

Ввсегда радует этот оптимизм, что если кто-то что-то и сможет так это МарияДБ. Только монти и Ко, больше никто. Особенно если забыть кто собственно создал весь этот MySQL со всеми его проблемами и «ошибками в ДНК». Конечно-же виноваты Плохие Менеджеры, которые давили на Монти чтоб он разрешал выпускать отстой, Тупые Разработчки которые писали кривой код — за всеми ведь не уследишь, Обстановка на Рынке которая заставляла прогибаться под Инвесторов-которым-только-деньги-давай. Всегда виноват Кто-то Другой. Но теперь у них своя компания ~~с блекджеком..~~ без Тупых Менеджеров и Плохих Разработчиков, так что теперь точно будет все в порядке и теперь-то они точно все сделают как надо. Осталась правда Обстановка на Рынке, но зато будет кого винить в случае факапа.

Vurtatoo 16 мар 2013 в 13:19

Может ОНИ подумают хорошо и сделают очень хороший продукт с названием MySQL 6 без обратной совместимости с предыдущими MySQL…

zabivator 16 мар 2013 в 14:37

Тогда это будет новый продукт, просто с тем же именем.

achekalin 16 мар 2013 в 23:11

Хорошо помню, как еще несколько лет после появления MySQL 4 все авторы публично выкладываемых скриптов/веб-приложений боялись использовать что-то большее, чем умела «тройка», просто потому, что на хостингах тройка стояла еще долгое время. И была дилема: либо ты используешь те же вложенные запросы, и имеешь кучу проблем с особо непонятливыми юзерами твоего скрипта (которые не обращают внимание на приписку в списке необходимого ПО, что MySQL должна быть "(4.0+ only)", либо воспринимаешь MySQL как тупое хранилище, не умеющее особо ничего «лишнего», и всю логику оптимизируешь именно под него.

По мне, так MySQL6, несовместимый с «предыдущим MySQL» — это просто конец кое-как воспрянувшему в глазах разработчиков «образу» MySQL как базы, которая хоть что-то умеет. Правда, нет худа без добра, даже дешевые хостинги наконец-то поставят себе PostgreSQL :)

Правда, совсем не факт, что, если в 6-ке что-то и поломают, это заметит такой уж большое число людей: далеко не все используют эту СУБД на 100%.

kaichou 18 мар 2013 в 08:17

дешевые хостинги наконец-то поставят себе PostgreSQL

кто ж в дешёвых хостингах с настройкой этого монстра разбираться будет?
мускул тем и хорош, что из коробки адекватно работает.

а для абсолютного большинства проектов смена версии не будет ничем страшной,
бо 90% программистов и так не пользуются БД сложнее, чем LEFT JOIN и GROUP BY.

VolCh 16 мар 2013 в 14:14

Материал интересный, но вот посылки, по-моему, ложные.

развивать не ломая обратной совместимости его нельзя

Как я понял, речь о внутренних интерфейсах. Если синтаксис останется прежним, то подавляющее большинство юзеров (админов и разработчиков) ничего не заметят

MySQL со сломанной обратной совместимостью никому не нужен.

Весьма спорно. Даже если синтаксис запросов и/или внешних API изменится незначительно. Инерция очень велика.

zabivator 16 мар 2013 в 14:35

> Как я понял, речь о внутренних интерфейсах. Если синтаксис останется прежним, то подавляющее большинство юзеров (админов и разработчиков) ничего не заметят

Неверно. Сломанный Storage Engine API == переделка всех storage engine.
Многой информации там просто нету.
Например, Merge Engine — я вижу проблемы в реализации новых функций.
С другими тоже непросто — те же индексы в InnoDB придётся _существенно_ переделывать.

В общем, там кругами по воде будет расходиться.

> Весьма спорно. Даже если синтаксис запросов и/или внешних API изменится незначительно. Инерция очень велика.

Storage Engine API — внешнее.

VolCh 16 мар 2013 в 14:42

Как много людей/компаний разрабатывают свои Storage Engine?

Когда я вызываю какую-нибудь mysql_query(), я разве напрямую к нему обращаюсь?

zabivator 16 мар 2013 в 14:44

Когда вы вызываете mysql_query(), вы зовёте код, который зовёт внешние (относительно MySQL) storage engine.
unique row count добавить в API просто
Починить NULL'ы — это архитектурная переделка оптимизатора и индексов во всех storage engine.

Я в посте недостаточно правильно выразился.
Проблема не только в API как таковом, но и в том, как под него заточен оптимизатор и engines.

symbix 16 мар 2013 в 16:02

А null-ы в libinnodb тоже сломаны, или это mysql-евская специфика?

zabivator 16 мар 2013 в 16:08

Прочитайте пост. Это API сломано. Все storage engine, by degisn.

symbix 16 мар 2013 в 17:21

Прочитал. Я понимаю, что innodb — тот, что в составе mysql — так работает.

Есть отдельный embeddable innodb (ныне HailDB), который используют в других СУБД как storage engine (тот же riak) — потому и поинтересовался, сломана ли innodb изначально, или был сделан специальный патч именно для mysql — может, вы в курсе :)

symbix 16 мар 2013 в 17:26

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

youROCK 16 мар 2013 в 21:59

Потому что это было подарено проекту MySQL, нежели написано самостоятельно :).

symbix 16 мар 2013 в 22:05

Да, сразу видно, что другие люди писали :)

VolCh 16 мар 2013 в 16:09

Для меня это все же внутренний API, ну, или API расширений/плагинов. Но не внешний точно, с внешним взаимодействует конечный пользователь (в данном случае разработчики продуктов, использующих MySQL, и во внутреннею архитектуру вникающие разве что из любопытства, ну как я :).

Между «нельзя» и «сложно» большая разница. то есть дело, получается не в том, что сложно изменить API, а что для достижения цели придется переделывать как архитектуру «фреймворка» MySQL, так и архитектуру его «расширений»? Но это же сложно, а не невозможно.

И всё же, если вы в теме, много ли сторонних Storage Engine? Или речь только о тех, что входят в стандартный комплект поставки и решение все же изменить архитектуры коснется лишь разработчиков MySQL как единого продукта?

zabivator 16 мар 2013 в 16:13

Используемых — порядка двадцати (суммарно).
А как вы определяете, какие сторонние, какие нет?
Вот XtraDB — сторонний?

VolCh 16 мар 2013 в 16:32

Грубо, то что можно скачать на dev.mysql.com/downloads/mysql/ или прочитать на dev.mysql.com/doc/refman/5.6/en/storage-engines.html

vitalif 17 мар 2013 в 22:12

> Неверно. Сломанный Storage Engine API == переделка всех storage engine.

Ну во-первых, разве нельзя доработать не ломая _обратную_ совместимость, просто добавив новые фичи? Разве так сложно добавить в АПИ пару новых чисел? Ну это условно, по-хорошему надо продумать и добавить не абы какие, а действительно нужные :-))
Единственную опасность я вижу в том, что Maria и Oracle захотят делать разные доработки и в итоге мы таки получим два несовместимых мускуля, но опять-таки — если это останется на уровне исходников и не затронет SQL (а с чего бы ему затронуть?) — заметят это только собственно разработки марии и оракла…

zabivator 17 мар 2013 в 22:18

Разве что так. Только это немало работы.

Chamie 18 мар 2013 в 07:03

По сравнению с «ничего не делать» — конечно.

ZonD80 16 мар 2013 в 14:16

Может стоит просто спроектировать БД нормально и поставить нормальные индексы?

zabivator 16 мар 2013 в 14:35

А что, в «нормально» спроективрованных БД не нужны outer join'ы?
Или подзапросы не используются?

kazmiruk 17 мар 2013 в 07:00

Если важна производительность — нет. Outer join — очень расточительно. Лучше уж денормализовать, чтобы не было необходимости.

paranoik 16 мар 2013 в 15:18

— Придумать StorageEngineApiEx
— Сделать прокси реализующий StorageEngineApiEx поверх StorageEngineApi
— Переписать все на StorageEngineApiEx
— Profit

VolCh 16 мар 2013 в 16:12

Проблемы, как я понял, с «Переписать все на StorageEngineApiEx». Переписывать придется прежде всего ядро, причем если плагины не переписывать одновременно, то нужно будет два ядра иметь в коде.

CKOPOBAPKuH 16 мар 2013 в 16:35

Как человек, не имеющий никакого отношения к разработке mysql, но пытавшийся в своё время получить строчку из базы по первичному ключу с помощью storage engine api (успешно), хотел бы добавить, что этот api не отличается особенным изяществом. Своего я конечно добился сам путём чтения исходников, но.

С другой стороны, непонятно:
— добавляем расширенный интерфейс, с функциями типа get_unique_row_count или get_null_row_count
— если сторадж плагин в курсе расширенного интерфейса, он сам её реализует. если нет, то тупая дефолтная реализация была приведена: get_unique_row_count = row_count / ((int) (row_count / unique_row_count_engine))
— оптимизатор использует функции расширенного интерфейса, и с умными storage engine — профит, со старыми и тупыми — всё как и раньше.

Что мешает поступить так? (хотя я догадываюсь на самом деле. миллионы строк кода и общая тяжесть. я угадал?)

(Ещё хотел бы добавить, что если вы вдруг будете делать что-то с внутренностями mysql и попытаетесь искать помощи в mailing list, то во-первых подтверждения я ждал больше суток, а во-вторых, когда меня туда включили, мой вопрос всё равно проигнорировали. Ну то есть даже никакого «go away, you moron» не было, вообще ничего)

zabivator 16 мар 2013 в 16:48

> Что мешает поступить так? (

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

> (Ещё хотел бы добавить, что если вы вдруг будете делать что-то с внутренностями mysql и попытаетесь искать помощи в mailing list, то во-первых подтверждения я ждал больше суток, а во-вторых, когда меня туда включили, мой вопрос всё равно проигнорировали. Ну то есть даже никакого «go away, you moron» не было, вообще ничего)

Да, изучать внутренности может получится лишь двумя способами
1) Пообщаться с человеком, что занимается разработкой
2) Читать сорцы.

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

CKOPOBAPKuH 16 мар 2013 в 16:37

И ещё вопрос к автору. Не в курсе ли ты, TokuDB сделали только storage engine, или ядро они тоже исправляли?

svetasmirnova 16 мар 2013 в 18:58

Я думала, что они только storage engine сделали, однако на их сайте перечислено несколько патчей для ядра: www.tokutek.com/products/mysql-patches/ Впрочем, ни один не включён в код MySQL.

zabivator 16 мар 2013 в 16:49

> И ещё вопрос к автору. Не в курсе ли ты, TokuDB сделали только storage engine, или ядро они тоже исправляли?

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

eXTreMeHawk 16 мар 2013 в 17:38

Кто-нибудь объясните «несведующему» человеку зачем нужен MySQL, когда есть православный PostgreSQL в котором и с оптимизатором все в порядке и описанных в статье проблем отродясь не было?

CKOPOBAPKuH 16 мар 2013 в 17:56

потому что в постгресе очень долго не было нормальной репликации.
а ещё постгрес (по-моему и сейчас тоже) хуже чем mysql держит нагрузку из простых запросов вида update table… where id=? / select * from table where id=? — то есть время ответа скачет. особенно если мерить 99.5 перцентиль — 0.5% запросов супердолго работают. То есть работали (как сейчас дела, не знаю). И ещё не дай бог вакуум не вовремя запустится.

DurRandir 16 мар 2013 в 18:38

a) глобальный вакуум не советуют использовать лет пять-шесть. да, автовакуум надо настраивать, но постгри «из коробки» вообще странен (и тут его ключевое отличие от mysql — тот из коробки худо-бедно, но работает на нормальных объёмах данных)
б) «простые» апдейты, не затрагивающие индекс, заметно ускорили в 8.3, добавив heap-only tuples
в) репликации там уже больше 2х лет)

eXTreMeHawk 16 мар 2013 в 18:43

Это все было когда-то давно, сейчас PostgreSQL абсолютно взрослая и полноценная БД, лишенная указанных Вами недостатков. А еще Postgres постоянно развивается, чего нельзя сказать про MySql.

Недавно переносил проект с MySql на Postgres, — никаких значительных трудностей не встритил, все получилось очень даже мило…

VolCh 16 мар 2013 в 19:06

С Постгри до этого дело имели?

eXTreMeHawk 17 мар 2013 в 11:04

Совсем немного и очень давно… А сейчас уже один недавний проект на нем переделал и новый большой проет пишем уже сразу на PostgreSQL.

CKOPOBAPKuH 16 мар 2013 в 19:06

Ну, новые проекты на постгресе делают часто (это заметно по хантим'у). А вот то что многие существующие проекты на mysql, объяснить можно как раз детскими болезнями постгреса.
(ну, и доступностью mysql на копеечных шаред хостингах, наверное)

> Недавно переносил проект с MySql на Postgres, — никаких значительных трудностей не встритил, все получилось очень даже мило…

А большой проект?

zapimir 16 мар 2013 в 19:08

Какие проблемы MySQL решились переходом на Postgres?

eXTreMeHawk 17 мар 2013 в 11:14

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

zapimir 17 мар 2013 в 11:51

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

DmitryKoterov 16 мар 2013 в 18:01

Присоединяюсь к вопросу. В 9.2 есть и index-only scan наконец-то. Моментальные alter-ы, транзакционность изменения структуры БД. Плюс производительность на запись почти на порядок выше, чем в innodb. И почему только никак не закопают стюардессу…

VolCh 16 мар 2013 в 18:09

А не вы ли в своих книгах учили как работать с мускулом, но про постгри ничего не рассказывали? :)

DmitryKoterov 16 мар 2013 в 19:20

Это было столько лет назад… Плюс книги же эти для начинающих, а mysql тогда (да и сейчас, наверное) стоит на всех хостингах.

VolCh 16 мар 2013 в 19:34

Как-то серьезно вы восприняли. Но если так, то какие причины у тогдашнего начинающего менять привычную «дефолтную» СУБД сейчас? Пускай даже в плане кода это ему ничего почти не будет стоить — строчку для PDO поменять, а там ORM разберется.

VolCh 16 мар 2013 в 18:08

Так исторически сложилось, что в некоторых (или многих?) нишах — это стандарт де-факто уже больше десятка лет. А значит куча унаследованного кода, куча специалистов заточенных под мускул, вероятно больше и/или более развитые инструменты и, как следствие, из всего этого переход на постгри даже в новом приложении (не говоря о модификации старого) выходит дорого, без заметных преимуществ на задачах специфичных для этих ниш. А чисто субъективно мне постгри показался более сложным и в разработке, и в администрировании, когда работа с СУБД сводится в основном к CRUD и высокие нагрузки не предполагаются.

vitalif 17 мар 2013 в 22:28

Да фиг знает. Потому что не особо быстрый он, постгрес этот, а сильно серьёзных «киллерфич» как-то не наблюдается. Т.е. понятно, что всё это относительно конфигурации и способов реализации, но например на одной и той же структуре базы по относительно тупому тесту на сложных запросах из bugzilla (с правильным, переписанным, оптимизированным движком поиска) mysql 5.1 был медленнее в 2 раза, чем Pg 9.2, а mysql 5.5 уже был быстрее в 1.5 раза, чем Pg 9.2. А MariaDB была ещё чуть-чуть быстрее.

Возможностей в Pg конечно больше, одно hstore чего стоит, но не всегда они так уж нужны. Ну, тем, кто сильно любит писать логику в базе — нужны, да. Плюс этот вакуум несчастный, даже авто, всё равно задержки вносит.

И глубинные мины в оптимизаторе Pg тоже встречаются, и куда более странные, засчёт наличия большего объёма функционала. У нас, например, уже пару раз было, что один и тот же (!) запрос при выполнении с prepare (не кэшированным, а просто prepare и сразу execute) и без prepare выполнялся сильно по-разному, причём с prepare он выполнялся косо, а без — прямо. Последний раз, кстати, был как раз в пятницу.

DurRandir 18 мар 2013 в 15:07

Потому что Постгри — это не «супер-mysql», это «маленький оракл». Поэтому его надо уметь готовить. Вот прямо к вашему последнему замечанию, в 9.2 это должно было быть исправлено в стиле «просто работает», см. www.postgresql.org/docs/devel/static/release-9-2.html#AEN114087. Если именно на 9.2 с этим есть проблемы — стоит написать багрепорт/вопрос в рассылку.

DmitryKoterov 18 мар 2013 в 15:31

Не надо там уже ничего давно «уметь готовить» (если под «уметь готовить» не подразумевается настройка 4 параметров под объем памяти). Не больше, чем MySQL, по крайней мере. Даже автовакуум сделали автоматическим.

kaichou 18 мар 2013 в 08:22

Вакуум. Со всем можно примириться, кроме вакуума.

svetasmirnova 17 мар 2013 в 06:20

Обратите внимание — количество уникальных записей узнать напрямую нельзя.
MySQL считает его как количество_записей в таблице / rows_per_key

Запишем это в виде кода (как это в результате вычисляется):

int unique_row_count_mysql = row_count / rows_per_key;
int rows_per_key = row_count / unique_row_count_engine;
int unique_row_count_mysql = row_count / ((int) (row_count / unique_row_count_engine));

Поясняю:

a2 = (int)b / ((int) (b / a));

Я не поняла каким образом storage engine API не даёт сделать

a2 = (int)( (double)b / ( (double)b /(double) a) ) );

Если это в самом деле так, откройте bug report, пожалуйста.

zabivator 17 мар 2013 в 11:45

> Я не поняла каким образом storage engine API не даёт сделать

Нету такой функции в API, чтобы unique cardinality узнать

> Если это в самом деле так, откройте bug report, пожалуйста.

Файл storage/innobase/handler/ha_innodb.c

innodb_rec_per_key(
...
{
	ha_rows		rec_per_key;

....
			rec_per_key = (ha_rows)(
				(records - num_null)
				/ (index->stat_n_diff_key_vals[i + 1]
				   - num_null));
		}
	} else {
		rec_per_key = (ha_rows)
			 (records / index->stat_n_diff_key_vals[i + 1]);
	}

	return(rec_per_key);
}

./include/my_base.h:typedef ulong		ha_rows;

svetasmirnova 17 мар 2013 в 13:13

> > Я не поняла каким образом storage engine API не даёт сделать
>
> Нету такой функции в API, чтобы unique cardinality узнать

Да какая разница — есть или нету? Я говорю о приведении к типу double, с тем, чтобы округлить конечный результат. (Например, для выражений типа 100/(100/40) этого будет достаточно)

> > Если это в самом деле так, откройте bug report, пожалуйста.
>
> Файл storage/innobase/handler/ha_innodb.c

Запостите баг репорт =)

zabivator 17 мар 2013 в 13:56

bugs.mysql.com/bug.php?id=68697

svetasmirnova 17 мар 2013 в 14:21

Спасибо!

VolCh 17 мар 2013 в 20:37

a2 = (int)( (double)b / ( (double)b /(double) a) ) );

А не лучше (int) ( (long)b * a ) / b); (приведение к long чтобы избежать переполнения при умножении, исхожу, что в начале a и b int).

svetasmirnova 17 мар 2013 в 22:31

Нет, там дело в том, что b /a независимо от числителя вычисляется, а при привидении к любым целым мы теряем точность при округлении.

Daedmen 17 мар 2013 в 15:26

Так почему всетаки нельзя расширять апи?

eexo 18 мар 2013 в 08:17

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

Зарегистрируйтесь на Хабре, чтобы оставить комментарий