Re: [PERFORM] bad performances using hashjoin
Tom Lane wrote: > but this behavior isn't reproduced in the later message, so I wonder if > it wasn't an artifact of something else taking a chunk of time. I think is due the fact that first queries were performed in peakhours. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Effects of IDLE processes
Christopher Browne wrote: > After a long battle with technology, Gaetano Mendola <[EMAIL PROTECTED]>, an > earthling, wrote: > >>JM wrote: >> >>>Hi ALL, >>> >>> I was wondering if there is a DB performance reduction if >>>there are a lot of IDLE processes. >>> >>>30786 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>>32504 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>>32596 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 1722 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 1724 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 3881 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 6332 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 6678 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 6700 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 6768 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 8544 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 8873 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 8986 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 9000 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 9010 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 9013 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 9016 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 9019 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> 9020 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >>> > > >>In my experience not at all, you have to wonder if some of that are >>"idle in transaction" that are really a pain in the @#$ > > > I'd be concerned about "idle" processes insofar as they are holding on > to _some_ memory that isn't shared. For "not at all" I was refering the fact that the normal engine work and maintenances are not affected ( at least your iron shall be able to support all these connections and processes ). A long transaction for example can stop the entire engine if for example a "Vacuum full" remain stuck on some tables locked by that transaction Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] bad performances using hashjoin
Gaetano Mendola wrote: I think is due the fact that first queries were performed in peakhours. A memory intensive operation takes 7.5 times longer during heavy loads. Doesn't this suggest that swapping of working memory is occurring? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] seq scan cache vs. index cache smackdown
> Magnus Hagander wrote: > > I don't think that's correct either. Scatter/Gather I/O is used to SQL > > Server can issue reads for several blocks from disks into it's own > > buffer cache with a single syscall even if these buffers are not > > sequential. It did make significant performance improvements when they > > added it, though. > > > > (For those not knowing - it's ReadFile/WriteFile where you pass an array > > of "this many bytes to this address" as parameters) > > Isn't that like the BSD writev()/readv() that Linux supports also? Is > that something we should be using on Unix if it is supported by the OS? readv and writev are in the single unix spec...and yes they are basically just like the win32 versions except that that are synchronous (and therefore better, IMO). On some systems they might just be implemented as a loop inside the library, or even as a macro. http://www.opengroup.org/onlinepubs/007908799/xsh/sysuio.h.html On operating systems that optimize vectored read operations, it's pretty reasonable to assume good or even great performance gains, in addition to (or instead of) recent changes to xlog.c to group writes together for a file...it just takes things one stop further. Is there a reason why readv/writev have not been considered in the past? Merlin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] seq scan cache vs. index cache smackdown
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Is there a reason why readv/writev have not been considered in the past? Lack of portability, and lack of obvious usefulness that would justify dealing with the lack of portability. I don't think there's any value in trying to write ordinary buffers this way; making the buffer manager able to write multiple buffers at once sounds like a great deal of complexity and deadlock risk in return for not much. It might be an alternative to the existing proposed patch for writing multiple WAL buffers at once, but frankly I consider that patch a waste of effort. In real scenarios you seldom get to write more than one WAL page without a forced sync occurring because someone committed. Even if *your* transaction is long, any other backend committing a small transaction still fsyncs. On top of that, the bgwriter will be flushing WAL in order to maintain the write-ahead rule any time it dumps a dirty buffer. I have a personal to-do item to make the bgwriter explicitly responsible for writing completed WAL pages as part of its duties, but I haven't done anything about it because I think that it will write lots of such pages without any explicit code, thanks to the bufmgr's LSN interlock. Even if it doesn't get it done that way, the simplest answer is to add a little bit of code to make sure bgwriter generally does the writes, and then we don't care. If you want to experiment with writev, feel free, but I'll want to see demonstrable performance benefits before any such code actually goes in. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] bad performances using hashjoin
Quoting Tom Lane <[EMAIL PROTECTED]>: > Klint Gore <[EMAIL PROTECTED]> writes: > > Is having an order by in a view legal? > > Not according to the SQL spec, but PG has allowed it for several releases. > (The same goes for ORDER BY in a sub-select, which is actually pretty > much the same thing ...) Umm... if you implement LIMIT in a subselect, it becomes highly meaningful (nad useful. Is this a case of one nonstandard feature being the thin edge of the wedge for another? -- "Dreams come true, not free." ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function
[EMAIL PROTECTED] wrote: I would suspect a DBI/DBD installation issue, either perl DBI cannot find DBD-Pg (not installed ?) or DBD-Pg cannot find your Pg 7.4.5. I note that FC3 comes with Pg 7.4.6 - did you installed 7.4.5 from source? If so this could be why the perl database modules cannot find it (you may need to rebuild DBD-Pg, telling it where your Pg install is). I installed FC3 from rpm kernel 2.6.9 which already included postgresql 7.4.5 . Suppose that there were some missing component , what should be the missing rpm component which I forgot to install ? Ok - I must be looking at the *updated* FC3 distribution... I may have 'jumped the gun' a little - the situation I describe above will prevent *any* access at all to Pg from webmin. If this is the case then check you have (perl) DBI and (perl) DBD-Pg components installed. If on the other hand you can do *some* Pg admin from webmin, and you are only having problems with the grants then there is something it does not like about the *particular* statement. The way to debug this is to do a tiny perl DBI program that tries to execute the statement : select relname, relacl from pg_class where (relkind = 'r' OR relkind = 'S') and relname !~ '^pg_' order by relname So - sorry to confuse, but let us know which situation you have there :-) best wishes Mark ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function
Mark Kirkwood wrote:
If on the other hand you can do *some* Pg admin from webmin, and you are
only having problems with the grants then there is something it does not
like about the *particular* statement. The way to debug this is to do a
tiny perl DBI program that tries to execute the statement :
select relname, relacl from pg_class where (relkind = 'r' OR relkind =
'S') and relname !~ '^pg_' order by relname
I did a quick check of this case... seems to be no problem running this
statement using perl 5.8.5, DBI-1.42 and DBD-Pg-1.22. You might like to
try out the attached test program that does this (You may have to add a
password in order to connect, depending on your security settings).
Mark
#!/usr/bin/perl -w
#
# relacl.pl : testbed for
#
use DBI;
use strict;
my $db = "dbname=template1;port=5432";
my $user= "postgres";
my $pwd = "";
my $dsn = "DBI:Pg:$db";
my $con;
my $sql = "select relname, relacl from pg_class where " .
"(relkind = 'r' OR relkind = 'S') and relname
!~ '^pg_' " .
"order by relname";
my $sth;
my @row;
$con = DBI->connect($dsn,$user,$pwd)
or die "Error in connect to $dsn: $!\n";
$sth = $con->prepare($sql)
or die "Error in prepare : $!";
$sth->execute()
or die "Error in execute : $!";
print "Relname\t\tRelacl\n";
while ( @row = $sth->fetchrow_array() ) {
print $row[0] . "\t" . $row[1] . "\n";
}
$sth->finish();
$con->disconnect();
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] seq scan cache vs. index cache smackdown
Merlin Moncure wrote:
readv and writev are in the single unix spec...and yes ...
On some systems they might just be implemented as a loop inside the
library, or even as a macro.
You sure?
Requirements like this:
http://www.opengroup.org/onlinepubs/007908799/xsh/write.html
"Write requests of {PIPE_BUF} bytes or less will not be
interleaved with data from other processes doing writes
on the same pipe."
make me think that it couldn't be just a macro; and if it
were a loop in the library it seems it'd still have to
make sure it's done with a single write system call.
(yeah, I know that requirement is just for pipes; and I
suppose they could write a loop for normal files and a
different special case for pipes; but I'd be surprised).
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function
> Ok - I must be looking at the *updated* FC3 distribution...
>
> I may have 'jumped the gun' a little - the situation I describe above
> will prevent *any* access at all to Pg from webmin. If this is the case
> then check you have (perl) DBI and (perl) DBD-Pg components installed.
>
> If on the other hand you can do *some* Pg admin from webmin, and you are
> only having problems with the grants then there is something it does not
> like about the *particular* statement. The way to debug this is to do a
> tiny perl DBI program that tries to execute the statement :
>
> select relname, relacl from pg_class where (relkind = 'r' OR relkind =
> 'S') and relname !~ '^pg_' order by relname
>
> So - sorry to confuse, but let us know which situation you have there :-)
>
> best wishes
>
> Mark
>
I used you perl script and found the error =>
[EMAIL PROTECTED] tmp]# perl relacl.pl
DBI connect('dbname=template1;port=5432','postgres',...) failed: FATAL: IDENT
authentication failed for user "postgres" at relacl.pl line 21
Error in connect to DBI:Pg:dbname=template1;port=5432:
And my pg_hba.conf is
# IPv4-style local connections:
hostall all 127.0.0.1 255.255.255.255 trust
hostall all 192.168.0.0 255.255.0.0 trust
trusted for every user.
Would you give me an idea what's wrong?
Thanks .
Amrit,Thailand
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function
[EMAIL PROTECTED] wrote:
I used you perl script and found the error =>
[EMAIL PROTECTED] tmp]# perl relacl.pl
DBI connect('dbname=template1;port=5432','postgres',...) failed: FATAL: IDENT
authentication failed for user "postgres" at relacl.pl line 21
Error in connect to DBI:Pg:dbname=template1;port=5432:
Excellent - we know what is going on now!
And my pg_hba.conf is
# IPv4-style local connections:
hostall all 127.0.0.1 255.255.255.255 trust
hostall all 192.168.0.0 255.255.0.0 trust
trusted for every user.
Ok, what I think has happened is that there is another Pg installation
(or another initdb'ed cluster) on this machine that you are accidentally
talking to. Try
$ rpm -qa|grep -i postgres
which will spot another software installation, you may just have to
search for files called pg_hba.conf to find another initdb'ed cluster
This other installation should have a pg_hba.conf that looks something
like :
local all allident
hostall all 127.0.0.1 255.255.255.255 ident
So a bit of detective work is in order :-)
Mark
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
