gmane.comp.db.postgresql.devel.general

Re: Move unused buffers to freelist

Amit Kapila — 2013-05-21T10:23:56

On looking more closely at data posted by you, I believe that there is some
problem with data (8
clients < at > scale factor 1000 on master) as in all other cases, the data for
scale factor 1000 is better than 3000 except for this case.
So I think no need to run again.

Re: Fast promotion failure

Simon Riggs — 2013-05-21T08:26:01

I'm OK with that principle, as long as we don't touch ThisTimeLineID,
which has been the source of multiple bugs.

So we should set the TLI explicitly before installing, like attached patch.

Otherwise we'd need multiple permanent TLIs which would be overkill.

I feel there are problems because we set the newly selected TLI from
startup process into shared memory, then some time later we set
SharedRecoveryInProgress = false. That timing window isn't good, but I
don't see a different way.


Agreed, but looks like too much code to touch that lightly.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Move unused buffers to freelist

Amit Kapila — 2013-05-21T07:06:17

Thanks for running detailed tests



Is the above test for tpc-b?
In the above tests, there is performance increase from 1~8% and decrease
from 0.2~1.5%


  Do you want to say the reading of 1000 scale factor?
  

I have not generated numbers for read-write tests, I will check that once.
For read-only tests, the performance increase is minor and different from
what I saw. 
Few points which I could think of for difference in data:

1. In my test's I always observed best data when number of clients/threads
are equal to number of cores which in your case should be at 16.
2. I think for scale factor 100 and 300, there should not be much
performance increase, as for them they should mostly get buffer from
freelist inspite of even bgwriter adds to freelist or not. 
3. In my tests variance is for shared buffers, database size is always less
than RAM (Scale Factor -1200, approx db size 16~17GB, RAM -24 GB), but due
to variance in shared buffers, it can lead to I/O. 
4. Each run is of 20 minutes, not sure if this has any difference.
 

To think about the difference in your and my runs, could you please tell me
about below points
1. What is RAM in machine.
2. Are number of threads equal to number of clients.
3. Before starting tests I have always done pre-warming of buffers (used
pg_prewarm written by you last year), is it same for above read-only tests.
4. Can you please once again run only the test where you saw variation(8
clients < at > scale> factor 1000 on master), because I have also seen that
performance difference is very good for certain    
   configurations(Scale Factor, RAM, Shared Buffers)

Apart from above, I had one more observation during my investigation to find
why in some cases, there is a small dip:
1. Many times, it finds the buffer in free list is not usable, means it's
refcount or usage count is not zero, due to which it had to spend more time
under BufFreelistLock.
   I had not any further experiments related to this finding like if it
really adds any overhead.

Currently I am trying to find reasons for small dip of performance and see
if I could do something to avoid it. Also I will run tests with various
configurations.

Any other suggestions?

With Regards,
Amit Kapila.

Re: Fast promotion failure

Heikki Linnakangas — 2013-05-21T06:46:31

Hmm. lastReplayedTLI is supposed to be the timeline of the last record 
that was replayed, ie. the timeline corresponding lastReplayedEndRecPtr. 
I think it would work, but it feels like it could be an easy source of 
bugs in the future.

It might be a good idea to change walsender to not store that in 
ThisTimeLineID, though. It used to make sense for ThisTimeLineID to 
track the current recovery timeline in 9.2 and below, but now that 
walsender can be sending any older timeline, using ThisTimeLineID to 
store the latest one seems confusing.

- Heikki

Re: [PATCH] Correct release notes about DROP TABLE IF EXISTS and add, link.

Bruce Momjian — 2013-05-21T03:02:08

Yeah.  :-)  The patch added markup, and fixed the command text.

Re: [PATCH] Correct release notes about DROP TABLE IF EXISTS and add, link.

Andrew Dunstan — 2013-05-21T02:55:19

On 05/20/2013 10:38 PM, Bruce Momjian wrote:

*snort*

This would be a rather pointless command!

cheers

andrew

Re: [PATCH] Correct release notes about DROP TABLE IF EXISTS and add, link.

Bruce Momjian — 2013-05-21T02:38:50

Thanks, applied.

---------------------------------------------------------------------------

On Fri, May 17, 2013 at 03:12:21PM -0400, Joe Abbate wrote:

Re: Proposal to add --single-row to psql

Darren Duncan — 2013-05-21T02:18:16

I have actually been working on the task discussed in this thread, most relevant 
parts quoted below, for awhile now, and hope to have something concrete that you 
can use by the end of this summer.

My in-development Muldis D language is homoiconic as a core feature, its source 
code is data to it, and the native syntax of the language resembles an abstract 
syntax tree.  This tree of nodes primarily defines behavior of the code, but it 
also supports arbitrary metadata per node, which for example can be used to 
preserve concrete syntax for any programming language that can be parsed into 
Muldis D nodes or conversely generated from said.

For example, you can have one type of node defining a function, and its details 
are defined by its attributes or child nodes, such as its result type, its 
parameters, whether it is declared associative/commutative/etc or not, and the 
expression(s) defining its body.  Another type of node defines a call to a 
function, another type defines a text literal, another a relation literal, and 
so on.  Conversely, a node can define a schema or package etc.  Example 
metadata, which is also structured, could include line numbers or code comments 
or whitespace or exact numeric literal formats or quoting formats or exact 
keyword choices, for example.

Using these node data types, we have a fairly normalized representation of any 
source code (or data) that is easy to introspect or transform with code.  A key 
design of Muldis D is the clear separation of syntax and behavior.

A parser is just a function that takes (typically) a character string as input 
and produces a node (tree) as output.  A compiler is just a function or 
operation that takes a node (tree) as input and produces machine code.  An 
optimizer can be a function that derives one node tree from another, either as 
its own operation or typically as part of the compiler stage.

A compiler or optimizer generally can trivially ignore the node metadata, but a 
code generator or debugger can use it; metadata can be stripped without 
affecting behavior.  The canonical tree form can also easily be mapped 
losslessly with relation forms, such as typical information schemas have.

Practically all behavior is defined in terms of generic type and routine 
definitions.  Practically all system-defined types and routines are defined in 
one or more libraries/modules/packages that have the same format as those users 
would write like extensions.  So, all the relational operators have the same 
syntax as say string or numeric or array operators.

I envision that the most effective way to use Muldis D to handle an arbitrary 
programming language, including the native SQL syntax+behavior of each SQL DBMS, 
is to have a Muldis D library+parser pair for it.

For example, to represent PostgreSQL 9.2 most directly, we have a library with 
an explicitly defined type and routine for every built-in that Pg 9.2 has, and 
we also have a parser function that takes the SQL syntax that Pg 9.2 understands 
and produces a Muldis D node tree consisting of calls to the routines of that 
library or value selectors of types in that library (things like SELECT and 
INSERT etc are each mapped to a routine too).  That way, even with a standard 
node format that isn't specific to a typical language or version, the code for 
parsing Pg 9.2 SQL has the minimal amount of indirection that it has to deal 
with, as each syntax element would have a direct library call counterpart. 
Similarly, the existing Pg 9.2 SQL compiler would have the least indirection to 
take said nodes and execute them.  (The library would be named eg "Postgres_9_2" 
for example, which makes it easier say to also have one side-by-side for other 
versions, shims, legacy code you want to more easily support compatibility with.)

Where one decides to do cross-compilation, say make Oracle SQL run on Pg, that 
could be done as simply as defining a library for Oracle SQL with the 
routines+types that has, and then mapping it to a Pg one just in terms of shim 
calls (which the compiler can optimize away as applicable), and so parsers or 
compilers never necessarily have to deal with behavior compatibility issues.

I am presently working out the exact set of such language nodes, and making a 
reference implementation which is mostly self-defined and would compile to Perl 
code, and hope to have the first Muldis D executable by the end of this summer.

I am confident that an adaption of this design into C or whatever would serve 
Postgres greatly in letting it effectively parse multiple languages, anywhere 
from application programming languages to multiple SQL dialects or versions.

Even if you don't decide to use my design specifically (which is open source and 
you can influence it), I think you should find some of the general design 
principles I stated above to be useful.  Representing behavior as libraries the 
AST being flexible enough for any concrete language without being too specific 
to details of one.  And of course, cross-invocation of code written in multiple 
languages is made much easier.

Note, just to be clear, my proposal does not necessitate that all of a node tree 
has to be kept in memory at once.  This design should be adaptable to a 
streaming approach, especially as it is expected to be able to handle database 
dumps or transfers of arbitrary size, same as SQL engines can today.  That is in 
contrast to probably what most application languages would assume, where 
everything would fit in memory at once.  But the ability to stream or not would 
largely be an implementation detail.  Realistically, all code should fit in 
memory at once, and anything that would have to be buffered out of memory would 
be say embedded data literals (whether large strings or simply large relations).

If you're not sure how my proposal would address any of the needs or wants 
raised in the thread, go ahead and ask, and I will try and answer as time permits.

Removal of pageinspect--1.0.sql

Michael Paquier — 2013-05-20T23:50:25

Hi all,

The contrib module pageinspect has been upgraded to 1.1, but
pageinspect--1.0.sql is still present in source code. Shouldn't it be
removed? Please find patch attached.

Thanks

Re: Add more regression tests for dbcommands

Robins Tharakan — 2013-05-20T21:28:25

Hi,

Attached is an updated patch that does only 1 CREATE DATABASE and reuses
that for all other tests.
The code-coverage with this patch goes up from 36% to 70%.

--
Robins Tharakan


On 13 May 2013 21:04, Robins Tharakan <tharakan< at >gmail.com> wrote:

Re: Better LWLocks with compare-and-swap (9.4)

Heikki Linnakangas — 2013-05-20T21:20:10

Stop the press! I'm getting the same speedup on that 32-core box I got 
with the compare-and-swap patch, from this one-liner:

--- a/src/include/storage/s_lock.h
+++ b/src/include/storage/s_lock.h
< at >< at > -200,6 +200,8 < at >< at > typedef unsigned char slock_t;

  #define TAS(lock) tas(lock)

+#define TAS_SPIN(lock)(*(lock) ? 1 : TAS(lock))
+
  static __inline__ int
  tas(volatile slock_t *lock)
  {

So, on this system, doing a non-locked test before the locked xchg 
instruction while spinning, is a very good idea. That contradicts the 
testing that was done earlier when the x86-64 implementation was added, 
as we have this comment in the tas() implementation:


On my test system, the non-locking test is a big win. I tested this 
because I was reading this article from Intel:

http://software.intel.com/en-us/articles/implementing-scalable-atomic-locks-for-multi-core-intel-em64t-and-ia32-architectures/. 
It says very explicitly that the non-locking test is a good idea:


Now, I'm not sure what to do about this. If we put the non-locking test 
in there, according to the previous testing that would be a huge loss on 
Opterons.

Perhaps we should just sleep earlier, ie. lower MAX_SPINS_PER_DELAY. 
That way, even if each TAS_SPIN test is very expensive, we don't spend 
too much time spinning if it's really busy, or held by a process that is 
sleeping.

- Heikki

Re: Fast promotion failure

Simon Riggs — 2013-05-20T21:00:59

Hmm, OK.

Then we should using the correct value, not the one that patch set. It
certainly worked at that time, but not in a principled way.

When we set the new timeline we should be updating all values that
might be used elsewhere. If we do that, then no matter when or how we
run GetXLogReplayRecPtr, it can't ever get it wrong in any backend.

Patch attached.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Better LWLocks with compare-and-swap (9.4)

Bruce Momjian — 2013-05-20T20:41:07

So we are going to need to add this kind of assembly language
optimization for every CPU type?

Re: Better LWLocks with compare-and-swap (9.4)

Heikki Linnakangas — 2013-05-20T20:20:50

Thanks, good catch. I renamed HAVE_GCC_INT_ATOMICS to 
HAVE_GCC_INT_TEST_AND_SET because "atomics" seems too generic when we 
also test for __sync_val_compare_and_swap(p, oldval, newval).

- Heikki

Re: Better LWLocks with compare-and-swap (9.4)

Heikki Linnakangas — 2013-05-20T20:16:41

All modern architectures have an atomic compare-and-swap instruction (or 
something more powerful that can be used to implement it). That includes 
x86, x86-64, ARM, PowerPC, among others.

There are some differences in how wide values can be swapped with it; 
386 only supported 32-bit, until Pentium, which added a 64-bit variant. 
I used the 64-bit variant in the patch, but for lwlocks, we could 
actually get away with the 32-bit variant if we packed the booleans and 
the shared lock counter more tightly.

- Heikki

Re: Better LWLocks with compare-and-swap (9.4)

Alvaro Herrera — 2013-05-20T20:11:21

Careful here --- s_lock.h has some code conditional on
HAVE_GCC_INT_ATOMICS which your patch is not touching, yet it is
removing the definition, unless I'm misreading.

Re: Better LWLocks with compare-and-swap (9.4)

Bruce Momjian — 2013-05-20T20:01:39

Uh, is this an x86-64-only optimization?  Seems so.

Re: fast promotion and log_checkpoints

Simon Riggs — 2013-05-20T19:44:20


The reason text would still be absent, so it wouldn't really help the
user interpret the log message correctly.

I suggest we use RequestCheckpoint(CHECKPOINT_CAUSE_TIME) instead,
since it is literally time for a checkpoint.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Heap truncation without AccessExclusiveLock (9.4)

Heikki Linnakangas — 2013-05-20T19:43:05

Hmm. For the above to work, you'd need to atomically check that the 
pages you're truncating away are not pinned, and truncate them. If those 
steps are not atomic, a backend might pin a page after you've checked 
that it's not pinned, but before you've truncated the underlying file. I 
guess that be doable; needs some new infrastructure in the buffer 
manager, however.


Yeah. I'll think some more how the required buffer manager changes could 
be done.

- Heikki

Re: Fast promotion failure

Heikki Linnakangas — 2013-05-20T19:40:14



That's essentially reverting commit 343ee00b7, resurrecting the bug that 
that fixed.



That breaks the only remaining caller that passes a replayTLI pointer, 
GetStandbyFlushRecPtr(); *replayTLI points to a local variable in that 
function, which is left uninitialized if !xlogctl->SharedRecoveryInProgress.

- Heikki

Re: FK locking concurrency improvement

Alvaro Herrera — 2013-05-20T19:38:50

No, you're misunderstanding.  When the spec file specifies permutations,
then those are exactly the permutations that are run.  When the spec
does not list any permutations, then the test driver runs all the
possible permutations.

This was made possibly by a change to isolationtester that an
"impossible" permutation did not cause the test to die, but instead to
continue by reporting that the permutation is impossible.  That way, we
ensure that not only the listed permutations are running and passing,
but also that the set of permutations that are possible does not change.
(An "impossible" permutation is one that requires running a command in a
session that is in blocked state, something which is clearly
impossible.)

In hindsight, we could have committed this change separately.


Interesting.  I'm not sure it's worth messing with this now (given that
the current coding works everywhere), but if there's a strong reason to
do it that way we can certainly change it.