Skip to content

influence of selinux on postgres

influence of selinux on postgres published on Комментариев к записи influence of selinux on postgres нет

Test was done on VM with 4X CPU E5-2660 2.20GHz + 4GB mem with tools written by pgbench-tools on postgresql 9.4.5

settings: run test 3 times by 10 min, db size 100(1.5gb) 400(6gb) 700 (10gb) 1000 (15gb), clients 1,2,4,8,16,32

Results: with the exception of an anomalous drawdown in the last test, without selinux performance gain can reaches 20%

raw data:
pg_selinux_res

select * from (select set,dbsize,clients,avg(tps) avg_tps, round (100*(  1-lag ( avg(tps)) over ( order by dbsize,clients,set)/avg(tps)),1)  diff_pct,scale from v1 group by set,dbsize,scale,clients order by  dbsize,clients,set) t where t.set =4 order by scale,clients;
 set | dbsize  | clients |        avg_tps         | diff_pct | scale
-----+---------+---------+------------------------+----------+-------
   4 | 1502 MB |       1 |  7002.7727490000000000 |      2.1 |   100
   4 | 1502 MB |       2 |     13726.746474333333 |      0.8 |   100
   4 | 1502 MB |       4 |     24392.296147000000 |      2.6 |   100
   4 | 1502 MB |       8 |     24276.725237333333 |      2.3 |   100
   4 | 1502 MB |      16 |     23974.144015000000 |      0.2 |   100
   4 | 1502 MB |      32 |     25629.092840000000 |      0.5 |   100
   4 | 5988 MB |       1 |  2633.2450016666666667 |      4.2 |   400
   4 | 5988 MB |       2 |  4668.1277356666666667 |     15.8 |   400
   4 | 5988 MB |       4 |  7622.5932590000000000 |     14.1 |   400
   4 | 5988 MB |       8 |  9906.9333880000000000 |      8.7 |   400
   4 | 5988 MB |      16 | 12596.0858780000000000 |     20.4 |   400
   4 | 5988 MB |      32 | 13229.7484046666666667 |     18.1 |   400
   4 | 10 GB   |       1 |  2073.9020166666666667 |      1.8 |   700
   4 | 10 GB   |       2 |  3829.0723596666666667 |      7.8 |   700
   4 | 10 GB   |       4 |  6281.3118036666666667 |      7.2 |   700
   4 | 10 GB   |       8 |  8431.4767026666666667 |      8.0 |   700
   4 | 10 GB   |      16 | 11576.7615346666666667 |     19.2 |   700
   4 | 10 GB   |      32 | 13192.6005800000000000 |     20.4 |   700
   4 | 15 GB   |       1 |  1586.9348243333333333 |     -9.0 |  1000
   4 | 15 GB   |       2 |  3034.7334050000000000 |     -2.6 |  1000
   4 | 15 GB   |       4 |  5172.9388216666666667 |     -7.6 |  1000
   4 | 15 GB   |       8 |  7585.5694350000000000 |     -0.7 |  1000
   4 | 15 GB   |      16 | 10037.6583543333333333 |      5.3 |  1000
   4 | 15 GB   |      32 | 11943.1506076666666667 |     16.1 |  1000

Screen Shot 2015-11-29 at 21.11.13
Screen Shot 2015-11-29 at 21.11.07

So, for better results selinux should be disabled

postgres monitoring metrics

postgres monitoring metrics published on 2 комментария к записи postgres monitoring metrics
postgres=#
SELECT COUNT(1) AS session_count,
  SUM(  CASE    WHEN state = 'active'    THEN 1    ELSE 0  END) AS active,
  SUM(  CASE    WHEN state LIKE 'idle in%'    THEN 1    ELSE 0  END) AS idle_in_transaction,
  SUM(  CASE    WHEN now()-query_start > INTERVAL '1s'    AND state  = 'active'    THEN 1    ELSE 0  END) AS slow_query_1s,
  SUM(  CASE    WHEN now()-query_start > INTERVAL '600s'    AND state = 'active'    THEN 1    ELSE 0 END)              AS slow_query_600s,
  SUM(waiting::INT) AS waiting_query,
  CASE pg_is_in_recovery()    WHEN true    THEN 1    ELSE 0  END AS is_in_recovery,
  CASE pg_is_in_recovery()    WHEN true    THEN extract(epoch FROM now() - COALESCE(pg_last_xact_replay_timestamp(),now()))    ELSE 0   END AS replication_delay
FROM pg_stat_activity ;
 session_count | active | idle_in_transaction | slow_query_1s | slow_query_600s | waiting_query | is_in_recovery | replication_delay
---------------+--------+---------------------+---------------+-----------------+---------------+----------------+-------------------
           177 |      3 |                   0 |             2 |               1 |             0 |              0 |                 0

postgrespro pg_stat_wait patch

postgrespro pg_stat_wait patch published on Комментариев к записи postgrespro pg_stat_wait patch нет

usefull materials
github postgrespro
Waits monitoring on mail list of postgresql.org
pgconf2015 slides

Packages

ubuntu:

apt-get install gcc libreadline-dev zlib1g-dev libxml2-dev libldap2-dev python-dev libssl-dev build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libperl-dev

fedora:

yum install -y gcc bison-devel flex bison readline-devel zlib-devel openssl-devel wget perl-ExtUtils-MakeMaker perl-ExtUtils-Embed  readline-devel zlib-devel libxml2-devel openldap-devel python-devel openssl-devel

Install from source

mkdir /postgres/src -p
cd /postgres/src
wget https://github.com/postgrespro/postgres/archive/waits_monitoring_94.zip
unzip waits_monitoring_94.zip
cd postgres-waits_monitoring_94/
./configure --with-ldap --with-perl --with-python --with-openssl --with-libxml --prefix=/usr/pgsql-pro --exec-prefix=/usr/pgsql-pro
make
make install
/postgres/src/postgres-waits_monitoring_94/contrib/pg_stat_wait
make
make install
groupadd postgres -g 26
useradd --uid 26 --gid postgres --groups postgres -d /postgres -s /bin/bash -c "PostgreSQL Software Owner" postgres

enable waits monitoring

add to postgresql.conf

#History GUC parameters:

 shared_preload_libraries = 'pg_stat_wait.so' #for background worker that will be sample waits.
 pg_stat_wait.history = on #/off - turn on/off history recording
 pg_stat_wait.history_size = 1000 #how many records keep in history
 pg_stat_wait.history_period = 100 # period in millseconds between the sampling
waits_monitoring=on

### alter system set waits_monitoring=on;

 postgres=# select * from pg_stat_wait_current;
  pid  |          sample_ts           | class_id | class_name | event_id | event_name | wait_time  |  p1  |  p2   |  p3   | p4 |   p5
-------+------------------------------+----------+------------+----------+------------+------------+------+-------+-------+----+--------
 19213 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        0 | READ       |    2971593 | 1663 | 13056 | 12817 |  0 |      6
 19061 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        0 | READ       |        622 | 1663 | 13056 | 12810 |  0 |     44
 19054 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        1 | WRITE      |      47717 | 1663 | 16418 | 16431 |  0 | 165053
 19056 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |      15808 |    0 |     0 |     0 |  0 |      0
 19059 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |       3179 |    0 |     0 |     0 |  0 |      0
 19057 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |    2971267 |    0 |     0 |     0 |  0 |      0
 19055 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |     157947 |    0 |     0 |     0 |  0 |      0
 19206 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        544 |    0 |     0 |     0 |  0 |      0
 19204 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       1958 |    0 |     0 |     0 |  0 |      0
 19209 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        380 |    0 |     0 |     0 |  0 |      0
 19062 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       | 2726490525 |    0 |     0 |     0 |  0 |      0
 19201 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        704 |    0 |     0 |     0 |  0 |      0
 19203 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       2002 |    0 |     0 |     0 |  0 |      0
 19208 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       1625 |    0 |     0 |     0 |  0 |      0
 19210 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        999 |    0 |     0 |     0 |  0 |      0
 19205 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        248 |    0 |     0 |     0 |  0 |      0
 19202 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        648 |    0 |     0 |     0 |  0 |      0
 19207 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        718 |    0 |     0 |     0 |  0 |      0

ps: Many thanks to Vladimir Borodin for help !

Primary Sidebar

Яндекс.Метрика