Re: 7.2 - 7.3 activity

On 05 Sep 2002 20:27:23 +0500, Hannu Krosing <hannu@tm.ee> wrote:
>Has anyone run any speed tests to see how 7.2 and 7.3 compare ?

Running a modified OSDB (CREATE TABLE ... WITHOUT OIDS) with 400 MB
data on a Pentium III 1 GHz, 382 MB RAM, 7200 rpm IBM 14 GB HD under
Linux, this is what I got so far:

Testname                       Wo8         Old8wo        721b
Nr                                17           18           19
Test MB                          400          400          400
System mem                       382          382          382

Tuple header                  small        large        large
WITH / WITHOUT OIDS          WITHOUT      WITHOUT      WITHOUT

(populate + single user)
Elapsed  hh:mm:ss            05:04:36     06:54:18     06:27:26
User  mm:ss.00               00:19.32     00:19.61     00:17.72
System  mm:ss.00             00:15.97     00:17.37     00:15.90

Xlog                            ...5B        ...5B        ...5A
Size KB                     1,038,564    1,070,656    1,069,652
CTIME postmaster  mmm:ss       284:22       391:44       363:09
Updates                         2,009        2,009        2,009                            VAC, ANA     VAC, ANA
VAC,ANA  *1
 

(multi user)                                                     *2
Elapsed  hh:mm:ss            31:34:17     51:33:54
User  mm:ss.00              130:31.22    222:08.98
System  mm:ss.00             92:59.18    159:24.81

Xlog                        5B...1,8F    5B...2,21
Size KB                     1,143,080    1,193,536
CTIME postmaster  mmm:ss      1640:00      2680:00
Updates                       243,390      341,233


create_tables()                  0.08         0.08         0.06
load()                         633.30       681.91       725.79
create_idx_uniques_key_bt()    320.90       344.45       305.63
create_idx_updates_key_bt()    321.23       351.97       327.52
create_idx_hundred_key_bt()    319.26       349.17       327.87
create_idx_tenpct_key_bt()     318.78       349.05       326.82
create_idx_tenpct_key_code_bt() 65.40        94.34        70.69
create_idx_tiny_key_bt()         3.15         0.10         4.69
create_idx_tenpct_int_bt()      23.44        27.04        21.60
create_idx_tenpct_signed_bt()   25.16        25.81        25.45
create_idx_uniques_code_h()    118.48       138.47       122.57
create_idx_tenpct_double_bt()   32.03        29.78        29.49
create_idx_updates_decim_bt()  130.92       149.37       136.27
create_idx_tenpct_float_bt()    28.71        29.66        28.88
create_idx_updates_int_bt()     55.05        62.62        56.90
create_idx_tenpct_decim_bt()    52.14        54.05        52.41
create_idx_hundred_code_h()    116.09       136.30       122.34
create_idx_tenpct_name_h()      40.91        42.94        39.28
create_idx_updates_code_h()     73.54        81.80        75.48
create_idx_tenpct_code_h()      36.51        37.99        36.17
create_idx_updates_double_bt()  64.02        71.18        67.72
create_idx_hundred_foreign()   135.44       140.54       131.18
Sum                          2,914.54     3,198.62     3,034.81
populateDataBase()           2,914.69     3,195.71     3,034.89

sel_1_cl()                       0.09         0.07         0.08
join_3_cl()                      0.10         0.10         0.10
sel_100_ncl()                    2.60         2.62         2.53
table_scan()                    36.72        41.32        37.74
agg_func()                     100.06       137.39       113.70
agg_scal()                      37.93        41.68        37.69
sel_100_cl()                     2.59        29.53         2.54
join_3_ncl()                   231.39       234.77       239.32
sel_10pct_ncl()                 51.50        20.68       133.47
agg_simple_report()          8,734.76    14,222.07    13,132.75
agg_info_retrieval()            46.03       133.41       131.11
agg_create_view()                0.69         0.67         0.47
agg_subtotal_report()           98.67       146.69        87.07
agg_total_report()              94.19       132.59       120.86
join_2_cl()                      0.12         0.11         0.08
join_2()                        96.67       108.61       101.16
sel_variable_select_low()       21.92        35.75        20.35
sel_variable_select_high()      30.12        29.57        28.33
join_4_cl()                      0.02         0.01         0.01
proj_100()                     100.81       144.07       114.83
join_4_ncl()                   282.74       368.88       315.62
proj_10pct()                   109.96       144.27       124.76
sel_1_ncl()                      0.14         0.09         0.07
join_2_ncl()                    94.76       113.95       105.70
integrity_test()                 5.61         6.00         5.60
drop_updates_keys()              0.38         0.36         0.48
bulk_save()                      0.25         0.30         0.26
bulk_modify()                2,464.31     2,647.28     2,552.16
upd_append_duplicate()           0.11         0.13         0.12
upd_remove_duplicate()           0.00         0.00         0.00
upd_app_t_mid()                  0.01         0.01         0.01
upd_mod_t_mid()                  2.46         2.84         2.58
upd_del_t_mid()                  2.48         2.83         2.55
upd_app_t_end()                  0.04         0.04         0.04
upd_mod_t_end()                  2.46         2.84         2.57
upd_del_t_end()                  2.47         2.84         2.54
create_idx_updates_code_h()     73.84        83.08        75.63
upd_app_t_mid()                  0.10         0.11         0.10
upd_mod_t_cod()                  0.00         0.01         0.00
upd_del_t_mid()                  2.48         2.82         2.56
create_idx_updates_int_bt()     54.50        61.96        57.03
upd_app_t_mid()                  0.10         0.09         0.12
upd_mod_t_int()                  0.00         0.01         0.01
upd_del_t_mid()                  2.52         2.91         2.57
bulk_append()                   11.88        19.01        11.49
bulk_delete()                2,513.29     2,685.69     2,593.61
Sum                         15,313.87    21,610.06    20,162.37
Single     User Test        15,313.88    21,610.08    20,162.40

Mixed     IR (tup/sec)         101.32        98.97               *2
sel_1_ncl()                      0.08         0.09
agg_simple_report()         98,086.15   162,492.57
mu_sel_100_seq()                 0.89         0.81
mu_sel_100_rand()                0.22         0.20
mu_mod_100_seq_abort()           2.82         3.27
mu_mod_100_rand()                0.18         0.21
mu_unmod_100_seq()               0.26         0.45
mu_unmod_100_rand()              0.38         0.33                           98,090.98   162,497.93
crossSectionTests               (Mixed IR)  98,090.98   162,497.93

mu_checkmod_100_seq()            0.15         0.15
mu_checkmod_100_rand()           0.01         0.01

Mixed     OLTP (tup/sec)        32.03        28.69
sel_1_ncl()                      0.15         0.32
agg_simple_report()         13,094.84    20,635.31
mu_sel_100_seq()                15.93        16.88
mu_sel_100_rand()                1.34         4.18
mu_mod_100_seq_abort()          10.72        25.82
mu_mod_100_rand()                3.58         0.61
mu_unmod_100_seq()               1.75         1.37
mu_unmod_100_rand()              2.18         0.57                           13,130.49    20,685.06
crossSectionTests             (Mixed OLTP)  13,130.63    20,685.20
mu_checkmod_100_seq()            2.39         1.88
mu_checkmod_100_rand()           0.10         0.64

Multi-User     Test        113,649.68   185,610.40               *3


wo8 is a CVS snapshot from 2002-07-20.  Since then there have been
NAMEDATALEN and FUNC_MAX_ARGS changes making PG slightly slower (cf.
Joe Conway's mail "Re: [HACKERS] FUNC_MAX_ARGS benchmarks"
2002-08-06).  Are there any other performance-relevant changes?
Anyway I'll do some tests with 7.3beta1 later.

old8wo is the 2002-08-20 snapshot with the heap tuple header changes
reversed.

721b is plain 7.2.1.

*1  I did a VACUUM ANALYZE for all user tables before the multi user
test.

*2  721b multi user test still running, expected to finish on Friday. 

*3  Multi user tests were run with ten users.

Don't trust the numbers returned by the multi user tests. There are at
least two problems, both related to the fact that child processes do
random selects/updates as fast as possible:

First, with a faster server you have more deleted tuples making the
tests slower.

Second, with system RAM significantly smaller than database size the
random selects/updates have to wait for I/O most of the time and the
master process gets almost all the CPU, especially on the longer tests
(agg_simple_report!).  With enough RAM to cache most of the database
there is much less I/O and CPU is distributed evenly between all
processes, so the master process gets only a small fraction of CPU
power.

The single user tests look plausible to me.  Though I did each test
only once.  So please use with a grain of salt and feel free to
comment, if you think there's something wrong.

ServusManfred