Re: UPDATE 66k rows too slow

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(at)guiamac(dot)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(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