Posted:
Фев 12, 2023
Comments:
0 Comments

Размер базы данных postgresql: Как узнать размер базы данных PostgreSQL?

PostgreSQL и Clickhouse / Хабр

С момента появления System R в 1974 году реляционные базы данных в целом, и SQL-базы в частности, стали доминирующим подходом для хранения данных, и до сих пор сохранили это положение, несмотря на появление многочисленных серьезных конкурентов. Слухи о кончине и упадке традиционных реляционных баз данных появляются постоянно, но PostgreSQL уверенно держит позиции и опережает  как своих предшественников, так и предполагаемых преемников.

Фактически, база данных MySQL была настолько распространена, что стала частью одноименного стека LAMP (Linux, Apache, MySQL, Perl), преобладающего в ранних веб-разработках.

Единственным большим исключением из этой тенденции является OLAP, со специализированными методами, позволяющими резко повысить производительность определенных рабочих нагрузок. А новые соперники, такие как ClickHouse, качественно изменили подходы к аналитике.

Один размер не подходит для всех

Часто бывает, что доминирующая технология применяется бездумно, даже если это нецелесообразно. По этой причине часто встречаются решения, когда любые виды данных помещаются в реляционные базы данных общего назначения. Можно найти такие экстремальные примеры, как базы данных Oracle для датасетов с  пятью небольшими элементами (не столбцами, а именно единицами данных) или использование компанией Apple базы данных SQLite для логов (они позже исправили эту ошибку).

Разработка DNS-сервера Bind10 началась с целью решить проблемы масштабирования Bind9, используя SQLite в качестве бэкенда. Но разработка была прекращена ISC в 2014 году, а OSS-проект Bundy в настоящее время не активен. С другой стороны, PowerDNS изначально фокусировался на масштабировании производительности с помощью MySQL/PostgreSQL.

В 2005 году Майкл Стоунбрейкер (Michael Stonebraker), ученый в области баз данных (Ingres и позже PostgreSQL), вместе с Угур Кетинтемел (Uğur Çetintemel) написали статью «One Size Fits All»: An Idea Whose Time Has Come and Gone» (перевод на русский — «Один размер пригоден для всех: идея, время которой пришло и ушло«), утверждая, что все это слишком затянулось, подкрепив этот аргумент бенчмарками.

Вкратце, есть множество рабочих нагрузок помимо основной — Online Transaction Processing (OLTP), с которыми базы данных с привычной архитектурой не справлялись и их применение не имело смысла.

Следует отметить, что Стоунбрейкер  и Кетинтемел выступали не против реляционных баз данных или SQL, а против конкретной архитектуры, происходящей от оригинальных систем System R и Ingres, которые были и продолжают использоваться в большинстве систем баз данных общего назначения.

Эта архитектура имеет следующие особенности:

  • Дисковые хранилища, ориентированные на строки, и индексные структуры.

  • Многопоточность для уменьшения задержек.

  • Управление параллелизмом через блокировки.

  • Восстановление на основе журналов транзакций.

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

Clickhouse

Прогноз об отделении OLAP-движков от обычных баз данных в значительной степени сбылся, и теперь OLAP сами по себе являются важной категорией, а Vertica, коммерческое ответвление оригинального cstore, о котором идет речь в статье, является одним из основных игроков.

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

Или даже необходимо, как это произошло в случае с OLAP-базой данных Yandex ClickHouse, которая недавно была выделена в стартап, получивший финансирование в рамках серии В на сумму 250 млн долларов США. Разработчики ClickHouse хотели анализировать данные в реальном времени, но не с помощью специализированных структур данных, как это обычно бывает в данной области, а с помощью стандартного языка SQL. Конечно, для использования специализированных структур были причины: использование стандартных инструментов считалось невозможным, отчасти из-за архитектуры. Но как это часто бывает, невозможное оказалось «просто» большим объемом работы и блестящими инженерными решениями. Через несколько лет разработчики получили то, к чему стремились: специализированный движок для OLAP, но с возможностью SQL-запросов и аналитики в реальном времени.

Впечатляют как инженерные решения, так и результаты бенчмарков, включая наши собственные тесты (видео).

ClickHouse значительно быстрее расширений PostgreSQL, таких как CitusDB или Timescale DB, и, по некоторым данным, быстрее, чем Vertica.

Конец эпохи?

В статье 2005 года OLTP оставалась единственной областью, где традиционная архитектура (дисковая, ориентированная на строки, многопоточная) оставалась жизнеспособной. Два года спустя Стоунбрейкерс соавторами опубликовали статью «The end of an Architectural Era (It’s Time for a Complete Rewrite)»  (перевод на русский — «Конец архитектурной эпохи, или Наступило время полностью переписывать системы управления данными«), где утверждалось, что даже для OLTP существующие движки могут быть превзойдены в десятки раз.

Ключевым моментом стало то, что давно принятые предположения об относительной производительности различных компонентов больше не являются точными, и выяснилось, что, около 90% бюджета производительности движка базы данных используется не для фактической обработки данных, а для накладных расходов, таких как управление буферами и блокировками. Таким образом, если бы мы могли устранить эти накладные расходы, то сделали бы механизм базы данных, который в десятки раз быстрее существующих. Для достижения таких результатов потребуется создать однопоточный движок базы данных, работающий в оперативной памяти, что является радикальным отходом от существующей архитектуры.

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

Например, еще в начале 2000-х годов Yahoo придерживалась политики, согласно которой любой датасет размером менее 2 ГБ должен храниться в оперативной памяти, а не на диске. Чуть позже архитектуры EventPoster, In-Process REST и LMAX с шаблоном Disruptor показали, что переход от сложных многопоточных систем на базе дисков к однопоточным архитектурам на базе оперативной памяти может дать огромные преимущества в плане простоты, надежности и производительности.

И это при 32-битных вычислениях. Сегодня мы можем получить отдельные серверы с десятками терабайт памяти, сконфигурированные для нас одним щелчком мыши (с последующим выставлением счета…), поэтому рабочие нагрузки, которые мы можем хранить в оперативной памяти, весьма значительны.

Stonebraker и его команда создали H-Store, академический прототип, и voltdb, коммерческий продукт с соответствующим стартапом.

Это не вызвало бурного распространения в мире баз данных

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

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

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

PostgreSQL

Итак, похоже, что эра баз данных OLTP с традиционной архитектурой на самом деле не закончилась. Но это не должно сильно беспокоить профессора Stonebraker, поскольку претендент на победу по-прежнему является его детищем, просто более ранним. Нет, не Ingres — ранний публичный аналог System R от IBM, а его преемник: PostgreSQL.

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

В GitLab мы тоже используем PostgreSQL с репликацией, отказавшись от MySQL в 2019 году, отчасти потому, что PostgreSQL действительно обладает важными для нас функциями, а также по причине того, что большинство наших клиентов и так его использовали.

А что насчет NoSQL?

Ну, и что насчет этого? Хотя движение NoSQL в начале 2000-х годов говорило о некоторых недостатках популярных баз данных, технологические ограничения действительно имели значение только в очень редких случаях. NoSQL — это не совсем категория СУБД, а скорее особенность таких движков баз данных, как PostgreSQL.

С увеличением вычислительной мощности и быстродействия хранилищ большие объемы и скорость транзакций могут обрабатываться традиционными движками баз данных, а нетрадиционные могут обслуживать реляционные модели, используя SQL в качестве интерфейса, например, VoltDB и Spanner от Google, построенный на основе BigTable. Бывают ситуации, когда реляционная база данных не нужна, а достаточно хранилищ ключ-значение или документов JSON, но, например, PostgreSQL прекрасно справляется с JSON в большинстве таких случаев. В современных реляционных базах данных также есть поддержка и специализированных возможностей, таких, как полнотекстовый поиск или GIS.

Через пару дней в OTUS пройдет открытый урок «Особенности мажорного обновления PostgreSQL с расширениями на примере расширения PostGIS». На этом занятии рассмотрим:

  • Какие виды обновления PostgreSQL бывают.

  • Какие методы используются при обновлении системы.

  • Как обновить кластер БД, в котором уже установлены расширения.

  • Особенности методов обновления.

Регистрация на занятие для всех желающих — по ссылке.

Оценка размера базы данных — SQL Server





Twitter




LinkedIn




Facebook




Адрес электронной почты










  • Статья


  • Чтение занимает 2 мин


Применимо к: SQL Server (все поддерживаемые версии) Azure SQL database Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

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

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

  • Обеспечения соответствующего физического объема места на диске, необходимого для хранения данных и индексов.

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

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

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






Как точно получить размер базы данных в postgresql?

Задавать вопрос

спросил

Изменено
4 года, 11 месяцев назад

Просмотрено
7к раз

Мы работаем на PostgreSQL версии 9. 1, ранее у нас было более 1 миллиарда строк в одной таблице, и она была удалена. Однако похоже, что команда \l+ по-прежнему неточно сообщает о фактическом размере базы данных (она сообщила о 568 ГБ, но на самом деле это намного меньше).

Доказательством того, что 568 Гбайт неверно, является то, что размер отдельной таблицы не совпал с числом, как вы можете видеть, первые 20 связей имеют размер 4292 Мбайт, остальные 985 отношений значительно меньше 10 Мбайт. На самом деле все они в сумме составляют менее 6 ГБ.

Есть идеи, почему PostgreSQL так сильно раздувается? Если подтвердится, как я могу разблокировать? Я не очень хорошо знаком с VACUUM , это то, что мне нужно сделать? Если да, то как?

Большое спасибо.

 pmlex=# \l+
                                                                       Список баз данных
      Имя | Владелец | Кодирование | Разобрать | Тип | Права доступа | Размер | Табличное пространство | Описание
------------------+----------+------------+---------- ---+-------------+------------------------+-------- -+---------------------------+----------------------------------- ---------
 пмлекс | пмлекс | UTF8 | en_US. UTF-8 | en_US.UTF-8 | | 568 ГБ | pg_default |
 pmlex_analytics | пмлекс | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 433 МБ | pg_default |
 постгрес | постгрес | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5945 КБ | pg_default | база данных административного соединения по умолчанию
 шаблон0 | постгрес | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =с/постгрес +| 5841 КБ | pg_default | неизменяемая пустая база данных
                 | | | | | postgres=CTc/postgres | | |
 шаблон1 | постгрес | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =с/постгрес +| 5841 КБ | pg_default | шаблон по умолчанию для новых баз данных
                 | | | | | postgres=CTc/postgres | | |
(5 рядов)
pmlex=# ВЫБЕРИТЕ nspname || '.' || relname AS "отношение",
pmlex-# pg_size_pretty(pg_relation_size(C.oid)) КАК "размер"
pmlex-# ИЗ pg_class C
pmlex-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
pmlex-# ГДЕ nspname НЕ В ('pg_catalog', 'information_schema')
pmlex-# ORDER BY pg_relation_size(C.oid) DESC;
              отношение | размер
----------------------+------------------------
 public. page_page | 1289МБ
 public.page_pageimageистория | 570 МБ
 pg_toast.pg_toast_158103 | 273 МБ
 public.celery_taskmeta_task_id_key | 233 МБ
 public.page_page_unique_hash_uniq | 140 МБ
 public.page_page_ad_text_id | 136 МБ
 public.page_page_kn_result_id | 125 МБ
 public.page_page_seo_term_id | 124 МБ
 public.page_page_kn_search_id | 124 МБ
 public.page_page_direct_network_tag | 124 МБ
 public.page_page_traffic_source_id | 123 МБ
 public.page_page_active | 123 МБ
 public.page_page_is_referrer | 123 МБ
 public.page_page_category_id | 123 МБ
 public.page_page_host_id | 123 МБ
 public.page_page_serp_id | 121 МБ
 public.page_page_domain_id | 120 МБ
 public.celery_taskmeta_pkey | 106 МБ
 public.page_pagerenderhistory | 102 МБ
 public.page_page_campaign_id | 89МБ
...
...
...
 pg_toast.pg_toast_4354379 | 0 байт
(1005 строк)
  • postgresql
  • управление базой данных

3

Возможные варианты:

1). Убедитесь, что автоочистка включена и настроена агрессивно.

2). Воссоздание таблицы, как я упоминал в предыдущем комментарии (создать-таблицу-как-выбрать + обрезать + перезагрузить исходную таблицу).

3). Запуск CLUSTER для таблицы, если вы можете позволить себе быть заблокированным из этой таблицы (монопольная блокировка).

4). ВАКУУМ ПОЛНЫЙ, хотя КЛАСТЕР более эффективен и рекомендуется.

5). Запустив простой VACUUM ANALYZE несколько раз и оставив таблицу как есть, чтобы в конечном итоге заполнить резервную копию по мере поступления новых данных.

6). Дамп и перезагрузка таблицы через pg_dump

7). pg_repack (хотя я не использовал его в продакшене)

1

скорее всего будет выглядеть иначе, если вы используете pg_total_relation_size вместо pg_relation_size

pg_relation_size не дает общий размер таблицы, см.

https://www.postgresql.org/docs/9.5/static/functions-admin. html#FUNCTIONS-ADMIN-DBSIZE

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

postgresql — Postgres: как определить, откуда происходит рост размера базы данных в поведении пользователей за это время.

Наш основной администратор базы данных дал несколько рекомендаций, причем его главная рекомендация заключалась в том, чтобы экспортировать всю базу данных, а затем повторно импортировать ее, что, согласно его тестам на втором сервере, клонированном с нашего основного, требует около 3 часов простоя и получает размер всего до 300 Гб.

Двумя основными областями, которые меня беспокоят, было бы выяснить, откуда берется этот значительный рост (используя du -h, я могу, по крайней мере, увидеть, что он находится в каталоге /data без значительного роста в табличном пространстве или pg_wal), и понять, как импортируется а экспорт базы данных может дать нам почти 300 ГБ пространства для восстановления без фактической потери каких-либо производственных данных.

  • postgresql
  • postgresql-12
  • размер базы данных

2

Первым делом я бы перешел в каталог данных и запустил

 du -sk *

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

Как правило, увеличение использования диска происходит по одной из двух причин:

  1. WAL в pg_wal не может быть удален. Это может быть связано с проблемой архиватора (см. pg_stat_archiver ) или с устаревшим слотом репликации (см. pg_replication_slots ).

  2. Некоторые таблицы или индексы раздуты.

    Если вы создали копию базы данных с помощью pg_dump /restore, вы уже на полпути к решению. Запустите что-то вроде этого в обеих базах данных:

     SELECT oid::regclass AS объект, relkind, pg_relation_size(oid) размер AS
ОТ pg_class
ЗАКАЗАТЬ ПО РАЗМЕРУ DESC;

    Сравните выходные данные обеих сторон и отслеживайте таблицы и индексы, которые значительно больше в исходной базе данных.

    Устраните вздутие, изучив возможные причины. Как только вы это сделаете, избавьтесь от блода с помощью VACUUM (FULL) (внимание, это требует простоя).

    This entry was posted in Популярное