Lock partitions
in [PgSql]
|
From: "Strong, David" on 14 Sep 2006 11:44 Tom, Taking the 4 lock vs 8 lock partitions, 4 LockMgr lock partitions spent a total of 652 seconds in lock management (acquiring/releasing) and 8 LockMgr lock partitions spent a total of 536 in lock management. This is an improvement of 116 seconds, but the TPS didn't improve by much - only a 1.21 TPS improvement. The improvement in the LockMgr processing is consumed by the next system bottleneck downstream as more work is being let through. In this particular case it's the WALInsertLock lock. The 4 LockMgr lock partition test spent a total of 5868 seconds in WALInsertLock lock management whereas the 8 LockMgr partition test spent 5945 seconds in WALInsertLock lock management which is an increase of 77 seconds. But, that's not the only static lock that increased in time, it's just the most significant increase. The WALWriteLock lock increased by 12 seconds, ProcArrayLock increased by 8 seconds and SInvalLock increased by 5 seconds. This takes the total time flowing to other locks to 102 seconds. The locks are not the only part of the puzzle. As improvements are made to various areas like the BufMapping and LockMgr lock partitions, other parts of the system start to get exercised in ways that were not possible in previous releases. We're still trying to get our arms around all the functions that might become bottlenecks when other lock contention is minimized. And, improvements are being made. The locking changes from 8.0.x to 8.1.x made a significant difference in scalability. Again, the current lock improvements in 8.2 have realized ~20% improvement over 8.1.x, based on our testing. We added monitoring code to the LWLockAcquire and LWLockRelease functions. We track the total time taken to pass through LWLockAcquire and LWLockRelease. So, if a particular backend process takes 1 second to run through LWLockAcquire, we will track that as 1 second in lock acquisition. Irrespective of whether my backend process was spinning or in a semaphore wait, it's 1 second that was taken away from processing a statement/request. We could also add timing for semaphore waits within LWLockAcquire, if that would be a useful statistic. Let me know if there are any other tests or metrics that would be useful. David -----Original Message----- From: Tom Lane [mailto:tgl(a)sss.pgh.pa.us] Sent: Wednesday, September 13, 2006 1:36 PM To: Strong, David Cc: PostgreSQL-development Subject: Re: [HACKERS] Lock partitions "Strong, David" <david.strong(a)unisys.com> writes: > We have some results for you. We left the buffer partition locks at 128 > as this did not seem to be a concern and we're still using 25 backend > processes. We ran tests for 4, 8 and 16 lock partitions. > For 4 lock partitions, it took 620 seconds to acquire locks and 32 > seconds to release locks. The test produced 199.95 TPS. > For 8 lock partitions, it took 505 seconds to acquire locks and 31 > seconds to release locks. The test produced 201.16 TPS. > For 16 lock partitions, it took 362 seconds to acquire locks and 22 > seconds to release locks. The test produced 200.75 TPS. > And, just for grins, using 128 buffer and 128 lock partitions, took 235 > seconds to acquire locks and 22 seconds to release locks. The test > produced 203.24 TPS. [ itch... ] I can't help thinking there's something wrong with this; the wait-time measurements seem sane, but why is there essentially no change in the TPS result? The above numbers are only for the lock-partition LWLocks, right? What are the totals --- that is, how much time is spent blocked vs. processing overall? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
From: Mark Wong on 14 Sep 2006 13:16 Tom Lane wrote: > Mark Wong <markw(a)osdl.org> writes: >> Tom Lane wrote: >>> It would be nice to see some results from the OSDL tests with, say, 4, >>> 8, and 16 lock partitions before we forget about the point though. >>> Anybody know whether OSDL is in a position to run tests for us? > >> Yeah, I can run some dbt2 tests in the lab. I'll get started on it. >> We're still a little bit away from getting the automated testing for >> PostgreSQL going again though. > > Great, thanks. The thing to twiddle is LOG2_NUM_LOCK_PARTITIONS in > src/include/storage/lwlock.h. You need a full backend recompile > after changing it, but you shouldn't need to initdb, if that helps. Sorry for the delay but looks like there's some data coming in. It also looks like my kit is starting to be a little dated. My stored libpq calls are failing. I'm getting this message: ERROR: record type has not been registered From PQerrorMessage() on line 41 from this bit of code: /* Create the query and execute it. */ sprintf(stmt, "SELECT * FROM order_status(%d, %d, %d, '%s')", data->c_id, data->c_w_id, data->c_d_id, data->c_last); res = PQexec(dbc->conn, stmt); if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK)) { LOG_ERROR_MESSAGE("%s", PQerrorMessage(dbc->conn)); PQclear(res); return ERROR; } LOG_ERROR_MESSAGE() is just a macro for a function that does a printf(). Any suggestions? Thanks, Mark ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
From: Tom Lane on 14 Sep 2006 14:05 Mark Wong <markw(a)osdl.org> writes: > Sorry for the delay but looks like there's some data coming in. It also > looks like my kit is starting to be a little dated. My stored libpq > calls are failing. I'm getting this message: > ERROR: record type has not been registered This is a server-side failure --- could we see how order_status() is defined? What PG version are you testing exactly? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
From: Mark Wong on 14 Sep 2006 14:13 Tom Lane wrote: > Mark Wong <markw(a)osdl.org> writes: >> Sorry for the delay but looks like there's some data coming in. It also >> looks like my kit is starting to be a little dated. My stored libpq >> calls are failing. I'm getting this message: > >> ERROR: record type has not been registered > > This is a server-side failure --- could we see how order_status() > is defined? What PG version are you testing exactly? I took pgsqsl snapshot from cvs on Sept 11. Due to the length of the file that order_status() is in and of order_status() itself, here's is a url for the file in the svn repository. order_status() is defined starting on line 710: http://svn.sourceforge.net/viewvc/osdldbt/trunk/dbt2/storedproc/pgsql/c/funcs.c?view=markup Thanks, Mark ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: Tom Lane on 14 Sep 2006 14:34
Mark Wong <markw(a)osdl.org> writes: > Tom Lane wrote: >> This is a server-side failure --- could we see how order_status() >> is defined? What PG version are you testing exactly? > I took pgsqsl snapshot from cvs on Sept 11. Due to the length of the > file that order_status() is in and of order_status() itself, here's is a > url for the file in the svn repository. order_status() is defined > starting on line 710: > http://svn.sourceforge.net/viewvc/osdldbt/trunk/dbt2/storedproc/pgsql/c/funcs.c?view=markup Hmph. I think we broke something --- the error implies that some function tried to return a tuple that hadn't been properly "blessed", but I can't see that order_status or any of the other functions in that file are doing anything wrong. In any case, if it used to work for you, we had better figure out exactly why it stopped working. I know you've posted info before on how to set up and run the dbt code, but could you refresh my memory? Is there a URL somewhere with the info? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |