Re: R: [GENERAL] DB on mSATA SSD
On 4/23/15 8:36 AM, Job wrote: Hello, thank you first of all for your wonder help! Tomas, regarding: There are ways to make the writes less frequent, both at the database and OS level. We don't know what's your PostgreSQL config, but making the checkpoints less frequent and tuning the kernel/mount options may help a lot. We can raise up checkpoints, at kernel-side which options do you suggest to tune for minimize disk writing? Certainly disable atime updates if you haven't already. Having a long checkpoint period is somewhat similar to running in a ram disk and doing periodic backups. BTW, if you want to try using backups, I recommend you setup actual PITR archiving to the SSD. That will write data sequentially and in larger chunks, which should help the SSD better deal with the writes. This will give you more control over how much data you lose during a crash. Though, if all you do is a single large update once a day you're probably better off just taking a backup right after the update. I would also look at the backup size and recovery time of pg_dump vs PITR or a filesystem snapshot; it could be significantly smaller. It might take longer to restore though. BTW, if you go the ramdisk route you should turn off fsync; there's no point in the extra calls to the kernel. Only do that if the ENTIRE database is in a ramdisk though. We have a table, about 500Mb, that is updated and written every day. When machines updates, table is truncated and then re-populated with pg_bulk. But i think we strongly writes when importing new data tables.. That depends on how much data has actually changed. If most of the data in the table is changed then truncate and load will be your best bet. OTOH if relatively little of the data has changed you'll probably get much better results by loading the data into a loading table and then updating changed data, deleting data that shouldn't be there anymore, and inserting new data. You definitely want the loading table to not be on SSD, and to be unlogged. That means it needs to go in a tablespace on a ram disk. True temporary tables (CREATE TEMP TABLE) are not logged, but I don't know that that will work well with pg_bulk. You can use a real table with the unlogged option to the same effect (though, I'm not sure if unlogged is available in 8.4). You also need to consider the indexes. First, make absolutely certain you need all of them. Get rid of every one that isn't required. Second, you need to test the amount of data that's written during an update with the indexes in place *and doing a subsequent VACCUM* compared to dropping all the indexes and re-creating them. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] DB on mSATA SSD
On Thu, Apr 23, 2015 at 9:36 AM, Job j...@colliniconsulting.it wrote: We have a table, about 500Mb, that is updated and written every day. When machines updates, table is truncated and then re-populated with pg_bulk. But i think we strongly writes when importing new data tables.. so this is static data you update once per day from some external source? seems like a good candidate to put into RAM if you have enough. make sure your ram disk is not backed by swap, though. how and how much do you query it? perhaps you don't even need indexes if it is on SSD or RAM disk.
Re: R: [GENERAL] DB on mSATA SSD
On 04/23/15 15:36, Job wrote: Hello, thank you first of all for your wonder help! Tomas, regarding: There are ways to make the writes less frequent, both at the database and OS level. We don't know what's your PostgreSQL config, but making the checkpoints less frequent and tuning the kernel/mount options may help a lot. We can raise up checkpoints, at kernel-side which options do you suggest to tune for minimize disk writing? You may make the pdflush configuration less aggressive, but that may not help with your workload. You should use TRIM (of fstrim regularly), move /tmp into a tmpfs and don't put swap on the SSD. We have a table, about 500Mb, that is updated and written every day. When machines updates, table is truncated and then re-populated with pg_bulk. But i think we strongly writes when importing new data tables.. In that case the checkpoint optimizations or kernel tuning probably won't help much. But if you can easily recreate the database, and it fits into RAM, then you can just place it into a tmpfs. Here is why we tought putting some tables into ramdrive... Well, technically you can do that, but don't expect the database to work after a crash or a reboot. You might keep a snapshot of the database (e.g. using pg_basebackup), and use it to 'seed' the database after a server restart. But don't expect the database to start without a tablespace that got lost because of being placed in a tmpfs or something. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- 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] function returning a merge of the same query executed X time
Thanks Geoff for your idea but my query return something different each time I call it. This way, select row_number()over() as id,q.* from (select sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q CROSS JOIN generate_series(1, 3) the quey is executed only 1 time. Thanks Marc De : gwinkl...@gmail.com [mailto:gwinkl...@gmail.com] De la part de Geoff Winkless Envoyé : 22 avril 2015 11:22 À : Marc-André Goderre Cc : 'pgsql-general@postgresql.org' (pgsql-general@postgresql.org) Objet : Re: [GENERAL] function returning a merge of the same query executed X time Can you not just CROSS JOIN it to generate_series(1, 8)? On 22 April 2015 at 14:14, Marc-André Goderre magode...@cgq.qc.ca wrote: Hi all, I'm having difficulties to create a function that should execute X time the same query and return their results as a single table. I tried this way but it don't work : Thanks to help. create function cm_get_json_loop_info( IN profile integer, IN distance double precision DEFAULT 10, IN x_start double precision DEFAULT 0, IN y_start double precision DEFAULT 0, IN nbr integer default 1) returns setof json as $BODY$ declare jsona json; BEGIN for json a i in 1.. nbr loop select row_to_json(q) from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q return next jsona end loop; return jsona; Marc -- 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] Moving Specific Data Across Schemas Including FKs
I have the need to move a specific set of data from one schema to another. These schemas are on the same database instance and have all of the same relations defined. The SQL to copy data from one table is relatively straightforward: INSERT INTO schema_b.my_table SELECT * FROM schema_a.my_table WHERE ... What I am trying to figure out is that if I also have other relations that have foreign keys into the data I am moving, how would I also move the data from those relations and maintain the FK integrity? The tables are setup to use BIGSERIAL values for the id column which is the primary key, and the foreign keys reference these id columns. Ideally each schema would use it's own serial for the ID values, but I'm open to clever alternatives. I am using PG 9.3.5 (Amazon RDS), but with an option to move to 9.4 should the situation call for it. thanks --Cory
Re: [GENERAL] Moving Specific Data Across Schemas Including FKs
On Apr 23, 2015, at 10:09 AM, Cory Tucker cory.tuc...@gmail.com wrote: I have the need to move a specific set of data from one schema to another. These schemas are on the same database instance and have all of the same relations defined. The SQL to copy data from one table is relatively straightforward: INSERT INTO schema_b.my_table SELECT * FROM schema_a.my_table WHERE ... Would ALTER TABLE ... SET SCHEMA do what you need? A schema is mostly just a name space, so there's no need to create new tables or copy data around. Cheers, Steve What I am trying to figure out is that if I also have other relations that have foreign keys into the data I am moving, how would I also move the data from those relations and maintain the FK integrity? The tables are setup to use BIGSERIAL values for the id column which is the primary key, and the foreign keys reference these id columns. Ideally each schema would use it's own serial for the ID values, but I'm open to clever alternatives. I am using PG 9.3.5 (Amazon RDS), but with an option to move to 9.4 should the situation call for it. thanks --Cory -- 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] Moving Specific Data Across Schemas Including FKs
On 23/04/2015 18:09, Cory Tucker wrote: I have the need to move a specific set of data from one schema to another. These schemas are on the same database instance and have all of the same relations defined. The SQL to copy data from one table is relatively straightforward: INSERT INTO schema_b.my_table SELECT * FROM schema_a.my_table WHERE ... What I am trying to figure out is that if I also have other relations that have foreign keys into the data I am moving, how would I also move the data from those relations and maintain the FK integrity? I'd create the tables in the new schema without the FK constraints, copy the data, then add the constraints afterwards. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Moving Specific Data Across Schemas Including FKs
On 23/04/2015 19:08, Raymond O'Donnell wrote: On 23/04/2015 18:09, Cory Tucker wrote: I have the need to move a specific set of data from one schema to another. These schemas are on the same database instance and have all of the same relations defined. The SQL to copy data from one table is relatively straightforward: INSERT INTO schema_b.my_table SELECT * FROM schema_a.my_table WHERE ... What I am trying to figure out is that if I also have other relations that have foreign keys into the data I am moving, how would I also move the data from those relations and maintain the FK integrity? I'd create the tables in the new schema without the FK constraints, copy the data, then add the constraints afterwards. Meant to add, you'll also need to do select setval(...); on the sequence(s) in the new schema supplying the ID values, to set them to something higher than any extant values copied in from the old schema... but I'm sure you thought of that. :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] BDR Across Distributed Nodes
I'm starting to test BDR, and I've followed the quickstart included in the documentation successfully. The problem I'm encountering is when two servers are on different hosts, which is not covered in the documentation. Node1 is 10.0.0.1, node2 is 10.0.0.2, but when I try to connect from node2: demo=# SELECT bdr.bdr_group_join( local_node_name := 'node2', node_external_dsn := 'port=5432 dbname=demo', join_using_dsn := 'port=5432 dbname=demo host=10.0.0.1' ERROR: node identity for node_external_dsn does not match current node when connecting back via remote DETAIL: The dsn '' connects to a node with identity (6140654556124456820,1,16385) but the local node is (6140654709151998583,1,16385) HINT: The 'node_external_dsn' parameter must refer to the node you're running this function from, from the perspective of the node pointed to by join_using_dsn If I add node2 host entry: demo=# SELECT bdr.bdr_group_join( local_node_name := 'node2', node_external_dsn := 'port=5432 dbname=demo host=10.0.0.2', join_using_dsn := 'port=5432 dbname=demo host=10.0.0.1' ); FATAL: could not connect to the server in non-replication mode: could not connect to server: Connection refused Is the server running on host 10.0.0.2 and accepting TCP/IP connections on port 5432? DETAIL: dsn was: port=5432 dbname=demo host=10.0.0.2 fallback_application_name='bdr (6140654709151998583,1,16385,):bdrnodeinfo' justgive=# select bdr.bdr_version(); bdr_version -- 0.9.0-2015-03-24-f36ee65 (1 row) Does anybody happen to have any examples of calling bdr.bdr_group_join when there are remote IPs involved? - Billy
Re: [GENERAL] Moving Specific Data Across Schemas Including FKs
On Thu, Apr 23, 2015 at 10:27 AM Steve Atkins st...@blighty.com wrote: On Apr 23, 2015, at 10:09 AM, Cory Tucker cory.tuc...@gmail.com wrote: I have the need to move a specific set of data from one schema to another. These schemas are on the same database instance and have all of the same relations defined. The SQL to copy data from one table is relatively straightforward: INSERT INTO schema_b.my_table SELECT * FROM schema_a.my_table WHERE ... Would ALTER TABLE ... SET SCHEMA do what you need? A schema is mostly just a name space, so there's no need to create new tables or copy data around. Cheers, Steve If I were moving all of the contents from these table(s) then it might work, but I only want to move a specific selection of it based on a where clause. --Cory What I am trying to figure out is that if I also have other relations that have foreign keys into the data I am moving, how would I also move the data from those relations and maintain the FK integrity? The tables are setup to use BIGSERIAL values for the id column which is the primary key, and the foreign keys reference these id columns. Ideally each schema would use it's own serial for the ID values, but I'm open to clever alternatives. I am using PG 9.3.5 (Amazon RDS), but with an option to move to 9.4 should the situation call for it. thanks --Cory -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] DB on mSATA SSD
Additional things to consider for decreasing pressure on the cheap drives: - Another configuration parameter to look into is effective_io_concurrency. For SSD we typically set it to 1 io per channel of controller card not including the RAID parity drives. If you decrease this value PostgreSQL will not try to initiate as many parallel io operations. http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html - A strategy Instagram used is to (manually) vertically partition tables that have some, but not all, columns updated frequently. When PostgreSQL updates a data value for a column it writes a new copy of the entire row and marks the original row as garbage. If you have tables with many rows but many of them are updated infrequently (Instagram's example was a table with user information where last login was updated very frequently but other information about the user was rarely updated) you could split the frequently updated columns into a separate table to reduce io. However note that PostgreSQL does not have features to support vertical partitioning directly so to employ this technique you would need to manually partition and update your SQL code accordingly. - Also check into your WAL and logging settings to make sure you aren't writing more to them than you need to. *Will J Dunn* On Thu, Apr 23, 2015 at 10:38 AM, Jim Nasby jim.na...@bluetreble.com wrote: On 4/23/15 8:36 AM, Job wrote: Hello, thank you first of all for your wonder help! Tomas, regarding: There are ways to make the writes less frequent, both at the database and OS level. We don't know what's your PostgreSQL config, but making the checkpoints less frequent and tuning the kernel/mount options may help a lot. We can raise up checkpoints, at kernel-side which options do you suggest to tune for minimize disk writing? Certainly disable atime updates if you haven't already. Having a long checkpoint period is somewhat similar to running in a ram disk and doing periodic backups. BTW, if you want to try using backups, I recommend you setup actual PITR archiving to the SSD. That will write data sequentially and in larger chunks, which should help the SSD better deal with the writes. This will give you more control over how much data you lose during a crash. Though, if all you do is a single large update once a day you're probably better off just taking a backup right after the update. I would also look at the backup size and recovery time of pg_dump vs PITR or a filesystem snapshot; it could be significantly smaller. It might take longer to restore though. BTW, if you go the ramdisk route you should turn off fsync; there's no point in the extra calls to the kernel. Only do that if the ENTIRE database is in a ramdisk though. We have a table, about 500Mb, that is updated and written every day. When machines updates, table is truncated and then re-populated with pg_bulk. But i think we strongly writes when importing new data tables.. That depends on how much data has actually changed. If most of the data in the table is changed then truncate and load will be your best bet. OTOH if relatively little of the data has changed you'll probably get much better results by loading the data into a loading table and then updating changed data, deleting data that shouldn't be there anymore, and inserting new data. You definitely want the loading table to not be on SSD, and to be unlogged. That means it needs to go in a tablespace on a ram disk. True temporary tables (CREATE TEMP TABLE) are not logged, but I don't know that that will work well with pg_bulk. You can use a real table with the unlogged option to the same effect (though, I'm not sure if unlogged is available in 8.4). You also need to consider the indexes. First, make absolutely certain you need all of them. Get rid of every one that isn't required. Second, you need to test the amount of data that's written during an update with the indexes in place *and doing a subsequent VACCUM* compared to dropping all the indexes and re-creating them. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Invalid memory alloc
Hello, I'm processing a 100Million row table. I get error message about memory and I'ld like to know what can cause this issue. ... psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104855000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104856000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104857000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: invalid memory alloc request size 1677721600 psql:/home/ubuntu/create_topo.sql:12: NOTICE: UPDATE public.way_noded SET source = 88374866,target = 88362922 WHERE id = 142645362 pgr_createtopology FAIL (1 row) The server has a 10Gb of shared_buffer. Do you thing this quantity of memory allowed should normaly be enough to process the data? Thanks Marc -- 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] Invalid memory alloc
On 23-04-2015 16:55, Marc-André Goderre wrote: Hello, I'm processing a 100Million row table. I get error message about memory and I'ld like to know what can cause this issue. ... psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104855000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104856000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104857000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: invalid memory alloc request size 1677721600 psql:/home/ubuntu/create_topo.sql:12: NOTICE: UPDATE public.way_noded SET source = 88374866,target = 88362922 WHERE id = 142645362 pgr_createtopology FAIL (1 row) The server has a 10Gb of shared_buffer. Do you thing this quantity of memory allowed should normaly be enough to process the data? Thanks Marc My question would sound stupid... you have 10Gb shared buffer, but how much physical memory on this server? How have you configured the kernel swappines, overcommit_memoryt, overcommit_ratio? Have you set anything different in shmmax or shmall? Edson -- 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] Moving Specific Data Across Schemas Including FKs
On 4/23/15 1:08 PM, Raymond O'Donnell wrote: What I am trying to figure out is that if I also have other relations that have foreign keys into the data I am moving, how would I also move the data from those relations and maintain the FK integrity? I'd create the tables in the new schema without the FK constraints, copy the data, then add the constraints afterwards. You could also deffer the constraints, but that's probably going to be a lot slower. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Invalid memory alloc
Jim Nasby jim.na...@bluetreble.com writes: We need more information from the OP about what they're doing. Yeah. Those NOTICEs about nnn edges processed are not coming out of anything in core Postgres; I'll bet whatever is producing those is at fault (by trying to palloc indefinitely-large amounts of memory as a single chunk). regards, tom lane -- 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] Invalid memory alloc
Hello, I'm processing a 100Million row table. I get error message about memory and I'ld like to know what can cause this issue. ... psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104855000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104856000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: 104857000 edges processed psql:/home/ubuntu/create_topo.sql:12: NOTICE: invalid memory alloc request size 1677721600 psql:/home/ubuntu/create_topo.sql:12: NOTICE: UPDATE public.way_noded SET source = 88374866,target = 88362922 WHERE id = 142645362 pgr_createtopology FAIL (1 row) The server has a 10Gb of shared_buffer. Do you thing this quantity of memory allowed should normaly be enough to process the data? Thanks Marc Hi, what version of Postgres are you using? Any extensions? I guess you're using PostGIS, right? This error indicates something is trying to allocate 1600 MB of memory in the backend - that should never happen, as data chunks that are larger than 1 GB are broken down in smaller pieces. I hope you're not suffering data corruption, what happens if you do select * from public.way_noded where id = 142645362 ? Any other hints? Log messages (from Linux or the postgres backend)? Bye, Chris. -- 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] Invalid memory alloc
On 4/23/15 3:15 PM, Edson Richter wrote: My question would sound stupid... you have 10Gb shared buffer, but how much physical memory on this server? How have you configured the kernel swappines, overcommit_memoryt, overcommit_ratio? Have you set anything different in shmmax or shmall? I don't think this is an OS thing at all. That error messages is pretty associated with this code: #define MaxAllocSize((Size) 0x3fff) /* 1 gigabyte - 1 */ #define AllocSizeIsValid(size) ((Size) (size) = MaxAllocSize) If malloc failed you'd get an actual out of memory error, not a notice. We need more information from the OP about what they're doing. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] BDR Across Distributed Nodes
Hi! On 04/23/15 20:42, billythebomber wrote: I'm starting to test BDR, and I've followed the quickstart included in the documentation successfully. The problem I'm encountering is when two servers are on different hosts, which is not covered in the documentation. Node1 is 10.0.0.1, node2 is 10.0.0.2, but when I try to connect from node2: demo=# SELECT bdr.bdr_group_join( local_node_name := 'node2', node_external_dsn := 'port=5432 dbname=demo', join_using_dsn := 'port=5432 dbname=demo host=10.0.0.1' ERROR: node identity for node_external_dsn does not match current node when connecting back via remote DETAIL: The dsn '' connects to a node with identity (6140654556124456820,1,16385) but the local node is (6140654709151998583,1,16385) HINT: The 'node_external_dsn' parameter must refer to the node you're running this function from, from the perspective of the node pointed to by join_using_dsn The problem apparently is that 'node2' connects to 'node1', using the join_using_dsn and passes it the node_external_dsn. node1 takes that, and attempts to connect to that. Sadly, the DSN does not contain any host, so node1 uses localhost and connects to itself. And finds that the identity does not match the expected one (because it expects *583, assigned to node2, but gets *820, which is probably node1). If I add node2 host entry: demo=# SELECT bdr.bdr_group_join( local_node_name := 'node2', node_external_dsn := 'port=5432 dbname=demo host=10.0.0.2', join_using_dsn := 'port=5432 dbname=demo host=10.0.0.1' ); FATAL: could not connect to the server in non-replication mode: could not connect to server: Connection refused Is the server running on host 10.0.0.2 and accepting TCP/IP connections on port 5432? This seems correct, but apparantly node1 can't connect to node2, using the external_dsn. Try connecting using psql from 10.0.0.1 to 10.0.0.2 and then from 10.0.0.2 to 10.0.0.1. So something like this: # from 10.0.0.1 psql -h 10.0.0.2 -p 5432 demo # from 10.0.0.2 psql -h 10.0.0.1 -p 5432 demo If that does not work, you probably need to investigate - firewall settings, pg_hba.conf and postgresql.conf (maybe it's not listening on this address)? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] rolled back transactions logging
Hi all, I can see that value pg_stat_database.xact_rollback for my db is instantly growing, but I can not find a way to log these rolled back transactions (or, may be, last statement within). Even with log_min_duration_statement = 0 log_statement = 'all' there is no error messages in log. I mean that I was expecting something like this (for example) ERROR: value too long for type character varying(4096) ERROR: current transaction is aborted, commands ignored until end of transaction block or to see ROLLBACK statement directly. Can VACUUM be a reason of xact_rollback increasing? Server version is 9.2.4 P.S, Root problem is the number of wal files, it raised up significantly (more than 4 times) with the same workload and I do not know why. -- Regards, Andrey Lizenko
Re: [GENERAL] What constitutes reproducible numbers from pgbench?
On 4/23/2015 4:07 AM, holger.friedrich-fa-triva...@it.nrw.de wrote: On Tuesday, April 21, 2015 7:43 PM, Andy Colson wrote: On 4/21/2015 9:21 AM, holger.friedrich-fa-triva...@it.nrw.de wrote: Exactly what constitutes reproducible values from pgbench? I keep getting a range between 340 tps and 440 tps or something like that I think its common to get different timings. I think its ok because things are changing (files, caches, indexes, etc). Qingqing Zhou wrote that the range between 340 tps and 440 tps I keep getting is not ok and numbers should be the same within several per cent. Of course, if other things are going on on the physical server, I can't always expect a close match. I disagree. Having a reproducible test withing a few percent is a great result. But any result is informative. You're tests tell you an upper and lower bound on performance. It tells you to expect a little variance in your work load. It probably tells you a little about how your vm host is caching writes to disk. You are feeling the pulse of your hardware. Each hardware setup has its own pulse, and understanding it will help you understand how it'll handle a load. -Andy -- 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] LDAP Authentication
W dniu 23.04.2015 o 00:06, John R Pierce pisze: On 4/22/2015 2:57 PM, Joseph Kregloh wrote: I see. That would still require a manual process to create the user on each server. I was planing on using some already existing scripts to create the user automatically on all servers and then LDAP would authorize depending on attributes in their LDAP profile. but thats not how it works, so all the 'planing' in the world won't change a thing. access rights per database are managed with GRANT, users must be CREATE USER on each server regardless of how they are authenticated. As I understand: 1. postgresql maintains whatever's GRANTed within its system tables. 2. postgresql supports DBLINK whatif there was a way to supplement (join) system rights table with DBLINKed LDAP? /whatif -R
Re: [GENERAL] DB on mSATA SSD
Hi, On 04/23/15 14:33, John McKown wrote: That's a really old release. But I finally found some doc on it. And 8.4 does appear to have TABLESPACEs in it. http://www.postgresql.org/docs/8.4/static/manage-ag-tablespaces.html quote To define a tablespace, use the CREATE TABLESPACE http://www.postgresql.org/docs/8.4/static/sql-createtablespace.html command, for example:: CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data'; The location must be an existing, empty directory that is owned by the PostgreSQL system user. All objects subsequently created within the tablespace will be stored in files underneath this directory. I think you should read this: http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/ in other words, using tablespaces for placing some of the data into a RAM filesystem (which may disappear) is a rather terrible idea. In case of crash you won't be able to even start the database, because it will try to recover the tablespace. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- 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] What constitutes reproducible numbers from pgbench?
On Tuesday, April 21, 2015 7:43 PM, Andy Colson wrote: On 4/21/2015 9:21 AM, holger.friedrich-fa-triva...@it.nrw.de wrote: Exactly what constitutes reproducible values from pgbench? I keep getting a range between 340 tps and 440 tps or something like that I think its common to get different timings. I think its ok because things are changing (files, caches, indexes, etc). As I found out, our test server is a virtual machine, so while I should be alone on that virtual machine, of course I have no idea what else might be going on on the physical server the virtual machine is running on. That would explain the somewhat wide variations. Qingqing Zhou wrote that the range between 340 tps and 440 tps I keep getting is not ok and numbers should be the same within several per cent. Of course, if other things are going on on the physical server, I can't always expect a close match. Since someone asked, the point of the exercise is to see if and how various configurations in postgresql.conf are affecting performance. Cheers, Holger Friedrich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DB on mSATA SSD
Dear Postgresql mailing list, we use Postgresql 8.4.x on our Linux firewall distribution. Actually, we are moving from standard SATA disk to mSATA SSD solid drive, and we noticed that the DB, using lots of indexes, is writing a lot. In some monthes, two test machine got SSD broken, and we are studying how to reduce write impact for DB. Are there some suggestions with SSD drives? Putting the DB into RAM and backing up periodically to disk is a valid solutions? Or, is storing indexes on a ram drive possible? Thank you in advance for your appreciated interest! Best regards, Francesco -- 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] What constitutes reproducible numbers from pgbench?
On Thu, 23 Apr 2015 11:07:05 +0200 holger.friedrich-fa-triva...@it.nrw.de wrote: On Tuesday, April 21, 2015 7:43 PM, Andy Colson wrote: On 4/21/2015 9:21 AM, holger.friedrich-fa-triva...@it.nrw.de wrote: Exactly what constitutes reproducible values from pgbench? I keep getting a range between 340 tps and 440 tps or something like that I think its common to get different timings. I think its ok because things are changing (files, caches, indexes, etc). As I found out, our test server is a virtual machine, so while I should be alone on that virtual machine, of course I have no idea what else might be going on on the physical server the virtual machine is running on. That would explain the somewhat wide variations. Qingqing Zhou wrote that the range between 340 tps and 440 tps I keep getting is not ok and numbers should be the same within several per cent. Of course, if other things are going on on the physical server, I can't always expect a close match. Since someone asked, the point of the exercise is to see if and how various configurations in postgresql.conf are affecting performance. You're going to have difficulty doing that sort of tuning and testing on a VM. Even when there's nothing else going on, VMs tend to have a wider range of behaviors than native installs (since things like cron jobs can run both on the host and the guest OS, as well as other reasons, I'm sure). Whether such an endeavour is worthwhile depends on your reason for doing it. If your production environment will also be a VM of similar configuration to this one, then I would proceed with the tests, simply tracking the +/- variance and keeping it in mind; since you'll likely see the same variance on production. If you're doing it for your own general learning, then it might still be worth it, but it's hardly an idea setup for that kind of thing. -- PT wmo...@potentialtech.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] DB on mSATA SSD
On Thu, Apr 23, 2015 at 7:07 AM, Job j...@colliniconsulting.it wrote: Are there some suggestions with SSD drives? Putting the DB into RAM and backing up periodically to disk is a valid solutions? I have some very busy databases on SSD-only systems. I think you're using SSDs that are not rated for server use. Your strategy of using in-ram disk and backing up to the SSD is sensible, depending on what guarantees you need for the data to survive an unclean system shutdown. You will want to use a file system that allows you to snapshot and backup or logical DB backups. Postgres 9.4 has some features that will make taking the backup from the file system much easier and cleaner, too.
Re: [GENERAL] DB on mSATA SSD
On Thu, Apr 23, 2015 at 6:07 AM, Job j...@colliniconsulting.it wrote: Dear Postgresql mailing list, we use Postgresql 8.4.x on our Linux firewall distribution. Actually, we are moving from standard SATA disk to mSATA SSD solid drive, and we noticed that the DB, using lots of indexes, is writing a lot. In some monthes, two test machine got SSD broken, and we are studying how to reduce write impact for DB. Are there some suggestions with SSD drives? Putting the DB into RAM and backing up periodically to disk is a valid solutions? Or, is storing indexes on a ram drive possible? Thank you in advance for your appreciated interest! Best regards, Francesco That's a really old release. But I finally found some doc on it. And 8.4 does appear to have TABLESPACEs in it. http://www.postgresql.org/docs/8.4/static/manage-ag-tablespaces.html quote To define a tablespace, use the CREATE TABLESPACE http://www.postgresql.org/docs/8.4/static/sql-createtablespace.html command, for example:: CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data'; The location must be an existing, empty directory that is owned by the PostgreSQL system user. All objects subsequently created within the tablespace will be stored in files underneath this directory. /quote By using tablespaces appropriately, you can direct individual tables onto different media. I do not know what is causing your excessive writing, but if it is temporary tables, they you can set a TABLESPACE aside for those tables which is somewhere other than your SSD (actual hard drive, or RAM disk if you have enough memory to create a RAM disk). http://www.postgresql.org/docs/8.4/static/runtime-config-client.html#GUC-TEMP-TABLESPACES quote temp_tablespaces (string) This variable specifies tablespace(s) in which to create temporary objects (temp tables and indexes on temp tables) when a CREATE command does not explicitly specify a tablespace. Temporary files for purposes such as sorting large data sets are also created in these tablespace(s). The value is a list of names of tablespaces. When there is more than one name in the list, PostgreSQL chooses a random member of the list each time a temporary object is to be created; except that within a transaction, successively created temporary objects are placed in successive tablespaces from the list. If the selected element of the list is an empty string, PostgreSQL will automatically use the default tablespace of the current database instead. When temp_tablespaces is set interactively, specifying a nonexistent tablespace is an error, as is specifying a tablespace for which the user does not have CREATE privilege. However, when using a previously set value, nonexistent tablespaces are ignored, as are tablespaces for which the user lacks CREATE privilege. In particular, this rule applies when using a value set in postgresql.conf. The default value is an empty string, which results in all temporary objects being created in the default tablespace of the current database. /quote I agree that is sounds like you're not using quality SSD drives. -- If you sent twitter messages while exploring, are you on a textpedition? He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! John McKown
Re: [GENERAL] DB on mSATA SSD
Dear Postgresql mailing list, we use Postgresql 8.4.x on our Linux firewall distribution. Actually, we are moving from standard SATA disk to mSATA SSD solid drive, and we noticed that the DB, using lots of indexes, is writing a lot. In some monthes, two test machine got SSD broken, and we are studying how to reduce write impact for DB. Are there some suggestions with SSD drives? Putting the DB into RAM and backing up periodically to disk is a valid solutions? Or, is storing indexes on a ram drive possible? Thank you in advance for your appreciated interest! Best regards, Francesco Hi, I don't think that today's SSDs - and certainly not the server-grade ones - will break due to write intensive loads. Have a look at the SMART data for you drives, there should be some metrics called wear level count or similar that gives some indications. I wouldn't be surprised if you find that your broken drives had failures not related to wear level. If you're on Linux use smartctl. Also, as others have pointed out 8.4 is out of support, so consider upgrading. Bye, Chris. -- 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] DB on mSATA SSD
On 04/23/15 13:07, Job wrote: Dear Postgresql mailing list, we use Postgresql 8.4.x on our Linux firewall distribution. Actually, we are moving from standard SATA disk to mSATA SSD solid drive, and we noticed that the DB, using lots of indexes, is writing a lot. In some monthes, two test machine got SSD broken, and we are studyinghow to reduce write impact for DB. Are there some suggestions with SSD drives? There are ways to make the writes less frequent, both at the database and OS level. We don't know what's your PostgreSQL config, but making the checkpoints less frequent and tuning the kernel/mount options may help a lot. Putting the DB into RAM and backing up periodically to disk is a validsolutions? Well, that depends on your requirements. You may lose the changes since the last backup. Or, is storing indexes on a ram drive possible? No, not really. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] DB on mSATA SSD
Hello, thank you first of all for your wonder help! Tomas, regarding: There are ways to make the writes less frequent, both at the database and OS level. We don't know what's your PostgreSQL config, but making the checkpoints less frequent and tuning the kernel/mount options may help a lot. We can raise up checkpoints, at kernel-side which options do you suggest to tune for minimize disk writing? We have a table, about 500Mb, that is updated and written every day. When machines updates, table is truncated and then re-populated with pg_bulk. But i think we strongly writes when importing new data tables.. Here is why we tought putting some tables into ramdrive... Thank you, Francesco -- 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] DB on mSATA SSD
On 23/04/2015 15:28, Vick Khera wrote: On Thu, Apr 23, 2015 at 7:07 AM, Job j...@colliniconsulting.it mailto:j...@colliniconsulting.it wrote: Are there some suggestions with SSD drives? Putting the DB into RAM and backing up periodically to disk is a valid solutions? I have some very busy databases on SSD-only systems. I think you're using SSDs that are not rated for server use. Your strategy of using in-ram disk and backing up to the SSD is sensible, depending on what guarantees you need for the data to survive an unclean system shutdown. You will want to use a file system that allows you to snapshot and backup or logical DB backups. Postgres 9.4 has some features that will make taking the backup from the file system much easier and cleaner, too. FS Snapshots are an option but one should make sure that all file systems are snapshot atomically, which is not very common unless you use ZFS or similarly high-end FS. Regarding file filesys based backups, apart from pg_basebackup which is a nice utility but built on top of the existing Continuous Archiving philosophy, the very feature was already implemented since 8.* -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] DB on mSATA SSD
Hi On 04/23/15 14:50, Chris Mair wrote: Dear Postgresql mailing list, we use Postgresql 8.4.x on our Linux firewall distribution. Actually, we are moving from standard SATA disk to mSATA SSD solid drive, and we noticed that the DB, using lots of indexes, is writing a lot. In some monthes, two test machine got SSD broken, and we are studyinghow to reduce write impact for DB. Are there some suggestions with SSD drives? Putting the DB into RAM and backing up periodically to disk is a valid solutions? Or, is storing indexes on a ram drive possible? Thank you in advance for your appreciated interest! Best regards, Francesco Hi, I don't think that today's SSDs - and certainly not the server-grade ones - will break due to write intensive loads. Exactly. If you want an SSD for a write-intensive database, you need a reasonably good SSD. Sadly, the OP mentioned they're going for a mSATA drive, and those suck when used for this purpose. Theoretically it's possible to improve the lifetime by only allocating part of the SSD and leaving some additional free space for the wear leveling - the manufacturer already does that, but allocates a small amount of space for the cheaper SSDs (say ~10% while the server-grade SSDs may have ~25% of unallocated space for this purpose). So by allocating only 75% for a filesystem, it may last longer. Have a look at the SMART data for you drives, there should be some metrics called wear level count or similar that gives some indications. I wouldn't be surprised if you find that your broken drives had failures not related to wear level. My experience with mSATA drives is rather bad - the SMART data is rather unreliable, and most of them doesn't even have power-loss protection (which you need for a database, although a UPS may help a bit here). But maybe that changed recently. If you're on Linux use smartctl. Also, as others have pointed out 8.4 is out of support, so consider upgrading. +1 to this -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
