Re: [GENERAL] Need suggestion
On Fri, Jun 03, 2011 at 07:15:40AM +0200, to...@tuxteam.de wrote: but you wouldn't have large blobs of data clobbering your regular queries. You would want to write better queries than select * from my_table_with_bytea_column; anyway. You could pass the scans and pics piecemeal between client and database At least for retrieval even BYTEA can be accessed piecemeal: select substring(bytea_column from start for number_of_bytes) Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Interpreting EXPLAIN ANALYSE
Greets, I'm trying to figure out why the following SELECT has become slow (hardware, code changes, etc) and would appreciate any comments on interpreting the EXPLAIN ANALYZE output. It *used* to take a few seconds at most, but not anymore... In figuring out which part is taking so long, what's the important bit to examine (besides making sure indexes are being used)? Presumably actual time=? If I can identify which index is taking the longest from the output below, then I can look at moving it to a faster TABLESPACE or something. explain analyze SELECT pl.sss, pl.did, pl.lid, pr.rank, plc.obl FROM plink pl LEFT JOIN prank pr USING (did) LEFT JOIN plink_count plc ON md5(plc.did)=md5(pr.did) LEFT JOIN snames s ON s.name=pl.sss WHERE s.bsit=0 AND s.disabled=0 AND s.prankignore=0 AND pl.lid = lower(E'stuff'); Nested Loop Left Join (cost=22717.85..40240.86 rows=47 width=177) (actual time=532299.546..532385.533 rows=1 loops=1) - Nested Loop Left Join (cost=22717.84..40055.65 rows=47 width=205) (actual time=532299.546..532385.533 rows=1 loops=1) - Hash Join (cost=22717.84..39936.36 rows=47 width=141) (actual time=532297.546..532383.533 rows=1 loops=1) Hash Cond: (pl.sss = s.name) - Index Scan using sk_plink3 on plink pl (cost=0.00..17155.35 rows=16718 width=141) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (lid = 'stuff'::text) - Hash (cost=22717.57..22717.57 rows=22 width=32) (actual time=461886.321..461886.321 rows=164147851 loops=1) Buckets: 1024 Batches: 8 (originally 1) Memory Usage: 1548289kB - Bitmap Heap Scan on snames s (cost=18260.53..22717.57 rows=22 width=32) (actual time=45939.971..351687.125 rows=164147851 loops=1) Recheck Cond: ((bsit = 0) AND (prankignore = 0)) Filter: (disabled = 0) - BitmapAnd (cost=18260.53..18260.53 rows=4455 width=0) (actual time=43802.298..43802.298 rows=0 loops=1) - Bitmap Index Scan on snames7 (cost=0.00..9130.13 rows=890933 width=0) (actual time=20285.896..20285.896 rows=178144776 loops=1) Index Cond: (bsit = 0) - Bitmap Index Scan on sk_snames20 (cost=0.00..9130.13 rows=890933 width=0) (actual time=22245.596..22245.596 rows=178186036 loops=1) Index Cond: (prankignore = 0) - Index Scan using sk_prank on prank pr (cost=0.00..2.53 rows=1 width=64) (actual time=2.000..2.000 rows=0 loops=1) Index Cond: (pl.did = pr.did) - Index Scan using skplink_count0 on plink_count plc (cost=0.00..3.92 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (md5(plc.did) = md5(pr.did)) Total runtime: 532386.533 ms Thanks Henry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Make problems / 3rd-party extension w/ PostgreSQL 9.1 on MacOSX
I'm running into make problems on MacOSX. While technically solved, I was wondering if there was a better way. To cut a the long story short (the gory details are @ http://stackoverflow.com/questions/6225510/), I'd like to pass the USE_PGXS=1 PGUSER=postgres variables automatically when running sudo make [command] from the shell. Might any Mac-user on the list know how to do that? Thanks in advance! Denis -- 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] Interpreting EXPLAIN ANALYSE
Greets, I'm trying to figure out why the following SELECT has become slow (hardware, code changes, etc) and would appreciate any comments on interpreting the EXPLAIN ANALYZE output. It *used* to take a few seconds at most, but not anymore... In figuring out which part is taking so long, what's the important bit to examine (besides making sure indexes are being used)? Presumably actual time=? If I can identify which index is taking the longest from the output below, then I can look at moving it to a faster TABLESPACE or something. explain analyze SELECT pl.sss, pl.did, pl.lid, pr.rank, plc.obl FROM plink pl LEFT JOIN prank pr USING (did) LEFT JOIN plink_count plc ON md5(plc.did)=md5(pr.did) LEFT JOIN snames s ON s.name=pl.sss WHERE s.bsit=0 AND s.disabled=0 AND s.prankignore=0 AND pl.lid = lower(E'stuff'); See this http://explain.depesz.com/s/THh There's something very wrong with snames - the planner expects 22 rows but gets 164147851. Which probably causes a bad plan choice or something like that. Try to analyze the snames table (and maybe increase the statistics target on the columns). regards Tomas -- 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] Mixed up protocol packets in server response?
On Thu, 2 Jun 2011 08:50:30 +0800, Craig Ringer wrote: On 1/06/2011 9:06 PM, Michal Politowski wrote: What may be the cause of this weird problem? Is it some known or unknown bug in 8.3.4 or is the application/Java side more suspected? It'd be really helpful if you could collect and examine a trace of the client/server communication using WireShark. That way you can confirm whether it is (as Tom suspects) the client side mangling its buffers, or whether the server really did send the nonsensical sequence. Actually my own money is with Tom's. It's very hard to believe Postgres would do something like this, unless it were some obvious and long fixed bug in 8.3.4. Still, trying to trace the communication makes sense, if I can convince the owners of the system to let me do it. Unfortunately this is an one in a million of successful queries (actually two in much more than a million) problem. And the next run of the application seems not to have hit it, yet. Thinking aloud: If this is, as it is to be suspected, an application-side problem, there is at first sight not much space in the application where it could hide. The data is mixed up in a driver buffer, two method calls from the standard library socket code. There is the VisibleBufferedInputStream there. Could it do something like this? Maybe if the connection was erroneously used from two threads concurrently? The connections are pooled via commons-dbcp BasicDataSource and queries are executed via Spring JdbcTemplate within Spring-configured transaction. No passing connections by hand anywhere, everything should be nicely thread-bound. Still, if not here, where could it go wrong? -- Michał Politowski Talking has been known to lead to communication if practiced carelessly. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL service won't start after bad computer time
I did some testing involving changing a computer's time, and left the time one year early (6/3/2010 instead of 2011). The PostgreSQL service now will not start up. Here's what the log says: 2011-06-03 08:46:50 EDTWARNING: autovacuum not started because of misconfiguration 2011-06-03 08:46:50 EDTHINT: Enable the track_counts option. 2011-06-03 08:46:50 EDTLOG: database system shutdown was interrupted; last known up at 2011-06-03 08:43:59 EDT 2011-06-03 08:46:50 EDTLOG: database system was not properly shut down; automatic recovery in progress 2011-06-03 08:46:50 EDTLOG: redo starts at 9/6E08AAB8 2011-06-03 08:46:50 EDTFATAL: the database system is starting up 2011-06-03 08:46:50 EDTFATAL: the database system is starting up 2011-06-03 08:46:51 EDTFATAL: the database system is starting up 2011-06-03 08:46:52 EDTFATAL: the database system is starting up 2011-06-03 08:46:53 EDTFATAL: the database system is starting up 2011-06-03 08:46:54 EDTFATAL: the database system is starting up 2011-06-03 08:46:55 EDTFATAL: the database system is starting up 2011-06-03 08:46:55 EDTFATAL: the database system is starting up 2011-06-03 08:46:56 EDTLOG: record with zero length at 9/8E080968 2011-06-03 08:46:56 EDTLOG: redo done at 9/8E07FA78 2011-06-03 08:46:56 EDTLOG: last completed transaction was at log time 2010-06-03 08:11:17.531-04 2011-06-03 08:46:56 EDTFATAL: xlog flush request 18/A2BE3510 is not satisfied --- flushed only to 9/8E080968 2011-06-03 08:46:56 EDTCONTEXT: writing block 0 of relation global/1261_vm 2011-06-03 08:46:56 EDTLOG: startup process (PID 4596) exited with exit code 1 2011-06-03 08:46:56 EDTLOG: aborting startup due to startup process failure How do we recover from this? RobR
Re: [GENERAL] Question about configuration and SSD
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Craig Ringer Sent: Donnerstag, 2. Juni 2011 10:53 On 02/06/11 16:26, Szymon Guz wrote: Hi, do we need some special configuration for SSD drives, or is that enough to treat those drives normally? Make sure the SSDs have a supercapacitor or battery backup for their write cache. If they do not, then do not use them unless you can disable write caching completely (probably resulting in horrible performance), because you WILL get a corrupt database when power fails. ... Hello, may the database also get corrupt if SSDs are only used for temp tablespaces ? And will Postgres fall back on another temp tablespace if one get down? regards, Marc Mamin -- 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] Hidden Risk w/ UPDATE Cascade and Trigger-Based Validation
On 3 June 2011 01:26, David Johnston pol...@yahoo.com wrote: Hi, I am trying to get a better understanding of how the following Foreign Keys with Update Cascades and validation trigger interact. The basic setup is a permission table where the two permission parts share a common “group/parent” which is embedded into their id/PK and which change via the FK cascade mechanism. Rest of my thoughts and questions follow the setup. I have the following schema (parts omitted/simplified for brevity since everything works as expected) CREATE TABLE userstorepermission ( userid text NOT NULL FK UPDATE CASCADE, storeid text NOT NULL FK UPDATE CASCADE, PRIMARY KEY (userid, storeid) ); FUNCTION validate() RETURNS trigger AS SELECT groupid FROM store WHERE storeid = [NEW.storeid] INTO storegroup SELECT groupid FROM user WHERE userid = [NEW.userid] INTO usergroup RAISE NOTICE ‘Validating User Store Permission U:%;%, S:%;%’, NEW.userid, usergroup, NEW.storeid, storegroup; IF (usergroup storegroup) THEN RAISE NOTICE ‘Disallow’; RETURN null; ELSE RAISE NOTICE ‘Allow’; RETURN NEW; END; CREATE TRIGGER INSERT OR UPDATE EXECUTE validate(); Basically if I change the groupid both the userid and storeid values in userstorepermission will change as well. This is desired. When I do update the shared groupid the following NOTICES are raised from the validation function above: The change for groupid was TESTSGB - TESTSGD: NOTICE: Validating User Store Permission U:tester@TESTSGB;NULL S:[TESTSGD]STORE01;TESTSGD [at this point apparently both user and store have been updated and storeid in the permission table is being change] CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET s_id = $1 WHERE $2 OPERATOR(pg_catalog.=) s_id NOTICE: Allow CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET s_id = $1 WHERE $2 OPERATOR(pg_catalog.=) s_id NOTICE: Validating User Store Permission U:tester@TESTSGD;TESTSGD S:[TESTSGD]STORE01;TESTSGD [and now the userid in the permission table gets its turn] CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET u_id = $1 WHERE $2 OPERATOR(pg_catalog.=) u_id NOTICE: Allow CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET u_id = $1 WHERE $2 OPERATOR(pg_catalog.=) u_id The end result is that both values are changed as desired but the notices, while they indirectly make sense (only one of the values can be update cascaded at a time), are somewhat confusing and thus I am not sure if I am possibly missing something that could eventually blow up in my face. I expect other similar situations will present themselves in my model so I want to get more understanding on at least whether what I am doing is safe and ideally whether the CASCADE rules possibly relax intra-process enforcement of constraints in order to allow this kind of multi-column key update to succeed. I see BUG #5505 from January of last year where Tom confirms that the trigger will fire but never addresses the second point about the referential integrity check NOT FAILING since the example’s table_2 contains a value not present in table_1… Conceptually, as long as I consistently update ALL the relevant FKs the initial and resulting state should remain consistent but only with a different value. I’ll probably do some more playing with “missing” a FK Update Cascade and see whether the proper failures occurs but regardless some thoughts and/or pointers are welcomed. Hmm, perhaps it would be better if your validation trigger raised an exception in the disallow case, rather than risk silently breaking the FK (even if you get to a point where you think that can't happen). Regards, Dean -- 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] Mixed up protocol packets in server response?
Michal Politowski mpol...@meep.pl writes: Thinking aloud: If this is, as it is to be suspected, an application-side problem, there is at first sight not much space in the application where it could hide. The data is mixed up in a driver buffer, two method calls from the standard library socket code. There is the VisibleBufferedInputStream there. Could it do something like this? Maybe if the connection was erroneously used from two threads concurrently? The connections are pooled via commons-dbcp BasicDataSource and queries are executed via Spring JdbcTemplate within Spring-configured transaction. No passing connections by hand anywhere, everything should be nicely thread-bound. Still, if not here, where could it go wrong? You'd probably be better off asking these questions in pgsql-jdbc ... a lot of us here don't even speak Java. 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] Mixed up protocol packets in server response?
On Fri, 3 Jun 2011 09:53:59 -0400, Tom Lane wrote: [...] You'd probably be better off asking these questions in pgsql-jdbc ... a lot of us here don't even speak Java. Good point. Will try the other list. Thank you for your help. -- Michał Politowski Talking has been known to lead to communication if practiced carelessly. -- 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] Hidden Risk w/ UPDATE Cascade and Trigger-Based Validation
David Johnston pol...@yahoo.com writes: I am trying to get a better understanding of how the following Foreign Keys with Update Cascades and validation trigger interact. The basic setup is a permission table where the two permission parts share a common group/parent which is embedded into their id/PK and which change via the FK cascade mechanism. Rest of my thoughts and questions follow the setup. Well, the short answer is that there's not very much behind the curtain here. The FK CASCADE mechanisms just run SQL queries (like the ones you showed in CONTEXT lines) to perform the necessary adjustments of the referencing table when something changes in the referenced table. If you have a trigger on the referencing table that prevents some of these updates, then the updates don't get done ... and the result will be that the FK condition no longer holds everywhere. It might be safer if your trigger actually threw errors, rather than silently disabling such updates. Then at least the original referenced-table update would get rolled back and the two tables would remain consistent. There have been occasional discussions of how to make this stuff a bit cleaner/safer, but it's hard to see what to do without basically breaking the ability to have user-defined triggers on the referenced table. There are lots of safe and useful things such a trigger can do; but editorializing on the effects of an FK update query isn't one of them. 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] Interpreting EXPLAIN ANALYSE
On Fri, June 3, 2011 13:57, t...@fuzzy.cz wrote: See this http://explain.depesz.com/s/THh There's something very wrong with snames - the planner expects 22 rows but gets 164147851. Which probably causes a bad plan choice or something like that. Try to analyze the snames table (and maybe increase the statistics target on the columns). Thanks - like you say, looks like the interesting bit is: rows=22 -- rows=164147851 for table snames. Nice online tool you have there my china! Cheers Henry -- 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] PostgreSQL service won't start after bad computer time
On 6/3/11 8:52:15 AM, Rob Richardson wrote: I did some testing involving changing a computer’s time, and left the time one year early (6/3/2010 instead of 2011). The PostgreSQL service now will not start up. Here’s what the log says: 2011-06-03 08:46:50 EDTWARNING: autovacuum not started because of misconfiguration 2011-06-03 08:46:50 EDTHINT: Enable the track_counts option. 2011-06-03 08:46:50 EDTLOG: database system shutdown was interrupted; last known up at 2011-06-03 08:43:59 EDT 2011-06-03 08:46:50 EDTLOG: database system was not properly shut down; automatic recovery in progress 2011-06-03 08:46:50 EDTLOG: redo starts at 9/6E08AAB8 2011-06-03 08:46:50 EDTFATAL: the database system is starting up 2011-06-03 08:46:50 EDTFATAL: the database system is starting up 2011-06-03 08:46:51 EDTFATAL: the database system is starting up 2011-06-03 08:46:52 EDTFATAL: the database system is starting up 2011-06-03 08:46:53 EDTFATAL: the database system is starting up 2011-06-03 08:46:54 EDTFATAL: the database system is starting up 2011-06-03 08:46:55 EDTFATAL: the database system is starting up 2011-06-03 08:46:55 EDTFATAL: the database system is starting up 2011-06-03 08:46:56 EDTLOG: record with zero length at 9/8E080968 2011-06-03 08:46:56 EDTLOG: redo done at 9/8E07FA78 2011-06-03 08:46:56 EDTLOG: last completed transaction was at log time 2010-06-03 08:11:17.531-04 2011-06-03 08:46:56 EDTFATAL: xlog flush request 18/A2BE3510 is not satisfied --- flushed only to 9/8E080968 2011-06-03 08:46:56 EDTCONTEXT: writing block 0 of relation global/1261_vm 2011-06-03 08:46:56 EDTLOG: startup process (PID 4596) exited with exit code 1 2011-06-03 08:46:56 EDTLOG: aborting startup due to startup process failure How do we recover from this? Reset the time to be correct. You can't seriously expect something as time-critical as a RDBMS to function properly if you jockey around making the system time all fictional? -- Bill Moran -- 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] PostgreSQL service won't start after bad computer time
Bill Moran wmo...@potentialtech.com writes: On 6/3/11 8:52:15 AM, Rob Richardson wrote: I did some testing involving changing a computers time, and left the time one year early (6/3/2010 instead of 2011). The PostgreSQL service now will not start up. Heres what the log says: 2011-06-03 08:46:56 EDTLOG: record with zero length at 9/8E080968 2011-06-03 08:46:56 EDTLOG: redo done at 9/8E07FA78 2011-06-03 08:46:56 EDTLOG: last completed transaction was at log time 2010-06-03 08:11:17.531-04 2011-06-03 08:46:56 EDTFATAL: xlog flush request 18/A2BE3510 is not satisfied --- flushed only to 9/8E080968 2011-06-03 08:46:56 EDTCONTEXT: writing block 0 of relation global/1261_vm How do we recover from this? Reset the time to be correct. I don't think that failure has anything to do with system clock time. It looks more like filesystem corruption or missing pg_xlog files. 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] PostgreSQL service won't start after bad computer time
My thanks for your replies. We used pg_resetxlog to clear things up. The database was not in active use, so the loss of the transactions didn't matter. RobR -- 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] Need suggestion
Thanks for the replies, and suggestion from Ognjen, Ben Chobot, John R Pierce, Tomás, and Karsten... I checked the links, and I decided, that I cannot decide :-) because I don't know, how large could be the infrastructure for this. If I store the images, and scanned docus in the database, a radically larger enviroment will be needed, than if I store only the link. My boss will decide, and I will implement anything, that he wants, I just wanted to collect some experience, that you have, and that you have provided form me, and many thanks for it :-) Thanks again, Regards, Carl. 2011/6/3 Karsten Hilbert karsten.hilb...@gmx.net On Fri, Jun 03, 2011 at 07:15:40AM +0200, to...@tuxteam.de wrote: but you wouldn't have large blobs of data clobbering your regular queries. You would want to write better queries than select * from my_table_with_bytea_column; anyway. You could pass the scans and pics piecemeal between client and database At least for retrieval even BYTEA can be accessed piecemeal: select substring(bytea_column from start for number_of_bytes) Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Mixed up protocol packets in server response?
transaction. No passing connections by hand anywhere, everything should be nicely thread-bound. Still, if not here, where could it go wrong? I have seen two cases in my career where there was an evil box on the network that corrupted the traffic. The first was a very long time ago (in the late '80s) but the second was only a couple of years ago and presented with very similar symptoms to your report. This happened at a consulting client's site (actually between two sites). Weird franken-packets showed up once in a while, leading to a protocol decode failure. Luckily we had been involved in writing both the client and the server, and therefore had a high degree of confidence that they were correct. The network administrators denied strongly that they had any equipment deployed that touched the payload of any packet. They denied this several times. Eventually we were able to take packet traces on both client and server machines, correlate the traffic (not necessarily an easy task), and prove conclusively that what had been sent from one end did not show up intact at the other end. A few days later the network people revealed that they had some sort of firewall/traffic management box that was mangling the traffic. Having said that, bugs in buffer management code are also not uncommon, and can manifest intermittently since they may be triggered by specific boundary conditions, specific received data buffer size, and so on. I have also seen once case of data leaking between threads in an unpleasant and intermittent way in a Java application, in buffer management code that attempted to avoid GC overhead by re-using buffers across sessions. So that's definitely a non-zero possibility too. -- 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] Interpreting EXPLAIN ANALYSE
On Fri, June 3, 2011 13:57, t...@fuzzy.cz wrote: There's something very wrong with snames - the planner expects 22 rows but gets 164147851. Which probably causes a bad plan choice or something like that. Try to analyze the snames table (and maybe increase the statistics target on the columns). ANALYZE is your friend indeed, like a nice cold beer... from over 500k ms to 1 ms. I was running ANALYZE on one of the tables in the join, but not the others... sigh -- 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] Interpreting EXPLAIN ANALYSE
On Fri, June 3, 2011 13:57, t...@fuzzy.cz wrote: There's something very wrong with snames - the planner expects 22 rows but gets 164147851. Which probably causes a bad plan choice or something like that. Try to analyze the snames table (and maybe increase the statistics target on the columns). ANALYZE is your friend indeed, like a nice cold beer... from over 500k ms to 1 ms. I was running ANALYZE on one of the tables in the join, but not the others... sigh So you have turned off autovacuum (that should handle this automatically) or you're running an old version (autovacuum was enabled by default in 8.3 IIRC). regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why are IDLE connections using cpu according to TOP.
I have an overloaded DB and I see several IDLE connections that are using significant CPU.. (Not Idle in transaction) Why would an idle process be eating so much cpu? Or is it not actually idle? Here is an example from pg_top: last pid: 11821; load avg: 6.11, 6.32, 7.64; up 1+21:05:31 50 processes: 3 running, 42 sleeping, 5 uninterruptable CPU states: 21.7% user, 0.0% nice, 7.8% system, 46.9% idle, 23.6% iowait Memory: 29G used, 149M free, 13M buffers, 27G cached Swap: PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND 4779 postgres 200 4383M 573M disk3:16 4.79% 39.42% postgres: gpup gpup 10.202.99.5(46391) UPDATE 11591 postgres 200 4383M 108M sleep 0:12 2.08% 19.61% postgres: gpup gpup 10.202.99.6(52459) idle 4191 postgres 200 4384M 709M sleep 4:33 2.50% 19.41% postgres: gpup gpup 10.202.99.6(42288) idle 10942 postgres 200 4383M 242M sleep 0:42 5.08% 16.86% postgres: gpup gpup 10.202.99.5(58373) idle 10930 postgres 200 4390M 281M sleep 0:43 1.62% 15.30% postgres: gpup gpup 10.202.99.6(52273) idle 11571 postgres 200 4390M 210M run 0:25 4.32% 14.51% postgres: gpup gpup 10.202.99.6(52455) SELECT 11533 postgres 200 4383M 109M run 0:14 2.31% 12.75% postgres: gpup gpup 10.202.99.6(52453) SELECT 7494 postgres 200 4384M 1611M disk2:31 2.44% 12.35% postgres: gpup gpup 10.202.99.6(53620) SELECT
Re: [GENERAL] Why are IDLE connections using cpu according to TOP.
On Fri, Jun 3, 2011 at 3:15 PM, bubba postgres bubba.postg...@gmail.com wrote: I have an overloaded DB and I see several IDLE connections that are using significant CPU.. (Not Idle in transaction) Why would an idle process be eating so much cpu? Or is it not actually idle? Because there's often a difference in time between when the process is sampled for how hard it's working and the output that says what it's doing. I.e. it was working hard, then when we looked at the part that says what it's doing, it's not idle. -- 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] Need suggestion
another option is using sqlite for storing images. All data is in single file. (or files if you organize it that way) easier backup etc... you have some db benefits and retaining solid speed vs file system. Haven't used this, but seems as viable option to explore. Esmin On Fri, Jun 3, 2011 at 6:33 PM, Carl von Clausewitz clausewit...@gmail.comwrote: Thanks for the replies, and suggestion from Ognjen, Ben Chobot, John R Pierce, Tomás, and Karsten... I checked the links, and I decided, that I cannot decide :-) because I don't know, how large could be the infrastructure for this. If I store the images, and scanned docus in the database, a radically larger enviroment will be needed, than if I store only the link. My boss will decide, and I will implement anything, that he wants, I just wanted to collect some experience, that you have, and that you have provided form me, and many thanks for it :-) Thanks again, Regards, Carl. 2011/6/3 Karsten Hilbert karsten.hilb...@gmx.net On Fri, Jun 03, 2011 at 07:15:40AM +0200, to...@tuxteam.de wrote: but you wouldn't have large blobs of data clobbering your regular queries. You would want to write better queries than select * from my_table_with_bytea_column; anyway. You could pass the scans and pics piecemeal between client and database At least for retrieval even BYTEA can be accessed piecemeal: select substring(bytea_column from start for number_of_bytes) Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [HACKERS] Postmaster holding unlinked files for pg_largeobject table
[ For future reference, -general is the appropriate list. Moving discussion there. ] On Sat, 2011-06-04 at 00:45 +0300, Alexander Shulgin wrote: We've noticed that free disk space went down heavily on a system, and after a short analysis determined that the reason was that postmaster was holding lots of unlinked files open. A sample of lsof output was something like this: ... Restarting PostgreSQL obviously helps the issue and the disk space occupied by those unlinked files (about 63GB actually) is reclaimed. Normally postgres closes unlinked files during a checkpoint. How long between checkpoints on this system? Is it possible that you noticed before postgresql caused an automatic checkpoint? Also, you can do a manual checkpoint with the CHECKPOINT command. Regards, Jeff Davis -- 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] Need suggestion
On 06/03/11 3:09 PM, Esmin Gracic wrote: another option is using sqlite for storing images. All data is in single file. (or files if you organize it that way) easier backup etc... you have some db benefits and retaining solid speed vs file system. Haven't used this, but seems as viable option to explore. a single multi-terabyte file?what a *wonderful* idea. *NOT* -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
