Re: [PERFORM] Sunfire X4500 recommendations
On Friday 23 March 2007 03:20, Matt Smiley wrote: > My company is purchasing a Sunfire x4500 to run our most I/O-bound > databases, and I'd like to get some advice on configuration and tuning. > We're currently looking at: - Solaris 10 + zfs + RAID Z > - CentOS 4 + xfs + RAID 10 > - CentOS 4 + ext3 + RAID 10 > but we're open to other suggestions. > Matt, for Solaris + ZFS you may find answers to all your questions here: http://blogs.sun.com/roch/category/ZFS http://blogs.sun.com/realneel/entry/zfs_and_databases Think to measure log (WAL) activity and use separated pool for logs if needed. Also, RAID-Z is more security-oriented rather performance, RAID-10 should be a better choice... Rgds, -Dimitri ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Performance of count(*)
Michael Stone schrieb: On Thu, Mar 22, 2007 at 06:27:32PM +0100, Tino Wildenhain wrote: Craig A. James schrieb: You guys can correct me if I'm wrong, but the key feature that's missing from Postgres's flexible indexing is the ability to maintain state across queries. Something like this: select a, b, my_index_state() from foo where ... offset 100 limit 10 using my_index(prev_my_index_state); Yes, you are wrong :-) The technique is called "CURSOR" if you maintain persistent connection per session (e.g. stand allone application or clever pooling webapplication) Did you read the email before correcting it? From the part you trimmed out: The problem is that relational databases were invented before the web and its stateless applications. In the "good old days", you could connect to a database and work for hours, and in that environment cursors and such work well -- the RDBMS maintains the internal state of the indexing system. But in a web environment, state information is very difficult to maintain. There are all sorts of systems that try (Enterprise Java Beans, for example), but they're very complex. Yes, but actually this is not true. They are not so complex in this regard. All you have to do is to look in the pg_cursor view if your cursor is there and if not, create it in your session. All you need to maintain is the cursor name which maps to your session + the special query you run. This should be easy in any web application. It sounds like they wrote their own middleware to handle the problem, which is basically what you suggested (a "clever pooling web application") after saying "wrong". I read about "building index data outside postgres" which still is the wrong approach imho. This discussion is a bit theoretical until we see the actual problem and the proposed solution here. Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] linux - server configuration for small database
Hi I'm going to install a new server and I'm looking for some advice about how I can help my database performing at its best. I'll be using a redhat ES 4 on a dual core opteron with 2 SCSI 10.000rpm disks in RAID1. The first question I have is, since I'd strongly prefer to use postgresql 8.1 or 8.2 instead of the 7.4 that comes with redhat, if someone else is using with success third party rpms with success in business critical applications. Database size: under 1 G Load: reaches 100 r/w queries per second peak time but the average is lower Filesystem: I'll probably have ext3 because of the redhat support. RAM: I will have enough to let postgresql use a quantity about the size of the db itself. I'll have just one pair of disks so Linux will live on the same disk array of the database, but obviously I can make any choice I like about the partitioning and my idea was to put postgresql data on a dedicated partition. As processes I'll have some other webby things running on the server but the I/O impact at disk level of these should be not that bad. Now aside of the filesystem/partitioning choice, I have experience with LVM, but I've never tried to take snapshots of postgresql databases and I'd be interested in knowing how well this works/perform and if there's any downside. I'd like even to read which procedures are common to restore a db from the LVM snapshot. As an alternative i was thinking about using the wal incremental backup strategy. In any case I'll take a pg_dump daily. I would appreciate even just interesting web links (I've already googled a bit so I'm quite aware of the most common stuff) Thanks Paolo ---(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 of count(*)
On Fri, Mar 23, 2007 at 01:01:02PM +0100, Tino Wildenhain wrote: This discussion is a bit theoretical until we see the actual problem and the proposed solution here. It's good to see you back off a bit from your previous stance of assuming that someone doesn't know what they're doing and that their solution is absolutely wrong without actually knowing anything about what they are trying to do. Mike Stone ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Sunfire X4500 recommendations
Thanks Dimitri! That was very educational material! I'm going to think out loud here, so please correct me if you see any errors. The section on tuning for OLTP transactions was interesting, although my OLAP workload will be predominantly bulk I/O over large datasets of mostly-sequential blocks. The NFS+ZFS section talked about the zil_disable control for making zfs ignore commits/fsyncs. Given that Postgres' executor does single-threaded synchronous I/O like the tar example, it seems like it might benefit significantly from setting zil_disable=1, at least in the case of frequently flushed/committed writes. However, zil_disable=1 sounds unsafe for the datafiles' filesystem, and would probably only be acceptible for the xlogs if they're stored on a separate filesystem and you're willing to loose recently committed transactions. This sounds pretty similar to just setting fsync=off in postgresql.conf, which is easier to change later, so I'll skip the zil_disable control. The RAID-Z section was a little surprising. It made RAID-Z sound just like RAID 50, in that you can customize the trade-off between iops versus usable diskspace and fault-tolerance by adjusting the number/size of parity-protected disk groups. The only difference I noticed was that RAID-Z will apparently set the stripe size across vdevs (RAID-5s) to be as close as possible to the filesystem's block size, to maximize the number of disks involved in concurrently fetching each block. Does that sound about right? So now I'm wondering what RAID-Z offers that RAID-50 doesn't. I came up with 2 things: an alleged affinity for full-stripe writes and (under RAID-Z2) the added fault-tolerance of RAID-6's 2nd parity bit (allowing 2 disks to fail per zpool). It wasn't mentioned in this blog, but I've heard that under certain circumstances, RAID-Z will magically decide to mirror a block instead of calculating parity on it. I'm not sure how this would happen, and I don't know the circumstances that would trigger this behavior, but I think the goal (if it really happens) is to avoid the performance penalty of having to read the rest of the stripe required to calculate parity. As far as I know, this is only an issue affecting small writes (e.g. single-row updates in an OLTP workload), but not large writes (compared to the RAID's stripe size). Anyway, when I saw the filesystem's intent log mentioned, I thought maybe the small writes are converted to full-stripe writes by deferring their commit until a full stripe's worth of data had been accumulated. Does that sound plausible? Are there any other noteworthy perks to RAID-Z, rather than RAID-50? If not, I'm inclined to go with your suggestion, Dimitri, and use zfs like RAID-10 to stripe a zpool over a bunch of RAID-1 vdevs. Even though many of our queries do mostly sequential I/O, getting higher seeks/second is more important to us than the sacrificed diskspace. For the record, those blogs also included a link to a very helpful ZFS Best Practices Guide: http://www.solarisinternals.com/wiki/index.php/ZFS_Best_Practices_Guide To sum up, so far the short list of tuning suggestions for ZFS includes: - Use a separate zpool and filesystem for xlogs if your apps write often. - Consider setting zil_disable=1 on the xlogs' dedicated filesystem. ZIL is the intent log, and it sounds like disabling it may be like disabling journaling. Previous message threads in the Postgres archives debate whether this is safe for the xlogs, but it didn't seem like a conclusive answer was reached. - Make filesystem block size (zfs record size) match the Postgres block size. - Manually adjust vdev_cache. I think this sets the read-ahead size. It defaults to 64 KB. For OLTP workload, reduce it; for DW/OLAP maybe increase it. - Test various settings for vq_max_pending (until zfs can auto-tune it). See http://blogs.sun.com/erickustarz/entry/vq_max_pending - A zpool of mirrored disks should support more seeks/second than RAID-Z, just like RAID 10 vs. RAID 50. However, no single Postgres backend will see better than a single disk's seek rate, because the executor currently dispatches only 1 logical I/O request at a time. >>> Dimitri <[EMAIL PROTECTED]> 03/23/07 2:28 AM >>> On Friday 23 March 2007 03:20, Matt Smiley wrote: > My company is purchasing a Sunfire x4500 to run our most I/O-bound > databases, and I'd like to get some advice on configuration and tuning. > We're currently looking at: - Solaris 10 + zfs + RAID Z > - CentOS 4 + xfs + RAID 10 > - CentOS 4 + ext3 + RAID 10 > but we're open to other suggestions. > Matt, for Solaris + ZFS you may find answers to all your questions here: http://blogs.sun.com/roch/category/ZFS http://blogs.sun.com/realneel/entry/zfs_and_databases Think to measure log (WAL) activity and use separated pool for logs if needed. Also, RAID-Z is more security-oriented rather performance, RAID-10 should be a better choice...
Re: [PERFORM] linux - server configuration for small database
"Paolo Negri" <[EMAIL PROTECTED]> writes: > The first question I have is, since I'd strongly prefer to use > postgresql 8.1 or 8.2 instead of the 7.4 that comes with redhat, if > someone else is using with success third party rpms with success in > business critical applications. Red Hat does support postgres 8.1 on RHEL4: http://www.redhat.com/appstack/ regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Parallel Vacuum
On Thursday 22 March 2007 19:46, Michael Stone wrote: > On Thu, Mar 22, 2007 at 07:24:38PM +0100, Dimitri wrote: > >you're right until you're using a single disk :) > >Now, imagine you have more disks > > I do have more disks. I maximize the I/O performance by dedicating > different sets of disks to different tables. YMMV. I do suggest watching > your I/O rates and wallclock time if you try this to see if your > aggregate is actually substantially faster than the single case. (I > assume that you haven't yet gotten far enough to actually do performance > testing.) You may also want to look into tuning your sequential I/O > performance. > > Mike Stone Mike, specially for you :) Parallel Vacuum Test == - Database 'db_OBJ' PgSQL 8.2.3 tables: object1, object2, ... object8 (all the same) volume: 10.000.000 rows in each table, 22GB in total - Script Mono Vacuum $ cat vac_mono.sh /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object1 /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object2 /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object3 /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object4 /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object5 /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object6 /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object7 /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object8 $ - Script Parallel Vacuum $ cat vac_pll.sh /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object1 & /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object2 & /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object3 & /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object4 & /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object5 & /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object6 & /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object7 & /usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object8 & wait $ Test 1: Cold Clean database (already previously vacuumed) = Scenario: - stop database - flush FS cache (umount/mount) - start database - execute vacuum script $ time sh vac_mono.sh real4m24.23s user0m0.00s sys 0m0.01s $ time sh vac_pll.sh real1m9.36s user0m0.00s sys 0m0.01s Test 2: Hot Dirty database (modified and not vacuumed) == Scenario: - stop database - flush FS cache (umount/mount) - start database - execute 200.000 updates against each from 8 object' tables - execute vacuum script $ time sh vac_mono.sh real9m36.90s user0m0.00s sys 0m0.01s $ time sh vac_pll.sh real2m10.41s user0m0.00s sys 0m0.02s Speed-up x4 is obtained just because single vacuum process reaching max 80MB/sec in throughput, while with 8 parallel vacuum processes I'm jumping to 360MB/sec... And speakink about Sequential I/O: while you're doing read - file system may again prefetch incoming data in way once you reclaim next read - your data will be already in FS cache. However, file system cannot 'pre-write' data for you - so having more concurrent writers helps a lot! (Of course in case you have a storage configured to keep concurrent I/O :)) Well, why all this staff?... Let's imagine once you need more performance, and you buy 10x times more performant storage box, will you still able to kill it with a single-process I/O activity? No... :) To scale well you need to be able to split your work in several task executed in parallel. And personally, I'm very happy we can do it with vacuum now - the one of the most critical part of PostgreSQL... Best regards! -Dimitri ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] linux - server configuration for small database
Tom Lane wrote: > "Paolo Negri" <[EMAIL PROTECTED]> writes: >> The first question I have is, since I'd strongly prefer to use >> postgresql 8.1 or 8.2 instead of the 7.4 that comes with redhat, if >> someone else is using with success third party rpms with success in >> business critical applications. > > Red Hat does support postgres 8.1 on RHEL4: > http://www.redhat.com/appstack/ And so does the community: http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/v8.1.8/linux/rpms/redhat/ http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/v8.2.3/linux/rpms/redhat/ Sincerely, Joshua D. Drake -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Vacuum full is slow
On Mon, 2007-03-19 at 06:02, Ruben Rubio wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, > > Vacuum full is very slow for me . I dont know how to speed it up. It > takes between 60 and 90 minutes. > > I have set up autovacuum but I also run vacuum full once per week. Note two things. 1: you need to update your pgsql version. 8.1.3 is a bit old. 2: You shouldn't normally need to run vacuum full. Vacuum full is there to get you out of problems created when regular vacuum falls behind. It contributes to index bloat as well. If routine vacuuming isn't working, regular vacuum full is not the answer (well, 99% of the time it's not). Fixing routing vacuuming is the answer. If you don't have an actual problem with routine vacuuming, you would be better off writing a monitoring script to keep track of bloat in tables and send you an email than running vacuum full all the time. ---(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 of count(*)
Michael Stone schrieb: On Fri, Mar 23, 2007 at 01:01:02PM +0100, Tino Wildenhain wrote: This discussion is a bit theoretical until we see the actual problem and the proposed solution here. It's good to see you back off a bit from your previous stance of assuming that someone doesn't know what they're doing and that their solution is absolutely wrong without actually knowing anything about what they are trying to do. Well I'm sure its very likely wrong :-) At least the core part of it with the statement of "keeping index data outside postgres". What I meant with my comment about the theoreticalness: we cannot make educated suggestions about alternative solutions to the problem until we know the problem and maybe the current solution in detail. Regards Tino ---(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] Parallel Vacuum
On Fri, Mar 23, 2007 at 04:37:32PM +0100, Dimitri wrote: Speed-up x4 is obtained just because single vacuum process reaching max 80MB/sec in throughput I'd look at trying to improve that, it seems very low. Mike Stone ---(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] EXISTS optimization
I'm posting this to performance in case our workaround may be of benefit to
someone with a similar issue. I'm posting to hackers because I hope we can
improve our planner in this area so that a workaround is not necessary. (It
might make sense to reply to one group or the other, depending on reply
content.)
We are converting from a commercial database (which shall remain unnamed here,
due to license restrictions on publishing benchmarks). Most queries run faster
on PostgreSQL; a small number choose very poor plans and run much longer. This
particular query runs on the commercial product in 6.1s first time, 1.4s
cached. In PostgreSQL it runs in about 144s both first time and cached. I was
able to use an easy but fairly ugly rewrite (getting duplicate rows and
eliminating them with DISTINCT) which runs on the commercial product in
9.2s/3.0s and in PostgreSQL in 2.0s/0.7s.
Here are the tables:
Table "public.TranHeader"
Column | Type | Modifiers
---+--+---
tranNo| "TranNoT"| not null
countyNo | "CountyNoT" | not null
acctPd| "DateT" | not null
date | "DateT" | not null
isComplete| boolean | not null
tranId| "TranIdT"| not null
tranType | "TranTypeT" | not null
userId| "UserIdT"| not null
workstationId | "WorkstationIdT" | not null
time | "TimeT" |
Indexes:
"TranHeader_pkey" PRIMARY KEY, btree ("tranNo", "countyNo")
"TranHeader_TranAcctPeriod" UNIQUE, btree ("acctPd", "tranNo", "countyNo")
"TranHeader_TranDate" UNIQUE, btree (date, "tranNo", "countyNo")
Table "public.TranDetail"
Column |Type| Modifiers
-++---
tranNo | "TranNoT" | not null
tranDetailSeqNo | "TranDetailSeqNoT" | not null
countyNo| "CountyNoT"| not null
acctCode| "AcctCodeT"| not null
amt | "MoneyT" | not null
assessNo| "TranIdT" |
caseNo | "CaseNoT" |
citnNo | "CitnNoT" |
citnViolDate| "DateT"|
issAgencyNo | "IssAgencyNoT" |
partyNo | "PartyNoT" |
payableNo | "PayableNoT" |
rcvblNo | "RcvblNoT" |
Indexes:
"TranDetail_pkey" PRIMARY KEY, btree ("tranNo", "tranDetailSeqNo",
"countyNo")
"TranDetail_TranDetCaseNo" UNIQUE, btree ("caseNo", "tranNo",
"tranDetailSeqNo", "countyNo")
"TranDetail_TranDetPay" UNIQUE, btree ("payableNo", "tranNo",
"tranDetailSeqNo", "countyNo")
"TranDetail_TranDetRcvbl" UNIQUE, btree ("rcvblNo", "tranNo",
"tranDetailSeqNo", "countyNo")
"TranDetail_TranDetAcct" btree ("acctCode", "citnNo", "countyNo")
Table "public.Adjustment"
Column | Type | Modifiers
-+---+---
adjustmentNo| "TranIdT" | not null
countyNo| "CountyNoT" | not null
date| "DateT" | not null
isTranVoided| boolean | not null
reasonCode | "ReasonCodeT" | not null
tranNo | "TranNoT" | not null
adjustsTranId | "TranIdT" |
adjustsTranNo | "TranNoT" |
adjustsTranType | "TranTypeT" |
explanation | character varying(50) |
Indexes:
"Adjustment_pkey" PRIMARY KEY, btree ("adjustmentNo", "countyNo")
"Adjustment_AdjustsTranId" btree ("adjustsTranId", "adjustsTranType",
"tranNo", "countyNo")
"Adjustment_AdjustsTranNo" btree ("adjustsTranNo", "tranNo", "countyNo")
"Adjustment_Date" btree (date, "countyNo")
Admittedly, the indexes are optimized for our query load under the commercial
product, which can use the "covering index" optimization.
explain analyze
SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",
"H"."userId", "H"."time"
FROM "Adjustment" "A"
JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND
"H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
WHERE "H"."tranType" = 'A'
AND "A"."date" > DATE '2006-01-01'
AND "H"."countyNo" = 66
AND "A"."countyNo" = 66
AND EXISTS
(
SELECT 1 FROM "TranDetail" "D"
WHERE "D"."tranNo" = "H"."tranNo"
AND "D"."countyNo" = "H"."countyNo"
AND "D"."caseNo" LIKE '2006TR%'
)
;
Nested Loop (cost=182.56..72736.37 rows=1 width=46) (actual
time=6398.108..143631.427 rows=2205 loops=1)
Join Filter: (("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar)
-> Bitmap Heap Scan on "Adjustment" "A" (cost=182.56..1535.69 rows=11542
width=22) (actual time=38.098..68.324 rows=12958 loops=1)
Recheck Cond: (((date)::date > '2006-01-01'::date) AND
(("countyNo")::smallint = 66))
-> Bitmap I
[PERFORM] Strange left outer join performance issue
Hi,
I have two queries that are very similar, that run on the same table
with slightly different conditions. However, despite a similar number
of rows returned, the query planner is insisting on a different
ordering and different join algorithm, causing a huge performance
hit. I'm not sure why the planner is doing the merge join the way it
is in the slow case, rather than following a similar plan to the fast
case.
Notice that the difference in the query is near the very end, where
it's supplier_alias_id vs. buyer_alias_id and company_type =
'Supplier' vs 'Buyer'.
What I don't get is why, in the slow (supplier) case, the index scan
on customs_records is done first without the index condition of
cr.supplier_alias_id = "outer".id, which means selecting 1.7 million
rows; why wouldn't it do a nested loop left join and have the index
condition use that alias id the way the fast ('buyer') query is done?
I'd appreciate any help -- thanks!
SLOW:
select a.id as alias_id, a.company_type as alias_company_type, a.name
as alias_name, cr.shipper as customs_record_shipper, cr.saddr1 as
customs_record_saddr1, cr.saddr2 as customs_record_saddr2, cr.saddr3
as customs_record_saddr3, cr.consignee as customs_record_consignee,
cr.caddr1 as customs_record_caddr1, cr.caddr2 as
customs_record_caddr2, cr.caddr3 as customs_record_caddr3,
cr.notify_party as customs_record_notify_party, cr.naddr1 as
customs_record_naddr1, cr.naddr2 as customs_record_naddr2, cr.naddr3
as customs_record_naddr3, cr.also_notify_party as
customs_record_also_notify_party, cr.anaddr1 as
customs_record_anaddr1, cr.anaddr2 as customs_record_anaddr2,
cr.anaddr3 as customs_record_addr3, cr.id as customs_record_id,
cr.buyer_field as customs_record_buyer_field from aliases a left
outer join customs_records cr on cr.supplier_alias_id = a.id where
a.company_type = 'Supplier' and a.company_id is NULL
Merge Right Join (cost=1138.78..460482.84 rows=2993 width=405)
(actual time=1244745.427..1245714.571 rows=39 loops=1)
Merge Cond: ("outer".supplier_alias_id = "inner".id)
-> Index Scan using index_customs_records_on_supplier_alias_id on
customs_records cr (cost=0.00..6717806.37 rows=1704859 width=363)
(actual time=54.567..1245210.707 rows=117424 loops=1)
-> Sort (cost=1138.78..1139.53 rows=300 width=46) (actual
time=24.093..24.161 rows=39 loops=1)
Sort Key: a.id
-> Index Scan using index_aliases_company_type_company_id
on aliases a (cost=0.00..1126.44 rows=300 width=46) (actual
time=22.400..23.959 rows=10 loops=1)
Index Cond: ((company_type)::text = 'Supplier'::text)
Filter: (company_id IS NULL)
Total runtime: 1245714.752 ms
FAST:
Nested Loop Left Join (cost=0.00..603052.46 rows=3244 width=405)
(actual time=68.526..3115.407 rows=1355 loops=1)
-> Index Scan using index_aliases_company_type_company_id on
aliases a (cost=0.00..639.56 rows=165 width=46) (actual
time=32.419..132.286 rows=388 loops=1)
Index Cond: ((company_type)::text = 'Buyer'::text)
Filter: (company_id IS NULL)
-> Index Scan using index_customs_records_on_buyer_alias_id on
customs_records cr (cost=0.00..3639.55 rows=915 width=363) (actual
time=2.133..7.649 rows=3 loops=388)
Index Cond: (cr.buyer_alias_id = "outer".id)
Total runtime: 3117.713 ms
(7 rows)
select a.id as alias_id, a.company_type as alias_company_type, a.name
as alias_name, cr.shipper as customs_record_shipper, cr.saddr1 as
customs_record_saddr1, cr.saddr2 as customs_record_saddr2, cr.saddr3
as customs_record_saddr3, cr.consignee as customs_record_consignee,
cr.caddr1 as customs_record_caddr1, cr.caddr2 as
customs_record_caddr2, cr.caddr3 as customs_record_caddr3,
cr.notify_party as customs_record_notify_party, cr.naddr1 as
customs_record_naddr1, cr.naddr2 as customs_record_naddr2, cr.naddr3
as customs_record_naddr3, cr.also_notify_party as
customs_record_also_notify_party, cr.anaddr1 as
customs_record_anaddr1, cr.anaddr2 as customs_record_anaddr2,
cr.anaddr3 as customs_record_addr3, cr.id as customs_record_id,
cr.buyer_field as customs_record_buyer_field from aliases a left
outer join customs_records cr on cr.buyer_alias_id = a.id where
a.company_type = 'Buyer' and a.company_id is NULL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Strange left outer join performance issue
Run VACUUM ANALYZE and see if the cost estimates became close to the
effective rows. This could make it faster.
2007/3/23, Noah M. Daniels <[EMAIL PROTECTED]>:
SLOW:
Merge Right Join (cost=1138.78..460482.84 rows=2993 width=405)
(actual time=1244745.427..1245714.571 rows=39 loops=1)
Merge Cond: ("outer".supplier_alias_id = "inner".id)
-> Index Scan using index_customs_records_on_supplier_alias_id on
customs_records cr (cost=0.00..6717806.37 rows=1704859 width=363)
(actual time=54.567..1245210.707 rows=117424 loops=1)
-> Sort (cost=1138.78..1139.53 rows=300 width=46) (actual
time=24.093..24.161 rows=39 loops=1)
Sort Key: a.id
-> Index Scan using index_aliases_company_type_company_id
on aliases a (cost=0.00..1126.44 rows=300 width=46) (actual
time=22.400..23.959 rows=10 loops=1)
Index Cond: ((company_type)::text = 'Supplier'::text)
Filter: (company_id IS NULL)
Total runtime: 1245714.752 ms
FAST:
Nested Loop Left Join (cost=0.00..603052.46 rows=3244 width=405)
(actual time=68.526..3115.407 rows=1355 loops=1)
-> Index Scan using index_aliases_company_type_company_id on
aliases a (cost=0.00..639.56 rows=165 width=46) (actual
time=32.419..132.286 rows=388 loops=1)
Index Cond: ((company_type)::text = 'Buyer'::text)
Filter: (company_id IS NULL)
-> Index Scan using index_customs_records_on_buyer_alias_id on
customs_records cr (cost=0.00..3639.55 rows=915 width=363) (actual
time=2.133..7.649 rows=3 loops=388)
Index Cond: (cr.buyer_alias_id = "outer".id)
Total runtime: 3117.713 ms
(7 rows)
--
Daniel Cristian Cruz
Analista de Sistemas
[PERFORM] Optimization pg 8.14 and postgresql.conf
I try to change my database server from the older one ie. 2Cpu Xeon 2.4 32 bit 4Gb SDram Hdd SCSI RAID 5 and FC 3 ix86 with 7..4.7 PG to the newer one with 2CPU Xeon 3.0 64 Bit 4Gb DDRram SCSI Raid5 and FC6 X64 PG 8.14 and try to use rather the same parameter from the previous postgresql.conf :- the older server config: shared_buffers = 31744 #sort_mem = 1024# min 64, size in KB sort_mem = 8192 #vacuum_mem = 8064 # min 1024, size in KB vacuum_mem = 32768 # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8# min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3# in logfile segments, min 1, 16MB each checkpoint_segments = 8 #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30# 0 is off, in seconds #commit_delay = 0 # range 0-10, in microseconds commit_delay = 20 #commit_siblings = 5# range 1-1000 #effective_cache_size = 153600 effective_cache_size = 307800 I use pgbench to test the speed of my older database server and the result is bash-3.00$ pgbench test -t 20 -c 30 -s 50 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 50 number of clients: 30 number of transactions per client: 20 number of transactions actually processed: 600/600 tps = 337.196481 (including connections establishing) tps = 375.478735 (excluding connections establishing) But my newer database server configuration is somewhat like this;- max_connections = 200 #shared_buffers = 2000 # min 16 or max_connections*2, 8KB each shared_buffers = 31744 #temp_buffers = 1000 # min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). #work_mem = 1024 # min 64, size in KB work_mem = 8192 #maintenance_work_mem = 16384 # min 1024, size in KB maintenance_work_mem = 131078 #max_stack_depth = 2048 # min 100, size in KB #commit_delay = 0 # range 0-10, in microseconds commit_delay = 20 #commit_siblings = 5 # range 1-1000 # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_segments = 8 #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # in seconds, 0 is off #effective_cache_size = 1000 # typically 8KB each effective_cache_size = 307800 #autovacuum = off # enable autovacuum subprocess? #autovacuum_naptime = 60 # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 500 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before # analyze #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit and the result of pgbench from my new server is only -- pgbench test -t 20 -c 30 -s 50 tps = 197 (including connections establishing) tps = 212 1. How should I adjust my new configuration to improve the performance ? 2. And should I set autovaccum = on and if it is on what is the other proper parameter to be set? Thank a lot for your help. Amrit Angsusingh Thailand
Re: [PERFORM] Strange left outer join performance issue
Not much of a difference, unfortunately... I still wonder why it's
doing the 'supplier' (slow) query using the merge right join.
the 'fast' query:
Nested Loop Left Join (cost=0.00..423342.71 rows=2481 width=410)
(actual time=100.076..6380.865 rows=1355 loops=1)
-> Index Scan using index_aliases_company_type_company_id on
aliases a (cost=0.00..462.33 rows=118 width=46) (actual
time=24.811..143.690 rows=388 loops=1)
Index Cond: ((company_type)::text = 'Buyer'::text)
Filter: (company_id IS NULL)
-> Index Scan using index_customs_records_on_buyer_alias_id on
customs_records cr (cost=0.00..3572.61 rows=890 width=368) (actual
time=5.526..16.042 rows=3 loops=388)
Index Cond: (cr.buyer_alias_id = "outer".id)
Total runtime: 6382.940 ms
(7 rows)
the 'slow' one:
Merge Right Join (cost=842.53..479378.17 rows=2281 width=410)
(actual time=554713.506..84.825 rows=39 loops=1)
Merge Cond: ("outer".supplier_alias_id = "inner".id)
-> Index Scan using index_customs_records_on_supplier_alias_id
on customs_records cr (cost=0.00..6673133.76 rows=1704859 width=368)
(actual time=42.327..555225.588 rows=117424 loops=1)
-> Sort (cost=842.53..843.07 rows=218 width=46) (actual
time=0.109..0.164 rows=39 loops=1)
Sort Key: a.id
-> Index Scan using index_aliases_company_type_company_id
on aliases a (cost=0.00..834.06 rows=218 width=46) (actual
time=0.033..0.074 rows=10 loops=1)
Index Cond: ((company_type)::text = 'Supplier'::text)
Filter: (company_id IS NULL)
Total runtime: 84.978 ms
(9 rows)
On Mar 23, 2007, at 4:04 PM, Daniel Cristian Cruz wrote:
Run VACUUM ANALYZE and see if the cost estimates became close to
the effective rows. This could make it faster.
2007/3/23, Noah M. Daniels <[EMAIL PROTECTED]>:
> SLOW:
> Merge Right Join (cost=1138.78..460482.84 rows=2993 width=405)
> (actual time=1244745.427..1245714.571 rows=39 loops=1)
>Merge Cond: ("outer".supplier_alias_id = "inner".id)
>-> Index Scan using
index_customs_records_on_supplier_alias_id on
> customs_records cr (cost=0.00..6717806.37 rows=1704859 width=363)
> (actual time=54.567..1245210.707 rows=117424 loops=1)
>-> Sort (cost=1138.78..1139.53 rows=300 width=46) (actual
> time=24.093..24.161 rows=39 loops=1)
> Sort Key: a.id
> -> Index Scan using index_aliases_company_type_company_id
> on aliases a (cost=0.00..1126.44 rows=300 width=46) (actual
> time=22.400..23.959 rows=10 loops=1)
>Index Cond: ((company_type)::text = 'Supplier'::text)
>Filter: (company_id IS NULL)
> Total runtime: 1245714.752 ms
>
> FAST:
>
> Nested Loop Left Join (cost=0.00..603052.46 rows=3244 width=405)
> (actual time=68.526..3115.407 rows=1355 loops=1)
> -> Index Scan using index_aliases_company_type_company_id on
> aliases a (cost= 0.00..639.56 rows=165 width=46) (actual
> time=32.419..132.286 rows=388 loops=1)
> Index Cond: ((company_type)::text = 'Buyer'::text)
> Filter: (company_id IS NULL)
> -> Index Scan using index_customs_records_on_buyer_alias_id on
> customs_records cr (cost=0.00..3639.55 rows=915 width=363) (actual
> time=2.133..7.649 rows=3 loops=388)
> Index Cond: (cr.buyer_alias_id = "outer".id)
> Total runtime: 3117.713 ms
> (7 rows)
--
Daniel Cristian Cruz
Analista de Sistemas
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [PERFORM] Strange left outer join performance issue
"Noah M. Daniels" <[EMAIL PROTECTED]> writes: > I have two queries that are very similar, that run on the same table > with slightly different conditions. However, despite a similar number > of rows returned, the query planner is insisting on a different > ordering and different join algorithm, causing a huge performance > hit. I'm not sure why the planner is doing the merge join the way it > is in the slow case, rather than following a similar plan to the fast > case. It likes the merge join because it predicts (apparently correctly) that only about 1/14th of the table will need to be scanned. This'd be an artifact of the relative ranges of supplier ids in the two tables. What PG version is this? 8.2 understands about repeated indexscans being cheaper than standalone ones, but I get the impression from the explain estimates that you may be using something older that's overestimating the cost of the nestloop way. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] EXISTS optimization
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> explain analyze
> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",
> "H"."userId", "H"."time"
> FROM "Adjustment" "A"
> JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND
> "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
> WHERE "H"."tranType" = 'A'
> AND "A"."date" > DATE '2006-01-01'
> AND "H"."countyNo" = 66
> AND "A"."countyNo" = 66
> AND EXISTS
> (
> SELECT 1 FROM "TranDetail" "D"
> WHERE "D"."tranNo" = "H"."tranNo"
> AND "D"."countyNo" = "H"."countyNo"
> AND "D"."caseNo" LIKE '2006TR%'
> )
> ;
> The commercial product scans the index on caseNo in TranDetail to build a
> work table of unique values, then uses indexed access to the TranHeader and
> then to Adjustment.
If you want that, try rewriting the EXISTS to an IN:
AND ("H"."tranNo", "H"."countyNo") IN
(
SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
WHERE "D"."caseNo" LIKE '2006TR%'
)
We don't currently try to flatten EXISTS into a unique/join plan as we
do for IN. I seem to recall not doing so when I rewrote IN planning
because I didn't think it would be exactly semantically equivalent,
but that was awhile ago. Right at the moment it seems like it ought
to be equivalent as long as the comparison operators are strict.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Strange left outer join performance issue
Tom, You're right; this is postgres 8.0.8. Perhaps upgrading will solve this issue. Is there any way to get this query to perform better in postgres 8.0.8? thanks! On Mar 23, 2007, at 6:13 PM, Tom Lane wrote: "Noah M. Daniels" <[EMAIL PROTECTED]> writes: I have two queries that are very similar, that run on the same table with slightly different conditions. However, despite a similar number of rows returned, the query planner is insisting on a different ordering and different join algorithm, causing a huge performance hit. I'm not sure why the planner is doing the merge join the way it is in the slow case, rather than following a similar plan to the fast case. It likes the merge join because it predicts (apparently correctly) that only about 1/14th of the table will need to be scanned. This'd be an artifact of the relative ranges of supplier ids in the two tables. What PG version is this? 8.2 understands about repeated indexscans being cheaper than standalone ones, but I get the impression from the explain estimates that you may be using something older that's overestimating the cost of the nestloop way. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Strange left outer join performance issue
"Noah M. Daniels" <[EMAIL PROTECTED]> writes: > You're right; this is postgres 8.0.8. Perhaps upgrading will solve > this issue. Is there any way to get this query to perform better in > postgres 8.0.8? You could try reducing random_page_cost, but I'm not sure that will help much. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [HACKERS] EXISTS optimization
>>> On Fri, Mar 23, 2007 at 4:49 PM, in message <[EMAIL PROTECTED]>,
Tom Lane <[EMAIL PROTECTED]> wrote:
> "Kevin Grittner" <[EMAIL PROTECTED]> writes:
>> explain analyze
>> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",
> "H"."userId", "H"."time"
>> FROM "Adjustment" "A"
>> JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND
> "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
>> WHERE "H"."tranType" = 'A'
>> AND "A"."date" > DATE '2006- 01- 01'
>> AND "H"."countyNo" = 66
>> AND "A"."countyNo" = 66
>> AND EXISTS
>> (
>> SELECT 1 FROM "TranDetail" "D"
>> WHERE "D"."tranNo" = "H"."tranNo"
>> AND "D"."countyNo" = "H"."countyNo"
>> AND "D"."caseNo" LIKE '2006TR%'
>> )
>> ;
>
>> The commercial product scans the index on caseNo in TranDetail to build a
> work table of unique values, then uses indexed access to the TranHeader and
> then to Adjustment.
>
> If you want that, try rewriting the EXISTS to an IN:
>
>AND ("H"."tranNo", "H"."countyNo") IN
> (
> SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
> WHERE "D"."caseNo" LIKE '2006TR%'
> )
Nice. I get this:
explain analyze
SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",
"H"."userId", "H"."time"
FROM "Adjustment" "A"
JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND
"H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
WHERE "H"."tranType" = 'A'
AND "A"."date" > DATE '2006- 01- 01'
AND "H"."countyNo" = 66
AND "A"."countyNo" = 66
AND ("H"."tranNo", "H"."countyNo") IN
(
SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
WHERE "D"."caseNo" LIKE '2006TR%'
)
;
Nested Loop (cost=27.76..36.38 rows=1 width=46) (actual time=92.999..200.398
rows=2209 loops=1)
Join Filter: (("H"."tranNo")::integer = ("A"."tranNo")::integer)
-> Nested Loop (cost=27.76..32.08 rows=1 width=50) (actual
time=92.970..176.472 rows=2209 loops=1)
-> HashAggregate (cost=27.76..27.77 rows=1 width=6) (actual
time=92.765..100.810 rows=9788 loops=1)
-> Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail"
"D" (cost=0.00..27.66 rows=20 width=6) (actual time=0.059..60.967 rows=46301
loops=1)
Index Cond: ((("caseNo")::bpchar >= '2006TR'::bpchar) AND
(("caseNo")::bpchar < '2006TS'::bpchar) AND (("countyNo")::smallint = 66))
Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)
-> Index Scan using "TranHeader_pkey" on "TranHeader" "H"
(cost=0.00..4.30 rows=1 width=46) (actual time=0.006..0.006 rows=0 loops=9788)
Index Cond: ((("H"."tranNo")::integer = ("D"."tranNo")::integer)
AND (("H"."countyNo")::smallint = 66))
Filter: (("tranType")::bpchar = 'A'::bpchar)
-> Index Scan using "Adjustment_pkey" on "Adjustment" "A" (cost=0.00..4.28
rows=1 width=22) (actual time=0.008..0.009 rows=1 loops=2209)
Index Cond: ((("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar)
AND (("A"."countyNo")::smallint = 66))
Filter: ((date)::date > '2006-01-01'::date)
Total runtime: 201.306 ms
That's the good news. The bad news is that I operate under a management
portability dictate which doesn't currently allow that syntax, since not all of
the products they want to cover support it. I tried something which seems
equivalent, but it is running for a very long time. I'll show it with just the
explain while I wait to see how long the explain analyze takes.
explain
SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",
"H"."userId", "H"."time"
FROM "Adjustment" "A"
JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND
"H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
WHERE "H"."tranType" = 'A'
AND "A"."date" > DATE '2006- 01- 01'
AND "H"."countyNo" = 66
AND "A"."countyNo" = 66
AND "H"."tranNo" IN
(
SELECT "D"."tranNo" FROM "TranDetail" "D"
WHERE "D"."caseNo" LIKE '2006TR%'
AND "D"."countyNo" = "H"."countyNo"
)
;
Nested Loop (cost=0.00..181673.08 rows=1 width=46)
Join Filter: (("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar)
-> Seq Scan on "Adjustment" "A" (cost=0.00..2384.27 rows=11733 width=22)
Filter: (((date)::date > '2006-01-01'::date) AND
(("countyNo")::smallint = 66))
-> Index Scan using "TranHeader_pkey" on "TranHeader" "H"
(cost=0.00..15.27 rows=1 width=46)
Index Cond: ((("H"."tranNo")::integer = ("A"."tranNo")::integer) AND
(("H"."countyNo")::smallint = 66))
Filter: ((("tranType")::bpchar = 'A'::bpchar) AND (subplan))
SubPlan
-> Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" "D"
(cost=0.00..27.66 rows=20 width=4)
Index Cond: ((("caseNo"
Re: [PERFORM] [HACKERS] EXISTS optimization
On Fri, Mar 23, 2007 at 05:49:42PM -0400, Tom Lane wrote: > We don't currently try to flatten EXISTS into a unique/join plan as we > do for IN. I seem to recall not doing so when I rewrote IN planning > because I didn't think it would be exactly semantically equivalent, > but that was awhile ago. Right at the moment it seems like it ought > to be equivalent as long as the comparison operators are strict. Wasn't it due to the fact that IN needs to scan through all possibilites anyway because of its interaction with NULL, whereas EXISTS can stop at the first row? That would mean the subquery to be materialised would not be equivalent if it called any non-immutable functions. It's also much less clear to be a win in the EXISTs case. But then, that's a costs issue the planner can deal with... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [PERFORM] [HACKERS] EXISTS optimization
I don't understand -- TRUE OR UNKNOWN evaluates to TRUE, so why would the IN need to continue? I'm not quite following the rest; could you elaborate or give an example? (Sorry if I'm lagging behind the rest of the class here.) -Kevin >>> Martijn van Oosterhout 03/23/07 5:26 PM >>> On Fri, Mar 23, 2007 at 05:49:42PM -0400, Tom Lane wrote: > We don't currently try to flatten EXISTS into a unique/join plan as we > do for IN. I seem to recall not doing so when I rewrote IN planning > because I didn't think it would be exactly semantically equivalent, > but that was awhile ago. Right at the moment it seems like it ought > to be equivalent as long as the comparison operators are strict. Wasn't it due to the fact that IN needs to scan through all possibilites anyway because of its interaction with NULL, whereas EXISTS can stop at the first row? That would mean the subquery to be materialised would not be equivalent if it called any non-immutable functions. It's also much less clear to be a win in the EXISTs case. But then, that's a costs issue the planner can deal with... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [HACKERS] EXISTS optimization
>>> On Fri, Mar 23, 2007 at 5:26 PM, in message
<[EMAIL PROTECTED]>, "Kevin Grittner"
<[EMAIL PROTECTED]> wrote:
> I tried something which seems
> equivalent, but it is running for a very long time. I'll show it with just
> the explain while I wait to see how long the explain analyze takes.
>
> explain
> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",
> "H"."userId", "H"."time"
> FROM "Adjustment" "A"
> JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND
> "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
> WHERE "H"."tranType" = 'A'
> AND "A"."date" > DATE '2006- 01- 01'
> AND "H"."countyNo" = 66
> AND "A"."countyNo" = 66
> AND "H"."tranNo" IN
> (
> SELECT "D"."tranNo" FROM "TranDetail" "D"
> WHERE "D"."caseNo" LIKE '2006TR%'
> AND "D"."countyNo" = "H"."countyNo"
> )
> ;
explain analyze results:
Nested Loop (cost=0.00..181673.08 rows=1 width=46) (actual
time=42224.077..964266.969 rows=2209 loops=1)
Join Filter: (("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar)
-> Seq Scan on "Adjustment" "A" (cost=0.00..2384.27 rows=11733 width=22)
(actual time=15.355..146.620 rows=13003 loops=1)
Filter: (((date)::date > '2006-01-01'::date) AND
(("countyNo")::smallint = 66))
-> Index Scan using "TranHeader_pkey" on "TranHeader" "H"
(cost=0.00..15.27 rows=1 width=46) (actual time=74.141..74.141 rows=0
loops=13003)
Index Cond: ((("H"."tranNo")::integer = ("A"."tranNo")::integer) AND
(("H"."countyNo")::smallint = 66))
Filter: ((("tranType")::bpchar = 'A'::bpchar) AND (subplan))
SubPlan
-> Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" "D"
(cost=0.00..27.66 rows=20 width=4) (actual time=0.039..58.234 rows=42342
loops=13003)
Index Cond: ((("caseNo")::bpchar >= '2006TR'::bpchar) AND
(("caseNo")::bpchar < '2006TS'::bpchar) AND (("countyNo")::smallint =
($0)::smallint))
Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)
Total runtime: 964269.555 ms
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] EXISTS optimization
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> If you want that, try rewriting the EXISTS to an IN:
>>
>> AND ("H"."tranNo", "H"."countyNo") IN
>> (
>> SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
>> WHERE "D"."caseNo" LIKE '2006TR%'
>> )
> That's the good news. The bad news is that I operate under a
> management portability dictate which doesn't currently allow that
> syntax, since not all of the products they want to cover support it.
Which part of it don't they like --- the multiple IN-comparisons?
> I tried something which seems equivalent, but it is running for a very
> long time.
> AND "H"."tranNo" IN
> (
> SELECT "D"."tranNo" FROM "TranDetail" "D"
> WHERE "D"."caseNo" LIKE '2006TR%'
> AND "D"."countyNo" = "H"."countyNo"
> )
No, that's not gonna accomplish a darn thing, because you've still got
a correlated subquery (ie, a reference to outer "H") and so turning the
IN into a join doesn't work.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] [HACKERS] EXISTS optimization
>>> On Fri, Mar 23, 2007 at 6:04 PM, in message <[EMAIL PROTECTED]>, "Peter Kovacs" <[EMAIL PROTECTED]> wrote: > On 3/23/07, Kevin Grittner <[EMAIL PROTECTED]> wrote: > [...] >> That's the good news. The bad news is that I operate under a management > portability dictate which doesn't currently allow that syntax, since not all > of the products they want to > > It doesn't really touch the substance, but I am curious: are you not > even allowed to discriminate between products in your code like: > if db is 'postresql' then > ... > else > ... > ? > > What would be the rationale for that? Anybody who's not curious about that should skip the rest of this email. Management has simply given a mandate that the software be independent of OS and database vendor, and to use Java to help with the OS independence. I have to admit that I am the architect of the database independence solution that was devised. (The choice of Java for the OS independence has been very successful. We have run our bytecode on HP-UX, Windows, Sun Solaris, and various flavors of Linux without having to compile different versions of the bytecode. Other than when people get careless with case sensitivity on file names or with path separators, it just drops right in and runs. For the data side, we write all of our queries in ANSI SQL in our own query tool, parse it, and generate Java classes to run it. The ANSI source is broken down to "lowest common denominator" queries, with all procedural code covered in the Java query classes. So we have stored procedures which can be called, triggers that fire, etc. in Java, issuing SELECT, INSERT, UPDATE, DELETE statements to the database. This allows us to funnel all DML through a few "primitive" routines which capture before and after images and save them in our own transaction image tables. We use this to replicate from our 72 county databases, which are the official court record, to multiple central databases, and a transaction repository, used for auditing case activity and assisting with failure recovery. The problem with burying 'if db is MaxDB', 'if db is SQLServer', 'if db is PostgreSQL' everywhere is that you have no idea what to do when you then want to drop in some different product. We have a plugin layer to manage known areas of differences which aren't handled cleanly by JDBC, where the default behavior is ANSI-compliant, and a few dozen to a few hundred lines need to be written to modify that default support a new database product. (Of course, each one so far has brought in a few surprises, making the plugin layer just a little bit thicker.) So, to support some new syntax, we have to update our parser, and have a way to generate code which runs on all the candidate database products, either directly or through a plugin layer. If any of the products don't support multi-value row value constructors, I have a hard time seeing a good way to cover that with the plugin. On the subject issue, I'm pretty sure it would actually be less work for me to modify the PostgreSQL optimizer to efficiently handle the syntax we do support than to try to bend row value constructors to a syntax that is supported on other database products. And, by the way, I did take a shot on getting them to commit to PostgreSQL as the long-term solution, and relax the portability rules. No sale. Perhaps when everything is converted to PostgreSQL and working for a while they may reconsider. -Kevin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Optimization postgresql 8.1.4 FC 6 X64 ?
I try to change my database server from the older one ie. 2Cpu Xeon 2.4 32 bit 4Gb SDram Hdd SCSI RAID 5 and FC 3 ix86 with 7..4.7 PG to the newer one with 2CPU Xeon 3.0 64 Bit 4Gb DDRram SCSI Raid5 and FC6 X64 PG 8.14 and try to use rather the same parameter from the previous postgresql.conf :- the older server config -- shared_buffers = 31744 #sort_mem = 1024# min 64, size in KB sort_mem = 8192 #vacuum_mem = 8064 # min 1024, size in KB vacuum_mem = 32768 # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8# min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3# in logfile segments, min 1, 16MB each checkpoint_segments = 8 #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30# 0 is off, in seconds #commit_delay = 0 # range 0-10, in microseconds commit_delay = 20 #commit_siblings = 5# range 1-1000 #effective_cache_size = 153600 effective_cache_size = 307800 I use pgbench to test the speed of my older database server and the result is bash-3.00$ pgbench test -t 20 -c 30 -s 50 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 50 number of clients: 30 number of transactions per client: 20 number of transactions actually processed: 600/600 tps = 337.196481 (including connections establishing) tps = 375.478735 (excluding connections establishing) But my newer database server configuration is somewhat like this;- max_connections = 200 #shared_buffers = 2000 # min 16 or max_connections*2, 8KB each shared_buffers = 31744 #temp_buffers = 1000 # min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). #work_mem = 1024 # min 64, size in KB work_mem = 8192 #maintenance_work_mem = 16384 # min 1024, size in KB maintenance_work_mem = 131078 #max_stack_depth = 2048 # min 100, size in KB #commit_delay = 0 # range 0-10, in microseconds commit_delay = 20 #commit_siblings = 5 # range 1-1000 # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_segments = 8 #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # in seconds, 0 is off #effective_cache_size = 1000 # typically 8KB each effective_cache_size = 307800 #autovacuum = off # enable autovacuum subprocess? #autovacuum_naptime = 60 # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 500 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before # analyze #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit and the result of pgbench from my new server is only -- pgbench test -t 20 -c 30 -s 50 tps = 197 (including connections establishing) tps = 212 1. How should I adjust my new configuration to improve the performance ? 2. And should I set autovaccum = on and if it is on what is the other proper parameter to be set? Thank a lot for your help. Amrit Angsusingh Thailand
