#sql #databases
SQL Tuning by Dan Tow (O’Reilly)
Базы данных стараются кэшировать часто используемые куски данных (единообразного размера, по 2—16 Кб), копируя их с диска к себе в буфер. Буфер доступен всем сессиям одновременно, он общий на всех.
❓Ещё бы понимать хорошенечко, как устроены сессии — это туннель соединения юзера (приложения) с БД, в котором гоняются транзакции? А что это за протокол на уровне БД, это ведь не всегда TCP? Короче, хз, не до конца понимаю, чё там.
Буфер, как и у многих других штук, устроен по принципу LRU cache (от Least Recently Used): удаляет объекты, которые дольше всего не использовались, за ними снова придётся идти на диск. В целом, операции чтения с диска дороже, поэтому хорошо, если данные кэшируются удачно.
Маленькие таблицы (меньше 10к записей) часто кэшируются полностью, если они нужны, а если не очень, то даже это неважно, потому что достаточно нескольких физических запросов к диску, чтобы быстро всё поместить в логический кэш, да и вообще, кажется, с маленькими таблицами БД умеют работать на ура. Физический I/O обычно дополнительно оптимизируется операционной системой.
У таблиц бывает разный физический layout, в зависимости от того, как часто их данные архивируются/purge’атся. Кажется, большинство таблиц в мире работают по принципу перманентного роста: никто ничего из них не убирает, так как боятся потерять что-то нужное, и таблицы тупо растут. При этом новые строки чаще всего нужны чаще, чем старые, и они чаще попадают в кэш. А так как они записываются поступательно, то склонны быть физически сгруппированы на диске, что хорошо и удобно для кэша. Труднее всего, когда purge делается не по принципу возраста данных, тогда очищаемые строки освобождают случайные куски в памяти, и нужные (“горячие”) строки оказываются понатыканы в разных местах — при обращении к диску их можно долго собирать-читать.
High-water mark (отметка, до которой место принадлежит таблице) дропается при TRUNCATE (про Postgres это надо бы проверить, но наверняка это тоже так). DELETE, видимо, освобождает место стертых записей под новые записи, но, естественно, не может вот так просто взять и понизить high-water mark.
SQL Tuning by Dan Tow (O’Reilly)
Базы данных стараются кэшировать часто используемые куски данных (единообразного размера, по 2—16 Кб), копируя их с диска к себе в буфер. Буфер доступен всем сессиям одновременно, он общий на всех.
❓Ещё бы понимать хорошенечко, как устроены сессии — это туннель соединения юзера (приложения) с БД, в котором гоняются транзакции? А что это за протокол на уровне БД, это ведь не всегда TCP? Короче, хз, не до конца понимаю, чё там.
Буфер, как и у многих других штук, устроен по принципу LRU cache (от Least Recently Used): удаляет объекты, которые дольше всего не использовались, за ними снова придётся идти на диск. В целом, операции чтения с диска дороже, поэтому хорошо, если данные кэшируются удачно.
Маленькие таблицы (меньше 10к записей) часто кэшируются полностью, если они нужны, а если не очень, то даже это неважно, потому что достаточно нескольких физических запросов к диску, чтобы быстро всё поместить в логический кэш, да и вообще, кажется, с маленькими таблицами БД умеют работать на ура. Физический I/O обычно дополнительно оптимизируется операционной системой.
У таблиц бывает разный физический layout, в зависимости от того, как часто их данные архивируются/purge’атся. Кажется, большинство таблиц в мире работают по принципу перманентного роста: никто ничего из них не убирает, так как боятся потерять что-то нужное, и таблицы тупо растут. При этом новые строки чаще всего нужны чаще, чем старые, и они чаще попадают в кэш. А так как они записываются поступательно, то склонны быть физически сгруппированы на диске, что хорошо и удобно для кэша. Труднее всего, когда purge делается не по принципу возраста данных, тогда очищаемые строки освобождают случайные куски в памяти, и нужные (“горячие”) строки оказываются понатыканы в разных местах — при обращении к диску их можно долго собирать-читать.
High-water mark (отметка, до которой место принадлежит таблице) дропается при TRUNCATE (про Postgres это надо бы проверить, но наверняка это тоже так). DELETE, видимо, освобождает место стертых записей под новые записи, но, естественно, не может вот так просто взять и понизить high-water mark.
#sql #databases #индексы #indexes
🗄🧶🗃
Дальше Дэн пересказывает что-то похожее на документацию Оракла про индексы.
1) Индексы могут покрывать не все строки таблицы, — например, если индексируемое поле может содержать NULL. Поэтому понятие строки таблицы и индексированной строки не взаимозаменяемы.
2) Чаще всего индекс — это b-tree индекс (дерево индексакции сбалансированной глубины), примерно по триста промежутков на один уровень. То есть корень индекса содержит указатели на начало и конец трёхсот промежутков отсортированных индексируемых значений, каждый промежуток своего уровня в свою очередь покрывает триста промежутков внутри себя, и так далее, пока не достигается уровень указателей на конкретные индексируемые блоки памяти со значениями.
3) В данном случае то, что мы находим, двигаясь по уровням индекса, будет парой адреса блока в памяти и номера строки. В оракле (?) эта пара называется называется rowid. Индексы используют именно rowid для того, чтобы указывать на строки индексируемой таблицы.
4) Если условие подразумевает, что может понадобиться вернуть не одну, а несколько строк из листьев, то БД делает index range scan по блокам памяти найденных листьев и их значениям. Листья, кажется, содержат уже и значения индексируемых строк, а не только rowid, и эти значения уже отсортированы, собственно, по индексу. Так что сначала БД делает обход по дереву, находит нужный лист, дальше делает обход по значениям листа.
5) Индексы чаще всего покрывают самые часто используемые части таблиц, так что обычно у них хороший cache-hit ratio (соотношение частоты использования логического буфера к количеству нежелательных обращений к диску). Так что обычно большую часть стоимости исполнения запроса организует физическое чтение данных именно из самих таблиц. (Как вообще переводят cost?)
6) При этом просто так навешивать индексы туда-сюда не стоит, так как это может сильно повысить стоимость операций UPDATE/INSERT/DELETE. Самая дорогая операция для индексов — это UPDATE, так как в индексе надо сначала удалить старое значение, затем добавить новое, то это для индекса это целых цве операции. Удаления тоже могут снизить эффективность индексов.
7) Есть всякие более экзотические штуки, которые используются в конкретных применениях, когда условия подобрались именно для них. Партиционированные таблицы, кластера и т.д. Битмапные индексы, например, хороши именно для АХД, когда данные в основном используются для чтения, а потом ночью обновляются.
🗄🧶🗃
Дальше Дэн пересказывает что-то похожее на документацию Оракла про индексы.
1) Индексы могут покрывать не все строки таблицы, — например, если индексируемое поле может содержать NULL. Поэтому понятие строки таблицы и индексированной строки не взаимозаменяемы.
2) Чаще всего индекс — это b-tree индекс (дерево индексакции сбалансированной глубины), примерно по триста промежутков на один уровень. То есть корень индекса содержит указатели на начало и конец трёхсот промежутков отсортированных индексируемых значений, каждый промежуток своего уровня в свою очередь покрывает триста промежутков внутри себя, и так далее, пока не достигается уровень указателей на конкретные индексируемые блоки памяти со значениями.
3) В данном случае то, что мы находим, двигаясь по уровням индекса, будет парой адреса блока в памяти и номера строки. В оракле (?) эта пара называется называется rowid. Индексы используют именно rowid для того, чтобы указывать на строки индексируемой таблицы.
4) Если условие подразумевает, что может понадобиться вернуть не одну, а несколько строк из листьев, то БД делает index range scan по блокам памяти найденных листьев и их значениям. Листья, кажется, содержат уже и значения индексируемых строк, а не только rowid, и эти значения уже отсортированы, собственно, по индексу. Так что сначала БД делает обход по дереву, находит нужный лист, дальше делает обход по значениям листа.
5) Индексы чаще всего покрывают самые часто используемые части таблиц, так что обычно у них хороший cache-hit ratio (соотношение частоты использования логического буфера к количеству нежелательных обращений к диску). Так что обычно большую часть стоимости исполнения запроса организует физическое чтение данных именно из самих таблиц. (Как вообще переводят cost?)
6) При этом просто так навешивать индексы туда-сюда не стоит, так как это может сильно повысить стоимость операций UPDATE/INSERT/DELETE. Самая дорогая операция для индексов — это UPDATE, так как в индексе надо сначала удалить старое значение, затем добавить новое, то это для индекса это целых цве операции. Удаления тоже могут снизить эффективность индексов.
7) Есть всякие более экзотические штуки, которые используются в конкретных применениях, когда условия подобрались именно для них. Партиционированные таблицы, кластера и т.д. Битмапные индексы, например, хороши именно для АХД, когда данные в основном используются для чтения, а потом ночью обновляются.
#sql #databases
🌹⚾️🚏
SQL Tuning by Dan Tow (O’Reilly)
• Чаще всего СУБД сама догадается использовать индекс там, где он есть и где это удобно. Но при этом иногда она делает неверный выбор, об этом будет дальше.
• Значения границ промежутков индекса чаще всего уже закэшированы.
• Сами проиндексированные значения тоже чаще закэшированы, чем остальные.
• Index scans анализируют небольшую часть данных из блока памяти — только те строки, которые нужны, а не вообще все строки в блоке памяти. Это сокращает вычислительные затраты процессора.
• Запросы, которые используют чтение по индексу (index scans), лучше масштабируются при росте таблицы — по понятной причине.
• Неважно, насколько огромна таблица, если на ней есть индекс, то можно по умолчанию предполагать, что он будет закэширован.
• При Full table scan СУБД будут запрашивать данные с диска большими кусками, а не блоками, как при индексе, что тоже логично, так как СУБД уже знает, что ей все равно надо будет вытащить всё.
• Итоговая стоимость полного скана таблицы — в процессорной вычислительной нагрузке, так как проверяться на соответствие условиям будут все записи.
• Но при этом если вам нужно получить большой кусок большой таблицы (больше 20%), то full table scan вполне имеет смысл.
• В остальных случаях лучше стараться использовать индекс.
• Если вдруг вам нужно так много данных, это часто может оказаться неверным с точки зрения бизнес-логики, если это пользовательское приложение. В таких случаях часто стоит вернуться к осознанию бизнес-логики и постараться использовать индексы и меньше данных.
• Оптимизатор постарается сам принять верное решение о том, какой метод поиска использовать, но он не всегда угадывает верно.
📒 Дальше Дэн круто описывает, как лично он принимает решение о том, какой scan использовать:
Он просто профилирует время исполнения обоих вариантов, при этом делая паузу между ними, чтобы не было ситуации, что прогон первого варианта помещает данные в горячий кэш, за счет чего второй эксперимент может пройти гораздо быстрее. Обычно Дэн прогоняет каждый метод скана дважды и сравнивает результаты вторых прогонов каждого. Если результаты почти одинаковые, он повторяет эксперименты, выжидая не меньше десяти минут между ними.
При этом, если разница действительно невелика, то управлять execution plan'ом запроса довольно бессмысленно.
🌹⚾️🚏
SQL Tuning by Dan Tow (O’Reilly)
• Чаще всего СУБД сама догадается использовать индекс там, где он есть и где это удобно. Но при этом иногда она делает неверный выбор, об этом будет дальше.
• Значения границ промежутков индекса чаще всего уже закэшированы.
• Сами проиндексированные значения тоже чаще закэшированы, чем остальные.
• Index scans анализируют небольшую часть данных из блока памяти — только те строки, которые нужны, а не вообще все строки в блоке памяти. Это сокращает вычислительные затраты процессора.
• Запросы, которые используют чтение по индексу (index scans), лучше масштабируются при росте таблицы — по понятной причине.
• Неважно, насколько огромна таблица, если на ней есть индекс, то можно по умолчанию предполагать, что он будет закэширован.
• При Full table scan СУБД будут запрашивать данные с диска большими кусками, а не блоками, как при индексе, что тоже логично, так как СУБД уже знает, что ей все равно надо будет вытащить всё.
• Итоговая стоимость полного скана таблицы — в процессорной вычислительной нагрузке, так как проверяться на соответствие условиям будут все записи.
• Но при этом если вам нужно получить большой кусок большой таблицы (больше 20%), то full table scan вполне имеет смысл.
• В остальных случаях лучше стараться использовать индекс.
• Если вдруг вам нужно так много данных, это часто может оказаться неверным с точки зрения бизнес-логики, если это пользовательское приложение. В таких случаях часто стоит вернуться к осознанию бизнес-логики и постараться использовать индексы и меньше данных.
• Оптимизатор постарается сам принять верное решение о том, какой метод поиска использовать, но он не всегда угадывает верно.
📒 Дальше Дэн круто описывает, как лично он принимает решение о том, какой scan использовать:
Он просто профилирует время исполнения обоих вариантов, при этом делая паузу между ними, чтобы не было ситуации, что прогон первого варианта помещает данные в горячий кэш, за счет чего второй эксперимент может пройти гораздо быстрее. Обычно Дэн прогоняет каждый метод скана дважды и сравнивает результаты вторых прогонов каждого. Если результаты почти одинаковые, он повторяет эксперименты, выжидая не меньше десяти минут между ними.
При этом, если разница действительно невелика, то управлять execution plan'ом запроса довольно бессмысленно.
⏹◀️⬅️⬅️⬅️⬅️⬅️↔️➡️➡️➡️➡️➡️▶️
#крик_утки
Боже, как медленно я читаю книгу про оптимизацию запросов; боже, пусть резиновая утка моего канала станет счастливее.
🔽⏬🔽⏬🔽⏬⏹⏹⏬🔽⏬🔽⏬🔽
#крик_утки
Боже, как медленно я читаю книгу про оптимизацию запросов; боже, пусть резиновая утка моего канала станет счастливее.
🔽⏬🔽⏬🔽⏬⏹⏹⏬🔽⏬🔽⏬🔽
🌚
Короче. В разных источниках парни называют селективностью то общее количество строк, которое вернётся, держа в уме общее количество строк в таблице вообще. Тогда единица — это прекрасная селективность. И двойка тоже.
Другие же авторы, например, Дэн, сразу используют количество строк в таблице как знаменатель, и тогда хорошая селективность стремится к нулю.
🍘
Короче. В разных источниках парни называют селективностью то общее количество строк, которое вернётся, держа в уме общее количество строк в таблице вообще. Тогда единица — это прекрасная селективность. И двойка тоже.
Другие же авторы, например, Дэн, сразу используют количество строк в таблице как знаменатель, и тогда хорошая селективность стремится к нулю.
🍘
Это был показ плёночной версии фильма Метрополис на закрытой станции метро «Деловой Центр» Солнцевской линии с живой музыкой от Саши Елиной и Ивана Бушуева.
#moire_experience
#moire_experience