[PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig DDR PC3200
-- Forwarded message --From: Kjell Tore Fossbakk <[EMAIL PROTECTED]>Date: Jul 26, 2006 8:55 AM Subject: Performance with 2 AMD/Opteron 2.6Ghz and 8gig DDR PC3200To: [EMAIL PROTECTED]Hello!I have upgraded my server to an HP Proliant DL585. It got two Processor motherboards, each holding an AMD/Opteron 2.6Ghz and 4GIG of memory.I got 4 150GIG SCSI disks in a Smart Array 5i 1+0 RAID. I'v been using Postgres for a few years, and the reason for this major hardware upgrade is to boost my performance. I have created a small web application that pulls huge amounts of data from my database.The database consists of basically one table, but it's big. It got 10 columns and a few indexes on the 3-4 most used fields (based on my queries of course). My queries use a few aggregated functions, such as sum and count, which makes the data moving process time-consuming. Now, on my older server (2gig memory and probabably some 2ghz cpu) my queries took quite a lot of time (30sec - several minutes). Now, with a much better hardware platform, I was hoping I could juice the process! As I have understood, there is alot of tuning using both postgres.conf and analyzing queries to make the values of postgres.conf fit my needs, system and hardware. This is where I need some help. I have looked into postgres.conf , and seen the tunings. But I'm still not sure what I should put into those variables (in postgres.conf) with my hardware.Any suggestions would be most appreciated!- Kjell Tore -- "Be nice to people on your way up because you meet them on your way down." -- "Be nice to people on your way up because you meet them on your way down."
Re: [PERFORM] Disk writes
> I could I discover who is sending so many data to the disks? Documentation/laptop-mode.txt in the Linux kernel tree has some instructions how to track down unwanted disk writes. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(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] Savepoint performance
My understanding of EDB's approach is that our prototype just implicitly does a savepoint before each INSERT, UPDATE, or DELETE statement inside of PLpgSQL. We then rollback to that savepoint if a sql error occurs. I don 't believe our prelim approach changes any transaction start/end semantics on the server side and it doesn't change any PLpgSQL syntax either (although it does allow you to optionally code commits &/or rollbacks inside stored procs). Can anybody point me to a thread on the 7.3 disastrous experiment? I personally think that doing commit or rollbacks inside stored procedures is usually bad coding practice AND can be avoided... It's a backward compatibility thing for non-ansi legacy stuff and this is why I was previously guessing that the community wouldn't be interested in this for PLpgSQL. Actually... does anybody know offhand if the ansi standard for stored procs allows for explicit transaction control inside of a stored procedure? --Luss On 7/27/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Denis Lussier" <[EMAIL PROTECTED]> writes: > Would the community be potentially interested in this feature if we created > a BSD Postgres patch of this feature for PLpgSQL (likely for 8.3)?? Based on our rather disastrous experiment in 7.3, I'd say that fooling around with transaction start/end semantics on the server side is unlikely to fly ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Savepoint performance
"Denis Lussier" <[EMAIL PROTECTED]> writes: > Would the community be potentially interested in this feature if we created > a BSD Postgres patch of this feature for PLpgSQL (likely for 8.3)?? Based on our rather disastrous experiment in 7.3, I'd say that fooling around with transaction start/end semantics on the server side is unlikely to fly ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Disk writes
Hi, We've a fedora core 3 box with PostgreSQL 8.0. There is some performance problems with the server and I discovered with vmstat tool that there is some process writing a lot of information in the disk subsystem. I stopped the database and even so vmstat showed the same rates of disk writes. I could I discover who is sending so many data to the disks? Thanks in advance, Reimer
Re: [PERFORM] Savepoint performance
We've actually done some prelim benchmarking of this feature about six months ago and we are actively considering adding it to our "closer to Oracle" version of PLpgSQL. I certainly don't want to suggest that it's a good idea to do this because it's Oracle compatible. :-) I'll get someone to post our performance results on this thread. As Alvaro correctly alludes, it has an overhead impact that is measurable, but, likely acceptable for situations where the feature is desired (as long as it doesn't negatively affect performance in the "normal" case). I believe the impact was something around a 12% average slowdown for the handful of PLpgSQL functions we tested when this feature is turned on. Would the community be potentially interested in this feature if we created a BSD Postgres patch of this feature for PLpgSQL (likely for 8.3)?? --Luss Denis Lussier CTO http://www.enterprisedb.com On 7/27/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Mark Lewis wrote:> So my question is, how expensive is setting a savepoint in PG? If it's> not too expensive, I'm wondering if it would be feasible to add a config > parameter to psql or other client interfaces (thinking specifically of> jdbc here) to do it automatically. Doing so would make it a little> easier to work with PG in a multi-db environment. It is moderately expensive. It's cheaper than starting/committing atransaction, but certainly much more expensive than not setting asavepoint.In psql you can do what you want using \set ON_ERROR_ROLLBACK on. This is clearly a client-only issue, so the server does not provide anyspecial support for it (just like autocommit mode).--Alvaro Herrera http://www.CommandPrompt.com/PostgreSQL Replication, Consulting, Custom Development, 24x7 support---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Savepoint performance
On 7/27/06, Mark Lewis <[EMAIL PROTECTED]> wrote: All, I support a system that runs on several databases including PostgreSQL. I've noticed that the other DB's always put an implicit savepoint before each statement executed, and roll back to that savepoint if the statement fails for some reason. PG does not, so unless you manually specify a savepoint you lose all previous work in the transaction. you're talking about transactions not savepoints (savepoints is something more like nested transactions), i guess... postgres execute every single statement inside an implicit transaction unless you put BEGIN/COMMIT between a block of statements... in that case if an error occurs the entire block of statements must ROLLBACK... if other db's doesn't do that, is a bug in their implementation of the SQL standard -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Query plan issue when upgrading to postgres 8.14 (from
Ioana Danes <[EMAIL PROTECTED]> writes: > Does anyone now what the problem is with the following select when > upgrading to postgresql 8.1.4 the query plan does not use the indexes as in > postgresql 8.0.3. The planner doesn't have enough information about the correlation between testtype and testid to guess that the index-driven max() optimization doesn't work well in this case. But I see you've already found the solution ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] performance issue with a specific query
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Joshua D. Drake wrote: >> Enterprises are not going to compile. They are going to accept the >> latest support by vendor release. >> >> Redhat has a tendency to be incredibly stupid about this particular >> area of their packaging. > Stupid how? Red Hat feels (apparently accurately, judging by their subscription revenue ;-)) that what RHEL customers want is a platform that's stable over multi-year application lifespans. So major incompatible changes in the system software are not looked on with favor. That's why RHEL4 is still shipping PG 7.4.*. You can call it a stupid policy if you like, but it's hard to argue with success. However, there will be an RH-supported release of PG 8.1.* as an optional add-on for RHEL4. Real Soon Now, I hope --- the release date has been pushed back a couple times already. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Savepoint performance
Mark Lewis wrote: > So my question is, how expensive is setting a savepoint in PG? If it's > not too expensive, I'm wondering if it would be feasible to add a config > parameter to psql or other client interfaces (thinking specifically of > jdbc here) to do it automatically. Doing so would make it a little > easier to work with PG in a multi-db environment. It is moderately expensive. It's cheaper than starting/committing a transaction, but certainly much more expensive than not setting a savepoint. In psql you can do what you want using \set ON_ERROR_ROLLBACK on. This is clearly a client-only issue, so the server does not provide any special support for it (just like autocommit mode). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Query plan issue when upgrading to postgres 8.14 (from
Hi everyone,I posted this question some time ago and I did not get any answer so here I am again. Does anyone now what the problem is with the following select when upgrading to postgresql 8.1.4 the query plan does not use the indexes as in postgresql 8.0.3. Here are the results of my query for postgresql 8.0.3 and 8.1.4. For postgresql 8.1.4 there are 2 results, one for test table having the same indexes as in 8.0.3 and the second one for a new index on test table by (testtype,testid) that will speed up my query. This last index will fix my problem for this particular query. In the Test table there are 19,494,826 records and 11,090 records have testtype = 1455. The data on both servers is identical. And on both servers I run vacuum analyze prior executing this queries.As it can be seen the result in postgresql 8.1.4 is very slow and I am wondering why is that. Bug, missing configuration, ...1. Result on Postgresql 8.0.3: - # explain analyze select max(TESTID) from TEST where TESTTYPE = 1455; Aggregate (cost=391.56..391.56 rows=1 width=8) (actual time=94.707..94.711 rows=1 loops=1) -> Index Scan using ix_test_testtype on test (cost=0.00..355.18 rows=14551 width=8) (actual time=0.036..51.089 rows=11090 loops=1) Index Cond: (testtype = 1455) Total runtime: 94.778 ms (4 rows)# select max(TESTID) from TEST where TESTTYPE = 1455; max -- 18527829 (1 row)Time: 13.447 ms 2. Result on Postgresql 8.1.4 (with the same indexes as in 8.0.3): -- Result (cost=32.78..32.79 rows=1 width=0) (actual time=1865.406..1865.408 rows=1 loops=1) InitPlan -> Limit (cost=0.00..32.78 rows=1 width=8) (actual time=1865.378..1865.381 rows=1 loops=1) -> Index Scan Backward using pk_testid on test (cost=0.00..464069.25 rows=14155 width=8) (actual time=1865.371..1865.371 rows=1 loops=1) Filter: ((testid IS NOT NULL) AND (testtype = 1455)) Total runtime: 1865.522 ms (6 rows)# select max(TESTID) from TEST where TESTTYPE = 1455; max -- 18527829Time: 1858.076 ms 3. Result on Postgresql 8.1.4 (after creating an index by testtype, testid ): - # explain analyze select max(TESTID) from TEST where TESTTYPE = 1455; Result (cost=1.71..1.72 rows=1 width=0) (actual time=0.069..0.070 rows=1 loops=1) InitPlan -> Limit (cost=0.00..1.71 rows=1 width=8) (actual time=0.055..0.056 rows=1 loops=1) -> Index Scan Backward using ix_test2 on test (cost=0.00..24248.92 rows=14155 width=8) (actual time=0.050..0.050 rows=1 loops=1) Index Cond: (testtype = 1455) Filter: (testid IS NOT NULL) Total runtime: 0.159 ms# select max(TESTID) from TEST where TESTTYPE = 1455; max -- 18527829Time: 1.029 msThank you in advance, Ioana Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail
[PERFORM] Savepoint performance
All, I support a system that runs on several databases including PostgreSQL. I've noticed that the other DB's always put an implicit savepoint before each statement executed, and roll back to that savepoint if the statement fails for some reason. PG does not, so unless you manually specify a savepoint you lose all previous work in the transaction. So my question is, how expensive is setting a savepoint in PG? If it's not too expensive, I'm wondering if it would be feasible to add a config parameter to psql or other client interfaces (thinking specifically of jdbc here) to do it automatically. Doing so would make it a little easier to work with PG in a multi-db environment. My main reason for wanting this is so that I can more easily import, say, 50 new 'objects' (related rows stored across several tables) in a transaction instead of only one at a time without fear that an error in one object would invalidate the whole batch. I could do this now by manually setting savepoints, but if it's not a big deal performance-wise to modify the JDBC driver to start an anonymous savepoint with each statement, then I'd prefer that approach as it seems that it would make life easier for other folks too. Thanks in advance for any feedback :) -- Mark Lewis ---(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] performance issue with a specific query
Joshua D. Drake wrote: > >> > > > >try turning off genetic query optimization. regarding the rhel4 > >issue...does rhel not come with a c compiler? :) > > Enterprises are not going to compile. They are going to accept the > latest support by vendor release. > > Redhat has a tendency to be incredibly stupid about this particular > area of their packaging. Stupid how? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] performance issue with a specific query
try turning off genetic query optimization. regarding the rhel4 issue...does rhel not come with a c compiler? :) Enterprises are not going to compile. They are going to accept the latest support by vendor release. Redhat has a tendency to be incredibly stupid about this particular area of their packaging. Joshua D. Drake merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] performance issue with a specific query
On 7/27/06, Eliott <[EMAIL PROTECTED]> wrote: Hi! I hope I'm sending my question to the right list, please don't flame if it's the wrong one. I have noticed that while a query runs in about 1.5seconds on a 8.xx version postgresql server on our 7.4.13 it takes around 15-20 minutes. Since we are using RHEL4 on our server we are stuck with 7.4.13. The enormous time difference between the different builds drives me crazy. Can you please help me identifying the bottleneck or suggest anything to improve the dismal performance. The query is the following: try turning off genetic query optimization. regarding the rhel4 issue...does rhel not come with a c compiler? :) merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] performance issue with a specific query
On Thu, 2006-07-27 at 09:23, Eliott wrote:
> Hi!
>
> I hope I'm sending my question to the right list, please don't flame
> if it's the wrong one.
>
> I have noticed that while a query runs in about 1.5seconds on a 8.xx
> version postgresql server on our 7.4.13 it takes around 15-20 minutes.
> Since we are using RHEL4 on our server we are stuck with 7.4.13. The
> enormous time difference between the different builds drives me crazy.
> Can you please help me identifying the bottleneck or suggest anything
> to improve the dismal performance.
You are absolutely on the right list. A couple of points.
1: Which 8.xx? 8.0.x or 8.1.x? 8.1.x is literally light years ahead
of 7.4 in terms of performance. 8.0 is somewhere between them. The
performance difference you're seeing is pretty common.
2: Looking at your query, there are places where you're joining on
things like date_trunc(...). In 7.4 the database will not, and cannot
use a normal index on the date field for those kinds of things. It can,
however, use a funtional index on some of them. Try creating an index
on date_trunc('day',yourfieldhere) and see if that helps.
3: You are NOT Stuck on 7.4.13. I have a RHEL server that will be
running 8.1.4 or so pretty soon as a dataware house. It may get updated
to RHEL4, may not. You can either compile from the .tar.[gz|bz2] files
or download the PGDG rpms for your distro.
4: You are fighting an uphill battle. There were a LOT of improvements
made all over in the march from 7.4 to 8.1. Not all of them were simple
planner tweaks and shortcuts, but honest to goodness changes to the way
things happen. No amount of tuning can make 7.4 run as fast as 8.1.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[PERFORM] performance issue with a specific query
Hi!I hope I'm sending my question to the right list, please don't flame if it's the wrong one.I have noticed that while a query runs in about 1.5seconds on a 8.xx version postgresql server on our 7.4.13 it takes around 15-20 minutes. Since we are using RHEL4 on our server we are stuck with
7.4.13. The enormous time difference between the different builds drives me crazy. Can you please help me identifying the bottleneck or suggest anything to improve the dismal performance.The query is the following:
Select car_license_plate.license_plate, substr(date_trunc('day', car_km_fuel.transaction_time), 1, 10), substr(date_trunc('second', car_km_fuel.transaction_time), 12, 8), vehicle_make.make, vehicle_type.model,
engine_size, vehicle_fuel_type.fuel_type, v_org_person_displayname.displayname_lastfirst, car_km_fuel.ammount, car_km_fuel.unit_price, car_km_fuel.total_ammount, currency.currency AS,
car_km_fuel.km AS, vehicle_specific.fuel_capacity, CASE WHEN (car_km_fuel.ammount > vehicle_specific.fuel_capacity) THEN CAST(ROUND(CAST(car_km_fuel.ammount - vehicle_specific.fuel_capacity AS NUMERIC), 2) AS varchar) ELSE '---' END AS "over",
car_km_fuel.notes,CASE WHEN (prev_car_km_fuel.km IS NOT NULL AND car_km_fuel.km IS NOT NULL AND (car_km_fuel.km - prev_car_km_fuel.km <> 0)) THEN CAST(Round(CAST(((car_km_fuel.ammount / (car_km_fuel.km - prev_car_km_fuel.km)) * 100) AS Numeric), 2) AS VARCHAR)
WHEN (prev_car_km_fuel.km IS NULL) THEN '' WHEN (car_km_fuel.km IS NULL) THEN 'error' END AS "average", vehicle_specific.consumption_town, org_person.email_addressFROM
car_km_fuelLEFT JOIN car ON car.id = car_km_fuel.car_idLEFT JOIN car_license_plate ON car_license_plate.car_id = car.id AND (car_license_plate.license_plate_end_date < date_trunc('day', car_km_fuel.transaction_time) OR car_license_plate.license_plate_end_date IS NULL)
LEFT JOIN vehicle_specific ON vehicle_specific.id = car.vehicle_specific_idLEFT JOIN vehicle_variant ON vehicle_variant.id = vehicle_specific.vehicle_variant_idLEFT JOIN vehicle_type ON vehicle_type.id = vehicle_variant.vehicle_type_id
LEFT JOIN vehicle_make ON vehicle_make.id = vehicle_type.vehicle_make_idLEFT JOIN vehicle_fuel_type ON vehicle_fuel_type.id = vehicle_specific.fuel_type_idLEFT JOIN car_driver ON car_driver.car_id =
car.id AND car_driver.allocation_date <= date_trunc('day', car_km_fuel.transaction_time) AND (car_driver.end_date >= date_trunc('day', car_km_fuel.transaction_time) OR car_driver.end_date IS NULL)
LEFT JOIN v_org_person_displayname ON v_org_person_displayname.id = car_driver.car_driver_idLEFT JOIN org_person ON org_person.id = v_org_person_displayname.idLEFT JOIN currency ON
currency.id = car_km_fuel.currency_idLEFT JOIN car_km_fuel AS prev_car_km_fuel ON prev_car_km_fuel.transaction_time = (SELECT MAX(transaction_time) FROM car_km_fuel as car_km_fuel2 WHERE car_km_fuel2.car_id =
car.id AND car_km_fuel2.transaction_time < car_km_fuel.transaction_time)LEFT JOIN org_company ON org_company.id = org_person.company_idWHERE (lower(org_company.name) LIKE lower(:param3) || '%') AND
(car_km_fuel.transaction_time >= :param1 OR :param1 IS NULL) AND (car_km_fuel.transaction_time <= :param2 OR :param2 IS NULL) ORDER BY 1, 2, 3; The output of explain if the following under
7.4.13:QUERY PLAN
-
Sort (cost=66.66..66.66 rows=1 width=917)Sort Key: car_license_plate.license_plate, substr((date_trunc('day'::text, car_km_fuel.transaction_time))::text, 1, 10), substr((date_trunc('second'::text, car_km_fuel.transaction_time))::text, 12, 8)
-> Nested Loop (cost=44.93..66.65 rows=1 width=917) -> Nested Loop Left Join (cost=44.93..62.23 rows=1 width=921)Join Filter: ("inner".transaction_time = (subplan))
-> Nested Loop Left Join (cost=44.93..62.21 rows=1 width=917) Join Filter: ("inner".id = "outer".currency_id) -> Nested Loop (cost=
44.93..60.92 rows=1 width=828) -> Hash Join (cost=44.93..58.32 rows=1 width=805) Hash Cond: ("outer".id = "inner".car_driver_id)
-> Subquery Scan v_org_person_displayname (cost=16.42..28.82 rows=196 width=520) -> Merge Right Join (cost=16.42..26.86 rows=196
