Skip site navigation (1) Skip section navigation (2)

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

From: Eduardo Almeida <edalmeida(at)yahoo(dot)com>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-advocacy(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] MySQL vs PG TPC-H benchmarks
Date: 2004-04-22 12:53:18
Message-ID: 20040422125318.26053.qmail@web60601.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-performance
Folks,

Im doing the 100GB TPC-H and Ill 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.

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 thats why Ill not
present anything so far. Now Ill 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
Administrao de Banco de Dados
UFPR - CCE 
+55-41-361-3321
eduardo(dot)almeida(at)ufpr(dot)br
edalmeida(at)yahoo(dot)com

--- Jan Wieck <JanWieck(at)Yahoo(dot)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(at)Yahoo(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Grega BremecDate: 2004-04-22 13:42:49
Subject: Re: [PERFORM] MySQL vs PG TPC-H benchmarks
Previous:From: Tom LaneDate: 2004-04-22 12:36:28
Subject: Re: Wierd context-switching issue on Xeon patch for 7.4.1

pgsql-advocacy by date

Next:From: Merlin MoncureDate: 2004-04-22 13:08:04
Subject: Re: Fwd: Re: [PERFORM] MySQL vs PG TPC-H benchmarks
Previous:From: Gavin SherryDate: 2004-04-22 08:55:06
Subject: Re: Fwd: Re: [PERFORM] MySQL vs PG TPC-H benchmarks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group