Re: [HACKERS] Page-at-a-time Locking Considerations
On Sat, 2008-03-22 at 20:37 -0400, Bruce Momjian wrote: > With no concrete patch or performance numbers, this thread has been > removed from the patches queue. I agree since there is no patch. However, I think recent performance reports around the cost of visibility checks such as "Very slow seq scan" by Craig Ringer on Perform list on 10 Mar shows that this remains an area of concern. We may have tuned some parts of the visibility checks, but not all. So I think it should be a TODO to investigate further. > Simon Riggs wrote: > > > > In heapgetpage() we hold the buffer locked while we look for visible > > tuples. That works well in most cases since the visibility check is fast > > if we have status bits set. If we don't have visibility bits set we have > > to do things like scan the snapshot and confirm things via clog lookups. > > All of that takes time and can lead to long buffer lock times, possibly > > across multiple I/Os in the very worst cases. > > > > This doesn't just happen for old transactions. Accessing very recent > > TransactionIds is prone to rare but long waits when we ExtendClog(). > > > > Such problems are numerically rare, but the buffers with long lock times > > are also the ones that have concurrent or at least recent write > > operations on them. So all SeqScans have the potential to induce long > > wait times for write transactions, even if they are scans on 1 block > > tables. Tables with heavy write activity on them from multiple backends > > have their work spread across multiple blocks, so a SeqScan will hit > > this issue repeatedly as it encounters each current insertion point in a > > table and so greatly increases the chances of it occurring. > > > > It seems possible to just memcpy() the whole block away and then drop > > the lock quickly. That gives a consistent lock time in all cases and > > allows us to do the visibility checks in our own time. It might seem > > that we would end up copying irrelevant data, which is true. But the > > greatest cost is memory access time. If hardware memory pre-fetch cuts > > in we will find that the memory is retrieved en masse anyway; if it > > doesn't we will have to wait for each cache line. So the best case is > > actually an en masse retrieval of cache lines, in the common case where > > blocks are fairly full (vague cutoff is determined by exact mechanism of > > hardware/compiler induced memory prefetch). > > > > The copied block would be used only for visibility checks. The main > > buffer would retain its pin and we would pass references to the block > > through the executor as normal. So this would be a change completely > > isolated to heapgetpage(). > > > > Was the copy-aside method considered when we introduced page at a time > > mode? Any reasons to think it would be dangerous or infeasible? If not, > > I'll give it a bash and get some test results. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.
On Thu, Mar 20, 2008 at 06:53:27PM -0400, Tom Lane wrote: > Kurt Roeckx <[EMAIL PROTECTED]> writes: > > I did some tests with gcc 4.3 on the branches from 7.4 to 8.3 and head. > > 8.3 and head don't have a problem. All others failed in the > > ContribCheck state. > > > You can see the results on buildfarm member panda. > > Bizarre. There doesn't seem to be any significant difference in the seg > code between 8.2 and 8.3, so why is 8.2 failing there? > > The cube code got a significant overhaul (V0 to V1 call conventions) > between 8.1 and 8.2, so that might explain why it's OK in 8.2 and up. > Or not. If that is it, we'd likely just write it off as not-gonna-fix, > but it could stand investigation to pinpoint the cause. > > Can you poke into it with a debugger? The first failure case in seg > ought to be easy enough to investigate. I currently don't have the time to look into it. Kurt - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump additional options for performance
Bruce Momjian wrote: > > Added to TODO: > > o Allow pre/data/post files when dumping a single object, for > performance reasons > > http://archives.postgresql.org/pgsql-hackers/2008-02/msg00205.php "When dumping a single object"?? Do you mean database? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Hi, As an attempt at a first PostgreSQL patch, I'd like to see if I can do anything about this issue. I've read both the attached threads; http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php There seems no consensus about how to go about this. I have done some initial changes and found some problems with my attempts. initdb always creates pg_catalog relations with lowercase names, as does the function list. eg count() with uppercased identifiers requires "count"(). All of these can be altered on database copy. It shouldn't be a problem. However I see shared relations as a big problem. The 2004 thread suggests that we want a per database setting. I am unable to see how we share shared relations between databases with different case folder. pg_user is an example of this; Lowercase database; CREATE ROLE mrruss LOGIN; results in -> mrruss as data in pg_user Uppercase database; CREATE ROLE mrruss LOGIN; resutls in -> MRRUSS as data in pg_user Now both of those can be accessed from any database. And you will get a different user based on the source database. Overall, I'd like to concentrate on the implementation as I'm a beginner. But I see this being mainly a problem with nailing down the actual requirement for the implementation. So I'll try to start the discussion to allow me or somebody else to eventually develop a patch for this. The first question is, are all the requirements of the 2004 thread still true now? Setting case folder at initdb time seems the easiest method but I'm not sure if that's what people want. Any GUC variables seem to play havoc with the pg_catalog schema and the data in the catalogs. Ideas and comments? Thanks Russell - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Russell Smith wrote: The 2004 thread suggests that we want a per database setting. I am unable to see how we share shared relations between databases with different case folder. pg_user is an example of this; Lowercase database; CREATE ROLE mrruss LOGIN; results in -> mrruss as data in pg_user Uppercase database; CREATE ROLE mrruss LOGIN; resutls in -> MRRUSS as data in pg_user Now both of those can be accessed from any database. And you will get a different user based on the source database. You could use a setting, in, say, the control file, for the global tables. IIRC there are only three such tables. Overall, I'd like to concentrate on the implementation as I'm a beginner. I'm not sure this is a very good project for a beginner - but that's your choice. But I see this being mainly a problem with nailing down the actual requirement for the implementation. So I'll try to start the discussion to allow me or somebody else to eventually develop a patch for this. The first question is, are all the requirements of the 2004 thread still true now? Setting case folder at initdb time seems the easiest method but I'm not sure if that's what people want. Any GUC variables seem to play havoc with the pg_catalog schema and the data in the catalogs. Ideas and comments? Just getting to standard compliance will satisfy some, but I suspect not many. What a lot of people want is case sensitivity, with no folding. I think you need to look at that as an option. cheers andrew - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug in libpq causes local clients to hang
Lately I've noticed that local (UNIX socket) clients using libpq4 8.1.9 (Debian 8.1.9-0etch1) and the same version of the server can hang forever waiting in poll(). The symptom is that the local client waits forever, using no CPU time, until it is interrupted by some event (such as attaching gdb or strace to it), after which it proceeds normally. From the server's perspective, such clients are in the state " in transaction" as reported via pg_stat_activity. I attached GDB to one such client, and the stack trace is as follows: #0 0x2b4f2f914d7f in poll () from /lib/libc.so.6 #1 0x2b4f3038449f in PQmblen () from /usr/lib/libpq.so.4 #2 0x2b4f30384580 in pqWaitTimed () from /usr/lib/libpq.so.4 #3 0x2b4f30383e62 in PQgetResult () from /usr/lib/libpq.so.4 #4 0x2b4f30383f3e in PQgetResult () from /usr/lib/libpq.so.4 #5 0x2b4f3025f014 in dbd_st_execute () from /usr/lib/perl5/auto/DBD/Pg/Pg.so #6 0x2b4f302548b6 in XS_DBD__Pg__db_do () from /usr/lib/perl5/auto/DBD/Pg/Pg.so #7 0x2b4f2fd201f0 in XS_DBI_dispatch () from /usr/lib/perl5/auto/DBI/DBI.so #8 0x2b4f2f310b95 in Perl_pp_entersub () from /usr/lib/libperl.so.5.8 #9 0x2b4f2f30f36e in Perl_runops_standard () from /usr/lib/libperl.so.5.8 #10 0x2b4f2f2ba7dc in perl_run () from /usr/lib/libperl.so.5.8 #11 0x004017ac in main () You'll note that I'm using the DBD::Pg Perl interface. So far I've never seen this happen with TCP connections, only with UNIX sockets. I see it with about 1 in 100 local client invocations. As a workaround I've configured my local clients to use TCP anyway, and this seems to solve the problem. Is this something that might have been fixed in a post-8.1 version of libpq? -jwb - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bug in libpq causes local clients to hang
On Sun, Mar 23, 2008 at 7:12 PM, Jeffrey Baker <[EMAIL PROTECTED]> wrote: > Lately I've noticed that local (UNIX socket) clients using libpq4 > 8.1.9 (Debian 8.1.9-0etch1) and the same version of the server can > hang forever waiting in poll(). The symptom is that the local client > waits forever, using no CPU time, until it is interrupted by some > event (such as attaching gdb or strace to it), after which it proceeds > normally. From the server's perspective, such clients are in the > state " in transaction" as reported via pg_stat_activity. I > attached GDB to one such client, and the stack trace is as follows: > > #0 0x2b4f2f914d7f in poll () from /lib/libc.so.6 > #1 0x2b4f3038449f in PQmblen () from /usr/lib/libpq.so.4 > #2 0x2b4f30384580 in pqWaitTimed () from /usr/lib/libpq.so.4 > #3 0x2b4f30383e62 in PQgetResult () from /usr/lib/libpq.so.4 > #4 0x2b4f30383f3e in PQgetResult () from /usr/lib/libpq.so.4 > #5 0x2b4f3025f014 in dbd_st_execute () from > /usr/lib/perl5/auto/DBD/Pg/Pg.so > #6 0x2b4f302548b6 in XS_DBD__Pg__db_do () from > /usr/lib/perl5/auto/DBD/Pg/Pg.so > #7 0x2b4f2fd201f0 in XS_DBI_dispatch () from > /usr/lib/perl5/auto/DBI/DBI.so > #8 0x2b4f2f310b95 in Perl_pp_entersub () from /usr/lib/libperl.so.5.8 > #9 0x2b4f2f30f36e in Perl_runops_standard () from > /usr/lib/libperl.so.5.8 > #10 0x2b4f2f2ba7dc in perl_run () from /usr/lib/libperl.so.5.8 > #11 0x004017ac in main () Following up to myself, I note that a very similar issue was reported, with a very similar stack, only two days ago, with subject "ecpg program getting stuck" archived at http://groups.google.com/group/pgsql.general/browse_thread/thread/0b7ede57faad803e/9abfd7ab1b7e1d86 -jwb - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Russell Smith <[EMAIL PROTECTED]> writes: > As an attempt at a first PostgreSQL patch, I'd like to see if I can do > anything about this issue. Trying that as a first patch is a recipe for failure... the short answer is that no one can think of a solution that will be generally acceptable. regards, tom lane - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bug in libpq causes local clients to hang
"Jeffrey Baker" <[EMAIL PROTECTED]> writes: > You'll note that I'm using the DBD::Pg Perl interface. So far I've > never seen this happen with TCP connections, only with UNIX sockets. If it works over TCP and not over Unix socket, it's a kernel bug. The libpq code doesn't really know the difference after connection setup. regards, tom lane - Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
