Message-ID: <5004E0AD.6090502@uptime.jp>
Date: Tue, 17 Jul 2012 12:49:01 +0900
From: Satoshi Nagayasu <snaga@uptime.jp>
Organization: Uptime Technologies, LLC
User-Agent: Mozilla/5.0 (Windows NT 6.1;
	rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
To: Jon Nelson <jnelson+pgsql@jamponi.net>
CC: pgsql-performance@postgresql.org
Subject: Re: very very slow inserts into very large table
References: 
 <CAKuK5J1wY9NoZHxOaTf+=WNhgfn1uEU19Mw5dLvZh_NWpc8+tw@mail.gmail.com>
In-Reply-To: 
 <CAKuK5J1wY9NoZHxOaTf+=WNhgfn1uEU19Mw5dLvZh_NWpc8+tw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

2012/07/16 22:37, Jon Nelson wrote:
> I have a single *table* that is some 560GB in size, 6 columns, average
> row width 63.
> There are approximately 6.1 billion rows.
> It has 4 indices, 131, 313, 131 and 190 GB in size, respectively. All
> are btree indices.
>
> I tried inserting new data into the table, and it's taking a *very* long time.
> I pre-built the data to be inserted into a temporary table with the
> exact same structure and column ordering, etc, and the temporary table
> is about 8.5GB in size with about 93 million rows.
> The temporary table was built in about 95 seconds.
> The insert has been going for 47 hours and 21 minutes, give or take.
> I'm not doing any correlation or filtering, etc --  straight up
> insert, literally "insert into big_table select * from
> the_temp_table;".
>
> vmstat output doesn't seem that useful, with disk wait being 10-15%
> and I/O speeds highly variable, from 5-20MB/s reads couple with
> 0-16MB/s writes, generally on the lower end of these.
> strace of the inserting process shows that it's basically hammering
> the disk in terms of random reads and infrequent writes.
> postgresql. It's not verifying, rebuilding, etc. While this process is
> active, streaming write I/O is terrible - 36MB/s. WIth it "paused"
> (via strace) I get 72MB/s.  (reads are 350MB/s).

I think the most possible reason could exists around WAL and its
buffers.

But it's just my guess, and you need to determine a cause of the
situation precisely. Disk I/O operations must be broken down
into the PostgreSQL context, such as block reads, wal writes or bgwiter.

If you want to know what's actually going on inside PostgreSQL,
pgstatview may help you that.

http://pgsnaga.blogspot.jp/2012/06/pgstatview-visualize-your-postgresql-in.html
http://www2.uptimeforce.com/pgstatview/

pgstatview provides an easy way not only to visualize your performance
statistics while workload, but also to share it with the PostgreSQL
experts.

Here is an example of the report:
http://www2.uptimeforce.com/pgstatview/a9ee29aa84668cca2d8cdfd2556d370c/

I believe you can find some thoughts from visualizing and comparing
your statistics between your temp table and regular table.

Regards,

>
> The OS is Scientific Linux 6.2, and the version of postgresql is 9.1.4
> - x86_64. There is nothing else of note happening on the box. The box
> is a quad CPU, dual-core each Xeon E5430  @ 2.66GHz with 32GB of RAM
> and a 3ware 9690 RAID 4TB RAID10 for the storage for
>
> What might be going on here?
>
>

-- 
Satoshi Nagayasu <snaga@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp