Re: [GENERAL] pg_restore out of memory

[Felipe Santos]

2016-07-12 8:54 GMT-03:00 Miguel Ramos <org.postgre...@miguel.ramos.name>:

>
> Às 12:32 de 12-07-2016, Felipe Santos escreveu:
>
>> I would try lowering max_connections to 50 and then set work_mem to 128MB.
>>
>> After that restart your server and retry the restore.
>>
>
> Ok, I will try restarting tonight.
> work_mem is the parameter I was most afraid of.
>
> I'll post some news in 24h...
>
>
> --
> Miguel Ramos
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

After the restore (being it a success or failure) don't forget to set the
parameters back to their original values (max_conn = 100 and work_mem=64MB).

BR

Re: [GENERAL] pg_restore out of memory

[Felipe Santos]

2016-07-12 8:25 GMT-03:00 Miguel Ramos :

>
> Hi,
>
> We have backed up a database and now when trying to restore it to the same
> server we get this:
>
> > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump
> > pg_restore: [custom archiver] out of memory
> > 12:09:56.58  9446.593u+1218.508s 24.3%  167+2589k  6+0io  0pf+0sw
> 6968822cs
>
>
> Some information about the application:
>
> - We have sensor data, including pictures, and number crunshing output,
> then so the large tables on this database have 319, 279, 111 and 26GB.
> Mostly on TOAST pages, but the 279GB one divides it evenly. This database
> is 765GB. We try to keep them under 4TB.
> - Transactions are large, some 100 MB at a time.
> - We also use PostGIS.
>
> About the server (dedicated):
>
> - FreeBSD 9.1-RELEASE #0 on AMD64
> - 16 GB of RAM
> - 8x3GB hardware RAID 10
> - 10TB slice for pgdata UFS-formatted and 32kB block
> - PostgreSQL 9.1.8 custom compiled to get 32kB blocks
> - Installed in 2013 with ~10 people working with it, 145 days uptime today.
>
> I found two relevant threads on the mailing-lists.
> The most recent one sugested that postgresql was being configured to use
> more memory than what's available.
> The older one sugested that the system limits on the size of the data or
> stack segments were lower than required.
>
> So here are some server parameters (relevant or otherwise):
>
> > max_connections = 100
> > shared_buffers = 4GB  -- 25% of RAM
> > temp_buffers = 32MB  -- irrelevant?
> > work_mem = 64MB
> > maintenance_work_mem = was 1G lowered to 256M then 64M
> > wal_buffers = -1  -- should mean 1/32 of shared_buffers = 128MB
> > checkpoint_segments = 64  -- WAL segments are 16MB
> > effective_cache_size = 8GB  -- irrelevant?
>
>
> I suspect that the restore fails when constructing the indices. After the
> process is aborted, the data appears to be all or most there, but no
> indices.
> So, all I did so far, was lowering maintenance_work_mem and it didn't work.
>
> System limits, as you can see, are at defaults (32GB for data and 512MB
> for stack):
>
> > # limit
> > cputime  unlimited
> > filesize unlimited
> > datasize 33554432 kbytes
> > stacksize524288 kbytes
> > coredumpsize unlimited
> > memoryuseunlimited
> > vmemoryuse   unlimited
> > descriptors  11095
> > memorylocked unlimited
> > maxproc  5547
> > sbsize   unlimited
> > swapsize unlimited
>
> Shared memory is configured to allow for the single shared memory segment
> postgresql appears to use, plus a bit of extra (8GB):
>
> > # ipcs -M
> > shminfo:
> > shmmax:   8589934592(max shared memory segment size)
> > shmmin:1(min shared memory segment size)
> > shmmni:  192(max number of shared memory identifiers)
> > shmseg:  128(max shared memory segments per process)
> > shmall:  2097152(max amount of shared memory in pages)
>
> And semaphores (irrelevant?)...
>
> > # ipcs -S
> > seminfo:
> > semmni:  256(# of semaphore identifiers)
> > semmns:  512(# of semaphores in system)
> > semmnu:  256(# of undo structures in system)
> > semmsl:  340(max # of semaphores per id)
> > semopm:  100(max # of operations per semop call)
> > semume:   50(max # of undo entries per process)
> > semusz:  632(size in bytes of undo structure)
> > semvmx:32767(semaphore maximum value)
> > semaem:16384(adjust on exit max value)
>
>
> I don't know what else to try.
> I lowered maintenance_work_mem without restarting the server.
> In some of the attempts, but not all, the restore was done while people
> were working.
>
> Each attempt takes 12 hours...
> We couldn't use the directory -Fd dump because it's postgresql 9.1.
> The original database is still on the server, this is a test restore.
>
> We have about one or two months of slack before we really need to remove
> them from the server to recover space.
>
>
> --
> Miguel Ramos
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


Hi Miguel,

I would try lowering max_connections to 50 and then set work_mem to 128MB.

After that restart your server and retry the restore.

Tell us if that helps.

Regards,

Re: [GENERAL] dumb question

[Felipe Santos]

2016-06-02 14:23 GMT-03:00 Steve Crawford :

> Something like:
>
> select max(id) from yourtable where sts=0 and ref_id is null;
>
> That assumes that ref_id is null. It would help to see your table
> structure and the query you tried that doesn't work. If ref_id is actually
> a character string then you might need ref_id='' or coalesce(ref_id,'')=''
> if it can be null or empty string.
>
> Cheers,
> Steve
>
>
> On Thu, Jun 2, 2016 at 10:16 AM, Steve Clark 
> wrote:
>
>> Hi List,
>>
>> I am a noob trying to do something that seems like it should be easy but
>> I can't figure it out.
>>
>> I have a table like so:
>>
>> id | ref_id | sts
>> --
>> 1  ||  0
>> 2  | 1  |  1
>> 3  ||  0
>> 4  ||  0
>> 5  | 4  |  1
>> 6  ||  0
>> 7  | 6  |  1
>>
>> I want to find the max(id) whose sts is 0 but whose id is not referenced
>> by ref_id.
>>
>> so the answer would be id=3.
>>
>> Thanks for any pointers,
>> Steve
>>
>> --
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>

I think sts=0 means ref_id is null

So, what I think he wants to achieve is:

select max(id) from yourtable where sts=0 and id not in (select ref_id from
yourtable);

Isn't it?

Re: [GENERAL] Postgresql Server Upgarde

[Felipe Santos]

2016-02-15 15:40 GMT-02:00 Melvin Davidson :

>
>
> On Tue, Feb 9, 2016 at 1:57 AM, subhan alimy  wrote:
>
>> Hello Everyone,
>>
>> I want to upgrade PostgreSQL 9.1 to PostgreSQL 9.4, currently my database
>> hold millions of records, shall anyone please help me the recommend steps
>> to
>> upgrade my servers without any technical impact.
>>
>> Thanks In Advance.
>>
>> --
>> View this message in context:
>> http://postgresql.nabble.com/Postgresql-Server-Upgarde-tp5886543.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
>>
>
>
> Please refer to the following documentation:
>
> http://www.postgresql.org/docs/9.4/static/pgupgrade.html
>
> CAVEAT EMPTOR: It is always advisable to have a valid pg_dump/backup
> before proceeding with pg_upgrade.
> pg_upgrade is faster than a reload, but in the event of unexplained
> problems, a backup is always good to have as a last resort.
> Also, practice upgrading in a development environment before attempting to
> do so in production.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>


Hi Subhan Alimy,

I would recommend that, in an environment apart from production (maybe your
DEV env), you should:

1. install PG 9.4 in a different dir than PG 9.1
2. create a new cluster, apart from dev/test/qa/etc in a new data dir
3. do a dump from the PG 9.1 cluster
4. restore the dump to the PG 9.4 cluster
5. test your application on the new cluster

Given that you have enough storage space on the production environment, you
could use the same approach to upgrade your prod database.

Regards,

Felipe

Re: [GENERAL] BRIN indexes

[Felipe Santos]

2016-01-28 16:33 GMT-02:00 Igor Neyman <iney...@perceptron.com>:

>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] *On Behalf Of *Felipe Santos
> *Sent:* Thursday, January 28, 2016 1:17 PM
> *To:* Joshua D. Drake <j...@commandprompt.com>
> *Cc:* Melvin Davidson <melvin6...@gmail.com>; David Rowley <
> david.row...@2ndquadrant.com>; pgsql-general@postgresql.org; Thomas
> Kellerer <spam_ea...@gmx.net>
> *Subject:* Re: [GENERAL] BRIN indexes
>
>
>
> "Further to the point, it is self defeating to have more than one BRIN
> index on the table if the columns involved would have mutually
> non-adjacent pages."
>
>
>
>Not really, if both columns are ordered, BRIN will work
>
>
>
> "Therefore, it actually would be good to state that in the documentation,
> even it were just a comment."
>
>
>
>It is = "BRIN is designed for handling very large tables in which
> certain columns have some natural correlation with their physical location
> within the table"
>
>Link: http://www.postgresql.org/docs/devel/static/brin-intro.html
>
>
>
>
>
> Also, I did some tests and here are the results I got:
>
>
>
> Query with no index = completion time 43s
>
> Same Query with BRIN = completion time 14s / index size 0,5 MB
>
> Same Query without BRIN and with BTREE = completion time 10s / index size
> 5.000,00 MB
>
>
>
> As you can see, BRIN can save 99% of disk space for just a slightly worse
> performance.
>
>
>
> It seems like a huge improvement, given that your data fits BRIN's use
> case.
>
>
>
> Felipe,
>
>
>
> What kind of queries you used in your test?
>
> Where they based on clustering columns?
>
>
>
> Regards
>
> Igor Neyman
>


Hello Igor,

I took the sample BRIN test from the new release's wiki and added the BTREE
test:
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#BRIN_Indexes

The results today may vary from the reported above but are still in the
same levels of performance gain:

brin_db=# CREATE TABLE orders (
brin_db(#  id int,
brin_db(#  order_date timestamptz,
brin_db(#  item text);
CREATE TABLE

brin_db=# INSERT INTO orders (order_date, item)
brin_db-#  SELECT x, 'dfiojdso'
brin_db-#  FROM generate_series('2000-01-01 00:00:00'::timestamptz,
'2015-03-01 00:00:00'::timestamptz,'2 seconds'::interval) a(x);
INSERT 0 239243401

brin_db=# \dt+ orders
List of relations
 Schema |  Name  | Type  |  Owner   | Size  | Description
++---+--+---+-
 public | orders | table | postgres | 12 GB |
(1 row)

brin_db=# EXPLAIN ANALYSE SELECT count(*) FROM orders WHERE order_date
BETWEEN '2012-01-04 09:00:00' and '2014-01-04 14:30:00';

 QUERY PLAN


--
---
 Aggregate  (cost=4108912.01..4108912.02 rows=1 width=0) (actual
time=81116.722..81116.722 rows=1 loops=1)
   ->  Seq Scan on orders  (cost=0.00..4106759.58 rows=860972 width=0)
(actual time=60173.531..78566.113 rows=31589101 loops=1)
 Filter: ((order_date >= '2012-01-04 09:00:00-02'::timestamp with
time zone) AND (order_date <= '2014-01-04 14:30:00-02'::timestamp with time
zone))
 Rows Removed by Filter: 207654300
 Planning time: 0.443 ms
 Execution time: 81118.168 ms
(6 rows)

brin_db=#  CREATE INDEX idx_order_date_brin
brin_db-#ON orders
brin_db-#USING BRIN (order_date);
CREATE INDEX

brin_db=# \di+ idx_order_date_brin
List of relations
 Schema |Name | Type  |  Owner   | Table  |  Size  |
Description
+-+---+--+++-
 public | idx_order_date_brin | index | postgres | orders | 432 kB |
(1 row)

brin_db=# EXPLAIN ANALYSE SELECT count(*) FROM orders WHERE order_date
BETWEEN '2012-01-04 09:00:00' and '2014-01-04 14:30:00';

  QUERY PLAN

--
-
 Aggregate  (cost=2408269.34..2408269.35 rows=1 width=0) (actual
time=14164.923..14164.923 rows=1 loops=1)
   ->  Bitmap Heap Scan on orders  (cost=326808.28..2328609.76
rows=31863832 width=0) (actual time=42.742..11430.562 rows=31589101 loops=1)
 Recheck Cond: ((order_date >= '2012-01-04 09:00:00-02'::timestamp
with time zone) AND (order_date <= '2014-01-04 14:30:00-02'::timestamp with
 time zone))
 Rows Removed by Index Recheck: 21907
 Heap Blocks: lossy=201344
 ->  Bitmap Index Scan on idx_order_date_brin
 (cost=0.00..3

Re: [GENERAL] BRIN indexes

[Felipe Santos]

2016-01-28 16:03 GMT-02:00 Joshua D. Drake :

> On 01/28/2016 09:41 AM, Melvin Davidson wrote:
>
>> So, IOW, and the answer to my question is yes, it should be insured that
>> all pages involved are physically adjacent (by design or by pre-sort)
>> before creating a BRIN on them.
>> Further to the point, it is self defeating to have more than one BRIN
>> index on the table if the columns involved would have mutually
>> non-adjacent pages.
>> Therefore, it actually would be good to state that in the documentation,
>> even it were just a comment.
>>
>
> BRIN indexes are best used on INSERT only tables with a sequence of
> numbers as a PK or indexed column that will be queried against. At least as
> I understand it.
>
> JD
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
>  +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


"Further to the point, it is self defeating to have more than one BRIN
index on the table if the columns involved would have mutually
non-adjacent pages."

   Not really, if both columns are ordered, BRIN will work

"Therefore, it actually would be good to state that in the documentation,
even it were just a comment."

   It is = "BRIN is designed for handling very large tables in which
certain columns have some natural correlation with their physical location
within the table"
   Link: http://www.postgresql.org/docs/devel/static/brin-intro.html


Also, I did some tests and here are the results I got:

Query with no index = completion time 43s
Same Query with BRIN = completion time 14s / index size 0,5 MB
Same Query without BRIN and with BTREE = completion time 10s / index size
5.000,00 MB

As you can see, BRIN can save 99% of disk space for just a slightly worse
performance.

It seems like a huge improvement, given that your data fits BRIN's use case.

Re: [NOVICE] [SQL] [GENERAL] JSON TO POSTGRE TABLE

[Felipe Santos]

Hi,
 I want to ask a question about json index.
 Can I make a index on a field of a json column?
 e.g, for table 'json_tab' you given, can I run something like create
 index 'json_index' on json_tab::key?



Yes.

Here it is an example:
http://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3

Also, in 9.4 we got JSONB data type, which may enhance JSON fields
performance.