Re: [PERFORM] performance hit for replication

2005-04-13 Thread Dave Page
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Matthew Nuzum Sent: 12 April 2005 17:25 To: pgsql-performance@postgresql.org Subject: [PERFORM] performance hit for replication So, my question is this: My server currently works great,

[PERFORM] speed of querry?

2005-04-13 Thread Joel Fradkin
I must be missing something important, because I am just not seeing why this query is slower on a 4 processor 8 gig machine running redhat AS4. The SQL: explain analyze SELECT a.clientnum, a.associateid, a.associatenum, a.lastname, a.firstname, jt.value AS jobtitle, l.name AS location,

[PERFORM] Foreign keys and partial indexes

2005-04-13 Thread Nichlas Löfdahl
Hello! I have a partial index (btree(col) WHERE col 0) on table2 ('col' contains alot of NULL-values). There's also a foreign key on the column pointing to the primary key of table1 (ON UPDATE CASCADE ON DELETE SET NULL). During update/delete, it seems like it cannot use the partial index

Re: [PERFORM] Foreign keys and partial indexes

2005-04-13 Thread Tom Lane
Nichlas =?iso-8859-1?Q?L=F6fdahl?= [EMAIL PROTECTED] writes: I have a partial index (btree(col) WHERE col 0) on table2 ('col' contains alot of NULL-values). There's also a foreign key on the column pointing to the primary key of table1 (ON UPDATE CASCADE ON DELETE SET NULL). During

Re: [PERFORM] [NOVICE] Many connections lingering

2005-04-13 Thread Mark Lewis
If there are potentially hundreds of clients at a time, then you may be running into the maximum connection limit. In postgresql.conf, there is a max_connections setting which IIRC defaults to 100. If you try to open more concurrent connections to the backend than that, you will get a connection

Re: [PERFORM] [NOVICE] Many connections lingering

2005-04-13 Thread John DeSoi
On Apr 13, 2005, at 1:09 AM, Slavisa Garic wrote: This is not a Windows server. Both server and client are the same machine (done for testing purposes) and it is a Fedora RC2 machine. This also happens on debian server and client in which case they were two separate machines. There are thousands

Re: [PERFORM] speed of querry?

2005-04-13 Thread Richard Huxton
Joel Fradkin wrote: I must be missing something important, because I am just not seeing why this query is slower on a 4 processor 8 gig machine running redhat AS4. Well, the 4 processors aren't going to help with a single query. However, assuming the configurations for both machines are

Re: [PERFORM] [NOVICE] Many connections lingering

2005-04-13 Thread Richard Huxton
Slavisa Garic wrote: This is a serious problem for me as there are multiple users using our software on our server and I would want to avoid having connections open for a long time. In the scenario mentioned below I haven't explained the magnitute of the communications happening between Agents and

[PERFORM] PLM pulling from CVS nightly for testing in STP

2005-04-13 Thread Mark Wong
Hi all, Just wanted everyone to know what we're pulling CVS HEAD nightly so it can be tested in STP now. Let me know if you have any questions. Tests are not automatically run yet, but I hope to remedy that shortly. For those not familiar with STP and PLM, here are a couple of links: STP

Re: [PERFORM] PLM pulling from CVS nightly for testing in STP

2005-04-13 Thread Josh Berkus
Mark, Just wanted everyone to know what we're pulling CVS HEAD nightly so it can be tested in STP now. Let me know if you have any questions. Way cool.How do I find the PLM number? How are you nameing these? -- --Josh Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] speed of querry?

2005-04-13 Thread Joel Fradkin
are you sure the query was identical in each case. I just ran a second time same results ensuring that the query is the same. Not sure why it is doing a column10 thing. Any ideas what to look for? Both data bases are a restore from the same backup file. One is running redhat the other XP, I

Re: [PERFORM] PLM pulling from CVS nightly for testing in STP

2005-04-13 Thread Mark Wong
convention I'm using is postgresql-MMDD, for example postgresql-20050413, for the anonymous cvs export from today (April 13). I have a cronjob that'll do the export at 1AM PST8PDT. The search page for the PLM numbers is here: https://www.osdl.org/plm-cgi/plm?module=search or you can

[PERFORM] Problem with slow query (caused by improper nestloop?)

2005-04-13 Thread Tom Mack
Someone (twanger) sent me here from the IRC channel with the following: I have a query that normally takes 0.150 seconds, but after an insert can take 14 seconds. Here's the scenario: Run this query: select * from cad_part left join smart_part using (cannon_part_id)

[PERFORM] Strange serialization problem

2005-04-13 Thread Mischa Sandberg
I have a performance problem; I'd like any suggestions on where to continue investigation. A set of insert-only processes seems to serialize itself. :-( The processes appear to be blocked on disk IO, and probably the table drive, rather than the pg_xlog drive. Each process is inserting a block

Re: [PERFORM] Compressing WAL

2005-04-13 Thread Jim C. Nasby
On Sun, Apr 10, 2005 at 09:12:41PM -0400, Bruce Momjian wrote: I have never heard anyone talk about it, but it seems useful. I think compressing the page images written on first page modification since checkpoint would be a big win. Could you clarify that? Maybe I'm being naive, but it seems

Re: [PERFORM] Compressing WAL

2005-04-13 Thread Simon Riggs
On Sun, 2005-04-10 at 21:12 -0400, Bruce Momjian wrote: Jim C. Nasby wrote: Maybe better for -hackers, but here it goes anyway... Has anyone looked at compressing WAL's before writing to disk? On a system generating a lot of WAL it seems there might be some gains to be had WAL data

Re: [PERFORM] [NOVICE] Many connections lingering

2005-04-13 Thread Slavisa Garic
HI Mark, My DBServer module already serves as a broker. At the moment it opens a new connection for every incoming Agent connection. I did it this way because I wanted to leave synchronisation to PGSQL. I might have to modify it a bit and use a shared, single connection for all agents. I guess

Re: [PERFORM] [NOVICE] Many connections lingering

2005-04-13 Thread Slavisa Garic
Hi, This looks very interesting. I'll give it a better look and see if the performance penalties pgpool brings are not substantial in which case this program could be very helpful, Thanks for the hint, Slavisa On 4/14/05, Richard Huxton dev@archonet.com wrote: Slavisa Garic wrote: This is a