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

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
>> 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@Yahoo.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


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #