Re: [PERFORM] 1 or 2 servers for large DB scenario.
Matthew wrote: On Fri, 25 Jan 2008, Greg Smith wrote: If you're seeing <100TPS you should consider if it's because you're limited by how fast WAL commits can make it to disk. If you really want good insert performance, there is no substitute for getting a disk controller with a good battery-backed cache to work around that. You could just put the WAL xlog directory on a RAID-1 pair of disks to accelerate that, you don't have to move the whole database to a new controller. Hey, you *just* beat me to it. Yes, that's quite right. My suggestion was to move the whole thing, but Greg is correct - you only need to put the WAL on a cached disc system. That'd be quite a bit cheaper, I'd imagine. Another case of that small SSD drive being useful, I think. PostgreSQL 8.3 will have "asynchronous commits" feature, which should eliminate that bottleneck without new hardware, if you can accept the loss of last few transaction commits in case of sudden power loss: http://www.postgresql.org/docs/8.3/static/wal-async-commit.html -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Slow set-returning functions
>> Is there any way that I can see what execution plan is being used >> internally by the functions? >> > > Not directly, but you can do this: > > > postgres=# PREPARE p (int4) AS SELECT id FROM foo WHERE lower(name) LIKE > 'foo' ORDER BY id OFFSET 0 LIMIT $1; > PREPARE > > postgres=# EXPLAIN EXECUTE p(100); QUERY PLAN > - > Limit (cost=0.00..49.18 rows=2 width=4) > > -> Index Scan using foo_pkey on foo (cost=0.00..614.77 rows=25 width=4) > Filter: (lower(name) ~~ 'foo'::text) > (3 rows) I think that having the ability to see the execution plans being used by queries inside functions would be quite useful. More generally, I would like to be able to log the execution plans of all queries issued by an application (in my case I am working on a web application, where some of the queries are auto-generated by Hibernate). I've tried setting debug_print_plan, but the results are a little hard to interpret. As an experiment, I have tried hacking around a little with the code. This is my first foray into the source code, so I might well be missing something, but basically I added a new configuration parameter debug_explain_plan which causes all queries to be instrumented and ExecutorRun() to call explain_outNode() at the end, logging the results at level DEBUG1. It seems to work quite well as a development aid for my web application. It is also useful from a psql session (similar to AUTOTRACE in Oracle): test=# create table foo(a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE test=# insert into foo values (1), (2), (3), (4), (5); INSERT 0 5 test=# set debug_explain_plan=true; SET test=# set client_min_messages=debug1; SET test=# select count(*) from foo where a>3; DEBUG: --- query plan --- DETAIL: Aggregate (cost=32.45..32.46 rows=1 width=0) (actual time=0.066..0.068 rows=1 loops=1) -> Bitmap Heap Scan on foo (cost=10.45..30.45 rows=800 width=0) (actual time=0.039..0.043 rows=2 loops=1) Recheck Cond: (a> 3) -> Bitmap Index Scan on foo_pkey (cost=0.00..10.25 rows=800 width=0) (actual time=0.025..0.025 rows=2 loops=1) Index Cond: (a> 3) Query runtime: 0.089 ms count --- 2 (1 row) test=# create function foo() returns int as 'select max(a) from foo;' language sql stable; CREATE FUNCTION test=# select * from foo where a=foo(); DEBUG: --- query plan --- DETAIL: Result (cost=0.04..0.05 rows=1 width=0) (actual time=0.044..0.044 rows=1 loops=1) InitPlan -> Limit (cost=0.00..0.04 rows=1 width=4) (actual time=0.032..0.034 rows=1 loops=1) -> Index Scan Backward using foo_pkey on foo (cost=0.00..84.25 rows=2400 width=4) (actual time=0.025..0.025 rows=1 loops=1) Filter: (a IS NOT NULL) Query runtime: 0.050 ms CONTEXT: SQL function "foo" statement 1 DEBUG: --- query plan --- DETAIL: Result (cost=0.04..0.05 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=1) InitPlan -> Limit (cost=0.00..0.04 rows=1 width=4) (actual time=0.027..0.029 rows=1 loops=1) -> Index Scan Backward using foo_pkey on foo (cost=0.00..84.25 rows=2400 width=4) (actual time=0.021..0.021 rows=1 loops=1) Filter: (a IS NOT NULL) Query runtime: 0.044 ms CONTEXT: SQL function "foo" statement 1 DEBUG: --- query plan --- DETAIL: Index Scan using foo_pkey on foo (cost=0.25..8.52 rows=1 width=4) (actual time=1.638..1.642 rows=1 loops=1) Index Cond: (a = foo()) Query runtime: 1.686 ms a --- 5 (1 row) (Curious that foo() is being executed twice in this case). The CONTEXT is very useful, particularly when functions call other functions, since it gives the call stack (presumably only for SQL and PL/pgSQL functions). For top-level queries I would ideally like the CONTEXT to log the SQL being executed, but I couldn't figure out how to access that information. Anyway, I'd be interested to know if anyone has thought about doing anything like this before and if anyone else might find this useful. Dean _ Free games, great prizes - get gaming at Gamesbox. http://www.searchgamesbox.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Slow set-returning functions
Dnia 27-01-2008, N o godzinie 17:29 +, Dean Rasheed pisze: > The CONTEXT is very useful, particularly when functions call other > functions, since it gives the call stack (presumably only for SQL and > PL/pgSQL functions). For top-level queries I would ideally like the > CONTEXT to log the SQL being executed, but I couldn't figure out how > to access that information. > > Anyway, I'd be interested to know if anyone has thought about doing > anything like this before and if anyone else might find this useful. > > Dean I'd love to see that. The mentioned PREPARE workaround doesn't seem to work when executed function calls for example three another (or I don't know how to use it in such situation) - and is generally painful to use. I'm afraid I can't help you much (besides testing) but I'd be more than interested in such improvement. Regards, Marcin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 8.3rc1 Out of memory when performing update
>>"Stephen Denne" <[EMAIL PROTECTED]> writes: >>> I altered the update statement slightly, and reran the query. >>> The altered query has been running over 3 hours now, >>> without using lots of memory (38M private bytes). >>> 2046 temp files were created (2.54GB worth), >>> which have recently changed from slowly growing in size >>> to very very slowly reducing in number. To which Tom Lane replied: >>Hmm. I think what that really means is you haven't got to the part of >>the query where the leak is :-(. I then said: > It is now Friday night for me. I left the alternate query running, and will > find out on Monday what happened. Well, it is now Monday morning for me, and those temp files are still slowly reducing in number. There are now only 1629 of them left, so I'm guessing that the query is about 20% done. The PC seems to have been steadily but very slowly working away at this very simple query for close to 70 hours. I decided not to leave this query running for a fortnight to find out if I then strike the memory leak. Private Bytes had grown to 685MB I cancelled the query. Rough snapshot of what was happening with IO (a single 7200 IDE disk): The process for the update query was reading about 500KB/second , writing between 80KB/second to 200KB/second. The stats collector process was writing about 100KB/second The wal writer process was writing about 200KB/second The writer process was writing about 400KB/second Checkpoints were 10 minutes apart, taking about 85 seconds to write 1000 buffers. What could cause such poor performance? I presume that the disk was being forced to move the head a great deal. I also asked: > If I drop the fk constraint, and/or its index, would I still be affected by > the leak you found? I dropped two indexes and one fk constraint and ran VACUUM FULL VERBOSE ANALYZE document_file; As an indication of the disk performance: at its peak the vacuum process was reading and writing 20MB/seconds (sustained), completing in less than 11 minutes. I reran the original query. It used constant memory (6.7MB private bytes) It was reading 2 to 3MB/second, writing 3 to 6MB/second. The stats collector process was writing about 100KB/second The wal writer process was writing about 200KB/second The writer process was initially writing about 1MB/second, increasing to about 3MB/second Checkpoints in the middle of this query were taking up to 13 seconds to write 100 buffers. The checkpoint after the query took 300 seconds (exactly half the checkpoint interval), and was writing about 60KB/second. It wrote 2148 buffers. So dropping the fk constraint and index results in successful query execution with constant memory usage. Does this confirm that the memory leak you found is the one I was suffering from? I'd also class the extremely poor performance of the alternate query as a bug. Why take a fortnight when you could take three quarters of an hour? (Granted there where two less indexes to update, but that is still too long.) Aside: I must say that I am impressed with PostgreSQL's handling of this connection. It recovers extremely well from running out of memory, cancelling very long running queries, reloading config (to turn autovacuum off), and continues to work as expected (the 3 day old connection that is). Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Slow set-returning functions
On Jan 27, 2008 12:29 PM, Dean Rasheed <[EMAIL PROTECTED]> wrote: > >> Is there any way that I can see what execution plan is being used > >> internally by the functions? > >> > > > > Not directly, but you can do this: > > > > > > postgres=# PREPARE p (int4) AS SELECT id FROM foo WHERE lower(name) LIKE > > 'foo' ORDER BY id OFFSET 0 LIMIT $1; > > PREPARE > > > > postgres=# EXPLAIN EXECUTE p(100); QUERY PLAN > > - > > Limit (cost=0.00..49.18 rows=2 width=4) > > > > -> Index Scan using foo_pkey on foo (cost=0.00..614.77 rows=25 width=4) > > Filter: (lower(name) ~~ 'foo'::text) > > (3 rows) > > > I think that having the ability to see the execution plans being used > by queries inside functions would be quite useful. > > More generally, I would like to be able to log the execution plans of > all queries issued by an application (in my case I am working on a web > application, where some of the queries are auto-generated by > Hibernate). I've tried setting debug_print_plan, but the results are a > little hard to interpret. > > As an experiment, I have tried hacking around a little with the code. > This is my first foray into the source code, so I might well be > missing something, but basically I added a new configuration parameter > debug_explain_plan which causes all queries to be instrumented and > ExecutorRun() to call explain_outNode() at the end, logging the > results at level DEBUG1. I read your email, blinked twice, and thought: where have you been all my life! :-) (IOW, +1) merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Vacuum and FSM page size
Thanks for the advice. I used the default settings before, thought though that vacuum was a bit aggressive, ie, using too many resources. Now its taking very long. So will have to find reasonable settings in between I guess. On the other hand, if I keep the fsm_page number high enough, the system should be fine with a low number of vacuum cycles, right. As memory is not really scarce (16G, 32 bit PG though) an x million fsm_page entry should be ok. Any thoughts on that? cheers, -- tom. -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Thursday, 24. January, 2008 10:48 To: Thomas Lozza Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Vacuum and FSM page size On Jan 23, 2008 12:29 PM, Thomas Lozza <[EMAIL PROTECTED]> wrote: > Auto vacuum is enabled. Here are the settings: > > autovacuum = true > autovacuum_naptime = 900 > autovacuum_vacuum_threshold = 2000 > autovacuum_analyze_threshold = 1000 > autovacuum_vacuum_scale_factor = 0.25 > autovacuum_analyze_scale_factor = 0.18 autovacuum_vacuum_cost_delay = > 150 autovacuum_vacuum_cost_limit = 120 > > A manual vacuum takes very long (around 4 days), so maybe the cost > delay and limit or too high. Your autovacuum_vacuum_cost_delay is REALLY high. Try setting it to 10 or 20 and see if that helps. What is your plain old vacuum_cost_delay set to? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Vacuum and FSM page size
On Jan 27, 2008 5:01 PM, Thomas Lozza <[EMAIL PROTECTED]> wrote: > Thanks for the advice. > I used the default settings before, thought though that vacuum was a bit > aggressive, ie, using too many resources. Now its taking very long. So > will have to find reasonable settings in between I guess. > > On the other hand, if I keep the fsm_page number high enough, the system > should be fine with a low number of vacuum cycles, right. As memory is > not really scarce (16G, 32 bit PG though) an x million fsm_page entry > should be ok. Any thoughts on that? The issue you then run into is bloat, where you have a table or index that is 90% or so empty space, and performance on selects might suffer, especially on larger tables or indexes. What often works best is to let autovacuum handle most of your tables, then schedule individual tables to be vacuumed by cron, setting the nap time for vacuum at 20 or 30 milliseconds so they don't chew up all of your I/O ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Postgres 8.2 memory weirdness
On Fri, 25 Jan 2008, Tory M Blue wrote: I doubled the checkpoint segments yesterday and have not seen any warnings. Will run with segments of 100 for a while and see how things look.. Anyway to make sure that there is not a number between 50 and 100 that makes more sense? More segments means more disk space taken up with them and a longer crash recovery. Those are the downsides; if you can live with those there's no reason to run at <100 if that works for you. Fine-tuning here isn't really that helpful. I'm a little confused by your report through because you should still be seeing regular checkpoint warnings if you set checkpoint_warning = 3600s , they should just be spaced further apart. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 1 or 2 servers for large DB scenario.
Hi David, I have been running few tests with 8.2.4 and here is what I have seen: If fysnc=off is not an option (and it should not be an option :-) ) then commit_delay=10 setting seems to help a lot in my OLTP runs. Granted it will delay your transactions a bit, but the gain is big considering the WAL writes end up doing bigger writes under high load and got a good boost in performance due to that change (IIRC it was about 6-10% depending on load and contention). So that might help out. Curiosly I did spend why it helps out on write contention. Atleast on Solaris my observation is WAL logs then end up getting bigger than 8K (Blocksize). This meant an overall reduction in IOPS on the filesystem thats holding the logs and hence more IOPS capacity available to do do more Log writes. (Using EAStress type of benchmark, it ended up doing somewhere between 128K-256KB writes on the logs which was pretty fascinating since the benchmark does drive fair amount of WAL writes and without commit_delay, the disks were pretty saturated quickly. Also if the load is high, then the delay in transaction is pretty much non existent. (atleast what I observed with commit_delay=10 and commit_siblings left to default) Of course as already replied back, 8.3's async commit helps on top of commit_delay so thats an option if few transactions loss potential is acceptable. -Jignesh David Brain wrote: Hi, I'd appreciate some assistance in working through what would be the optimal configuration for the following situation. We currently have one large DB (~1.2TB on disk), that essentially consists of 1 table with somewhere in the order of 500 million rows , this database has daily inserts as well as being used for some semi-data mining type operations, so there are a fairly large number of indices on the table. The hardware storing this DB (a software RAID6) array seems to be very IO bound for writes and this is restricting our insert performance to ~50TPS. As we need to achieve significantly faster insert performance I have been considering splitting the table into 'new' and 'old' data, making inserts into the 'new' table (which will also help as there are really 1 insert, an update and some selects involved in populating the table), then moving the data over to the 'old' DB on a periodic basis. There would be new hardware involved, I'm thinking of HW RAID 10 to improve the write performance. The question really is, is it best to use two separate servers and databases (and have to come up with some copy process to move data from one to another), or to just add the faster storage hardware to the existing server and create a new tablespace for the 'new data' table on that hardware. Doing this would enable us to potentially move data more easily from new to old (we can't use partitioning because there is some logic involved in when things would need to be moved to 'old'). Are there any global resources that make just adding the faster storage to the existing box a bad idea (the wal_log springs to mind - although that could be moved too), that would make adding an additional server instead a better idea? Also are there any settings that might boost our TPS on the existing hardware (sync=off isn't an option.. (-: ). I have already significantly increased the various buffers, but this was mainly to improve select performance? Verson of Postgresql is 8.2.3. Thanks, David. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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: [PERFORM] 8.3rc1 Out of memory when performing update
"Stephen Denne" <[EMAIL PROTECTED]> writes: > So dropping the fk constraint and index results in successful query execution > with constant memory usage. Does this confirm that the memory leak you found > is the one I was suffering from? Well, it confirms that you were suffering from that memory leak. What's not clear is whether the leak completely explains the bad performance you saw. The leak would have resulted in extra swapping, but I wouldn't have thought it would drive the machine completely into swap hell. Would the monitoring tools you were using have shown swapping I/O? If you want to try a focused experiment, please apply the patch shown here: http://archives.postgresql.org/pgsql-committers/2008-01/msg00322.php and see if the behavior gets better. > I'd also class the extremely poor performance of the alternate query as a bug. Yeah, we should look into that. The plan you showed before estimated about 16.9M rows in "document" --- is that about right? What have you got work_mem set to? > Aside: I must say that I am impressed with PostgreSQL's handling of this > connection. It recovers extremely well from running out of memory, cancelling > very long running queries, reloading config (to turn autovacuum off), and > continues to work as expected (the 3 day old connection that is). Hey, we've had plenty of opportunity to improve the system's robustness in the face of such things ;-) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 1 or 2 servers for large DB scenario.
On Jan 25, 2008 11:36 AM, David Brain <[EMAIL PROTECTED]> wrote: > I'd appreciate some assistance in working through what would be the > optimal configuration for the following situation. > > We currently have one large DB (~1.2TB on disk), that essentially > consists of 1 table with somewhere in the order of 500 million rows , > this database has daily inserts as well as being used for some semi- > data mining type operations, so there are a fairly large number of > indices on the table. The hardware storing this DB (a software RAID6) > array seems to be very IO bound for writes and this is restricting our > insert performance to ~50TPS. > > As we need to achieve significantly faster insert performance I have > been considering splitting the table into 'new' and 'old' data, > making inserts into the 'new' table (which will also help as there are > really 1 insert, an update and some selects involved in populating the > table), then moving the data over to the 'old' DB on a periodic > basis. There would be new hardware involved, I'm thinking of HW RAID > 10 to improve the write performance. > > The question really is, is it best to use two separate servers and > databases (and have to come up with some copy process to move data > from one to another), or to just add the faster storage hardware to > the existing server and create a new tablespace for the 'new data' > table on that hardware. Doing this would enable us to potentially > move data more easily from new to old (we can't use partitioning > because there is some logic involved in when things would need to be > moved to 'old'). Are there any global resources that make just adding > the faster storage to the existing box a bad idea (the wal_log springs > to mind - although that could be moved too), that would make adding an > additional server instead a better idea? > > Also are there any settings that might boost our TPS on the existing > hardware (sync=off isn't an option.. (-: ). I have already > significantly increased the various buffers, but this was mainly to > improve select performance? I would (amalgamating suggestions from others and adding my own): *) get off raid 6 asap. raid 6 is wild wild west in database terms *) partition this table. if you have a lot of indexes on the table you might be running into random read problems. I'm not a huge fan in partitioning in most cases, but your case passes the smell test. Unique constraints are a problem, so partition wisely. *) move wal to separate device(s). you could see as much as double tps, but probably less than that. a single 15k drive will do, or two in a raid 1. contrary to the others, I would advise _against_ a ssd for the wal...wal writing is mostly sequential and ssd is unlikely to help (where ssd is most likely to pay off is in the database volume for faster random reads...likely not cost effective). *) and, for heaven's sake, if there is any way for you to normalize your database into more than one table, do so :-) merlin ---(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: [PERFORM] 8.3rc1 Out of memory when performing update
Tom Lane wrote: > "Stephen Denne" <[EMAIL PROTECTED]> writes: > > So dropping the fk constraint and index results in > successful query execution with constant memory usage. Does > this confirm that the memory leak you found is the one I was > suffering from? > > Well, it confirms that you were suffering from that memory > leak. What's > not clear is whether the leak completely explains the bad performance > you saw. The leak would have resulted in extra swapping, but > I wouldn't > have thought it would drive the machine completely into swap hell. The query crashing from out of memory did so after an hour, which isn't bad performance given the workaround with less indexes to update succeeded after 45 minutes. It was the rewritten one which I killed after 3 days. > Would the monitoring tools you were using have shown swapping I/O? I was using Process Explorer, which shows page faults and deltas, which are not included in the read & write IO stats. The query with poor IO performance wasn't swapping. > > I'd also class the extremely poor performance of the > alternate query as a bug. > > Yeah, we should look into that. The plan you showed before estimated > about 16.9M rows in "document" --- is that about right? What have you > got work_mem set to? Yes, 16894164 rows. Exactly the same number of rows in document as in document_file. [count(*) queries taking 38 and 63 seconds] work_mem appears to be left as the default 1MB I get 1023 temp files created straight away, which take four minutes (250s) to grow to about 448K each (reading @ 5MB/s writing @ 2MB/s) memory usage during this first phase slowly increased from 13.4M to 14.4M then 1023 more temp files are created, and they grow to about 2170K each (reading @ 2MB/s writing @ 2MB/s until the checkpoint starts, when the speed decreases to 200K/s, and doesn't increase again after the checkpoint finishes.) memory usage during this first phase slowly increased from 22.5M to 26.4M My concern is with what it then does. (Spends a fortnight doing really slow IO) An hour's worth of logs from during this phase show 6 checkpoints, and 6 temp files reported (which seems to coincide with them being deleted): 2008-01-26 06:02:08.086 NZDT [3432]: [233-1] LOG: checkpoint starting: time 2008-01-26 06:03:28.916 NZDT [3432]: [234-1] LOG: checkpoint complete: wrote 899 buffers (21.9%); 0 transaction log file(s) added, 0 removed, 11 recycled; write=77.798 s, sync=2.750 s, total=80.830 s 2008-01-26 06:12:08.094 NZDT [3432]: [235-1] LOG: checkpoint starting: time 2008-01-26 06:12:23.407 NZDT [3824]: [209-1] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp3824.1321", size 2224520 2008-01-26 06:12:23.407 NZDT [3824]: [210-1] STATEMENT: UPDATE ONLY document_file AS df SET document_type_id = d.document_type_id FROM document AS d WHERE d.id = document_id; 2008-01-26 06:12:24.157 NZDT [3824]: [211-1] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp3824.477", size 461356 2008-01-26 06:12:24.157 NZDT [3824]: [212-1] STATEMENT: UPDATE ONLY document_file AS df SET document_type_id = d.document_type_id FROM document AS d WHERE d.id = document_id; 2008-01-26 06:13:21.876 NZDT [3432]: [236-1] LOG: checkpoint complete: wrote 724 buffers (17.7%); 0 transaction log file(s) added, 0 removed, 17 recycled; write=71.500 s, sync=2.108 s, total=73.781 s 2008-01-26 06:22:08.024 NZDT [3432]: [237-1] LOG: checkpoint starting: time 2008-01-26 06:23:25.415 NZDT [3432]: [238-1] LOG: checkpoint complete: wrote 877 buffers (21.4%); 0 transaction log file(s) added, 0 removed, 11 recycled; write=74.141 s, sync=2.985 s, total=77.391 s 2008-01-26 06:29:36.311 NZDT [3824]: [213-1] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp3824.1350", size 2220990 2008-01-26 06:29:36.311 NZDT [3824]: [214-1] STATEMENT: UPDATE ONLY document_file AS df SET document_type_id = d.document_type_id FROM document AS d WHERE d.id = document_id; 2008-01-26 06:29:36.982 NZDT [3824]: [215-1] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp3824.516", size 463540 2008-01-26 06:29:36.982 NZDT [3824]: [216-1] STATEMENT: UPDATE ONLY document_file AS df SET document_type_id = d.document_type_id FROM document AS d WHERE d.id = document_id; 2008-01-26 06:32:08.016 NZDT [3432]: [239-1] LOG: checkpoint starting: time 2008-01-26 06:33:19.501 NZDT [3432]: [240-1] LOG: checkpoint complete: wrote 872 buffers (21.3%); 0 transaction log file(s) added, 0 removed, 15 recycled; write=69.062 s, sync=2.171 s, total=71.484 s 2008-01-26 06:42:08.101 NZDT [3432]: [241-1] LOG: checkpoint starting: time 2008-01-26 06:43:27.431 NZDT [3432]: [242-1] LOG: checkpoint complete: wrote 813 buffers (19.8%); 0 transaction log file(s) added, 0 removed, 14 recycled; write=76.579 s, sync=2.592 s, total=79.329 s 2008-01-26 06:46:45.558 NZDT [3824]: [217-1] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp3824.1940", size 2229130 2008-01-26 06:46:45.558 NZDT [3824]: [218-1] STATEMENT: UPDATE ONLY
Re: [PERFORM] Postgres 8.2 memory weirdness
On Jan 27, 2008 4:08 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > > More segments means more disk space taken up with them and a longer crash > recovery. Those are the downsides; if you can live with those there's no > reason to run at <100 if that works for you. Fine-tuning here isn't > really that helpful. > > I'm a little confused by your report through because you should still be > seeing regular checkpoint warnings if you set checkpoint_warning = 3600s , > they should just be spaced further apart. I'm not seeing any warnings at all. [idb01 ~]$ sudo cat /data/logs/pgsql-27.log | grep -i check [idb01 ~]$ sudo cat /data/logs/pgsql-26.log | grep -i check [idb01 ~]$ sudo cat /data/logs/pgsql-25.log | grep -i check [idb01 ~]$ sudo cat /data/logs/pgsql-24.log | grep -i check 2008-01-24 03:54:39 PSTLOG: checkpoints are occurring too frequently (89 seconds apart) 2008-01-24 03:54:39 PSTHINT: Consider increasing the configuration parameter "checkpoint_segments". 2008-01-24 07:26:25 PSTLOG: checkpoints are occurring too frequently (106 seconds apart) 2008-01-24 07:26:25 PSTHINT: Consider increasing the configuration parameter "checkpoint_segments". 2008-01-24 11:34:18 PSTLOG: checkpoints are occurring too frequently (173 seconds apart) 2008-01-24 11:34:18 PSTHINT: Consider increasing the configuration parameter "checkpoint_segments". Segment config still: # - Checkpoints - checkpoint_segments = 100 # bumped from 50 checkpoint_timeout = 300# range 30s-1h #checkpoint_warning = 30s # 0 is off checkpoint_warning = 3600s # 0 is off No warnings in my logs, I see some LOG information but it pertains to slon and not postgres directly. Ideas?! Thanks again Tory ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
