Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PostgreSQL using the system cache to improve operational efficiency     - Linux System Getting Started Learning: Repair fatal error openssl aes h no such file or directory (Linux)

- Formatted output printf command (Programming)

- CentOS / Linux SELinux Close (Linux)

- Bash environment is automatically install and initialize oh-my-zsh & autojump zsh (Linux)

- NIC configuration parameters under Linux (Linux)

- TOAD connect DB2 error SQL1460N solve (Database)

- Ubuntu 14.04 after the restart the default maximum screen brightness solutions (Linux)

- Linux crontab command format example (Linux)

- C # Future: Method Contract (Programming)

- Use Mop monitor stock prices at the Linux command line (Linux)

- Openfire achieve load balancing cluster by Nginx (Server)

- Linux Kernel 4.2 Installation Instructions (Linux)

- To read the Linux ext3 / ext4 format partitions under Windows system software (Linux)

- Redis Design and Implementation study notes (Database)

- Use Aptik Battery Monitor monitoring Ubuntu laptop battery usage (Linux)

- Linux character device - user mode and kernel mode data transfer data (Linux)

- sudoers file parsing (Linux)

- CentOS 6.5 platform offline compile and install PHP5.6.6 (Server)

- Close common port to protect server security (Linux)

- Compile and install GCC 4.8.1 + GDB 7.6.1 + Eclipse in CentOS 6.4 in (Linux)

 
         
  PostgreSQL using the system cache to improve operational efficiency
     
  Add Date : 2016-11-17      
         
       
         
  Recently, the trial pgfincore, an experiment of this record. pgfincore is loaded into the database objects in advance OS cache, and accelerate operational efficiency. When there is a physical change when, pgfincore will be loaded into the cache. Mainly to improve the efficiency of the query, when there is frequent switching cache, the cache may be squeezed out.

Environment Introduction:

OS: CentOS 6.4 64bit
Database: PostgreSQL9.4
Memory: 2G
CPU: 1 Nuclear

Download and install:

In pgfoundry download pgfincore-v1.1.1.tar.gz ,, extract to the source database source under contrib. Do not download its github, there should be some bug, the latest version is 1.1.1, 1.1.2, when I found that most of the trial function can not be used.

Secondly, I have the following error when compiling:

[Postgres @ localhost pgfincore-1.1.1] $ make
/ Bin / sh: pg_config: command not found
make: pg_config: Command not found
cp pgfincore.sql pgfincore - 1.1.1.sql
cp: can not create regular file `pgfincore - 1.1.1.sql ': Permission denied
make: *** [pgfincore - 1.1.1.sql] Error 1
 
So I will modify the Makefile file, as follows:

# Contrib / pgfincore / Makefile
 
MODULE_big = pgfincore
OBJS = pgfincore.o
 
EXTENSION = pgfincore
DATA = pgfincore - 1.1.1.sql pgfincore - unpackaged - 1.1.1.sql
 
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS: = $ (shell $ (PG_CONFIG) --pgxs)
include $ (PGXS)
else
subdir = contrib / pgfincore
top_builddir = ../ ..
include $ (top_builddir) /src/Makefile.global
include $ (top_srcdir) /contrib/contrib-global.mk
 
Now proceed to make, you can:

[Postgres @ localhost pgfincore-1.1.1] $ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I . -I ../../ src / include -D_GNU_SOURCE -c -o pgfincore.o pgfincore.c
pgfincore.c: In function 'pgsysconf':
pgfincore.c: 227: warning: implicit declaration of function 'heap_form_tuple'
pgfincore.c: 227: warning: assignment makes pointer from integer without a cast
pgfincore.c: In function 'pgfadvise':
pgfincore.c: 477: warning: assignment makes pointer from integer without a cast
pgfincore.c: In function 'pgfadvise_loader':
pgfincore.c: 710: warning: assignment makes pointer from integer without a cast
pgfincore.c: In function 'pgfincore':
pgfincore.c: 1016: warning: assignment makes pointer from integer without a cast
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -shared -o pgfincore .so pgfincore.o -L ../../ src / port -L ../../ src / common -Wl, - as-needed -Wl, -rpath, '/ opt / hg3.0 / lib ', - enable-new-dtags
[Postgres @ localhost pgfincore-1.1.1] $ make install
/ Bin / mkdir -p '/opt/hg3.0/lib/postgresql'
/ Bin / mkdir -p '/opt/hg3.0/share/postgresql/extension'
/ Bin / mkdir -p '/opt/hg3.0/share/postgresql/extension'
/ Usr / bin / install -c -m 755 pgfincore.so '/opt/hg3.0/lib/postgresql/pgfincore.so'
/ Usr / bin / install -c -m 644 pgfincore.control '/opt/hg3.0/share/postgresql/extension/'
/ Usr / bin / install -c -m 644 pgfincore - 1.1.1.sql pgfincore - unpackaged - 1.1.1.sql '/opt/hg3.0/share/postgresql/extension/'
[Postgres @ localhost pgfincore-1.1.1] $
 
Experimental records:
1, the installation:

[Postgres @ localhost bin] $ ./psql
psql (9.4.5)
Type "help" for help.
 
postgres = # create extension pgfincore;
CREATE EXTENSION
postgres = # select * from pg_extension;
  extname | extowner | extnamespace | extrelocatable | extversion | extconfig |
 extcondition
----------- + ------------ + ---------- + -------------- ---- + ------------ + ----------- +
--------------
 plpgsql | 10 | 11 | f | 1.0 | |
  
 pgfincore | 10 | 2200 | t | 1.1.1 | |
  
(2 rows)
 
postgres = #

2. Preparation: Record relatively poor performance pgfincore before and after use, the use of plug-pg_stat_statments record each sql life.
 Secondly shared_buffer to 16mb, reduce its impact on the pgfincore.

postgres = # create extension pg_stat_statements;
CREATE EXTENSION
postgres = # select * from pg_extension;
      extname | extowner | extnamespace | extrelocatable | extversion | ex
tconfig | extcondition
-------------------- + ---------- + -------------- + --- + ------------ + ------------- ---
-------------- -------- +
 plpgsql | 10 | 11 | f | 1.0 |
        |
 pgfincore | 10 | 2200 | t | 1.1.1 |
        |
 pg_stat_statements | 10 | 2200 | t | 1.2 |
        |
(3 rows)

3, the establishment of table testmem, respectively, in the use and does not apply under circumstances pgfincore select, update, delete operations, and compare the execution time, where to go in order to better compare the establishment of three tables testmem1, testmem2, testmem3, respectively perform three operations were compared. And inserting the same data.

[Postgres @ localhost bin] $ ./psql
psql (9.4.5)
Type "help" for help.
 
postgres = # \ d
No relations found.
postgres = # create table testmem1 (t1 int, t2 text, t3 text, t4 bigint, t5 text, t6 text, t7 varchar, t8 char (22), t9 numeric); CREATE TABLE
postgres = # create table testmem2 (t1 int, t2 text, t3 text, t4 bigint, t5 text, t6 text, t7 varchar, t8 char (22), t9 numeric);
CREATE TABLE
postgres = # create table testmem3 (t1 int, t2 text, t3 text, t4 bigint, t5 text, t6 text, t7 varchar, t8 char (22), t9 numeric);
CREATE TABLE
postgres = # insert into testmem1 select generate_series (1,999999), 'wangshuo' || generate_series (1,999999) :: text || random () :: text, generate_series (1,999999) :: text || 'sure ', generate_series (1,999999), random () :: text, random () :: text, random () :: text,' liuyuanyuan ', generate_series (1,999999);
INSERT 0 999999
postgres = # select pg_relation_size ( 'testmem1') / 1024/1024 || 'MB';
 ? Column?
----------
 166MB
(1 row)
 
postgres = # insert into testmem2 select generate_series (1,999999), 'wangshuo' || generate_series (1,999999) :: text || random () :: text, generate_series (1,999999) :: text || 'sure ', generate_series (1,999999), random () :: text, random () :: text, random () :: text,' liuyuanyuan ', generate_series (1,999999);
INSERT 0 999999
postgres = # insert into testmem3 select generate_series (1,999999), 'wangshuo' || generate_series (1,999999) :: text || random () :: text, generate_series (1,999999) :: text || 'sure ', generate_series (1,999999), random () :: text, random () :: text, random () :: text,' liuyuanyuan ', generate_series (1,999999);
INSERT 0 999999
postgres = # select pg_relation_size ( 'testmem2') / 1024/1024 || 'MB';? column?
----------
 166MB
(1 row)
 
postgres = # select pg_relation_size ( 'testmem3') / 1024/1024 || 'MB';
 ? Column?
----------
 166MB
(1 row)
 
postgres = #

Three operating performance comparison:

postgres = # select * from testmem1 where t1 <789432 and t1> 1208;
  t1 | t2 | t3 | t4 | t5
          | T6 | t7 | t8
 | T9
+ -------- + ---- ------------------------------------ -------- + -------- + ------------
---------- ---------------------- + ---------------- + ------ + -----------------------
- + --------
  1209 | wangshuo12090.964191045146435 | 1209sure | 1209 | 0.075409890
152514 | 0.468206159770489 | 0.692714618053287 | liuyuanyuan
 | 1209
  1210 | wangshuo12100.652063825167716 | 1210sure | 1210 | 0.026932121
720165 | 0.802233531605452 | 0.706556385848671 | liuyuanyuan
 | 1210
  1211 | wangshuo12110.724962076637894 | 1211sure | 1211 | 0.732285636
477172 | 0.816857317462564 | 0.868489125277847 | liuyuanyuan
 | 1211
  1212 | wangshuo12120.0478062951005995 | 1212sure | 1212 | 0.190716865
006834 | 0.898683389648795 | 0.537546805106103 | liuyuanyuan
 | 1212
  1213 | wangshuo12130.582098880317062 | 1213sure | 1213 | 0.161297460
086644 | 0.395338968373835 | 0.684920639265329 | liuyuanyuan
 | 1213
  1214 | wangshuo12140.53120110463351 | 1214sure | 1214 | 0.253457680
810243 | 0.428548218682408 | 0.671272001229227 | liuyuanyuan
 
postgres = # update testmem2 set t7 = random () :: text where t1 <789432 and t1> 1208;
UPDATE 788223
postgres = # delete from testmem3 where t1 <789432 and t1> 1208;
DELETE 788223
 
    Before using pgfincore: See three operating time by querying the table pg_stat_statments:

postgres = # SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
              nullif (shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_time DESC;
- [RECORD 1] --------------------------------------------- ---------
query | update testmem2 set t7 = random () :: text where t1; ?
calls | 1
total_time | 14303.53
rows | 788223
hit_percent | 97.7162513440240383
- [RECORD 2] --------------------------------------------- ---------
query | delete from testmem3 where t1; ?
calls | 1
total_time | 5256.305
rows | 788223
hit_percent | 97.4004941321803582
- [RECORD 6] --------------------------------------------- ---------
query | select * from testmem1 where t1; ?
calls | 1
total_time | 2397.866
rows | 788223
hit_percent | 0.15039714245429336843

After using pgfincore: See three operating time by querying the table pg_stat_statments (after reboot, re-building a database, rebuild the table, insert the data):

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
              nullif (shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_time DESC;
- [RECORD 1] --------------------------------------------- ---------
query | update testmem2 set t7 = random () :: text where t1; ?
calls | 1
total_time | 6800.237
rows | 788223
hit_percent | 97.6908892215405358
- [RECORD 2] --------------------------------------------- ---------
query | delete from testmem3 where t1; ?
calls | 1
total_time | 3295.312
rows | 788223
hit_percent | 97.3715873996294009
- [RECORD 3] --------------------------------------------- ---------
query | select * from testmem1 where t1; ?
calls | 1
total_time | 891.002
rows | 788223
hit_percent | 0.000000000000000000000000
 

    As is apparent when performing a select speed increase significantly, the other time is not obvious, this is because the data also need to operate IO, so here faster should only be read in time savings out.

postgres = # select pgfadvise_willneed ( 'testmem1');
- [RECORD 1] ------ + -------------------------------------
pgfadvise_willneed | (base / 13003 / 16384,4096,42554,279311)
 
postgres = # select * from pgfincore ( 'testmem1');
- [RECORD 1] - + -----------------
relpath | base / 13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 42554
group_mem | 1
os_pages_free | 279317
databit |
 
postgres = # select count (*) from testmem1 where t1 <99999 and t1> 12;
- [RECORD 1]
count | 99986
 
postgres = # insert into testmem1 select generate_series (1,9999), 'wangshuo' || generate_series (1,9999) :: text || random () :: text, generate_series (1,9999) :: text || 'sure ', generate_series (1,9999), random () :: text, random () :: text, random () :: text,' liuyuanyuan ', generate_series (1,9999);
INSERT 0 9999
postgres = # select * from pgfincore ( 'testmem1'); - [RECORD 1] - + -----------------
relpath | base / 13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42980
pages_mem | 42980
group_mem | 1
os_pages_free | 277433
databit |
 
postgres = # select pg_relation_size ( 'testmem1');
- [RECORD 1] ---- + ----------
pg_relation_size | 176046080
 
postgres = # select 42980 * 4096;
- [RECORD 1] -------
? Column |? 176046080
 
postgres = # insert into testmem1 select generate_series (1,999999), 'wangshuo' || generate_series (1,999999) :: text || random () :: text, generate_series (1,999999) :: text || 'sure ', generate_series (1,999999), random () :: text, random () :: text, random () :: text,' liuyuanyuan ', generate_series (1,999999);
INSERT 0 999999
postgres = # select * from pgfincore ( 'testmem1');
- [RECORD 1] - + -----------------
relpath | base / 13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 85532
pages_mem | 6188
group_mem | 22
os_pages_free | 403050
databit |
 
postgres = #
 
    The above results show the load will willneed small part of the newly added data to the cache, it is possible to quickly find the location, but when a large amount of data is inserted, it will not prompt written to the cache in.
Summary: pgfincore currently applicable to the amount of data is very large, infrequently updated, smaller update amount to a table loaded into the cache, helps improve application efficiency.

    Plugin function introduction:

    Here function will be introduced, and all functions operate experiments.

    1, pgsysconf:
        The total number of page output function OS block size (os_page_size), OS remaining page number (os_pages_free) and the OS has (os_total_pages).

postgres = # select * from pgsysconf ();
 os_page_size | os_pages_free | os_total_pages
-------------- + --------------- + ----------------
        4096 | 167445 | 476688
(1 row)
 
    2, pgsysconf_pretty:
        This function is the same as above, just some more understandable output.

postgres = # select * from pgsysconf_pretty ();
 os_page_size | os_pages_free | os_total_pages
-------------- + --------------- + ----------------
 4096 bytes | 654 MB | 1862 MB
(1 row)
 

    3, pgfadvise_willneed:

    This function is
    Output file name (relpath), OS block size (os_page_size), occupied by the object system page number (rel_os_pages), OS remaining page number (os_pages_free).

postgres = # select * from pgfadvise_willneed ( 'testmem1');
    relpath | os_page_size | rel_os_pages | os_pages_free
------------------ + -------------- + -------------- + - --------------
 base / 13003/16384 | 4096 | 42554 | 167294
(1 row)
 
4, pgfadvise_dontneed:
This function sets dontneed mark the current object. dontneed mark means that when the operating system needs to free up memory priority release marked dontneed of pages. Significance and consistent output above, do not introduced.

postgres = # select * from pgfadvise_dontneed ( 'testmem1');
    relpath | os_page_size | rel_os_pages | os_pages_free
------------------ + -------------- + -------------- + - --------------
 base / 13003/16384 | 4096 | 42554 | 332798
(1 row)
 
    5, pgfadvise_NORMAL, pgfadvise_SEQUENTIAL, pgfadvise_RANDOM:
    And pgfadvise_dontneed similar, respectively, mark the object as normal, sequential, random, meaning literally understand it.
    In fact pgfadvise is a separate function, the argument is relname, fork, action, respectively, meaning the object name, process name and behavioral id, 3,4,5 above functions are different input parameters and settings function, which willneed, dontneed , normal, sequential, random values ​​were 10,20,30,40,50, and the default fork is main.
    Here pgfadvise main function is called posix_fadvise Linux under the tag value is posix_fadvise need.

    6, pgfincore:
 

  This function is to provide information about objects in the operating system's cache.
    It is divided into three functions, parameters are (relname, fork, getdatabit), (relname, getdatabit), (relname), meaning three parameters for the object name, process name (by default this place is main), whether or not to display databit (long note display), the first function need all the input, the second function is the default fork main, the third function is the default fork main, getdatabit is false.
    It is the output file location and name (relpath), file sequence (segment), OS page or block size (os_page_size), occupied by the object system cache desired page number (rel_os_pages), it has been occupied by the object cache page number (pages_mem) , contiguous pages in the cache group number (group_mem), OS remaining page number (os_pages_free), load the bitmap information (databit).

postgres = # select * from pgfincore ( 'testmem1', 'main', false);
- [RECORD 1] - + -----------------
relpath | base / 13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 2
group_mem | 1
os_pages_free | 325475
databit |
 
postgres = # select * from pgfincore ( 'testmem1', false);
- [RECORD 1] - + -----------------
relpath | base / 13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 2
group_mem | 1
os_pages_free | 325475
databit |
 
postgres = # select * from pgfincore ( 'testmem1');
- [RECORD 1] - + -----------------
relpath | base / 13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 2
group_mem | 1
os_pages_free | 325475
databit |

7, pgfadvise_loader:

This function can be cached pages directly two effects. Therefore, you can be in the cache to be loaded or unloaded on the page by page bitmap.
It is divided into two upper and functions similar to the default value is set, the input parameters (relname, fork, segment, load, unload, databit) and (relname, segment, load, unload, databit), are the objects name, process name, file number, whether loading, unloading whether, bitmap information. The second function is the default fork main.
 It is the physical output file name and path (relpath), OS page or block size (os_page_size), OS in the rest of the page number (os_pages_free), load the page number (pages_load), uninstall the page number (pages_unload).

postgres = # select * from pgfincore ( 'testmem1');
- [RECORD 1] - + -----------------
relpath | base / 13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 0
group_mem | 0
os_pages_free | 331290
databit |
 
postgres = # select * from pgfadvise_loader ( 'testmem1', 0, true, true, B'1100 ');
- [RECORD 1] - + -----------------
relpath | base / 13003/16384
os_page_size | 4096
os_pages_free | 331294
pages_loaded | 2
pages_unloaded | 2
 
postgres = # select * from pgfincore ( 'testmem1');
- [RECORD 1] - + -----------------
relpath | base / 13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 2
group_mem | 1
os_pages_free | 331290
databit |
     
         
       
         
  More:      
 
- Ten linux version of the system (Linux)
- MongoDB query statistics grouping remove duplicate records (Database)
- 10 example of the detection memory usage free Linux commands (Linux)
- Check with Hello World Docker installation (Server)
- Linux based serial programming (Programming)
- Java Database Programming JDBC configuration (Programming)
- CentOS / Linux install VNC Server (Linux)
- Install the Red Hat Container Development Kit on OSX (Server)
- How to install the NVIDIA 358.16 driver in Ubuntu 15.10,14.04 (Linux)
- Linux System Getting Started Learning: hard disk partition, and to deal with traps (Linux)
- Additional SQL Server 5123 database reported error (Database)
- 5 steps to help you become a good Docker contributors (Linux)
- Linux server startup and logon security settings (Linux)
- Using Oracle for Oracle GoldenGate to achieve a one-way data synchronization (Database)
- Build RPM package uses Docker mirror (Linux)
- CentOS7 iptables (Linux)
- Linux system performance analysis and top command ps.pstree Comments (Linux)
- Learning C language pointer essays (Programming)
- 20 Unix / Linux command skills (Linux)
- C ++ Supplements - Virtual Function Principle (Programming)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.