[PERFORM] Second SpecJAppserver2004 with PostgreSQL
http://blogs.sun.com/jkshah/entry/specjappserver2004_with_glassfish_v2_and This time with 33% less App Server hardware but same setup for PostgreSQL 8.2.4 with 4.5% better score .. There has been reduction in CPU utilization by postgresql with the new app server which means there is potential to do more JOPS. But looks like Simon Rigg added another project for us to look into maxalign side effect with Solaris on SPARC before doing more benchmarks now. Cheers, Jignesh ---(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] User concurrency thresholding: where do I look?
On Mon, 2007-07-23 at 14:19 -0400, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > currPos and markPos are defined as BTScanPosData, which is an array of > > BTScanPosItems. That makes BTScanOpaqueData up to 8232 bytes, which > > seems wasteful since markPos is only ever used during merge joins. Most > > of that space isn't even used during merge joins either, we just do that > > to slightly optimise the speed of the restore during merge joins. > > Ah. I was seeing it as 6600 bytes on HPPA and 6608 on x86_64, but > I forgot that both of those architectures have MAXALIGN = 8. On a > MAXALIGN = 4 machine, MaxIndexTuplesPerPage will be significantly > larger, leading to larger BTScanPosData. > > Not sure it's worth fooling with, given that these days almost everyone > who's seriously concerned about performance is probably using 64bit > hardware. One less malloc cycle per indexscan is never going to be a > measurable savings anyway... Oh sure, I was thinking to avoid Solaris' mutex by avoiding malloc() completely. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] User concurrency thresholding: where do I look?
"Simon Riggs" <[EMAIL PROTECTED]> writes: > currPos and markPos are defined as BTScanPosData, which is an array of > BTScanPosItems. That makes BTScanOpaqueData up to 8232 bytes, which > seems wasteful since markPos is only ever used during merge joins. Most > of that space isn't even used during merge joins either, we just do that > to slightly optimise the speed of the restore during merge joins. Ah. I was seeing it as 6600 bytes on HPPA and 6608 on x86_64, but I forgot that both of those architectures have MAXALIGN = 8. On a MAXALIGN = 4 machine, MaxIndexTuplesPerPage will be significantly larger, leading to larger BTScanPosData. Not sure it's worth fooling with, given that these days almost everyone who's seriously concerned about performance is probably using 64bit hardware. One less malloc cycle per indexscan is never going to be a measurable savings anyway... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] User concurrency thresholding: where do I look?
On Mon, 2007-07-23 at 12:35 -0400, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > Well, I discover there is an allocation of 8232 (inflation...) made once > > per statement by a memory context called... ExecutorState. Still not > > sure exactly which allocation this is, but its definitely once per > > statement on pgbench, which should narrow it down. Plan, query etc? > > Are you working with stock sources? The only allocation exceeding 1K > that I can see during pgbench is BTScanOpaqueData, which is 6600 bytes. > (Checked by setting a conditional breakpoint on AllocSetAlloc.) The > path that allocates a single-chunk block is never taken at all. I do have the bitmap patch currently applied, but it doesn't touch that part of the code. (gdb) p size $1 = 8232 (gdb) p sizeof(int) $2 = 4 (gdb) p sizeof(BTScanPosData) $3 = 4104 Since my notes say I got 8228 last year, seems reasonable. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] User concurrency thresholding: where do I look?
On Mon, 2007-07-23 at 16:48 +0100, Simon Riggs wrote: > On Mon, 2007-07-23 at 10:54 -0400, Tom Lane wrote: > > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > I looked at this last May and my notes say "ExecutorState". I guess that > > > was wrong, but my analysis showed there was a single malloc of 8228 > > > bytes happening once per query during my tests. > > > > Well, if you can track down where it's coming from, we could certainly > > hack the containing context's parameters. But EState's not it. > > Well, I discover there is an allocation of 8232 (inflation...) made once > per statement by a memory context called... ExecutorState. Still not > sure exactly which allocation this is, but its definitely once per > statement on pgbench, which should narrow it down. Plan, query etc? > > I don't see a way to hack the allocation, since the max chunk size is > 8K. It is the allocation of BTScanOpaqueData called from btrescan() in nbtree.c currPos and markPos are defined as BTScanPosData, which is an array of BTScanPosItems. That makes BTScanOpaqueData up to 8232 bytes, which seems wasteful since markPos is only ever used during merge joins. Most of that space isn't even used during merge joins either, we just do that to slightly optimise the speed of the restore during merge joins. Seems like we should allocate the memory when we do the first mark. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] disable archiving
Alvaro, thanks for the quick reply. Just to make sure: I do not set this command. This results in the database cycling through a finite set (hopefully small) set of WAL files. So old WAL files are reused once the engine thinks this can be done. Thanks Paul On 23-jul-2007, at 19:34, Alvaro Herrera wrote: Paul van den Bogaard wrote: the manual somewhere states "... if archiving is enabled..." To me this implies that archiving can be disabled. However I cannot find the parameter to use to get this result. Archiving is disabled by not setting archive_command. -- Alvaro Herrerahttp:// www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. - Paul van den Bogaard [EMAIL PROTECTED] CIE -- Collaboration and ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] disable archiving
Perhaps you should've read the configuration-manual-page more carefully. ;) Besides, WAL-archiving is turned off by default, so if you see them being archived you actually enabled it earlier The "archive_command" is empty by default: "If this is an empty string (the default), WAL archiving is disabled." http://www.postgresql.org/docs/8.2/interactive/runtime-config-wal.html Best regards, Arjen On 23-7-2007 19:24 Paul van den Bogaard wrote: the manual somewhere states "... if archiving is enabled..." To me this implies that archiving can be disabled. However I cannot find the parameter to use to get this result. Or should I enable archiving and use a backup script like #!/usr/bin/bash exit 0 Would appreciate a hint. And yes I know I put my database in danger etc. This is for some benchmarks where I do not want the overhead of archiving. Jus a file system that will not fill with zillions of these 16MB WAL files ;^) Thanks Paul. ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] disable archiving
am Mon, dem 23.07.2007, um 19:24:48 +0200 mailte Paul van den Bogaard folgendes: > the manual somewhere states "... if archiving is enabled..." To me Please don't hijack other threads... (don't edit a mail-subject to create a new thread. Create a NEW mail!) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] disable archiving
Paul van den Bogaard wrote: > the manual somewhere states "... if archiving is enabled..." To me this > implies that archiving can be disabled. However I cannot find the parameter > to use to get this result. Archiving is disabled by not setting archive_command. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] disable archiving
the manual somewhere states "... if archiving is enabled..." To me this implies that archiving can be disabled. However I cannot find the parameter to use to get this result. Or should I enable archiving and use a backup script like #!/usr/bin/bash exit 0 Would appreciate a hint. And yes I know I put my database in danger etc. This is for some benchmarks where I do not want the overhead of archiving. Jus a file system that will not fill with zillions of these 16MB WAL files ;^) Thanks Paul. ---(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] multicolumn index column order
On Jul 23, 7:00 pm, [EMAIL PROTECTED] (Tom Lane) wrote: > valgog <[EMAIL PROTECTED]> writes: > > how to build an multicolumn index with one column order ASCENDING and > > another column order DESCENDING? > > Use 8.3 ;-) > > In existing releases you could fake it with a custom reverse-sorting > operator class, but it's a pain in the neck to create one. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > >http://www.postgresql.org/about/donate ok, thanks for a rapid answer, can live with the ((-flag), text_column) functional multicolumn index by now. Waiting for 8.3 :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] multicolumn index column order
valgog <[EMAIL PROTECTED]> writes: > how to build an multicolumn index with one column order ASCENDING and > another column order DESCENDING? Use 8.3 ;-) In existing releases you could fake it with a custom reverse-sorting operator class, but it's a pain in the neck to create one. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] multicolumn index column order
Hello all, how to build an multicolumn index with one column order ASCENDING and another column order DESCENDING? The use case that I have is that I use 2 column index where the first column is kind of flag and the second column is an actual ordering column. The flag should be always ordered DESCENDING, but the second column is ordered DESCENDING when it is a numeric column, and ASCENDING when it is a text column. CREATE TABLE storage (id int, flag int, numeric_data int, text_data text); SELECT * FROM storage ORDER BY flag DESC, numeric_column DESC LIMIT 20 OFFSET 0; SELECT * FROM storage ORDER BY flag DESC, text_column ASC LIMIT 20 OFFSET 0; Definitely the multicolumn index on (flag, numeric_column) is being used. But how to create an index on (flag, text_column DESC)? I will try to index by ((-flag), text_column) and sort by (-flag) ASC, but it, to say the truth, does not really look like a nice solution. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] User concurrency thresholding: where do I look?
"Simon Riggs" <[EMAIL PROTECTED]> writes: > Well, I discover there is an allocation of 8232 (inflation...) made once > per statement by a memory context called... ExecutorState. Still not > sure exactly which allocation this is, but its definitely once per > statement on pgbench, which should narrow it down. Plan, query etc? Are you working with stock sources? The only allocation exceeding 1K that I can see during pgbench is BTScanOpaqueData, which is 6600 bytes. (Checked by setting a conditional breakpoint on AllocSetAlloc.) The path that allocates a single-chunk block is never taken at all. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] User concurrency thresholding: where do I look?
On Mon, 2007-07-23 at 10:54 -0400, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > I looked at this last May and my notes say "ExecutorState". I guess that > > was wrong, but my analysis showed there was a single malloc of 8228 > > bytes happening once per query during my tests. > > Well, if you can track down where it's coming from, we could certainly > hack the containing context's parameters. But EState's not it. Well, I discover there is an allocation of 8232 (inflation...) made once per statement by a memory context called... ExecutorState. Still not sure exactly which allocation this is, but its definitely once per statement on pgbench, which should narrow it down. Plan, query etc? I don't see a way to hack the allocation, since the max chunk size is 8K. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] User concurrency thresholding: where do I look?
"Simon Riggs" <[EMAIL PROTECTED]> writes: > I looked at this last May and my notes say "ExecutorState". I guess that > was wrong, but my analysis showed there was a single malloc of 8228 > bytes happening once per query during my tests. Well, if you can track down where it's coming from, we could certainly hack the containing context's parameters. But EState's not it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] User concurrency thresholding: where do I look?
On Mon, 2007-07-23 at 10:11 -0400, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > EState is about 8300 bytes, > > What? > > (gdb) p sizeof(EState) > $1 = 112 > > This is on a 32-bit machine, but even on 64-bit it wouldn't be more than > double that. > > > Would it be worth a special case in the palloc system to avoid having to > > repeatedly issue external memory allocation calls? > > The appropriate hack would be to change the AllocSetContextCreate > initial-size parameter for the containing context. But I really have > no idea what you're on about. I looked at this last May and my notes say "ExecutorState". I guess that was wrong, but my analysis showed there was a single malloc of 8228 bytes happening once per query during my tests. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] User concurrency thresholding: where do I look?
"Simon Riggs" <[EMAIL PROTECTED]> writes: > EState is about 8300 bytes, What? (gdb) p sizeof(EState) $1 = 112 This is on a 32-bit machine, but even on 64-bit it wouldn't be more than double that. > Would it be worth a special case in the palloc system to avoid having to > repeatedly issue external memory allocation calls? The appropriate hack would be to change the AllocSetContextCreate initial-size parameter for the containing context. But I really have no idea what you're on about. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] insert vs select into performance
On Wed, Jul 18, 2007 at 09:13:14PM +0200, Thomas Finneid wrote: Michael Stone wrote: I don't understand how the insert you described is table to table? SELECT INTO is table to table, so is INSERT INTO SELECT FROM. I could have sworn that at least one of the examples you gave didn't have any select. Doesn't really matter. Mike Stone ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] User concurrency thresholding: where do I look?
On Fri, 2007-07-20 at 16:57 -0400, Tom Lane wrote: > "Jignesh K. Shah" <[EMAIL PROTECTED]> writes: > > What its saying is that there are holds/waits in trying to get locks > > which are locked at Solaris user library levels called from the > > postgresql functions: > > For example both the following functions are hitting on the same mutex > > lock 0x10059e280 in Solaris Library call: > > postgres`AllocSetDelete+0x98 > > postgres`AllocSetAlloc+0x1c4 > > That's a perfect example of the sort of useless overhead that I was > complaining of just now in pgsql-patches. Having malloc/free use > an internal mutex is necessary in multi-threaded programs, but the > backend isn't multi-threaded. And yet, apparently you can't turn > that off in Solaris. > > (Fortunately, the palloc layer is probably insulating us from malloc's > performance enough that this isn't a huge deal. But it's annoying.) There is one thing that the palloc layer doesn't handle: EState. All other memory contexts have a very well chosen initial allocation that prevents mallocs during low-medium complexity OLTP workloads. EState is about 8300 bytes, so just above the large allocation limit. This means that every time we request an EState, i.e. at least once per statement we need to malloc() and then later free(). Would it be worth a special case in the palloc system to avoid having to repeatedly issue external memory allocation calls? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
