Thread: COPY problem

COPY problem

From
Ron
Date:
I am creating a new database on a brand new server (P4, 1GB RAM,
postgres 7.3.3, debian 3.0) and trying to populate one of the tables
with the COPY command. I split a large file with 20 million records into
20 files, but when I run COPY I usually get the following message:

analytics=# COPY tbl555 FROM '/usr/local/pgsql/xaa' WITH NULL AS '';
ERROR:  copy: line 167641, Query was cancelled.

The line number varies each time I run it, and occasionally succeeds. I
split up the data into even smaller files (100,000 rows) with the same
results. Then I tried it on one of our older debian boxes (version ?)
with postgres 7.3 and the COPY's succeed. I turned up logging on the new
server but the messages returned are:
Jul 16 09:04:43 imp postgres[31180]: [72-2]  Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0.
Jul 16 09:04:43 imp postgres[31180]: [72-3] ^ICPU 0.00s/0.00u sec
elapsed 0.00 sec.
Jul 16 09:04:43 imp postgres[31180]: [73-1] DEBUG:  Index
pg_toast_4070343_index: Pages 1; Tuples 0.
Jul 16 09:04:43 imp postgres[31180]: [73-2] ^ICPU 0.00s/0.00u sec
elapsed 0.00 sec.
Jul 16 09:04:43 imp postgres[31180]: [74] DEBUG:  CommitTransactionCommand
Jul 16 09:04:57 imp postgres[31180]: [75] DEBUG:  StartTransactionCommand
Jul 16 09:04:57 imp postgres[31180]: [76] DEBUG:  ProcessUtility

analytics=# COPY tc555 FROM '/usr/local/pgsql/xaa' WITH NULL AS '';
DEBUG:  StartTransactionCommand
DEBUG:  ProcessUtility
ERROR:  copy: line 218765, Query was cancelled.
DEBUG:  AbortCurrentTransaction
ERROR:  copy: line 218765, Query was cancelled.


I set the following values in postgresql.conf:
tcpip_socket = true
shared_buffers = 1000        # min max_connections*2 or 16, 8KB each
max_fsm_relations = 1000    # min 10, fsm is free space map, ~40 bytes
sort_mem = 32168        # min 64, size in KB
vacuum_mem = 64336        # min 1024, size in KB
fsync = false
effective_cache_size = 32768    # typically 8KB each
geqo_threshold = 25
server_min_messages = debug5    # Values, in order of decreasing detail:
syslog = 1            # range 0-2
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'

If someone has any ideas what the problem may be, please let me know. I
am thinking that it may involve one of the following, but am not sure which:
- my configuration file changes
- ram
- driver issues - mother board = asus p4 p800
- driver issues - hard drive (IDE)
- ?

Thanks in advance
Ron



Re: COPY problem

From
Andrew Gould
Date:
--- Ron <rstpierre@syscor.com> wrote:
> I am creating a new database on a brand new server
> (P4, 1GB RAM,
> postgres 7.3.3, debian 3.0) and trying to populate
> one of the tables
> with the COPY command. I split a large file with 20
> million records into
> 20 files, but when I run COPY I usually get the
> following message:
>
> analytics=# COPY tbl555 FROM '/usr/local/pgsql/xaa'
> WITH NULL AS '';
> ERROR:  copy: line 167641, Query was cancelled.
>
> The line number varies each time I run it, and
> occasionally succeeds. I
> split up the data into even smaller files (100,000
> rows) with the same
> results. Then I tried it on one of our older debian
> boxes (version ?)
> with postgres 7.3 and the COPY's succeed. I turned
> up logging on the new
> server but the messages returned are:
> Jul 16 09:04:43 imp postgres[31180]: [72-2]
> Free/Avail. Space 0/0;
> EndEmpty/Avail. Pages 0/0.
> Jul 16 09:04:43 imp postgres[31180]: [72-3] ^ICPU
> 0.00s/0.00u sec
> elapsed 0.00 sec.
> Jul 16 09:04:43 imp postgres[31180]: [73-1] DEBUG:
> Index
> pg_toast_4070343_index: Pages 1; Tuples 0.
> Jul 16 09:04:43 imp postgres[31180]: [73-2] ^ICPU
> 0.00s/0.00u sec
> elapsed 0.00 sec.
> Jul 16 09:04:43 imp postgres[31180]: [74] DEBUG:
> CommitTransactionCommand
> Jul 16 09:04:57 imp postgres[31180]: [75] DEBUG:
> StartTransactionCommand
> Jul 16 09:04:57 imp postgres[31180]: [76] DEBUG:
> ProcessUtility
>
> analytics=# COPY tc555 FROM '/usr/local/pgsql/xaa'
> WITH NULL AS '';
> DEBUG:  StartTransactionCommand
> DEBUG:  ProcessUtility
> ERROR:  copy: line 218765, Query was cancelled.
> DEBUG:  AbortCurrentTransaction
> ERROR:  copy: line 218765, Query was cancelled.
>
>
> I set the following values in postgresql.conf:
> tcpip_socket = true
> shared_buffers = 1000        # min max_connections*2
> or 16, 8KB each
> max_fsm_relations = 1000    # min 10, fsm is free
> space map, ~40 bytes
> sort_mem = 32168        # min 64, size in KB
> vacuum_mem = 64336        # min 1024, size in KB
> fsync = false
> effective_cache_size = 32768    # typically 8KB each
> geqo_threshold = 25
> server_min_messages = debug5    # Values, in order
> of decreasing detail:
> syslog = 1            # range 0-2
> LC_MESSAGES = 'C'
> LC_MONETARY = 'C'
> LC_NUMERIC = 'C'
> LC_TIME = 'C'
>
> If someone has any ideas what the problem may be,
> please let me know. I
> am thinking that it may involve one of the
> following, but am not sure which:
> - my configuration file changes
> - ram
> - driver issues - mother board = asus p4 p800
> - driver issues - hard drive (IDE)
> - ?
>
> Thanks in advance
> Ron

Does your postgresql partition have enough space?

I was testing a Linux distro recently and gave /usr
lots of space, forgetting that Linux distros often
install postgresql and mysql to /var/db (or
/var/lib/db --  I forget).

If this is your problem (and you don't want to
reinstall Debian), stop the database server, move the
postgresql directory to a spacious partition, and
create a link to it in the original location.

Best of luck,

Andrew Gould

Re: COPY problem

From
Tom Lane
Date:
Ron <rstpierre@syscor.com> writes:
> I am creating a new database on a brand new server (P4, 1GB RAM,
> postgres 7.3.3, debian 3.0) and trying to populate one of the tables
> with the COPY command. I split a large file with 20 million records into
> 20 files, but when I run COPY I usually get the following message:

> analytics=# COPY tbl555 FROM '/usr/local/pgsql/xaa' WITH NULL AS '';
> ERROR:  copy: line 167641, Query was cancelled.

"Query was cancelled" is not a failure that the database would ever
produce on its own.  Something external to the Postgres code decided
to send SIGINT to either psql or the connected backend.

I suspect that you are running into some kind of resource-usage-limiting
functionality that you were not aware was active.  Perhaps you have
"ulimit" settings that are restricting how long a process can run or how
much I/O it can do.  It's a really bad idea to start the postmaster with
any non-infinite ulimit settings :-(.  ulimit on the client side is not
so dangerous, but could still prevent you from getting your work done,
as in this case.

            regards, tom lane