Re: [pgsql-advocacy] MySQL vs PG TPC-H benchmarks - Mailing list pgsql-performance

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 I�ll send the
EXPLAIN for this ones.

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

regards
Eduardo
--- Jan Wieck <JanWieck@Yahoo.com> wrote:
> Eduardo Almeida wrote:
>
> > Folks,
> >
> > I�m doing the 100GB TPC-H and I�ll 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 that�s why I�ll
> not
> > present anything so far. Now I�ll 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
> > Administra��o de Banco de Dados
> > UFPR - CCE
> > +55-41-361-3321
> > eduardo.almeida@ufpr.br
> > edalmeida@yahoo.com
> >
> > --- Jan Wieck <JanWieck@Yahoo.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