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
[EMAIL PROTECTED]
[EMAIL PROTECTED]
--- Jan Wieck <[EMAIL PROTECTED]> 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 wronghttp://www.postgresql.org/docs/faqs/FAQ.html
than for being right. #
# Let's break this rule - forgive me. #
#==================================================
[EMAIL PROTECTED] #
---------------------------(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?
__________________________________ 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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
