Re: UPDATE 66k rows too slow

Hi!

The disk subsystem will be a RAID 1, but for now it's just a single
7200 rpm 160 GB SATA hard drive. The PgSQL version is 8.3, the latest
one.

I have done some performance tests on the drive, and it handles
about 40 MB/s on sequential writes, so I'm assuming it's OK.

Yours

Miguel Arroz

On 2008/03/10, at 23:56, Joshua D. Drake wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Mon, 10 Mar 2008 23:46:10 +0000
> Miguel Arroz <arroz(at)guiamac(dot)com> wrote:
> tty ad4 ad6 cpu
>> tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
>> 0 60 33.48 449 14.66 0.00 0 0.00 11 0 3 0 86
>> 0 180 16.85 599 9.87 0.00 0 0.00 1 0 1 0 98
>> 0 60 55.37 455 24.58 0.00 0 0.00 25 0 4 1 69
>> 0 60 49.83 376 18.28 0.00 0 0.00 18 0 5 1 76
>> 0 60 29.86 363 10.58 0.00 0 0.00 3 0 0 1 96
>> 0 60 36.21 365 12.90 0.00 0 0.00 12 0 3 1 84
>> 0 60 33.13 353 11.41 0.00 0 0.00 2 0 2 0 96
>> 0 60 39.47 345 13.28 0.00 0 0.00 16 0 3 0 80
>> 0 60 40.48 363 14.34 0.00 0 0.00 8 0 2 0 89
>> 0 60 30.91 397 11.97 0.00 0 0.00 5 0 2 0 93
>> 0 60 18.21 604 10.75 0.00 0 0.00 5 0 2 0 93
>> 0 60 48.65 359 17.04 0.00 0 0.00 20 0 6 0 74
>> 0 60 32.91 375 12.04 0.00 0 0.00 10 0 4 0 86
>> 0 60 35.81 339 11.84 0.00 0 0.00 3 0 2 0 96
>> 0 60 33.38 394 12.83 0.00 0 0.00 11 0 4 0 85
>> 0 60 34.40 313 10.51 0.00 0 0.00 4 0 2 0 93
>> 0 60 45.65 358 15.94 0.00 0 0.00 19 0 7 0 74
>> 0 60 37.41 309 11.28 0.00 0 0.00 3 0 2 0 95
>> 0 60 32.61 447 14.22 0.00 0 0.00 10 0 3 1 86
>> 0 60 17.11 516 8.63 0.00 0 0.00 1 0 1 0 98
>>
>> There's surely a lot of disk activity going on. With this
>> figures, I could have written some hundred gigabytes during the
>> query execution! Something is definitely not right here.
>
>
> Well the above says you are getting ~ 10-15MB/s a second performance.
> What is the disk subsystem you have. Also note that the duration
> probably went up because you didn't vacuum between tests.
>
> What version of PostgreSQL (I missed it).
>
> Joshua D. Drake
>
>
>
> - --
> The PostgreSQL Company since 1997: http://www.commandprompt.com/
> PostgreSQL Community Conference: http://www.postgresqlconference.org/
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL political pundit | Mocker of Dolphins
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFH1cq5ATb/zqfZUUQRAhVvAKCfQk4Mg6qLNQfc6uyiI2TBSbkThACeK/5k
> Tgc9ltxoOvnTMzKG2hG/4LY=
> =Tm4N
> -----END PGP SIGNATURE-----
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Miguel Arroz
http://www.terminalapp.net
http://www.ipragma.com