Re: 7.2 - 7.3 activity

On 05 Sep 2002 20:27:23 +0500, Hannu Krosing <hannu(at)tm(dot)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.

Servus
Manfred