[HACKERS] signal handling
hi all: is there any unused signal on postgres? TIA and regards
[HACKERS] Performance on NUMA
- Original Message - From: Luis Alberto Amigo Navarro [EMAIL PROTECTED] To: Neil Conway [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 5:02 PM Subject: Re: [HACKERS] a tiny question A small patch for low performance on NUMA arquitectures could be the chance of using more than one shared region. Several months away there was a brief talk about low performance on IRIX, it was not real, it's low performance on Origin servers, they use ccNUMA architecture, so using a single shared segment allocated by only one proccess doesn't give IRIX planner a chance for attempting to optimize memory accesses, even thought page migration is almost impossible with DB memory accesses. With this scenario, IRIX uses a first-touch algorith for memory placement (all the memory is allocated on the node Postgres starts running). Due to high latency between local and remote memory accesses, if there is data modification, remote proccesors cache hit rate goes down, all of this makes that all the basis of high scalability on this systems(big cache, memory distribution, increasing bandwith and page migration) ar thrown to the earth. I suppose that making more than one shared segment it's not too difficult, but with this u can do that a long time Postgres running on a NUMA system achieve a good performance scenario. Is it so difficult? Thanks and regards. Luis Amigo ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: 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: [HACKERS] a tiny question
Another tiny question: Is there a way to set more than one shared regions? Thanks and regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] a tiny question
I supposed it, but I've not seen 7.3. A small patch for low performance on NUMA arquitectures could be the chance of using more than one shared region. Several months away there was a brief talk about low performance on IRIX, it was not real, it's low performance on Origin servers, they use ccNUMA architecture, so using a single shared segment allocated by only one proccess doesn't give IRIX planner a chance for attempting to optimize memory accesses, even thought page migration is almost impossible with DB memory accesses. With this scenario, IRIX uses a first-touch algorith for memory placement (all the memory is allocated on the node Postgres starts running). Due to high latency between local and remote memory accesses, if there is data modification, remote proccesors cache hit rate goes down, all of this makes that all the basis of high scalability on this systems(big cache, memory distribution, increasing bandwith and page migration) ar thrown to the earth. I suppose that making more than one shared segment it's not too difficult, but with this u can do that a long time Postgres running on a NUMA system achieve a good performance scenario. Is it so difficult? Thanks and regards. Luis Amigo ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] a tiny question
a guess it's said inter-locking, I mean data accessed exclusively. I apologize for my english - Original Message - From: Neil Conway [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 4:47 PM Subject: Re: [HACKERS] a tiny question Luis Alberto Amigo Navarro [EMAIL PROTECTED] writes: When we improve seq scans, systems scales well up to 8 cpus.When we improve index scan, query performance increase but system stops scaling at about 4 proccessors, profiling shows that it is due to increased memory contention What do you mean by memory contention? Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: 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: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5
regression tests are tested and passed with IRIX 6.5.13 and 6.5.16 and postgres from 7.1.3 to 7.2.2 there where previously passed tests with IRIX 6.5.5, but I don't know what postgres versions Regards - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, October 24, 2002 12:01 AM Subject: Re: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5 Hello again to everybody! I've just downloaded and installed a binary version of postgresql 7.1.1 from ftp.postgresql.org and I have the same bad results: crashes during regression tests. Can please someone confirm me that the binary version posted there is able to pass all the regression tests? This way I will know that the problem is somwere in my IRIX setup. Thank you, mache On Wed, 23 Oct 2002, Nicolae Mihalache wrote: Hello! I'm tring to move a database and an application from linux to IRIX and I have some problems getting postgresql to work on IRIX. My problem is that postgres crashes is killed by the kernel because of (this is from /var/log/SYSLOG): unix: |$(0x6db)ALERT: Process [postgres] pid 105818 killed: not enough memory to lock stack This happens when running the regression tests at the trigger and also when I try to import my db schema that I've dump with pg_dump on the linux machine. Unfortunately I have not much experience with IRIX, so I don't know if it is postgres fault or is a bad configuration. What I've tried to do is to increase the maxkmem kernel parameter from 2000 to 2 but it makes no difference. The machine has 256MB ram and when running postgres seems to take no more than 15MB. Some system information: uname -a: IRIX cassini 6.5 04101931 IP32 mips cc -version: MIPSpro Compilers: Version 7.2.1 src/templates/irix5 file: CC=cc CFLAGS='-O2' LDFLAGS='-O2 -lcurses -n32' configure command: ./configure --prefix=/disk1/users/robust/famous/local --with-tcl --with-includes=/disk1/users/robust/famous/local/include --with-libraries=/disk1/users/robust/famous/local/lib --disable-locale --without-java --without-CXX Thank you for any ideea that can help, Nicolae Mihalache ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5
could send the output from: limit and systune | grep shm also, what kind of machine is regards - Original Message - From: Nicolae Mihalache [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 23, 2002 5:57 PM Subject: crashes with postgresql 7.2.1 on IRIX 6.5 Hello! I'm tring to move a database and an application from linux to IRIX and I have some problems getting postgresql to work on IRIX. My problem is that postgres crashes is killed by the kernel because of (this is from /var/log/SYSLOG): unix: |$(0x6db)ALERT: Process [postgres] pid 105818 killed: not enough memory to lock stack This happens when running the regression tests at the trigger and also when I try to import my db schema that I've dump with pg_dump on the linux machine. Unfortunately I have not much experience with IRIX, so I don't know if it is postgres fault or is a bad configuration. What I've tried to do is to increase the maxkmem kernel parameter from 2000 to 2 but it makes no difference. The machine has 256MB ram and when running postgres seems to take no more than 15MB. Some system information: uname -a: IRIX cassini 6.5 04101931 IP32 mips cc -version: MIPSpro Compilers: Version 7.2.1 src/templates/irix5 file: CC=cc CFLAGS='-O2' LDFLAGS='-O2 -lcurses -n32' configure command: ./configure --prefix=/disk1/users/robust/famous/local --with-tcl --with-includes=/disk1/users/robust/famous/local/include --with-libraries=/disk1/users/robust/famous/local/lib --disable-locale --without-java --without-CXX Thank you for any ideea that can help, Nicolae Mihalache ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5
System configuration looks fine to me, there is no upper limit, the only configuration problem a can see is that stacksize limit is set to 512Mb when u only have 256Mb ram, try limit stacksize 65536 (64Mb) a most serious size, if it does not help I can't be of any more help. If it helps u may need to change hard limit(this is a soft limit) with systune. Regards - Original Message - From: Nicolae Mihalache [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 23, 2002 6:09 PM Subject: Re: crashes with postgresql 7.2.1 on IRIX 6.5 Luis Alberto Amigo Navarro wrote: could send the output from: limit and systune | grep shm also, what kind of machine is regards robust@cassini limit cputime unlimited filesizeunlimited datasize2097152 kbytes stacksize 524288 kbytes coredumpsizeunlimited memoryuse 524288 kbytes vmemoryuse 2097152 kbytes descriptors 2500 threads 1024 robust@cassini systune |grep shm group: shm (statically changeable) sshmseg = 2000 (0x7d0) shmmni = 1064 (0x428) shmmin = 1 (0x1) shmmax = 214748365 (0xccd) robust@cassini hinv CPU: MIPS R1 Processor Chip Revision: 2.7 FPU: MIPS R10010 Floating Point Chip Revision: 0.0 1 195 MHZ IP32 Processor Main memory size: 256 Mbytes Secondary unified instruction/data cache size: 1 Mbyte on Processor 0 Instruction cache size: 32 Kbytes Data cache size: 32 Kbytes FLASH PROM version 4.17 Integral SCSI controller 0: Version ADAPTEC 7880 Disk drive: unit 2 on SCSI controller 0 CDROM: unit 4 on SCSI controller 0 Integral SCSI controller 1: Version ADAPTEC 7880 Disk drive: unit 5 on SCSI controller 1 On-board serial ports: tty1 On-board serial ports: tty2 On-board EPP/ECP parallel port CRM graphics installed Integral Ethernet: ec0, version 1 Iris Audio Processor: version A3 revision 0 Video: MVP unit 0 version 1.4 AV: AV2 Card version 0, Camera not connected. Vice: TRE IEEE 1394 High performance serial bus controller 0: Type: OHCI, Version 0x104C-1 0 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Question about LWLockAcquire's use of semaphores instead
- Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; [EMAIL PROTECTED] We would have to understand how the SGI code is better than our existing code on SMP machines. I've been searching for data from SGI's Origin presentation to illustrate what am I saying, this graph only covers Memory bandwith, but take present that as distance between nodes increase, memory access latency is also increased: Imagen1.png Description: PNG image ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Question about LWLockAcquire's use of semaphores instead
We would have to understand how the SGI code is better than our existing code on SMP machines. there is a big problem with postgres on SGI NUMA architectures, on UMA systems postgres works fine, but NUMA Origins need a native shared memory management. It scales fine over old challenges, but scales very poorly on NUMA architectures, giving fine speed-up only within a single node. For more than one node throughput drops greatly, implementing Round-robin memory placement algorithms it gets a bit better, changing from forks to native sprocs(medium-weighted processes) makes it work better, but not good enough, if you want postgres to run fine on this machines I think (it's not tested yet) it would be neccesary to implement native shared arenas instead of IPC shared memory in order to let IRIX make a fine load-balance. I take advantage of this message to say that there is a cuple of things that we have to insert on FAQ-IRIX about using 32 bits or 64 bits objects, because it is a known issue that using 32 bit objects on IRIX do not allow to use more than 1,2 Gb of shared memory because system management is unable to find a single segment of this size. Regards ---(end of broadcast)--- TIP 3: 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
[HACKERS] question on backends
Hi all As I understand every time there is a request to postgres a new backend is made, and when the request is finished, even if the connection is already active the backend dies. I wonder if is there any parameter that allow backends to remain beyond a transaction. Creating a new backend every time a transaction is made means forking the code and reallocating sort_memory. Although it is not a high resource usage, on short transactions as OLTPs it is a relevant work time, I think it would be interesting that a predefined number of backends were allowed to remain active beyond the transaction. Thanks and Regards
Re: [HACKERS] Question about LWLockAcquire's use of semaphores instead of spinlocks
- Original Message - From: Robert E. Bruccoleri [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 29, 2002 2:48 AM Subject: Re: [HACKERS] Question about LWLockAcquire's use of semaphores instead of spinlocks Dear Luis, I would be very interested. Replacing the IPC shared memory with an arena make a lot of sense. --Bob On old PowerChallenge postgres works really fine, but in new NUMA architectures postgres works so badly, as we have known, forked backends don't allow IRIX to manage memory as it would be desired. Leaving First Touch placement algorithm means that almost every useful data is placed on the first node the process is run. Trying to use more than one node with this schema results in a false sharing, secondary cache hits ratio drops below 85% due to latency on a second node is about 6 times bigger than in the first node even worse if you have more than 4 nodes. All of this causes that you're almost only working with a node (4 cpus in origin 3000). Implementing Round-Robin placement algorithms causes that memory pages are placed each one in one node, this causes that all nodes have the same chance to work with some pages locally and some pages remotely. The more the number of nodes, the more advantage you can take with round-robin. You can enable round-robin recompiling postgres, setting before the enviroment variable _DSM_ROUND_ROBIN=TRUE it works fine with fork(), and it is not necessary using sprocs. Changing IPC shared memory for a shared arena could improve performance because it's the native shared segment on IRIX. it's something we're willing to do, but by now it is only a project. Hope it helps ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] question on backends
libpq PQsetdb( - Original Message - From: Hannu Krosing [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, July 29, 2002 9:40 PM Subject: Re: [HACKERS] question on backends On Mon, 2002-07-29 at 20:21, Luis Alberto Amigo Navarro wrote: if i put debug_level=1 i get for one connect and several inserts on backend die after each insert What client do you use ? -- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] question on backends
How? - Original Message - From: Christopher Kings-Lynne To: Luis Alberto Amigo Navarro ; [EMAIL PROTECTED] Sent: Monday, July 29, 2002 12:36 PM Subject: Re: [HACKERS] question on backends Justuse persistent connections. Chris - Original Message - From: Luis Alberto Amigo Navarro To: [EMAIL PROTECTED] Sent: Monday, July 29, 2002 5:32 PM Subject: [HACKERS] question on backends Hi all As I understand every time there is a request to postgres a new backend is made, and when the request is finished, even if the connection is already active the backend dies. I wonder if is there any parameter that allow backends to remain beyond a transaction. Creating a new backend every time a transaction is made means forking the code and reallocating sort_memory. Although it is not a high resource usage, on short transactions as OLTPs it is a relevant work time, I think it would be interesting that a predefined number of backends were allowed to remain active beyond the transaction. Thanks and Regards
Re: [HACKERS] Question about LWLockAcquire's use of semaphores instead of spinlocks
Hi Bob: We're have been working with an sproc version of postgres and it has improve performance over a NUMA3 origin 3000 due to IRIX implements round_robin by default on memory placement instead of first touch as it did on fork. We're been wondering about replacing IPC shmem with a shared arena to help performance improve on IRIX. I dont´know if people here in postgres are interested on specifical ports but it could help you improve your performance. Regards - Original Message - From: Robert E. Bruccoleri [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, July 28, 2002 5:45 AM Subject: Re: [HACKERS] Question about LWLockAcquire's use of semaphores instead of spinlocks Tom Lane writes: Robert E. Bruccoleri [EMAIL PROTECTED] writes: On SGI multiprocessor machines, I suspect that a spinlock implementation of LWLockAcquire would give better performance than using IPC semaphores. Is there any specific reason that a spinlock could not be used in this context? Are you confusing LWLockAcquire with TAS spinlocks? No. If you're saying that we don't have an implementation of TAS for SGI hardware, then feel free to contribute one. If you are wanting to replace LWLocks with spinlocks, then you are sadly mistaken, IMHO. This touches on my question. Why am I mistaken? I don't understand. BTW, about 5 years ago, I rewrote the TAS spinlocks for the SGI platform to make it work correctly. The current implementation is fine. +-++ | Robert E. Bruccoleri, Ph.D. | email: [EMAIL PROTECTED]| | P.O. Box 314| URL: http://www.congen.com/~bruc | | Pennington, NJ 08534|| +-++ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: 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: [HACKERS] why is postgres estimating so badly?
AND part.name LIKE '%green%' It's difficult for the planner to produce a decent estimate for the selectivity of an unanchored LIKE clause, since there are no statistics it can use for the purpose. We recently changed FIXED_CHAR_SEL in src/backend/utils/adt/selfuncs.c from 0.04 to 0.20, which would make this particular case come out better. (I believe the estimate would work out to about 320, if part is 200K rows; that should be enough to produce at least some change of plan.) You could try patching your local installation likewise. Here are the results, worse than before: NOTICE: QUERY PLAN: Sort (cost=25209.88..25209.88 rows=1 width=93) (actual time=1836143.78..1836144.48 rows=175 loops=1) - Aggregate (cost=25209.85..25209.87 rows=1 width=93) (actual time=1803559.97..1836136.47 rows=175 loops=1) - Group (cost=25209.85..25209.86 rows=2 width=93) (actual time=1803348.04..1816093.89 rows=325302 loops=1) - Sort (cost=25209.85..25209.85 rows=2 width=93) (actual time=1803347.97..1804795.41 rows=325302 loops=1) - Hash Join (cost=25208.43..25209.84 rows=2 width=93) (actual time=1744714.61..1772790.19 rows=325302 loops=1) - Seq Scan on nation (cost=0.00..1.25 rows=25 width=15) (actual time=13.92..14.84 rows=25 loops=1) - Hash (cost=25208.42..25208.42 rows=2 width=78) (actual time=1744603.74..1744603.74 rows=0 loops=1) - Nested Loop (cost=0.00..25208.42 rows=2 width=78) (actual time=139.21..1740110.04 rows=325302 loops=1) - Nested Loop (cost=0.00..25201.19 rows=2 width=70) (actual time=122.37..1687895.49 rows=325302 loops=1) - Nested Loop (cost=0.00..25187.93 rows=4 width=62) (actual time=121.75..856097.27 rows=325302 loops=1) - Nested Loop (cost=0.00..17468.91 rows=1280 width=24) (actual time=78.43..19698.77 rows=43424 loops=1) - Seq Scan on part (cost=0.00..12399.00 rows=320 width=4) (actual time=29.57..4179.70 rows=10856 loops=1) - Index Scan using partsupp_pkey on partsupp (cost=0.00..15.79 rows=4 width=20) (actual time=1.17..1.33 rows=4 loops=10856) - Index Scan using l_partsupp_index on lineitem (cost=0.00..6.02 rows=1 width=38) (actual time=2.83..18.97 rows=7 loops=43424) - Index Scan using orders_pkey on orders (cost=0.00..3.23 rows=1 width=8) (actual time=2.47..2.50 rows=1 loops=325302) - Index Scan using supplier_pkey on supplier (cost=0.00..3.01 rows=1 width=8) (actual time=0.08..0.09 rows=1 loops=325302) Total runtime: 1836375.16 msec It looks even worse, another advice?, or maybe a query change. here is the query again: SELECT nation, o_year, CAST((sum(amount))AS NUMERIC(10,2))AS sum_profit FROM( SELECT nation.name AS nation, EXTRACT(year FROM orders.orderdate) AS o_year, lineitem.extendedprice*(1-lineitem.discount)-partsupp.supplycost*lineitem.qu antity AS amount FROM part, supplier, lineitem, partsupp, orders, nation WHERE supplier.suppkey=lineitem.suppkey AND partsupp.suppkey=lineitem.suppkey AND partsupp.partkey=lineitem.partkey AND part.partkey=lineitem.partkey AND orders.orderkey=lineitem.orderkey AND supplier.nationkey=nation.nationkey AND part.name LIKE '%green%' ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC; Thanks and regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] why is postgres estimating so badly?
I have a query and estimations and results don´t look similar, here is explain analyze: NOTICE: QUERY PLAN: Sort (cost=12443.90..12443.90 rows=1 width=93) (actual time=505331.94..505332.67 rows=175 loops=1) - Aggregate (cost=12443.88..12443.89 rows=1 width=93) (actual time=472520.29..505326.48 rows=175 loops=1) - Group (cost=12443.88..12443.89 rows=1 width=93) (actual time=472307.31..485173.92 rows=325302 loops=1) - Sort (cost=12443.88..12443.88 rows=1 width=93) (actual time=472307.24..473769.79 rows=325302 loops=1) - Nested Loop (cost=12439.25..12443.87 rows=1 width=93) (actual time=103787.68..441614.43 rows=325302 loops=1) - Hash Join (cost=12439.25..12440.64 rows=1 width=85) (actual time=103733.76..120916.86 rows=325302 loops=1) - Seq Scan on nation (cost=0.00..1.25 rows=25 width=15) (actual time=7.81..8.72 rows=25 loops=1) - Hash (cost=12439.25..12439.25 rows=1 width=70) (actual time=103722.25..103722.25 rows=0 loops=1) - Nested Loop (cost=0.00..12439.25 rows=1 width=70) (actual time=95.43..100162.91 rows=325302 loops=1) - Nested Loop (cost=0.00..12436.23 rows=1 width=62) (actual time=84.91..47502.93 rows=325302 loops=1) - Nested Loop (cost=0.00..12412.93 rows=4 width=24) (actual time=66.86..8806.01 rows=43424 loops=1) - Seq Scan on part (cost=0.00..12399.00 rows=1 width=4) (actual time=24.88..4076.81 rows=10856 loops=1) - Index Scan using partsupp_pkey on partsupp (cost=0.00..13.89 rows=4 width=20) (actual time=0.20..0.34 rows=4 loops=10856) - Index Scan using l_partsupp_index on lineitem (cost=0.00..6.02 rows=1 width=38) (actual time=0.20..0.61 rows=7 loops=43424) - Index Scan using supplier_pkey on supplier (cost=0.00..3.01 rows=1 width=8) (actual time=0.08..0.10 rows=1 loops=325302) - Index Scan using orders_pkey on orders (cost=0.00..3.22 rows=1 width=8) (actual time=0.85..0.87 rows=1 loops=325302)Total runtime: 505563.85 msec estimated 12000msec here is the query: SELECTnation,o_year,CAST((sum(amount))AS NUMERIC(10,2))AS sum_profitFROM(SELECTnation.name AS nation,EXTRACT(year FROM orders.orderdate) AS o_year,lineitem.extendedprice*(1-lineitem.discount)-partsupp.supplycost*lineitem.quantity AS amountFROMpart,supplier,lineitem,partsupp,orders,nationWHEREsupplier.suppkey=lineitem.suppkeyAND partsupp.suppkey=lineitem.suppkeyAND partsupp.partkey=lineitem.partkeyAND part.partkey=lineitem.partkeyAND orders.orderkey=lineitem.orderkeyAND supplier.nationkey=nation.nationkeyAND part.name LIKE '%green%') AS profitGROUP BYnation,o_yearORDER BYnation,o_year DESC; lineitem is about 6M rows partsupp 800K rows part 200K rows any advice? Thanks and regards
[HACKERS] please help on query
I can't improve performance on this
query:
SELECTsupplier.name,supplier.addressFROMsupplier,nationWHEREsupplier.suppkey
IN(SELECTpartsupp.suppkeyFROMpartsuppWHEREpartsupp.partkey
IN(SELECTpart.partkeyFROMpartWHEREpart.name
like 'forest%')AND
partsupp.availqty(SELECT0.5*(sum(lineitem.quantity)::FLOAT)FROMlineitemWHERElineitem.partkey=partsupp.partkeyAND
lineitem.suppkey=partsupp.partkeyAND
lineitem.shipdate=('1994-01-01')::DATEAND
lineitem.shipdate(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE))AND
supplier.nationkey=nation.nationkeyAND nation.name='CANADA'ORDER
BYsupplier.name;
explain results:
NOTICE: QUERY PLAN:
Sort (cost=2777810917708.17..2777810917708.17
rows=200 width=81) - Nested Loop
(cost=0.00..2777810917700.53 rows=200
width=81) - Seq Scan on
nation (cost=0.00..1.31 rows=1
width=4) - Index Scan
using snation_index on supplier (cost=0.00..2777810917696.72 rows=200
width=77)
SubPlan
- Materialize (cost=6944527291.72..6944527291.72 rows=13
width=4)
- Seq Scan on partsupp (cost=0.00..6944527291.72 rows=13
width=4)
SubPlan
- Materialize (cost=8561.00..8561.00 rows=1
width=4)
- Seq Scan on part (cost=0.00..8561.00 rows=1
width=4)
- Aggregate (cost=119.61..119.61 rows=1
width=4)
- Index Scan using lineitem_index on lineitem (cost=0.00..119.61
rows=1 width=4)
partsupp::80 tuples
Table
"partsupp" Column |
Type | Modifiers
++---partkey
| integer | not
nullsuppkey |
integer | not
nullavailqty |
integer | supplycost |
numeric(10,2) | comment | character(199) |
Primary key: partsupp_pkeyTriggers:
RI_ConstraintTrigger_16597,
RI_ConstraintTrigger_16603
tpch=# select attname,n_distinct,correlation from
pg_stats where tablename='partsupp'; attname | n_distinct
| correlation
++-partkey
| -0.195588 |
1suppkey | 9910
| 0.00868363availqty
| 9435 | -0.00788662supplycost
| -0.127722 | -0.0116864comment
| -1 |
0.0170702
I accept query changes, reordering, indexes ideas
and horizontal partitioning
thanks in advance.
Regards
Re: [HACKERS] PostgreSQL index usage discussion.
(2) Use programmatic hints which allow coders specify which indexes are used during a query. (ala Oracle) As I said before it would be useful a way to improve(not force) using indexes on particular queries, i.e. lowering the cost of using this index on this query. Regards ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL index usage discussion.
I was told that DB2 has per-table (or rather per-tablespace) knowledge of disk speeds, so keeping separate random and seqsqan costs for each table and index could be a good way here (to force use of a particular index make its use cheap) I was wondering something even easier, keeping 1 cost per index, 1 cost per seqscan, but being allowed to scale cost for each index on each query(recommended, null or unrecommended) Regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
I was thinking in something independent from the executor, simply a variable that recommends or not the use of a particular index, it could be obtained from user, and so it could be improved(a factor lower than 1) on planner. How about something like this? - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Cc: Lincoln Yeoh [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; mlw [EMAIL PROTECTED]; Andrew Sullivan [EMAIL PROTECTED]; PostgreSQL-development [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 6:42 PM Subject: Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE Luis Alberto Amigo Navarro wrote: Hi All. I've been reading all the thread and I want to add a few points: You can set enable_seqscan=off in small or easy queries, but in large queries index can speed parts of the query and slow other, so I think it is neccesary if you want Postgres to become a Wide-used DBMS that the planner could be able to decide accuratelly, in the thread there is a point that might be useful, it will be very interesting that the planner could learn with previous executions, even there could be a warm-up policy to let planner learn about how the DB is working, this info could be stored with DB data, and could statistically show how use of index or seqscan works on every column of the DB. Yes, I have always felt it would be good to feed back information from the executor to the optimizer to help with later estimates. Of course, I never figured out how to do it. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
Hi All. I've been reading all the thread and I want to add a few points: You can set enable_seqscan=off in small or easy queries, but in large queries index can speed parts of the query and slow other, so I think it is neccesary if you want Postgres to become a Wide-used DBMS that the planner could be able to decide accuratelly, in the thread there is a point that might be useful, it will be very interesting that the planner could learn with previous executions, even there could be a warm-up policy to let planner learn about how the DB is working, this info could be stored with DB data, and could statistically show how use of index or seqscan works on every column of the DB. I think it will be useful hearing all users and not guiding only with our own experience, the main objective is to make a versatil DBMS, It's very easy to get down the need of improving indexes with single selects, but a lot of us are not doing single select, so I think that point needs to be heard. Regards ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
Look at the pgbench utility. I can't run that program without a +- 10% variation from run to run, no mater how many times I run vacuum and checkpoint. It's pgbench's fault, TPC-B was replaced with TPC-C because it is not accurate enough, we run a pseudo TPC-H and it has almost no variations from one run to another. Regards ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
On Wed, 2002-04-17 at 06:51, mlw wrote: I just think there is sufficient evidence to suggest that if a DBA creates an index, there is strong evidence (better than statistics) that the index need be used. In the event that an index exists, there is a strong indication that, without overwhelming evidence, that the index should be used. You have admitted that statistics suck, but the existence of an index must weight (heavily) on the evaluation on whether or not to use an index. On my own few experience I think this could be solved decreasing random_page_cost, if you would prefer to use indexes than seq scans, then you can lower random_page_cost to a point in which postgres works as you want. So the planner would prefer indexes when in standard conditions it would prefer seq scans. Regards ---(end of broadcast)--- TIP 3: 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: Fw: Fw: [HACKERS] bad performance on irix
Makes me wonder... perhaps now someone will be convinced to take a look at the POSIX IPC patch. On some platforms (not on Linux I am afraid) POSIX mutexes might be quite a bit faster than SYSV semaphores. Is there any current patch? Regards ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Fw: Fw: [HACKERS] bad performance on irix
I've done some meditions with timex, it uses sar(System activity register) to take workloads, it's not very relliable, but it allow us to see how it is been doing, it has been taken during an execution of a like tpc-h benchmark, it performs inserts, deletes(about 5% of the time of the execution) and a set of 8 continous streams of 22 read only queries, notice that it only gives idle time (not the cause of idle), notice semafores/sec is up to 2700!!! Regards 12:27:08 %usr %sys %intr %wio %idle %sbrk %wfs %wswp %wphy %wgsw %wfif 12:55:3932 3 0 956 0 100 0 0 0 09% waiting for I/O which is 100% file system 12:27:08 device %busy avque r+w/s blks/sw/s wblks/s avwait avserv 12:55:39 dks0d5 00.00.0 00.0 0 0.0 0.0 dks1d1 13.10.7 190.7 1627.8 15.0 dks1d2 01.00.0 00.0 0 0.0 13.3 dks1d3 00.00.0 00.0 0 0.0 0.0 dks1d423 15.39.117057.81553 519.7 24.8 12:27:08 bread/s lread/s %rcach bwrit/s lwrit/s wcncl/s %wcach pread/s pwrit/s 12:55:39 1582372 9315499072 1 83 0 093% of read cache hits and 83% of write chache hits 12:27:08 scall/s sread/s swrit/s fork/s exec/s rchar/s wchar/s 12:55:394618 181 1260.180.06 648854 580354 syscalls averages 12:27:08 msg/s sema/s 12:55:390.00 2704.28 12:27:08 vflt/s dfill/s cache/s pgswp/s pgfil/s pflt/s cpyw/s steal/s rclm/snotice that there aren't page swaps, so idle is not waiting for paging 12:55:39 862.58 58.31 804.240.000.045.703.11 60.90 0.00 12:27:08CPU %usr %sys %intr %wio %idle %sbrk %wfs %wswp %wphy %wgsw %wfif 12:55:39 025 3 0 863 0 100 0 0 0 0 per CPU usage 125 3 0 962 0 100 0 0 0 0 224 3 0 964 0 100 0 0 0 0 330 3 0 859 0 100 0 0 0 0 430 3 0 859 0 100 0 0 0 0 539 3 0 850 0 100 0 0 0 0 654 3 0 834 0 100 0 0 0 0 733 3 0 855 0 100 0 0 0 0 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgres is not using tas
postgres is compiled with Mipspro compiler, how may i prepare it for profiling. Thanks and regards ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] postgres is not using tas
Theoriginal problem was the low cpu usage due to semaphores, most of orange zone is due to sems thanks and regards attachment: ejecucion.jpg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] postgres is not using tas
hi tom It is compiled with mips pro compilers I've tried to remove if defined in s_lock.h, but it's still using semop, is there any other side it could be defined. thanks and regards. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fw: [HACKERS] bad performance on irix
- Original Message - From: Robert E. Bruccoleri [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Sent: Monday, March 18, 2002 4:08 PM Subject: Re: [HACKERS] bad performance on irix Dear Luis, Dear Bob: I've removed ifdefs from s_lock.h trying if semop using was define problem, but it's still using semop any suggest? No, I see the same compilation as you do with 7.2. It's using the spinlocks for some locks, but semaphores for others. I don't know what to do next. Alas... --Bob +-++ | Robert E. Bruccoleri, Ph.D. | email: [EMAIL PROTECTED]| | P.O. Box 314| URL: http://www.congen.com/~bruc | | Pennington, NJ 08534|| +-++ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] postgres is not using tas
As i know, it's only using semop, even with TAS_AND_SET defined, this is an extract from postmaster's process registry 2515.934mS(+ 5914uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 a9 6e 10 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2520.497mS(+ 4562uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 a9 9a 18 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2526.496mS(+ 5998uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 a9 c6 38 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2527.115mS(+ 619uS)[ 4]postgres(38089): semop(1568, 0x7fff1c70, 1) OK 2527.314mS(+ 198uS)[ 4]postgres(38089): semop(1568, 0x7fff1c70, 1) OK 2527.390mS(+ 76uS)[ 4]postgres(38089): semop(1568, 0x7fff1c70, 1) OK 2532.199mS(+ 4809uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 a9 f2 40 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2537.896mS(+ 5696uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 aa 1e 48 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2543.147mS(+ 5251uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 aa 4a 68 00 00 00 22 00 a8 00 c8..., 8192) = 8192 Thanks and regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] postgres is not using tas
here is the execution of one backend summary: System call summary: Average Total Name #Calls Time(ms) Time(ms) - semop 39305 0.06 2497.53 select 7 19.86139.01 unlink 1 22.96 22.96 close 49 0.04 2.06 recv1 0.72 0.72 send1 0.11 0.11 fsync 1 0.07 0.07 prctl 1 0.01 0.01 exit1 0.00 0.00 As u can see it's amazing Thanks and regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] postgres is not using tas
hi tom could you please tell me where to find info on when and why is semop used, this thread began because i had excessive sem usage as u can see thanks and regards ---(end of broadcast)--- TIP 3: 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: [HACKERS] postgres is not using tas
hi tom If i track a single backend during an 8 read-only queries parallel execution these are the results( System call summary: Average Total Name #Calls Time(ms) Time(ms) - semop3803 0.20774.03 select 4 19.58 78.33 recv1 2.41 2.41 brk 6 0.08 0.48 close 1 0.14 0.14 send1 0.14 0.14 semctl 1 0.05 0.05 prctl 1 0.01 0.01 exit1 0.00 0.00 I think it's a bit excessive for a 8 SMP what do u think? thanks and regards ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgres is not using tas
hi tom how may we have believable statistics? what do u think about the graph i've sent to you, there are retrieved using hardware counters, i believe they are exact. Any idea? Thanks and regards ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
