[GENERAL] tcp_keepalives_idle ignored
Hello all, PG: 8.2.4 We have a very busy setup using multiple clusters, slony, etc. My problem relates to the number of postgres procs increasing, and not decreasing when idle. I eventually end up with thousands of idle processes listening on /tmp/.s.PGSQL.5432 and not quitting (eventually bumping into max_connections). I have tried setting tcp_keepalives_idle = 120 (eg), then restarting PG, but SHOW ALL; shows tcp_keepalives_idle=0 (ignoring my setting). I have even tried setting setting /proc/sys/net/ipv4/tcp_keepalive_time directly, but the idle procs just keep increasing in number. tcp_keepalives_interval and tcp_keepalives_count I have left on default. After a few hours worth of running, theres a few thousand idle postgres procs, and they're all idle... Any idea what's going on here? Thanks Henry ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] tcp_keepalives_idle ignored
"henry" <[EMAIL PROTECTED]> writes: > tcp_keepalives_interval and tcp_keepalives_count I have left on default. > After a few hours worth of running, theres a few thousand idle postgres > procs, and they're all idle... Are you sure the clients are actually gone? tcp keepalives are only going to help if you have clients disappearing -- such as networks being unplugged or client machines getting turned off. Under normal conditions when a client disconnects the postgres process will go away immediately. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] tcp_keepalives_idle ignored
> We have a very busy setup using multiple clusters, slony, etc. My problem > relates to the number of postgres procs increasing, and not decreasing > when idle. I eventually end up with thousands of idle processes listening > on /tmp/.s.PGSQL.5432 and not quitting (eventually bumping into > max_connections). What about your client session timeouts? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] tcp_keepalives_idle ignored
On Sat, January 12, 2008 1:20 pm, Gregory Stark wrote: > "henry" <[EMAIL PROTECTED]> writes: > >> tcp_keepalives_interval and tcp_keepalives_count I have left on default. >> After a few hours worth of running, theres a few thousand idle postgres >> procs, and they're all idle... > > Are you sure the clients are actually gone? tcp keepalives are only going hmm, I think you're right - in a way. This is beginning to look like a friggin leak somewhere. What's supposed to be persistent connections in long-lived processes are not so persistent after all (and the old connections are not being released). Thanks Gregory. Henry ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] tcp_keepalives_idle ignored
"henry" <[EMAIL PROTECTED]> writes: > I have tried setting tcp_keepalives_idle = 120 (eg), then restarting PG, > but SHOW ALL; shows tcp_keepalives_idle=0 (ignoring my setting). Just FYI, this is the expected behavior on platforms where the kernel doesn't allow adjustment of the TCP keepalive parameters from userspace. You didn't say what you are running the server on, but the reference to /proc/sys makes me think it's Linux ... which seems odd, because modern Linuxen do allow these things to be adjusted. [ thinks... ] Maybe you were inspecting the value in a Unix-socket connection instead of a TCP connection? This is all irrelevant to your real problem, to judge by the rest of the thread, but I'm curious. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Postgresql 8.2.4 on linux-sparc problem
Hello, all. I have a Postgresql 8.2.5 running on gentoo linux on sun fire v240 server with storage 3310. And sometimes I have a problem: time to time one postgres process starts using all CPU time. I can't kill this process. I can't stop postgres. I can't shutdown server at all from OS, only reset helps. When it happen (CPU usage is 100%) for about 30 minutes DB works (new connections may establish, queries works fine). But in 30 minutes DB stop responding. I understand that this description can't give you enough information to give an advice what to do. But I don't know what to collect from OS/ PG when it will happen again. Could you give me an advice what should I do when next time one of process will use all CPU time? I guess that I can collect stack from this process at that time by using GDB or something like it. But I have no experience how exactly to do it. About DB. It is about 40Gb statistical DB. Every minute some processes inserts data collected from different sources. The biggest part of types of statements from clients is INSERT. Also, I have a WEB interface to this DB which shows a lot of graphs. I have some aggregation functions in PL/PGSQL which SELECT info and then INSERT or UPDATE it in some tables. I never use DELETE (except some cases. but I'm sure that problem appears not while DELETE). Every day scripts inserts data from the same sources via the same queries. But problem appears at different time. More info: postgres=# show server_version; server_version 8.2.4 machupicchu ~ # uname -a Linux machupicchu 2.6.20-gentoo-r8 #1 SMP Fri Jun 22 09:51:20 MSD 2007 sparc64 sun4u TI UltraSparc IIIi (Jalapeno) GNU/Linux machupicchu pgdata # grep -v "^#" postgresql.conf | sort -u autovacuum = on autovacuum_analyze_scale_factor = 0.1 autovacuum_analyze_threshold = 250 autovacuum_naptime = 10min autovacuum_vacuum_scale_factor = 0.2 autovacuum_vacuum_threshold = 500 client_min_messages = notice constraint_exclusion = on datestyle = 'iso, mdy' enable_bitmapscan = on enable_hashagg = on enable_hashjoin = on enable_indexscan = on enable_mergejoin = on enable_nestloop = on enable_seqscan = on enable_sort = on enable_tidscan = on lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' listen_addresses = '*' log_directory = 'pg_log' log_duration = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_line_prefix = '%m, %s, %r, %p, ' log_min_duration_statement = 1000 log_min_error_statement = error log_min_messages = error log_statement = 'all' log_truncate_on_rotation = off maintenance_work_mem = 1572864 max_connections = 100 max_fsm_pages = 153600 redirect_stderr = on shared_buffers = 24MB stats_block_level = on stats_command_string = on stats_row_level = on stats_start_collector = on work_mem = 1048576 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] know the schema name in a trigger
danilo.juvinao napisal 2008-01-12 01:18: Hello, i want know how can i get the schema name that execute a trigger. for example, if a have a schema "myschema" and a table "mytable" and it have a trigger procedure, i want know inside the trigger procedure, with plpgsql, the shcema name "myschema".. Did you try function current_schema() ? -- Regards, Tomasz Myrta ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgresql 8.2.4 on linux-sparc problem
Zelinskiy Alexander <[EMAIL PROTECTED]> writes: > I understand that this description can't give you enough information > to give an advice what to do. But I don't know what to collect from OS/ > PG when it will happen again. Could you give me an advice what should > I do when next time one of process will use all CPU time? Before you get into that, try reducing these parameters: > maintenance_work_mem = 1572864 > work_mem = 1048576 They are way too high, especially the second one. But if that doesn't help: > I guess that I can collect stack from this process at that time by > using GDB or something like it. But I have no experience how exactly > to do it. Yup, that's the thing to do when you don't know what's happening. The procedure is: Determine PID of the troublesome backend (use ps or whatever). As root or the postgres user, do $ gdb /path/to/postgres-executable PID ... introductory noise, but the list of libraries can be useful ... gdb> bt ... useful info here ... gdb> quit OK to detach? y $ Necessary advance preparation: be sure gdb is installed and you have a debug-enabled postgres (install debuginfo RPM if needed). I'd suggest practicing and making sure that the backtrace shows routine names and parameter values. As an example, a backtrace I was just looking at looked in part like this: #0 0x00310202e174 in xmlCleanupCharEncodingHandlers () from /usr/lib64/libxml2.so.2 #1 0x003102036725 in xmlCleanupParser () from /usr/lib64/libxml2.so.2 #2 0x00660e39 in xpath (fcinfo=) at xml.c:3452 #3 0x0053601e in ExecMakeFunctionResult (fcache=0xb32f60, econtext=0xb32e68, isNull=0xb33ed8 "", isDone=0xb33f90) at execQual.c:1351 #4 0x005343c6 in ExecProject (projInfo=, isDone=0x0) at execQual.c:4601 #5 0x0053f5a5 in ExecAgg (node=0xb32b58) at nodeAgg.c:989 #6 0x00533eaa in ExecProcNode (node=0xb32b58) at execProcnode.c:394 I do not have debuginfo installed for libxml2, thus the first two lines provide very little information. The rest look more like what a developer would like to see. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres and MySQL Rosetta stone??
In article <[EMAIL PROTECTED]>, Erik Jones <[EMAIL PROTECTED]> wrote: >I'd suggest getting a different book that gives examples in >Postgres or, failing that, read the Postgres manual and learn >how to translate them yourself. I found O'Reilly's SQL Cookbook quite good for showing the differences: http://www.cornhobble.com/am/0596009763 Every recipe includes discussion of how to approach the problem in Orance, Postgresql, mysql, and M$. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to safely compare transaction id?
On 1/12/08, alphax <[EMAIL PROTECTED]> wrote: > Thanks. Actually, I want to compares the system columns(xmin, xmax, > ctid) with the tid returned by txid functions declared in > > http://developer.postgresql.org/pgdocs/postgres/functions-info.html#FUNCTIONS-TXID-SNAPSHOT > > > I want to determines a given record which visible to current transaction > whether or not be updated after some time point, that time point is > indicated by aother transaction id started and committed in past time. > How can I safely do that? You cannot compare txids with each other and determine visibility, you need snapshots for that. Eg. PgQ (generic queue) is implemented in following way: - Current txid is stored with data rows. - Periodically txid_snapshot is stored to separate table (pgq.tick). - later when reading data, 2 snapshots are taken from pgq.tick, and from them are txids determined that were committed between those. - The data rows for the txids are fetched from data tables then. AFAIK this is only way how to implement robust and high-performance queue in otherwise generic RDBMS. > By the way, Can I think that the value of system column "ctid" of an > record is the logical "current version" of that record, and used to > compare with txid returned by "FUNCTIONS-TXID-SNAPSHOT"? No, it is just physical location of the row. -- marko ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Prepared Statements
On Thu, 10 Jan 2008, [EMAIL PROTECTED] wrote: ah! So it doesn't help if it's the same statement, it has to be the same object! So DBCP has a statement pool like a map, say Map so it can fetch the reference to already existing prepared Statement by looking at the statement itself, right? Exactly. But JDBC itself uses the "PREPARE" sql command, right? So the statement is not really unnamed its name is "" like prepare "" as select * from table; execute ""; But i can't see any DEALLOCATE statements in my log file. The JDBC driver does not use SQL level PREPARE / DEALLOCATE calls, but instead uses protocol level commands that are pretty much equivalent. The logging process tries to log sql and protocol level commands the same way which is why it shows up that way in the log. Perhaps the server should log the protocol level deallocate as well? What do you mean with "longer lifespan"? Doesn't the JDBC driver uses the PREPARE Sql Statement and therefore the prepared Statement has the same lifespan as the connection? If so, as connections are pooled and never closed, the prepared Statement will last forever. What if the table analyzes changes and a better execution plan could be found? Data and stats changes do not trigger a replan. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] UPDATE .. JOIN?
Hi. I was wondering if I could do something similar to this in Postgres and if yes how? UPDATE table1 SET blah = 1 FROM table1 INNER JOIN table2 ON table1.id = table2.t1id If not, is there any way to make UPDATE ... WHERE id IN () use indexes? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Many 8.3 server crashes in xmlCleanupCharEncodingHandlers()
"Matt Magoffin" <[EMAIL PROTECTED]> writes: > Hello, I'm using 8.3b4 and keep experiencing server crash when I execute > various queries using XML functions. Please see if it's better with 8.3RC1 plus this patch: http://archives.postgresql.org/pgsql-committers/2008-01/msg00190.php regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)
"Lawrence Oluyede" <[EMAIL PROTECTED]> writes: > Here it is: > postgres$ gdb /usr/local/pgsql/bin/postgres core.1600 > (gdb) bt > #0 0x082c101c in pfree (pointer=0x8472f00) at mcxt.c:591 > #1 0xb7e46513 in xmlCleanupCharEncodingHandlers () from /usr/lib/libxml2.so.2 > #2 0xb7e4f091 in xmlCleanupParser () from /usr/lib/libxml2.so.2 > #3 0x082940e4 in xpath (fcinfo=0xbfee59c4) at xml.c:3441 I think we finally found the problem. Please see if things are more stable with 8.3RC1 plus this patch: http://archives.postgresql.org/pgsql-committers/2008-01/msg00190.php regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Prepared Statements
Kris Jurka <[EMAIL PROTECTED]> writes: > On Thu, 10 Jan 2008, [EMAIL PROTECTED] wrote: >> What do you mean with "longer lifespan"? Doesn't the JDBC driver uses the >> PREPARE Sql Statement and therefore the prepared Statement has the same >> lifespan as the connection? If so, as connections are pooled and never >> closed, the prepared Statement will last forever. What if the table analyzes >> changes and a better execution plan could be found? > Data and stats changes do not trigger a replan. Note that this is no longer true as of 8.3: a stats update from ANALYZE (either manual or autovacuum) will trigger invalidation of cached plans. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] UPDATE .. JOIN?
On Jan 12, 2008 5:22 PM, Sergei Shelukhin <[EMAIL PROTECTED]> wrote: > Hi. > > I was wondering if I could do something similar to this in Postgres and > if yes how? > > UPDATE table1 SET blah = 1 FROM table1 > INNER JOIN table2 ON table1.id = table2.t1id UPDATE table1 t1 SET blah = 1 FROM table2 t2 WHERE t1.id = t2.t1id > If not, is there any way to make UPDATE ... WHERE id IN () use indexes? It depends. Read the docs: http://www.postgresql.org/docs/8.2/static/using-explain.html http://www.postgresql.org/docs/8.2/static/planner-stats.html http://www.postgresql.org/docs/8.2/static/planner-stats-details.html ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] UPDATE .. JOIN?
Rodrigo E. De León Plicet wrote: On Jan 12, 2008 5:22 PM, Sergei Shelukhin <[EMAIL PROTECTED]> wrote: Hi. I was wondering if I could do something similar to this in Postgres and if yes how? UPDATE table1 SET blah = 1 FROM table1 INNER JOIN table2 ON table1.id = table2.t1id UPDATE table1 t1 SET blah = 1 FROM table2 t2 WHERE t1.id = t2.t1id Hmmm. What if there's more than one table? Is "from x,y" a viable option? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] ERROR during WAL replay
Hi All, We were trying to move a big database from one machine to the other using PITR mechanism. We hit the following LOG message in during the recovery (WAL replay) process" LOG: incorrect resource manager data checksum in record at 111/A7738C8 I had used this procedure to do such migrations in the past, and never had any problem. This particular DB is pretty huge, and under load, so I would like to consider other options before even trying to redo the whole process. Here's the whole scanario: The DB is about 420 GB in size. We want to move it to another box with more storage. I have set up WAL archival, executed pg_start_backup(), started $DATA/ copy (took just over 24 hours), executed pg_stop_backup(), and while the WAL archival is still enabled I started recovery on the second machine, using the $DATA/ copied earlier I have the compressed WAL file, so if someone wishes to take a peek at it it will be available... Considering this is a weekend, any help/suggestion at all would be great. Thanks and best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] UPDATE .. JOIN?
On Jan 13, 2008 12:05 AM, I said > It's all in the docs: > http://www.postgresql.org/docs/8.2/static/sql-update.html To clarify, you can use the direct form, without using a subselect: UPDATE table1 t1 SET blah = 1 FROM table2 t2 JOIN table3 t3 ON t2.id = t3.t2id WHERE t1.id = t2.t1id Lookup "fromlist" (without the quotes) on the quoted link. Regards. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] UPDATE .. JOIN?
On Jan 12, 2008 11:26 PM, Sergei Shelukhin <[EMAIL PROTECTED]> wrote: > Hmmm. What if there's more than one table? Is "from x,y" a viable option? UPDATE table1 t1 SET blah = 1 FROM ( SELECT t2.t1id FROM table2 t2 JOIN table3 t3 ON t2.id = t3.t2id ) foobar WHERE t1.id = foobar.t1id It's all in the docs: http://www.postgresql.org/docs/8.2/static/sql-update.html Good luck. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.2.4 serious slowdown
Actually I just checked and the plan is exactly the same for those 2 clauses. Original Message Subject: Re:8.2.4 serious slowdown From: Sim Zacks <[EMAIL PROTECTED]> To: Date: Sunday, January 13, 2008 07:59:22 AM > How would you rewrite something like: > WHERE (COALESCE(b.quantity, 0) - COALESCE(b.deliveredsum, 0)) > 0; > I could write: > where case when b.quantity is null then 0 else b.quantity end - case > when b.deliveredsum is null then 0 else b.deliveredsum end > 0 > > It is butt ugly, but is that the most efficient way to write it in 8.2.4? > > Sim > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.4 serious slowdown
How would you rewrite something like: WHERE (COALESCE(b.quantity, 0) - COALESCE(b.deliveredsum, 0)) > 0; I could write: where case when b.quantity is null then 0 else b.quantity end - case when b.deliveredsum is null then 0 else b.deliveredsum end > 0 It is butt ugly, but is that the most efficient way to write it in 8.2.4? Sim Pavel Stehule wrote: Hello On 11/01/2008, Sim Zacks <[EMAIL PROTECTED]> wrote: I changed it to "where f.commited is not true" and the query now takes 1 second as opposed to 60. (much faster then the 3 seconds it took on 8.0.1, which could also be because of the coalesce there) Is it considered better practice (or more efficient) to always use (x is not or x=value) instead of coalesce? Or does it make more sense to turn on the option "transform_null_equals"? You can use without coalesce() = some operator IS DISTINCT FROM ... . Use coalesce only if you need some NON NULL value. for you sample where f.commited IS DISTINCT FROM true; operator IS DISTINCT FROM is NULL insensitive Regards Pavel Stehule Thank you much Sim I assume that the original query is something along the lines of d left join f on (...) where coalesce(f.commited, false) = false In the meantime, Sim would probably have better luck if he restructured this particular clause in some other way, say where f.commited is not true or where f.commited = false or f.commited is null Note also that he really ought to move up to 8.2.6, as 8.2.4 is not very sane about what IS NULL means for a left join's result. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
