Комментарии 81
— Фронтэнд делает 500 async запросов выполняющихся на бд 0.5-2с каждый.
— Один запрос с портянкой на 500 параметров, которая будет работать через WHILE (или JOIN) в одой процедуре за 40 секунд?
И да, в любой момент все 100 подключений подключены.
Почему нельзя растягивать пул?
Старайтесь отправлять запросы к СУБД пачками, а не единичными
А как?
Подписывайте в строке подключения к БД компонент приложения в Application Name:
Чем лучше? Почему?
Запросы пачками-имеется в виду, не по одной строчке, а сразу одним запросом.
Например, удаляете 34 строки-удаляйте не тупо в цикле вызовом хранимки по 1 строке, а сразу 34. А еще лучше просто пометить на удаление сразу 34 строки одним запросом. А потом или ночью уже физически удалить
А чем пометить и ночью удалить лучше?
При обновлении (пометить) и построчном хранении насколько пос все старые строчки(условно, там ведь страницы) будут удалены, новые созданы + логи, при последующем удалении "ночью" будет повторное удаление строчек.
Или в mssql что-то сильно по другому?
Лучше физическое удаление делать в минимальные нагрузочные часы
Так в том то и дело, что физически, при обновлении, старые записи(блоки) будут помечены как удалённые, а обновлённые будут записаны. Т.е. по факту в индексе логический адрес ячейки не сменится а физический будет заменён. Т.е. отложенное удаление в сумме приведёт к большему количеству операций ввода-вывода и потенциально большей фрагментации данных в файлах БД (износу ssd-ников в т.ч.) при этом ещё потребует изменения логики работы приложения (фильтровать придётся ещё и удалённые записи, возможно добавлять индексы в случае наличия большого количества удалений).
Для oltp ещё возникает проблема с unique key т.к. сценарий удалили запись — вставили такую-же удалили ещё раз приводит к нарушению уникальности даже в случае включения в constraint признака удаления.
В общем на мой взгляд выигрыш сомнителен, особенно если БД из Oracle, PG, firebird. Возможно с
MSSQL дела обстоят по другому всёже корни блокировочника могут давать особенности, не знаю.
Довольно часто пользователи, разработчики, администраторы и т д СУБД MS SQL Server встречаются с проблемами производительности БД или СУБД в целом.Пусть переходят на PostgreSQL и перестают жаловаться! Я у себя отмечал прирост скорости в 20 и более раз.
Однако, сама СУБД не решит все проблемы и не является унифицированным средством и подходом для решения всего на свете
То, что какая-то СУБД-это наше все-большое и распространенное заблуждение.
Каждая СУБД имеет и свои плюсы и свои минусы
Также важно еще сопоставлять с бюджетом, поддержкой и т д и т п
Бесплатно, тоже не всегда хорошо, т к порой поддержка будет значительно дороже платного аналога и более дешевой поддержки.
А уж тонкостей по настройке, оптимизации и эксплуатации везде хватает.
Нужно смотреть в комплексе, а не в вакууме
А вообще попробуй сначала посгрес с его возможностями и функциями, которых на порядки больше, чем в мс, а потом поговорим. Ты и смотри в комплексе.
Грубо как.
А по факту:
1) В класе версионник он отстал от оракла навсегда.
2) Партиции которие дают реальний прирост и маштабируемость появилось буквально в 10 версии и назвать что ето уже продакшин — как-то язик не поварачивается. Про in-memory в посгри я тоже ничего не слишал. (Оракл 12, MSSQL 2014). Пускай реализация местами странная и ограничений вагон и тележка но оно работает и дает реальний прирост.
Если правильно настроить базу и експлуатировать ее как она «любит» то на одном и том же железе разница может бить в процентах а не в разы.
Я тоже читаю про постгрес и как СУБД считаю её не плохой
Но повторюсь-все нужно смотреть в комплексе
Там где в MS SQL приходилось писать простыни хранимок и писать велосипеды для распарсивания чего-то, или потом пришлось даже на CLR C# хранимки перейти, в PostgeSQL есть всё из коробки в SQL.
Может не смогли настроить MSSQL? 20 выглядит фантастикой, давайте репо.
---с его возможностями и функциями
Давайте где там Graph DBMS в Постгрессе?
Go, JavaScript (Node.js), R, Ruby не поддерживаются.
Transact SQL and .NET languages в Server-side scripts тоже не поддерживается.
In-memory capabilities нет.
Может не смогли настроить MSSQL? 20 выглядит фантастикой, давайте репо.А может вы PostgreSQL не пользовались?! Настраивать я MS SQL умею, мы базу и запросы ускоряли на порядки.
А учтивая что R не поддeрживается о каком BI вообще можно говорить?
Разница в запросах + их оптимизация, не говоря уже о разнице pl/sql t-sql и pg/sql настолько огромная что ето фактически наново писать всю логику. То есть ето в принципе невозможно. А переход с версионника на блокировщик или наоборот ето еще то приключение, вплоть то переписивания логики приложения.
А то о чем вы говорите — ето способ писать приложения только используя SQL, а всю логику вне БД. При етом вы используете только незначительную часть возможности БД.
P.S. Была попотка перенести приложение c оракла на IBM DB2 которий декларировал совместимость pl/sql на 99%. Так от все вкусняшки по быстродействию оракла в pl/sql DB2 там не реализованы. Хотя я уверен есть свои. Оценив неоходимое время — мы отказались.
Раз уж вы стесняетесь и читаете себя спецом в обоих серверах. Переведи это репо на новейший стандарт постгресса и скажите за сколько процедура отработает.
CREATE DATABASE MSSQLtst ON PRIMARY(NAME = InMemoryData, FILENAME = 'c:\db\MSSQLtst.mdf', size=200MB),
FILEGROUP [MSSQLtst_FG] CONTAINS MEMORY_OPTIMIZED_DATA( NAME = [MSSQLtst_dir], FILENAME = 'c:\db\MSSQLtst')
LOG ON (name = [MSSQLtst_log], Filename='c:\db\MSSQLtst.ldf', size=100MB)
GO
USE MSSQLtst;
GO
CREATE TABLE MSSQLtbl (ID INT NOT NULL, Name VARCHAR(100) NOT NULL
CONSTRAINT ID_MSSQLtbl_Mem PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT=1000000))
WITH (MEMORY_OPTIMIZED=ON)
GO
CREATE PROCEDURE proc_MSSQLtst
WITH NATIVE_COMPILATION, SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='english')
DECLARE @counter AS INT = 1
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter <= 100000)
BEGIN
INSERT INTO dbo.MSSQLtbl VALUES(@counter, 'SQLAuthority')
SET @counter = @counter + 1
END
SELECT DATEDIFF(SECOND, @start, GETDATE() ) [MSSQLtst in sec] END
GO
declare @start datetime2= getdate()
EXEC proc_MSSQLtst
select datediff(MILLISECOND, @start, getdate() )
GO
Попробуй один проект на постгресе начать — будешь недоумевать как на МС мог работать. Всё грамотно сделано.
В статье дана общая концепция и примеры на скуле и .NET
Перейдя на постгрес все равно придётся делать теже концепции, но другим кодом
И если ПО настроено неправильно или написано мягко сказать вычурно, то СУБД не во всех аспектах спасет
На MSSQL мой пример отрабатывает за 83 ms, на Постгресс это будет работать в лучшем случае секунд 10-15.
Это я еще не рассматривают код где MSSQL может распаралелить запрос, а Постгресс нет.
И подходы для них в корне различны.
Лобовое сравнение одинакового кода для них — пригодно только для простейших тестовых ситуаций.
Для сложных — нужно отдельно оптимизировать под конкретный сервер.
Более того я даже указал в моем примере уровень изоляции.
---пригодно только для простейших тестовых ситуаций.
Я привел концептуальную разницу, где постгресс серьезно проигрывает даже на простейших текстах. И мне этот пример в BI задачах принципиально нужен.
Второй пример приведенный ниже тоже показывает серьезное концептуальное отставание в производительности. На дворе 21 век, а до сих Постгресс не может распаралеливать запросы.
2 экземпляра кода для MSSQL вам уже приведены. Вы эксперт в Постгрессе — приведите аналогичный, показывающий что я неправ.
Моя оценка основана понимании того что будет делать не-MSSQL сервер. Мой код простой — вставка ста тысяч значений в таблицу внутри процедуры.
Это я еще не рассматривают код где MSSQL может распаралелить запрос, а Постгресс нет.Лишний раз убеждаюсь, что Вы постгреса не знаете от слова совсем. Я работал на обеих базах: 5+ лет на МС и 7+ на постгресе, на двух проектах: BI и ETL — в обоих проектах при переходе на постгрес скорость вырастала на порядок.
Могу допустить, что за последнее время особенно портировав МС на Линукс, могли произойти позитивные изменения — надо смотреть, но не таки кардинальные, как на постгресе.
Лишний раз убеждаюсь, что Вы постгреса не знаете от слова совсем. Я работал на обеих базах: 5+ лет на МС и 7+ на постгресе, на двух проектах: BI и ETL — в обоих проектах при переходе на постгрес скорость вырастала на порядок.
А мужики то оказывается не в курсе…
Позвольте, а где же здесь Постгресс??
www.tpc.org/tpch/results/tpch_advanced_sort.asp?PRINTVER=false&FLTCOL1=ALL&ADDFILTERROW=&filterRowCount=1&SRTCOL1=h_sponsor&SRTDIR1=ASC&ADDSORTROW=&sortRowCount=1&DISPRES=100+PERCENT&include_withdrawn_results=none&include_historic_results=no
Наверное мешки таскать — не языком чесать?
А табличка, ну это самый известный в мире database benchmarks. Подробней можно почитать здесь:
www.tpc.org/information/about/abouttpc.asp
И что такое TOP-500?
Any company may join the TPC. A membership costs $15,000 per year.А постгрес не завязан на конкретную фирму.
TOP-500 — это линуксоиды знают: www.linux.org.ru/news/clusters/13823822
Но это всё таки не так интересно. Интересен свой опыт. А я пробовал на однотипных задачах обе СУБД. Был резкий контраст когда даже на старом железе запросы в постгре летали мсек, а при переходе на МС на порядок мощном железе аналогичный запрос тянул на секунды или даже десятки секунд — это было сильно заметно.
У людей вот тоже в одиночных тестах производительность отличается на порядок: www.enterprisedb.com/blog/postgresql-vs-microsoft-sql — надо всё пробовать самому!
По функциям — вот отличный обзор: www.pg-versus-ms.com
и вот: dba.stackexchange.com/a/77015/15730
По настройкам: МС был оптимизирован максимально: настройки, версионность, изоляция, индексы, запросы и т.п. Постгрес — дефолтный, данные не кешированные и БЕЗ ИНДЕКСОВ, повторюсь.
При переходе на Линукс из-за гораздо более грамотного проектирования системы, чем у МС, на том же железе всегда отмечал увеличение производительности как минимум в 2р: сети, ФС, IO, скорости скомпилированной программы и т.п. Тоже самое отметил и в случае с БД.
Не исключаю, что в последнее время они могли значительно увеличить производительность SQL Server-а, .Net Core и т.п. Какие-то серьёзные изменения у них вроде были.
Если в будущем будет подобный проект — отпишусь.
Когда планируете опубликовать результаты?
Да, действительно
Но ладно нам-читателям, для своего же интереса или Вы позволите самообман? В итоге конечно решать Вам. Но либо факты с учетом текущих версий, а не 5-10 и более лет назад, или ничего. Предполагать все могут, а уж генераторов всяких идей на каждом шагу. Это малоценная информация (я бы даже сказал бесполезная или устаревшая).
Но BI-решения всегда были дорогими
Постгресс одна из самых лучших бесплатных СУБД-в этом нет сомнений, но если позволяет бюджет, то лучше рассмотреть для BI-вертику или скуль, если OLTP-скуль
Видел решение по финансовой бирже-более 100 ТБ БД на постгресе… скажу вам плачевно все-на поддержку тратятся суммы в разы больше, чем если бы купили вертику и 2-х спецов, а не целый штат
что бы не быть голословным можешь выкачать этот набор данных:
northcentr.blob.core.windows.net/tpcc/order_line.txt
(8,6GB)
Это CSV с разделителем tab
Загрузить в PostgreSQL
Прооптимизированный, куда же без этого :)
и выполнить запрос:
SELECT row_number() over (order by ol_number) rnk,
ol_number
,sum(ol_quantity) AS sum_qty
,sum(ol_amount) AS sum_amount
,avg(ol_quantity) AS avg_qty
,avg(ol_amount) AS avg_amount
,count(*) AS count_order
FROM order_line
GROUP BY ol_number
ORDER BY ol_number;
Жду время выполнения этого запроса.
Последняя версия, на которой я это тестировал была
PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
И результат был мягко говоря не радостный.
Как по мне — типичная задача BI на не самых больших данных
Но я ожидал такой ответ :)
У меня на гораздо более сложном многоэтажном запросе с несколькими JOIN и агрегатами и базе в несколько гиг постгрес был в 20 раз быстрее на базе БЕЗ ИНДЕКСОВ, чем МС с индексами, включая кластерный, с индексированный VIEW WITH SCHEMABINDING, который как ты знаешь в МС работает гораздо быстрее обычного JOIN-а таблиц и т.п.
К сожалению, с Join'ами на больших таблицах все не так радужно… И мегаспецы с sql.ru так же бессильны. Это лично мой пример, и моя попытка получить помощь
www.sql.ru/forum/1199125/has-join
Спасут ли индексы? Если да, то какие подошли оптимальное всего?
Или какое было ещё решение?
В итоге после прогонов разных тестов:
в общем получили почти ничью только из-за того, что файловая система лучше на UNIX-системах, чем в Windows, потому постгре и показывает хуже результаты в ОС Windows, чем скуль.
Вообще интересно было бы протестить скуль 2019 (как выйдет) на линуксе и постгре на линуксе, чтобы понять на скоько сильно и как отличаются в производительности две эти СУБД при одинаковой ОС.
Также такие статьи и им подобные не соответствуют действительности по тесту Гилева (возможно были проплачены, либо ради высокого рейтинга для пиара):
infostart.ru/public/962876
Так что не нужно вешать лапшу на уши-скорее всего Вы просто неправильно оптимизировали скуль или использовали старые его версии или редакцию урезанную
постгре дал сначала результат в среднем 8,8 при оптимизации для 1С, после всех оптимизаций (ядро CentOS, файловой системы и т д) в том числе и по следующему калькулятору подгоняли: pgconfigurator.cybertec.at
получили, что макс на постгре тест Гилева показал результат в 9,96, тогда как даже не в оптимизированном скуле (оптимизация была поверхностная-как указано в 1С) показатель был всегда выше 10, а именно 10,4-это среднее значение.
Итог: 3 дня подбирали оптимальные параметры для постгре, оптимизированного под 1С со всеми патчами, а скуль лишь немного настроили как рекомендует 1С. И в итоге-постгрес продул в производительности скулю, несмотря на то, что файловая система в линуксе лучше файловой системы в винде да еще и оптимизирована была. Вывод: СУБД постгре явно проигрывает в производительности СУБД MS SQL Server и только за счет ОС и файловой системы может приближаться к производительности скуля на винде. Но выход скуля 2019, который будет нормально работать в том числе и на линуксе все изменит
Вот читаю я эту статью и вижу, что написано человеком знающим и опытным — без иронии, действительно знающим и опытным. Но. Но ваши рекомендации опоздали лет на 8-10 — не для какого-то конкретного читателя опоздали, а в принципе опоздали. И по объёмам данных опоздали, и по конкретным приёмам опоздали.
Мне уже надоело переучивать DBA и разработчиков, начитавшихся "советов в энторнетах", подобных данной статье. Проблема не в советах, конечно же а в том, что их применяют не включая мозг. Эти статьи смешиваются, превращаются в опасные мифы и заблуждения.
Вот просто для иллюстрации, совет "сначала выберите удаляемые/изменяемые данные во временную таблицу". Совет для некоторых конкретных случаев весьма дельный. Но на самом деле часто это очень вредный совет:
- Не сильно заботясь воткнули stop-and-go.
- Потеребили tempdb
- Всё сложили в таблицу без индексов (кстати и в вашем примере), а если таких таблиц 10-20, то что потом в соединении?
- А если и создали, то воспроизводимость планов с индексами и таблицами в tempdb и их диагностика мягко говоря слабее, чем с продуманными постоянными
- И при создании/удалении временных таблиц можно легко нарваться на узкое место в виде перекомпиляции запросов.
Не бывает универсальных (или "вечных") рекомендаций. Рекомендации нужно применять либо как архитектурные принципы ("знаем, что есть исключения, но делаем так пока нет явных причин так не делать"), либо только после диагностики. А вот про диагностику у вас вообще почти ничего нет. Я не про ту диагностику где "индекс давно не используется", а про "что является узким местом данной системы и чем это обусловлено". Ну хоть бы sys.dm_os_wait_stats
упомянули. А в идеале, чтобы велосипеды не изобретать, можно и тулзы Брента Озара упомянуть. Кстати, на его основном сайте очень много интересной, полезной и более актуальной информации о производительности MS SQL
И еще сильно напрягли рекомендации в стиле "делайте всё правильно", например:
- "Правильно задавайте уровень изоляции при работе с БД" — ахаха, 80% корпоративных БД на nolock до сих пор сидят, а из разработчиков, хорошо, если 20% могут осмысленно уровни изоляции обсуждать
- "используйте разумно хинты к запросам" — тоже прекрасно. Степени разумности, которые я видел — от "нельзя ничего" до "указать индекс для каждой таблицы".
А во-вторых, здесь даны примеры. И как и любой пример он имеет свои преимущества и недостатки
И в-третьих, грубо, но для общего понимания вполне сгодится-всегда используется tempdb и если проблема с выделением временной таблицы, то либо вы обрабатываете слишком много данных за раз для вашей системы (тогда делайте порциями обработку), либо проблема не в запросе, а в tempdb-нехватка памяти, медленный ввод-вывод и т д
И итог-напишите свою статью, исходя из того опыта и знаний, что накопил и поделитесь с читателями
Вставка же небольшой порции данных во временную таблицу, позволяет минимизировать блокировки. Для систем, где есть проблема с tempdb разницы не будет, т к либо ожидание будет на блокировку у других запросов в том числе при не использовании временной таблицы, либо задержка будет в вводе-выводе. Но система сама сделает промежуточную таблицу при определённых условиях, а система, где с tempdb все нормально-данный запрос позволит другим не ждать окончания блокировки
И Вы правы-все нужно применять с головой
Три аспекта оптимизации (БД и ПО)