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

Folks,

I forgot to mention that I used Shell scripts to load
the data and use Java just to run the refresh
functions.

Talking about sort_mem config, I used 65000 but in the
TPCH specification they said that you are not able to
change the configs when you start the benchmark, is
that a big problem to use 65000? In the TPCH 100GB we
run 5 streams in parallel for the throughput test! To
power test I think is not a problem because it runs
one query after another.

Another thing is that I put statement_timeout =
10000000

Some queries may exceed this timeout and Ill send the
EXPLAIN for this ones.

The last thing is that Jan forgets to mention that
Teradata doesnt show up now but in older lists shows
3TB and 10TB results.

regards
Eduardo
--- Jan Wieck <JanWieck(at)Yahoo(dot)com> wrote:
> Eduardo Almeida wrote:
>
> > 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.
>
> QphH and Price/QphH will be enought for us to see
> where in the list we
> are. Unfortunately there are only Sybase and MS SQL
> results published in
> the 100 GB category. The 300 GB has DB2 as well.
> Oracle starts at 1 TB
> and in the 10 TB category Oracle and DB2 are the
> only players left.
>
>
> Jan
>
> >
> > 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
>
=== message truncated ===

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