Re: [PERFORM] User concurrency thresholding: where do I look?
Tom Lane [EMAIL PROTECTED] writes: Josh Berkus [EMAIL PROTECTED] writes: That's an interesting thought. Let me check lock counts and see if this is possibly the case. AFAIK you'd get hard failures, not slowdowns, if you ran out of lock space entirely I assume you've checked the server logs and are sure that you aren't in fact getting errors. I could, for example, envision a situation where a fraction of the transactions are getting some error and those transactions are therefore not being counted against the txn/s result. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Simple query showing 270 hours of CPU time
Today, I looked at 'top' on my PG server and saw a pid that reported 270 hours of CPU time. Considering this is a very simple query, I was surprised to say the least. I was about to just kill the pid, but I figured I'd try and see exactly what it was stuck doing for so long. Here's the strace summary as run for a few second sample: % time seconds usecs/call callserrors syscall -- --- --- - - 97.250.671629 92 7272 semop 1.760.012171 40630 recvfrom 0.570.003960 6660 gettimeofday 0.360.002512 2890 sendto 0.050.000317 1032 lseek 0.010.49 148 select -- --- --- - - 100.000.690638 7532 total Here's the query: select id from eventkeywords where word = '3322' If I run the query manually, it completes in about 500ms, which is very reasonable. There are 408563 rows in this table. I just noticed there is no index on word ( there should be! ). Would this have caused the problem? This is 8.0.12 Linux sunrise 2.6.15-26-amd64-server #1 SMP Fri Sep 8 20:33:15 UTC 2006 x86_64 GNU/Linux Any idea what might have set it into this loop? -Dan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Simple query showing 270 hours of CPU time
Dan Harris [EMAIL PROTECTED] writes: Here's the strace summary as run for a few second sample: % time seconds usecs/call callserrors syscall -- --- --- - - 97.250.671629 92 7272 semop 1.760.012171 40630 recvfrom 0.570.003960 6660 gettimeofday 0.360.002512 2890 sendto 0.050.000317 1032 lseek 0.010.49 148 select -- --- --- - - 100.000.690638 7532 total Here's the query: select id from eventkeywords where word = '3322' How sure are you that (a) that's really what it's doing and (b) you are not observing multiple executions of the query? There are no recvfrom calls in the inner loops of the backend AFAIR, so this looks to me like the execution of 30 different queries. The number of semops is distressingly high, but that's a contention issue not an amount-of-runtime issue. I think you're looking at a backend that has simply executed one heckuva lot of queries on behalf of its client, and that inquiring into what the client thinks it's doing might be the first order of business. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Simple query showing 270 hours of CPU time
Today, I looked at 'top' on my PG server and saw a pid that reported 270 hours of CPU time. Considering this is a very simple query, I was surprised to say the least. I was about to just kill the pid, but I figured I'd try and see exactly what it was stuck doing for so long. If you are using connection pooling, or if your client keeps the connections for a long time, this backend could be very old... With PHP's persistent connections, for instance, backends restart when you restart the webserver, which isn't usually very often. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] User concurrency thresholding: where do I look?
On Thu, 19 Jul 2007, Josh Berkus wrote:
It's a TPCC-like workload, so heavy single-row updates, and the
updates/inserts are what's being measured.
There's so much going on with a TPC-C kind of workload. Has anyone ever
looked into quantifying scaling for more fundamental operations? There
are so many places a complicated workload could get caught up that
starting there is hard. I've found it's helpful to see the breaking
points for simpler operations, then compare how things change as each new
transaction element is introduced.
As an example, take a look at the MySQL SysBench tool:
http://sysbench.sourceforge.net/docs/
Specifically their oltp tests. Note how you can get a handle on how
simple selects scale, then simple inserts, then updates, and so on. The
only thing I've thought of they missed is testing a trivial operation that
doesn't even touch the buffer cache ('SELECT 1'?) that would let you
quantify just general connection scaling issues.
It seems to me that you could narrow the list of possible causes here much
more quickly if you had a good handle on the upper concurrency of
lower-level operations.
[Note: it's possible to run SysBench against a PG database, but the code
is very immature. Last time I tried there were plenty of crashes and
there seemed to be some transaction wrapping issues that caused deadlocks
with some tests.]
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] User concurrency thresholding: where do I look?
Greg, There's so much going on with a TPC-C kind of workload. Has anyone ever looked into quantifying scaling for more fundamental operations? There are so many places a complicated workload could get caught up that starting there is hard. I've found it's helpful to see the breaking points for simpler operations, then compare how things change as each new transaction element is introduced. ... eagerly awaiting Michael Doilson's PgUnitTest --Josh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Simple query showing 270 hours of CPU time
Tom Lane wrote: Dan Harris [EMAIL PROTECTED] writes: Here's the strace summary as run for a few second sample: % time seconds usecs/call callserrors syscall -- --- --- - - 97.250.671629 92 7272 semop 1.760.012171 40630 recvfrom 0.570.003960 6660 gettimeofday 0.360.002512 2890 sendto 0.050.000317 1032 lseek 0.010.49 148 select -- --- --- - - 100.000.690638 7532 total Here's the query: select id from eventkeywords where word = '3322' How sure are you that (a) that's really what it's doing and (b) you are not observing multiple executions of the query? There are no recvfrom calls in the inner loops of the backend AFAIR, so this looks to me like the execution of 30 different queries. The number of semops is distressingly high, but that's a contention issue not an amount-of-runtime issue. You were absolutely right. This is one connection that is doing a whole lot of ( slow ) queries. I jumped the gun on this and assumed it was a single query taking this long. Sorry to waste time and bandwidth. Since you mentioned the number of semops is distressingly high, does this indicate a tuning problem? The machine has 64GB of RAM and as far as I can tell about 63GB is all cache. I wonder if this is a clue to an undervalued memory-related setting somewhere? -Dan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] User concurrency thresholding: where do I look?
Awww Josh, I was just enjoying the chat on the picket fence! :-) Anyway the workload is mixed (reads,writes) with simple to medium queries. The workload is known to scale well. But inorder to provide substantial input I am still trying to eliminate things that can bottleneck. Currently I have eliminated CPU (plenty free) , RAM (memory is 48GB RAM in this server for a 32-bit postgresql instance), IO Storage (used the free ram to do /tmp database to eliminate IO) and am still trying to eliminate any network bottlenecks to say for sure we have a problem in PostgreSQL. But yes till that final thing is confirmed (network which can very well be the case) it could be a problem somewhere else. However the thing that worries me is more of the big drop instead of remaining constant out there.. Anyway more on this within a day or two once I add more network nics between the systems to eliminate network problems (even though stats dont show them as problems right now) and also reduce malloc lock penalties if any. As for other questions: max_locks_per_transactions is set to default (10 I believe) increasing it still seems to degrade overall throughput number. max_connections is set to 1500 for now till I get decent scaling till 1400-1500 users. There are no hard failures reported anywhere. Log min durations does show that queries are now slowing down and taking longer. OS is not swapping and also eliminated IO by putting the whole database on /tmp So while I finish adding more network connections between the two systems (need to get cards) do enjoy the following URL :-) http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-20070703-00073.html Of course all postgresql.conf still remains from the old test so no flames on that one again :-) Regards, Jignesh Josh Berkus wrote: Tom, Well, if the load is a lot of short writing transactions then you'd expect the throughput to depend on how fast stuff can be pushed down to WAL. What have you got wal_buffers set to? Are you using a commit delay? What's the I/O system anyway (any BB write cache on the WAL disk?) and what wal sync method are you using? You know, I think Jignesh needs to me on this list so I can stop relaying questions on a workload I didn't design. Let me get him. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Simple query showing 270 hours of CPU time
Dan Harris [EMAIL PROTECTED] writes: Since you mentioned the number of semops is distressingly high, does this indicate a tuning problem? More like an old-version problem. We've done a lot of work on concurrent performance since 8.0.x, and most likely you are hitting one of the bottlenecks that have been solved since then. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] User concurrency thresholding: where do I look?
I forgot to add one more piece of information.. I also tried the same test with 64-bit postgresql with 6GB shared_buffers and results are the same it drops around the same point which to me sounds like a bottleneck.. More later -Jignesh Jignesh K. Shah wrote: Awww Josh, I was just enjoying the chat on the picket fence! :-) Anyway the workload is mixed (reads,writes) with simple to medium queries. The workload is known to scale well. But inorder to provide substantial input I am still trying to eliminate things that can bottleneck. Currently I have eliminated CPU (plenty free) , RAM (memory is 48GB RAM in this server for a 32-bit postgresql instance), IO Storage (used the free ram to do /tmp database to eliminate IO) and am still trying to eliminate any network bottlenecks to say for sure we have a problem in PostgreSQL. But yes till that final thing is confirmed (network which can very well be the case) it could be a problem somewhere else. However the thing that worries me is more of the big drop instead of remaining constant out there.. Anyway more on this within a day or two once I add more network nics between the systems to eliminate network problems (even though stats dont show them as problems right now) and also reduce malloc lock penalties if any. As for other questions: max_locks_per_transactions is set to default (10 I believe) increasing it still seems to degrade overall throughput number. max_connections is set to 1500 for now till I get decent scaling till 1400-1500 users. There are no hard failures reported anywhere. Log min durations does show that queries are now slowing down and taking longer. OS is not swapping and also eliminated IO by putting the whole database on /tmp So while I finish adding more network connections between the two systems (need to get cards) do enjoy the following URL :-) http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-20070703-00073.html Of course all postgresql.conf still remains from the old test so no flames on that one again :-) Regards, Jignesh Josh Berkus wrote: Tom, Well, if the load is a lot of short writing transactions then you'd expect the throughput to depend on how fast stuff can be pushed down to WAL. What have you got wal_buffers set to? Are you using a commit delay? What's the I/O system anyway (any BB write cache on the WAL disk?) and what wal sync method are you using? You know, I think Jignesh needs to me on this list so I can stop relaying questions on a workload I didn't design. Let me get him. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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?
Jignesh K. Shah [EMAIL PROTECTED] writes: There are no hard failures reported anywhere. Log min durations does show that queries are now slowing down and taking longer. OS is not swapping and also eliminated IO by putting the whole database on /tmp Hmm. Do you see any evidence of a context swap storm (ie, a drastic increase in the context swaps/second reading reported by vmstat)? 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] User concurrency thresholding: where do I look?
Yes I did see increase in context switches and CPU migrations at that point using mpstat. Regards, Jignesh Tom Lane wrote: Jignesh K. Shah [EMAIL PROTECTED] writes: There are no hard failures reported anywhere. Log min durations does show that queries are now slowing down and taking longer. OS is not swapping and also eliminated IO by putting the whole database on /tmp Hmm. Do you see any evidence of a context swap storm (ie, a drastic increase in the context swaps/second reading reported by vmstat)? 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 ---(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] 8.2 - 8.3 performance numbers
On Jul 20, 2007, at 1:03 PM, Josh Berkus wrote: Jim, Has anyone benchmarked HEAD against 8.2? I'd like some numbers to use in my OSCon lightning talk. Numbers for both with and without HOT would be even better (I know we've got HOT-specific benchmarks, but I want complete 8.2 - 8.3 numbers). We've done it on TPCE, which is a hard benchmark for PostgreSQL. On that it's +9% without HOT and +13% with HOT. I think SpecJ would show a greater difference, but we're still focussed on benchmarks we can publish (i.e. 8.2.4) right now. Bleh, that's not a very impressive number. Anyone else have something better? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] User concurrency thresholding: where do I look?
Tom Lane wrote: Jignesh K. Shah [EMAIL PROTECTED] writes: Yes I did see increase in context switches and CPU migrations at that point using mpstat. So follow that up --- try to determine which lock is being contended for. There's some very crude code in the sources that you can enable with -DLWLOCK_STATS, but probably DTrace would be a better tool. regards, tom lane Using plockstat -A -s 5 -p $pid on bgwriter: doesnt report anything On one of the many connections: This one is hard to read easily By default, plockstat monitors all lock con- tention events, gathers frequency and timing data about those events, and displays the data in decreasing frequency order, so that the most common events appear first. ^Cbash-3.00# plockstat -A -s 5 -p 6401 ^C Mutex hold --- Count nsec Lock Caller 59 186888 0x10059e280 libumem.so.1`process_free+0x12c nsec Time Distribution --- count Stack 16384 || 1 libumem.so.1`process_free+0x12c 32768 |@ |14 postgres`AllocSetDelete+0x98 65536 |@@ | 5 postgres`MemoryContextDelete+0x78 131072 || 0 postgres`CommitTransaction+0x240 262144 |@@@ |39 --- Count nsec Lock Caller 53012226 0x10059e280 libumem.so.1`umem_cache_alloc+0x200 nsec Time Distribution --- count Stack 4096 |@@@ | 338 libumem.so.1`umem_cache_alloc+0x200 8192 |@ |24 libumem.so.1`umem_alloc+0x5c 16384 |@ |37 libumem.so.1`malloc+0x40 32768 |@ | 131 postgres`AllocSetAlloc+0x1c4 --- Count nsec Lock Caller 32410214 0x100578030 libumem.so.1`vmem_xfree+0x164 nsec Time Distribution --- count Stack 4096 |@@ | 192 libumem.so.1`vmem_xfree+0x164 8192 ||56 libumem.so.1`process_free+0x12c 16384 |@ |26 postgres`AllocSetDelete+0x98 32768 |@@@ |50 postgres`MemoryContextDelete+0x78 --- Count nsec Lock Caller 16113585 0x10059e280 libumem.so.1`process_free+0x12c nsec Time Distribution --- count Stack 4096 |@ | 118 libumem.so.1`process_free+0x12c 8192 || 4 postgres`AllocSetDelete+0x98 16384 |@ |10 postgres`MemoryContextDelete+0x78 32768 |@@@ |24 postgres`PortalDrop+0x160 65536 || 3 131072 || 0 262144 || 2 --- Count nsec Lock Caller 326 6081 libumem.so.1`vmem0+0xc38 libumem.so.1`vmem_xalloc+0x630 nsec Time Distribution --- count Stack 4096 || 170 libumem.so.1`vmem_xalloc+0x630 8192 |@@@ | 155 libumem.so.1`vmem_alloc+0x1f8 16384 || 1 libumem.so.1`vmem_xalloc+0x524 libumem.so.1`vmem_alloc+0x1f8 --- Count nsec Lock Caller 326 5867 libumem.so.1`vmem0+0x30 libumem.so.1`vmem_alloc+0x248 nsec Time Distribution --- count Stack 4096 |@ | 185 libumem.so.1`vmem_alloc+0x248 8192 |@@ | 141 libumem.so.1`vmem_sbrk_alloc+0x30 libumem.so.1`vmem_xalloc+0x524 libumem.so.1`vmem_alloc+0x1f8 --- Count nsec Lock Caller 318 5873 0x100578030 libumem.so.1`vmem_alloc+0x1d0 nsec Time Distribution --- count Stack 4096 |@ | 228 libumem.so.1`vmem_alloc+0x1d0 8192 |@ |78 libumem.so.1`umem_alloc+0xec 16384 || 6 libumem.so.1`malloc+0x40 32768 || 6 postgres`AllocSetAlloc+0x1c4 --- Count nsec Lock Caller 326 5591
Re: [PERFORM] User concurrency thresholding: where do I look?
Jignesh K. Shah [EMAIL PROTECTED] writes: Tom Lane wrote: So follow that up --- try to determine which lock is being contended for. There's some very crude code in the sources that you can enable with -DLWLOCK_STATS, but probably DTrace would be a better tool. Using plockstat -A -s 5 -p $pid I don't know what that is, but it doesn't appear to have anything to do with Postgres LWLocks or spinlocks, which are the locks I was thinking of. Try asking Robert Lor about this --- IIRC he had some dtrace probes to work with our locks. 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?
sorry.. The are solaris mutex locks used by the postgresql process. 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 I need to enable the DTrace probes on my builds -Jignesh Tom Lane wrote: Jignesh K. Shah [EMAIL PROTECTED] writes: Tom Lane wrote: So follow that up --- try to determine which lock is being contended for. There's some very crude code in the sources that you can enable with -DLWLOCK_STATS, but probably DTrace would be a better tool. Using plockstat -A -s 5 -p $pid I don't know what that is, but it doesn't appear to have anything to do with Postgres LWLocks or spinlocks, which are the locks I was thinking of. Try asking Robert Lor about this --- IIRC he had some dtrace probes to work with our locks. 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] User concurrency thresholding: where do I look?
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.) 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] User concurrency thresholding: where do I look?
True you cant switch off the locks since libthread has been folded into libc in Solaris 10. Anyway just to give you an idea of the increase in context switching at the break point here are the mpstat (taken at 10 second interval) on this 8-socket Sun Fire V890. The low icsw (Involuntary Context Switches) is about 950-1000 user mark after which a context switch storm starts at users above 1000-1050 mark and drops in total throughput drops about 30% instantaneously.. I will try rebuilding the postgresql with dtrace probes to get more clues. (NOTE you will see 1 cpu (cpuid:22) doing more system work... thats the one doing handling the network interrupts) CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 57 0 27 1086 4072 98 1749 4161 7763 47 13 0 40 1 46 0 24226 4198 11 1826 4270 7547 45 13 0 42 2 42 0 34 1048 4103 91 1682 4241 7797 46 13 0 41 3 51 0 22216 4125 10 1734 4350 7399 45 13 0 43 4 65 0 27196 40158 1706 4110 7292 44 15 0 41 5 54 0 21216 4297 10 1702 4640 7708 45 13 0 42 6 36 0 1666 47 4218 12 1713 4260 7685 47 11 0 42 7 40 0 100 318 206 3699 10 1534 5850 6851 45 14 0 41 16 41 0 30875 3780 78 1509 4011 7604 45 13 0 42 17 39 0 24225 3970 12 1631 4080 7265 44 12 0 44 18 42 0 24995 3829 89 1519 4011 7343 45 12 0 43 19 39 0 31 788305 35888 1509 4000 6629 43 13 0 44 20 22 0 20196 39259 1577 4190 7364 44 12 0 44 21 38 0 31235 3792 13 1566 4070 7133 45 12 0 44 228 0 110 7053 7045 16418 728 8380 2917 16 50 0 33 23 62 0 29215 3985 10 1579 4490 7368 44 12 0 44 CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 13 0 27 1236 4228 113 1820 4331 8084 49 13 0 38 1 16 0 63266 4253 15 1875 4200 7754 47 14 0 39 2 11 0 31 1108 4178 97 1741 4251 8095 48 14 0 38 38 0 24206 42579 1818 4440 7807 47 13 0 40 4 13 0 54286 4145 17 1774 4261 7732 46 16 0 38 5 12 0 35236 4412 12 1775 4470 8249 48 13 0 39 68 0 2438 15 4323 14 1760 4220 8016 49 11 0 39 78 0 120 323 206 3801 15 1599 6350 7290 47 15 0 38 16 11 0 44 1075 3896 98 1582 3931 7997 47 15 0 39 17 15 0 29245 4120 14 1716 4160 7648 46 13 0 41 189 0 35 1135 3933 103 1594 3991 7714 47 13 0 40 198 0 34 832715 3702 12 1564 4030 7010 45 14 0 41 207 0 28276 3997 16 1624 4000 7676 46 13 0 41 218 0 28255 3997 15 1664 4020 7658 47 12 0 41 224 0 97 7741 7731 1586 11 704 9060 2933 17 51 0 32 23 13 0 28255 4144 15 1658 4370 7810 47 12 0 41 CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 00 0 141 3156 9262 301 2812 3300 10905 49 16 0 35 11 0 153 1996 9400 186 2808 3120 11066 48 16 0 37 20 0 140 2568 8798 242 2592 3100 10111 47 15 0 38 31 0 141 1896 8803 172 2592 3140 10171 47 15 0 39 40 0 120 2146 9540 207 2801 3220 10531 46 17 0 36 51 0 152 1806 8764 161 2564 3420 9904 47 15 0 38 61 0 107 344 148 8180 181 2512 2900 9314 51 14 0 35 70 0 665 443 204 8733 153 2574 4040 9892 43 21 0 37 160 0 113 2175 6446 201 1975 2650 7552 45 12 0 44 170 0 107 1535 6568 140 2021 2740 7586 44 11 0 45 180 0 121 2155 6072 201 1789 2761 7690 44 12 0 44 191 0 102 471425 6123 126 1829 2620 7185 43 12 0 45 200 0 102 1436 6451 129 1939 2620 7450 43 13 0 44 211 0 106 1505 6538 133 1997 2850 7425 44 11 0 44 220 0 494 5949 5876 3586 73 1040 3990 4058 26 39 0 34 230 0 102 1595 6393 142 1942 3240 7226 43 12 0 46 CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 00 0 217 4417 10763 426 3234 3630 12449 47 18 0 35 10 0 210 3227 3 309 3273 3510 12527 46 17 0 37 21 0 212 3878 10306 370 2977 3540 11320 45 16 0 38 30 0 230 2767 10332 257 2947 3410 11901 43 16 0 40 40 0 234 3067 11324 290 3265 3520
Re: [PERFORM] User concurrency thresholding: where do I look?
Jignesh K. Shah [EMAIL PROTECTED] writes: Yes I did see increase in context switches and CPU migrations at that point using mpstat. So follow that up --- try to determine which lock is being contended for. There's some very crude code in the sources that you can enable with -DLWLOCK_STATS, but probably DTrace would be a better tool. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Postgres configuration for 64 CPUs, 128 GB RAM...
Marc, Server Specifications: -- Sun SPARC Enterprise M8000 Server: http://www.sun.com/servers/highend/m8000/specs.xml File system: http://en.wikipedia.org/wiki/ZFS There are some specific tuning parameters you need for ZFS or performance is going to suck. http://www.solarisinternals.com/wiki/index.php/ZFS_Best_Practices_Guide (scroll down to PostgreSQL) http://www.sun.com/servers/coolthreads/tnb/applications_postgresql.jsp http://bugs.opensolaris.org/view_bug.do?bug_id=6437054 You also don't say anything about what kind of workload you're running. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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] large number of connected connections to postgres database (v8.0)
Fei Liu, It appears my multi-thread application (100 connections every 5 seconds) is stalled when working with postgresql database server. I have limited number of connections in my connection pool to postgresql to 20. At the begining, connection is allocated and released from connection pool as postgres serves data request. The pool can recover from exhaustion. But very quickly (after about 400 client requests), it seems postgres server stops serving and connection to postgres server is not released any more resulting a resource exhausting for clients. This sounds more like a problem with your connection pool. Unless PostgreSQL is slowing down due to CPU/RAM/I/O saturation? In any case, I doubt the problem is too many connections, or you'd just get an error message ... -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Postgres configuration for 64 CPUs, 128 GB RAM...
Having done something similar recently, I would recommend that you look at adding connection pooling using pgBouncer transaction pooling between your benchmark app and PgSQL. In our application we have about 2000 clients funneling down to 30 backends and are able to sustain large transaction per second volume. This has been the #1 key to success for us in running on monster hardware. Regards, Gavin On 7/17/07, Marc Mamin [EMAIL PROTECTED] wrote: Postgres configuration for 64 CPUs, 128 GB RAM... Hello, We have the oppotunity to benchmark our application on a large server. I have to prepare the Postgres configuration and I'd appreciate some comments on it as I am not experienced with servers of such a scale. Moreover the configuration should be fail-proof as I won't be able to attend the tests. Our application (java + perl) and Postgres will run on the same server, whereas the application activity is low when Postgres has large transactions to process. There is a large gap between our current produtcion server (Linux, 4GB RAM, 4 cpus) and the benchmark server; one of the target of this benchmark is to verify the scalability of our application. And you have no reason to be envious as the server doesn't belong us :-) Thanks for your comments, Marc Mamin Posgres version: 8.2.1 Server Specifications: -- Sun SPARC Enterprise M8000 Server: *http://www.sun.com/servers/highend/m8000/specs.xml*http://www.sun.com/servers/highend/m8000/specs.xml File system: *http://en.wikipedia.org/wiki/ZFS* http://en.wikipedia.org/wiki/ZFS Planned configuration: # we don't expect more than 150 parallel connections, # but I suspect a leak in our application that let some idle connections open max_connections=2000 ssl = off #maximum allowed shared_buffers= 262143 # on our current best production server with 4GB RAM (not dedicated to Postgres), work_mem is set to 600 MB # this limitation is probably the bottleneck for our application as the files in pgsql_tmp grows up to 15 GB # during large aggregations (we have a locking mechanismus to avoid parallel processing of such transactions) work_mem = 31457280 # (30 GB) # index creation time is also an issue for us; the process is locking other large processes too. # our largest table so far is 13 GB + 11 GB indexes maintenance_work_mem = 31457280 # (30 GB) # more than the max number of tables +indexes expected during the benchmark max_fsm_relations = 10 max_fsm_pages = 180 # don't know if I schoud modify this. # seems to be sufficient on our production servers max_stack_depth = 2MB # vacuum will be done per hand between each test session autovacuum = off # required to analyse the benchmark log_min_duration_statement = 1000 max_prepared_transaction = 100 # seems to be required to drop schema/roles containing large number of objects max_locks_per_transaction = 128 # I use the default for the bgwriter as I couldnt find recommendation on those #bgwriter_delay = 200ms # 10-1ms between rounds #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round #WAL fsync = on #use default #wal_sync_method # we are using 32 on our production system wal_buffers=64 # we didn't make any testing with this parameter until now, but this should'nt be a relevant # point as our performance focus is on large transactions commit_delay = 0 #CHECKPOINT # xlog will be on a separate disk checkpoint_segments=256 checkpoint_timeout = 5min
Re: [PERFORM] User concurrency thresholding: where do I look?
Tom Lane wrote: Having malloc/free use an internal mutex is necessary in multi-threaded programs, but the backend isn't multi-threaded. Hmm...confused. I'm not following why then there is contention for the mutex. Surely this has to be some other mutex that is in contention, not a heap lock ? It'd be handy to see the call stack for the wait state -- if the thing is spending a significant proportion of its time in contention it should be easy to get that with a simple tool such as pstack or a debugger. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] User concurrency thresholding: where do I look?
David Boreham [EMAIL PROTECTED] writes: Tom Lane wrote: Having malloc/free use an internal mutex is necessary in multi-threaded programs, but the backend isn't multi-threaded. Hmm...confused. I'm not following why then there is contention for the mutex. There isn't any contention for that mutex; Jignesh's results merely show that it was taken and released a lot of times. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
