Измерьте размер строки таблицы PostgreSQL

У меня есть таблица PostgreSQL. select * является очень медленным тогда как select id хорошо и быстр. Я думаю, что может случиться так, что размер строки является очень большим, и это требует времени к транспорту, или это может быть некоторый другой фактор.

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

Вот моя схема таблицы минус имена:

integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
integer                  | not null default 0
text                     | default '{}'::text
text                     | 
timestamp with time zone | 
integer                  | 
timestamp with time zone | 
integer                  | 

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

Вопросы

  1. Есть ли что-нибудь об этом, которое кричит 'сумасшедшее неэффективный'?
  2. Существует ли способ измерить размер страницы в командной строке Пост-ГРЭС, чтобы помочь мне отладить это?
88
08.01.2020, 00:25
3 ответа

Q2: way to measure page size

PostgreSQL обеспечивает много Функций Размера Объекта базы данных. Я упаковал самые интересные в этом запросе и добавил некоторые Функции доступа Статистики внизу. (Дополнительный модуль pgstattuple обеспечивает более полезные функции, все же.)

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

Этот запрос требует Пост-ГРЭС 9.3 или позже. Поскольку более старые версии видят ниже.

Используя a VALUES выражение в a LATERAL подзапрос, чтобы не обстоятельно объяснять вычисления для каждой строки.

Замена public.tbl (дважды) с Вашим дополнительно квалифицированным к схеме именем таблицы для получения компактного представления собранных статистических данных о размере строк. Вы могли перенести это в функцию plpgsql для повторного использования, вручить имя таблицы как параметр и использование EXECUTE ...

SELECT l.metric, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t                     -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);

Результат:

              metric               | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 44138496 | 42 MB        |            91
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    32768 | 32 kB        |             0
 table_size_incl_toast             | 44179456 | 42 MB        |            91
 indexes_size                      | 33128448 | 32 MB        |            68
 total_size_incl_toast_and_indexes | 77307904 | 74 MB        |           159
 live_rows_in_text_representation  | 29987360 | 29 MB        |            62
 ------------------------------    |          |              |
 row_count                         |   483424 |              |
 live_tuples                       |   483424 |              |
 dead_tuples                       |     2677 |              |

Для более старых версий (Пост-ГРЭС 9.2 или более старый):

WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'public.tbl'::regclass AS tbl      -- provide table name as string
   FROM   public.tbl t                       -- provide table name as name
   ), y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS metric
     , unnest(val)                 AS "bytes/ct"
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;

Тот же результат.

Q1: anything inefficient?

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

integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

Это сохраняет между 8 и 18 байтами за строку. Я называю его "столбцом Тетрисом".Подробнее:

Также рассмотрите:

103
24.01.2020, 22:45

Приближение размера строки, включая содержание TOAST'ed, легко получить путем запросов продолжительности ТЕКСТОВОГО представления всей строки:

SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value;

Это - близкое приближение к числу байтов, которые будут получены клиентские при выполнении:

SELECT * FROM tablename WHERE primary_key=:value;

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

Та же техника могла быть применена для определения местоположения N "самые большие в тексте" строки tablename:

SELECT primary_key, octet_length(t.*::text) FROM tablename AS t
   ORDER BY 2 DESC LIMIT :N;
38
24.01.2020, 22:45

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

Вы говорите, что текстовые поля могут заниматься несколькими k. Строка не может пробежаться через 8k в оперативной памяти, и вероятно, что Ваши более крупные текстовые поля были ПОДЖАРЕНЫ или перемещены из оперативной памяти в расширенное устройство хранения данных в отдельных файлах. Это делает Вашу оперативную память быстрее (таким образом, избранный идентификатор на самом деле быстрее, потому что меньше дисковых страниц к доступу), но выбор * становится медленнее, потому что существует более случайный ввод-вывод.

Если Ваши размеры строки итогов - все еще все хорошо под 8k, Вы могли бы попытаться изменить настройки устройства хранения данных. Я, однако, предупредил бы, что можно добраться, плохие вещи происходят при вставке атрибута увеличенного размера в оперативную память так лучше всего для не касания этого, если Вы не имеете к и если Вы делаете, установите соответствующие пределы через проверочные ограничения. Таким образом, транспортировка маловероятна единственная вещь. Это может сопоставлять многих, много полей, которые требуют случайных чтений. Большие количества случайных чтений могут также вызвать неудачные обращения в кэш, и большие объемы требуемой памяти могут потребовать, чтобы вещи были осуществлены на номерах диска и больших количествах широких строк, если соединение присутствует (и существует тот, если ТОСТ включен), может потребовать более дорогостоящих шаблонов соединения, и т.д.

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

14
24.01.2020, 22:45

Теги

Похожие вопросы