pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow delete/insert.
7+ messages / 3 participants
[nested] [flat]
* Slow delete/insert.
@ 2006-08-23 14:30 Thor Tall <tall_thor@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Thor Tall @ 2006-08-23 14:30 UTC (permalink / raw)
To: pgsql-admin
I am in the process of converting an old system to a
new system where I have chosen to use postgres in
stead of a home grow system based on b-trees.
The system receives 2650 message a total of 10Mbytes
of data per 15 minutes this information have to be
store in 4 tables in the database. Some of the
messages are updates to existing data and the result
is:
1038 records in table 1 size per record 282 bytes
2074 records in table 2 size per record 36 bytes
21488 records in table 3 size per record 60 bytes
25676 records in table 4 size per record 42 bytes.
Without indexes this should be something like
2,7Mbytes/15min.
The disk speed is as follows taken from hdparm -Tt
/dev/hda:
Timing buffer-cache reads: 128 MB in 0.89 seconds
=143.82 MB/sec
Timing buffered disk reads: 64 MB in 1.45 seconds =
44.14 MB/sec
My problem is that the postmaster process is using
between 70-90% of the CPU time. (seen using top) and
the total wall time with nothing else running.
It takes 6 minutes to ingest the data with an empty
database and 25 minutes (wall time) if all the data is
already in the database.
The processing is done as follows:
1. Start transaction
2. check if message is in table 1 and if so delete
records from table 1(1 row),2(2 rows),3(30 rows),4(50
rows).
3. split the message and insert the date in table 1(1
row),2(2 rows),3(30 rows),4(50 rows)
4. end transaction
I need some suggestions so that I can ingest the data
faster. with the same computer and disks. We are
running redhat 7.2 on the server and I am using the c
interface to write the data to the server.
Thanks for any help,
Thor.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow delete/insert.
@ 2006-08-23 14:59 Joshua D. Drake <jd@commandprompt.com>
parent: Thor Tall <tall_thor@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Joshua D. Drake @ 2006-08-23 14:59 UTC (permalink / raw)
To: Thor Tall <tall_thor@yahoo.com>; +Cc: pgsql-admin
> It takes 6 minutes to ingest the data with an empty
> database and 25 minutes (wall time) if all the data is
> already in the database.
> The processing is done as follows:
> 1. Start transaction
>
> 2. check if message is in table 1 and if so delete
> records from table 1(1 row),2(2 rows),3(30 rows),4(50
> rows).
>
> 3. split the message and insert the date in table 1(1
> row),2(2 rows),3(30 rows),4(50 rows)
>
> 4. end transaction
Put more inserts into a single transaction
>
> I need some suggestions so that I can ingest the data
> faster. with the same computer and disks. We are
> running redhat 7.2 on the server and I am using the c
> interface to write the data to the server.
What version of PostgreSQL?
Joshua D. Drake
>
> Thanks for any help,
> Thor.
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow delete/insert.
@ 2006-08-24 08:24 Thor Tall <tall_thor@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Thor Tall @ 2006-08-24 08:24 UTC (permalink / raw)
To: Alexandre Leclerc <alexandre.leclerc@gmail.com>; pgsql-admin
Please see my comments below
--- Alexandre Leclerc <alexandre.leclerc@gmail.com>
wrote:
> 2006/8/23, Thor Tall <tall_thor@yahoo.com>:
> > The processing is done as follows:
> > 1. Start transaction
> >
> > 2. check if message is in table 1 and if so delete
> > records from table 1(1 row),2(2 rows),3(30
> rows),4(50
> > rows).
> >
> > 3. split the message and insert the date in table
> 1(1
> > row),2(2 rows),3(30 rows),4(50 rows)
> >
> > 4. end transaction
>
> In addition to the other post, the SQL requests can
> be the cause of
> the slow performance. I don't know how you do it,
> but the more you
> think the database way the better your SQL requests
> will be done.
> Also, are the tables auto-deleting the records using
> a real
> Relationnal approach in the DB (references fields
> with defined
> update/delete auto-events) or are they deleted
> manually by you with
> SQL requests?
The tables are deleted using cascaded deletes.
>
> The process can be very much slow of super-fast
> depending of the
> actual DB / Index / Table / Logic implementation and
> the actual
> programmer's SQL / Logic / requests.
>
> Best regards.
>
> --
> Alexandre Leclerc
>
I have the following simple tables which are populate:
CREATE TABLE tb_Flight (
FLIGHT_ID integer UNIQUE NOT NULL,
IFPLID varchar (20) NOT NULL ,
TIMESTAMP timestamp NULL ,
EVENT varchar (50) NULL ,
EVENTCLASS varchar (50) NULL ,
FLTSTATE varchar (50) NULL ,
ARCID varchar (10) NULL ,
ADEP varchar (4) NULL ,
ADES varchar (4) NULL ,
MODELTYP varchar (50) NULL ,
ARCTYP varchar (4) NULL ,
ETO_ADEP timestamp NULL ,
ETO_ADES timestamp NULL ,
ProcessTime_ms integer NULL ,
PRIMARY KEY (FLIGHT_ID)
);
CREATE SEQUENCE seq_Flight_Id;
CREATE TABLE tb_Flight_AD (
FLIGHT_ID integer REFERENCES tb_Flight ON DELETE
CASCADE,
AD_ID integer UNIQUE NOT NULL ,
AD varchar (4) NOT NULL ,
ETO timestamp NOT NULL
);
CREATE SEQUENCE seq_Flight_AD_Id;
CREATE TABLE tb_Flight_ASP (
FLIGHT_ID integer REFERENCES tb_Flight ON DELETE
CASCADE,
ASP_ID integer UNIQUE NOT NULL,
AIRSPDES varchar (20) NOT NULL ,
ETI timestamp NOT NULL ,
XTI timestamp NOT NULL
);
CREATE SEQUENCE seq_Flight_ASP_Id;
CREATE TABLE tb_Flight_RTEPTS (
FLIGHT_ID integer REFERENCES tb_Flight ON DELETE
CASCADE,
RTEPTS integer UNIQUE NOT NULL,
PT varchar (10) NOT NULL ,
ETO timestamp NOT NULL ,
Obsolete integer NOT NULL
);
CREATE SEQUENCE seq_Flight_RTEPTS_Id;
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow delete/insert.
@ 2006-08-24 12:08 Alexandre Leclerc <alexandre.leclerc@gmail.com>
parent: Thor Tall <tall_thor@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Alexandre Leclerc @ 2006-08-24 12:08 UTC (permalink / raw)
To: Thor Tall <tall_thor@yahoo.com>; +Cc: pgsql-admin
All this is prety straight forward.
As for the transaction you are crating, is the process of 'split
message' actually do on the DB side or are you doing this process on
you side? Maybe this is the actual work of splitting the message by
the DB that is very slow.
/Maybe/ if you do this data-processing on your side, then sending that
to the DB will save you time. If you really want that job being done
on the server side, you could build yourself an extension module
adding a special function that you would call to do the job. Maybe you
could win couple cycles...
But all that could be confirmed or not by someone else which knows
more about PG internals.
Best regards.
--
Alexandre Leclerc
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow delete/insert.
@ 2006-08-24 13:02 Thor Tall <tall_thor@yahoo.com>
parent: Alexandre Leclerc <alexandre.leclerc@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Thor Tall @ 2006-08-24 13:02 UTC (permalink / raw)
To: Alexandre Leclerc <alexandre.leclerc@gmail.com>; +Cc: pgsql-admin
The messages are split on the client side and PQexec
are called with INSERT/DELETE statements.
I have seen that I could use PQprepare and
PQexecPrepared but I doubt that it would help a lot to
use those functions?
/Thor
--- Alexandre Leclerc <alexandre.leclerc@gmail.com>
wrote:
> All this is prety straight forward.
>
> As for the transaction you are crating, is the
> process of 'split
> message' actually do on the DB side or are you doing
> this process on
> you side? Maybe this is the actual work of splitting
> the message by
> the DB that is very slow.
>
> /Maybe/ if you do this data-processing on your side,
> then sending that
> to the DB will save you time. If you really want
> that job being done
> on the server side, you could build yourself an
> extension module
> adding a special function that you would call to do
> the job. Maybe you
> could win couple cycles...
>
> But all that could be confirmed or not by someone
> else which knows
> more about PG internals.
>
> Best regards.
>
> --
> Alexandre Leclerc
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow delete/insert.
@ 2006-08-24 13:32 Alexandre Leclerc <alexandre.leclerc@gmail.com>
parent: Thor Tall <tall_thor@yahoo.com>
1 sibling, 0 replies; 7+ messages in thread
From: Alexandre Leclerc @ 2006-08-24 13:32 UTC (permalink / raw)
To: Thor Tall <tall_thor@yahoo.com>; +Cc: pgsql-admin
2006/8/24, Thor Tall <tall_thor@yahoo.com>:
> The messages are split on the client side and PQexec
> are called with INSERT/DELETE statements.
> I have seen that I could use PQprepare and
> PQexecPrepared but I doubt that it would help a lot to
> use those functions?
/Usually/, they are always called internally if not by the coder; it
is good practice to call them. It does optimize the speed.
--
Alexandre Leclerc
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow delete/insert.
@ 2006-08-24 13:35 Alexandre Leclerc <alexandre.leclerc@gmail.com>
parent: Thor Tall <tall_thor@yahoo.com>
1 sibling, 0 replies; 7+ messages in thread
From: Alexandre Leclerc @ 2006-08-24 13:35 UTC (permalink / raw)
To: Thor Tall <tall_thor@yahoo.com>; +Cc: pgsql-admin
By the way, I think the "performance" mailling list would be the ideal
place to post you question since it is all about optimisation (and not
management).
Best regards.
--
Alexandre Leclerc
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-08-24 13:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-23 14:30 Slow delete/insert. Thor Tall <tall_thor@yahoo.com>
2006-08-23 14:59 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-24 08:24 Re: Slow delete/insert. Thor Tall <tall_thor@yahoo.com>
2006-08-24 12:08 ` Alexandre Leclerc <alexandre.leclerc@gmail.com>
2006-08-24 13:02 ` Thor Tall <tall_thor@yahoo.com>
2006-08-24 13:32 ` Alexandre Leclerc <alexandre.leclerc@gmail.com>
2006-08-24 13:35 ` Alexandre Leclerc <alexandre.leclerc@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox