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
[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 wrong
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?


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.                                  #
#================================================== [EMAIL PROTECTED] #


---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster

Reply via email to