Thread: iowait

I'm not sure if this is typical or not but:

I am running a command:
  COPY email (address,domain) FROM '/tmp/temp.dat';
That is reading in some 1.7 million rows to a table which is defined as:

create table email (
    address varchar(100) primary key,
    domain varchar(100),
    status smallint default 0,
    reason varchar(64),
    last_timestamp timestamp default now(),
    created_timestamp timestamp default now(),
    retry_count smallint default 0
);

I am running it under 'BEGIN' so in theory autocommit is off, which seems to be
the case since there aren't any rows to query.
I've increased the mem_sort to 65535 or 65536.

The whole thing is writing to a partition on a relatively simple EIDE disk.

My iowait is 95-95% according to iostat and it's all pointed at this one disk
partition.  And it's taking a REALLY long time.

Question:
Is this normal for a large data load like this?
Is there something "obvious" I could do in the future to better the situation?
Or am I simply bound by the type of hardware I'm running it on?
Are the number of default values and primary key index going to be the death of me?

On Friday 09 June 2006 16:44, Tom Allison <tallison@tacocat.net> wrote:
> I am running it under 'BEGIN' so in theory autocommit is off, which seems
> to be the case since there aren't any rows to query.

The COPY would run as a single transaction, anyway.

> The whole thing is writing to a partition on a relatively simple EIDE
> disk.

And that's why it's slow.

>
> My iowait is 95-95% according to iostat and it's all pointed at this one
> disk partition.  And it's taking a REALLY long time.
>
> Question:
> Is this normal for a large data load like this?

Yep.

> Is there something "obvious" I could do in the future to better the
> situation? Or am I simply bound by the type of hardware I'm running it
> on?

Drop indexes before the COPY and recreate them after.  Other than that, not
much.

> Are the number of default values and primary key index going to be the
> death of me?

Default values aren't really a factor.  The index slows down COPY some.  The
reason it's slow is your hard drive, though.

--
When we vote for taxes, we are voting to steal from our neighbors

Alan Hodgson wrote:
> On Friday 09 June 2006 16:44, Tom Allison <tallison@tacocat.net> wrote:
> Default values aren't really a factor.  The index slows down COPY some.  The
> reason it's slow is your hard drive, though.
>

That's what I was starting to think.

I can run individual transactions fine and handle concurrent threads up to ~100
as long as the transactions are simple (eg: index keyed SQL, no full table scans).

But anytime I try to do aggregate or large blocks of operations....