My application is very database intensive so I'm trying to reduce the load on the database. I am using PostgreSQL
as rdbms and python is the programming language. To reduce the load I am already using a caching mechanism in the application. The caching type I used is a server cache, browser cache. Currently I'm tuning the PostgreSQL query cache to get it in line with the characteristics of queries being run on the server.
我的应用程序是数据库密集型的,所以我试图减少数据库的负载。我使用PostgreSQL作为rdbms, python是编程语言。为了减少负载,我已经在应用程序中使用了缓存机制。我使用的缓存类型是服务器缓存,浏览器缓存。目前,我正在调优PostgreSQL查询缓存,使其符合正在服务器上运行的查询的特征。
Questions:
问题:
- Is it possible to fine tune query cache on a per database level?
- 是否可以对每个数据库级别的查询缓存进行微调?
- Is it possible to fine tune query cache on a per table basis?
- 是否可以根据每个表对查询缓存进行微调?
- please provide tutorial to learn query cache in PostgreSQL.
- 请提供在PostgreSQL中学习查询缓存的教程。
1 个解决方案
#1
-1
I developed a system for caching results, to speed-up results queried from a web-based solution. I reproduced below in essence what it did:
我开发了一个缓存结果的系统,以加速从基于web的解决方案查询的结果。我从本质上复制了它所做的:
The following are the generic caching handling tables and functions.
下面是通用缓存处理表和函数。
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;
The following is an example of how to use the tables and functions above, for a given view named my_view
with a field key
to be selected within a range of integer values. You would replace all the following with your particular needs, and replace my_view
with either a table, a view, or a function. Also replace the filtering parameters as required.
下面是一个如何使用上面的表和函数的示例,用于一个名为my_view的给定视图,该视图的字段键将在一个整数值范围内选择。您将用您的特定需求替换所有以下内容,并将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;
Example: Retrieve rows from 1 to 2000 from cached my_view results filtered by key BETWEEN 30044 AND 10610679
. Run a first time and the results of the query will be cached into table cached_results_my_view
, and the first 2000 records will be returned. Run it again shortly after and the results will be retrieved from the table cached_results_my_view
directly without executing the query.
示例:从缓存的my_view结果中检索1到2000行,这些结果由30044到106106679之间的key过滤。第一次运行,查询的结果将缓存到表cached_results_my_view中,并返回第一个2000条记录。稍后再运行它,结果将直接从表cached_results_my_view中检索,而不需要执行查询。
SELECT * FROM f_get_my_view_cached_rows(30044, 10610679, 1, 2000);
#1
-1
I developed a system for caching results, to speed-up results queried from a web-based solution. I reproduced below in essence what it did:
我开发了一个缓存结果的系统,以加速从基于web的解决方案查询的结果。我从本质上复制了它所做的:
The following are the generic caching handling tables and functions.
下面是通用缓存处理表和函数。
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;
The following is an example of how to use the tables and functions above, for a given view named my_view
with a field key
to be selected within a range of integer values. You would replace all the following with your particular needs, and replace my_view
with either a table, a view, or a function. Also replace the filtering parameters as required.
下面是一个如何使用上面的表和函数的示例,用于一个名为my_view的给定视图,该视图的字段键将在一个整数值范围内选择。您将用您的特定需求替换所有以下内容,并将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;
Example: Retrieve rows from 1 to 2000 from cached my_view results filtered by key BETWEEN 30044 AND 10610679
. Run a first time and the results of the query will be cached into table cached_results_my_view
, and the first 2000 records will be returned. Run it again shortly after and the results will be retrieved from the table cached_results_my_view
directly without executing the query.
示例:从缓存的my_view结果中检索1到2000行,这些结果由30044到106106679之间的key过滤。第一次运行,查询的结果将缓存到表cached_results_my_view中,并返回第一个2000条记录。稍后再运行它,结果将直接从表cached_results_my_view中检索,而不需要执行查询。
SELECT * FROM f_get_my_view_cached_rows(30044, 10610679, 1, 2000);