Обсуждение: Slow delete/insert.
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
> 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/
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
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
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
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
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