performance issue using DBI

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

performance issue using DBI

От:
"Nicolas Nolst" <nnolst@hotmail.com>
Дата:
Hi all,

I have developped a perl script to populate a database with two tables: sessions
and actions.

the table actions contains the following columns: session_id, url, timestamp.
The column session_id references to the table sessions.

the table sessions contains the following columns: session_id, remote_ip,
phone_type, phone_number. The column session_id is serial.

The lines of the table actions which are part of the same session have the same
session_id.

There are then more lines in the table actions than in the table session.


To fill the two tables, I first need to know if the session already exists for a
certain phone_type, a certain remote_ip and a certain phone_number:

SELECT session_id FROM sessions WHERE (phone_number = ?) AND (remote_ip  = ?)
AND (phone_type = ?) ORDER BY session_id;

I also need to apply a criteria to know if I have to add a new entry in the
table sessions or not:

SELECT (max(timestamp) + ?)<? FROM actions WHERE (session_id = ?);



If the session already exists I add a line in the table actions with a INSERT

If the session doesn't exist or if the criteria is true, I add a line in the
table sessions with an INSERT and then add  a line with a INSERT in the table actions (I use nextval and currval).

I have put indexes on sessions(session_id), sessions(msisdn),
actions(session_id) and actions(timestamp). I process one log file of about 20000 lines every day. All the lines are processed in one transaction (autocommit set to 0).

My problem is that populating my database is slower  when the data gets bigger
and the performance falls dramatically. I thought that is would be improve with
my indexes but the problem still persists.

Could you please give me some clues that could solve this issue.

Thanks.



Nicolas Nolst


MSN Photos is the easiest way to share and print your photos: Click Here

Re: performance issue using DBI

От:
nikolaus@dilger.cc
Дата:
Nicolas,

The more data your tables contain the more work
PostgreSQL needs to do.  Adding indexes on your tables
makes the INSERTS slower due to additional overhead. 
Indexes speed up SELECT but slow down INSERT. 
Therefore OLTP systems tend to have few indexes and
data warehouses many.

Instead of adding your data one line at a time I would
use a staging area to speed things up as well as set
operations instead of processing one item at a time in
a loop.

Your raw data obviously contains the following columns:
remote_ip, phone_type, phone_number url, timestamp

So create a table raw_data with the above columns. 
Truncate it before each data load.

As first step of your daily processing load your raw
data into the new table.

Then find out all unique sessions within your raw data:
CREATE TABLE unique_sessions AS
SELECT DISTINCT remote_ip, phone_type, phone_number
FROM raw_data;

Now find all the new sessions:
CREATE TABLE new_sessions AS
SELECT remote_ip, phone_type, phone_number
FROM unique_sessions
MINUS
SELECT remote_ip, phone_type, phone_number
FROM sessions;

Finally insert the new sessions into the sessions table:
INSERT INTO sessions
SELECT nextval('sessin_id'), remote_ip, phone_type,
phone_number
FROM new_sessions;

Now insert into the actions table:
INSERT INTO actions
SELECT s.session_id, r.url, r.timestamp
FROM raw_data r, sessions s
WHERE s. remote_ip = r. remote_ip
AND s.phone_type = r. phone_type 
AND s.phone_number =r. phone_number

As the last step clean up the staging tables no longer
needed:
DROP TABLE unique_sessions;
DROP TABLE new_sessions;
TRUNCATE TABLE raw_data;

Regards,
Nikolaus



On Thu, 06 June 2002, "Nicolas Nolst" wrote

> 
> 
>
Hi all,

I have developped a perl script to populate a database with two tables: sessions
and actions.

the table actions contains the following columns: session_id, url, timestamp.
The column session_id references to the table sessions.

the table sessions contains the following columns: session_id, remote_ip,
phone_type, phone_number. The column session_id is serial.

The lines of the table actions which are part of the same session have the same
session_id.

There are then more lines in the table actions than in the table session.


To fill the two tables, I first need to know if the session already exists for a
certain phone_type, a certain remote_ip and a certain phone_number:

SELECT session_id FROM sessions WHERE (phone_number = ?) AND (remote_ip  = ?)
AND (phone_type = ?) ORDER BY session_id;

I also need to apply a criteria to know if I have to add a new entry in the
table sessions or not:

SELECT (max(timestamp) + ?)<? FROM actions WHERE (session_id = ?);



If the session already exists I add a line in the table actions with a INSERT

If the session doesn't exist or if the criteria is true, I add a line in the
table sessions with an INSERT and then add  a line with a INSERT in the table actions (I use nextval and currval).

I have put indexes on sessions(session_id), sessions(msisdn),
actions(session_id) and actions(timestamp). I process one log file of about 20000 lines every day. All the lines are processed in one transaction (autocommit set to 0).

My problem is that populating my database is slower  when the data gets bigger
and the performance falls dramatically. I thought that is would be improve with
my indexes but the problem still persists.

Could you please give me some clues that could solve this issue.

Thanks.



Nicolas Nolst >
>
>


MSN Photos is the easiest way to share and print your photos: Click Here

Re: [GENERAL] performance issue using DBI

От:
"Joshua b. Jore" <josh@greentechnologist.org>
Дата:
How much data is 20,000 lines? 20KB? 100MB? You might do well to just
process this in memory and then just COPY the right data to the table.
This gets away from doing the SELECT/INSERT/SELECT/INSERT thing which is
going to be painful for batch jobs.

See, the thing is that on every insert the indexes have to be updated.
They won't be used until after you VACUUM the tables so it does no good
inside your transaction. Drop the indexes.

Also, leave off with your use of currval/nextval. The point of a serial
type is that the column increments each time automagically. For what you
are doing you could turn that serial into an integer, create your sequence
separately, exec nextval('yourseq') and then just use the value you
retrieved. Don't use currval since some other process might alter the
sequence between the time you call nextval and currval. Just store the
value.

I think this problem is better solved on the perl side than on the
PostgreSQL side. Consider using data structures like so. You can probably
create a better structure since you know your data and I don't.

sessions
{ remote_ip => { phone_type => { phone_number => session_id,
                                 phone_number => session_id
                               },
                 phone_type => { phone_number => session_id,
                                 phone_number => session_id
                               }
               },
  remote_ip => { phone_type => { phone_number => session_id,
                                 phone_number => session_id
                               },
                 phone_type => { phone_number => session_id,
                                 phone_number => session_id
                               }
               }
}

actions - now session_id is the array offset.
[ [ url, timestamp ],
  [ url, timestamp ],
  [ url, timestamp ] ],
[ [ url, timestamp ],
  [ url, timestamp ],
  [ url, timestamp ] ],
[ [ url, timestamp ],
  [ url, timestamp ],
  [ url, timestamp ] ],


> If the session already exists I add a line in the table actions with a INSERT
>
> If the session doesn't exist or if the criteria is true, I add a line in the
> table sessions with an INSERT and then add  a line with a INSERT in the table actions (I use nextval and currval).
>
> I have put indexes on sessions(session_id), sessions(msisdn),
> actions(session_id) and actions(timestamp). I process one log file of about 20000 lines every day. All the lines are
> processed in one transaction (autocommit set to 0).
>
> My problem is that populating my database is slower  when the data gets bigger
> and the performance falls dramatically. I thought that is would be improve with
> my indexes but the problem still persists.
>
> Could you please give me some clues that could solve this issue.
>
> Thanks.
>
>
>
> Nicolas Nolst
> [belgium_gs.gif]
>
> ______________________________________________________________________________________________________________________________
> MSN Photos is the easiest way to share and print your photos: Click Here
>
>




FAQ