Включить кеш запросов в postgreSQL для повышения производительности

Ответ 1

Настройка PostgreSQL - это гораздо больше, чем просто настройка кэшей. На самом деле первичные вещи высокого уровня - это "общие буферы" (представьте себе, что это основной кэш данных и индексов) и work_mem.

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

"Work_mem" используется для сортировки результатов запроса в памяти, и вам не нужно прибегать к записи на диск. В зависимости от вашего запроса эта область может быть столь же важной, как буферный кэш, и ее легче настроить. Перед выполнением запроса, который должен выполнить более крупную сортировку, вы можете выполнить команду set, такую как "SET work_mem = '256MB';"

Как и предполагали другие, вы можете выяснить, ПОЧЕМУ запрос выполняется медленно, используя "объяснение". Я бы лично предложил изучить "путь доступа", который использует postgresql для доступа к вашим данным. Это гораздо сложнее и, честно говоря, лучше использовать ресурсы, чем просто думать о "кэшировании результатов".

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

Еще одна вещь заключается в том, что вы можете повысить производительность, написав лучшие запросы. Такие вещи, как предложения "with", могут помешать оптимизатору postgres полностью оптимизировать запросы. У самого оптимизатора также есть параметры, которые могут быть adjusted--, так что БД будет тратить больше (или меньше) времени на оптимизацию запроса до его выполнения... что может иметь значение.

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

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

В общем, вам нужно найти запросы, которые не так эффективны, как вам кажется, и выяснить, где происходит конфликт. Вероятно, это доступ к диску, однако, причина, в конечном счете, является самой важной частью... нужно ли идти на диск, чтобы провести сортировку? Является ли он внутренним выбором неверного пути для доступа к данным, так что он считывает данные, которые можно было легко удалить на более ранних этапах процесса запроса... Я был сертифицированным администратором Oracle более 20 лет, и PostgreSQL определенно отличается, однако, многие из тех же методов используются, когда дело доходит до диагностики проблем производительности запроса. Хотя вы на самом деле не можете предоставить подсказки, вы все равно можете переписать запросы или настроить определенные параметры, чтобы получить лучшую производительность... в общем, я обнаружил, что postgresql легче настраивать в долгосрочной перспективе. Если вы можете предоставить некоторые подробности, например, запрос и объяснить информацию, я был бы рад дать вам конкретные рекомендации. К сожалению, "настройка кеша", скорее всего, сама по себе обеспечит вам желаемую скорость.

Ответ 2

Я разработал систему для кэширования результатов, чтобы ускорить результаты, полученные от веб-решения. Я воспроизвел ниже по существу то, что он сделал:

Ниже приведены общие таблицы и функции обработки кеширования.

CREATE TABLE cached_results_headers (
  cache_id serial NOT NULL PRIMARY KEY,
  date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_access timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  relid regclass NOT NULL,
  query text NOT NULL,
  rows int NOT NULL DEFAULT 0
  );
CREATE INDEX ON cached_results_headers (relid, md5(query));

CREATE TABLE cached_results (
  cache_id int NOT NULL,
  row_no int NOT NULL  
  );

CREATE OR REPLACE FUNCTION f_get_cached_results_header (p_cache_table text, p_source_relation regclass, p_query text, p_max_lifetime interval, p_clear_old_data interval) RETURNS cached_results_headers AS $BODY$
DECLARE
  _cache_id int;
  _rows int;
BEGIN
  IF p_clear_old_data IS NOT NULL THEN
    DELETE FROM cached_results_headers WHERE date < CURRENT_TIMESTAMP - p_clear_old_data;
  END IF;

  _cache_id := cache_id FROM cached_results_headers WHERE relid = p_source_relation AND md5(query) = md5(p_query) AND query = p_query AND date > CURRENT_TIMESTAMP - p_max_lifetime;
  IF _cache_id IS NULL THEN
    INSERT INTO cached_results_headers (relid, query) VALUES (p_source_relation, p_query) RETURNING cache_id INTO _cache_id;
    EXECUTE $$ INSERT INTO $$||p_cache_table||$$ SELECT $1, row_number() OVER (), r.r FROM ($$||p_query||$$) r $$ USING _cache_id;
    GET DIAGNOSTICS _rows = ROW_COUNT;
    UPDATE cached_results_headers SET rows = _rows WHERE cache_id = _cache_id;
  ELSE
    UPDATE cached_results_headers SET last_access = CURRENT_TIMESTAMP;
  END IF;
  RETURN (SELECT h FROM cached_results_headers h WHERE cache_id = _cache_id);
END;
$BODY$ LANGUAGE PLPGSQL SECURITY DEFINER;

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

CREATE VIEW my_view AS SELECT ...; -- create a query with your data, with one of the integer columns in the result as "key" to filter by

CREATE TABLE cached_results_my_view (
  row my_view NOT NULL,
  PRIMARY KEY (cache_id, row_no),
  FOREIGN KEY (cache_id) REFERENCES cached_results_headers ON DELETE CASCADE
  ) INHERITS (cached_results);

CREATE OR REPLACE FUNCTION f_get_my_view_cached_rows (p_filter1 int, p_filter2 int, p_row_from int, p_row_to int) RETURNS SETOF my_view AS $BODY$
DECLARE
  _cache_id int;
BEGIN
  _cache_id := cache_id 
    FROM f_get_cached_results_header('cached_results_my_view', 'my_view'::regclass,
                                     'SELECT r FROM my_view r WHERE key BETWEEN '||p_filter1::text||' AND '||p_filter2::text||' ORDER BY key',
                                     '15 minutes'::interval, '1 day'::interval); -- cache for 15 minutes max since creation time; delete all cached data older than 1 day old

  RETURN QUERY
    SELECT (row).*
    FROM cached_results_my_view
    WHERE cache_id = _cache_id AND row_no BETWEEN p_row_from AND p_row_to
    ORDER BY row_no;
END;
$BODY$ LANGUAGE PLPGSQL;

Пример. Извлеките строки от 1 до 2000 из кешированных результатов my_view, отфильтрованных key BETWEEN 30044 AND 10610679. Запустите первый раз, и результаты запроса будут кэшированы в таблицу cached_results_my_view, и будут возвращены первые 2000 записей. Запустите его снова вскоре, и результаты будут получены из таблицы cached_results_my_view напрямую, не выполняя запрос.

SELECT * FROM f_get_my_view_cached_rows(30044, 10610679, 1, 2000);