Thread: Copy to Csv file extremely slow

On Thu, May 30, 2013 at 12:48 PM, Jalisson Mello
<jalissonmeneses@hotmail.com> wrote:
> Hi,  I´m using  the copy command to generate a csv file, but the generation
> its extremely slow in a production server. In our test's server this copy
> takes 20 minutes but in a production server this takes 2 days.
>
> The database that i used to the tests is a copy of the production database.
> The machines are totaly differents.  Test Server is a simple Intel core 2
> duo 2.8 ghz with 8 GB Ram  and Production Server has 2 xeon 2.8 ghz with 24
> GB RAM.
>
> Before the tests we ran vacuum  and closed all another connections.
>  The query takes 19 minutes and file generation less than 1 minute to create
> a file with 473 MB in the  tests server. In Production server the query runs
> in the same 19 minutes, but file generation 2 days.
>
> I'm running postgresql 9.0 on CentOS 6.3 on both machines.
>
> Any idea about this big difference.
>
> Thanks , and sorry by my english.


have you ruled out o/s problems?  what's iowait during this time?  is
database writing to local storage?

merlin

On Thu, May 30, 2013 at 6:43 PM, Jalisson Mello
<jalissonmeneses@hotmail.com> wrote:
> Thanks by the answer.
>
> The iowait is very low i think, it's varies between 0.14 and 1.1  and cpu
> 98% idle during this time.
>
> OS and hardware problems were the first things that i thought and tested and
> until now nothing strange.
>
>  It's writing to local disk.
>
>  Any suggestion or idea will be welcome.
>
> Tomorrow I will post my postgresql.conf  because today is holiday here in
> Brazil and I don't have acess to the servers from my home.

What I would try next is to see if if the copying process is binding
up in a system call:  try strace -t on the process that is doing the
dump.  If you see excessive timings there that will be a big clue.

merlin