[PERFORM] Benchmarking PG
Hi to all. I'd like to benchmark PG. I'd like to compare sorting performances (time spent, #of disk accesses, # of run produced etc) of the present Replacement Selection (external sorting) algorithm and of a refinement I'm going to implement. I'm new on PG, I just had the idea of how to possibly get better that algorithm and choosed to test it on PG since it's an open-source DBMS. I've been searching the web for a benchmark. I suppose TPC-H and Wisconsin could fit, but had problems when trying to use them. Any suggestion on a "good benchmark"? Any tutorial on how to use them? Thanks for your time. Regards. Manolo. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Benchmarking PG
On Dec 11, 2007 4:06 AM, Manolo _ <[EMAIL PROTECTED]> wrote: > > Hi to all. > > I'd like to benchmark PG. I'd like to compare sorting performances (time > spent, #of disk accesses, # of run produced etc) of the present Replacement > Selection (external sorting) algorithm and of a refinement I'm going to > implement. > > I'm new on PG, I just had the idea of how to possibly get better that > algorithm and choosed to test it on PG since it's an open-source DBMS. > > I've been searching the web for a benchmark. I suppose TPC-H and Wisconsin > could fit, but had problems when trying to use them. > Any suggestion on a "good benchmark"? > Any tutorial on how to use them? > > Thanks for your time. > > Regards. > Manolo. > _ > Express yourself instantly with MSN Messenger! Download today it's FREE! > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > Well, there's pgbench. http://www.postgresql.org/docs/8.3/static/pgbench.html - Josh / eggyknap ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] libgcc double-free, backend won't die
Craig James wrote: > This is driving me crazy. I have some Postgres C function extensions > in a shared library. They've been working fine. I upgraded to Fedora > Core 6 and gcc4, and now every time psql(1) disconnects from the > server, the serverlog gets this message: > > *** glibc detected *** postgres: mydb mydb [local] idle: double free or > corruption! (!prev): 0x08bfcde8 Do you have any Perl or Python functions or stuff like that? > Postgres 8.1.4 Please upgrade to 8.1.10 and try again. If it still fails we will be much more interested in tracking it down. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" Maybe there's lots of data loss but the records of data loss are also lost. (Lincoln Yeoh) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Benchmarking PG
Hi Josh! Thanks for your reply. Actually I forgot to mention PGBench, sorry. But I also forgot to mention I'm looking for an "impartial"... I mean "outer" tool to test PG. Any suggestion, please? Regards. > Date: Tue, 11 Dec 2007 04:16:02 -0700 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: Re: [PERFORM] Benchmarking PG > CC: pgsql-performance@postgresql.org > > On Dec 11, 2007 4:06 AM, Manolo _ wrote: >> >> Hi to all. >> >> I'd like to benchmark PG. I'd like to compare sorting performances (time >> spent, #of disk accesses, # of run produced etc) of the present Replacement >> Selection (external sorting) algorithm and of a refinement I'm going to >> implement. >> >> I'm new on PG, I just had the idea of how to possibly get better that >> algorithm and choosed to test it on PG since it's an open-source DBMS. >> >> I've been searching the web for a benchmark. I suppose TPC-H and Wisconsin >> could fit, but had problems when trying to use them. >> Any suggestion on a "good benchmark"? >> Any tutorial on how to use them? >> >> Thanks for your time. >> >> Regards. >> Manolo. >> _ >> Express yourself instantly with MSN Messenger! Download today it's FREE! >> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ >> ---(end of broadcast)--- >> TIP 6: explain analyze is your friend >> > > Well, there's pgbench. http://www.postgresql.org/docs/8.3/static/pgbench.html > > - Josh / eggyknap _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] Is it spam or not?
The subject of the email: Confirmação de envio / Sending confirmation (captchaid:132432b16f55) > Date: Tue, 11 Dec 2007 09:40:37 -0200 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: Confirmação de envio / Sending confirmation (captchaid:132432b16f55) > > > A mensagem de email enviada para pgsql-performance@postgresql.org requer > confirmação para ser entregue. Por favor, responda este e-mail informando os > caracteres que você vê na imagem abaixo. > > The email message sent to pgsql-performance@postgresql.org requires a > confirmation to be delivered. Please, answer this email informing the > characters that you see in the image below. > > [http://by112w.bay112.mail.live.com/mail/SafeRedirect.aspx?hm__tg=http://64.4.26.249/att/GetAttachment.aspx&hm__qs=file%3d5f23f5aa-dc94-4b8d-979c-5baa326a8b17.gif%26ct%3daW1hZ2UvZ2lm%26name%3dY2FwdGNoYS5naWY_3d%26inline%3d1%26rfc%3d0%26empty%3dFalse%26imgsrc%3dcid%253acaptcha_img&oneredir=1&ip=10.1.106.210&d=d2813&mf=0] > > Não remova a próxima linha / Don't remove next line > captchakey:asbEJCQmIwQkowMTA1NDc _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/<><>
Re: [PERFORM] Benchmarking PG
Manolo _ wrote: I'd like to benchmark PG. I'd like to compare sorting performances (time spent, #of disk accesses, # of run produced etc) of the present Replacement Selection (external sorting) algorithm and of a refinement I'm going to implement. I'm new on PG, I just had the idea of how to possibly get better that algorithm and choosed to test it on PG since it's an open-source DBMS. I've been searching the web for a benchmark. I suppose TPC-H and Wisconsin could fit, but had problems when trying to use them. Any suggestion on a "good benchmark"? Any tutorial on how to use them? If you want to compare sorting performance in particular, running a big query that needs a big sort will be much more useful than a generic DBMS benchmark like TPC-H. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Is it spam or not?
RE: Confirmação de envio / Sending confirmation (captchaid:132432b18776) > Date: Tue, 11 Dec 2007 10:02:14 -0200 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: Confirmação de envio / Sending confirmation (captchaid:132432b18776) > > > A mensagem de email enviada para pgsql-performance@postgresql.org requer > confirmação para ser entregue. Por favor, responda este e-mail informando os > caracteres que você vê na imagem abaixo. > > The email message sent to pgsql-performance@postgresql.org requires a > confirmation to be delivered. Please, answer this email informing the > characters that you see in the image below. > > [http://by112w.bay112.mail.live.com/mail/SafeRedirect.aspx?hm__tg=http://64.4.26.249/att/GetAttachment.aspx&hm__qs=file%3dbba6c9e0-b23f-4cf8-bc4c-f54bdf0763c3.gif%26ct%3daW1hZ2UvZ2lm%26name%3dY2FwdGNoYS5naWY_3d%26inline%3d1%26rfc%3d0%26empty%3dFalse%26imgsrc%3dcid%253acaptcha_img&oneredir=1&ip=10.1.106.210&d=d2813&mf=0] > > Não remova a próxima linha / Don't remove next line > captchakey:asbEJCQndIaDUwMTU0NjU _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/<><>
[PERFORM] Slow Query
Hi,
This below query is taking more than 3 minutes to run, as you can see
from the explain plan it is pretty much using all the indexes still it
is slow, nested loops are taking too long. Is there anyway I can improve
this query performance ?
I am using postgres8.2.4. Here are the number of records in each table.
helix_fdc=# select relname,relpages,reltuples from pg_class where
relname in
('activity','listingactivity','activitytype','listing','address');
relname | relpages | reltuples
-+--+-
listing | 122215 | 8.56868e+06
listingactivity |51225 | 8.67308e+06
address | 244904 | 1.5182e+07
activity| 733896 | 6.74342e+07
activitytype|2 | 120
helix_fdc=# explain analyze
helix_fdc-# select count(listingact0_.listingactivityid) as col_0_0_,
date_trunc('day', activity3_.createdate) as col_1_0_,
helix_fdc-#activityty1_.activitytypeid as col_2_0_,
zipcode2_.zipcodeId as col_3_0_
helix_fdc-# from listing.listingactivity listingact0_, common.activity
activity3_, common.activitytype activityty1_,
helix_fdc-# postal.zipcode zipcode2_, common.activitytype
activityty5_, listing.listing listing7_,
helix_fdc-# listing.address listingadd8_
helix_fdc-# where listingact0_.fkactivityid=activity3_.activityId
helix_fdc-# and activity3_.fkactivitytypeid=activityty5_.activitytypeid
helix_fdc-# and listingact0_.fklistingid=listing7_.listingid
helix_fdc-# and listing7_.fkbestaddressid=listingadd8_.addressid
helix_fdc-# and (activityty5_.name in ( 'LISTING_ELEMENT_DETAIL',
'VIRTUALCARD_DISPLAY'))
helix_fdc-# and activity3_.fkactivitytypeid=activityty1_.activitytypeid
helix_fdc-# and listingadd8_.fkzipcodeid=zipcode2_.zipcodeId
helix_fdc-# and (listingadd8_.fkzipcodeid is not null)
helix_fdc-# and activity3_.createdate>='2007-12-11 00:00:00'
helix_fdc-# group by date_trunc('day', activity3_.createdate) ,
activityty1_.activitytypeid , zipcode2_.zipcodeId;
QUERY PLAN
---
HashAggregate (cost=3587.71..3588.31 rows=40 width=20) (actual
time=214022.231..214025.829 rows=925 loops=1)
-> Nested Loop (cost=3.52..3587.31 rows=40 width=20) (actual
time=464.743..213996.150 rows=3571 loops=1)
-> Nested Loop (cost=3.52..3574.01 rows=40 width=24) (actual
time=461.514..213891.251 rows=3571 loops=1)
-> Nested Loop (cost=3.52..3469.18 rows=41 width=24)
(actual time=421.683..208158.769 rows=3571 loops=1)
-> Nested Loop (cost=3.52..3299.05 rows=41
width=24) (actual time=321.155..91460.769 rows=3586 loops=1)
-> Nested Loop (cost=3.52..3147.50 rows=41
width=24) (actual time=188.756..821.893 rows=3586 loops=1)
-> Hash Join (cost=3.52..880.59
rows=321 width=20) (actual time=103.689..325.236 rows=4082 loops=1)
Hash Cond:
(activity3_.fkactivitytypeid = activityty5_.activitytypeid)
-> Index Scan using
idx_activity_createdate on activity activity3_ (cost=0.00..801.68
rows=19247 width=16) (actual time=103.495..244.987 rows=16918 loops=1)
Index Cond: (createdate >=
'2007-12-11 00:00:00'::timestamp without time zone)
-> Hash (cost=3.50..3.50 rows=2
width=4) (actual time=0.148..0.148 rows=2 loops=1)
-> Seq Scan on
activitytype activityty5_ (cost=0.00..3.50 rows=2 width=4) (actual
time=0.062..0.128 rows=2 loops=1)
Filter: (name = ANY
('{LISTING_ELEMENT_DETAIL,VIRTUALCARD_DISPLAY}'::text[]))
-> Index Scan using
idx_listingactivity_fkactivityid on listingactivity listingact0_
(cost=0.00..7.05 rows=1 width=12) (actual time=0.097..0.108 rows=1
loops=4082)
Index Cond:
(listingact0_.fkactivityid = activity3_.activityid)
-> Index Scan using pk_listing_listingid on
listing listing7_ (cost=0.00..3.68 rows=1 width=8) (actual
time=25.216..25.260 rows=1 loops=3586)
Index Cond: (listingact0_.fklistingid =
listing7_.listingid)
-> Index Scan using pk_address_addressid on
address listingadd8_ (cost=0.00..4.14 rows=1 width=8) (actual
time=32.508..32.527 rows=1 loops=3586)
Index Cond: (listing7_.fkbestaddressid =
listingadd8_.addressid)
Filter: (fkzipcodeid IS NOT NULL)
-> Index Scan using pk_zipcode_zipcodeid on
Re: [PERFORM] libgcc double-free, backend won't die
Alvaro Herrera wrote: Craig James wrote: This is driving me crazy. I have some Postgres C function extensions in a shared library. They've been working fine. I upgraded to Fedora Core 6 and gcc4, and now every time psql(1) disconnects from the server, the serverlog gets this message: *** glibc detected *** postgres: mydb mydb [local] idle: double free or corruption! (!prev): 0x08bfcde8 Do you have any Perl or Python functions or stuff like that? There is one Perl function, but it is never invoked during this test. I connect to Postgres, issue one "select myfunc()", and disconnect. Postgres 8.1.4 Please upgrade to 8.1.10 and try again. If it still fails we will be much more interested in tracking it down. Good idea, but alas, no difference. I get the same "double free or corruption!" mesage. I compiled 8.1.10 from source and installed, then rebuilt all of my code from scratch and reinstalled the shared object. Same message as before. Here is my guess -- and this is just a guess. My functions use a third-party library which, of necessity, uses malloc/free in the ordinary way. I suspect that there's a bug in the Postgres palloc() code that's walking over memory that regular malloc() allocates. The third-party library (OpenBabel) has been tested pretty thoroughly by me an others and has no memory corruption problems. All malloc's are freed properly. Does that seem like a possibility? I can't figure out how to use ordinary tools like valgrind with a Postgres backend process to track this down. Thanks, Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] libgcc double-free, backend won't die
Craig James <[EMAIL PROTECTED]> writes: > This is driving me crazy. I have some Postgres C function extensions in a > shared library. They've been working fine. I upgraded to Fedora Core 6 and > gcc4, and now every time psql(1) disconnects from the server, the serverlog > gets this message: >*** glibc detected *** postgres: mydb mydb [local] idle: double free or > corruption! (!prev): 0x08bfcde8 Have you tried attaching to one of these processes with gdb to see where it ends up? Have you checked to see if the processes are becoming multi-threaded? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] libgcc double-free, backend won't die
Tom Lane wrote: Craig James <[EMAIL PROTECTED]> writes: This is driving me crazy. I have some Postgres C function extensions in a shared library. They've been working fine. I upgraded to Fedora Core 6 and gcc4, and now every time psql(1) disconnects from the server, the serverlog gets this message: *** glibc detected *** postgres: mydb mydb [local] idle: double free or corruption! (!prev): 0x08bfcde8 Have you tried attaching to one of these processes with gdb to see where it ends up? Have you checked to see if the processes are becoming multi-threaded? regards, tom lane # ps -ef | grep postgres postgres 31362 1 0 06:53 ?00:00:00 /usr/local/pgsql/bin/postmaster -D /postgres/main postgres 31364 31362 0 06:53 ?00:00:00 postgres: writer process postgres 31365 31362 0 06:53 ?00:00:00 postgres: stats buffer process postgres 31366 31365 0 06:53 ?00:00:00 postgres: stats collector process postgres 31442 31362 0 06:54 ?00:00:00 postgres: craig_test craig_test [local] idle root 31518 31500 0 07:06 pts/600:00:00 grep postgres # gdb -p 31442 GNU gdb Red Hat Linux (6.5-15.fc6rh) Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. [snip - a bunch of symbol table stuff] 0x00110402 in __kernel_vsyscall () (gdb) bt #0 0x00110402 in __kernel_vsyscall () #1 0x0082fb8e in __lll_mutex_lock_wait () from /lib/libc.so.6 #2 0x007bfce8 in _L_lock_14096 () from /lib/libc.so.6 #3 0x007befa4 in free () from /lib/libc.so.6 #4 0x00744f93 in _dl_map_object_deps () from /lib/ld-linux.so.2 #5 0x0074989d in dl_open_worker () from /lib/ld-linux.so.2 #6 0x00745c36 in _dl_catch_error () from /lib/ld-linux.so.2 #7 0x00749222 in _dl_open () from /lib/ld-linux.so.2 #8 0x00858712 in do_dlopen () from /lib/libc.so.6 #9 0x00745c36 in _dl_catch_error () from /lib/ld-linux.so.2 #10 0x008588c5 in __libc_dlopen_mode () from /lib/libc.so.6 #11 0x00836139 in init () from /lib/libc.so.6 #12 0x008362d3 in backtrace () from /lib/libc.so.6 #13 0x007b3e11 in __libc_message () from /lib/libc.so.6 #14 0x007bba96 in _int_free () from /lib/libc.so.6 #15 0x007befb0 in free () from /lib/libc.so.6 #16 0x001f943a in DeleteByteCode (node=0x890ff4) at chains.cpp:477 #17 0x00780859 in exit () from /lib/libc.so.6 #18 0x081a6064 in proc_exit () #19 0x081b5b9d in PostgresMain () #20 0x0818e34b in ServerLoop () #21 0x0818f1de in PostmasterMain () #22 0x08152369 in main () (gdb) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] libgcc double-free, backend won't die
Craig James wrote: > Here is my guess -- and this is just a guess. My functions use a > third-party library which, of necessity, uses malloc/free in the > ordinary way. I suspect that there's a bug in the Postgres palloc() > code that's walking over memory that regular malloc() allocates. The > third-party library (OpenBabel) has been tested pretty thoroughly by > me an others and has no memory corruption problems. All malloc's are > freed properly. Does that seem like a possibility? Not really. palloc uses malloc underneath. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" "La vida es para el que se aventura" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] libgcc double-free, backend won't die
Alvaro Herrera wrote: Craig James wrote: Here is my guess -- and this is just a guess. My functions use a third-party library which, of necessity, uses malloc/free in the ordinary way. I suspect that there's a bug in the Postgres palloc() code that's walking over memory that regular malloc() allocates. The third-party library (OpenBabel) has been tested pretty thoroughly by me an others and has no memory corruption problems. All malloc's are freed properly. Does that seem like a possibility? Not really. palloc uses malloc underneath. But some Postgres code could be walking off the end of a malloc'ed block, even if palloc() is allocating and deallocating correctly. Which is why I was hoping to use valgrind to see what's going on. Thanks, Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] libgcc double-free, backend won't die
Craig James wrote: > Alvaro Herrera wrote: >> Craig James wrote: >> >>> Here is my guess -- and this is just a guess. My functions use a >>> third-party library which, of necessity, uses malloc/free in the >>> ordinary way. I suspect that there's a bug in the Postgres palloc() >>> code that's walking over memory that regular malloc() allocates. The >>> third-party library (OpenBabel) has been tested pretty thoroughly by >>> me an others and has no memory corruption problems. All malloc's are >>> freed properly. Does that seem like a possibility? >> >> Not really. palloc uses malloc underneath. > > But some Postgres code could be walking off the end of a malloc'ed > block, even if palloc() is allocating and deallocating correctly. > Which is why I was hoping to use valgrind to see what's going on. I very much doubt it. Since you've now shown that OpenBabel is multithreaded, then that's a much more likely cause. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "When the proper man does nothing (wu-wei), his thought is felt ten thousand miles." (Lao Tse) ---(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] libgcc double-free, backend won't die
Alvaro Herrera wrote: Craig James wrote: Alvaro Herrera wrote: Craig James wrote: Here is my guess -- and this is just a guess. My functions use a third-party library which, of necessity, uses malloc/free in the ordinary way. I suspect that there's a bug in the Postgres palloc() code that's walking over memory that regular malloc() allocates. The third-party library (OpenBabel) has been tested pretty thoroughly by me an others and has no memory corruption problems. All malloc's are freed properly. Does that seem like a possibility? Not really. palloc uses malloc underneath. But some Postgres code could be walking off the end of a malloc'ed block, even if palloc() is allocating and deallocating correctly. Which is why I was hoping to use valgrind to see what's going on. I very much doubt it. Since you've now shown that OpenBabel is multithreaded, then that's a much more likely cause. Can you elaborate? Are multithreaded libraries not allowed to be linked to Postgres? Thanks, Craig ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] libgcc double-free, backend won't die
Craig James wrote: > Alvaro Herrera wrote: >> Craig James wrote: >>> Alvaro Herrera wrote: Craig James wrote: > Here is my guess -- and this is just a guess. My functions use a > third-party library which, of necessity, uses malloc/free in the > ordinary way. I suspect that there's a bug in the Postgres palloc() > code that's walking over memory that regular malloc() allocates. The > third-party library (OpenBabel) has been tested pretty thoroughly by > me an others and has no memory corruption problems. All malloc's are > freed properly. Does that seem like a possibility? Not really. palloc uses malloc underneath. >>> But some Postgres code could be walking off the end of a malloc'ed >>> block, even if palloc() is allocating and deallocating correctly. >>> Which is why I was hoping to use valgrind to see what's going on. >> >> I very much doubt it. Since you've now shown that OpenBabel is >> multithreaded, then that's a much more likely cause. > > Can you elaborate? Are multithreaded libraries not allowed to be > linked to Postgres? Absolutely not. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "La gente vulgar solo piensa en pasar el tiempo; el que tiene talento, en aprovecharlo" ---(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] libgcc double-free, backend won't die
Craig James <[EMAIL PROTECTED]> writes: > GNU gdb Red Hat Linux (6.5-15.fc6rh) > Copyright (C) 2006 Free Software Foundation, Inc. > GDB is free software, covered by the GNU General Public License, and you are > welcome to change it and/or distribute copies of it under certain conditions. > [snip - a bunch of symbol table stuff] Please show that stuff you snipped --- it might have some relevant information. The stack trace looks a bit like a threading problem... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] libgcc double-free, backend won't die
Alvaro Herrera wrote: ...Since you've now shown that OpenBabel is multithreaded, then that's a much more likely cause. Can you elaborate? Are multithreaded libraries not allowed to be linked to Postgres? Absolutely not. Ok, thanks, I'll work on recompiling OpenBabel without thread support. Since I'm not a Postgres developer, perhaps one of the maintainers could update the Postgres manual. In chapter 32.9.6, it says, "To be precise, a shared library needs to be created." This should be amended to say, "To be precise, a non-threaded, shared library needs to be created." Cheers, Craig ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] libgcc double-free, backend won't die
Tom Lane wrote: Craig James <[EMAIL PROTECTED]> writes: GNU gdb Red Hat Linux (6.5-15.fc6rh) Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. [snip - a bunch of symbol table stuff] Please show that stuff you snipped --- it might have some relevant information. The stack trace looks a bit like a threading problem... # gdb -p 31442 GNU gdb Red Hat Linux (6.5-15.fc6rh) Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i386-redhat-linux-gnu". Attaching to process 31442 Reading symbols from /usr/local/pgsql/bin/postgres...(no debugging symbols found)...done. Using host libthread_db library "/lib/libthread_db.so.1". Reading symbols from /usr/lib/libz.so.1...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libz.so.1 Reading symbols from /usr/lib/libreadline.so.5...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libreadline.so.5 Reading symbols from /lib/libtermcap.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/libtermcap.so.2 Reading symbols from /lib/libcrypt.so.1... (no debugging symbols found)...done. Loaded symbols for /lib/libcrypt.so.1 Reading symbols from /lib/libresolv.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/libresolv.so.2 Reading symbols from /lib/libnsl.so.1...(no debugging symbols found)...done. Loaded symbols for /lib/libnsl.so.1 Reading symbols from /lib/libdl.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/libdl.so.2 Reading symbols from /lib/libm.so.6... (no debugging symbols found)...done. Loaded symbols for /lib/libm.so.6 Reading symbols from /lib/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libc.so.6 Reading symbols from /lib/ld-linux.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/ld-linux.so.2 Reading symbols from /lib/libnss_files.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/libnss_files.so.2 Reading symbols from /usr/local/pgsql/lib/libchmoogle.so...done. Loaded symbols for /usr/local/pgsql/lib/libchmoogle.so Reading symbols from /lib/libgcc_s.so.1...done. Loaded symbols for /lib/libgcc_s.so.1 Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/jaguarformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/jaguarformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/libopenbabel.so.2...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/libopenbabel.so.2 Reading symbols from /usr/lib/libstdc++.so.6...done. Loaded symbols for /usr/lib/libstdc++.so.6 Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/fastaformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/fastaformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/cansmilesformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/cansmilesformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/APIInterface.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/APIInterface.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/mmodformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/mmodformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/molreportformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/molreportformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/fhformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/fhformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/chemkinformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/chemkinformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/mmcifformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/mmcifformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/thermoformat.so...done. Loaded symbols for /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/thermoformat.so Reading symbols from /usr/local/openbabel-inst/openbabel-dev-2-1-x/lib/openbabel/carformat.so...done. Loaded sy
Re: [PERFORM] libgcc double-free, backend won't die
On Tue, Dec 11, 2007 at 07:50:17AM -0800, Craig James wrote: > Alvaro Herrera wrote: > >>>...Since you've now shown that OpenBabel is > >>>multithreaded, then that's a much more likely cause. > >>Can you elaborate? Are multithreaded libraries not allowed to be > >>linked to Postgres? > > > >Absolutely not. > > Ok, thanks, I'll work on recompiling OpenBabel without thread support. > > Since I'm not a Postgres developer, perhaps one of the maintainers could > update the Postgres manual. In chapter 32.9.6, it says, > > "To be precise, a shared library needs to be created." > > This should be amended to say, > > "To be precise, a non-threaded, shared library needs to be created." > Just before someone goes ahead and writes it (which is probably a good idea in general), don't write it just like taht - because it's platform dependent. On win32, you can certainly stick a threaded library to it - which is good, because most (if not all) win32 libs are threaded... Now, if they actually *use* threads explicitly things might break (but most likely not from that specifically), but you can link with them without the problem. I'm sure there are other platforms with similar situations. //Magnus ---(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] libgcc double-free, backend won't die
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Craig James wrote: >> Can you elaborate? Are multithreaded libraries not allowed to be >> linked to Postgres? > Absolutely not. The problem is that you get into library-interaction bugs like the one discussed here: http://archives.postgresql.org/pgsql-general/2007-11/msg00580.php http://archives.postgresql.org/pgsql-general/2007-11/msg00610.php I suspect what you're seeing is the exact same problem on a different glibc internal mutex: the mutex is left uninitialized on the first trip through the code because the process is not multithreaded, and then after OpenBabel gets loaded the process becomes multithreaded, and then it starts trying to use the mutex :-(. Since the glibc boys considered the other problem to be their bug, they'd probably be interested in fixing this one too. Unfortunately, you picked a Fedora version that reached EOL last week. Update to FC7 or FC8, and if you still see the problem, file a bugzilla entry against glibc. But having said all that, that still only addresses the question of why the process hangs up during exit(). Why the double-free report is being made at all is less clear, but I kinda think that unexpected multithread behavior may be at bottom there too. regards, tom lane ---(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] libgcc double-free, backend won't die
Craig James <[EMAIL PROTECTED]> writes: >> Please show that stuff you snipped --- it might have some relevant >> information. The stack trace looks a bit like a threading problem... > Using host libthread_db library "/lib/libthread_db.so.1". That's pretty suspicious, but not quite a smoking gun. Does "info threads" report more than 1 thread? > Reading symbols from /usr/lib/libstdc++.so.6...done. > Loaded symbols for /usr/lib/libstdc++.so.6 Hmm, I wonder whether *this* is the problem, rather than OpenBabel per se. Trying to use C++ inside the PG backend is another minefield of things that don't work. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] libgcc double-free, backend won't die
Magnus Hagander <[EMAIL PROTECTED]> writes: > On Tue, Dec 11, 2007 at 07:50:17AM -0800, Craig James wrote: >> Since I'm not a Postgres developer, perhaps one of the maintainers could >> update the Postgres manual. In chapter 32.9.6, it says, >> >> "To be precise, a shared library needs to be created." >> >> This should be amended to say, >> >> "To be precise, a non-threaded, shared library needs to be created." > Just before someone goes ahead and writes it (which is probably a good idea > in general), don't write it just like taht - because it's platform > dependent. I can find no such text in our documentation at all, nor any reference to OpenBabel. I think Craig must be looking at someone else's documentation. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] libgcc double-free, backend won't die
Tom Lane wrote: Magnus Hagander <[EMAIL PROTECTED]> writes: On Tue, Dec 11, 2007 at 07:50:17AM -0800, Craig James wrote: Since I'm not a Postgres developer, perhaps one of the maintainers could update the Postgres manual. In chapter 32.9.6, it says, "To be precise, a shared library needs to be created." This should be amended to say, "To be precise, a non-threaded, shared library needs to be created." Just before someone goes ahead and writes it (which is probably a good idea in general), don't write it just like taht - because it's platform dependent. I can find no such text in our documentation at all, nor any reference to OpenBabel. I think Craig must be looking at someone else's documentation. http://www.postgresql.org/docs/8.1/static/xfunc-c.html#DFUNChttp://www.postgresql.org/docs/8.1/static/xfunc-c.html#DFUNC Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] libgcc double-free, backend won't die
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 11 Dec 2007 11:25:08 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Magnus Hagander <[EMAIL PROTECTED]> writes: > > On Tue, Dec 11, 2007 at 07:50:17AM -0800, Craig James wrote: > >> Since I'm not a Postgres developer, perhaps one of the maintainers > >> could update the Postgres manual. In chapter 32.9.6, it says, > >> > >> "To be precise, a shared library needs to be created." > >> > >> This should be amended to say, > >> > >> "To be precise, a non-threaded, shared library needs to be > >> created." > > > Just before someone goes ahead and writes it (which is probably a > > good idea in general), don't write it just like taht - because it's > > platform dependent. > > I can find no such text in our documentation at all, nor any reference > to OpenBabel. I think Craig must be looking at someone else's > documentation. It's actually 33.9.6 and it is in: http://www.postgresql.org/docs/8.2/static/xfunc-c.html#DFUNC He is looking directly at our documentation :) Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHXrxJATb/zqfZUUQRAmSTAJwO0kdDovLB7kFGaPL9OPna3rm8ZwCfVaNo XKtTfT7He9rNEvMBs5e+O94= =qmOr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] libgcc double-free, backend won't die
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: >> I can find no such text in our documentation at all, nor any reference >> to OpenBabel. I think Craig must be looking at someone else's >> documentation. > It's actually 33.9.6 and it is in: > http://www.postgresql.org/docs/8.2/static/xfunc-c.html#DFUNC [ shrug... ] That documentation is not intended to address how to configure OpenBabel. It's talking about setting up linker commands, and "threaded" is not a relevant concept at that level. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] libgcc double-free, backend won't die
"Magnus Hagander" <[EMAIL PROTECTED]> writes: > On Tue, Dec 11, 2007 at 07:50:17AM -0800, Craig James wrote: > >> This should be amended to say, >> >> "To be precise, a non-threaded, shared library needs to be created." > > Just before someone goes ahead and writes it (which is probably a good idea > in general), don't write it just like taht - because it's platform > dependent. On win32, you can certainly stick a threaded library to it - > which is good, because most (if not all) win32 libs are threaded... Now, if > they actually *use* threads explicitly things might break (but most likely > not from that specifically), but you can link with them without the > problem. I'm sure there are other platforms with similar situations. Even on Unix there's nothing theoretically wrong with loading a shared library which uses threads. It's just that there are a whole lot of practical problems which can crop up. 1) No Postgres function is guaranteed to be thread-safe so you better protect against concurrent calls to Postgres API functions. Also Postgres functions use longjmp which can restore the stack pointer to a value which may have been set earlier, possibly by another thread which wouldn't work. So you're pretty much restricted to calling Postgres API functions from the main stack which means from the original thread Postgres loaded you with. Then there's 2) Some OSes have bugs (notably glibc for a specific narrow set of versions) and don't expect to have standard library functions called before pthread_init() then called again after pthread_init(). If they expect the system to be either "threaded" or "not threaded" then they may be surprised to see that state change. That just means you have to use a non-buggy version of your OS. Unfortunately tracking down bugs in your OS to figure out what's causing them and whether it's a particular known bug can be kind of tricky. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] database tuning
you know what you lot have left my original question this server is a temporary piece of shit my original question is what are the overheads for postgres but obviously no one knows or no one knows where a webpage containing this information is -_- overhead information i would to know is row overheads column overheads and header overheads for blocks and anything else i have missed trust me postgres and a Mac don't like working together you have no idea the amount of problems we have incurred with php trying to talk to postgres on a Mac out biggest problem is Mac tecs are incompetent and we cannot get any support for the server I know postgres connects fine just we cannot get it working on the Mac so I love your guys ideas but they don't work that's why I have had to take another approach if we were not using a Mac we would have none of the problems we have with connection issues such as php seems to want to take up 20 db connections at a time but I think we fixed that problem overall our problem is the Mac and we cannot get it support neither I nor the web app developer are Mac savvy hell as far as we have seen no Mac tec is Mac savvy either we cannot get parts of postgres to run on a Mac either such as pgagent which is necessary for us but we cannot seem to find a daemon that works on a Mac I have a list of problems a mile long and none of them are postgres it is the Mac so minus all that as the Mac is only a temporary solution can anyone just answer the original question for me if not and I mean no offence to anyone but I really don't care as I am going to re do it all later down the track as I have said your ideas sound good just not Mac oriented nor are they to do with my original question I have never had trouble finding overhead information on any other DBMS I have used this is the first time I have had to ask for it and since this DBMS is open source I have to ask a community rather than a company if anyone is wondering why I don't switch now money and time are not on my side and for those who wonder why don't I leave this job is big time just starts off small time but the potential of this job is very nice and as they say if you want something good you have to work hard for it I am not a fan of taking the easy way out as it brings no benefits for those who want to know more I cannot tell you as I am under a confidentiality agreement regards Kelvan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] database tuning
kelvan wrote: I wonder where did all the punctuation symbols on your keyboard went. Your email is amazingly hard to read. > overhead information i would to know is row overheads column overheads and > header overheads for blocks and anything else i have missed As for storage overhead, see here: http://www.postgresql.org/docs/8.3/static/storage-page-layout.html -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] database tuning
http://www.postgresql.org/docs/8.1/static/storage.html On Dec 11, 2007 5:18 PM, kelvan <[EMAIL PROTECTED]> wrote: > you know what you lot have left my original question this server is a > temporary piece of shit > > my original question is what are the overheads for postgres but obviously no > one knows or no one knows where a webpage containing this information is -_- So, have you looked in the docs? I go here: http://www.postgresql.org/docs/8.1/static/index.html see this down the page a bit: http://www.postgresql.org/docs/8.1/static/storage.html which takes me here: http://www.postgresql.org/docs/8.1/static/storage-page-layout.html And it seems to have that information in it. Again. You can look at the source, or find out experimentally by building tables and checking their size. Some of this is an inexact science because different architechtures have different alignment requirements. ---(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] database tuning
On Dec 11, 2007, at 5:18 PM, kelvan wrote: you know what you lot have left my original question this server is a temporary piece of shit my original question is what are the overheads for postgres but obviously no one knows or no one knows where a webpage containing this information is -_- overhead information i would to know is row overheads column overheads and header overheads for blocks and anything else i have missed trust me postgres and a Mac don't like working together you have no idea the amount of problems we have incurred with php trying to talk to postgres on a Mac out biggest problem is Mac tecs are incompetent and we cannot get any support for the server I know postgres connects fine just we cannot get it working on the Mac so I love your guys ideas but they don't work that's why I have had to take another approach if we were not using a Mac we would have none of the problems we have with connection issues such as php seems to want to take up 20 db connections at a time but I think we fixed that problem overall our problem is the Mac and we cannot get it support neither I nor the web app developer are Mac savvy hell as far as we have seen no Mac tec is Mac savvy either we cannot get parts of postgres to run on a Mac either such as pgagent which is necessary for us but we cannot seem to find a daemon that works on a Mac I have a list of problems a mile long and none of them are postgres it is the Mac so minus all that as the Mac is only a temporary solution can anyone just answer the original question for me if not and I mean no offence to anyone but I really don't care as I am going to re do it all later down the track as I have said your ideas sound good just not Mac oriented nor are they to do with my original question I have never had trouble finding overhead information on any other DBMS I have used this is the first time I have had to ask for it and since this DBMS is open source I have to ask a community rather than a company if anyone is wondering why I don't switch now money and time are not on my side and for those who wonder why don't I leave this job is big time just starts off small time but the potential of this job is very nice and as they say if you want something good you have to work hard for it I am not a fan of taking the easy way out as it brings no benefits for those who want to know more I cannot tell you as I am under a confidentiality agreement Kelvan, proper capitalization and punctuation are virtues when trying to communicate extensively via text mediums. I, for one, read the first couple and last couple of lines of this message after gruelingly reading your last message and I wouldn't be surprised if others with more experience and better answers at the ready simply ignored both as that much text is extremely difficult to follow in the absence those aforementioned virtues. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] database tuning
kelvan wrote: you know what you lot have left my original question this server is a temporary piece of shit my original question is what are the overheads for postgres but obviously no one knows or no one knows where a webpage containing this information is -_- overhead information i would to know is row overheads column overheads and header overheads for blocks and anything else i have missed You said you had most of that in your original post: > I have gathered some relevant information form the documentation such > as all > the data type sizes and the RM block information but I don't have any > information on INDEX blocks or other general overheads The index details are in the source, as I said in my first reply. It's just that nobody here thinks that'll help you much. neither I nor the web app developer are Mac savvy hell as far as we have seen no Mac tec is Mac savvy either So what on earth are you going to do with the index overhead figures? Without accurate information on usage patterns, fill-factor, vacuuming frequency etc. they aren't going to tell you anything. Even if you could get an accurate figure for database size with less effort than just generating test data, what would your next step be? as I have said your ideas sound good just not Mac oriented The only idea I've seen mentioned is connection-pooling. I'm not sure why that wouldn't work on a Mac. Other comments were warning that 30,000 connections weren't do-able, that de-normalising made poor use of your limited disk/memory and pointing out solutions other people use. Oh, and me asking for any info from your testing. > nor are they to do with my original question I have never had trouble finding overhead information on any other DBMS I have used this is the first time I have had to ask for it and since this DBMS is open source I have to ask a community rather than a company Again, since you said you had all the stuff from the manuals, the rest is in the source. That's what the source is there for. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] TB-sized databases
On Fri, 2007-12-07 at 12:45 -0500, Robert Treat wrote: > On Thursday 06 December 2007 04:38, Simon Riggs wrote: > > > I think you're completly overlooking the effect of disk latency has on > > > query times. We run queries all the time that can vary from 4 hours to > > > 12 hours in time based solely on the amount of concurrent load on the > > > system, even though they always plan with the same cost. > > > > Not at all. If we had statement_cost_limit then it would be applied > > after planning and before execution begins. The limit would be based > > upon the planner's estimate, not the likely actual execution time. > > > > This is nice, but it doesnt prevent "slow queries" reliably (which seemed to > be in the original complaints), since query time cannot be directly traced > back to statement cost. Hmm, well it can be directly traced, just not with the accuracy you desire. We can improve the accuracy, but then we would need to run the query first in order to find out it was killing us. > > So yes a query may vary in execution time by a large factor as you > > suggest, and it would be difficult to set the proposed parameter > > accurately. However, the same is also true of statement_timeout, which > > we currently support, so I don't see this point as an blocker. > > > > Which leaves us at the burning question: Would you use such a facility, > > or would the difficulty in setting it exactly prevent you from using it > > for real? > > I'm not sure. My personal instincts are that the solution is too fuzzy for me > to rely on, and if it isnt reliable, it's not a good solution. If you look at > all of the things people seem to think this will solve, I think I can raise > an alternative option that would be a more definitive solution: > > "prevent queries from taking longer than x" -> statement_timeout. > > "prevent planner from switching to bad plan" -> hint system > > "prevent query from consuming too many resources" -> true resource > restrictions at the database level I like and agree with your list, as an overview. I differ slightly on specifics. > I'm not so much against the idea of a statement cost limit, but I think we > need to realize that it does not really solve as many problems as people > think, in cases where it will help it often will do so poorly, and that there > are probably better solutions available to those problems. Of course if you > back me into a corner I'll agree a poor solution is better than no solution, > so... statement_cost_limit isn't a panacea for all performance ills, its just one weapon in the armoury. I'm caught somewhat in that whatever I propose as a concrete next step, somebody says I should have picked another. Oh well. On specific points: With hints I prefer a declarative approach, will discuss later in release cycle. The true resource restrictions sound good, but its still magic numbers. How many I/Os are allowed before you kill the query? How much CPU? Those are still going to be guessed at. How do we tell the difference between a random I/O and a sequential I/O - there's no difference as far as Postgres is concerned in the buffer manager, but it can cause a huge performance difference. Whether you use real resource limits or statement cost limits you still need to work out the size of your table and then guess at appropriate limits. Every other system I've seen uses resource limits, but the big problem is that they are applied after something has been running for a long time. It's kinda like saying I'll treat the gangrene when it reaches my knee. I prefer to avoid the problem before it starts to hurt at all, so I advocate learning the lessons from other systems, not simply follow them. But having said that, I'm not against having them; its up to the administrator how they want to manage their database, not me. What resource limit parameters would you choose? (temp disk space etc..) -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] database tuning
Ok thx I have got it thx to David and Scott for the links I now know why I couldn't find them as I was looking for blocks rather than page damn synonyms and to Eric thx for the criticism but yea I failed English so I know my punctuation is bad unless I concentrate and I am to busy to do that so for you Eric here is a full stop. (that was a joke not a shot at you I understand what you are saying but yeah) I have also learnt and also Richard pointed out just not in so many words the difference in support from a open source community compared to a non open source company is that the people who give support in open source are opinionated rather than concise meaning they will give you their opinion rather than answering the question Regards Kelvan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] database tuning
On Wed, 12 Dec 2007, kelvan wrote: my original question is what are the overheads for postgres but obviously no one knows or no one knows where a webpage containing this information is -_- In addition to the documentation links people have already suggested, I'd also suggest http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html which gives some helpful suggestions on measuring the actual size of data you've got in the database already. It's possible to make a mistake when trying to compute overhead yourself; loading a subset of the data and measuring the size is much less prone to error. if we were not using a Mac we would have none of the problems we have with connection issues such as php seems to want to take up 20 db connections at a time I can't imagine why the connection pooling links I suggested before wouldn't work perfectly fine on a Mac. You're correct to first nail down why PHP is connecting more than you expect, but eventually I suspect you'll need to wander toward pooling. neither I nor the web app developer are Mac savvy hell as far as we have seen no Mac tec is Mac savvy either :) we cannot get parts of postgres to run on a Mac either such as pgagent which is necessary for us but we cannot seem to find a daemon that works on a Mac You might want to give some specifics and ask about this on the pgAdmin mailing list: http://www.pgadmin.org/support/list.php OS X support is relatively recent for pgAdmin and I see some other recent fixes for specific issues on that platform. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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] database tuning
On Wed, Dec 12, 2007 at 12:27:39PM +1200, kelvan wrote: I have also learnt and also Richard pointed out just not in so many words the difference in support from a open source community compared to a non open source company is that the people who give support in open source are opinionated rather than concise meaning they will give you their opinion rather than answering the question No, the difference is that the paid support *has to* give *an* answer. It doesn't have to be a useful answer, it just has to fulfil their obligation. They will give you whatever answer you ask for to get you off the phone as quickly as possible because it makes their on-phone numbers better than arguing about it and trying to give a useful answer. Free support will tell you that what you're asking for is silly, because they don't have to give you the answer you asked for in order to get you off the phone. You seem to have already made up your mind about a whole number of things, making this whole discussion more amusing than erudite. Mike Stone ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] database tuning
Michael Stone wrote: On Wed, Dec 12, 2007 at 12:27:39PM +1200, kelvan wrote: I have also learnt and also Richard pointed out just not in so many words the difference in support from a open source community compared to a non open source company is that the people who give support in open source are opinionated rather than concise meaning they will give you their opinion rather than answering the question No, the difference is that the paid support *has to* give *an* answer. Good lord what a bizarre paragraph. Michael is right, paid support *has to* give *an* answer and I guarantee you that the answer will be opinionated. There are very little right and wrong in the world of software. It is mostly one pedantic opinion versus another pedantic opinion. I get paid everyday to offer my opinion :) Joshua D. Drake ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] Need help on parameters and their values to tune the postgresql database
Hi, I am doing a performance benchmarking test by using benchmarkSQL tool on postgresql 8.2.4.I need to tune the parameters to achieve an optimal performance of the postgresql database. I have installed postgresql 8.2.4 on RHEL AS4. It is a DELL Optiplex GX620 PC with 4GB RAM. Please suggest me which parameters I need to tune and what would be the possible values for the parameters. Appreciate if I would get a quick response. Thanks & Regards, Simanchala
