Re: [GENERAL] Block duplications in a shared buffers

[pinker]

Thank you Tom, you were right. I needed to group by all the columns:
reldatabase, reltablespace, relforknumber too. Now all of them are unique.
Thank you for clearing this out :)



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Block duplications in a shared buffers

[pinker]

Tom Lane-2 wrote
> Postgres would be completely broken if that were true, because
> modifications made to one copy would fail to propagate to other copies.
> I don't know where your data came from, but it can't be an accurate
> representation of the instantaneous state of the buffer cache.
> 
> ... actually, after looking at your query, I wonder whether the issue
> is that you're failing to include database and tablespace in the
> grouping key.  relfilenode isn't guaranteed unique across directories.
> The fork number can matter, too.

thank you I'll add those columns to my query




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Block duplications in a shared buffers

[pinker]

Hi All,
I was analysing shared buffers content and noticed that exactly the same
disk block appears there many times with different or the same usagecount.
What's the cause of that? It's because of transactions? 

SELECT
  count(*),
  relfilenode,
  relblocknumber,
  array_agg(usagecount) usagecount,
  array_agg(isdirty)isdirty,
  array_agg(bufferid)   bufferid
FROM fo_main.buffercache
WHERE added = 'some_date'
GROUP BY relfilenode, relblocknumber
HAVING count(*) > 1
ORDER BY 1 DESC;

Sample result:

 




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

[pinker]

Andres Freund wrote
> Others mentioned already that that's worth improving.

Yes, we are just setting up pgbouncer


Andres Freund wrote
> Some versions of this kernel have had serious problems with transparent
> hugepages. I'd try turning that off. I think it defaults to off even in
> that version, but also make sure zone_reclaim_mode is disabled.

Yes, I'm aware of that so always set it to never.
but thank you for the zone_reclaim_mode.



Andres Freund wrote
> 9.6 has quite some scalability improvements over 9.5. I don't know
> whether it's feasible for you to update, but if so, It's worth trying.
> 
> How about taking perf profile to investigate?

Both are on my to do list :)



Andres Freund wrote
> I'd suggest monitoring /proc/meminfo for the amount of Dirty and
> Writeback memory, and see whether rapid changes therein coincide with
> periodds of slowdown.

yes, I was monitoring it the whole day and that's the reason why I've
changed dirty_background_ratio but both of them were flat - without any
bigger spikes. 








--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

[pinker]

Tomas Vondra-4 wrote
> I'm probably a bit dumb (after all, it's 1AM over here), but can you
> explain the CPU chart? I'd understand percentages (say, 75% CPU used)
> but what do the seconds / fractions mean? E.g. when the system time
> reaches 5 seconds, what does that mean?

hehe, no you've just spotted a mistake, it suppose to be 50 cores :)
out of 80 in total


Tomas Vondra-4 wrote
> Have you tried profiling using perf? That usually identifies hot spots
> pretty quickly - either in PostgreSQL code or in the kernel.

I was always afraid because of overhead, but maybe it's time to start ...


Tomas Vondra-4 wrote
> What I meant is that if the system evicts this amount of buffers all the
> time (i.e. there doesn't seem to be any sudden spike), then it's
> unlikely to be the cause (or related to it).

I was actually been thinking about scenario where different sessions want to
at one time read/write from or to many different relfilenodes, what could
cause page swap between shared buffers and os cache? we see that context
switches on cpu are increasing as well. kernel documentation says that using
page tables instead of Translation Lookaside Buffer (TLB) is very costly and
on some blogs have seen recomendations that using huge pages (so more
addresses can fit in TLB) will help here but postgresql, unlike oracle,
cannot use it for anything else than page buffering (so 16gb) ... so process
memory still needs to use 4k pages.
or memory fragmentation?




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

[pinker]

Victor Yegorov wrote
> Looks like `sdg` and `sdm` are the ones used most.
> Can you describe what's on those devices? Do you have WAL and DB sitting
> together?
> Where DB log files are stored?

it's multipath with the same LUN for PGDATA and pg_log, but separate one for
xlogs and archives.

mpatha  dm-4 IBM ,2145

size=2.0T features='0' hwhandler='0' wp=rw

|-+- policy='round-robin 0' prio=0 status=active

| |- 7:0:1:2 sdg 8:96  active undef running

| `- 8:0:1:2 sdm 8:192 active undef running

`-+- policy='round-robin 0' prio=0 status=enabled

  |- 7:0:0:2 sdd 8:48  active undef running

  `- 8:0:0:2 sdj 8:144 active undef running



Victor Yegorov wrote
> Can you, please, provide the output of this query (linked from the article
> mentioned):
> https://gist.github.com/lesovsky/4587d70f169739c01d4525027c087d14

00:26:51.226024|120 days
03:05:37.987175|0.6|7.99|300.63|0.46|12673500.4|162.00|0.34|0.51|0.37|1.22|26.721|27.7|41.8|30.6|4.47|34.27|--|21532|124|6510377185|9920323|449049896|677360078|2321057|495798075|0


Victor Yegorov wrote
> And also this query:
> SELECT name,version,source FROM pg_settings WHERE source NOT IN
> ('default','override');

application_name | client   | psql

archive_command  | configuration file   | 

archive_mode | configuration file   | on

autovacuum   | configuration file   | on

autovacuum_max_workers   | configuration file   | 10

checkpoint_completion_target | configuration file   | 0.9

checkpoint_timeout   | configuration file   | 480

client_encoding  | client   | UTF8

DateStyle| configuration file   | ISO, MDY

default_statistics_target| configuration file   | 350

default_text_search_config   | configuration file   | pg_catalog.english

effective_cache_size | configuration file   | 52428800

enable_indexscan | configuration file   | on

huge_pages   | configuration file   | on

lc_messages  | configuration file   | en_US.UTF-8

lc_monetary  | configuration file   | en_US.UTF-8

lc_numeric   | configuration file   | en_US.UTF-8

lc_time  | configuration file   | en_US.UTF-8

listen_addresses | configuration file   | *

log_autovacuum_min_duration  | configuration file   | 0

log_checkpoints  | configuration file   | on

log_connections  | configuration file   | on

log_destination  | configuration file   | stderr

log_directory| configuration file   | pg_log

log_disconnections   | configuration file   | on

log_duration | configuration file   | off

log_filename | configuration file   | postgresql-%a.log

log_line_prefix  | configuration file   | %t [%p]: [%l-1]
user=%u,db=%d

log_lock_waits   | configuration file   | on

log_min_duration_statement   | configuration file   | 0

log_rotation_age | configuration file   | 1440

log_rotation_size| configuration file   | 0

log_temp_files   | configuration file   | 0

log_timezone | configuration file   | Poland

log_truncate_on_rotation | configuration file   | on

logging_collector| configuration file   | on

maintenance_work_mem | configuration file   | 2097152

max_connections  | configuration file   | 1000

max_stack_depth  | environment variable | 2048

max_wal_senders  | configuration file   | 10

max_wal_size | configuration file   | 640

random_page_cost | configuration file   | 1

shared_buffers   | configuration file   | 2097152

temp_buffers | configuration file   | 16384

TimeZone | configuration file   | Poland

track_functions  | configuration file   | all

track_io_timing  | configuration file   | off

wal_buffers  | configuration file   | 2048

wal_keep_segments| configuration file   | 150

wal_level| configuration file   | hot_standby

work_mem | configuration file   | 393216+






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

[pinker]

Tomas Vondra-4 wrote
> What is "CPU load"? Perhaps you mean "load average"?

Yes, I wasn't exact: I mean system cpu usage, it can be seen here - it's the
graph from yesterday's failure (after 6p.m.):
 
So as one can see connections spikes follow cpu spikes...


Tomas Vondra-4 wrote
> Also, what are the basic system parameters (number of cores, RAM), it's
> difficult to help without knowing that.

I have actually written everything in the first post:
80 CPU and 4 sockets
over 500GB RAM


Tomas Vondra-4 wrote
> Well, 3M transactions over ~2h period is just ~450tps, so nothing
> extreme. Not sure how large the transactions are, of course.

It's quite a lot going on. Most of them are complicated stored procedures.


Tomas Vondra-4 wrote
> Something gets executed on the database. We have no idea what it is, but
> it should be in the system logs. And you should see the process in 'top'
> with large amounts of virtual memory ...

Yes, it would be much easier if it would be just single query from the top,
but the most cpu is eaten by the system itself and I'm not sure why. I
suppose because of page tables size and anon pages is NUMA related.



Tomas Vondra-4 wrote
> Another possibility is a run-away query that consumes a lot of work_mem.

It was exactly my first guess. work_mem is set to ~ 350MB and I see a lot of
stored procedures with unnecessary WITH clauses (i.e. materialization) and
right after it IN query with results of that (hash).



Tomas Vondra-4 wrote
> Measure cache hit ratio (see pg_stat_database.blks_hit and blks_read),
> and then you can decide.

Thank you for the tip. I always do it but haven't here,  so the result is
0.992969610990056 - so increasing it is rather pointless.


Tomas Vondra-4 wrote
> You may also make the bgwriter more aggressive - that won't really
> improve the hit ratio, it will only make enough room for the backends.

yes i probably will


Tomas Vondra-4 wrote
> But I don't quite see how this could cause the severe problems you have,
> as I assume this is kinda regular behavior on that system. Hard to say
> without more data.

I can provide you with any data you need :)


regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

[pinker]

Scott Marlowe-2 wrote
> Ouch, unless I'm reading that wrong, your IO subsystem seems to be REALLY
> slow.

it's a huge array where a lot is happening, for instance data snapshots :/
the lun on which is this db is dm-7.
I'm a DBA with null knowledge about arrays so any advice will be much
appreciated :)




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

[pinker]

Victor Yegorov wrote
> Can you provide output of `iostat -myx 10` at the “peak” moments, please?

sure, please find it here:
https://pastebin.com/f2Pv6hDL


Victor Yegorov wrote
> Also, it'd be good to look in more detailed bgwriter/checkpointer stats.
> You can find more details in this post: http://blog.postgresql-
> consulting.com/2017/03/deep-dive-into-postgres-stats_27.html
> (You might want to reset 'shared' stats here.)

thank you for the link, it's really nice explanation. Here you'll find the
full bgwriter stats: https://pastebin.com/VA8pyfXj


-- 
Victor Yegorov





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

[pinker]

Thank you Scott,
we are planning to do it today. But are you sure it will help in this case?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] core system is getting unresponsive because over 300 cpu load

[pinker]

Hi to all!

We've got problem with a very serious repetitive incident on our core
system. Namely, cpu load spikes to 300-400 and the whole db becomes
unresponsive. From db point of view nothing special is happening, memory
looks fine, disks io's are ok and the only problem is huge cpu load. Kernel
parameters that are increasing with load are always the same:

* page tables size
* Committed_AS
* Active anon

 

and the total number of connections are increasing very fast (but I suppose
it's the symptom not the root cause of cpu load) and exceed max_connections
(1000).

System:
* CentOS Linux release 7.2.1511 (Core) 
* Linux 3.10.0-327.36.3.el7.x86_64 #1 SMP Mon Oct 24 16:09:20 UTC 2016
x86_64 x86_64 x86_64 GNU/Linux
* postgresql95-9.5.5-1PGDG.rhel7.x86_64
* postgresql95-contrib-9.5.5-1PGDG.rhel7.x86_64
* postgresql95-docs-9.5.5-1PGDG.rhel7.x86_64
* postgresql95-libs-9.5.5-1PGDG.rhel7.x86_64
* postgresql95-server-9.5.5-1PGDG.rhel7.x86_64

* 4 sockets/80 cores
* vm.dirty_background_bytes = 0
* vm.dirty_background_ratio = 2
* vm.dirty_bytes = 0
* vm.dirty_expire_centisecs = 3000
* vm.dirty_ratio = 20
* vm.dirty_writeback_centisecs = 500

after the first incident we have changed:
* increased shared_buffers to 16GB (completely on huge pages. previously
2GB)
* adjusted vm.nr_hugepages to 8000 (we've got 2mb pages)
* changed vm.overcommit_memory = 2 and vm.overcommit_ratio = 99
* disabled transparent huge pages (they were set before unfortunately to
'always')


It's a highly transactional db. Today I've run:
select now(), txid_current();
and the results:
3 339 351 transactions between 2017-10-10 14:42 and 2017-10-10 16:24

* db size 1,1TB
* RAM over 500GB
* biggest tables (the rest isn't big):
369 GB
48 GB
48 GB
34 GB
23 GB
19 GB
19 GB
17 GB
16 GB
12 GB
9910 MB

We have captured some of db statistics, for instance bgwriter and
buffercache.
Today the load spides happened at:
1). 10:44
2). 11:04
(and then several times during a day)
The premiere was yesterday about 6PM.

What we observed back then was for instance autovacuum process to prevent
wraparound on the biggest table (369GB). We did vacuum freeze manually after
this happened but before that we gathered statistics with the query:
SELECT
   oid::regclass::text AS table,
   age(relfrozenxid) AS xid_age, 
   mxid_age(relminmxid) AS mxid_age, 
   least( 
(SELECT setting::int
FROMpg_settings
WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid), 
(SELECT setting::int
FROMpg_settings
WHERE   name = 'autovacuum_multixact_freeze_max_age') -
mxid_age(relminmxid)  
) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROMpg_class
ORDER BY tx_before_wraparound_vacuum;

and the biggest table which was vacuumed looked like:
2173105118156548-17310511369 GB2017-09-30
01:57:33.972068+02


So, from the kernel stats we know that the failure happens when db is trying
to alocate some huge amount of pages (page tables size, anons, commited_as).
But what is triggering this situation? 
I suppose it could be lazy autovacuum (just standard settings). So
autovacuum had to read whole 369gb yesterday to clean xids. today did the
same on some other tables.
Another idea is too small shared buffers setting. 
Today it looked like:
 

c - means count 
the number after c is the usage count, so c5dirty means here count of dirty
pages with usagecount=5

that is the snapshot before and after the failure at 10:44

before and after the spike at 11:04:
 


My interpretation of it is the following:
 the count of clean buffers with high usagecount is decreasing, the count of
buffers with usagecount of 0 and 1 is very unstable -> so the buffers have
no time to get older in the shared buffers and are thrown out?

bgwriter stats:
 

the biggest number of buffers is cleaned by backends - so there is no free
buffers with usagecount 0 and LWlocks happen?

So increasing shared buffers would be a solution?
Please help, it's happening quite often and I'm not sure which way is the
right one...  








--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] delete a file everytime pg server starts/crashes

[pinker]

Actually if the name of the file doesn't matter you could put it into
$PGDATA/pg_stat and name it global.stat. When postgres stops (clean or
because of failure), replaces the file with his own. So your content will be
erased.
I'm not sure it's completely safe but works in simple test.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] delete a file everytime pg server starts/crashes

[pinker]

Look at inotify: https://github.com/rvoicilas/inotify-tools
You can check for instance if postmaster.pid exists.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Checkpoint write time - anything unusual?

[pinker]

I've just run pgBadger on my pg logs and wonder if those checkpoint
statistics is something I should worry about or not?
The highest write time is about 47 minutes but I'm not sure if that's
checkpoint_completion_target*checkpoint_target value or real time between
sending the command to write and getting response?
If that's the first one - then I assume everything is ok because:
 checkpoint_timeout = 8min
 checkpoint_completion_target = 0.9

but I'm not sure which value it is...

Hour  Written buffersWrite timeSync timeTotal time
00104,6222,510.379s0.558s2,511.144s
0123,549 2,218.747s0.298s2,219.199s
029,776  919.796s  0.244s920.281s
031,075  107.07s   0.398s107.667s
041,062  105.853s  0.396s106.384s
0516,216 1,609.462s0.576s1,610.245s
0638,315 2,137.492s0.246s2,137.879s
0751,504 2,571.057s0.48s 2,571.7s
0853,921 2,295.652s2.071s2,297.957s
0918,716 1,116.161s3.132s1,119.715s
109,000  581.912s  3.866s586.109s
1112,795 879.337s  2.427s882.124s
1213,872 860.987s  1.127s862.444s
1314,883 805.011s  0.866s806.119s
1413,172 1,062.677s1.228s1,064.188s
159,375  620.979s  1.013s622.632s
166,268  626.158s  1.059s627.721s
1712,387 613.113s  4.849s618.534s
1840,641 1,791.432s1.571s1,793.546s
1938,316 1,946.747s1.343s1,948.442s
2069,564 2,817.917s1.268s2,819.678s
2117,333 1,736.004s0.812s1,737.25s
229,230  924.742s  0.576s925.49s
23118,0372,150.212s2.167s2,152.673s



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Audit based on role

[pinker]

Hi,
I personally don't like the solution from
https://wiki.postgresql.org/wiki/Audit_trigger and here are the reasons why:
* it produces a large table where all the changes, from all tables and
schemas go - audit.logged_actions. So when you would like to read it to
check anything it will be super slow
* On audit table are 3 indexes - which slow down the insert process
* All the data are kept in one column - so when comes to any analysis you
need custom functions to do it

Besides: Why there is fillfactor=100? That's the default value for table,
isn't it?

I use a bit different approach:
* every table has got separate table in a audit schema
* audited data are partinioned (usually per month)
* it's much easier to remove old data - just by dropping partition
* data has got exactly the same structure as in original schema

Every od those solution has got pros and cons but I prefer the second one a
lot more.
You can find one of it here: https://github.com/AwdotiaRomanowna/pgaudit



--
View this message in context: 
http://www.postgresql-archive.org/Audit-based-on-role-tp5976507p5976641.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop column from interrelated views

[pinker]

I've got some functions on my own :)
If you prefer to do it from SQL level I've got two functions prepared which
first secure then restore all missing views definitions. 


-- secure all views
DO $$
BEGIN
--drop schema migration cascade
  CREATE SCHEMA migration;

  CREATE TABLE migration.views AS
SELECT
  table_schema,
  table_name,
  view_definition
FROM INFORMATION_SCHEMA.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

  CREATE TABLE migration.view_count AS
SELECT
  count(*),
  'before' :: TEXT AS desc
FROM INFORMATION_SCHEMA.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

END;
$$;

/*
 HERE DO YOUR EVIL DROP CASCADE
 YOUR VIEWS DEFINITIONS ARE SAFE IN MIGRATION SCHEMA
 REMEMBER YOU BACKED UP ONLY VIEWS DEFINITIONS - NO TABLES, RULES OR DATA
 */

-- restore all dropped views / only not existing views
DO $$

DECLARE
  l_string TEXT;
BEGIN

  FOR l_string IN SELECT 'CREATE VIEW ' || table_schema || '.' || table_name
|| ' AS '
 || view_definition
  FROM migration.views
  LOOP
BEGIN
  EXECUTE l_string;
  EXCEPTION WHEN OTHERS THEN
  -- do nothing
END;
  END LOOP;

  IF ((SELECT count
   FROM migration.view_count) = (SELECT count(*)
 FROM INFORMATION_SCHEMA.views
 WHERE table_schema NOT IN
('pg_catalog', 'information_schema')))
  THEN
RAISE NOTICE 'Migration successful';
  ELSE
RAISE NOTICE 'Something went wrong';
  END IF;

END;
$$;



If migration was successful you can drop schema migration.





--
View this message in context: 
http://www.postgresql-archive.org/How-to-drop-column-from-interrelated-views-tp5970484p5970518.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Huge Pages - setting the right value

[pinker]

standard hugepages, transparent are disabled.
They were set exactly following the procedure from postgres documentation.



--
View this message in context: 
http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5966064.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Huge Pages - setting the right value

[pinker]

Andrew Kerber wrote
> I can't give you an absolutely authoritative answer, but because of the
> way hugepages are implemented and allocated, I can't think how they could
> be used for other processes.  Linux hugepages are either 2m or 1g, far too
> large for any likely processes to require. They cannot be allocated in
> partial pages.

thank you for your help. 
My system is using 2MB pages for shared buffers. I have checked and one of
my processes has used 606788kB of memory, so potentially could use ~ 300
huge pages, but does postgres can use it for non shared memory?




--
View this message in context: 
http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5965963.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Huge Pages - setting the right value

[pinker]

We are experiencing some performance issues because of high CPU load. So I
would like to ask one more time. The exact question is:
Does PostgreSQL can use huge pages for processes or only for shared buffers?
(Does it make any sense to set the number of huge pages above the
shared_buffers?)
Any help or suggestions would be much appreciated! 



--
View this message in context: 
http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5965956.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query which "never" terminates

[pinker]

Try to change from_collapse_limit values at first, for instance run SET
from_collapse_limit = 1; and then your query in the same session. 
have you ran analyze lately? 



--
View this message in context: 
http://www.postgresql-archive.org/Query-which-never-terminates-tp5958734p5958736.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Block size recommendation for Red Hat Linux 7.2

[pinker]

I've seen very big differences with huge_pages set to on, especially in
context of CPU usage on multiple socket servers.

You could play as well with storage options, for instance inode size and
check if there is any advantage for your db from inlining, which is
supported by xfs. You can find more informations here:
http://beegfs.com/wiki/StorageServerTuning 

An interesting option for WAL would be to add the mount option- allocsize -
and set it to 16MB - so the exact size of WAL segment to reduce the risk of
fragmentation and optimal streaming write throughput.



--
View this message in context: 
http://www.postgresql-archive.org/Block-size-recommendation-for-Red-Hat-Linux-7-2-tp5958026p5958066.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Archiving data to another server using copy, psql with pipe

[pinker]

W dniu 2017-04-06 14:28:04 użytkownik Moreno Andreo <moreno.and...@evolu-s.it> 
napisał:
> Il 06/04/2017 13:58, pinker ha scritto:
> >
> > W dniu 2017-04-06 13:24:16 użytkownik Moreno Andreo 
> > <moreno.and...@evolu-s.it> napisał:
> >> Il 05/04/2017 23:26, pinker ha scritto:
> >>> Hi,
> >>> I'm trying to write an archive manager which will be first copying data 
> >>> from
> >>> tables with where clause and then, after successful load into second 
> >>> server
> >>> - delete them.
> >>> The simplest (and probably fastest) solution I came up with is to use 
> >>> copy:
> >>> psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) 
> >>> to
> >>> stdout " | psql -h localhost  postgres   -c "copy b from stdin"
> >> both  psql -h are on localhost. Is it a typo?
> > No, It's not a typo, just a test ;)

> ... so source and destination database are the same? (just guessing...)
yes, they are as you can easily read - it's postgres in both cases.
This is just easy to reproduce example.

> >
> >>> I have made very simple test to check if I can be sure about 
> >>> "transactional"
> >>> safety. It's not two phase commit of course but it's seems to throw an 
> >>> error
> >>> if something went wrong and it's atomic (i assume). The test was:
> >>>
> >>> CREATE TABLE public.a
> >>> (
> >>> id integer,
> >>> k01 numeric (3)
> >>> );
> >>>
> >>> CREATE TABLE public.b
> >>> (
> >>> id integer,
> >>> k01 numeric (1)
> >>> );
> >>>
> >>> insert into a select n,n from generate_series(1,100) n;
> >>>
> >>> and then:
> >>> psql -h localhost postgres -c "copy a to stdout "|psql -h localhost
> >>> postgres   -c "copy b from stdin"
> >>>
> >>> so psql has thrown an error
> >> ... and what is the error?
> >>> and no rows were inserted to the b table - so it
> >>> seems to be ok.
> >>>
> >>> Is there maybe something I'm missing?
> >>> Some specific condition when something could go wrong and make the process
> >>> not atomic? (i don't care about data consistency in this particular case).
> >> Without knowing OS and psql version of both servers, how they are
> >> connected, or what error you get, it's hard for me to help you further.
> > psql in version 9.6 and OS: Red Hat 7
> > Does Os version really make any difference?
> AFAIK the biggest differences are among different OS families, say 
> Windows and Linux, but there could be some small things among linux 
> distributions. More depth about this topic is beyond my knowledge.
> ... but you did not report the error message, with this is much easier 
> to help you without guessing too much :-)

Error message says, as one could expect, that the second table has got smaller 
precision...
The question isn't about this particular error - which was induced for purpose 
- but about atomicity of this operation

> >
> > Best regards,
> > A. Kucharczyk
> >
> >
> >> Best regards
> >> Moreno.
> >>>
> >>>
> >>>
> >>> --
> >>> View this message in context: 
> >>> http://www.postgresql-archive.org/Archiving-data-to-another-server-using-copy-psql-with-pipe-tp5954469.html
> >>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> >>>
> >>>
> >>
> >>
> >>
> >> -- 
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >>
> >
> >
> >
> 
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Archiving data to another server using copy, psql with pipe

[pinker]

W dniu 2017-04-06 13:24:16 użytkownik Moreno Andreo <moreno.and...@evolu-s.it> 
napisał:
> Il 05/04/2017 23:26, pinker ha scritto:
> > Hi,
> > I'm trying to write an archive manager which will be first copying data from
> > tables with where clause and then, after successful load into second server
> > - delete them.
> > The simplest (and probably fastest) solution I came up with is to use copy:
> > psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) to
> > stdout " | psql -h localhost  postgres   -c "copy b from stdin"
> both  psql -h are on localhost. Is it a typo?

No, It's not a typo, just a test ;)

> >
> > I have made very simple test to check if I can be sure about "transactional"
> > safety. It's not two phase commit of course but it's seems to throw an error
> > if something went wrong and it's atomic (i assume). The test was:
> >
> > CREATE TABLE public.a
> > (
> >id integer,
> >k01 numeric (3)
> > );
> >
> > CREATE TABLE public.b
> > (
> >id integer,
> >k01 numeric (1)
> > );
> >
> > insert into a select n,n from generate_series(1,100) n;
> >
> > and then:
> > psql -h localhost postgres -c "copy a to stdout "|psql -h localhost
> > postgres   -c "copy b from stdin"
> >
> > so psql has thrown an error
> ... and what is the error?
> > and no rows were inserted to the b table - so it
> > seems to be ok.
> >
> > Is there maybe something I'm missing?
> > Some specific condition when something could go wrong and make the process
> > not atomic? (i don't care about data consistency in this particular case).
> Without knowing OS and psql version of both servers, how they are 
> connected, or what error you get, it's hard for me to help you further.

psql in version 9.6 and OS: Red Hat 7
Does Os version really make any difference?

Best regards,
A. Kucharczyk


> 
> Best regards
> Moreno.
> >
> >
> >
> >
> > --
> > View this message in context: 
> > http://www.postgresql-archive.org/Archiving-data-to-another-server-using-copy-psql-with-pipe-tp5954469.html
> > Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> >
> >
> 
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Archiving data to another server using copy, psql with pipe

[pinker]

Hi,
I'm trying to write an archive manager which will be first copying data from
tables with where clause and then, after successful load into second server
- delete them.
The simplest (and probably fastest) solution I came up with is to use copy:
psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) to
stdout " | psql -h localhost  postgres   -c "copy b from stdin"

I have made very simple test to check if I can be sure about "transactional"
safety. It's not two phase commit of course but it's seems to throw an error
if something went wrong and it's atomic (i assume). The test was:

CREATE TABLE public.a
(
  id integer,
  k01 numeric (3)
);

CREATE TABLE public.b
(
  id integer,
  k01 numeric (1)
);

insert into a select n,n from generate_series(1,100) n;

and then:
psql -h localhost postgres -c "copy a to stdout "|psql -h localhost 
postgres   -c "copy b from stdin"

so psql has thrown an error and no rows were inserted to the b table - so it
seems to be ok.

Is there maybe something I'm missing?
Some specific condition when something could go wrong and make the process
not atomic? (i don't care about data consistency in this particular case).




--
View this message in context: 
http://www.postgresql-archive.org/Archiving-data-to-another-server-using-copy-psql-with-pipe-tp5954469.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Huge Pages - setting the right value

[pinker]

W dniu 2017-03-30 11:45:55 użytkownik pinker <pin...@onet.eu> napisał:
> Hi,
> I'm currently testing performance with and without huge pages. Documentation
> says that in order to estimate the number of huge pages needed one should
> check the postmaster's VmPeak value. I wonder if it's only postmaster memory
> usage what's matters? Or I could get better estimation from the most memory
> intensive postgres process - not necessarly postmaster? I'm using following
> command to check it:
> for i in $(ps -ef | grep postgres|awk '{print $2}'); do grep ^VmPeak
> /proc/${i}/status|awk '{print $2}' >> log; done; sort -n -r log | head -1
> 
> I'm asking because some other process takes 606788kB while postmaster only
> 280444kB.
> 
> 
> 
> --
> View this message in context: 
> http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 


or maybe sum of all processes?
I assume that memory allocated by postmaster means shared buffers, so if one 
wants to huge pages beeing used for sorting as well then should set some bigger 
number of huge pages in the kernel? Is it a right assumption?





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Huge Pages - setting the right value

[pinker]

Hi,
I'm currently testing performance with and without huge pages. Documentation
says that in order to estimate the number of huge pages needed one should
check the postmaster's VmPeak value. I wonder if it's only postmaster memory
usage what's matters? Or I could get better estimation from the most memory
intensive postgres process - not necessarly postmaster? I'm using following
command to check it:
for i in $(ps -ef | grep postgres|awk '{print $2}'); do grep ^VmPeak
/proc/${i}/status|awk '{print $2}' >> log; done; sort -n -r log | head -1

I'm asking because some other process takes 606788kB while postmaster only
280444kB.



--
View this message in context: 
http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Index loading methods

[pinker]

If PostgreSQL decides to use an index, does he every time load the whole
B-tree into memory? or maybe loads only specific subtree or some chunks of
index?



--
View this message in context: 
http://www.postgresql-archive.org/Index-loading-methods-tp5952220.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using ctid in delete statement

[pinker]

Adrian Klaver-4 wrote
> Exactly, they do not have it whereas:
> 
> https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE

Still not much. The documentation could be more verbose on this topic. I can
only presume that since there is an example with select:
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;
it's propably possible, but there is no information when the lock is
released (on commit like in oracle?) especially if there is no explicit
BEGIN/END clause like in this case.


Oracle documentation is much more clear about it:
You can also use SELECT FOR UPDATE to lock rows that you do not want to
update, as in Example 9-6.
  







--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944733.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using ctid in delete statement

[pinker]

Adrian Klaver-4 wrote
> https://www.postgresql.org/docs/9.6/static/sql-truncate.html
> 
> https://www.postgresql.org/docs/9.6/static/sql-delete.html

There is nothing about FOR UPDATE clause on those pages...




--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944720.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using ctid in delete statement

[pinker]

Thank you Tom for clarification.
Does it mean that FOR UPDATE clause works with other operations as well?
i.e. TRUNCATE, DELETE?



--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944658.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using ctid in delete statement

[pinker]

thank you for the answer



--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p591.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using ctid in delete statement

[pinker]

Hi,
is it safe to use ctid in following query? :

DELETE FROM table_name WHERE ctid = any ( array ( select tn.ctid from
table_name tn JOIN items i on tn.itemid=i.itemid WHERE tn.clock < extract (
epoch FROM now() - i.history * interval '10 day')::int + 6 limit 100));

Could I be sure that ctid will not change during the execution or will not
do any harm to other transactions?


regards



--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Strange activity of prepared queries

[pinker]

Hi!
I have pgBadger report with strange data about prepared queries I cannot
interpret by myself. If anybody could help me with interpretation, would be
greatly appreciated.
In first half of the day pgBadger shows there is no prepare statements and
all queries are binded - how is that possible if average duration of session
is only 4m16s, not 15 hours?

HourPrepare BindBind/PreparePercentage of prepare
0   0   4,059   4,059.000.00%
1   0   341 341.00  0.00%
2   0   8   8.000.00%
3   0   16  16.00   0.00%
4   0   28  28.00   0.00%
5   0   6   6.000.00%
6   0   350 350.00  0.00%
7   0   2,352,350.000.00%
8   0   1,999   1,999.000.00%
9   0   2,383   2,383.000.00%
10  3   870 290.00  0.34%
11  0   1,941   1,941.000.00%
12  2   1,397   698.50  0.14%
13  0   798 798.00  0.00%
14  0   1,445   1,445.000.00%
15  1,707,090   1,707,503   1.0099.93%
16  2,133,960   2,134,001   1.0099.94%
17  1,793,804   1,793,851   1.0099.93%
18  1,745,064   1,745,095   1.0099.94%
19  1,067,248   1,067,288   1.0099.90%
20  937,137 937,142 1.0099.88%
21  596,606 596,603 1.0034.43%
22  711,941 711,945 1.0058.58%
23  816,353 816,37  1.0099.86%




--
View this message in context: 
http://postgresql.nabble.com/Strange-activity-of-prepared-queries-tp5932990.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_am access in simple transaction?

[pinker]

Adrian Klaver-4 wrote
> I can not replicate using 50 clients instead of 2000. I suspect either 
> has to do with the extreme number of clients or it is an artifact of 
> from some other process.

And I have replicated it with 50 clients as well... lsof output:

51 data/base/13328/2601

command: watch 'lsof -e /run/user/1001/gvfs +D data|awk "{print
\$NF}"|sort|uniq -c|sort -nr'

Maybe our versions of PostgreSQL differs? I use "PostgreSQL 9.5.4 on
x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.1.1 20160621 (Red Hat
6.1.1-3), 64-bit"




--
View this message in context: 
http://postgresql.nabble.com/pg-am-access-in-simple-transaction-tp5931974p5931991.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_am access in simple transaction?

[pinker]

Hi,
I'm doing simple tests with lsof on data catalog with bash script:

#!/bin/bash
for i in {0..2000}
do
psql -U postgres -c '*BEGIN; select pg_sleep(30); COMMIT*'&
done

and i measure number of open files and what files are affected by specific
command.
Lsof has shown me that the only file that was open during this test was:
data/base/13328/2601, which is pg_catalog table pg_am.

Why postgres opens a table that stores information about relation (index)
access methods, while none index evidently is in use?








--
View this message in context: 
http://postgresql.nabble.com/pg-am-access-in-simple-transaction-tp5931974.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

[pinker]

Jeff Janes wrote
> Try swapping the order of the columns in the exclude constraint.  You want
> the more selective criterion to appear first in the index/constraint.
> Presumably "key with =" is the most selective, especially if many of your
> periods are unbounded.

I would not be so sure with that:
http://use-the-index-luke.com/sql/myth-directory/most-selective-first




--
View this message in context: 
http://postgresql.nabble.com/performance-problems-with-bulk-inserts-updates-on-tsrange-with-gist-based-exclude-constrains-tp5921498p5922219.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

[pinker]

Francisco Olarte wrote
> It's already been told that btrees work that way, if you find itstrange
> read a bit about them, this is completely normal, but ...

I am just surprised by the order of magnitude in the difference though. 2
and 27 minutes that's the huge difference...I did another, simplified test,
to make sure there is no duplicates and the only difference between both
sets is the order:
CREATE TABLE source_sequential AS SELECT s from generate_series(1,1000)
as s; CREATE TABLE  source_randomAS SELECT * from source_sequential
ORDER BY random();CREATE TABLE t_sequential (id bigint);CREATE INDEX
i_sequential ON t_sequential (id);CREATE TABLE t_random (id bigint);CREATE
INDEX i_random ON t_random (id);INSERT INTO t_sequential SELECT * FROM
source_sequential;*102258,949 ms*INSERT INTO t_random SELECT * FROM
source_random;*1657575,699 ms*




--
View this message in context: 
http://postgresql.nabble.com/Sequential-vs-random-values-number-of-pages-in-B-tree-tp5916956p5917292.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

[pinker]

W dniu 2016-08-18 14:19:25 użytkownik Ilya Kazakevich 
 napisał:
> >Thank you. So if that is the reason changing the fillfactor parameter should
> >help?
> 
> Fillfactor is not about rebalancing, but about page split. If you have many 
> insertions you may decrease fillfactor to minimize  page splits, but I am not 
> sure it will help in your case.  But you should try)
> Better approach is to create index _after_ insertion, but it is not always 
> possible.
> 
> 
> Ilya Kazakevich
> 
> JetBrains
> http://www.jetbrains.com
> The Drive to Develop
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 

>From link you have pasted:
"Both insertions and deletions are fast as long as space is available on a 
block. If an insertion won't fit on the block, then some free space on some 
nearby block must be found and the auxiliary indices adjusted. The hope is that 
enough space is nearby such that a lot of blocks do not need to be reorganized."

and from postgres documentation:
fillfactor

The fillfactor for an index is a percentage that determines how full the 
index method will try to pack index pages. For B-trees, leaf pages are filled 
to this percentage during initial index build, and also when extending the 
index at the right (adding new largest key values)

So spliting happens when no room left on the page. But before that room can be 
used for further insertions...




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

[pinker]

W dniu 2016-08-18 14:00:31 użytkownik Ilya Kazakevich 
 napisał:
> Hi, 
> 
> >What's the reason that postgres needs more index pages to store random
> >data
> >than sequential ones?
> 
> I assume that is because B-Tree is self-balanced tree, so it needs to be
> rebalanced after each insertion.
> Random insertions may go to the head of index where no space left leading to
> huge data moving.
> https://en.wikipedia.org/wiki/B-tree#Insertions_and_deletions
> 
> 
> 
> Ilya Kazakevich
> 
> JetBrains
> http://www.jetbrains.com
> The Drive to Develop
> 
> 

Thank you. So if that is the reason changing the fillfactor parameter should 
help?



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Sequential vs. random values - number of pages in B-tree

[pinker]

Hi!
After doing a quick test:
with sequential values:
create table t01 (id bigint);
create index i01 on t01(id);
insert into t01 SELECT s from generate_series(1,1000) as s;

and random values:
create table t02 (id bigint);
create index i02 on t02(id);
insert into t02 SELECT random()*100 from generate_series(1,1000) as s;

The page counts for tables remain the same:
 relpages | relname  
--+--
44248 | t01
44248 | t02

But for indexes are different:
 relpages | relname 
--+-
27421 | i01
34745 | i02

Plus, postgres does 5 times more writes to disk with random data. 
What's the reason that postgres needs more index pages to store random data
than sequential ones?



--
View this message in context: 
http://postgresql.nabble.com/Sequential-vs-random-values-number-of-pages-in-B-tree-tp5916956.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Escaping text / hstore

[pinker]

Maybe somebody have an idea how to escape text string for use in hstore
column? I have tried $$ and quote_literal in audit trigger function, but
still db won't let me pass values with // or ' to the hstore...INSERT FROM
trigger function:
EXECUTE 'INSERT INTO history.' || TG_TABLE_SCHEMA || '_' ||
TG_TABLE_NAME || l_table_suffix ||'(operation, event_time,
executed_by, new_value) VALUES(''' || TG_OP || ''', ''' ||
CURRENT_TIMESTAMP || ''', ''' || SESSION_USER || ''', $$''' ||   
hstore(NEW) || '''$$)';
During insert occurs error:
INSERT INTO my_table ("some_id", "someother_id","description") VALUES (1751,
10907,'gdfddfg''gdfg');
The same occurs with backslash:
INSERT INTO my_table ("some_id", "someother_id","description") VALUES (1751,
10907,'gdfddfg//gdfg');
ERROR:  Syntax error near ''' at position 73LINE 2: ..., '2015-10-20
15:41:08.534645+02', 'my_user', $$'"some... 
   
^QUERY:  INSERT INTO history.public_my_table_2015_10(operation, event_time,
executed_by, new_value) VALUES('INSERT', '2015-10-20
15:41:08.534645+02', 'my_user', $$'"some_id"=>"1751",
"someother_id"=>"10907", "description"=>"gdfddfg'gdfg"'$$)CONTEXT:  PL/pgSQL
function insert() line 6 at EXECUTE statement




--
View this message in context: 
http://postgresql.nabble.com/Escaping-text-hstore-tp5870728.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Escaping text / hstore

[pinker]

Adrian Klaver-4 wrote
> Here is my very similar function:
> 
> CREATE OR REPLACE FUNCTION utility.archive_del_record() RETURNS trigger AS
> $BODY$
> DECLARE
>  tbl_name text := TG_TABLE_NAME || '_delete' ;
>  archive_row hstore := hstore(OLD.*);
>  user_name text := session_user;
> BEGIN
>  EXECUTE 'INSERT INTO ' ||quote_ident(tbl_name) ||
>  '(record_fld, del_ts, del_user)'
>  || ' VALUES('||quote_literal(archive_row)||', now(),' ||
>  quote_literal(user_name)||')';
> RETURN OLD;
> END;
> $BODY$
> LANGUAGE plpgsql SECURITY DEFINER;

Thank you Adrian you made my day!

I compared our functions and came out that the problem was only in quotes...
I have used 3 of them and with hstore one is enough.




--
View this message in context: 
http://postgresql.nabble.com/Escaping-text-hstore-tp5870728p5870762.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

[pinker]

I've tried to write audit trigger which fires only when data changed, so I
used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause as described in 
documentation
  . Should
this clause be independent from data type? because an error occurs when I'm
trying to modify row with point data type: *ERROR:  could not identify an
equality operator for type point*Example:
CREATE TABLE IF NOT EXISTS test1(  col1 POINT)WITH (OIDS =FALSE);INSERT INTO
test1 VALUES ('(0,1)');CREATE OR REPLACE FUNCTION test_update()  RETURNS
TRIGGER AS  $BODY$  BEGINRETURN NEW;  END;  $BODY$LANGUAGE plpgsql
VOLATILECOST 100;CREATE TRIGGER trigger_update_testAFTER UPDATEON test1FOR
EACH ROWWHEN ((old.* IS DISTINCT FROM new.*))EXECUTE PROCEDURE
test_update();UPDATE test1 SET col1 = '(9,0)';




--
View this message in context: 
http://postgresql.nabble.com/Shouldn-t-WHEN-OLD-IS-DISTINCT-FROM-NEW-clause-be-independent-from-data-type-tp5866276.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Re: Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

[pinker]

Yes I will try to do something else like casting, hashing or using another
operator but in documentation this example stands as a model audit trigger
without any warnings...




--
View this message in context: 
http://postgresql.nabble.com/Shouldn-t-WHEN-OLD-IS-DISTINCT-FROM-NEW-clause-be-independent-from-data-type-tp5866276p5866286.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] SELECT clause without parameters

[pinker]

I would like to ask what's the reason of change SELECT behaviour.
In distributions below 9.4 SELECT without any parameters caused a syntax
error and now gives empty set. Was it made for some bigger aim ? :)

for instance 8.4:
postgres=# select version();
   version  
 
-
 PostgreSQL 8.4.17 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 32-bit
(1 wiersz)

postgres=# select
postgres-# ;
ERROR:  syntax error at or near ;
LINIA 2: ;


and 9.4:
psql (9.4.4)
Type help for help.

postgres=# select
postgres-# ;
--
(1 row)






--
View this message in context: 
http://postgresql.nabble.com/SELECT-clause-without-parameters-tp5862355.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

[pinker]

Adrian Klaver-4 wrote
 That is sort of dangerous:) As an example, do you really want --inserts 
 or --column-inserts, they really slow down a restore.

I know, but this time I need it more for versioning/demo version
prepariation so performence isn't important at all, what I care about is
only ddl and dictionary data.




--
View this message in context: 
http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5857333.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

[pinker]

Tom Lane-2 wrote
 $ time pg_dump -f /z/q regression
 pg_dump: [archiver] could not open output file /z/q: No such file or
 directory
 
 real0m1.164s
 user0m0.062s
 sys 0m0.034s
 
 However, I don't see that with any of the non-plain-text output formats:

In my case this is:

pg_dump: reading rewrite rules
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
pg_dump: [archiver] could not open output file /djsklj.sql: No such file
or directory

real1m6.841s
user0m0.412s
sys 0m0.068s



--
View this message in context: 
http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5857335.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

[pinker]

Adrian Klaver-4 wrote
 I was talking about when you ran the backup from pgAdmin. The backup 
 dialog has a file selector button to select the path/file you want to 
 back up to. So I was wondering how you got an incorrect path in the 
 first place? Then it  occurred to me you might not have have used the 
 selector, but directly pasted the path into the file field, is that the 
 case?
 
 Have you tried doing the backup directly from the command line?
 
 -- 
 Sent via pgsql-general mailing list (

 pgsql-general@

 )
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

I'm doing it directly from command line, just took command with options from
pgadmin window (because i don't like to read the whole documentation page
with pg_dump options), but always running it from bash, changing only
db-name, filepath, port etc. (in bash console)




--
View this message in context: 
http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856999.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

[pinker]

John McKown wrote
 ​What version of PostgreSQL? What OS? What was the command line?

*OS*: Linux OptiPlex-760 3.8.0-19-generic #29-Ubuntu SMP Wed Apr 17 18:16:28
UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
Linux Mint 15 Olivia

*pg_dump*: pg_dump (PostgreSQL) 9.4.1

*command line:*
opt/PostgreSQL/9.4/bin/pg_dump --host localhost --port 000 --username 000
--no-password  --format plain --no-owner --create --clean --encoding UTF8
--inserts --column-inserts --no-privileges --no-tablespaces --ignore-version
--verbose --no-unlogged-table-data --file 000-$(date +%Y%m%d_%H%M).sql
--schema 000 db-000




--
View this message in context: 
http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856930.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

[pinker]

I made mistake in a filename in pg_dump command, i.e. have used path from
another server, which not exists on this one. pg_dump instead of checking
permissions / existence of output file first dumped the whole database and
at the end (after some time ... ) threw an error:

(...)
pg_dump: saving database definition
pg_dump: [archiver] could not open output file
/home/.../dum-_20150707_1059.sql: No such file or directory

Is it correct behavior? Why wasting so much time and resources leaving
checking the output file at the last moment?



--
View this message in context: 
http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

[pinker]

Adrian Klaver-4 wrote
 So how did you get the wrong file name then, the pgAdmin backup dialog 
 has a file selector?
 
 Or did you cut and paste into the file field?

Anyway, shouldn't postgresql first check if dump can be saved? and then
start doing it?



--
View this message in context: 
http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856979.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

[pinker]

Adrian Klaver-4 wrote
 So this was not the actual command you ran as I see no path specification.
 
 Some notes:
 
 1) plain format is the default so it does not need to specified.
 
 2) Either --inserts or --column-inserts not both. FYI this really slows 
 down the restore process.
 
 3) --ignore-version is deprecated in 9.4 and is itself ignored.


command was copied from pgadmin, I've changed only parameters.
I'm doing dump from my computer where I have pg_dump 9.4.1 installed from db
which is 8.4.



--
View this message in context: 
http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856940.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

[pinker]

Adrian Klaver-4 wrote
 So how did you get the wrong file name then, the pgAdmin backup dialog 
 has a file selector?
 
 Or did you cut and paste into the file field?

I've cut, pasted and changed parameters.





--
View this message in context: 
http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856969.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Error prone compilation of stored procedure

[pinker]

What's the reason behind very tolerant error checking during stored
procedure compilation?
Why PostgreSQL allows using variable (j_var) that doesn't exists? It isn't
column name or isn't declared anywhere. Like in example below:
 
CREATE OR REPLACE FUNCTION test()
  RETURNS int AS
$BODY$
BEGIN

select 1 WHERE 1  j_var;
RETURN 2;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



--
View this message in context: 
http://postgresql.nabble.com/Error-prone-compilation-of-stored-procedure-tp5856699.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error prone compilation of stored procedure

[pinker]

Andreas Kretschmer-2 wrote
 pinker lt;

 pinker@

 gt; wrote: What's the reason behind very tolerant error checking
 during stored procedure compilation?they are not compiled but interpreted
 at runtime.Andreas

Documentation says:  during the compilation of a function
http://www.postgresql.org/docs/9.4/static/plpgsql-development-tips.html  I
know it's not classic one, but still this word is used in this context.



--
View this message in context: 
http://postgresql.nabble.com/Error-prone-compilation-of-stored-procedure-tp5856699p5856707.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Error prone compilation of stored procedure

[pinker]

Pavel Stehule wrote
 PLpgSQL doesn't check a identifiers inside embedded SQL before execution.
 In this case j_var can be theoretically some SQL identifiers - the
 possibility or impossibility is not know in function validation stage.
 
 It has some plus and minus points. The plus - there are not strong
 dependency between database objects and PL code. The minus - lot of bugs
 are not detected in validation stage. But this issue can be solved by
 plpgsql_check extension https://github.com/okbob/plpgsql_check/

Thank you for the link to extension. 
Another minus is that my colleagues which use to work on oracle think that
postgresql is at least one league below oracle.



--
View this message in context: 
http://postgresql.nabble.com/Error-prone-compilation-of-stored-procedure-tp5856699p5856708.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error prone compilation of stored procedure

[pinker]

lup wrote
 And are your colleagues offering to pay for an Oracle license?

I would prefer to prove them it's not necessary :)



--
View this message in context: 
http://postgresql.nabble.com/Error-prone-compilation-of-stored-procedure-tp5856699p5856734.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

Adrian Klaver-4 wrote
 Also per Kevin Grittner and Tom Lane there 
 is a Nabble issue at work where the list here is not seeing all the 
 information. Example:

It's because I edited some post. Then is visible only on nabble because
edited version isn't sent back to the mailing list.


Adrian Klaver-4 wrote
 Also per Kevin Grittner we are looking at a moving target, so some sort 
 of information about current state would be helpful.

I'm doing pg_dump from snapshot - on production that isn't possible. Today
at night my colleague will run vacuum full verbose on this table in
production environment, so we will see if this problem occurs only on
snapshot or was replicated from production.



--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841434.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

Yes I have. Environment I'm working on is production snapshot, so there is no
active transactions that could block those blocks from being removed...





--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841085.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

I did: select pg_cancel_backend(pid) from pg_stat_activity where usename 
'mine';

and then tried again vacuum full:

INFO:  vacuuming my_table
INFO:  my_table: found 0 removable, 3043947 nonremovable row versions in
37580 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 1.07s/1.56u sec elapsed 3.24 sec.
Query returned successfully with no result in 6436 ms.


PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2
20140120 (Red Hat 4.8.2-16), 64-bit

It was flash copy snapshot



--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841110.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

Vick Khera wrote
 On Fri, Mar 6, 2015 at 5:59 AM, pinker lt;

 pinker@

 gt; wrote:
 
 I have deleted a large number of records from my_table, which originally
 had
 288 MB. Then I ran vacuum full to make the table size smaller. After this
 operation size of the table remains the same, despite of the fact that
 table

 
 If your remaining records were in say, block 2 and block 1, then the
 blocks in between won't be returned to the system.
 
 You could achieve your fix by running cluster on the table, which will
 rewrite the table in-place.  There are also scripts out there that do
 in-place compaction by running targeted updates and vacuum commands to get
 the empty pages at the end of the files truncated off the file.

I have tried many combinations of VACUUM, VACUUM FULL and CLUSTER after
first failed VACUUM FULL and nothing works...




--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841086.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

 So there are no longer any dead rows being left behind, right?
 
 Why are we still discussing this?  Do you have some other question?

There are no dead rows, but postgres still cannot reuse the space because of
3043947 nonremovable row versions ..

INFO:  vacuuming my_table
INFO:  my_table: found 0 removable, 3043947 nonremovable row versions in
37580 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 2.67s/1.59u sec elapsed 7.71 sec.
Query returned successfully with no result in 8319 ms.



--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841137.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

Adrian Klaver-4 wrote
 On 03/09/2015 07:08 AM, pinker wrote:
 I did: select pg_cancel_backend(pid) from pg_stat_activity where usename
 
 'mine';
 
 What makes you think that queries from usename = 'mine' are not important?
 
 Because on production I don't have access to this table.
 
 Or to get back to the original request:
 
 What does select * from pg_stat_activity show?
 
 mainly idle connections and queries to tables in different schemas.
 query select * from pg_stat_activity where query ilike '%my_table%' shows
 nothing as well.
 
 Also did the queries actually get cancelled?
 Yes, but not all.
 

 and then tried again vacuum full:

 INFO:  vacuuming my_table
 INFO:  my_table: found 0 removable, 3043947 nonremovable row versions
 in
 37580 pages
 DETAIL:  0 dead row versions cannot be removed yet.
 CPU 1.07s/1.56u sec elapsed 3.24 sec.
 Query returned successfully with no result in 6436 ms.


 PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2
 20140120 (Red Hat 4.8.2-16), 64-bit

 It was flash copy snapshot
 
 So what does that mean?
 
 In other words detail the steps you took to get the snapshot.
 
 I would like to know as well. Sysadmin team manage it, I'll ask them, but
 as far I know it's matrix feature
 

 
 
 -- 
 Adrian Klaver

 adrian.klaver@

 
 
 -- 
 Sent via pgsql-general mailing list (

 pgsql-general@

 )
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general





--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841117.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

select * from pg_stat_activity where state ilike 'idle in transaction%' it's
empty.

And there is no relation between this table and the tables or functions
being queried? 

no...

If  snapshot is what I think it means, you might want to point them at: 

http://www.postgresql.org/docs/9.3/interactive/backup-file.html

hmmm, maybe it's because WAL is in another tablespace?



--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841123.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

and select txid_current_snapshot() - 1694632069:1694632069:

select distinct xmin::text::bigint from sms_actual_mapper  order by 1 desc
1694595273



--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841128.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

Query output is empty...



--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5840797.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] VACUUM FULL doesn't reduce table size

[pinker]

I have deleted a large number of records from my_table, which originally had
288 MB. Then I ran vacuum full to make the table size smaller. After this
operation size of the table remains the same, despite of the fact that table
contains now only 241 rows and after rewriting it in classic way: CREATE
TABLE new_table AS SELECT * FROM old_table - new_table size is 24kB.
What went wrong? And how can I remove those blocks?



--
View this message in context: 
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Spam on main page

[pinker]

You have spam on postgresql.org main page...
Jim Smith: Myśli o istotnych Szczegóły kwiatów dostawy online
It's not even proper polish :)



--
View this message in context: 
http://postgresql.nabble.com/Spam-on-main-page-tp5840406.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] infinite recursion detected in rules for relation

[pinker]

hmm I wanted to show only rows that was inserted today, so instead of
who='me' wanted to filter for instance where timestamp_column=CURRENT_DATE.

Yes, a view would be a solution but I thouhgt that's the case rules were
made for? Isn't it?



--
View this message in context: 
http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697p5837822.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] infinite recursion detected in rules for relation

[pinker]

Ok, but in this particular case I don't see any caveats and think that could
be classic case for rule to be used.
If it is almost never the correct solution why rules still exists at all?



--
View this message in context: 
http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697p5837867.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] infinite recursion detected in rules for relation

[pinker]

I wanted to set a rule:
CREATE RULE _RETURN ASON SELECT * from backend.testDO INSTEAD   
SELECT * FROM backend.test WHERE who='Me';
When I'm trying to do anything on the table I get following error:
ERROR:  infinite recursion detected in rules for relation
backend.test** Błąd **ERROR: infinite recursion detected
in rules for relation backend.testStan SQL: 42P17
Is there any way to avoid that? Maybe there exist some other approaches that
could be useful ?



--
View this message in context: 
http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Check if LDAP Authentication is used for user

[pinker]

Is there any way to check from SQL or psql level if for roles LDAP
Authentication is used? There is no information in pg_roles or pg_shadow.



--
View this message in context: 
http://postgresql.nabble.com/Check-if-LDAP-Authentication-is-used-for-user-tp5831817.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Check if LDAP Authentication is used for user

[pinker]

thank you for the answer. 



--
View this message in context: 
http://postgresql.nabble.com/Check-if-LDAP-Authentication-is-used-for-user-tp5831817p5831847.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Mistake in documentation? ANALYZE on partitioned tables

[pinker]

Hi!
As I have read in  documentation
http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html   that If
you are using manual VACUUM or ANALYZE commands, don't forget that you need
to run them on each partition individually. A command like: ANALYZE
measurement; will only process the master table.

But when I run ANALYZE VERBOSE orders; (test table with 12 partitions) I
get:

INFORMACJA:  analiza public.orders
INFORMACJA:  orders: przeskanowano 2747 z 2747 stron, zawierających 0
żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0 szacowanych
wszystkich wierszy
INFORMACJA:  analiza drzewa dziedziczenia public.orders
INFORMACJA:  orders: przeskanowano 2750 z 2750 stron, zawierających 0
żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0 szacowanych
wszystkich wierszy
INFORMACJA:  orders_2014_01: przeskanowano 418 z 418 stron, zawierających
0 żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0
szacowanych wszystkich wierszy
INFORMACJA:  orders_2014_02: przeskanowano 377 z 377 stron, zawierających
0 żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0
szacowanych wszystkich wierszy
INFORMACJA:  orders_2014_03: przeskanowano 418 z 418 stron, zawierających
0 żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0
szacowanych wszystkich wierszy
INFORMACJA:  orders_2014_04: przeskanowano 404 z 404 stron, zawierających
0 żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0
szacowanych wszystkich wierszy
INFORMACJA:  orders_2014_05: przeskanowano 418 z 418 stron, zawierających
0 żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0
szacowanych wszystkich wierszy
INFORMACJA:  orders_2014_06: przeskanowano 404 z 404 stron, zawierających
0 żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0
szacowanych wszystkich wierszy
INFORMACJA:  orders_2014_07: przeskanowano 317 z 317 stron, zawierających
0 żywych wierszy i 0 martwych wierszy; 0 wierszy w przykładzie, 0
szacowanych wszystkich wierszy


Abfrage war erfolgreich nach 353 ms. Keine Zeilen geliefert.

All partitions were processed...
So I guess: mistake in documentation?



--
View this message in context: 
http://postgresql.nabble.com/Mistake-in-documentation-ANALYZE-on-partitioned-tables-tp5829105.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Merge rows based on Levenshtein distance

[pinker]

There is nice extension in postgres:  fuzzystrmatch
http://www.postgresql.org/docs/9.3/static/fuzzystrmatch.html   I have used
to calculate the distance. From documetation:

SELECT levenshtein_less_equal('extensive', 'exhaustive',2);

You can use it then with your group by query.



--
View this message in context: 
http://postgresql.nabble.com/Merge-rows-based-on-Levenshtein-distance-tp5828841p5829111.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Optimal checkpoint_setting

[pinker]

Thank you for your answer, but I don't have access to this server any more
and have to just interpret and pick some parameters from test results, but
your advice about tar trick is something I'm gonna try for sure in next
test.

So, because I don't have any chance to do some more tests, should I change
checkpoint_segments parameter?
My colleagues prefer old setting as shown below, because of maintenance
reasons, but I still would like to convince them to much higher setting. 30
segments for machine like that seems to be too humble.
 
checkpoint_segments = 30
checkpoint_timeout = 8min

The rest of config looks like this:
shared_buffers=2GB 
temp_buffers=128MB 
max_files_per_process=1000 
work_mem=384MB
maintenance_work_mem=10240MB
effective_io_concurrency=1 
synchronous_commit=on 
wal_buffers=16MB
wal_writer_delay=200ms 
commit_delay=0 
commit_siblings=5
random_page_cost=1.0 
cpu_tuple_cost= 0.01 
effective_cache_size=450GB  
geqo_threshold=12
geqo_effort=5
geqo_selection_bias=2.0 
join_collapse_limit=8

Any ideas about rest of config? Maybe those connected with write operations?






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Optimal-checkpoint-setting-tp5822359p5822951.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Optimal checkpoint_setting

[pinker]

Hello All,
I have a brand new machine to tune:
x3750 M4, 4xE5-4640v2 2.2GHz; 512GBRAM (32x16GB), 4x300GB
SAS + SSD (Easy Tier) in RAID 10

What's particularly important now is to choose optimal configuration for
write operations. We have discussion about checkpoint_segments and
checkpoint_timeout parameters. Test, which was based on pg_replay, has shown
that the biggest amount of data is written when checkpoint_segments are set
to 10 000 and checkpoint_timeout to 30 min, but I'm worrying about amount of
time needed for crash recovery. On the other hand it's mighty machine :) and
I don't wanna go back with 30 segments.

I'd appreciate any helpful remark on that.

The rest of parameters from the test:
shared_buffers=2GB
temp_buffers=128MB
max_files_per_process=500
work_mem=256MB
maintenance_work_mem=128MB
effective_io_concurrency=50
synchronous_commit=local
wal_buffers=512kB
wal_writer_delay=1ms
commit_delay=100
commit_siblings=1000
random_page_cost=1.0
cpu_tuple_cost=2.0
effective_cache_size=500GB
geqo_threshold=20
geqo_effort=10
geqo_selection_bias=2.0
join_collapse_limit=16










--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Optimal-checkpoint-setting-tp5822359.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database block lifecycle

[pinker]

Jeff Janes wrote
 On Tuesday, August 12, 2014, pinker lt;

 pinker@

 gt; wrote:
 
 Do you ever plan on restarting this server?  Doing maintenance?  Applying
 security patches?
 
 Sure, I assumed when db is up and running, of course after first read from
 disk when whole data should be in RAM.
 
 
 More like b), but you are missing all the states that involve clean in
 shared_buffers, dirty in FS cache and such.
 
 Ok, so modified block is taken from shared_buffers or from RAM when
 needed, and is readed always from shared buffers?
 
 

 btw. 512MB if we assume up to 600 connection is a reasonable value?

 
Reasonable value for what?
 
 For normal server load.
 
 Cheers,
 
 Jeff





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Database-block-lifecycle-tp5814627p5814672.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Database block lifecycle

[pinker]

Yesterday I had an interesting discussion with my colleague about shared
buffers size for our new server. This machine (is dedicated for db) has got
512GB of RAM and database size is about 80GB, so he assumes that db will
never have to read from disk, so there is no point to adjust read ahead
setting, because every block gonna be read from RAM. As I've red in Greg
Smith book, once a block is changed it will be written to a disk and buffers
page is marked as clean, which would mean than changes occur in the same
page as before? What if dirty page doesn't have enough space for another row
and row has to be written to another page? Is it still occurs in RAM? If
that's true all updates of FSM occurs in RAM as well?

What about buffers_clean and pg_clog then? Are those maintained completely
in RAM as well without direct read from disk at all? 

To be precise, does the path to update and read updated row looks like a or
b?:
a). clean page (shared buffers) - dirty page (shared buffers) - to disk -
read from disk - shared buffers - query
b). clean page (shared buffers) - dirty page (shared buffers) - to disk
 dirty page (shared buffers) - clean page (shared buffers) - query

btw. 512MB if we assume up to 600 connection is a reasonable value?




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Database-block-lifecycle-tp5814627.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database block lifecycle

[pinker]

yes, I know the count is quite high. It is the max value we've 
estimated, but probably on average day it will be 100-200, and yes we 
use pgpool.


Am 13.08.2014 00:09, schrieb John R Pierce:

On 8/12/2014 2:41 PM, pinker wrote:

btw. 512MB if we assume up to 600 connection is a reasonable value?


thats an insanely high connection count, if you actually expect those 
connections to be executing concurrent queries, unless you have 
something north of 100 CPU cores.


you'd be much better to have a MUCH smaller connection count, and use 
a connection pooler such as pgbouncer, in transaction mode... let 600 
client htreads connect to the pooler, but have the pooler share maybe 
4X your CPU core/thread count of actual connections for transactions 
in progress.










--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database block lifecycle

[pinker]

Ok,  I wasn't precisely enough, you are right. It's brand new server, 
nothing is yet configured and we have not even os installed. The number 
was the overall count we expect for a whole cluster.


But the main question is: is it possible to completely avoid disk read 
if there is huge amount of RAM available?



Am 13.08.2014 00:39, schrieb John R Pierce:

On 8/12/2014 3:29 PM, pinker wrote:
yes, I know the count is quite high. It is the max value we've 
estimated, but probably on average day it will be 100-200, and yes we 
use pgpool. 



if you're using a pooler, then why would you be using 200 concurrent 
connections, unless you have a 50 or 100 CPU cores/threads ?


if you have 1000 transactions to execute on a 32 core server, and you 
try and do 200 at once, it will take longer than if you do 64 at a 
time and let the rest queue up.






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general