Re: two queries and dual cpu (perplexed)

here are some i/o stats with the unchanged postgresql.conf. Gonna change
it now and have another go.

[postgres(at)dbsql1 MultiCPU_test]$ vmstat 10
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
0 0 25808 710356 6348860 972052 2 4 73 29 1 3 1 0
99 0
2 0 25808 647636 6348960 1034784 0 0 3226 3048 1054 92819 55 19
25 1
2 0 25808 585684 6349032 1096660 0 0 3203 3057 1053 96375 55 19
25 1
2 0 25808 521940 6349112 1160364 0 0 3388 2970 1052 95563 54 19
26 1
2 0 25808 463636 6349184 1218568 0 0 2804 3037 1048 93560 55 19
25 1
2 0 25808 405460 6349264 1276696 0 0 2794 3047 1046 96971 55 19
25 1
2 0 25808 343956 6349340 1338160 0 0 3151 3040 1049 96629 55 20
25 1
2 0 25808 287252 6349412 1394732 0 0 2666 2990 1045 95173 54 20
25 1
2 0 25808 230804 6349484 1451168 0 0 2678 2966 1044 95577 54 19
26 1
2 0 25808 169428 6349560 1512428 0 0 3164 3015 1048 98451 55 19
25 1
2 0 25808 110484 6349640 1571304 0 0 2910 2970 1050 98214 55 20
25 0
0 0 25808 50260 6349716 1631408 0 0 3049 3015 1049 99830 55 20
25 1
1 0 25808 8512 6349788 1673156 0 0 2934 2959 1047 95940 54 19
24 3
2 1 25808 8768 6349796 1672944 0 0 2552 2984 1043 97893 55 19
18 8
1 1 25808 8384 6349824 1673256 0 0 2596 3032 1051 94646 55 19
19 6
2 1 25808 8960 6349856 1672680 0 0 2982 3028 1052 94486 55 20
19 6
1 1 25808 8960 6349884 1672584 0 0 3125 2919 1052 86969 52 20
19 8
2 0 25808 6196 6349912 1675276 0 0 2809 3064 1046 99147 55 20
19 5
1 1 25808 9216 6349976 1672152 0 0 2898 3076 1047 93271 55 19
21 6
2 0 25808 6580 6349316 1663972 0 0 3150 2982 1048 94964 54 22
20 4
2 0 25808 7692 6349348 1674480 0 0 2742 3006 1045 97488 54 21
21 4
2 1 25808 8232 6346244 1676700 0 0 2900 3022 1048 92496 54 20
19 8
2 0 25808 7104 6346192 1678044 0 0 3284 2958 1057 97265 55 20
18 7
2 0 25808 8488 6346168 1676776 0 0 2609 3031 1047 93965 55 19
20 7
2 1 25808 8680 6346184 1676488 0 0 3067 3044 1051 96594 55 19
19 6
2 0 25808 8576 6346168 1676640 0 0 2900 3070 1047 96300 55 19
20 6
2 1 25808 9152 6346156 1676176 0 0 3010 2993 1049 98271 55 20
19 7
2 0 25808 7040 6346172 1678200 0 0 3242 3034 1050 97669 55 20
21 4
1 1 25808 8900 6346192 1676344 0 0 2859 3014 1052 91220 53 19
21 6
2 1 25808 8512 6346188 1676824 0 0 2737 2960 1049 100609 55
20 18 6
2 0 25808 7204 6346236 1678000 0 0 2972 3045 1050 94851 55 19
17 9
1 0 25808 7116 6346208 1678028 0 0 3053 2996 1048 98901 55 19
20 5
2 1 25808 9180 6346196 1676068 0 0 2857 3067 1047 100629 56
21 20 3
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
3 1 25808 8896 6346172 1676500 0 0 3138 3022 1049 97937 55 20
20 5
2 1 25808 9088 6346188 1676212 0 0 2844 3022 1047 97664 55 19
20 5
1 1 25808 8920 6346248 1676288 0 0 3017 3024 1049 99644 55 20
17 7
1 1 25808 8064 6346116 1677168 0 0 2824 3037 1047 99171 55 20
19 5
2 1 25820 8472 6344336 1678596 0 0 2969 2957 1047 96396 54 21
18 7
2 1 25820 9208 6344300 1677884 0 0 3072 3031 1050 95017 54 19
22 5
1 0 25820 7848 6344328 1679148 0 0 3229 3011 1050 97108 55 19
20 5
2 1 25820 8960 6344348 1678040 0 0 2701 2954 1046 98485 54 20
21 5
2 0 25820 7900 6344368 1679244 0 0 2604 2931 1044 97198 54 20
19 7
2 0 25820 9240 6344424 1677896 0 0 2990 3015 1048 102414 56
20 19 5
2 0 25820 8924 6344436 1678088 0 0 3256 2991 1049 96709 55 19
21 5
1 1 25820 8900 6344456 1678204 0 0 2761 3030 1051 96498 55 20
20 5
2 0 25820 7628 6344440 1679444 0 0 2952 3012 1053 96534 55 20
19 6
2 0 25820 7080 6344472 1679956 0 0 2848 3079 1050 95074 56 19
19 6
2 0 25820 8928 6344444 1678080 0 0 2985 3021 1049 96806 55 20
18 7
2 1 25820 7976 6344976 1676892 11 0 3429 3062 1083 92817 55 19
18 8
2 0 25820 8096 6345080 1676652 0 0 2662 2989 1056 91921 54 19
17 10
1 0 25820 7424 6345128 1677352 0 0 2956 3029 1054 99385 56 19
20 5
2 0 25820 6664 6345232 1677724 0 0 3358 3030 1064 95929 55 19
21 5
1 0 25820 7268 6345320 1676956 0 0 2681 3012 1082 97744 54 20
18 7
2 0 25820 6944 6345364 1677184 0 0 3156 3022 1061 98055 55 19
22 4
2 0 25820 8668 6345420 1675428 0 0 2990 3018 1050 94734 55 19
22 5
2 1 25820 8724 6345464 1675452 0 0 2677 2967 1055 100760 55
20 18 7
2 1 25820 9260 6345508 1674796 0 0 3296 3233 1054 99711 55 20
20 5
2 0 25820 6196 6345556 1677944 0 0 2861 2950 1066 93289 53 19
23 6
2 0 25820 8052 6345620 1675908 0 0 3012 2920 1051 94428 54 19
20 7
2 1 25820 9000 6345672 1675040 0 0 2645 2980 1045 99992 56 20
17 8
2 1 25820 8296 6345728 1675732 0 0 3216 3058 1052 91934 54 19
21 5
2 0 25820 7900 6345796 1676072 0 0 3009 3022 1052 96303 55 19
20 7
2 0 25820 8516 6345844 1675344 0 0 2586 2956 1048 95812 54 20
19 8
2 1 25820 9000 6345892 1674752 0 0 3225 3028 1055 99786 54 20
21 5
0 1 25820 9128 6345768 1674684 0 1 2868 3016 1049 98301 55 21
19 6
2 1 25820 8160 6345828 1675576 0 0 3079 3056 1050 93725 55 19
21 5

On Thu, 21 Apr 2005, Shoaib Burq (VPAC) wrote:

>
> here's explain sorry about the mess: I can attach it as text-file if you
> like.
>
> ausclimate=# explain ANALYZE select count(*) from "getfutureausclimate";
>
>
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=1069345.85..1069345.85 rows=1 width=0) (actual
> time=443241.241..443241.242 rows=1 loops=1)
> -> Subquery Scan getfutureausclimate (cost=1069345.61..1069345.81
> rows=16 width=0) (actual time=411449.034..436165.259 rows=13276368
> loops=1)
> -> Sort (cost=1069345.61..1069345.65 rows=16 width=58) (actual
> time=411449.026..426001.199 rows=13276368 loops=1)
> Sort Key: "Aus40_DEM"."AusPosNumber",
> "CurrentAusClimate"."iMonth"
> -> Nested Loop (cost=2.19..1069345.29 rows=16 width=58)
> (actual time=135.390..366902.373 rows=13276368 loops=1)
> -> Nested Loop (cost=2.19..1067304.07 rows=44
> width=68) (actual time=107.627..186390.137 rows=13276368 loops=1)
> -> Nested Loop (cost=2.19..1067038.94 rows=44
> width=52) (actual time=87.255..49743.796 rows=13276368 loops=1)
> -> Nested Loop (cost=2.19..8.09 rows=1
> width=32) (actual time=52.684..52.695 rows=1 loops=1)
> -> Merge Join (cost=2.19..2.24
> rows=1 width=24) (actual time=28.000..28.007 rows=1 loops=1)
> Merge Cond:
> ("outer"."ClimateId" = "inner"."ClimateId")
> -> Sort (cost=1.17..1.19
> rows=7 width=10) (actual time=10.306..10.307 rows=3 loops=1)
> Sort Key:
> "ClimateVariables"."ClimateId"
> -> Seq Scan on
> "ClimateVariables" (cost=0.00..1.07 rows=7 width=10) (actual
> time=10.277..10.286 rows=7 loops=1)
> -> Sort (cost=1.02..1.02
> rows=1 width=14) (actual time=17.679..17.680 rows=1 loops=1)
> Sort Key:
> "GetFutureClimateParameters"."ClimateId"
> -> Seq Scan on
> "GetFutureClimateParameters" (cost=0.00..1.01 rows=1 width=14) (actual
> time=17.669..17.671 rows=1 loops=1)
> -> Index Scan using
> "PK_ScenarioEmissionLevels" on "ScenarioEmissionLevels" (cost=0.00..5.83
> rows=1 width=18) (actual time=24.676..24.679 rows=1 loops=1)
> Index Cond:
> (("ScenarioEmissionLevels"."ScenarioId" = "outer"."ScenarioId") AND
> ("ScenarioEmissionLevels"."iYear" = "outer"."iYear") AND
> ("ScenarioEmissionLevels"."LevelId" = "outer"."LevelId"))
> -> Index Scan using "IX_ClimateId" on
> "ClimateChangeModel40" (cost=0.00..1063711.75 rows=265528 width=20)
> (actual time=34.564..19435.855 rows=13276368 loops=1)
> Index Cond: ("outer"."ClimateId" =
> "ClimateChangeModel40"."ClimateId")
> -> Index Scan using "PK_Aus40_DEM" on
> "Aus40_DEM" (cost=0.00..6.01 rows=1 width=16) (actual time=0.005..0.006
> rows=1 loops=13276368)
> Index Cond: ("outer"."AusPosNumber" =
> "Aus40_DEM"."AusPosNumber")
> -> Index Scan using "PK_CurrentAusClimate" on
> "CurrentAusClimate" (cost=0.00..46.20 rows=11 width=14) (actual
> time=0.007..0.009 rows=1 loops=13276368)
> Index Cond: (("CurrentAusClimate"."ClimateId" =
> "outer"."ClimateId") AND ("outer"."AusPosNumber" =
> "CurrentAusClimate"."AusPosNum") AND ("CurrentAusClimate"."iMonth" =
> "outer"."iMonth"))
> Total runtime: 443983.269 ms
> (25 rows)
>
>
> Sheeeesshh...
>
> > You should really, really bump up shared_buffers and given you have 8GB
> > of ram this query would likely benefit from more work_mem.
>
> I actually tried that and there was a decrease in performance. Are the
> shared_buffers and work_mem the only things I should change to start with?
> If so what's the reasoning.
>
>
> > Is this an IO intensive query? If running both in parellel results in
> > 2x the run time and you have sufficient cpus it would (to me) indicate
> > you don't have enough IO bandwidth to satisfy the query.
>
> Yes I think so too: ... I am just compiling some io stats...
>
> Also will jump on to irc...
>
> >
> Whoa! thanks all... I am overwhelmed with the help I am getting... I love
> it!
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Shoaib Burq
--
VPAC - Geospatial Applications Developer
Building 91, 110 Victoria Street,
Carlton South, Vic 3053, Australia
_______________________________________________________________
w: www.vpac.org | e: sab_AT_vpac_DOT_org | mob: +61.431-850039