Re: [PERFORM] Insert performance (OT?)

2005-07-19 Thread Yves Vindevogel
nobody ? On 18 Jul 2005, at 21:29, Yves Vindevogel wrote: Hi, Suppose I have a table with 4 fields (f1, f2, f3, f4) I define 2 unique indexes u1 (f1, f2, f3) and u2 (f1, f2, f4) I have 3 records A, B, C, D (this will be inserted) A, B, C, E (this will pass u2, but not u1, thus not inserted)

Re: [PERFORM] Insert performance (OT?)

2005-07-19 Thread Richard Huxton
Yves Vindevogel wrote: Hi, Suppose I have a table with 4 fields (f1, f2, f3, f4) I define 2 unique indexes u1 (f1, f2, f3) and u2 (f1, f2, f4) I have 3 records A, B, C, D (this will be inserted) A, B, C, E (this will pass u2, but not u1, thus not inserted) A, B, F, D (this will pass u1, but

Fwd: [PERFORM] Insert performance (OT?)

2005-07-19 Thread Yves Vindevogel
BTW: thank you for the idea Begin forwarded message: From: Yves Vindevogel [EMAIL PROTECTED]> Date: Tue 19 Jul 2005 12:20:34 CEST To: Richard Huxton dev@archonet.com> Subject: Re: [PERFORM] Insert performance (OT?) On 19 Jul 2005, at 11:39, Richard Huxton wrote: Yves Vindevogel wrote: Hi,

Re: [PERFORM] Insert performance (OT?)

2005-07-19 Thread Richard Huxton
Yves Vindevogel wrote: So, I must use a function that will check against u1 and u2, and then insert if it is ok. I know that such a function is way slower that my insert query. So - you have a table, called something like upload with 20,000 rows and you'd like to know whether it is safe to

Re: [PERFORM] Insert performance (OT?)

2005-07-19 Thread Yves Vindevogel
I will use 2 queries. They run within a function fnUpload(), so I'm going to keep it simple. On 19 Jul 2005, at 12:51, Richard Huxton wrote: Yves Vindevogel wrote: >>> So, I must use a function that will check against u1 and u2, and then insert if it is ok. I know that such a function is way

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Christopher Petrilli
On 7/18/05, Tom Lane [EMAIL PROTECTED] wrote: The table has 15 columns, 5 indexes (character, inet and timestamp). No foreign keys. The only other thing running on the machine was the application actually DOING the benchmarking, written in Python (psycopg), but it was, according to top,

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: Here's a graph of the differences and density of behavior: http://blog.amber.org/diagrams/pgsql_copy_803_cvs.png I can provide the raw data. How about the complete test case? There's something awfully odd going on there, and I'd like to find

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC
What happens if, say at iteration 6000 (a bit after the mess starts), you pause it for a few minutes and resume. Will it restart with a plateau like at the beginning of the test ? or not ? What if, during this pause, you disconnect and reconnect, or restart the postmaster, or vacuum, or

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Christopher Petrilli
On 7/19/05, PFC [EMAIL PROTECTED] wrote: What happens if, say at iteration 6000 (a bit after the mess starts), you pause it for a few minutes and resume. Will it restart with a plateau like at the beginning of the test ? or not ? Not sure... my benchmark is designed to represent

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: On 7/19/05, PFC [EMAIL PROTECTED] wrote: What happens if, say at iteration 6000 (a bit after the mess starts), you pause it for a few minutes and resume. Will it restart with a plateau like at the beginning of the test ? or not ? Not sure... my

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC
total. If the insertion pattern is sufficiently random that the entire index ranges are hot then you might not have enough RAM. Try doing the test dropping some of your indexes and see if it moves the number of iterations after which it becomes slow. ---(end of

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC
I think PFC's question was not directed towards modeling your application, but about helping us understand what is going wrong (so we can fix it). Exactly, I was wondering if this delay would allow things to get flushed, for instance, which would give information about the problem (if

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Christopher Petrilli
On 7/19/05, Tom Lane [EMAIL PROTECTED] wrote: Christopher Petrilli [EMAIL PROTECTED] writes: Not sure... my benchmark is designed to represent what the database will do under typical circumstances, and unfortunately these are typical for the application. However, I can see about adding

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Christopher Petrilli
On 7/19/05, PFC [EMAIL PROTECTED] wrote: I think PFC's question was not directed towards modeling your application, but about helping us understand what is going wrong (so we can fix it). Exactly, I was wondering if this delay would allow things to get flushed, for

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: On 7/19/05, Tom Lane [EMAIL PROTECTED] wrote: I'm suddenly wondering if the performance dropoff corresponds to the point where the indexes have grown large enough to not fit in shared buffers anymore. If I understand correctly, the

Re: [PERFORM] Huge performance problem between 7.4.1 and 8.0.3 - CS issue?

2005-07-19 Thread Robert Creager
On Mon, 18 Jul 2005 13:52:53 -0400 Tom Lane [EMAIL PROTECTED] wrote: Start a fresh psql session and SHOW vacuum_cost_delay to verify what the active setting is. Alright. Restarted the 803 database. Cron based vacuum analyze is running every 5 minutes. vacuum_cost_delay is 0. The

Re: [PERFORM] Huge performance problem between 7.4.1 and 8.0.3 - CS issue?

2005-07-19 Thread Tom Lane
Robert Creager [EMAIL PROTECTED] writes: Alright. Restarted the 803 database. Cron based vacuum analyze is running every 5 minutes. vacuum_cost_delay is 0. The problem showed up after about 1/2 hour of running. I've got vacuum jobs stacked from the last 35 minutes, with 2 vacuums running

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Christopher Petrilli
As I'm doing this, I'm noticing something *VERY* disturbing to me: postmaster backend: 20.3% CPU psql frontend: 61.2% CPU WTF? The only thing going through the front end is the COPY command, and it's sent to the backend to read from a file? Chris -- | Christopher Petrilli | [EMAIL PROTECTED]

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: As I'm doing this, I'm noticing something *VERY* disturbing to me: postmaster backend: 20.3% CPU psql frontend: 61.2% CPU WTF? The only thing going through the front end is the COPY command, and it's sent to the backend to read from a file?

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Christopher Petrilli
On 7/19/05, Tom Lane [EMAIL PROTECTED] wrote: Christopher Petrilli [EMAIL PROTECTED] writes: As I'm doing this, I'm noticing something *VERY* disturbing to me: postmaster backend: 20.3% CPU psql frontend: 61.2% CPU WTF? The only thing going through the front end is the COPY command,

[PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
Hi, I'm running Postgres 7.4.6 on a dedicated server with about 1.5gigs of ram. Running scripts locally, it takes about 1.5x longer than mysql, and the load on the server is only about 21%. I upped the sort_mem to 8192 (kB), and shared_buffers and effective_cache_size to 65536 (512MB), but neither

Re: [PERFORM] Looking for tips

2005-07-19 Thread Joshua D. Drake
Oliver Crosby wrote: Hi, I'm running Postgres 7.4.6 on a dedicated server with about 1.5gigs of ram. Running scripts locally, it takes about 1.5x longer than mysql, and the load on the server is only about 21%. What queries? What is your structure? Have you tried explain analyze? How many

Re: [PERFORM] Looking for tips

2005-07-19 Thread John A Meinel
Oliver Crosby wrote: Hi, I'm running Postgres 7.4.6 on a dedicated server with about 1.5gigs of ram. Running scripts locally, it takes about 1.5x longer than mysql, and the load on the server is only about 21%. I upped the sort_mem to 8192 (kB), and shared_buffers and effective_cache_size to

Re: [PERFORM] Huge performance problem between 7.4.1 and 8.0.3 - CS issue?

2005-07-19 Thread Robert Creager
On Tue, 19 Jul 2005 12:54:22 -0400 Tom Lane [EMAIL PROTECTED] wrote: Hmm, I hadn't thought about the possible impact of multiple concurrent vacuums. Is the problem caused by that, or has performance already gone into the tank by the time the cron-driven vacuums are taking long enough to

Re: [PERFORM] Looking for tips

2005-07-19 Thread Richard Huxton
Oliver Crosby wrote: Hi, I'm running Postgres 7.4.6 on a dedicated server with about 1.5gigs of ram. Running scripts locally, it takes about 1.5x longer than mysql, and the load on the server is only about 21%. What scripts? What do they do? Oh, and 7.4.8 is the latest release - worth

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: Are you sure the backend is reading directly from the file, and not through psql? (\copy, or COPY FROM STDIN, would go through psql.) The exact command is: COPY test (columnlist...) FROM '/tmp/loadfile'; I tried to replicate this by putting a

Re: [PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
I was hoping to start with tuning postgres to match the hardware, but in any case.. The queries are all simple insert or select statements on single tables. Eg. select x from table where y=?; or insert into table (a, b, c) values (?, ?, ?); In the case of selects where it's a large table, there's

[PERFORM] context-switching issue on Xeon

2005-07-19 Thread Sailer, Denis (YBUSA-CDR)
The thread below has the test case that we were able to use to reproduce the issue. http://archives.postgresql.org/pgsql-performance/2004-04/msg00280.php The last messages on this subject are from April of 2005. Has there been any successful ways to significantly reduce the impact

Re: [PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
Identify what the problem is first of all. Some things to consider: - Are there particular queries giving you trouble? - Is your load mostly reads or mostly writes? - Do you have one user or 100? - Are you block-loading data efficiently where necessary? - Have you indexed both sides of

Re: [PERFORM] context-switching issue on Xeon

2005-07-19 Thread David Hodgkinson
FWIW, I'm seeing this with a client at the moment. 40-60k CS per second on Dual 3.2GHz. There are plenty of other issues we're dealing with, but this is obviously disconcerting... On 19 Jul 2005, at 19:23, Sailer, Denis (YBUSA-CDR) wrote: The thread below has the test case that we were

Re: [PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
What programming language are these scripts written in ? perl. using the DBD:Pg interface instead of command-lining it through psql ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] context-switching issue on Xeon

2005-07-19 Thread Tom Lane
Sailer, Denis (YBUSA-CDR) [EMAIL PROTECTED] writes: http://archives.postgresql.org/pgsql-performance/2004-04/msg00280.php The last messages on this subject are from April of 2005. Has there been any successful ways to significantly reduce the impact this has to multi-processing? CVS tip

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Christopher Petrilli
On 7/19/05, Tom Lane [EMAIL PROTECTED] wrote: Christopher Petrilli [EMAIL PROTECTED] writes: Are you sure the backend is reading directly from the file, and not through psql? (\copy, or COPY FROM STDIN, would go through psql.) The exact command is: COPY test (columnlist...) FROM

Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC
What programming language are these scripts written in ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: On 7/19/05, Tom Lane [EMAIL PROTECTED] wrote: How *exactly* are you invoking psql? It is a subprocess of a Python process, driven using a pexpect interchange. I send the COPY command, then wait for the '=#' to come back. Some weird interaction

Re: [PERFORM] Looking for tips

2005-07-19 Thread Kevin Grittner
Hi Oliver, We had low resource utilization and poor throughput on inserts of thousands of rows within a single database transaction. There were a lot of configuration parameters we changed, but the one which helped the most was wal_buffers -- we wound up setting it to 1000. This may be higher

Re: [PERFORM] Looking for tips

2005-07-19 Thread Tom Lane
Oliver Crosby [EMAIL PROTECTED] writes: The queries are all simple insert or select statements on single tables. Eg. select x from table where y=?; or insert into table (a, b, c) values (?, ?, ?); In the case of selects where it's a large table, there's an index on the column being searched,

Re: [PERFORM] Looking for tips

2005-07-19 Thread Steinar H. Gunderson
On Tue, Jul 19, 2005 at 03:01:00PM -0400, Tom Lane wrote: You could possibly get some improvement if you can re-use prepared plans for the queries; but this will require some fooling with the client code (I'm not sure if DBD::Pg even has support for it at all). Newer versions has, when

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC
It is a subprocess of a Python process, driven using a pexpect interchange. I send the COPY command, then wait for the '=#' to come back. did you try sending the COPY as a normal query through psycopg ? ---(end of broadcast)--- TIP 9:

Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC
I can't say wether MySQL is faster for very small queries (like SELECT'ing one row based on an indexed field). That's why I was asking you about the language... I assume you're using a persistent connection. For simple queries like this, PG 8.x seemed to be a lot faster

Re: [PERFORM] Looking for tips

2005-07-19 Thread Tom Lane
Oliver Crosby [EMAIL PROTECTED] writes: You could possibly get some improvement if you can re-use prepared plans for the queries; but this will require some fooling with the client code (I'm not sure if DBD::Pg even has support for it at all). Aye. We have prepared statements. Ah, but are

Re: [PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
If you're running only a single query at a time (no multiple clients), then this is pretty much the definition of a MySQL-friendly workload; I'd have to say we are doing really well if we are only 50% slower. Postgres doesn't have any performance advantages until you get into complex queries

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread Christopher Petrilli
On 7/19/05, Tom Lane [EMAIL PROTECTED] wrote: Christopher Petrilli [EMAIL PROTECTED] writes: On 7/19/05, Tom Lane [EMAIL PROTECTED] wrote: How *exactly* are you invoking psql? It is a subprocess of a Python process, driven using a pexpect interchange. I send the COPY command, then wait

Re: [PERFORM] Looking for tips

2005-07-19 Thread Steinar H. Gunderson
On Tue, Jul 19, 2005 at 03:16:31PM -0400, Tom Lane wrote: Ah, but are they really prepared, or is DBD::Pg faking it by inserting parameter values into the query text and then sending the assembled string as a fresh query? They are really prepared. /* Steinar */ -- Homepage:

Re: [PERFORM] Huge performance problem between 7.4.1 and 8.0.3 - CS issue?

2005-07-19 Thread Robert Creager
On Tue, 19 Jul 2005 12:54:22 -0400 Tom Lane [EMAIL PROTECTED] wrote: Robert Creager [EMAIL PROTECTED] writes: Hmm, I hadn't thought about the possible impact of multiple concurrent vacuums. Is the problem caused by that, or has performance already gone into the tank by the time the

Re: [PERFORM] Looking for tips

2005-07-19 Thread Jeff Trout
On Jul 19, 2005, at 3:36 PM, Steinar H. Gunderson wrote: On Tue, Jul 19, 2005 at 03:16:31PM -0400, Tom Lane wrote: Ah, but are they really prepared, or is DBD::Pg faking it by inserting parameter values into the query text and then sending the assembled string as a fresh query? They are

Re: [PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
We had low resource utilization and poor throughput on inserts of thousands of rows within a single database transaction. There were a lot of configuration parameters we changed, but the one which helped the most was wal_buffers -- we wound up setting it to 1000. This may be higher than it

Re: [PERFORM] Looking for tips

2005-07-19 Thread Dawid Kuroczko
On 7/19/05, Oliver Crosby [EMAIL PROTECTED] wrote: We had low resource utilization and poor throughput on inserts of thousands of rows within a single database transaction. There were a lot of configuration parameters we changed, but the one which helped the most was wal_buffers -- we

Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC
PS: Where can I find benchmarks comparing PHP vs Perl vs Python in terms of speed of executing prepared statements? I'm afraid you'll have to do these yourself ! And, I don't think the Python drivers support real prepared statements (the speed of psycopy is really good though). I

Re: [PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
If it is possible try: 1) wrapping many inserts into one transaction (BEGIN;INSERT;INSERT;...INSERT;COMMIT;). As PostgreSQL will need to handle less transactions per second (each your insert is a transaction), it may work faster. Aye, that's what I have it doing right now. The transactions

Re: [PERFORM] Looking for tips

2005-07-19 Thread Christopher Weimann
On 07/19/2005-02:41PM, Oliver Crosby wrote: No queries in particular appear to be a problem. That could mean they are ALL a problem. Let see some EXPLAIN ANAYZE results just to rule it out. At the moment it's just one user, With 1 user PostgreSQL will probobaly never beat MySQL but with

Re: [PERFORM] Looking for tips

2005-07-19 Thread Sven Willenberger
On Tue, 2005-07-19 at 16:28 -0400, Oliver Crosby wrote: If it is possible try: 1) wrapping many inserts into one transaction (BEGIN;INSERT;INSERT;...INSERT;COMMIT;). As PostgreSQL will need to handle less transactions per second (each your insert is a transaction), it may work faster.

Re: [PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
since triggers work with COPY, you could probably write a trigger that looks for this condition and does the ID processsing you need; you could thereby enjoy the enormous speed gain resulting from COPY and maintain your data continuity. So... (bear with me here.. trying to make sense of

[PERFORM] performance decrease after reboot

2005-07-19 Thread John Mendenhall
I tuned a query last week to obtain acceptable performance. Here is my recorded explain analyze results: - LOG: duration: 826.505 ms statement: explain analyze SELECT c.id AS contact_id, sr.id AS sales_rep_id, LTRIM(RTRIM(sr.firstname || ' ' || sr.lastname)) AS

Re: [PERFORM] Looking for tips

2005-07-19 Thread Sven Willenberger
On Tue, 2005-07-19 at 17:04 -0400, Oliver Crosby wrote: since triggers work with COPY, you could probably write a trigger that looks for this condition and does the ID processsing you need; you could thereby enjoy the enormous speed gain resulting from COPY and maintain your data

Re: [PERFORM] join and query planner

2005-07-19 Thread Dario
I'll try that. Let you know as soon as I can take a look. Thank you- -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Kevin Grittner Enviado el: lunes, 18 de julio de 2005 17:48 Para: pgsql-performance@postgresql.org; [EMAIL PROTECTED] Asunto: Re: [PERFORM]

Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC
You could have a program pre-parse your log and put it in a format understandable by COPY, then load it in a temporary table and write a part of your application simply as a plpgsql function, reading from this table and doing queries (or a plperl function)... So... (bear with me here..

Re: [PERFORM] Looking for tips

2005-07-19 Thread Oliver Crosby
Sorry for the lack of specifics... We have a file generated as a list of events, one per line. Suppose lines 1,2,3,5,7,11,etc were related, then the last one would specify that it's the last event. Gradually this gets assembled by a perl script and when the last event is encountered, it gets

Re: [PERFORM] Huge performance problem between 7.4.1 and 8.0.3 - CS

2005-07-19 Thread Robert Creager
When grilled further on (Tue, 19 Jul 2005 12:09:51 -0600), Robert Creager [EMAIL PROTECTED] confessed: On Tue, 19 Jul 2005 12:54:22 -0400 Tom Lane [EMAIL PROTECTED] wrote: Hmm, I hadn't thought about the possible impact of multiple concurrent vacuums. Is the problem caused by that, or