Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

Inefficient bytea escaping?


  • From: Andreas Pflug <pgadmin(at)pse-consulting(dot)de>
  • To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
  • Subject: Inefficient bytea escaping?
  • Date: Thu, 25 May 2006 18:16:52 +0200
  • Message-id: <4475D874(dot)1090505(at)pse-consulting(dot)de>

When preparing to transfer blob data from one database to another (8.0.5 to 8.1.4), I found some interesting numbers that made me suspect that bytea dumping is more ineffective than expectable.

I have a test dataset of 2000 rows, each row containing a bytea column. Total disk usage of the table (no indexes) is 138MB, total data size is 1.4GB (sum(length(bytea_col)). Data is stored on a RAID5 (Areca 128MB, SATA, 4 disks), and was dumped to a RAID1 on the same controller.

When dumping the table with psql \copy (non-binary), the resulting file would be 6.6GB of size, taking about 5.5 minutes. Using psql \copy WITH BINARY (modified psql as posted to -patches), the time was cut down to 21-22 seconds (filesize 1.4GB as expected), which is near the physical throughput of the target disk. If server based COPY to file is used, The same factor 12 can be observed, CPU is up to 100 % (single P4 3GHz 2MB Cache HT disabled, 1GB main mem).

What's happening here?

Regards,
Andreas



Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group