rows/second systained.
Just a point of reference. I'm trying to collect some data so that I
can provide some charts of the degredation, hoping to find the point
where it dies and thereby find the point where it needs attention.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED
On Apr 1, 2005 3:42 PM, Tom Lane [EMAIL PROTECTED] wrote:
Christopher Petrilli [EMAIL PROTECTED] writes:
I can start at about 4,000 rows/second, but at about 1M rows, it
plummets, and by 4M it's taking 6-15 seconds to insert 1000 rows.
That's only about 15 rows/second, which is quite
than appreciated.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get
On Apr 1, 2005 3:59 PM, Christopher Petrilli [EMAIL PROTECTED] wrote:
On Apr 1, 2005 3:53 PM, Joshua D. Drake [EMAIL PROTECTED] wrote:
What seems to happen is it slams into a wall of some sort, the
system goes into disk write frenzy (wait=90% CPU), and eventually
recovers and starts
On Apr 4, 2005 11:52 AM, Tom Lane [EMAIL PROTECTED] wrote:
Christopher Petrilli [EMAIL PROTECTED] writes:
The table has:
* 21 columns (nothing too strange)
* No OIDS
* 5 indexes, including the primary key on a string
Could we see the *exact* SQL definitions of the table
On Apr 4, 2005 12:23 PM, Tom Lane [EMAIL PROTECTED] wrote:
Christopher Petrilli [EMAIL PROTECTED] writes:
On Apr 4, 2005 11:52 AM, Tom Lane [EMAIL PROTECTED] wrote:
Could we see the *exact* SQL definitions of the table and indexes?
Of course, this is a bit cleansed, since it's an internal
On Apr 4, 2005 3:46 PM, Simon Riggs [EMAIL PROTECTED] wrote:
On Mon, 2005-04-04 at 09:48 -0400, Christopher Petrilli wrote:
The point, in the rough middle, is where the program begins inserting
into a new table (inherited). The X axis is the total number of rows
inserted.
and you also
8192 Apr 4 12:26 26488339
-rw--- 1 pgsql pgsql 60045 Apr 4 12:24 pg_internal.init
-rw--- 1 pgsql pgsql 4 Apr 4 12:24 PG_VERSION
Hopefully this helps.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast
slip 1 loop, which isn't
relevent).
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
without the auxilary indices. I only
have the primary key index. So far, a quick scan with the eye says
that it's behaving better, but beginning to have issues again. I'll
post results as soon as they are done.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end
/3000
* 2GB RAM (was 1GB, has made no difference)
* 1 x 120GB SATA drive (w/WAL), 7200RPM Seagate
* 1 x 160GB SATA drive (main), 7200RPM Seagate
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze
On Apr 4, 2005 10:36 PM, Tom Lane [EMAIL PROTECTED] wrote:
Christopher Petrilli [EMAIL PROTECTED] writes:
On Apr 4, 2005 12:23 PM, Tom Lane [EMAIL PROTECTED] wrote:
do a test run with *no* indexes on the table, just to see if it behaves
any differently? Basically I was wondering if index
tells me the system is pegged in its performance on IO. This is
not surprising.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
. I've restarted the run, with fsync turned
on (fdatasync), and we'll see.
My fear is that it's some bizarre situation interacting with both
issues, and one that might not be solvable. Does anyone else have
much experience with this sort of sustained COPY?
Chris
--
| Christopher Petrilli
| [EMAIL
concerned about that many tables when it comes to
query time. I assume this will kick in the genetic optimizer?
Anyway, where most of this started was with Christopher's comments:
On Fri, 2005-04-01 at 14:38 -0500, Christopher Petrilli wrote:
This was an application originally written
, but
looses in the end.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
*Loader for this application, you
should use the COPY syntax for PostgreSQL. You will find it a lot
faster. I have used it by building the input files and executing
'psql' with a COPY command, and also by using it with a subprocess,
both are quite effective.
Chris
--
| Christopher Petrilli
| [EMAIL
on disk and then kick off the psql process to run them?
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere
thing. I know a C++ app that I use
that runs SQL*Loader about once per second to deal with a HUGE volume
(10K/sec). In fact, moving the load files onto ramdisk has helped a
lot.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast
is that
this is anywhere from 5-10x faster than INSERT statements on the
whole, and sometimes 200x.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org
On 7/18/05, Tom Lane [EMAIL PROTECTED] wrote:
Christopher Petrilli [EMAIL PROTECTED] writes:
http://blog.amber.org/diagrams/comparison_mysql_pgsql.png
Notice the VERY steep drop off.
Hmm. Whatever that is, it's not checkpoint's fault. I would interpret
the regular upticks
On 7/18/05, Vivek Khera [EMAIL PROTECTED] wrote:
On Jul 17, 2005, at 1:08 PM, Christopher Petrilli wrote:
Normally, checkpoint_segments can help absorb some of that, but my
experience is that if I crank the number up, it simply delays the
impact, and when it occurs, it takes a VERY long
think this explains it.
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. Each COPY was 500 rows. Note that fsync
is turned off here. Maybe it'd be more stable with it turned on?
Chris
--
| Christopher
the trend
line. The data in the chart for v8.0.3 includes running pg_autovacuum
(5 minutes).
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
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
information can be extracted that way. If one of these
fixes your problem it'l give hints.
This could take a while :-)
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore
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
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
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 '/tmp
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
On 7/19/05, Christopher Petrilli [EMAIL PROTECTED] wrote:
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
On 7/19/05, Christopher Petrilli [EMAIL PROTECTED] wrote:
It looks like the CVS HEAD is definately better, but not by a huge
amount. The only difference is I wasn't run autovacuum in the
background (default settings), but I don't think this explains it.
Here's a graph of the differences
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
of historical reasons, but, that's just
hte way it is for now.
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
34 matches
Mail list logo
