X-Original-To: pgsql-advocacy-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8D18CD1DA3C for ; Thu, 22 Apr 2004 14:11:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 38476-02 for ; Thu, 22 Apr 2004 14:11:05 -0300 (ADT) Received: from web60607.mail.yahoo.com (web60607.mail.yahoo.com [216.109.118.245]) by svr1.postgresql.org (Postfix) with SMTP id 8D53CD1D26C for ; Thu, 22 Apr 2004 14:11:03 -0300 (ADT) Message-ID: <20040422171034.91737.qmail@web60607.mail.yahoo.com> Received: from [200.17.210.82] by web60607.mail.yahoo.com via HTTP; Thu, 22 Apr 2004 10:10:34 PDT Date: Thu, 22 Apr 2004 10:10:34 -0700 (PDT) From: Eduardo Almeida Subject: Re: [PERFORM] MySQL vs PG TPC-H benchmarks To: Jan Wieck Cc: Josh Berkus , pgsql-advocacy@postgresql.org, pgsql-performance@postgresql.org In-Reply-To: <4087F0A3.6040009@Yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/212 X-Sequence-Number: 4184 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 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 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