Обсуждение: Slow delete/insert.

Поиск
Список
Период
Сортировка

Slow delete/insert.

От
Thor Tall
Дата:
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

Re: Slow delete/insert.

От
"Joshua D. Drake"
Дата:
> 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/



Re: Slow delete/insert.

От
Thor Tall
Дата:
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

Re: Slow delete/insert.

От
"Alexandre Leclerc"
Дата:
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

Re: Slow delete/insert.

От
Thor Tall
Дата:
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

Re: Slow delete/insert.

От
"Alexandre Leclerc"
Дата:
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

Re: Slow delete/insert.

От
"Alexandre Leclerc"
Дата:
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