[PERFORM] pg_statistic doesnt contain details for specific table
Hi, While monitioring we noticed that there are no details in the pg_statistics for a particular table. Can you let us know what might be the reason? Also what steps can be taken care for adding the statistics? Note: The queries which are running on this table are taken longer time then al the other queries. Thanks, Nimesh.
Re: [PERFORM] pg_statistic doesnt contain details for specific table
Nimesh Satam wrote: While monitioring we noticed that there are no details in the pg_statistics for a particular table. Can you let us know what might be the reason? Also what steps can be taken care for adding the statistics? Have you ANALYZEd the table? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] pg_statistic doesnt contain details for specific table
Heikki, Thank you for replying. We have already used analyze command on the table. We have also ran the vacuum analyze command. But they are not helping. Thanks, Nimesh. On 6/11/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Nimesh Satam wrote: While monitioring we noticed that there are no details in the pg_statistics for a particular table. Can you let us know what might be the reason? Also what steps can be taken care for adding the statistics? Have you ANALYZEd the table? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: [PERFORM] How much ram is too much
Actually this one is an opteron, so it looks like it's all good. Dave On 8-Jun-07, at 3:41 PM, Guy Rouillier wrote: Dave Cramer wrote: It's an IBM x3850 using linux redhat 4.0 I had to look that up, web site says it is a 4-processor, dual-core (so 8 cores) Intel Xeon system. It also says Up to 64GB DDR II ECC memory, so are you sure you can even get 128 GB RAM? If you could, I'd expect diminishing returns from the Xeon northbridge memory access. If you are willing to spend that kind of money on memory, you'd be better off with Opteron or Sparc. -- Guy Rouillier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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: [PERFORM] pg_statistic doesnt contain details for specific table
On Mon, Jun 11, 2007 at 02:28:32PM +0530, Nimesh Satam wrote: We have already used analyze command on the table. We have also ran the vacuum analyze command. But they are not helping. Is there any data in the table? What does ANALYZE VERBOSE or VACUUM ANALYZE VERBOSE show for this table? Is there any chance that somebody set all of the columns' statistics targets to zero? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_statistic doesnt contain details for specific table
Michael, Following is the output of Vacuum analze on the same table: *psql =# VACUUM ANALYZE verbose cam_attr; INFO: vacuuming public.cam_attr INFO: index cam_attr_pk now contains 11829 row versions in 63 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: cam_attr: found 0 removable, 11829 nonremovable row versions in 103 pages DETAIL: 0 dead row versions cannot be removed yet. There were 236 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing public.cam_attr INFO: cam_attr: scanned 103 of 103 pages, containing 11829 live rows and 0 dead rows; 6000 rows in sample, 11829 estimated total rows VACUUM * Also how do we check if the statistics are set to Zero for the table? Regards, Nimesh. On 6/11/07, Michael Fuhr [EMAIL PROTECTED] wrote: On Mon, Jun 11, 2007 at 02:28:32PM +0530, Nimesh Satam wrote: We have already used analyze command on the table. We have also ran the vacuum analyze command. But they are not helping. Is there any data in the table? What does ANALYZE VERBOSE or VACUUM ANALYZE VERBOSE show for this table? Is there any chance that somebody set all of the columns' statistics targets to zero? -- Michael Fuhr
Re: [PERFORM] pg_statistic doesnt contain details for specific table
On Mon, Jun 11, 2007 at 07:22:24PM +0530, Nimesh Satam wrote: INFO: analyzing public.cam_attr INFO: cam_attr: scanned 103 of 103 pages, containing 11829 live rows and 0 dead rows; 6000 rows in sample, 11829 estimated total rows Looks reasonable. Also how do we check if the statistics are set to Zero for the table? SELECT attname, attstattarget FROM pg_attribute WHERE attrelid = 'public.cam_attr'::regclass AND attnum 0 AND NOT attisdropped; If nobody has changed the statistics targets then they're all probably -1. Negative attstattarget values mean to use the system default, which you can see with: SHOW default_statistics_target; How exactly are you determining that no statistics are showing up for this table? Are you running a query like the following? SELECT * FROM pg_stats WHERE schemaname = 'public' AND tablename = 'cam_attr'; -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] dbt2 NOTPM numbers
On Jun 4, 2007, at 1:56 PM, Markus Schiltknecht wrote: Simplistic throughput testing with dd: dd of=test if=/dev/zero bs=10K count=80 80+0 records in 80+0 records out 819200 bytes (8.2 GB) copied, 37.3552 seconds, 219 MB/s pamonth:/opt/dbt2/bb# dd if=test of=/dev/zero bs=10K count=80 80+0 records in 80+0 records out 819200 bytes (8.2 GB) copied, 27.6856 seconds, 296 MB/s I don't think that kind of testing is useful for good raid controllers on RAID5/6, because the controller will just be streaming the data out; it'll compute the parity blocks on the fly and just stream data to the drives as fast as possible. But that's not how writes in the database work (except for WAL); you're writing stuff all over the place, none of which is streamed. So in the best case (the entire stripe being updated is in the controller's cache), at a minimum it's going to have to write data + parity ( * 2 for RAID 6, IIRC) for every write. But any real-sized database is going to be far larger than your raid cache, which means there's a good chance a block being written will no longer have it's stripe in cache. In that case, the controller is going to have to read a bunch of data back off the drive, which is going to clobber performance. Now, add that performance bottleneck on top of your WAL writes and you're in real trouble. BTW, I was thinking in terms of stripe size when I wrote this, but I don't know if good controllers actually need to deal with things at a stripe level, or if they can deal with smaller chunks of a stripe. In either case, the issue is still the number of extra reads going on. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Vacuum takes forever
On May 29, 2007, at 12:03 PM, Joost Kraaijeveld wrote: vacuum_cost_delay = 200 vacuum_cost_page_hit = 6 #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits vacuum_cost_limit = 100 I didn't see anyone else mention this, so... Those settings are *very* aggressive. I'm not sure why you upped the cost of page_hit or dropped the cost_limit, but I can tell you the effect: vacuum will sleep at least every 17 pages... even if those pages were already in shared_buffers and vacuum didn't have to dirty them. I really can't think of any reason you'd want to do that. I do find vacuum_cost_delay to be an extremely useful tool, but typically I'll set it to between 10 and 20 and leave the other parameters alone. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] How much ram is too much
On Jun 8, 2007, at 11:31 AM, Dave Cramer wrote: Is it possible that providing 128G of ram is too much ? Will other systems in the server bottleneck ? Providing to what? PostgreSQL? The OS? My bet is that you'll run into issues with how shared_buffers are managed if you actually try and set them to anything remotely close to 128GB. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] How much ram is too much
On 10-Jun-07, at 11:11 PM, Jim Nasby wrote: On Jun 8, 2007, at 11:31 AM, Dave Cramer wrote: Is it possible that providing 128G of ram is too much ? Will other systems in the server bottleneck ? Providing to what? PostgreSQL? The OS? My bet is that you'll run into issues with how shared_buffers are managed if you actually try and set them to anything remotely close to 128GB. Well, we'd give 25% of it to postgres, and the rest to the OS. What is it specifically you are referring to ? Dave -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] dbt2 NOTPM numbers
Hi, Jim Nasby wrote: I don't think that kind of testing is useful for good raid controllers on RAID5/6, because the controller will just be streaming the data out; it'll compute the parity blocks on the fly and just stream data to the drives as fast as possible. That's why I called it 'simplistic throughput testing'... But that's not how writes in the database work (except for WAL); you're writing stuff all over the place, none of which is streamed. So in the best case (the entire stripe being updated is in the controller's cache), at a minimum it's going to have to write data + parity ( * 2 for RAID 6, IIRC) for every write. But any real-sized database is going to be far larger than your raid cache, which means there's a good chance a block being written will no longer have it's stripe in cache. In that case, the controller is going to have to read a bunch of data back off the drive, which is going to clobber performance. I'm well aware. Our workload (hopefully) consists of a much lower writes/reads ratio than dbt2, so RAID 6 might work anyway. Now, add that performance bottleneck on top of your WAL writes and you're in real trouble. Well, I'm basically surprised of the low NOTPM numbers compared to my desktop system, which also does around 200 NOTPMs, with only two platters in RAID 1 config... How can a server with four Cores and 8 Platters be equaly slow? Anyway, I've now reconfigured the system with RAID 1+0 and got more than twice the NOTPMs: Response Time (s) Transaction %Average :90th %Total Rollbacks % - - --- --- - Delivery 3.84204.733 : 241.998 704 0 0.00 New Order 45.77203.651 : 242.847 8382 75 0.90 Order Status 4.32199.184 : 238.081 7920 0.00 Payment 42.02198.969 : 236.549 7695 0 0.00 Stock Level 4.04198.668 : 236.113 740 0 0.00 - - --- --- - 567.72 new-order transactions per minute (NOTPM) 14.5 minute duration 0 total unknown errors 529 second(s) ramping up I'm still feeling that 550 is pretty low. The response times are beyond good and evil. As vmstat.out tells us, the CPUs are still pretty much idle or waiting most of the time. procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 0 3 494560 2181964 8 1787680 13 15 317 242 1402 4 1 72 23 0 9 494560 1558892 8 229834800 2973 2018 584 1114 2 1 76 21 1 14 494496 424116 8 331600020 5613 9293 935 2943 5 1 29 65 0 15 452840 150148 8 3487160 7383 5662 8709 925 3444 5 2 21 73 0 11 439172 151052 8 3386556 2630 5690 8293 969 4145 5 2 23 70 0 17 438996 149748 8 3308184 576 5036 7174 902 4104 5 2 25 69 1 25 439940 150344 8 32283049 28 4757 7479 922 4269 5 2 26 67 For everybody interested, these settings are different from Pg 8.2 default postgresql.conf: listen_addresses = '*' port = 54321 shared_buffers = 2048MB work_mem = 10MB maintenance_work_mem = 64MB #max_stack_depth = 4MB max_fsm_pages = 409600 eachcheckpoint_segments = 6 checkpoint_timeout = 1h effective_cache_size = 3800MB log_min_duration_statement = 500 For dbt2, I've used 500 warehouses and 90 concurrent connections, default values for everything else. Do I simply have to put more RAM (currently 4GB) in that machine? Or what else can be wrong? Is anybody else seeing low performance with the Areca SATA Controllers? (in my case: Areca Technology Corp. ARC-1260 16-Port PCI-Express to SATA RAID Controller, according to lspci) Then again, maybe I'm just expecting too much... Regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] How much ram is too much
On Mon, Jun 11, 2007 at 11:09:42AM -0400, Dave Cramer wrote: and set them to anything remotely close to 128GB. Well, we'd give 25% of it to postgres, and the rest to the OS. Are you quite sure that PostgreSQL's management of the buffers is efficient with such a large one? In the past, that wasn't the case for relatively small buffers; with the replacement of single-pass LRU, that has certainly changed, but I'd be surprised if anyone tested a buffer as large as 32G. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] dbt2 NOTPM numbers
Markus Schiltknecht wrote: For dbt2, I've used 500 warehouses and 90 concurrent connections, default values for everything else. 500? That's just too much for the hardware. Start from say 70 warehouses and up it from there 10 at a time until you hit the wall. I'm using 30 connections with ~100 warehouses on somewhat similar hardware. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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
[PERFORM] test / live environment, major performance difference
Hi All,
I really hope someone can shed some light on my problem. I'm not sure if
this is a posgres or potgis issue.
Anyway, we have 2 development laptops and one live server, somehow I
managed to get the same query to perform very well om my laptop, but on
both the server and the other laptop it's really performing bad.
All three environments are running the same versions of everything, the
two laptops are identical and the server is a monster compared to the
laptops.
I have narrowed down the problem (I think) and it's the query planner
using different plans and I haven't got a clue why. Can anyone please
shed some light on this?
EXPLAIN ANALYZE
SELECT l.*
FROM layer l, theme t, visiblelayer v, layertype lt, style s
WHERE l.the_geom geomfromtext('POLYGON((-83.0 -90.0, -83.0 90.0, 97.0
90.0, 97.0 -90.0, -83.0 -90.0))')
AND t.name = 'default'
AND v.themeid = t.id
AND v.zoomlevel = 1
AND v.enabled
AND l.layertypeid = v.layertypeid
AND lt.id = l.layertypeid
AND s.id = v.styleid
ORDER BY lt.zorder ASC
--
Sort (cost=181399.77..182144.30 rows=297812 width=370) (actual
time=1384.976..1385.072 rows=180 loops=1)
Sort Key: lt.zorder
- Hash Join (cost=31.51..52528.64 rows=297812 width=370) (actual
time=398.656..1384.574 rows=180 loops=1)
Hash Cond: (l.layertypeid = v.layertypeid)
- Seq Scan on layer l (cost=0.00..43323.41 rows=550720
width=366) (actual time=0.016..1089.049 rows=540490 loops=1)
Filter: (the_geom
'010300010005C054C0008056C000C054C000805640004058400080564000405840008056C000C054C0008056C0'::geometry)
- Hash (cost=31.42..31.42 rows=7 width=12) (actual
time=1.041..1.041 rows=3 loops=1)
- Hash Join (cost=3.90..31.42 rows=7 width=12) (actual
time=0.107..1.036 rows=3 loops=1)
Hash Cond: (v.styleid = s.id)
- Nested Loop (cost=2.74..30.17 rows=7 width=16)
(actual time=0.080..1.002 rows=3 loops=1)
Join Filter: (v.themeid = t.id)
- Seq Scan on theme t (cost=0.00..1.01
rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Filter: (name = 'default'::text)
- Hash Join (cost=2.74..29.07 rows=7
width=20) (actual time=0.071..0.988 rows=3 loops=1)
Hash Cond: (lt.id = v.layertypeid)
- Seq Scan on layertype lt
(cost=0.00..18.71 rows=671 width=8) (actual time=0.007..0.473 rows=671
loops=1)
- Hash (cost=2.65..2.65 rows=7
width=12) (actual time=0.053..0.053 rows=3 loops=1)
- Seq Scan on visiblelayer v
(cost=0.00..2.65 rows=7 width=12) (actual time=0.022..0.047 rows=3 loops=1)
Filter: ((zoomlevel = 1)
AND enabled)
- Hash (cost=1.07..1.07 rows=7 width=4) (actual
time=0.020..0.020 rows=7 loops=1)
- Seq Scan on style s (cost=0.00..1.07
rows=7 width=4) (actual time=0.005..0.012 rows=7 loops=1)
Total runtime: 1385.313 ms
--
Sort (cost=37993.10..37994.11 rows=403 width=266) (actual
time=32.053..32.451 rows=180 loops=1)
Sort Key: lt.zorder
- Nested Loop (cost=0.00..37975.66 rows=403 width=266) (actual
time=0.130..31.254 rows=180 loops=1)
- Nested Loop (cost=0.00..30.28 rows=1 width=12) (actual
time=0.105..0.873 rows=3 loops=1)
- Nested Loop (cost=0.00..23.14 rows=1 width=4)
(actual time=0.086..0.794 rows=3 loops=1)
- Nested Loop (cost=0.00..11.14 rows=2 width=8)
(actual time=0.067..0.718 rows=3 loops=1)
Join Filter: (s.id = v.styleid)
- Seq Scan on style s (cost=0.00..2.02
rows=2 width=4) (actual time=0.018..0.048 rows=7 loops=1)
- Seq Scan on visiblelayer v
(cost=0.00..4.47 rows=7 width=12) (actual time=0.031..0.079 rows=3 loops=7)
Filter: ((zoomlevel = 1) AND enabled)
- Index Scan using theme_id_pkey on theme t
(cost=0.00..5.98 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=3)
Index Cond: (v.themeid = t.id)
Filter: (name = 'default'::text)
- Index Scan using layertype_id_pkey on layertype lt
(cost=0.00..7.12 rows=1 width=8) (actual time=0.010..0.014 rows=1 loops=3)
Index Cond: (lt.id = v.layertypeid)
- Index Scan using fki_layer_layertypeid on layer l
(cost=0.00..36843.10 rows=88183 width=262) (actual time=0.031..9.825
rows=60 loops=3)
Index Cond: (l.layertypeid = v.layertypeid)
Filter: (the_geom
Re: [PERFORM] Best way to delete unreferenced rows?
Tyrrill, Ed wrote: QUERY PLAN --- Merge Left Join (cost=38725295.93..42505394.70 rows=13799645 width=8) (actual time=6503583.342..8220629.311 rows=93524 loops=1) Merge Cond: (outer.record_id = inner.record_id) Filter: (inner.record_id IS NULL) - Index Scan using backupobjects_pkey on backupobjects (cost=0.00..521525.10 rows=13799645 width=8) (actual time=15.955..357813.621 rows=13799645 loops=1) - Sort (cost=38725295.93..39262641.69 rows=214938304 width=8) (actual time=6503265.293..7713657.750 rows=214938308 loops=1) Sort Key: backup_location.record_id - Seq Scan on backup_location (cost=0.00..3311212.04 rows=214938304 width=8) (actual time=11.175..1881179.825 rows=214938308 loops=1) Total runtime: 8229178.269 ms (8 rows) I ran vacuum analyze after the last time any inserts, deletes, or updates were done, and before I ran the query above. I've attached my postgresql.conf. The machine has 4 GB of RAM. I thought maybe someone with more expertise than me might answer this, but since they haven't I'll just make a comment. It looks to me like the sort of 214 million rows is what's killing you. I suppose you could try to increase the sort memory, but that's a lot of memory. It seems to me an index merge of a relation this large would be faster, but that's a topic for the experts. On a theoretical level, the problem is that it's sorting the largest table. Perhaps you could re-cast the query so that it only has to sort the smaller table, something like select a.id from a where a.id not in (select distinct b.id from b) where b is the smaller table. There's still no guarantee that it won't do a sort on a, though. In fact one of the clever things about Postgres is that it can convert a query like the one above into a regular join, unless you do something like select ... offset 0 which blocks the optimizer from doing the rearrangement. But I think the first approach is to try to tune for a better plan using your original query. Craig ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] test / live environment, major performance difference
On 2007-06-11 Christo Du Preez wrote: I really hope someone can shed some light on my problem. I'm not sure if this is a posgres or potgis issue. Anyway, we have 2 development laptops and one live server, somehow I managed to get the same query to perform very well om my laptop, but on both the server and the other laptop it's really performing bad. You write that you have 3 systems, but provided only two EXPLAIN ANALYZE results. I will assume that the latter is from your laptop while the former is from one of the badly performing systems. All three environments are running the same versions of everything, the two laptops are identical and the server is a monster compared to the laptops. Please provide information what exactly those same versions of everything are. What's the PostgreSQL configuration on each system? Do all three systems have the same configuration? Information on the hardware wouldn't hurt either. [...] Sort (cost=181399.77..182144.30 rows=297812 width=370) (actual time=1384.976..1385.072 rows=180 loops=1) [...] Sort (cost=37993.10..37994.11 rows=403 width=266) (actual time=32.053..32.451 rows=180 loops=1) The row estimate of the former plan is way off (297812 estimated - 180 actual). Did you analyze the table recently? Maybe you need to increase the statistics target. Regards Ansgar Wiechers -- The Mac OS X kernel should never panic because, when it does, it seriously inconveniences the user. --http://developer.apple.com/technotes/tn2004/tn2118.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Variable (degrading) performance
Hi all, It seems that I have an issue with the performance of a PostgreSQL server. I'm running write-intensive, TPC-C like tests. The workload consist of 150 to 200 thousand transactions. The performance varies dramatically, between 5 and more than 9 hours (I don't have the exact figure for the longest experiment). Initially the server is relatively fast. It finishes the first batch of 50k transactions in an hour. This is probably due to the fact that the database is RAM-resident during this interval. As soon as the database grows bigger than the RAM the performance, not surprisingly, degrades, because of the slow disks. My problem is that the performance is rather variable, and to me non-deterministic. A 150k test can finish in approx. 3h30mins but conversely it can take more than 5h to complete. Preferably I would like to see *steady-state* performance (where my interpretation of the steady-state is that the average throughput/response time does not change over time). Is the steady-state achievable despite the MVCC and the inherent non-determinism between experiments? What could be the reasons for the variable performance? - misconfiguration of the PG parameters (e.g. autovacuum does not cope with the dead tuples on the MVCC architecture) - file fragmentation - index bloat - ??? The initial size of the database (actually the output of the 'du -h' command) is ~ 400 MB. The size increases dramatically, somewhere between 600MB and 1.1GB I have doubted the client application at some point too. However, other server combinations using different DBMS exhibit steady state performance.As a matter of fact when PG is paired with Firebird, through statement-based replication middleware, the performance of the pair is steady too. The hardware configuration: Client machine - 1.5 GHz CPU Pentium 4 - 1GB Rambus RAM - Seagate st340810a IDE disk (40GB), 5400 rpms Server machine - 1.5 GHz CPU Pentium 4 - 640 MB Rambus RAM - Seagate Barracuda 7200.9 rpms - Seagate st340810a IDE disk (40GB) - the WAL is stored on an ext2 partition The Software configuration: The client application is a multi-threaded Java client running on Win 2000 Pro sp4 The database server version is 8.1.5 running on Fedora Core 6. Please find attached: 1 - the output of vmstat taken after the first 60k transactions were executed 2 - the postgresql.conf file Any help would be appreciated. Best regards, Vladimir P.S. Apologies for possible multiple posts -- Vladimir StankovicT: +44 20 7040 0273 Research Student/Research Assistant F: +44 20 7040 8585 Centre for Software Reliability E: [EMAIL PROTECTED] City University Northampton Square, London EC1V 0HB # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432
Re: [PERFORM] Variable (degrading) performance
Vladimir Stankovic wrote: I'm running write-intensive, TPC-C like tests. The workload consist of 150 to 200 thousand transactions. The performance varies dramatically, between 5 and more than 9 hours (I don't have the exact figure for the longest experiment). Initially the server is relatively fast. It finishes the first batch of 50k transactions in an hour. This is probably due to the fact that the database is RAM-resident during this interval. As soon as the database grows bigger than the RAM the performance, not surprisingly, degrades, because of the slow disks. My problem is that the performance is rather variable, and to me non-deterministic. A 150k test can finish in approx. 3h30mins but conversely it can take more than 5h to complete. Preferably I would like to see *steady-state* performance (where my interpretation of the steady-state is that the average throughput/response time does not change over time). Is the steady-state achievable despite the MVCC and the inherent non-determinism between experiments? What could be the reasons for the variable performance? Steadiness is a relative; you'll never achieve perfectly steady performance where every transaction takes exactly X milliseconds. That said, PostgreSQL is not as steady as many other DBMS's by nature, because of the need to vacuum. Another significant source of unsteadiness is checkpoints, though it's not as bad with fsync=off, like you're running. I'd suggest using the vacuum_cost_delay to throttle vacuums so that they don't disturb other transactions as much. You might also want to set up manual vacuums for the bigger tables, instead of relying on autovacuum, because until the recent changes in CVS head, autovacuum can only vacuum one table at a time, and while it's vacuuming a big table, the smaller heavily-updated tables are neglected. The database server version is 8.1.5 running on Fedora Core 6. How about upgrading to 8.2? You might also want to experiment with CVS HEAD to get the autovacuum improvements, as well as a bunch of other performance improvements. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] How much ram is too much
Hi Andrew On 11-Jun-07, at 11:34 AM, Andrew Sullivan wrote: On Mon, Jun 11, 2007 at 11:09:42AM -0400, Dave Cramer wrote: and set them to anything remotely close to 128GB. Well, we'd give 25% of it to postgres, and the rest to the OS. Are you quite sure that PostgreSQL's management of the buffers is efficient with such a large one? No, I'm not sure of this. In the past, that wasn't the case for relatively small buffers; with the replacement of single-pass LRU, that has certainly changed, but I'd be surprised if anyone tested a buffer as large as 32G. So does anyone have experience above 32G ? Dave A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] test / live environment, major performance difference
On 2007-06-11 Christo Du Preez wrote: I really hope someone can shed some light on my problem. I'm not sure if this is a posgres or potgis issue. Anyway, we have 2 development laptops and one live server, somehow I managed to get the same query to perform very well om my laptop, but on both the server and the other laptop it's really performing bad. One simple possibility that bit me in the past: If you do pg_dump/pg_restore to create a copy of the database, you have to ANALYZE the newly-restored database. I mistakenly assumed that pg_restore would do this, but you have to run ANALYZE explicitely after a restore. Craig ---(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
[PERFORM] Best use of second controller with faster disks?
Configuration OS: FreeBSD 6.1 Stable Postgresql: 8.1.4 RAID card 1 with 8 drives. 7200 RPM SATA RAID10 RAID card 2 with 4 drives. 10K RPM SATA RAID10 Besides having pg_xlog in the 10K RPM drives what else can I do to best use those drives other than putting some data in them? Iostat shows the drives getting used very little, even during constant updates and vacuum. Some of the postgresl.conf settings that may be relevant. wal_buffers = 64 checkpoint_segments = 64 If nothing else I will start to put index files in the 10K RPM RAID. As for the version of postgreql.. we are likely getting a second machine, break off some of the data, change programs to read data from both and at some point when there is little data in the 8.1.4, upgrade the 8.1.4 machine. The new machine will have 8.2.4 We have a lot of historical data that never changes which is the main driving factor behind looking to split the database into current and historical. ---(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
