Re: two queries and dual cpu (perplexed) - Mailing list pgsql-performance

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


[postgres@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@postgresql.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