From a50666516d1bfec103f8cded1260ffce2a3b7431 Mon Sep 17 00:00:00 2001 From: =?utf8?q?C=C3=A9dric=20Villemain?= Date: Thu, 15 Sep 2016 10:16:14 +0200 Subject: [PATCH] preparing v1.2 --- AUTHORS | 4 +- COPYRIGHT | 2 +- ChangeLog | 6 + Makefile | 66 +--------- README.rst => README.md | 272 +++++++++++++++++----------------------- debian/copyright | 2 +- pgfincore.sql | 25 +++- 7 files changed, 156 insertions(+), 221 deletions(-) rename README.rst => README.md (58%) diff --git a/AUTHORS b/AUTHORS index d13a667..ac0677e 100644 --- a/AUTHORS +++ b/AUTHORS @@ -2,7 +2,9 @@ pgfincore is written by: * Cédric Villemain -I take pg_relation_size code as a model, I look at the C interesting part from fincore (http://net.doit.wisc.edu/~plonka/fincore/), and I follow the great idea from http://www.kennygorman.com/wordpress/?p=246. +I take pg_relation_size code as a model, I look at the C interesting part from +fincore (http://net.doit.wisc.edu/~plonka/fincore/), and I follow the great idea +from http://www.kennygorman.com/wordpress/?p=246. In short, thank you Kenny Gorman, thank you Dave Plonka ! diff --git a/COPYRIGHT b/COPYRIGHT index 4e50d43..4e3158f 100644 --- a/COPYRIGHT +++ b/COPYRIGHT @@ -1,5 +1,5 @@ /* - * Copyright (c) 2009-2013 Cédric Villemain + * Copyright (c) 2009-2016 Cédric Villemain * All rights reserved. * * Redistribution and use in source and binary forms, with or without diff --git a/ChangeLog b/ChangeLog index 9f2ae98..30b3471 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,3 +1,9 @@ +16/09/2016 Cédric Villemain + * 1.2 - Prepared fincore syscall usage + - Added 2 columns in pgfincore() with dirty status when available + - Support PostgreSQL 9.6 + - Added a drawer function + 12/10/2013 Cédric Villemain * 1.1.2 - Fix README filename for PGXS - Update to PostgreSQL 9.3 diff --git a/Makefile b/Makefile index 8ad9c6b..4be827f 100644 --- a/Makefile +++ b/Makefile @@ -1,30 +1,21 @@ EXTENSION = pgfincore -EXTVERSION = 1.1.2 +EXTVERSION = 1.2 EXTCOMMENT = examine and manage the os buffer cache MODULES = $(EXTENSION) MODULEDIR = $(EXTENSION) -DOCS = README.rst +DOCS = README.md DATA_built = $(EXTENSION)--$(EXTVERSION).sql $(EXTENSION)--unpackaged--$(EXTVERSION).sql REGRESS = $(EXTENSION).ext EXTRA_CLEAN = $(EXTENSION).control PG_CONFIG = pg_config -BUILD_EXTENSION = $(shell $(PG_CONFIG) --version | grep -qE "8\.|9\.0" && echo no || echo yes) - -ifeq ($(BUILD_EXTENSION),no) -DATA_built = -DATA = $(EXTENSION).sql uninstall_$(EXTENSION).sql -REGRESS = $(EXTENSION) -EXTRA_CLEAN = -endif - PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) # Build some more files for extension support: -ifeq ($(BUILD_EXTENSION),yes) + # pgxs is included after variable definition and before targets, so the # PostgreSQL default target is used (all:) @@ -42,57 +33,6 @@ $(EXTENSION)--$(EXTVERSION).sql: $(EXTENSION).sql # this build extension.control from extension.control.in $(EXTENSION).control: $(EXTENSION).control.in sed 's/EXTVERSION/$(EXTVERSION)/;s/EXTENSION/$(EXTENSION)/;s/EXTCOMMENT/$(EXTCOMMENT)/' $< > $@ -endif - -# Here we override targets -# Recent PostgreSQL got a bugfix about that, here we just abuse the upstream fix in the mean-time -# FIX HERE before PostgreSQL got the backpatch and push the latest minor, can remove this part when done -ifeq ($(BUILD_EXTENSION),yes) - -install: all installcontrol installdata installdocs installscripts | installdirs -ifdef MODULES - $(INSTALL_SHLIB) $(addsuffix $(DLSUFFIX), $(MODULES)) '$(DESTDIR)$(pkglibdir)/' -endif # MODULES - -installcontrol: $(addsuffix .control, $(EXTENSION)) -ifneq (,$(EXTENSION)) - $(INSTALL_DATA) $^ '$(DESTDIR)$(datadir)/extension/' -endif - -installdata: $(DATA) $(DATA_built) -ifneq (,$(DATA)$(DATA_built)) - $(INSTALL_DATA) $^ '$(DESTDIR)$(datadir)/$(datamoduledir)/' -endif - -installdocs: $(DOCS) -ifdef DOCS -ifdef docdir - $(INSTALL_DATA) $^ '$(DESTDIR)$(docdir)/$(docmoduledir)/' -endif # docdir -endif # DOCS - -installscripts: $(SCRIPTS) $(SCRIPTS_built) -ifdef SCRIPTS - $(INSTALL_SCRIPT) $^ '$(DESTDIR)$(bindir)/' -endif # SCRIPTS - -installdirs: -ifneq (,$(EXTENSION)) - $(MKDIR_P) '$(DESTDIR)$(datadir)/extension' -endif -ifneq (,$(DATA)$(DATA_built)) - $(MKDIR_P) '$(DESTDIR)$(datadir)/$(datamoduledir)' -endif -ifneq (,$(MODULES)) - $(MKDIR_P) '$(DESTDIR)$(pkglibdir)' -endif -ifdef DOCS -ifdef docdir - $(MKDIR_P) '$(DESTDIR)$(docdir)/$(docmoduledir)' -endif # docdir -endif # DOCS - -endif dist: git archive --prefix=$(EXTENSION)-$(EXTVERSION)/ -o ../$(EXTENSION)_$(EXTVERSION).orig.tar.gz HEAD diff --git a/README.rst b/README.md similarity index 58% rename from README.rst rename to README.md index b3c180c..e72215e 100644 --- a/README.rst +++ b/README.md @@ -1,6 +1,4 @@ -=========== - PgFincore -=========== +# PgFincore -------------------------------------------------------------- A set of functions to manage pages in memory from PostgreSQL @@ -9,8 +7,7 @@ A set of functions to handle low-level management of relations using mincore to explore cache memory. -DESCRIPTION -=========== +## DESCRIPTION With PostgreSQL, each Table or Index is splitted in segments of (usually) 1GB, and each segment is splitted in pages in memory then in blocks for the @@ -30,42 +27,40 @@ respectively. Each functions are call with at least a table name or an index name (or oid) as a parameter and walk each segment of the relation. -DOWNLOAD -======== +## DOWNLOAD -You can grab the latest code with git:: +You can grab the latest code with git: - git clone git://git.postgresql.org/git/pgfincore.git - or - git://github.com/klando/pgfincore.git + git clone git://git.postgresql.org/git/pgfincore.git + or + git://github.com/klando/pgfincore.git And the project is on pgfoundry : http://pgfoundry.org/projects/pgfincore -INSTALL -======= +## INSTALL -From source code:: +From source code: - make clean - make - su - make install + make clean + make + su + make install -For PostgreSQL >= 9.1, log in your database and:: +For PostgreSQL >= 9.1, log in your database and: - mydb=# CREATE EXTENSION pgfincore; + mydb=# CREATE EXTENSION pgfincore; For other release, create the functions from the sql script (it should be in -your contrib directory):: +your contrib directory): - psql mydb -f pgfincore.sql + psql mydb -f pgfincore.sql -PgFincore is also shipped with Debian scripts to build your own package:: +PgFincore is also shipped with Debian scripts to build your own package: - aptitude install debhelper postgresql-server-dev-all postgresql-server-dev-9.1 - # or postgresql-server-dev-8.4|postgresql-server-dev-9.0 - make deb - dpkg -i ../postgresql-9.1-pgfincore_1.1.1-1_amd64.deb + aptitude install debhelper postgresql-server-dev-all postgresql-server-dev-9.1 + # or postgresql-server-dev-8.4|postgresql-server-dev-9.0 + make deb + dpkg -i ../postgresql-9.1-pgfincore_1.1.1-1_amd64.deb PgFincore is packaged for *RPM* at http://yum.postgresql.org/ PgFincore is packaged for *debian* at http://pgapt.debian.net/ @@ -73,49 +68,45 @@ PgFincore is packaged for *debian* at http://pgapt.debian.net/ -EXAMPLES -======== +## EXAMPLES Here are some examples of usage. If you want more details go to Documentation_ -Get current state of a relation -------------------------------- +### Get current state of a relation -May be useful:: +May be useful: - cedric=# select * from pgfincore('pgbench_accounts'); - relpath | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit | pages_dirty | group_dirty - --------------------+---------+--------------+--------------+-----------+-----------+---------------+---------+-------------+------------- - base/11874/16447 | 0 | 4096 | 262144 | 262144 | 1 | 81016 | | 0 | 0 - base/11874/16447.1 | 1 | 4096 | 65726 | 65726 | 1 | 81016 | | 0 | 0 - (2 rows) - - Time: 31.563 ms + cedric=# select * from pgfincore('pgbench_accounts'); + relpath | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit | pages_dirty | group_dirty + --------------------+---------+--------------+--------------+-----------+-----------+---------------+---------+-------------+------------- + base/11874/16447 | 0 | 4096 | 262144 | 262144 | 1 | 81016 | | 0 | 0 + base/11874/16447.1 | 1 | 4096 | 65726 | 65726 | 1 | 81016 | | 0 | 0 + (2 rows) + + Time: 31.563 ms -Load a table or an index in OS Page Buffer ------------------------------------------- +### Load a table or an index in OS Page Buffer You may want to try to keep a table or an index into the OS Page Cache, or preload a table before your well know big query is executed (reducing the query time). -To do so, just execute the following query:: +To do so, just execute the following query: - cedric=# select * from pgfadvise_willneed('pgbench_accounts'); - relpath | os_page_size | rel_os_pages | os_pages_free - --------------------+--------------+--------------+--------------- - base/11874/16447 | 4096 | 262144 | 169138 - base/11874/16447.1 | 4096 | 65726 | 103352 - (2 rows) - - Time: 4462,936 ms + cedric=# select * from pgfadvise_willneed('pgbench_accounts'); + relpath | os_page_size | rel_os_pages | os_pages_free + --------------------+--------------+--------------+--------------- + base/11874/16447 | 4096 | 262144 | 169138 + base/11874/16447.1 | 4096 | 65726 | 103352 + (2 rows) + + Time: 4462,936 ms * The column *os_page_size* report that page size is 4KB. * The column *rel_os_pages* is the number of pages of the specified file. * The column *os_pages_free* is the number of free pages in memory (for caching). -Snapshot and Restore the OS Page Buffer state of a table or an index (or more) ------------------------------------------------------------------------------- +### Snapshot and Restore the OS Page Buffer state of a table or an index (or more) You may want to restore a table or an index into the OS Page Cache as it was while you did the snapshot. For example if you have to reboot your server, then @@ -123,33 +114,30 @@ when PostgreSQL start up the first queries might be slower because neither PostgreSQL or the OS have pages in their respective cache about the relations involved in those first queries. -Executing a snapshot and a restore is very simple:: - - -- Snapshot - cedric=# create table pgfincore_snapshot as - cedric-# select 'pgbench_accounts'::text as relname,*,now() as date_snapshot - cedric-# from pgfincore('pgbench_accounts',true); - - -- Restore - cedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, true, - (select databit from pgfincore_snapshot - where relname='pgbench_accounts' and segment = 0)); - relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded - ------------------+--------------+---------------+--------------+---------------- - base/11874/16447 | 4096 | 80867 | 262144 | 0 - (1 row) - - Time: 35.349 ms +Executing a snapshot and a restore is very simple: + + -- Snapshot + cedric=# create table pgfincore_snapshot as + cedric-# select 'pgbench_accounts'::text as relname,*,now() as date_snapshot + cedric-# from pgfincore('pgbench_accounts',true); + + -- Restore + cedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, true, + (select databit from pgfincore_snapshot + where relname='pgbench_accounts' and segment = 0)); + relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded + ------------------+--------------+---------------+--------------+---------------- + base/11874/16447 | 4096 | 80867 | 262144 | 0 + (1 row) + + Time: 35.349 ms * The column *pages_loaded* report how many pages have been read to memory (they may have already been in memoy) * The column *pages_unloaded* report how many pages have been removed from memory (they may not have already been in memoy); -SYNOPSIS -======== - -:: +## SYNOPSIS pgsysconf(OUT os_page_size bigint, OUT os_pages_free bigint, OUT os_total_pages bigint) @@ -227,120 +215,100 @@ SYNOPSIS OUT group_dirty bigint) RETURNS setof record -DOCUMENTATION -============= +## DOCUMENTATION -pgsysconf ---------- +### pgsysconf This function output size of OS blocks, number of free page in the OS Page Buffer. -:: + cedric=# select * from pgsysconf(); + os_page_size | os_pages_free | os_total_pages + --------------+---------------+---------------- + 4096 | 80431 | 4094174 - cedric=# select * from pgsysconf(); - os_page_size | os_pages_free | os_total_pages - --------------+---------------+---------------- - 4096 | 80431 | 4094174 - -pgsysconf_pretty ----------------- +### pgsysconf_pretty The same as above, but with pretty output. -:: - - cedric=# select * from pgsysconf_pretty(); - os_page_size | os_pages_free | os_total_pages - --------------+---------------+---------------- + cedric=# select * from pgsysconf_pretty(); + os_page_size | os_pages_free | os_total_pages + --------------+---------------+---------------- 4096 bytes | 314 MB | 16 GB -pgfadvise_WILLNEED ------------------- +### pgfadvise_WILLNEED This function set *WILLNEED* flag on the current relation. It means that the Operating Sytem will try to load as much pages as possible of the relation. Main idea is to preload files on server startup, perhaps using cache hit/miss ratio or most required relations/indexes. -:: + cedric=# select * from pgfadvise_willneed('pgbench_accounts'); + relpath | os_page_size | rel_os_pages | os_pages_free + --------------------+--------------+--------------+--------------- + base/11874/16447 | 4096 | 262144 | 80650 + base/11874/16447.1 | 4096 | 65726 | 80650 - cedric=# select * from pgfadvise_willneed('pgbench_accounts'); - relpath | os_page_size | rel_os_pages | os_pages_free - --------------------+--------------+--------------+--------------- - base/11874/16447 | 4096 | 262144 | 80650 - base/11874/16447.1 | 4096 | 65726 | 80650 - -pgfadvise_DONTNEED ------------------- +### pgfadvise_DONTNEED This function set *DONTNEED* flag on the current relation. It means that the Operating System will first unload pages of the file if it need to free some memory. Main idea is to unload files when they are not usefull anymore (instead of perhaps more interesting pages) -:: - - cedric=# select * from pgfadvise_dontneed('pgbench_accounts'); - relpath | os_page_size | rel_os_pages | os_pages_free - --------------------+--------------+--------------+--------------- - base/11874/16447 | 4096 | 262144 | 342071 - base/11874/16447.1 | 4096 | 65726 | 408103 + cedric=# select * from pgfadvise_dontneed('pgbench_accounts'); + relpath | os_page_size | rel_os_pages | os_pages_free + --------------------+--------------+--------------+--------------- + base/11874/16447 | 4096 | 262144 | 342071 + base/11874/16447.1 | 4096 | 65726 | 408103 -pgfadvise_NORMAL ----------------- +### pgfadvise_NORMAL This function set *NORMAL* flag on the current relation. -pgfadvise_SEQUENTIAL --------------------- +### pgfadvise_SEQUENTIAL This function set *SEQUENTIAL* flag on the current relation. -pgfadvise_RANDOM ----------------- +### pgfadvise_RANDOM This function set *RANDOM* flag on the current relation. -pgfadvise_loader ----------------- +### pgfadvise_loader This function allow to interact directly with the Page Cache. It can be used to load and/or unload page from memory based on a varbit representing the map of the pages to load/unload accordingly. -Work with relation pgbench_accounts, segment 0, arbitrary varbit map:: - - -- Loading and Unloading - cedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, true, B'111000'); - relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded - ------------------+--------------+---------------+--------------+---------------- - base/11874/16447 | 4096 | 408376 | 3 | 3 - - -- Loading - cedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, false, B'111000'); - relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded - ------------------+--------------+---------------+--------------+---------------- - base/11874/16447 | 4096 | 408370 | 3 | 0 - - -- Unloading - cedric=# select * from pgfadvise_loader('pgbench_accounts', 0, false, true, B'111000'); +Work with relation pgbench_accounts, segment 0, arbitrary varbit map: + + -- Loading and Unloading + cedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, true, B'111000'); + relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded + ------------------+--------------+---------------+--------------+---------------- + base/11874/16447 | 4096 | 408376 | 3 | 3 + + -- Loading + cedric=# select * from pgfadvise_loader('pgbench_accounts', 0, true, false, B'111000'); + relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded + ------------------+--------------+---------------+--------------+---------------- + base/11874/16447 | 4096 | 408370 | 3 | 0 + + -- Unloading + cedric=# select * from pgfadvise_loader('pgbench_accounts', 0, false, true, B'111000'); relpath | os_page_size | os_pages_free | pages_loaded | pages_unloaded - ------------------+--------------+---------------+--------------+---------------- - base/11874/16447 | 4096 | 408370 | 0 | 3 + ------------------+--------------+---------------+--------------+---------------- + base/11874/16447 | 4096 | 408370 | 0 | 3 -pgfincore ---------- +### pgfincore This function provide information about the file system cache (page cache). -:: - - cedric=# select * from pgfincore('pgbench_accounts'); - relpath | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit | pages_dirty | group_dirty - --------------------+---------+--------------+--------------+-----------+-----------+---------------+---------+-------------+------------- - base/11874/16447 | 0 | 4096 | 262144 | 3 | 1 | 408444 | | 0 | 0 - base/11874/16447.1 | 1 | 4096 | 65726 | 0 | 0 | 408444 | | 0 | 0 + cedric=# select * from pgfincore('pgbench_accounts'); + relpath | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit | pages_dirty | group_dirty + --------------------+---------+--------------+--------------+-----------+-----------+---------------+---------+-------------+------------- + base/11874/16447 | 0 | 4096 | 262144 | 3 | 1 | 408444 | | 0 | 0 + base/11874/16447.1 | 1 | 4096 | 65726 | 0 | 0 | 408444 | | 0 | 0 For the specified relation it returns: @@ -357,23 +325,20 @@ For the specified relation it returns: * pages_dirty : if HAVE_FINCORE constant is define and the platorm provides the relevant information, like pages_mem but for dirtied pages * group_dirty : if HAVE_FINCORE constant is define and the platorm provides the relevant information, like group_mem but for dirtied pages -DEBUG -===== +## DEBUG You can debug the PgFincore with the following error level: *DEBUG1* and *DEBUG5*. -For example:: +For example: - set client_min_messages TO debug1; -- debug5 is only usefull to trace each block + set client_min_messages TO debug1; -- debug5 is only usefull to trace each block -REQUIREMENTS -============ +## REQUIREMENTS * PgFincore needs mincore() or fincore() and POSIX_FADVISE -LIMITATIONS -=========== +## LIMITATIONS * PgFincore has a limited mode when POSIX_FADVISE is not provided by the platform. @@ -381,8 +346,7 @@ LIMITATIONS * PgFincore does not work on windows. -SEE ALSO -======== +## SEE ALSO 2ndQuadrant, PostgreSQL Expertise, developement, training and 24x7 support: diff --git a/debian/copyright b/debian/copyright index b19817b..09802d6 100644 --- a/debian/copyright +++ b/debian/copyright @@ -3,7 +3,7 @@ Upstream-Name: pgfincore Source: http://git.postgresql.org/gitweb/?p=pgfincore.git Files: * -Copyright: 2009-2013 Cédric Villemain +Copyright: 2009-2016 Cédric Villemain License: BSD-3-Clause Files: debian/* diff --git a/pgfincore.sql b/pgfincore.sql index f7c4f55..3e4643c 100644 --- a/pgfincore.sql +++ b/pgfincore.sql @@ -1,4 +1,3 @@ - -- -- SYSCONF -- @@ -10,6 +9,15 @@ RETURNS record AS '$libdir/pgfincore' LANGUAGE C; +COMMENT ON FUNCTION pgsysconf() +IS 'Get system configuration information at run time: + - os_page_size is _SC_PAGESIZE + - os_pages_free is _SC_AVPHYS_PAGES + - os_total_pages is _SC_PHYS_PAGES + +man 3 sysconf for details'; + + CREATE OR REPLACE FUNCTION pgsysconf_pretty(OUT os_page_size text, OUT os_pages_free text, @@ -22,6 +30,9 @@ select pg_size_pretty(os_page_size) as os_page_size, from pgsysconf()' LANGUAGE SQL; +COMMENT ON FUNCTION pgsysconf_pretty() +IS 'Pgsysconf() with human readable output'; + -- -- PGFADVISE -- @@ -35,6 +46,9 @@ RETURNS setof record AS '$libdir/pgfincore' LANGUAGE C; +COMMENT ON FUNCTION pgfadvise(regclass, text, int) +IS 'Predeclare an access pattern for file data'; + CREATE OR REPLACE FUNCTION pgfadvise_willneed(IN regclass, OUT relpath text, @@ -99,6 +113,10 @@ RETURNS setof record AS '$libdir/pgfincore' LANGUAGE C; +COMMENT ON FUNCTION pgfadvise_loader(regclass, text, int, bool, bool, varbit) +IS 'Restore cache from the snapshot, options to load/unload each block to/from cache'; + + CREATE OR REPLACE FUNCTION pgfadvise_loader(IN regclass, IN int, IN bool, IN bool, IN varbit, OUT relpath text, @@ -129,6 +147,9 @@ RETURNS setof record AS '$libdir/pgfincore' LANGUAGE C; +COMMENT ON FUNCTION pgfincore(regclass, text, bool) +IS 'Utility to inspect and get a snapshot of the system cache'; + CREATE OR REPLACE FUNCTION pgfincore(IN regclass, IN bool, OUT relpath text, @@ -168,3 +189,5 @@ RETURNS cstring AS '$libdir/pgfincore' LANGUAGE C; +COMMENT ON FUNCTION pgfincore_drawer(varbit) +IS 'A naive drawing function to visualize page cache per object'; -- 2.39.5