Re: [GENERAL] json indexing and data types

[Oleg Bartunov]

On Wed, Dec 2, 2015 at 11:48 AM, Kaare Rasmussen  wrote:

> Hi
>
> As json essentially only has three basic data types, string, int, and
> boolean, I wonder how much of this - to index, search, and sort on
> unstructured data -  is possible. I guess part of the answer would be
> 'jsquery and vodka', but let me describe the problem first.
>
> The basics is, that I have a column with what is essentially json data; a
> number of data structures of different depths. Perhaps 10 - 30 top levels,
> and probably no more than 3, max 4 levels deep. In total there are some
> hundred thousands of rows in each table. It would probably be best stored
> as jsonb. Right now it's text, because it's only used by the application
> itself.
>
> It would be incredibly useful to add an index to this column, and to be
> able to search, using the index, on arbitrary elements. This part seems
> already there, with jsquery.
>
> The hard part is that some of the data items really have another type.
> There are dates and floating points, as the most important ones. And the
> really hard part is that sorting and range searches are important,
> especially for these two types. Having dates is iso-format, and
> left-padding floats with zeros is a low tech solution, and especially the
> latter is not very efficient.
>
> The solution might be to add functional indexes for these data items, but
> it's cumbersome and not easily maintainable. If a one-stop solution is in
> the works, or already there, it could save a lot of time.
>
>
This is known problem, that's why we stop developing jsquery and are
working on sql-level query language for jsonb, then you'll use all power
and extendability of SQL.  The idea is to use power of subselects and
unnest to unroll jsonb to sql level.
There is presentation at pgconf.eu on this
https://wiki.postgresql.org/images/4/4e/Createam.pdf, see slide #27

But I'm afraid it'll come to 9.6.




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

[GENERAL] Memory Leak executing small queries without closing the connection

[Gerhard Wiesinger]

Hello,

We are running PostgreSQL 9.4.5 on FreeBSD 10.1 and have multiple worker 
processes connected via persistent connections to PostgreSQL, they 
perform just simple queries with SELECT on primary keys and simple 
INSERTS/UPDATES. Normally nearly all the workers are idle but they still 
consume the maximum configured work mem on the PostgreSQL server and the 
memory is also resident. If some other queries get in we get into out of 
memory situations. So it looks like PostgreSQL has memory leaks.


I found a test scenario to reproduce it also on a newer FreeBSD 10.2 VM 
as well as in a Fedora 23 VM (both with PostgreSQL 9.4.5):


Executions in psql with one persisent connection:
-- Create the table
CREATE TABLE t_random AS SELECT s, md5(random()::text) FROM 
generate_Series(1,1) s;

-- Create the index
CREATE INDEX ON t_random(s);

-- Restart psql with a new connection:

-- Memory goes slighty up after each execution even after canceling:
-- Memory leak on FreeBSD 10.2/Fedora 23 and PostgreSQL 9.4.5 on cancel 
the query or multiple execution

SELECT * FROM t_random ORDER BY md5 LIMIT 10;

-- Therefore I created a function:
CREATE OR REPLACE FUNCTION execmultiplei(IN num int8)
RETURNS void AS $$
BEGIN
  -- RAISE NOTICE 'num=%', num;
  FOR i IN 1..num LOOP
PERFORM * FROM t_random WHERE s = i;
  END LOOP;
END;
$$  LANGUAGE plpgsql;

-- Test it several times
SELECT execmultiplei(1000);

-- Linux testing (FreeBSD is similar), relevant part is RES (resident 
memory):

  PID USER  PR  NIVIRTRESSHR S  %CPU %MEM TIME+ COMMAND
-- after startup of psql
26851 postgres  20   0 2363276   7432   6292 S   0.0  0.2 0:00.00 
postgres: postgres postgres [local] idle

-- Memory goes up, ok so far
26851 postgres  20   0 2365732 255152 253548 R  99.0  6.3 0:10.77 
postgres: postgres postgres [local] SELECT
26851 postgres  20   0 2365732 408464 406788 R 100.0 10.1 0:17.81 
postgres: postgres postgres [local] SELECT
26851 postgres  20   0 2365732 864472 862576 R 100.0 21.4 0:38.90 
postgres: postgres postgres [local] SELECT
-- Function execmultiplei and transaction terminated, but memory still 
allocated!!!
26851 postgres  20   0 2365732 920668 918748 S   0.0 22.7 0:41.40 
postgres: postgres postgres [local] idle

-- Calling it again
26851 postgres  20   0 2365732 920668 918748 R  99.0 22.7 0:46.51 
postgres: postgres postgres [local] SELECT

-- idle again, memory still allocated
26851 postgres  20   0 2365732 920668 918748 S   0.0 22.7 1:22.54 
postgres: postgres postgres [local] idle


Memory will only be released if psql is exited. According to the 
PostgreSQL design memory should be freed when the transaction completed.


top commands on FreeBSD: top -SaPz -o res -s 1
top commands on Linux: top -o RES d1

Config: VMs with 4GB of RAM, 2 vCPUs
shared_buffers = 2048MB # min 128kB
effective_cache_size = 2GB
work_mem = 892MB
wal_buffers = 8MB
checkpoint_segments = 16

Any ideas?

Thank you.

Ciao,
Gerhard



--
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] connections not getting closed on a replica

[FarjadFarid(ChkNet)]

Assuming you have at least 16GB of memory. These numbers on a good hardware 
server is not a real problem. On a bad server motherboard. Might as well use a 
standard PC. With 32GB I have tested 10 times more connections. Not to 
postgresql. 

I would investigate everything from bottom up. 

Also under Tcp/Ip the flow and validity of the transaction is guaranteed.  So I 
would look for other issues that is locking system. 

For a good motherboard design check out Intel's motherboards.

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Kevin Grittner
Sent: 11 December 2015 22:13
To: Carlo Cabanilla
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] connections not getting closed on a replica

On Fri, Dec 11, 2015 at 3:37 PM, Carlo Cabanilla  wrote:

> 16 cores

> a default pool size of 650, steady state of 500-600 server connections

With so many more connections than resources to serve them, one thing that can 
happen is that just by happen-stance enough processes become busy at one time 
that they start context switching a lot before they finish, leaving spinlocks 
blocked and causing other contention that slows all query run times.  This 
causes bloat to increase because some database transactions are left active for 
longer times.  If the client software and/or pooler don't queue requests at 
that point there will be more connections made because connections have not 
been freed because of the contention causing slowness -- which exacerbates that 
problem and leads to a downward spiral.  That can become so bad that there is 
no recovery until either the clients software is stopped or the database is 
restarted.

>> I don't suppose you have vmstat 1 output from the incident?  If it 
>> happens again, try to capture that.
>
> Are you looking for a stat in particular?

Not really; what I like about `vmstat 1` is how many useful pieces of 
information are on each line, allowing me to get a good overview of what's 
going on.  For example, if system CPU time is high, it is very likely to be a 
problem with transparent huge pages, which is one thing that can cause these 
symptoms.  A "write glut" can also do so, which can be controlled by adjusting 
checkpoint and background writer settings, plus the OS vm.dirty_* settings (and 
maybe keeping shared_buffers smaller than you otherwise might).
NUMA problems are not at issue, since there is only one memory node.

Without more evidence of what is causing the problem, suggestions for a 
solution are shots in the dark.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
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] postgresql 9.3 failover time

[Shay Cohavi]

*Hi,*
*I have postgresql 9.3 setup with 2 nodes (active/standby with streaming
replication & continuos archiving).*
*I have created 2 failover & failback script in order to perform a
switchover between the DB servers:*
*1. failover - create a trigger file in order to promote the new primary.*
*2. failback - perform a base backup as mentions in :*
*   a. start backup on the primary.*
*   b. stop the failed node .*
   didn't delete the DB directory on the failed node
*   c. performing rsync between the nodes.*
*   d.stopping the backup on the primary.*
*   e.performing rsync on the pg_xlog.*
*   f. creating a recovery.conf*

*standby_mode = 'on'*
*primary_conninfo = 'host=10.50.1.153 port=5432 user=usr password=pass'*
*restore_command = 'scp 10.50.1.153:/home/postgres/archive/%f %p'*
*trigger_file = '/home/postgres/databases/fabrix/trigger'*
*archive_cleanup_command = 'ssh 10.50.1.153
/home/postgres/pg_utils/archive_cleanup.sh %r'*

*   g. starting the failed node as secondary.*

*the switchover method:*
*1. stop the primary node.*
*2. promote the secondary node (failover.sh).*
*3. perform failback on the failed node.*
*4. start the failed node.*

*this method works great! *


*but if I perform multiple switchovers (>20), each time the new primary
gets promoted (trigger file) - it takes longer because it searches the
timelines on the archive. *

*for example:*

*[2015-12-12 20:35:10.769 IST] LOG:  trigger file found:
/home/postgres/databases/fabrix/trigger*
*[2015-12-12 20:35:10.769 IST] FATAL:  terminating walreceiver process due
to administrator command*
*scp: /home/postgres/archive/0094000200DC: No such file or
directory*
*[2015-12-12 20:35:10.893 IST] LOG:  record with zero length at 2/DC000168*
*[2015-12-12 20:35:10.893 IST] LOG:  redo done at 2/DC000100*
*scp: /home/postgres/archive/0094000200DC: No such file or
directory*
*scp: /home/postgres/archive/0093000200DC: No such file or
directory*
*scp: /home/postgres/archive/0092000200DC: No such file or
directory*
*.*
*.*
*.*

*scp: /home/postgres/archive/0091000200DC: No such file or
directory*
*scp: /home/postgres/archive/009200DC: No such file or
directory*
*scp: /home/postgres/archive/0095.history: No such file or directory*
*[2015-12-12 20:35:11.801 IST] LOG:  selected new timeline ID: 149*
*[2015-12-12 20:35:11.931 IST] LOG:  restored log file "0094.history"
from archive*
*[2015-12-12 20:35:12.173 IST] LOG:  archive recovery complete*
*[2015-12-12 20:35:12.181 IST] LOG:  database system is ready to accept
connections*
*[2015-12-12 20:35:12.181 IST] LOG:  autovacuum launcher started*

*this could take for a least 1 min.or more.*

*is there any way to skip the timeline searching in order to decrease the
promotion?*


*Thanks,*
*ShayC*