SQL *

UPD: Продолжение статьи по ссылке — habrahabr.ru/company/avito/blog/322510

О чем статья

Незаметно пролетел год, как начались работы по разработке и внедрению хранилища данных на платформе Вертика.
На хабре уже есть статьи про саму СУБД Вертика, особенно рекомендую эту: HP Vertica, первый запущенный проект в РФ, ведь ее автор очень помог нам на начальном этапе. Алексей, спасибо еще раз.
Хотелось бы рассказать о том, какая методология применялась для проектирования физической структуры хранилища, чтобы наиболее полно использовать возможности HP Vertica.
Эту статью хотел бы посветить обоснованию оптимальности выбранной методологии, а в следующей — рассказать о том, какие техники позволяют анализировать данные, содержащие десятки млрд.

Постановка задачи

Рассмотрим высоконагруженный сайт крупной российской интернет-компании (теперь можно — это Авито ;)).
Деятельность компании описывается следующими цифрами: ~ 10 млн. активных пользователей, ~100 млн. просмотров страниц в день, около 1 тыс. новых объектов, размещенных пользователями на сайте в течение 1 минуты, ~10 тыс. поисковых запросов пользователей в минуту.
Грубая оценка количества действий, подлежащих сохранению в хранилище, составляет 100 млн. новых записей в сутки (~100 GB новых данных в сутки).
Т.е. при построении классического хранилища данных с отказом от стирания поступивших ранее данных, объем хранилища через 3 месяца эксплуатации составит 10TB сырых данных. Big Data как она есть.
Нужно построить хранилище, которое хранило бы не меньше 6 месяцев данных, позволяло их анализировать, визуализировать, и отставало бы от реальной жизни настолько мало, насколько это возможно (в худшем случае — отставало бы на день, в лучшем — на минуты).
Вынося сразу за скобки вопрос выбора платформы — хранилище должно работать на HP Vertica, MPP базе колоночного хранения, см. вводную статью в заголовке.

Во время посещения PyConRu 2014 я, с удивлением, узнал, что достаточно большая аудитория python-разработчиков не использует SQLAlchemy в качестве основного инструмента для работы с базой данных. Порассуждав на данную тему после Light Talks с коллегами было принято решение — во чтобы то ни стало написать статью о том, что же можно делать со всей мощью SQLAlchemy.

Обычно в написании сайтов не требуется чего-нибудь этакого от штатного ORM. А если и требуется, то хватает замены на нештатный или прочтения основной части документации. И, как правило, голову ломать над сложными запросами не приходится. Достаточно много различных ORM предлагают классические схемы One-2-Many, One-2-One, Many-2-Many, и т.д. Для обычных запросов и связей этого вполне достаточно. К сожалению, в больших проектах не обходится без частных случаев и программисты при сложных запросах пишут либо raw sql, либо полагаются на то, что им предлагает базовый функционал ORM. Это выглядит не совсем красиво или создает достаточно большую нагрузку на базу данных.

Понятно, что в погоне за скоростью выполнения сценариев, можно пожертвовать красотой кода, но что если скоростью можно пренебречь, а вот кроссплатформенностью — нет? Да и не хочется в python коде видеть что-то кроме python кода. А что если хочется на полную катушку использовать любимый ORM (для меня SQLAlchemy) и не писать raw sql запросы?

Последние пару дней работал над интересной задачей и хотел бы поделиться интересным опытом с сообществом.

В чём проявляется проблема:
Запускаю хранимую процедуру (хранимку) по выборке данных для отчета — выполняется три секунды, смотрю профайлером на бою — у пользователей те же результаты. Но проходит три часа и та же хранимка, с теми же параметрами выполняется уже 2 минуты, и аналогично у пользователей. Причём данные в используемые таблицы не вставлялись/удалялись, окружение не меняли и админы не делали настроек.

Как могут помнить те из вас, кто читает хаб «Программирование», зимой 2014 JetBrains объявила подписку на private preview C++ IDE. В результате немалое количество подписчиков ей уже пользуются, в начале осени мы планируем выпустить общедоступную early preview, а мы до сих пор олучаем письма от друзей на тему «мы не успели подписаться, как вскочить в этот поезд сейчас?».

А сейчас есть отличная возможность вписаться в похожую историю без опозданий: JetBrains открывает подписку на early preview новой IDE для разработчиков, которые пишут на SQL. И для админов баз данных, — им тоже бывает нужно что-то поудобнее, чем pgAdmin. Для админов новая IDE пригодится там, где им надо поработать с данными в таблицах. Назначать права, делать бэкапы и выполнять прочие чисто админские задачи IDE пока не умеет, хотя мы подумываем ее этому научить.

Короче: тут — подписываться, а под катом — подробности.

Добрый день, статья написана в качестве некоего продолжения данного опуса. Компания 1С довольно часто подвергается критике, нередко объективной, но я попытаюсь своим примером показать, что 1С предоставляет свободу выбора, что в нынешнее время как минимум заслуживает уважения. Также немного посчитаем деньги.

Мы разрабатываем базу данных. Однажны к нам обратилась компания, которая столкнулась со следующей задачей:

Есть некоторое множество объектов, и некоторое множество тегов. Каждый объект может содержать несколько тегов. Какие-то теги очень редкие, а какие-то встречаются часто. Одному объекту один тег может быть сопоставлен несколько раз.
Новые объекты, теги и связи между ними непрерывно добавляются.
Задача — очень быстро отвечать на вопросы вида: «сколько есть объектов, у которых есть тег А или B, но нету тега С» и похожие. На такие запросы хотелось бы отвечать за десятые доли секунды, при этом не останавливая загрузку данных.

Мы получили от них их данные вплоть до сегодняшнего дня, развернули тестовый кластер из четырех машин, и начали думать, как правильно распределить данные и как правильно представить задачу в виде SQL-запроса, чтобы получить максимальную производительность. В итоге решили, что запрос может иметь вид:

SELECT 
    COUNT(*) 
FROM (
    SELECT 
        object_id, 
        (MAX(tag == A) OR MAX(tag == B)) AND MIN(tag != C) AS good
    FROM tags
    WHERE tag IN (A, B, C)
    GROUP BY object_id
) WHERE good == 1;

Чтобы такой запрос выполнялся быстро, мы разбили данные между серверами кластера по object_id, а внутри каждого сервера отсортировали их по тегам. Таким образом сервер, выполняющий запрос, может отправить запрос без изменений на все сервера с данными, а затем просто сложить их результаты. На каждом сервере с данными для выполнения запроса достаточно найти строки для тегов A, B и C (а так как данные по тегу отсортированы, это быстрая операция), после чего выполнить запрос за один проход по этим строкам. Худший тег имеет несколько десятков миллионов объектов, несколько десятков миллионов строк обработать за десятые доли секунды видится возможным.
Стоит отметить, что подзапрос содержит GROUP BY object_id. GROUP BY в данной ситуации можно выполнить несколькими способами, например, если данные после тега отсортированы по object_id, то можно выполнить что-то похожее на merge sort. В данной ситуации, однако, мы данные по object_id не отсортировали, и оптимизатор разумно решил, что для выполнения GROUP BY надо построить хеш-таблицу.

Мы загрузили все данные в кластер, и запустили запрос. Запрос занял 25 секунд.

В обязанности администратора баз данных входит много разных задач, которые, в основном, направлены на поддержку работоспособности и целостности базы данных. И если целостность данных можно проверить через команду CHECKDB, то с поиском невалидных объектов в схеме не все так гладко.

Если проводить аналогии с Oracle, то в SQL Server нельзя так же легко получить список невалидных объектов:

SELECT owner, object_type, object_name
FROM all_objects
WHERE status = 'INVALID'

В большинстве ситуаций, узнать о том, что скриптовый объект является невалидным, можно только при его выполнении. Конечно, такое положение дел, может не всех устроить, поэтому предлагаю написать скрипт по поиску невалидных объектов в базе данных.

Иногда в своих проектах мне хотелось прикрутить некоторую географическую базу, с помощью которой я бы разделял пользователей ресурса по их месту пребывания. Но постоянная занятость делами насущными никак не давала реализовать идею с базой регионов и мало-мальски удобным интерфейсом для ее визуализации.
Волею судьбы и заказчика (или судьбы заказчика или заказчика судьбы) такая задача, наконец-то, возникла — необходимо создать базу регионов, городов и улиц для сегментирования пользователей и реализовать удобную web-форму, собственно, для ее использования. Благо заказчик ориентировал свой бизнес на Россию, что резко упростило задачу.

Путь к размещению SQL Server в любом облаке, будь это Microsoft или другое, должен начинаться с тщательного планирования. Архитектурные решения, принятые на ранней стадии проекта, могут совершенно неожиданным образом повлиять на будущее проекта, причем не всегда это могут быть очевидно-решаемые проблемы. Планирование также включает в себя решение о том, в какой модели должен размещаться SQL Server — в модели IaaS (на виртуальной машине) или в модели PaaS (как SQL Azure DB). Разница между моделями заключается как в особенностях технологического плана, так и ценового, например уровни соглашения об обслуживании (SLA) обеих моделей, влияние латентности как на ранней стадии, так и на стадии выхода проекта в свет, автоматизации и многого другого.
 
В этой статье мы попробуем разобраться не только в том, какие технологические различия есть между IaaS и PaaS в Microsoft Azure, но и том, откуда может такая разница возникать, откуда могут возникать проблемы в PaaS, которых можно и не встретить в IaaS, и архитектуре этих решений, а также о том, что такое Azure SQL DB Premium и когда его нужно использовать.
 

Здравствуйте, коллеги.
В этой статье я расскажу о своих изысканиях в вопросе: «А что же лучше: dense_rank() или max()» и, конечно, почему эти изыскания завершились с неожиданным, по крайней мере для меня, результатом.

Доброго дня,
Перед разработчиком часто возникает задача провести тест базы данных на больших объемах данных, но откуда взять эти самые данные? Ведь всем известно, что структура базы может достигать over 50 таблиц, которые не очень хочется заполнять руками. А если подумать о внешних ключах и составных первичных ключах значения которых связаны с другими таблицами, то голова начинает нагреваться пропорционально старому AMD с отключенным охлаждением.
В интернете существует много решений заполнения базы данный случайными значениями с использованием средств .NET, C++, Java и.д. В данной статье будет освещена тема заполнения базы данных случайными значениями средствами T-SQL под управлением MS SQL Server.

Есть немало книг, описывающих особенности и специфику конкретных БД.
Значительно меньше «программно-независимых» изданий, которые рассказывают об общих правилах и законах проектирования баз, принципах построения, нарушение которых может привести в дальнейшем к серьёзным ошибкам и проблемам. Где рассмотрена вся методологическая цепочка от постановки задачи до итогового анализа уровня целостности данных для каждого приложения.

Мы сейчас обсуждаем возможность издать на русском языке книгу Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design и хотели бы включить в эту дискуссию «коллективный разум».

Уважаемые читатели! Пожалуйста, оцените это издание по пятибалльной шкале. Насколько оно раскрывает тему? Будет ли оно полезно разработчикам БД — лично вам, или, может быть, вашим менее опытным коллегам, которые смогут избежать ошибок проектирования?
Комментарии своей оценки, как всегда, приветствуются.

Не так давно мы анонсировали бесплатную утилиту SQL Dynamite для поиска по метаинформации в базах данных. Хотим поблагодарить всех за хорошие отызвы, критику, найденные ошибки.
Для вас мы выпустили обновление, исправили найденные ошибки, и добавили поддержку SQLite и Informix.

Новая версия доступна здесь.

В прошлой статье мы обещали рассмотреть вопросы доступа к метаинформации БД. Сегодня поговорим про MS SQL и Sybase.

Процесс поиска и анализа связей различных объектов в СУБД Microsoft SQL Server, Sybase Adaptive Server Enterprise, Sybase Anywhere и SQL Azure прежде всего заключается в работе с метаданными.

Проблема в том что Sybase ASE и MSSQL, изначально имея одинаковую структуру метаданных, прошли долгий путь развития независимо друг от друга. Sybase Anywhere изначально была Watcom SQL и имела кучу отличий от Sybase ASE, а SQL Azure – это вообще облачная СУБД. Тем не менее, структура метаданных в них во многом схожа, в частности имеют одинаковые названия таблицы, содержащие ключевую информацию об объектах (sysobjects, syscomments и syscolumns).

OrientDB — взгляд человека, который привык работать с реляционными базами данных.
Напомню, что OrientDB — графовая, документно-ориентированная база данных, реализованная на Java.

Решил написать статью, для новичков, т.к в начале сложнее всего, а на рус. вводых статей с доходчивыми примерами практически нет.

Возможность мгновенного оповещения об определенных событиях требуется достаточно часто. Системные администраторы должны как можно быстрее узнавать о сбоях в работе сервисов и серверов, технический персонал на производстве — о сбоях и отклонениях в технологическом процессе, службы оперативного реагирования — о происшествиях. Самый очевидный способ оповещения — это оповещение по СМС. Для оповещения через СМС существуют специальные интернет сервисы, осуществляющие рассылку сообщений на заданную группу. Можно сэкономить и осуществлять рассылку самостоятельно, используя GSM модем. Но у этого способа есть несколько минусов: нужно уметь работать с последовательным портом, а через него с модемом, последовательно обрабатывать команды; отправлять сообщения по-русски не так просто; скорость отправки большому количеству адресатов может оказаться не достаточно быстрой; сложно обеспечить контроль доставки; нет гарантии, что сотовый оператор не заблокирует сим карту, если посчитает рассылки за спам. В целом, сервисы рассылки дают хотя бы какие-то гарантии, но стоят определенных денег.

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

Совсем другое дело — системы обмена мгновенными сообщениями (ICQ, XMPP). Протокол XMPP оказывается более предпочтительным благодаря тому, что он открыт. А благодаря тому, что это полноценный сетевой протокол, то получаются «из коробки» доступны следующие возможности:

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

При желании список можно продолжить.

В качестве примера реализации данного подхода, разработана программа, которая оповещает об ошибках технологического оборудования. Стойка оборудования представляет из себя некую программу, которая пишет сообщения (и сообщения об ошибках в том числе) в базу данных. БД имеет формат — Paradox, а кодировка данных Win-1251. Было решено отказаться от графического интерфейса в пользу консольного приложения, параметры задавать текстовыми файлами. Инструмент для решения — QT.

Реализованный функционал: сбор ошибок с множества технологических установок, отправка сообщений через jabber, общий чат через jabber.

Очень часто за основу архитектуры приложения берётся дерево. Простой пример: есть страны, в странах — области, в областях — города, в городах — организации, в организациях — работники, товары или что-либо ещё. Использование дерева вполне логично и оправдано. Иерархичность такой системы показывает некая абстрактная таблица. Назовём её object:

CREATE TABLE object (
  id NUMBER(11),
  parent_id NUMBER(11),
  type VARCHAR2(16) NOT NULL,
  name VARCHAR2(255) NOT NULL,
  CONSTRAINT pk_object PRIMARY KEY (id),
  CONSTRAINT fk_object_parent FOREIGN KEY (parent_id) REFERENCES object (id) ON DELETE CASCADE ENABLE
);

Наполним её какими-нибудь данными:

id  |  parent_id  |  type     |  name
------------------------------------------------------
1   |  NULL       |  country  |  Россия
2   |  1          |  region   |  Московская область
3   |  1          |  region   |  Новосибирская область
4   |  2          |  city     |  Москва
5   |  3          |  city     |  Новосибирск

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

-- Выбрать все города России
SELECT *
  FROM object
    WHERE type = 'city'
    START WITH id = 1 CONNECT BY PRIOR id = parent_id;

-- Выбрать страну, в которой находится Новосибирск
SELECT *
  FROM object
    WHERE type = 'country'
    START WITH id = 5 CONNECT BY PRIOR parent_id = id;

Однако проблемы появляются, когда записей в таблице становится на столько много, что любой рекурсивный запрос выполняется минуты две, а то и больше. Менять всю архитектуру как-то поздновато… Тут-то нам на помощь и приходит денормализация дерева. В этой статье я расскажу об одном из способов такой денормализации.

В данном топике хотелось бы поговорить о повышении производительности при работе с таблицами.

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

Как правило, это происходит из-за плохо спроектированной схемы – изначально не рассчитанной на оперирование большими объемами данных.

Чтобы рост данных в таблицах не приводил к падению производительности при работе с ними, рекомендуется взять на вооружение несколько правил при проектировании схемы.

Привет, уважаемые Хабро-читатели и SQL-писатели. Команда ХостТрекера хочет поделиться полезной утилитой для SQL разработчиков и администраторов.

Какую задачу решаем?

Нахождение зависимостей объектов в БД.
Поиск по строке в метаданных БД (хранимых процедурах, View, пользовательских функциях, определениях таблиц, индексах, ключах)

В предыдущем посте была рассмотрена автоматизация процесса дефрагментации индексов. Теперь пришла очередь статистики.

Собственно для чего она нужна?

При выполнении любого запроса, оптимизатор запросов, в рамках имеющейся у него информации, пытается построить оптимальный план выполнения — который будет отображать из себя последовательность операций, за счет выполнения которых можно получить требуемый результат, описанный в запросе.

В процессе выбора той или иной операции, оптимизатор запросов к числу наиболее важных входных данных относит статистику, описывающую распределение значений данных для столбцов внутри таблицы или индекса.

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

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

Среди подобных задач можно выделить следующие:

1. Дефрагментация индексов
2. Обновление статистики
3. Резервное копирование

Рассмотрим по порядку автоматизацию каждой из этих задач.