Re: UPDATE 66k rows too slow - Mailing list pgsql-performance

Hi!

   It now raised to 40 seconds... here goes the result of iostat:

iostat -K -c 40
       tty             ad4              ad6             cpu
  tin tout  KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in id
    1   78 32.86  34  1.08   0.70   0  0.00  13  0  1  0 86
    0  180  6.00   4  0.02   0.00   0  0.00   0  0  0  0 100
    1   63 39.74  62  2.40   0.00   0  0.00  17  0  1  0 82
    0   60 18.69 815 14.87   0.00   0  0.00  20  0  2  0 79
    0   60 56.17 293 16.06   0.00   0  0.00  41  0  5  0 53
    0   60 55.74 396 21.53   0.00   0  0.00  39  0 10  0 51
    0   60 42.24 357 14.71   0.00   0  0.00  10  0  2  0 88
    0   60 42.92 354 14.82   0.00   0  0.00  12  0  7  1 80
    0   60 38.51 368 13.82   0.00   0  0.00  14  0  6  0 80
    0   60 43.83 326 13.94   0.00   0  0.00   4  0  1  0 95
    0   60 33.30 395 12.83   0.00   0  0.00  11  0  3  0 86
    0   60 41.36 395 15.94   0.00   0  0.00   4  0  3  0 93
    0   60 21.97 684 14.68   0.00   0  0.00  10  0  2  0 88
    0   60 72.44 297 20.99   0.00   0  0.00  42  0  9  0 48
    0   60 38.18 453 16.87   0.00   0  0.00  23  0  8  1 68
    0   60 35.15 365 12.52   0.00   0  0.00   1  0  1  0 97
    0   60 44.40 396 17.15   0.00   0  0.00  17  0  6  0 77
    0   60 43.99 341 14.64   0.00   0  0.00   4  0  2  0 93
    0   60 33.53 440 14.39   0.00   0  0.00  10  0  5  0 85
    0   60 31.22 345 10.51   0.00   0  0.00   0  0  2  0 97
       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.

   Yours

Miguel Arroz

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

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Mon, 10 Mar 2008 23:17:54 +0000
> Miguel Arroz <arroz@guiamac.com> wrote:
>
>> Hi!
>>
>>   I read and did many stuff you pointed me too. Raised shared
>> buffers to 180 MB, and tried again. Same results.
>>
>>   I deleted the DB, created a new one and generated new test data.
>> I know have 72k rows, and the same query finishes in... 9 seconds.
>>
>>   I'm totally clueless. Anyway, two questions:
>>
>>   1) My working_mem is 2 MB. Does an UPDATE query like main depend
>> on working_mem?
>>
>>   2) I still feel this is all very trial-and-error. Change value,
>> run query, hope it solves the problem. Well, the DB itself knows what
>> is doing. Isn't there any way to make it tell us that? Like "the
>> working mem is too low" or anything else. I know the problem is not
>> the checkpoints, at least nothing appears on the log related to that.
>> But it irritates me to be in front of a such complex system and not
>> being able to know what's going on.
>
> What does iostat -k 1 tell you during the 9 seconds the query is
> running?
>
> 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)
>
> iD8DBQFH1cK3ATb/zqfZUUQRAhllAJ9C9aL9o/4hzq9vZyRaY8J6DknP5QCePDfS
> BxJ/umrVArStUJgG3oFYsSE=
> =n0uC
> -----END PGP SIGNATURE-----
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

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