官方wiki:
[url=https://wiki.postgresql.org/wiki/Using_EXPLAIN]https://wiki.postgresql.org/wiki/Using_EXPLAIN[/url]
[url=https://wiki.postgresql.org/wiki/Performance_Optimization]https://wiki.postgresql.org/wiki/Performance_Optimization[/url]
[b](1)通过统计信息pg_catalog查看当前运行状况[/b]
①数据库大小
各数据库大小:
②表大小
前20的大表(包含index和toast):
各表大小:
各表数据行数:
③当前链接数
④整体缓存命中率(不低于99%)
⑤整体索引缓存命中率(不低于99%)
⑥IO次数/缓存命中率
⑦频繁被访问的表
⑧索引的使用(「idx_scan」「idx_tup_read」「inx_tup_fetch」为0的索引就没有必要)
index的详细信息:
⑨清除所有统计信息
[b](2)配置信息postgresql.conf[/b]
①连接
max_connections 最大链接数
②内存
shared_buffers 不要少于物理内存的1/4
work_mem 每个进程可以使用的工作内存空间,数据库的排序操作和哈希表使用的内存缓冲区的大小,如何work_mem指定的内存被耗尽,数据库将使用磁盘文件进行完成操作,速度会慢很多。
maintenance_work_mem VACUUM或者REINDEX处理可以使用的内存空间。
③WAL
max_fsm_pages FSM最大页数
wal_sync_method 向磁盘强制更新 WAL 数据的方法
checkpoint_segments 定量,写入大小。一个是16MB,默认是3,所以就是48MB。到了这个量就会调用写入处理。
checkpoint_timeout 定时,写入间隔。默认是5min(五分种)。过了5分钟就调入写入处理。
④查询
effective_cache_size 最大缓存
random_page_cost
⑤日志
log_min_duration_statement = 3s 会将执行时间超过3秒的SQL文输出到PostgreSQL的Log
⑥垃圾清理
autovacuum = on
***具体数据应该设置成多少,应该根据服务器的配置多次尝试后设置。
[b](3)其他[/b]
①VACUUM
postgresql中用于维护数据库磁盘空间的工具是VACUUM,其重要的作用是删除那些已经标示为删除的数据并释放空间。
②vacuumdb
vacuumdb是VACUUM 的封装,用户切换到postgres用户,在postgres\bin下执行:
[quote]./vacuumdb -a -z[/quote]
常用参数
-a/--all vacuum所有的数据库
-d dbname 只vacuum dbname这个数据库
-f/--full 执行full的vacuum
-t table 只vacuum table这个数据表
-z/--analyze 生成用于查询优化器的内部统计数据
PostgreSQL的更新处理是先DELETE后INSERT,所以大量UPDATE会生成垃圾,所以每天执行一回。
[quote]vacuumdb -f -a -z [/quote]
③SQL语句执行计划分析
[quote]EXPLAIN ANALYZE select * from tbl;[/quote]
④重建索引REINDEX
[quote]psql -d dbname -c "REINDEX TABLE tbl;" username[/quote]
⑤定期做Dump和Restore
参考:[url=http://rensanning.iteye.com/blog/1939038]PostgreSQL常用命令[/url]
PostgreSQL Benchmarking Toolset : [url=https://github.com/gregs1104/pgbench-tools]pgbench-tools[/url]
PostgreSQL configuration wizard : [url=https://github.com/gregs1104/pgtune]pgtune[/url]
[url=https://wiki.postgresql.org/wiki/Using_EXPLAIN]https://wiki.postgresql.org/wiki/Using_EXPLAIN[/url]
[url=https://wiki.postgresql.org/wiki/Performance_Optimization]https://wiki.postgresql.org/wiki/Performance_Optimization[/url]
[b](1)通过统计信息pg_catalog查看当前运行状况[/b]
①数据库大小
SELECT pg_size_pretty(pg_database_size('data_base_name'));
各数据库大小:
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC
LIMIT 20
②表大小
SELECT pg_size_pretty(pg_relation_size('table_name'));
前20的大表(包含index和toast):
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
各表大小:
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a;
各表数据行数:
SELECT
relname as table_name,
reltuples as rowcounts
FROM
pg_class
WHERE
relkind = 'r'
ORDER BY
rowCounts desc;
③当前链接数
SELECT count(*) FROM pg_stat_activity;
④整体缓存命中率(不低于99%)
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;
⑤整体索引缓存命中率(不低于99%)
SELECT
sum(idx_blks_read) as idx_read,
sum(idx_blks_hit) as idx_hit,
(sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
pg_statio_user_indexes;
⑥IO次数/缓存命中率
SELECT
*,
(heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) as ratio
FROM
pg_statio_all_tables
WHERE
heap_blks_hit >= 1 AND
schemaname = 'public'
ORDER BY
ratio;
⑦频繁被访问的表
SELECT
relname,
coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0)+coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)+coalesce(n_tup_del,0) as total,
coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0) as select,
coalesce(n_tup_ins,0) as insert,
coalesce(n_tup_upd,0) as update,
coalesce(n_tup_del,0) as delete
FROM
pg_stat_user_tables
ORDER BY
total desc;
⑧索引的使用(「idx_scan」「idx_tup_read」「inx_tup_fetch」为0的索引就没有必要)
SELECT * FROM pg_stat_user_indexes;
index的详细信息:
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;
⑨清除所有统计信息
SELECT pg_stat_reset();
[b](2)配置信息postgresql.conf[/b]
①连接
max_connections 最大链接数
②内存
shared_buffers 不要少于物理内存的1/4
work_mem 每个进程可以使用的工作内存空间,数据库的排序操作和哈希表使用的内存缓冲区的大小,如何work_mem指定的内存被耗尽,数据库将使用磁盘文件进行完成操作,速度会慢很多。
maintenance_work_mem VACUUM或者REINDEX处理可以使用的内存空间。
③WAL
max_fsm_pages FSM最大页数
wal_sync_method 向磁盘强制更新 WAL 数据的方法
checkpoint_segments 定量,写入大小。一个是16MB,默认是3,所以就是48MB。到了这个量就会调用写入处理。
checkpoint_timeout 定时,写入间隔。默认是5min(五分种)。过了5分钟就调入写入处理。
④查询
effective_cache_size 最大缓存
random_page_cost
⑤日志
log_min_duration_statement = 3s 会将执行时间超过3秒的SQL文输出到PostgreSQL的Log
⑥垃圾清理
autovacuum = on
***具体数据应该设置成多少,应该根据服务器的配置多次尝试后设置。
[b](3)其他[/b]
①VACUUM
postgresql中用于维护数据库磁盘空间的工具是VACUUM,其重要的作用是删除那些已经标示为删除的数据并释放空间。
②vacuumdb
vacuumdb是VACUUM 的封装,用户切换到postgres用户,在postgres\bin下执行:
[quote]./vacuumdb -a -z[/quote]
常用参数
-a/--all vacuum所有的数据库
-d dbname 只vacuum dbname这个数据库
-f/--full 执行full的vacuum
-t table 只vacuum table这个数据表
-z/--analyze 生成用于查询优化器的内部统计数据
PostgreSQL的更新处理是先DELETE后INSERT,所以大量UPDATE会生成垃圾,所以每天执行一回。
[quote]vacuumdb -f -a -z [/quote]
③SQL语句执行计划分析
[quote]EXPLAIN ANALYZE select * from tbl;[/quote]
④重建索引REINDEX
[quote]psql -d dbname -c "REINDEX TABLE tbl;" username[/quote]
⑤定期做Dump和Restore
参考:[url=http://rensanning.iteye.com/blog/1939038]PostgreSQL常用命令[/url]
PostgreSQL Benchmarking Toolset : [url=https://github.com/gregs1104/pgbench-tools]pgbench-tools[/url]
PostgreSQL configuration wizard : [url=https://github.com/gregs1104/pgtune]pgtune[/url]