8K vs 16K block size report - Mailing list pgsql-hackers
| From | mlw |
|---|---|
| Subject | 8K vs 16K block size report |
| Date | |
| Msg-id | 3CC8B860.432D1A9C@mohawksoft.com Whole thread Raw |
| List | pgsql-hackers |
I have been doing some benchmarking. The idea is to measure the difference
between PostgreSQL 7.2.1 compiled for an 8K block or a 16K block. pgbench is
almost useless for measuring changes in performance, I can't seem to get any
real consistency, so I used the osdb to measure performance.
In the 16K block configuration, I reduced the number of blocks by half, so as
to keep caching memory similarly sized.
The machine:
RedHat 7.2, dual PIII 650, 1G ram, 2 IBM scsi 18G drives.
uname -a:
Linux slave1.mohawksoft.com 2.4.7-10smp #1 SMP Thu Sep 6 17:09:31 EDT 2001 i686
unknown
mount:
[markw@slave1 markw]$ mount
/dev/sda3 on / type ext3 (rw)
none on /proc type proc (rw)
usbdevfs on /proc/bus/usb type usbdevfs (rw)
/dev/sda1 on /boot type ext3 (rw)
/dev/sda4 on /u01 type ext2 (rw)
/dev/sdb1 on /u02 type ext2 (rw)
none on /dev/pts type devpts (rw,gid=5,mode=620)
none on /dev/shm type tmpfs (rw)
The 8K data directory:
[postgres@slave1 data]$ pwd
/home/postgres/data
[postgres@slave1 data]$ ls -l
total 40
lrwxrwxrwx 1 root root 9 Mar 6 07:21 base -> /u02/base
drwx------ 2 postgres postgres 4096 Apr 25 21:51 global
drwx------ 2 postgres postgres 4096 Apr 25 21:29 pg_clog
-rw------- 1 postgres postgres 10068 Mar 6 07:26 pg_hba.conf
-rw------- 1 postgres postgres 1250 Mar 6 07:20 pg_ident.conf
-rw------- 1 postgres postgres 4 Mar 6 07:20 PG_VERSION
lrwxrwxrwx 1 root root 12 Mar 6 07:22 pg_xlog -> /u01/pg_xlog
-rw------- 1 postgres postgres 3095 Apr 24 19:23 postgresql.conf
-rw------- 1 postgres postgres 56 Apr 25 20:54 postmaster.opts
-rw------- 1 postgres postgres 44 Apr 25 20:54 postmaster.pid
The 16K data directory:
[postgres@slave1 data16kb]$ pwd
/home/postgres/data16kb
[postgres@slave1 data16kb]$ ls -l
total 36
lrwxrwxrwx 1 postgres postgres 13 Apr 25 08:47 base -> /u02/base16kb
drwx------ 2 postgres postgres 4096 Apr 25 20:53 global
drwx------ 2 postgres postgres 4096 Apr 25 20:33 pg_clog
-rw------- 1 postgres postgres 10068 Apr 25 08:44 pg_hba.conf
-rw------- 1 postgres postgres 1250 Apr 25 08:43 pg_ident.conf
-rw------- 1 postgres postgres 4 Apr 25 08:43 PG_VERSION
lrwxrwxrwx 1 postgres postgres 16 Apr 25 08:46 pg_xlog ->
/u01/pg_xlog16kb
-rw------- 1 postgres postgres 3095 Apr 25 08:44 postgresql.conf
-rw------- 1 postgres postgres 64 Apr 25 14:59 postmaster.opts"osdb"
"Invoked: osdb-pg --postgresql=no_hash_index --logfile 16kb.log"
create_tables() 0.02 seconds return value = 0
load() 15.81 seconds return value = 0
create_idx_uniques_key_bt() 3.39 seconds return value = 0
create_idx_updates_key_bt() 3.42 seconds return value = 0
create_idx_hundred_key_bt() 3.47 seconds return value = 0
create_idx_tenpct_key_bt() 3.43 seconds return value = 0
create_idx_tenpct_key_code_bt() 0.93 seconds return value = 0
create_idx_tiny_key_bt() 0.00 seconds return value = 0
create_idx_tenpct_int_bt() 0.63 seconds return value = 0
create_idx_tenpct_signed_bt() 0.93 seconds return value = 0
create_idx_uniques_code_h() 1.40 seconds return value = 0
create_idx_tenpct_double_bt() 1.09 seconds return value = 0
create_idx_updates_decim_bt() 2.43 seconds return value = 0
create_idx_tenpct_float_bt() 1.10 seconds return value = 0
create_idx_updates_int_bt() 0.62 seconds return value = 0
create_idx_tenpct_decim_bt() 2.31 seconds return value = 0
create_idx_hundred_code_h() 1.40 seconds return value = 0
create_idx_tenpct_name_h() 1.38 seconds return value = 0
create_idx_updates_code_h() 1.28 seconds return value = 0
create_idx_tenpct_code_h() 1.27 seconds return value = 0
create_idx_updates_double_bt() 1.09 seconds return value = 0
create_idx_hundred_foreign() 11.11 seconds return value = 0
populateDataBase() 58.51 seconds return value = 0
"Logical database size 40MB"
sel_1_cl() 0.01 seconds return value = 1
join_3_cl() 0.05 seconds return value = 0
sel_100_ncl() 0.19 seconds return value = 100
table_scan() 0.37 seconds return value = 0
agg_func() 3.50 seconds return value = 100
agg_scal() 0.24 seconds return value = 0
sel_100_cl() 0.19 seconds return value = 100
join_3_ncl() 0.57 seconds return value = 1
sel_10pct_ncl() 1.34 seconds return value = 10000
agg_simple_report() 73.78 seconds return value = 990009900
agg_info_retrieval() 0.08 seconds return value = 0
agg_create_view() 0.02 seconds return value = 0
agg_subtotal_report() 3.41 seconds return value = 1000
agg_total_report() 3.23 seconds return value = 932849
join_2_cl() 0.00 seconds return value = 0
join_2() 0.99 seconds return value = 1000
sel_variable_select_low() 0.24 seconds return value = 0
sel_variable_select_high() 3.39 seconds return value = 25000
join_4_cl() 0.00 seconds return value = 0
proj_100() 4.40 seconds return value = 10000
join_4_ncl() 0.83 seconds return value = 1
proj_10pct() 9.70 seconds return value = 100000
sel_1_ncl() 0.01 seconds return value = 1
join_2_ncl() 0.18 seconds return value = 1
integrity_test() 0.36 seconds return value = 0
drop_updates_keys() 0.02 seconds return value = 0
bulk_save() 0.03 seconds return value = 0
bulk_modify() 164.11 seconds return value = 0
upd_append_duplicate() 0.00 seconds return value = 0
upd_remove_duplicate() 0.00 seconds return value = 0
upd_app_t_mid() 0.00 seconds return value = 1
upd_mod_t_mid() 0.17 seconds return value = 0
upd_del_t_mid() 0.16 seconds return value = 0
upd_app_t_end() 0.00 seconds return value = 1
upd_mod_t_end() 0.17 seconds return value = 0
upd_del_t_end() 0.16 seconds return value = 0
create_idx_updates_code_h() 1.27 seconds return value = 0
upd_app_t_mid() 0.01 seconds return value = 1
upd_mod_t_cod() 0.00 seconds return value = 0
upd_del_t_mid() 0.16 seconds return value = 0
create_idx_updates_int_bt() 0.63 seconds return value = 0
upd_app_t_mid() 0.01 seconds return value = 1
upd_mod_t_int() 0.00 seconds return value = 0
upd_del_t_mid() 0.16 seconds return value = 0
bulk_append() 0.22 seconds return value = 0
bulk_delete() 162.26 seconds return value = 0
"Single User Test" 436.63 seconds (0:07:16.63)
"Executing multi-user tests with 10 user tasks"
Mixed IR (tup/sec) 105.21 returned in 5.00 minutes
sel_1_ncl() 0.01 seconds return value = 1
agg_simple_report() 182.60 seconds return value = 990009900
mu_sel_100_seq() 0.12 seconds return value = 0
mu_sel_100_rand() 0.18 seconds return value = 0
mu_mod_100_seq_abort() 0.33 seconds return value = 0
mu_mod_100_rand() 0.12 seconds return value = 0
mu_unmod_100_seq() 0.16 seconds return value = 0
mu_unmod_100_rand() 0.08 seconds return value = 0
crossSectionTests(Mixed IR) 183.60
mu_checkmod_100_seq() 0.01 seconds return value = 100
mu_checkmod_100_rand() 0.72 seconds return value = 100
Mixed OLTP (tup/sec) 101.48 returned in 5.00 minutes
sel_1_ncl() 0.04 seconds return value = 1
agg_simple_report() 208.05 seconds return value = 990009900
mu_sel_100_seq() 1.90 seconds return value = 0
mu_sel_100_rand() 0.07 seconds return value = 0
mu_mod_100_seq_abort() 0.50 seconds return value = 0
mu_mod_100_rand() 0.23 seconds return value = 0
mu_unmod_100_seq() 0.18 seconds return value = 0
mu_unmod_100_rand() 0.14 seconds return value = 0
crossSectionTests(Mixed OLTP) 211.12
mu_checkmod_100_seq() 0.76 seconds return value = 100
mu_checkmod_100_rand() 11.10 seconds return value = 100
"Multi-User Test" 2830.09 seconds (0:47:10.09)
"osdb"
"Invoked: osdb-pg --postgresql=no_hash_index --logfile 8kb.log"
create_tables() 0.01 seconds return value = 0
load() 17.26 seconds return value = 0
create_idx_uniques_key_bt() 3.59 seconds return value = 0
create_idx_updates_key_bt() 3.75 seconds return value = 0
create_idx_hundred_key_bt() 3.67 seconds return value = 0
create_idx_tenpct_key_bt() 3.55 seconds return value = 0
create_idx_tenpct_key_code_bt() 0.95 seconds return value = 0
create_idx_tiny_key_bt() 0.00 seconds return value = 0
create_idx_tenpct_int_bt() 0.63 seconds return value = 0
create_idx_tenpct_signed_bt() 0.93 seconds return value = 0
create_idx_uniques_code_h() 1.42 seconds return value = 0
create_idx_tenpct_double_bt() 1.09 seconds return value = 0
create_idx_updates_decim_bt() 2.45 seconds return value = 0
create_idx_tenpct_float_bt() 1.09 seconds return value = 0
create_idx_updates_int_bt() 0.62 seconds return value = 0
create_idx_tenpct_decim_bt() 2.32 seconds return value = 0
create_idx_hundred_code_h() 1.42 seconds return value = 0
create_idx_tenpct_name_h() 1.38 seconds return value = 0
create_idx_updates_code_h() 1.27 seconds return value = 0
create_idx_tenpct_code_h() 1.28 seconds return value = 0
create_idx_updates_double_bt() 1.09 seconds return value = 0
create_idx_hundred_foreign() 11.02 seconds return value = 0
populateDataBase() 60.79 seconds return value = 0
"Logical database size 40MB"
sel_1_cl() 0.01 seconds return value = 1
join_3_cl() 0.01 seconds return value = 0
sel_100_ncl() 0.18 seconds return value = 100
table_scan() 0.38 seconds return value = 0
agg_func() 3.50 seconds return value = 100
agg_scal() 0.24 seconds return value = 0
sel_100_cl() 0.18 seconds return value = 100
join_3_ncl() 7.73 seconds return value = 1
sel_10pct_ncl() 1.35 seconds return value = 10000
agg_simple_report() 75.62 seconds return value = 990009900
agg_info_retrieval() 0.08 seconds return value = 0
agg_create_view() 0.01 seconds return value = 0
agg_subtotal_report() 3.41 seconds return value = 1000
agg_total_report() 3.19 seconds return value = 932849
join_2_cl() 0.00 seconds return value = 0
join_2() 1.00 seconds return value = 1000
sel_variable_select_low() 0.22 seconds return value = 0
sel_variable_select_high() 3.41 seconds return value = 25000
join_4_cl() 0.00 seconds return value = 0
proj_100() 4.41 seconds return value = 10000
join_4_ncl() 10.74 seconds return value = 1
proj_10pct() 9.87 seconds return value = 100000
sel_1_ncl() 0.00 seconds return value = 1
join_2_ncl() 3.65 seconds return value = 1
integrity_test() 0.34 seconds return value = 0
drop_updates_keys() 0.02 seconds return value = 0
bulk_save() 0.03 seconds return value = 0
bulk_modify() 162.64 seconds return value = 0
upd_append_duplicate() 0.00 seconds return value = 0
upd_remove_duplicate() 0.00 seconds return value = 0
upd_app_t_mid() 0.00 seconds return value = 1
upd_mod_t_mid() 0.16 seconds return value = 0
upd_del_t_mid() 0.17 seconds return value = 0
upd_app_t_end() 0.00 seconds return value = 1
upd_mod_t_end() 0.16 seconds return value = 0
upd_del_t_end() 0.17 seconds return value = 0
create_idx_updates_code_h() 1.27 seconds return value = 0
upd_app_t_mid() 0.01 seconds return value = 1
upd_mod_t_cod() 0.00 seconds return value = 0
upd_del_t_mid() 0.16 seconds return value = 0
create_idx_updates_int_bt() 0.63 seconds return value = 0
upd_app_t_mid() 0.00 seconds return value = 1
upd_mod_t_int() 0.00 seconds return value = 0
upd_del_t_mid() 0.17 seconds return value = 0
bulk_append() 0.23 seconds return value = 0
bulk_delete() 163.47 seconds return value = 0
"Single User Test" 458.83 seconds (0:07:38.83)
"Executing multi-user tests with 10 user tasks"
Mixed IR (tup/sec) 102.59 returned in 5.00 minutes
sel_1_ncl() 0.01 seconds return value = 1
agg_simple_report() 238.69 seconds return value = 990009900
mu_sel_100_seq() 0.04 seconds return value = 0
mu_sel_100_rand() 0.02 seconds return value = 0
mu_mod_100_seq_abort() 0.32 seconds return value = 0
mu_mod_100_rand() 0.18 seconds return value = 0
mu_unmod_100_seq() 0.21 seconds return value = 0
mu_unmod_100_rand() 0.09 seconds return value = 0
crossSectionTests(Mixed IR) 239.56
mu_checkmod_100_seq() 0.02 seconds return value = 100
mu_checkmod_100_rand() 0.72 seconds return value = 100
Mixed OLTP (tup/sec) 97.46 returned in 5.00 minutes
sel_1_ncl() 0.05 seconds return value = 1
agg_simple_report() 168.05 seconds return value = 990009900
mu_sel_100_seq() 0.26 seconds return value = 0
mu_sel_100_rand() 0.03 seconds return value = 0
mu_mod_100_seq_abort() 0.29 seconds return value = 0
mu_mod_100_rand() 0.16 seconds return value = 0
mu_unmod_100_seq() 0.13 seconds return value = 0
mu_unmod_100_rand() 0.27 seconds return value = 0
crossSectionTests(Mixed OLTP) 169.26
mu_checkmod_100_seq() 0.25 seconds return value = 100
mu_checkmod_100_rand() 13.33 seconds return value = 100
"Multi-User Test" 2843.70 seconds (0:47:23.70)
tcpip_socket = true
#ssl = false
max_connections =256
#port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#krb_server_keyfile = ''
#
# Shared Memory Size
#
shared_buffers = 4096 # 2*max_connections, min 16
max_fsm_relations = 200 # min 10, fsm is free space map
max_fsm_pages = 10000 # min 1000, fsm is free space map
#max_locks_per_transaction = 64 # min 10
wal_buffers = 32 # min 4
#
# Non-shared Memory Sizes
#
sort_mem = 8192 # min 32
vacuum_mem = 32768 # min 1024
#
# Write-ahead log (WAL)
#
wal_files = 16 # range 0-64
wal_sync_method = fdatasync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
checkpoint_timeout = 600 # in seconds, range 30-3600
fsync = false
#
# Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true
#ksqo = false
#effective_cache_size = 2000 # default in 8k pages
#random_page_cost = 1
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025
#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on #tables in query, range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed
#
# Debug display
#
#silent_mode = false
#log_connections = false
#log_timestamp = false
#log_pid = false
#debug_level = 0 # range 0-16
#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
# requires USE_ASSERT_CHECKING
#debug_assertions = true
#
# Syslog
#
# requires ENABLE_SYSLOG
#syslog = 0 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#
# Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false
# requires BTREE_BUILD_STATS
#show_btree_build_stats = false
#
# Access statistics collection
#
#stats_start_collector = true
#stats_reset_on_server_start = true
#stats_command_string = false
#stats_row_level = false
#stats_block_level = false
#
# Lock Tracing
#
#trace_notify = false
# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#
# Misc
#
#dynamic_library_path = '$libdir'
#australian_timezones = false
#authentication_timeout = 60 # min 1, max 600
#deadlock_timeout = 1000
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000 # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = false
#sql_inheritance = true
#transform_null_equals = false
tcpip_socket = true
#ssl = false
max_connections =256
#port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#krb_server_keyfile = ''
#
# Shared Memory Size
#
shared_buffers = 8192 # 2*max_connections, min 16
max_fsm_relations = 200 # min 10, fsm is free space map
max_fsm_pages = 10000 # min 1000, fsm is free space map
#max_locks_per_transaction = 64 # min 10
wal_buffers = 32 # min 4
#
# Non-shared Memory Sizes
#
sort_mem = 8192 # min 32
vacuum_mem = 32768 # min 1024
#
# Write-ahead log (WAL)
#
wal_files = 16 # range 0-64
wal_sync_method = fdatasync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
checkpoint_timeout = 600 # in seconds, range 30-3600
fsync = false
#
# Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true
#ksqo = false
#effective_cache_size = 2000 # default in 8k pages
#random_page_cost = 1
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025
#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on #tables in query, range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed
#
# Debug display
#
#silent_mode = false
#log_connections = false
#log_timestamp = false
#log_pid = false
#debug_level = 0 # range 0-16
#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
# requires USE_ASSERT_CHECKING
#debug_assertions = true
#
# Syslog
#
# requires ENABLE_SYSLOG
#syslog = 0 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#
# Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false
# requires BTREE_BUILD_STATS
#show_btree_build_stats = false
#
# Access statistics collection
#
#stats_start_collector = true
#stats_reset_on_server_start = true
#stats_command_string = false
#stats_row_level = false
#stats_block_level = false
#
# Lock Tracing
#
#trace_notify = false
# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#
# Misc
#
#dynamic_library_path = '$libdir'
#australian_timezones = false
#authentication_timeout = 60 # min 1, max 600
#deadlock_timeout = 1000
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000 # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = false
#sql_inheritance = true
#transform_null_equals = false
pgsql-hackers by date: