Re: [pgsql-advocacy] MySQL vs PG TPC-H benchmarks
От | Eduardo Almeida |
---|---|
Тема | Re: [pgsql-advocacy] MySQL vs PG TPC-H benchmarks |
Дата | |
Msg-id | 20040422171034.91737.qmail@web60607.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: [pgsql-advocacy] MySQL vs PG TPC-H benchmarks (Jan Wieck <JanWieck@Yahoo.com>) |
Список | 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
В списке pgsql-performance по дате отправления: