Re: [PERFORM] MySQL vs PG TPC-H benchmarks

Folks,

Im doing the 100GB TPC-H and Ill show the previous
results to our community (Postgres) in 3 weeks before
finishing the study.

My intention is to carry through a test with a VLDB in
a low cost platform (PostgreSQL, Linux and cheap HW)
and not to compare with another DBMS.

So far I can tell you that the load time on PG 7.4.2
with kernel 2.6.5 on Opteron 64 model 240 in RAID 0
with 8 disks (960 GB) loaded the database in less than
24 hours.
About 7hs:30min to load the data and 16:09:25 to
create the indexes

The Power test still running and thats why Ill not
present anything so far. Now Ill just send to the
list my environment configuration.

- The configuration of the machine is:
Dual opteron 64 bits model 240
4GB RAM
960 GB on RAID 0
Mandrake Linux 64 with Kernel 2.6.5 (I compiled a
kernel for this test)
Java SDK java version "1.4.2_04"
PostgreSQL JDBC pg74.1jdbc3.jar

- The TPC-H configuration is:
TPC-H 2.0.0
100GB
load using flat files
Refresh functions using java

- The PostgreSQL 7.4.2 configuration is:

add_missing_from | on
australian_timezones | off
authentication_timeout | 60
check_function_bodies | on
checkpoint_segments | 128
checkpoint_timeout | 300
checkpoint_warning | 30
client_encoding | SQL_ASCII
client_min_messages | notice
commit_delay | 0
commit_siblings | 5
cpu_index_tuple_cost | 0.001
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
DateStyle | ISO, MDY
db_user_namespace | off
deadlock_timeout | 1000
debug_pretty_print | off
debug_print_parse | off
debug_print_plan | off
debug_print_rewritten | off
default_statistics_target | 10
default_transaction_isolation | read committed
default_transaction_read_only | off
dynamic_library_path | $libdir
effective_cache_size | 150000
enable_hashagg | on
enable_hashjoin | on
enable_indexscan | on
enable_mergejoin | on
enable_nestloop | on
enable_seqscan | on
enable_sort | on
enable_tidscan | on
explain_pretty_print | on
extra_float_digits | 0
from_collapse_limit | 8
fsync | off
geqo | on
geqo_effort | 1
geqo_generations | 0
geqo_pool_size | 0
geqo_selection_bias | 2
geqo_threshold | 11
join_collapse_limit | 8
krb_server_keyfile | unset
lc_collate | en_US
lc_ctype | en_US
lc_messages | C
lc_monetary | C
lc_numeric | C
lc_time | C
log_connections | off
log_duration | off
log_error_verbosity | default
log_executor_stats | off
log_hostname | off
log_min_duration_statement | -1
log_min_error_statement | panic
log_min_messages | notice
log_parser_stats | off
log_pid | off
log_planner_stats | off
log_source_port | off
log_statement | off
log_statement_stats | off
log_timestamp | off
max_connections | 10
max_expr_depth | 10000
max_files_per_process | 1000
max_fsm_pages | 20000
max_fsm_relations | 1000
max_locks_per_transaction | 64
password_encryption | on
port | 5432
pre_auth_delay | 0
preload_libraries | unset
random_page_cost | 1.25
regex_flavor | advanced
rendezvous_name | unset
search_path | $user,public
server_encoding | SQL_ASCII
server_version | 7.4.2
shared_buffers | 40000
silent_mode | off
sort_mem | 65536
sql_inheritance | on
ssl | off
statement_timeout | 10000000
stats_block_level | off
stats_command_string | off
stats_reset_on_server_start | on
stats_row_level | off
stats_start_collector | on
superuser_reserved_connections | 2
syslog | 0
syslog_facility | LOCAL0
syslog_ident | postgres
tcpip_socket | on
TimeZone | unknown
trace_notify | off
transaction_isolation | read committed
transaction_read_only | off
transform_null_equals | off
unix_socket_directory | unset
unix_socket_group | unset
unix_socket_permissions | 511
vacuum_mem | 65536
virtual_host | unset
wal_buffers | 32
wal_debug | 0
wal_sync_method | fdatasync
zero_damaged_pages | off
(113 rows)

suggestions, doubts and commentaries are very welcome

regards
______________________________
Eduardo Cunha de Almeida
Administrao de Banco de Dados
UFPR - CCE
+55-41-361-3321
eduardo(dot)almeida(at)ufpr(dot)br
edalmeida(at)yahoo(dot)com

--- Jan Wieck <JanWieck(at)Yahoo(dot)com> wrote:
> Josh Berkus wrote:
>
> > Folks,
> >
> > I've sent a polite e-mail to Mr. Gomez offering
> our help. Please, nobody
> > flame him!
> >
>
> Please keep in mind that the entire test has, other
> than a similar
> database schema and query types maybe, nothing to do
> with a TPC-H. I
> don't see any kind of SUT. Foreign key support on
> the DB level is not
> required by any of the TPC benchmarks. But the
> System Under Test, which
> is the combination of middleware application and
> database together with
> all computers and network components these parts are
> running on, must
> implement all the required semantics, like ACID
> properties, referential
> integrity &c. One could implement a TPC-H with flat
> files, it's just a
> major pain in the middleware.
>
> A proper TPC benchmark implementation would for
> example be a complete
> PHP+DB application, where the user interaction is
> done by an emulated
> "browser" and what is measured is the http response
> times, not anything
> going on between PHP and the DB. Assuming that all
> requirements of the
> TPC specification are implemented by either using
> available DB features,
> or including appropriate workarounds in the PHP
> code, that would very
> well lead to something that can compare PHP+MySQL
> vs. PHP+PostgreSQL.
>
> All TPC benchmarks I have seen are performed by
> timing such a system
> after a considerable rampup time, giving the DB
> system a chance to
> properly populate caches and so forth. Rebooting the
> machine just before
> the test is the wrong thing here and will especially
> kill any advanced
> cache algorithms like ARC.
>
>
> Jan
>
> --
>
#======================================================================#
> # It's easier to get forgiveness for being wrong
> than for being right. #
> # Let's break this rule - forgive me.
> #
> #==================================================
> JanWieck(at)Yahoo(dot)com #
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>
> http://www.postgresql.org/docs/faqs/FAQ.htmlIP 5:
> Have you checked our extensive FAQ?
>
>
http://www.postgresql.org/docs/faqs/FAQ.html

__________________________________
Do you Yahoo!?
Yahoo! Photos: High-quality 4x6 digital prints for 25
http://photos.yahoo.com/ph/print_splash