[GENERAL] Backups and binary mode
Hi. Is this possible to force pg_dump to make backups using COPY ... BINARY ? Simple benchmark show that COPY BINARY is 8 times faster than COPY CSV on my desktop. Postgresql 8.4.8 , WinXp sp3 pasman -- 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] Backups and binary mode
Simple benchmark show that COPY BINARY is 8 times faster than COPY CSV on my desktop. I retry benchmark, and differences are small. pasman -- 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] Problem importing a csv file
On Sunday, August 28, 2011 5:48:50 pm you wrote: error is. Is the above the only command being run? Where is this coming from?: LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )... Explain mode, in pgadminIII Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII As far as I know EXPLAIN does not work with COPY. From the docs: http://www.postgresql.org/docs/9.0/interactive/sql-explain.html statement Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or CREATE TABLE AS statement, whose execution plan you wish to see. -- Adrian Klaver adrian.kla...@gmail.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] passing cursors from one PL function to another
El día 26 de agosto de 2011 09:15, Merlin Moncure mmonc...@gmail.com escribió:
2011/8/26 Martín Marqués martin.marq...@gmail.com:
El día 26 de agosto de 2011 00:04, Merlin Moncure mmonc...@gmail.com
escribió:
2011/8/25 Martín Marqués martin.marq...@gmail.com:
CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor
refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
cur alias for $2;
BEGIN
PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
tab1 WHERE field 11000');
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
RETURNS SETOF refcursor AS
$BODY$
BEGIN
OPEN $1 FOR Select * from tab1 where field 11000;
RAISE NOTICE '%', $1;
RETURN NEXT $1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
begin;
select * from prueba_cursor4(1, 'h');
end;
you pretty much had it.
select * from prueba_cursor4(1, 'h');
should be
select * from prueba_cursor(1, 'h');
after that, but inside the transaction, you can just do:
fetch all from 'cur';
That was a typo related with copy paste. Sorry.
note neither of your functions need to return setof fwict. you are
returning one cursor, not a set of them.
That's because originally I was trying to get more then one cursor.
Anyway, I was getting an annoying error on a windows server, and now
that I test it on my Linux installation it works like a charm.
Could it be that I was making changes to the functions and not
dropping them before recreating?
not likely -- got the error text?.
The error is version related. On 8.4, it works great. But with 8.3
(which is the version being used in production) I get this:
# select * from prueba_cursor(1, 'a');
ERROR: se llamó una función que retorna un conjunto en un contexto
que no puede aceptarlo
CONTEXTO: PL/pgSQL function construyecursordesdequery line 3 at RETURN NEXT
sentencia SQL: «SELECT construyeCursorDesdeQuery( $1 ,'SELECT * from
tab1 WHERE field 11000')»
PL/pgSQL function prueba_cursor line 3 at PERFORM
Why does it work on 8.4 and not on 8.3? Any work around that doesn't
involve upgradeing the DB server?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
--
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] passing cursors from one PL function to another
Actually, what we are trying to do is return 2 recordsets with the
same function call (simulate SP from SQL Server returning 2
recordsets).
I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query)
which works now, but can't run 2 different queries on the same cursor.
I was able to do it on 8.4, but not on 8.3.
El día 29 de agosto de 2011 13:48, Martín Marqués
martin.marq...@gmail.com escribió:
El día 26 de agosto de 2011 09:15, Merlin Moncure mmonc...@gmail.com
escribió:
2011/8/26 Martín Marqués martin.marq...@gmail.com:
El día 26 de agosto de 2011 00:04, Merlin Moncure mmonc...@gmail.com
escribió:
2011/8/25 Martín Marqués martin.marq...@gmail.com:
CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor
refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
cur alias for $2;
BEGIN
PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
tab1 WHERE field 11000');
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query
text)
RETURNS SETOF refcursor AS
$BODY$
BEGIN
OPEN $1 FOR Select * from tab1 where field 11000;
RAISE NOTICE '%', $1;
RETURN NEXT $1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
begin;
select * from prueba_cursor4(1, 'h');
end;
you pretty much had it.
select * from prueba_cursor4(1, 'h');
should be
select * from prueba_cursor(1, 'h');
after that, but inside the transaction, you can just do:
fetch all from 'cur';
That was a typo related with copy paste. Sorry.
note neither of your functions need to return setof fwict. you are
returning one cursor, not a set of them.
That's because originally I was trying to get more then one cursor.
Anyway, I was getting an annoying error on a windows server, and now
that I test it on my Linux installation it works like a charm.
Could it be that I was making changes to the functions and not
dropping them before recreating?
not likely -- got the error text?.
The error is version related. On 8.4, it works great. But with 8.3
(which is the version being used in production) I get this:
# select * from prueba_cursor(1, 'a');
ERROR: se llamó una función que retorna un conjunto en un contexto
que no puede aceptarlo
CONTEXTO: PL/pgSQL function construyecursordesdequery line 3 at RETURN NEXT
sentencia SQL: «SELECT construyeCursorDesdeQuery( $1 ,'SELECT * from
tab1 WHERE field 11000')»
PL/pgSQL function prueba_cursor line 3 at PERFORM
Why does it work on 8.4 and not on 8.3? Any work around that doesn't
involve upgradeing the DB server?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
--
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] passing cursors from one PL function to another
2011/8/29 Martín Marqués martin.marq...@gmail.com:
Actually, what we are trying to do is return 2 recordsets with the
same function call (simulate SP from SQL Server returning 2
recordsets).
I found out that I had to do PERFORM * FROM construyecursordesdequery($1,
query)
which works now, but can't run 2 different queries on the same cursor.
in your example you use a static cursor.
Pavel
I was able to do it on 8.4, but not on 8.3.
El día 29 de agosto de 2011 13:48, Martín Marqués
martin.marq...@gmail.com escribió:
El día 26 de agosto de 2011 09:15, Merlin Moncure mmonc...@gmail.com
escribió:
2011/8/26 Martín Marqués martin.marq...@gmail.com:
El día 26 de agosto de 2011 00:04, Merlin Moncure mmonc...@gmail.com
escribió:
2011/8/25 Martín Marqués martin.marq...@gmail.com:
CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor
refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
cur alias for $2;
BEGIN
PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
tab1 WHERE field 11000');
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query
text)
RETURNS SETOF refcursor AS
$BODY$
BEGIN
OPEN $1 FOR Select * from tab1 where field 11000;
RAISE NOTICE '%', $1;
RETURN NEXT $1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
begin;
select * from prueba_cursor4(1, 'h');
end;
you pretty much had it.
select * from prueba_cursor4(1, 'h');
should be
select * from prueba_cursor(1, 'h');
after that, but inside the transaction, you can just do:
fetch all from 'cur';
That was a typo related with copy paste. Sorry.
note neither of your functions need to return setof fwict. you are
returning one cursor, not a set of them.
That's because originally I was trying to get more then one cursor.
Anyway, I was getting an annoying error on a windows server, and now
that I test it on my Linux installation it works like a charm.
Could it be that I was making changes to the functions and not
dropping them before recreating?
not likely -- got the error text?.
The error is version related. On 8.4, it works great. But with 8.3
(which is the version being used in production) I get this:
# select * from prueba_cursor(1, 'a');
ERROR: se llamó una función que retorna un conjunto en un contexto
que no puede aceptarlo
CONTEXTO: PL/pgSQL function construyecursordesdequery line 3 at RETURN
NEXT
sentencia SQL: «SELECT construyeCursorDesdeQuery( $1 ,'SELECT * from
tab1 WHERE field 11000')»
PL/pgSQL function prueba_cursor line 3 at PERFORM
Why does it work on 8.4 and not on 8.3? Any work around that doesn't
involve upgradeing the DB server?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
--
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] passing cursors from one PL function to another
El día 29 de agosto de 2011 15:28, Pavel Stehule pavel.steh...@gmail.com escribió: 2011/8/29 Martín Marqués martin.marq...@gmail.com: Actually, what we are trying to do is return 2 recordsets with the same function call (simulate SP from SQL Server returning 2 recordsets). I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query) which works now, but can't run 2 different queries on the same cursor. in your example you use a static cursor. As opposed to? I see no way to define a cursor not-static. -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] passing cursors from one PL function to another
2011/8/29 Martín Marqués martin.marq...@gmail.com: El día 29 de agosto de 2011 15:28, Pavel Stehule pavel.steh...@gmail.com escribió: 2011/8/29 Martín Marqués martin.marq...@gmail.com: Actually, what we are trying to do is return 2 recordsets with the same function call (simulate SP from SQL Server returning 2 recordsets). I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query) which works now, but can't run 2 different queries on the same cursor. in your example you use a static cursor. As opposed to? I see no way to define a cursor not-static. refcursors can be dynamic defined Pavel -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] passing cursors from one PL function to another
El día 29 de agosto de 2011 15:52, Pavel Stehule pavel.steh...@gmail.com escribió: 2011/8/29 Martín Marqués martin.marq...@gmail.com: El día 29 de agosto de 2011 15:28, Pavel Stehule pavel.steh...@gmail.com escribió: 2011/8/29 Martín Marqués martin.marq...@gmail.com: Actually, what we are trying to do is return 2 recordsets with the same function call (simulate SP from SQL Server returning 2 recordsets). I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query) which works now, but can't run 2 different queries on the same cursor. in your example you use a static cursor. As opposed to? I see no way to define a cursor not-static. refcursors can be dynamic defined OK, I'm totally lost. How do you define a dynamic cursor? Couldn't find anything in the manuals. -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] passing cursors from one PL function to another
2011/8/29 Martín Marqués martin.marq...@gmail.com: El día 29 de agosto de 2011 15:52, Pavel Stehule pavel.steh...@gmail.com escribió: 2011/8/29 Martín Marqués martin.marq...@gmail.com: El día 29 de agosto de 2011 15:28, Pavel Stehule pavel.steh...@gmail.com escribió: 2011/8/29 Martín Marqués martin.marq...@gmail.com: Actually, what we are trying to do is return 2 recordsets with the same function call (simulate SP from SQL Server returning 2 recordsets). I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query) which works now, but can't run 2 different queries on the same cursor. in your example you use a static cursor. As opposed to? I see no way to define a cursor not-static. refcursors can be dynamic defined OK, I'm totally lost. How do you define a dynamic cursor? Couldn't find anything in the manuals. DECLARE curs1 refcursor; BEGIN OPEN curs1 FOR EXECUTE 'SELECT ...'; -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] passing cursors from one PL function to another
El día 29 de agosto de 2011 16:12, Pavel Stehule pavel.steh...@gmail.com escribió: 2011/8/29 Martín Marqués martin.marq...@gmail.com: refcursors can be dynamic defined OK, I'm totally lost. How do you define a dynamic cursor? Couldn't find anything in the manuals. DECLARE curs1 refcursor; BEGIN OPEN curs1 FOR EXECUTE 'SELECT ...'; OK, not that easy, but I think I got it working. Thanks alot Pavel! -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dropdb: database removal failed: active sessions
Hi, I cannot dropdb. Postgres throws me: dropdb: database removal failed: ERROR: database database1 is being accessed by other users DETAIL: There are 1 other session(s) using the database. the select datname,current_query,query_start from pg_stat_activity ; query gives me: datname | current_query | query_start -+--+--- database2 | CREATE INDEX location_object_bioseg ON location USING gist (locatedonid, bioseg_create(intermine_start, intermine_end)); | 2011-08-29 14:43:40.856594-04 database3 | IDLE | 2011-08-29 15:08:17.469927-04 database4 | IDLE | 2011-08-29 15:07:09.484543-04 database3 | IDLE | 2011-08-29 15:07:09.901601-04 database2 | select datname,current_query,query_start from pg_stat_activity ; | 2011-08-29 15:12:39.811168-04 database3 | IDLE | 2011-08-29 15:08:17.476254-04 database3 | IDLE | 2011-08-29 15:07:10.422579-04 database4 | IDLE | 2011-08-29 15:07:10.515946-04 database1 | IDLE | 2011-08-29 15:07:31.423596-04 Is the drop being prevented by the active query from database2? If not how can I kill the IDLE query that must be preventing deletion. Thanks!
Re: [GENERAL] dropdb: database removal failed: active sessions
On Mon, 2011-08-29 at 15:22 -0400, JD Wong wrote: Hi, I cannot dropdb. Postgres throws me: dropdb: database removal failed: ERROR: database database1 is being accessed by other users DETAIL: There are 1 other session(s) using the database. the select datname,current_query,query_start from pg_stat_activity ; query gives me: datname | current_query | query_start -+--+--- database2 | CREATE INDEX location_object_bioseg ON location USING gist (locatedonid, bioseg_create(intermine_start, intermine_end)); | 2011-08-29 14:43:40.856594-04 database3 | IDLE | 2011-08-29 15:08:17.469927-04 database4 | IDLE | 2011-08-29 15:07:09.484543-04 database3 | IDLE | 2011-08-29 15:07:09.901601-04 database2 | select datname,current_query,query_start from pg_stat_activity ; | 2011-08-29 15:12:39.811168-04 database3 | IDLE | 2011-08-29 15:08:17.476254-04 database3 | IDLE | 2011-08-29 15:07:10.422579-04 database4 | IDLE | 2011-08-29 15:07:10.515946-04 database1 | IDLE | 2011-08-29 15:07:31.423596-04 Is the drop being prevented by the active query from database2? No, by the IDLE one from database1. If not how can I kill the IDLE query that must be preventing deletion. You have to use the pg_terminate_backend on this connection. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] heavy swapping, not sure why
I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. All of them have fairly substantial amounts of RAM (not including swap), yet the amount of swap that postgres is using ramps up over time and eventually hurts performance badly. In every case, simply restarting postgresql frees up all the swap in use (until it ramps up again later). I'm assuming that I have at least one postgresql.conf parameter set wrong, but I'm not sure which. I read that (max_connections * work_mem) should never exceed physical RAM, and if that's accurate, then I suspect that's the root of my problem on systemA (below). However, I'd like confirmation before I start tweaking things, as one of these servers is in production, and I can't easily tweak settings to experiment (plus this problem takes a few weeks before swapping gets bad enough to impact performance). A few examples: 0) system A: 56GB RAM, running postgresql-8.4.8 with the following parameters: maintenance_work_mem = 96MB effective_cache_size = 40GB work_mem = 256MB wal_buffers = 16MB shared_buffers = 13GB max_connections = 300 1) system B: 120GB RAM, running postgresql-9.0.4 with the following parameters: maintenance_work_mem = 1GB effective_cache_size = 88GB work_mem = 576MB wal_buffers = 4MB shared_buffers = 28GB max_connections = 200 thanks -- 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] dropdb: database removal failed: active sessions
On 08/29/11 12:22 PM, JD Wong wrote: I cannot dropdb. Postgres throws me: dropdb: database removal failed: ERROR: database database1 is being accessed by other users DETAIL: There are 1 other session(s) using the database. the select datname,current_query,query_start from pg_stat_activity ; query gives me: datname | current_query | query_start -+--+--- ... database1 | IDLE | 2011-08-29 15:07:31.423596-04 Is the drop being prevented by the active query from database2? If not how can I kill the IDLE query that must be preventing deletion. any connection to the database, even idle, will prevent a drop database. try... select pg_terminate_backend(procpid) from pg_stat_activity where datname = 'database1'; that will snuff those processes. then you can drop the database, assuming the clients don't immediately reconnect. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] heavy swapping, not sure why
On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 1:46 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. I understand that the kernel determines what is swapped out, however postgres is what is using nearly all the RAM, and then overflowing into swap. I guess I should have noted that this isn't a case of a significant amount of RAM not being used, and swapping occurring anyway. Most of the RAM is already consumed when the heavy swapping is happening. So, I'd be surprised if setting vm.swappiness=0 will make a significant difference, however I can certainly try. -- 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] dropdb: database removal failed: active sessions
On Mon, Aug 29, 2011 at 2:39 PM, John R Pierce pie...@hogranch.com wrote: any connection to the database, even idle, will prevent a drop database. try... select pg_terminate_backend(procpid) from pg_stat_activity where datname = 'database1'; that will snuff those processes. then you can drop the database, assuming the clients don't immediately reconnect. If you lose your connection at this point you'll know who was connected to that database as well. Yes, I have done that before. -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 2:57 PM, Lonni J Friedman netll...@gmail.com wrote: On Mon, Aug 29, 2011 at 1:46 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. I understand that the kernel determines what is swapped out, however postgres is what is using nearly all the RAM, and then overflowing into swap. I guess I should have noted that this isn't a case of a significant amount of RAM not being used, and swapping occurring anyway. Most of the RAM is already consumed when the heavy swapping is happening. So, I'd be surprised if setting vm.swappiness=0 will make a significant difference, however I can certainly try. You haven't shown us how you determined this, it would be nice to see some copy and paste of things like top, free, or whatever. How much free AND cache is left over when the machine starts to run out of memory etc. Your settings for shared_memory are HUGE. I run a machine witih 128G of ram and my shared_memory is 8G and that's quite large. No testing anyone has done has shown anything over 10G being useful. -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 3:46 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. that won't help and, in almost all cases, is a bad idea. merlin -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 3:36 PM, Lonni J Friedman netll...@gmail.com wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. All of them have fairly substantial amounts of RAM (not including swap), yet the amount of swap that postgres is using ramps up over time and eventually hurts performance badly. In every case, simply restarting postgresql frees up all the swap in use (until it ramps up again later). I'm assuming that I have at least one postgresql.conf parameter set wrong, but I'm not sure which. I read that (max_connections * work_mem) should never exceed physical RAM, and if that's accurate, then I suspect that's the root of my problem on systemA (below). However, I'd like confirmation before I start tweaking things, as one of these servers is in production, and I can't easily tweak settings to experiment (plus this problem takes a few weeks before swapping gets bad enough to impact performance). using any C code in the backend? this includes 3rd party libraries which link in C, including postgis, pljava, xml2, etc. Any features being used not included in the standard core distribution are interesting. How long do your database connections stay open? forever? If yes, is memory distributed semi-evenly across all postgres processes or only to particular ones? If no, do you see excessive consumption with the non user backends like the stats collector, etc? Anything else running on these boxes? Application server? Anything written in java? merlin -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 2:24 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Aug 29, 2011 at 2:57 PM, Lonni J Friedman netll...@gmail.com wrote: On Mon, Aug 29, 2011 at 1:46 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. I understand that the kernel determines what is swapped out, however postgres is what is using nearly all the RAM, and then overflowing into swap. I guess I should have noted that this isn't a case of a significant amount of RAM not being used, and swapping occurring anyway. Most of the RAM is already consumed when the heavy swapping is happening. So, I'd be surprised if setting vm.swappiness=0 will make a significant difference, however I can certainly try. You haven't shown us how you determined this, it would be nice to see some copy and paste of things like top, free, or whatever. How much free AND cache is left over when the machine starts to run out of memory etc. Sorry, I was looking at the output from 'free' (plus we have some generic monitoring tools which generate pretty graphs that also illustrate the problem). I restarted postgres this morning, so everything is in a good state right now: total used free sharedbuffers cached Mem: 56481 55486995 0 15 53298 -/+ buffers/cache: 2172 54309 Swap: 1099 18 1081 total used free sharedbuffers cached Mem:121177 111603 9573 0 0 101007 -/+ buffers/cache: 10596 110581 Swap: 1498 10 1488 Based on past results, it'll be about two weeks before a few hundred MB of swap is in use, and perf is noticeably poor. Although it will creep up over time, so even in a day or 2, it will be worse than right now. I could post the pretty graph somewhere (or send it to the list, if you'd prefer) if you want to see something right now (filesize is less than 40KB). Your settings for shared_memory are HUGE. I run a machine witih 128G of ram and my shared_memory is 8G and that's quite large. No testing anyone has done has shown anything over 10G being useful. Do you mean shared_buffers? thanks -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 2:38 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Aug 29, 2011 at 3:36 PM, Lonni J Friedman netll...@gmail.com wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. All of them have fairly substantial amounts of RAM (not including swap), yet the amount of swap that postgres is using ramps up over time and eventually hurts performance badly. In every case, simply restarting postgresql frees up all the swap in use (until it ramps up again later). I'm assuming that I have at least one postgresql.conf parameter set wrong, but I'm not sure which. I read that (max_connections * work_mem) should never exceed physical RAM, and if that's accurate, then I suspect that's the root of my problem on systemA (below). However, I'd like confirmation before I start tweaking things, as one of these servers is in production, and I can't easily tweak settings to experiment (plus this problem takes a few weeks before swapping gets bad enough to impact performance). using any C code in the backend? this includes 3rd party libraries which link in C, including postgis, pljava, xml2, etc. Any features being used not included in the standard core distribution are interesting. Nope, nothing like that. They're fairly generic setups, with nothing added that isn't part of the core distribution. How long do your database connections stay open? forever? If yes, is memory distributed semi-evenly across all postgres processes or only to particular ones? If no, do you see excessive consumption with the non user backends like the stats collector, etc? Nope, nothing is forever, everything is a fairly brief connection (a few seconds, tops, with most under 1s). Although I do have pgbouncer sitting in front of systemA to serve as a connection pooler. Anything else running on these boxes? Application server? Anything written in java? Nothing. They're all 100% dedicated to postgres, and don't run anything else beyond the basic OS level stuff (crond, etc). -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 3:38 PM, Lonni J Friedman netll...@gmail.com wrote: On Mon, Aug 29, 2011 at 2:24 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Aug 29, 2011 at 2:57 PM, Lonni J Friedman netll...@gmail.com wrote: On Mon, Aug 29, 2011 at 1:46 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. I understand that the kernel determines what is swapped out, however postgres is what is using nearly all the RAM, and then overflowing into swap. I guess I should have noted that this isn't a case of a significant amount of RAM not being used, and swapping occurring anyway. Most of the RAM is already consumed when the heavy swapping is happening. So, I'd be surprised if setting vm.swappiness=0 will make a significant difference, however I can certainly try. You haven't shown us how you determined this, it would be nice to see some copy and paste of things like top, free, or whatever. How much free AND cache is left over when the machine starts to run out of memory etc. Sorry, I was looking at the output from 'free' (plus we have some generic monitoring tools which generate pretty graphs that also illustrate the problem). I restarted postgres this morning, so everything is in a good state right now: total used free shared buffers cached Mem: 56481 55486 995 0 15 53298 -/+ buffers/cache: 2172 54309 Swap: 1099 18 1081 total used free shared buffers cached Mem: 121177 111603 9573 0 0 101007 -/+ buffers/cache: 10596 110581 Swap: 1498 10 1488 Based on past results, it'll be about two weeks before a few hundred MB of swap is in use, and perf is noticeably poor. That's all a few hundred Megs? That shouldn't make any real difference. Now a few dozen gigs that would make a difference. Use top or htop or some other method that shows you the VIRT RES and SHR memory usage of the processes. Although it will creep up over time, so even in a day or 2, it will be worse than right now. I could post the pretty graph somewhere (or send it to the list, if you'd prefer) if you want to see something right now (filesize is less than 40KB). Your settings for shared_memory are HUGE. I run a machine witih 128G of ram and my shared_memory is 8G and that's quite large. No testing anyone has done has shown anything over 10G being useful. Do you mean shared_buffers? Yeah -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 2:51 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Aug 29, 2011 at 3:38 PM, Lonni J Friedman netll...@gmail.com wrote: On Mon, Aug 29, 2011 at 2:24 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Aug 29, 2011 at 2:57 PM, Lonni J Friedman netll...@gmail.com wrote: On Mon, Aug 29, 2011 at 1:46 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. I understand that the kernel determines what is swapped out, however postgres is what is using nearly all the RAM, and then overflowing into swap. I guess I should have noted that this isn't a case of a significant amount of RAM not being used, and swapping occurring anyway. Most of the RAM is already consumed when the heavy swapping is happening. So, I'd be surprised if setting vm.swappiness=0 will make a significant difference, however I can certainly try. You haven't shown us how you determined this, it would be nice to see some copy and paste of things like top, free, or whatever. How much free AND cache is left over when the machine starts to run out of memory etc. Sorry, I was looking at the output from 'free' (plus we have some generic monitoring tools which generate pretty graphs that also illustrate the problem). I restarted postgres this morning, so everything is in a good state right now: total used free shared buffers cached Mem: 56481 55486 995 0 15 53298 -/+ buffers/cache: 2172 54309 Swap: 1099 18 1081 total used free shared buffers cached Mem: 121177 111603 9573 0 0 101007 -/+ buffers/cache: 10596 110581 Swap: 1498 10 1488 Based on past results, it'll be about two weeks before a few hundred MB of swap is in use, and perf is noticeably poor. That's all a few hundred Megs? That shouldn't make any real difference. Now a few dozen gigs that would make a difference. Use top or htop or some other method that shows you the VIRT RES and SHR memory usage of the processes. Yes, a few hundred MB of swap, and its definitely making a huge difference. Upon restarting postgres, its all freed up, and then perf is good again. Also, this box only has 1GB of swap total, so its never going to get up a few dozen GB. Anyway, here's some of top output for systemA right now: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 5582 postgres 20 0 13.5g 8.9g 8.9g R 97.7 16.2 2:51.61 postmaster 6554 postgres 20 0 13.5g 1.9g 1.9g D 63.8 3.4 1:50.50 postmaster 6052 postgres 20 0 13.5g 1.3g 1.2g S 22.6 2.3 0:26.33 postmaster 2751 postgres 20 0 13.5g 1.6g 1.6g S 21.6 2.8 0:52.32 postmaster 31221 postgres 20 0 13.5g 2.0g 2.0g S 10.0 3.6 1:19.05 postmaster 1721 postgres 20 0 13.5g 6.7g 6.7g S 3.0 12.2 2:19.21 postmaster 6050 postgres 20 0 13.5g 879m 867m S 8.3 1.6 0:06.89 postmaster I can certainly grab more in a few days once swap usage has started to creep up a bit. Although it will creep up over time, so even in a day or 2, it will be worse than right now. I could post the pretty graph somewhere (or send it to the list, if you'd prefer) if you want to see something right now (filesize is less than 40KB). Your settings for shared_memory are HUGE. I run a machine witih 128G of ram and my shared_memory is 8G and that's quite large. No testing anyone has done has shown anything over 10G being useful. Do you mean shared_buffers? Yeah OK, I'll reduce it to 10GB and see if there's any noticable change in performance. thanks -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 4:45 PM, Lonni J Friedman netll...@gmail.com wrote: using any C code in the backend? this includes 3rd party libraries which link in C, including postgis, pljava, xml2, etc. Any features being used not included in the standard core distribution are interesting. Nope, nothing like that. They're fairly generic setups, with nothing added that isn't part of the core distribution. How long do your database connections stay open? forever? If yes, is memory distributed semi-evenly across all postgres processes or only to particular ones? If no, do you see excessive consumption with the non user backends like the stats collector, etc? Nope, nothing is forever, everything is a fairly brief connection (a few seconds, tops, with most under 1s). Although I do have pgbouncer sitting in front of systemA to serve as a connection pooler. hm. well iirc pgbouncer tries to dump server connections older than an hour or so. this plus your other statements makes me very suspension the problem is in postgres itself. since postgres process dies when the connection dies, long term memory accumulation is just not possible except in the processes that aren't serving client sessions (the very first thing you need to do is rule those processes out). pgbouncer itself could be the issue, but i doubt it. obviously, a full memory profile during your problem times is a critical piece of evidence (a 'top' sorted by memory usage should to the trick nicely). it's possible you've unhappily tripped a leak in the o/s -- is everything properly updated? running any funky hardware (like fiber san drivers)? anything else interesting or out of the ordinary to report? merlin -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 3:17 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Aug 29, 2011 at 4:45 PM, Lonni J Friedman netll...@gmail.com wrote: using any C code in the backend? this includes 3rd party libraries which link in C, including postgis, pljava, xml2, etc. Any features being used not included in the standard core distribution are interesting. Nope, nothing like that. They're fairly generic setups, with nothing added that isn't part of the core distribution. How long do your database connections stay open? forever? If yes, is memory distributed semi-evenly across all postgres processes or only to particular ones? If no, do you see excessive consumption with the non user backends like the stats collector, etc? Nope, nothing is forever, everything is a fairly brief connection (a few seconds, tops, with most under 1s). Although I do have pgbouncer sitting in front of systemA to serve as a connection pooler. hm. well iirc pgbouncer tries to dump server connections older than an hour or so. this plus your other statements makes me very suspension the problem is in postgres itself. since postgres process dies when the connection dies, long term memory accumulation is just not possible except in the processes that aren't serving client sessions (the very first thing you need to do is rule those processes out). pgbouncer itself could be the issue, but i doubt it. obviously, a full memory profile during your problem times is a critical piece of evidence (a 'top' sorted by memory usage should to the trick nicely). OK, I'll get that top output for everyone in a week or so once swap usage has grown noticeably above its current level. it's possible you've unhappily tripped a leak in the o/s -- is everything properly updated? running any funky hardware (like fiber san drivers)? anything else interesting or out of the ordinary to report? No funky HW. Fairly standard 1U server with SATA disks, the OS is up to date. -- 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] heavy swapping, not sure why
On August 29, 2011 02:34:26 PM you wrote: On Mon, Aug 29, 2011 at 3:46 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. that won't help and, in almost all cases, is a bad idea. Overly aggressive swapping with the default settings has frequently caused me performance issues. Using this prevents those problems. -- 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] Indexes on inheriting tables
2011/8/24 Ondrej Ivanič ondrej.iva...@gmail.com Hi, On 25 August 2011 11:17, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Yes, it is little bit annoying but I like it. You don't need any index on parent table but you have to create them manually. I wrote simple python script which creates partitions and required indexes in advance (tables are partitioned by date). I like the flexibility because you can have different indexex on different partitions. For example, I discovered that adding index will improve several queries. In the production I can't afford exclusive lock (build index concurrently takes ages) so I updated and re-run the script which re-created future partitions. My Personal favorite is the LIKE syntax: CREATE TABLE foo_1 (LIKE foo including indexes ) inherits (foo); It doesn't help you change children after the fact, but your new partitions automatically get whatever indexes you've stuck on the master table. --Scott -- Ondrej Ivanic (ondrej.iva...@gmail.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] heavy swapping, not sure why
Lonni J Friedman netll...@gmail.com writes: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. All of them have fairly substantial amounts of RAM (not including swap), yet the amount of swap that postgres is using ramps up over time and eventually hurts performance badly. In every case, simply restarting postgresql frees up all the swap in use (until it ramps up again later). If you're certain that it's restarting *postgres* that does it, and not restarting your application or pgbouncer or some other code, then it seems like you must have uncovered a memory leak someplace. We haven't got nearly enough info here to diagnose it though. First thing I'd want to know is which process(es) exactly are bloating. The top output you showed us is unhelpful for that since it just shows them all as postmaster --- you'll need to match up the problem PIDs with ps auxww output. Keep in mind also that top is pretty awful about distinguishing a process's actual memory use (private memory) from the portion of PG's shared memory that it happens to have touched. What you need to pay attention to is RES minus SHR, not either number alone. With shared buffers set as high as you've got it, you'll probably not be able to be sure that a process is bloating until it's eaten hundreds of megs of private space. Where we go from there will depend on what you find out ... but if possible, don't restart the server right away, or it'll probably be another couple weeks before you can do the next round of investigation. 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 5:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. All of them have fairly substantial amounts of RAM (not including swap), yet the amount of swap that postgres is using ramps up over time and eventually hurts performance badly. In every case, simply restarting postgresql frees up all the swap in use (until it ramps up again later). If you're certain that it's restarting *postgres* that does it, and not restarting your application or pgbouncer or some other code, then it seems like you must have uncovered a memory leak someplace. We haven't got nearly enough info here to diagnose it though. I'm 100% certain its hte postgres restart that frees up all the swap. First thing I'd want to know is which process(es) exactly are bloating. The top output you showed us is unhelpful for that since it just shows them all as postmaster --- you'll need to match up the problem PIDs with ps auxww output. Keep in mind also that top is pretty awful about distinguishing a process's actual memory use (private memory) from the portion of PG's shared memory that it happens to have touched. What you need to pay attention to is RES minus SHR, not either number alone. With shared buffers set as high as you've got it, you'll probably not be able to be sure that a process is bloating until it's eaten hundreds of megs of private space. ok, I'll do my best to capture this data, and then reply back. Where we go from there will depend on what you find out ... but if possible, don't restart the server right away, or it'll probably be another couple weeks before you can do the next round of investigation. understood thanks! -- 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] heavy swapping, not sure why
You should monitor PageTables value in /proc/meminfo.if the value larger than 1G,I Suggest enable hugepages . To monitor PageTables: # cat /proc/meminfo |grep -i pagetables -- 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] heavy swapping, not sure why
On Mon, Aug 29, 2011 at 5:01 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 02:34:26 PM you wrote: On Mon, Aug 29, 2011 at 3:46 PM, Alan Hodgson ahodg...@simkin.ca wrote: On August 29, 2011 01:36:07 PM Lonni J Friedman wrote: I have several Linux-x68_64 based dedicated PostgreSQL servers where I'm experiencing significant swap usage growth over time. It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0 (usually in /etc/sysctl.conf) and put that into effect. that won't help and, in almost all cases, is a bad idea. Overly aggressive swapping with the default settings has frequently caused me performance issues. Using this prevents those problems. On a machine with lots of memory, I've run into pathological behaviour with both the RHEL 5 and Ubuntu 10.04 kernels where the kswapd starts eating up CPU and swap io like mad, while doing essentially nothing. Setting swappiness to 0 delayed this behaviour but did not stop it. Given that I'm on a machine with 128G ram, I just put /sbin/swapoff -a in /etc/rc.local and viola, problem solved. -- 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] heavy swapping, not sure why
On 08/29/2011 06:12 PM, Lonni J Friedman wrote: OK, I'll reduce it to 10GB and see if there's any noticable change in performance. thanks I've never heard a report of a Linux system using more than 8GB of shared_buffers usefully, and peak performance on systems I've tested has sometimes been far less than that even. (I have one server that's stuck at 512MB!) The only report of even 10GB helping came from a Solaris test. I doubt this has anything to do with your problem, just pointing this out as future guidance. Until there's a breakthrough in the PostgreSQL buffer cache code, there really is no reason to give more than 8GB of dedicated memory to the database on Linux via shared_buffers. You're better off letting the OS do caching with it instead. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Enable PITR in Postgresql
Dear all, Today I need to enable PITR in my Postgres Production Server. I followed the below links for this purpose : http://scale-out-blog.blogspot.com/2009/02/simple-ha-with-postgresql-point-in-time.html http://www.postgresql.org/docs/8.3/static/continuous-archiving.html But I am confused about the below line , do we manually write the name of each file to archive in the archive directory : I have just installed PostgresPlus-8.4 and load some data in the demo server and at present there is only one file name (0001) and archive_status directory is empty. Now do we need to change the below command each time new log file is created or there is some standard way to do that. |archive_command = 'cp -v %p /data/pgsql/archives/%f' Thanks |
