Ответы пользователя по тегу Базы данных
  • Чем править базу данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    Судя по расширениям - это куски mysql.
    Только запросами к запущенному серверу mysql и взаимодействовать. Клиент выберите любой, их много
    Ответ написан
    9 комментариев
  • Как перенести данные одного кластера postgres в другой?

    Melkij
    @Melkij
    PostgreSQL DBA
    Минорные релизы именно так и ставятся: обновляются выполняемые бинарники и рестартовать базу в том же самом datadir.
    О чём есть замечание в каждых release notes: https://www.postgresql.org/docs/9.6/static/release...
    A dump/restore is not required for those running 9.6.X.

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

    Кстати, обратите внимание, что 9.6.9 уже вышел и обновляться стоит именно на него.
    Ответ написан
    2 комментария
  • Как устроены базы данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    4+32*2+32*2 = 132

    Мне даже интересно, с чего вы придумали умножить именно на 2. Я честно не знаю распространённых кодировок фиксированной двухбайтной ширины. Обычно речь или про ворох однобайтовых или кодировки переменной длины.

    Поиск по смещению в датафайле - да, применяется. Но не имеет в общем случае ровно ничего общего со структурой таблицы уровня SQL.

    Например, краткий взгляд на internals postgresql: momjian.us/main/writings/pgsql/internalpics.pdf
    Ответ написан
  • Как отслеживать изменения в СУБД при конкурентной записи?

    Melkij
    @Melkij
    PostgreSQL DBA
    Возьмите pgq и пишите в очередь перед коммитом как найти нужные данные. Или триггером сразу при записи в таблицы шлите сообщения через pgq.
    При экспорте соответственно читаете сообщения из pgq. Всё.

    Или напишите или возьмите какой-нибудь готовый logical decoding. Сможете читать вообще поток репликации

    В общем случае в общем-то нужна всё равно очередь. Из транзакции в таблички очереди пишете метки какие данные изменились, читатель читает эту метки и получает все изменения независимо от времени коммита.
    Ответ написан
    Комментировать
  • Как вы измеряете профиль нагрузки (соотношение кол-ва чтений/кол-ву записей) в PostgreSQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    Посмотрите в циферки что собирает stats collector, их много разных. https://www.postgresql.org/docs/10/static/monitori...
    например, запись буферов на диски - это buffers_checkpoint, buffers_clean и buffers_backend в pg_stat_bgwriter
    pg_stat_database c blks_read, blks_hit, blk_read_time и blk_write_time

    И разумеется pg_stat_statements с детализацией ресурсов по отдельным запросам.

    Вот кстати, хорошая картинка куда смотреть за детализацией каких подсистем базы
    postgres-observability-9.6.png
    Ответ написан
  • Почему не работает LIMIT в этом подготовленном запросе?

    Melkij
    @Melkij
    PostgreSQL DBA
    limit можно передавать параметром. Проблема в комбинации двух фактов:
    1) эмуляция prepared statements в pdo дефолтно передаёт строки
    2) парсер mysql утверждает, что limit обязан быть числом

    Поэтому необходимо исправить один любой из этих нюансов:
    1) указать тип параметра явно:
    $q = $dbh->prepare("select ... limit :limit");
    $q->bindValue(':limit', 10, PDO::PARAM_INT);
    $q->execute();

    2) либо отключить эмуляцию prepared statements: PDO::ATTR_EMULATE_PREPARES => false в настройках соединения.
    Ответ написан
    1 комментарий
  • Как перенести данные из Postgres в Mysql?

    Melkij
    @Melkij
    PostgreSQL DBA
    Если у вас в дампе только insert'ы (и именно они, а не copy; например после pg_dump --inserts --data-only) - то попробуйте импортировать в mysql как есть.
    Возможно sql_mode надо будет подправить в ANSI quotes, а данные вроде корректно переезжают.

    Ну или сделать дампы в csv - команды copy в postgresql и load data infile в mysql.
    Ответ написан
    Комментировать
  • Как написать функцию итератор?

    Melkij
    @Melkij
    PostgreSQL DBA
    Это называется не итератором.

    insert into terms (term, freq) values (?, 1) on conflict (term) do update freq = excluded.freq + 1 returning freq;

    В функцию завернуть по желанию. 9.5+
    Ответ написан
    Комментировать
  • Насколько правильно использовать json для хранения данных в базе данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    json нужно использовать если у вам надо хранить json и сохранять полностью его форматирование. Форматирование именно самого json с пробелами, табами и переносами строк - т.е. как текст, но с валидацией что здесь именно json.
    если вам нужно хранить json - лучше использовать jsonb
    А для описанной задачи и просто массива достаточно, bigint[] или text[]. Контактные данные для заказа уместны именно в заказе, а по jsonb и массивам возможно вполне внятно и искать в том числе
    Ответ написан
    Комментировать
  • Для чего нужен покрывающий индекс?

    Melkij
    @Melkij
    PostgreSQL DBA
    Покрывающий (covering) индекс - это индекс, которого достаточно для ответа на запрос вовсе без обращения к самой таблице. В самом индексе хранится достаточно данных для ответа на запрос и, хоть и возможно по индексу достать всю строку данных - это просто не нужно. За счёт того, что не нужно дёргать непосредственно таблицу, а ответить можно используя только индекс - покрывающие индексы несколько быстрее (насколько - зависит от дисков, кэша и объёма горячей части базы). Но при этом, разумеется, сам индекс становится больше и злоупотреблять этим не нужно.
    Ответ написан
    Комментировать
  • Как правильно реализовать структуру бд для многопользовательской платформы?

    Melkij
    @Melkij
    PostgreSQL DBA
    У конструкторов типичный workload - около нуля на большинство площадок и небольшой процент активных. Заворачивать индивидуально каждого в свою БД - сильно утомительно в поддержке. Дожили до 1000 созданных сайтов (что не так уж много если не сказать "вообще крохи") - у вас 1000 баз, что уже весьма дофига. Выкатить в прод новую версию приложения - уже приключение.

    Если вы хотите SaaS - т.е. у конечных пользователей прямого доступа в БД нет никакого - то в поддержке сильно проще будет классическая схема шардирования. К тому же у вас данные разных клиентов заведомо по предметной области никак не пересекаются.
    - в таблицах специфичных для клиентов вводится site_id и входит в уникальные ключи и прочее счастье ограничений целостности (для postgresql можете дополнительно прикрутить row level security и база будет дополнительно приглядывать)
    - отдельно размещается ваш биллинг и управление пользователями, где помимо прочего пишете таблицу соответствия, какой site_id расположен на каком физическом хосте и в какой базе данных (плюс список ro-реплик)
    - закешировать соответствие сайта базе можно в каком-нибудь redis и ходить с одного единого пула серверов приложений что сильно проще по масштабированию приложения (если сервер приложения рассматривать как stateless, что опять же сильно проще в поддержке).

    В итоге схема позволяет работает с осмысленным числом крупных баз (начиная с одной и вводя новые по необходимости), неактивные сайты мигрировать в архивные базы, горячие от дорогих клиентов - выделять хоть даже в отдельную инфраструктуру. Кстати, фокус с отделением vip-клиента в отдельную инфраструктуру позволяет дать этому клиенту и прямой доступ в базу при необходимости и возможность пилить кастомный функционал именно для него.
    Ответ написан
  • Можно ли поставить разные таймзоны в типе datetime with timezone postgresql?

    Melkij
    @Melkij
    PostgreSQL DBA
    timestamp with time zone приводит и хранит данные только в UTC и переводит их в местное время при чтении согласно сессионной настройке timezone.

    https://www.postgresql.org/docs/9.6/static/datatyp...
    For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.


    timestamp without time zone вовсе игнорирует указание в литерале временной зоны.

    а других timestamp и нет. Поэтому нет, хранить таймзону (при необходимости в этом) необходимо отдельно. Зато за счёт этого timestamp занимает всего константные 8 байт.
    Ответ написан
    Комментировать
  • Pgpool2 multy-master mode где искать мануалы по настройке? Как вы масштабируете базу(под запись)?

    Melkij
    @Melkij
    PostgreSQL DBA
    Как вы масштабируете базу(под запись)?

    В основном никак. У нас нет баз на столько десятков террабайт данных чтобы внятно настроенный единственный мастер с парой обычных потоковых ro-реплик не справлялся.

    Если у вас столько данных пишется что одного мастера действительно мало - то репликация вам не подходит по своему определению, она не про масштабирование записи, а только чтения и high availability (репликация = копия. Копию нельзя сделать, если писать не все данные).

    А те базы что у нас распиливались - распиливались они не потому что в мастер упёрлись:
    Что-то пилится на горячее-архивное: отдельные одна или несколько машин на более дешёвых (медленных) дисках, куда переносятся данные старше какого-то времени, к которым обращаются редко.
    Что-то пилится на части функционально. Например, сайт - один кластер, данные для окружающих некритичных сервисов - другой кластер. Или данные ru-сайта - один кластер, данные нескольких других стран - отдельные базы на другом кластере.

    Ещё можно пилить по каким-нибудь другим динамическим критериям. Например, данные всех пользователей от 1 до 1000 - на один кластер, от 1000 до 2000 - на другой. Плюс пара машин с данными координатора какой id на какой машине и авторизации. Это уже эталонное горизонтальное масштабирование. Крайне редко кому действительно нужно и собирается типично вручную с пониманием что и зачем делается.

    Сильно ли нагружает сервер процес синхронной репликация?

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

    чаще всего используеться репликация на уровне приложения

    Репликация чаще всего используется штатная потоковая.
    Запросы от приложения удобнее направлять именно из приложения не из-за сырости прокси, а потому что именно приложению лучше знать, нужно ли запрос отправлять на реплику (и если реплику - то какую, боевую? отдельную для медленных запросов аналитики?) или его вообще можно выполнять только на мастере.

    какие есть угрозы при такой балансировке базы?

    В смысле при мультимастере? Кучу головной боли вам доставит CAP теорема, из-за которой внятного мультимастера нет.
    Самое весёлое - split brain, когда у вас образовались противоречащие друг другу данные на двух мастерах.
    Или триггерная репликация внезапно встала колом и надо разбираться, из-за чего.
    Отправлять пишущие запросы синхронно на несколько хостов и предполагать, что там будут сходиться данные в итоге - это надо быть или большим оптимистом или очень внимательно проверять каждый запрос на предмет что именно он будет делать и как себя поведёт при конкурентном доступе.
    Ответ написан
    4 комментария
  • Что учить администратору баз данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    Как человек в этом году неожиданно сменивший деятельность с senior php dev на DBA - хочу задать встречный вопрос:
    а вы вообще видите вакансии на начинающего-студента-DBA? Целую одну или хотя бы даже две? Для увидевшего SQL вот только что студента и уже желающего быть DBA всего через пару месяцев? Человека, который даже не написал, какую именно СУБД ему интересно изучать до уровня DBA?

    Отдельная позиция DBA в проекте - значит ни штатные системные администраторы (со стороны администрирования) ни старшие разработчики (со знанием SQL) уже не обладают достаточными познаниями в используемой СУБД. Но эта БД важна для бизнеса и ищется специалист по ней, который будет обеспечивать хорошую работу этой базы.
    Несколько позиций DBA в кампании чтобы брать стажера - это очень большой проект. Эти могут себе позволить отвлекать своих специалистов от работы и вкладывать деньги в выращивание нового специалиста. Если вы чем-то сильно заинтересуете кампанию.

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

    Определитесь с конкретной СУБД и прочитайте полностью её мануал. Например мануал postgresql 10 в pdf занимает свыше 3 тысяч страниц A4. На пару месяцев этого уже хватит. А это только мануал. Только по непосредственно СУБД.
    Плюс необходимо знать базовое администрирование той ОС под которой эта СУБД используется (например, я как postgresql dba даже близко не представляю что делать с windows - такая экзотика в жизни не встречается. А вот для MS SQL наверняка необязательно разбираться в linux).
    Плюс теория: реляционная логика, обеспечение транзакционного, конкурентного доступа, восстановление после сбоев
    Плюс практика - активность в профильных сообществах, форумах. Читаете, проверяете, запоминаете, вежливо переспрашиваете в комментариях если вам кажется что предыдущий отвечающий ошибся, отвечаете на вопросы.

    Интересно? Вперёд. Но в DBA за 3 месяца из нулевого студента - не верю.
    Ответ написан
    2 комментария
  • Как при запросе получить в ячейке больший объем символов?

    Melkij
    @Melkij
    PostgreSQL DBA
    pg_stat_activity.query ограничен сверху конфигурационным параметром track_activity_query_size. Дефолтно 1024 байта.

    PS: current_query оно называлось до postgresql 9.2, которые уже давно EOL и сам 9.2 уже EOL. Планируйте обновление.
    Ответ написан
  • Как посмотреть логи изменений в базе данных MySQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    - изобрести машину времени
    - вернуться на дату 90 дней назад
    - включить логирование всех запросов либо настроить ведение и непрерывное архивирование бинарного лога
    - вернуться в настоящее время и анализировать настроенный в прошлом шаге лог

    Если вы не настроили вести лог изменений - то его никто хранить не будет. Даже mvcc версионник innodb будет стараться вычищать старые версии строк. Потому что зачем хранить кучу данных, если не было изложено требования их хранить?
    Ответ написан
    Комментировать
  • Какие книги прочитать по лучшим практикам проектирования БД?

    Melkij
    @Melkij
    PostgreSQL DBA
    Bill Karwin SQL Antipatterns
    В общем-то, в духе рефакторинга как раз: как делают часто неправильно, почему это плохо (но когда и адекватно для задачи) и как сделать лучше.
    Ответ написан
    1 комментарий
  • Что почитать на досуге?

    Melkij
    @Melkij
    PostgreSQL DBA
    Конечно, Макконнелл "Совершенный код",
    Мартин "Чистый код"
    "Рефакторинг" Фаулера

    "Приемы объектно-ориентированного проектирования" Э. Гамма

    Поскольку в списке mysql - High Performance MySQL обязателен к прочтению
    Отдельно рекомендую "Регулярные выражения" Дж.Фриддла, отлично вправляет мозг по регуляркам.

    По базам в целом:
    короткая "Architecture of a Database System" J. M. Hellerstein, M. Stonebraker and J. Hamilton расскажет об общих подходах построения СУБД
    Bill Karwin SQL_Antipatterns - как не надо писать SQL. А вот, кстати, отличный материал как надо писать, но не книга: use-the-index-luke.com

    Это всё вполне можно читать в порядке отдыха.

    Фундаментальные, которые уже идут заметно тяжелее:
    "Transactional Information Systems" Weikum, Vossen - монументальная теория транзакционной обработки
    "Алгоритмы. Построение и анализ" Томас Х. Кормен, Чарльз И. Лейзерсон, Рональд Л. Ривест, Клиффорд Штайн - про основные алгоритмы. Всё библиотечное, но полезнейшая весь для понимания обычно скрытой машинерии и для прохождения собеседований.
    Ответ написан
    Комментировать
  • Как сделать временную таблицу с одинаковыми данными?

    Melkij
    @Melkij
    PostgreSQL DBA
    небольшая ремарка №3: а нафига вам вообще cross join понадобился?
    SELECT 'Economy' FROM generate_series(1,10);
    Ответ написан
    1 комментарий
  • Нужен ли primary key для такого?

    Melkij
    @Melkij
    PostgreSQL DBA
    primary key нужен всегда.
    Если по логике вашего приложения одному user_id может соответствовать только один пользователь в каждом service_id - то PK на первые два поля.
    Если несколько - то pk на 3 поля.

    Порядок полей при указании PK таблицы связей важен. Для mysql/innodb очень важен, т.к. таблица кластеризована по PK и любое обращение по вторичному ключу влечёт поиск по PK (кроме покрывающих индексов). Порядок выбирайте исходя из наиболее частых запросов к таблице, чтобы максимум запросов могло идти по первичному ключу.
    Ответ написан
    Комментировать