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@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx )To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Miguel Arroz http://www.terminalapp.net http://www.ipragma.com
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
