> Not always, AFAICT. The four most common reasons why PG tests slower
> than Mysql are:
> 1. You haven't configured or have misconfigured PostgreSQL.
> 2. You are testing a MySQL-tuned application (lots of small, simple
> queries, no views, no subselects etc)
> 3. You are only testing one connection (try 10,20,50 simultaneous users
> and see who wins then).
> 4. You are not testing the transaction-safe storage systems in MySQL
>
> See if you can answer some of the questions above and I'm sure we'll be
> able to get your database server running smoothly.
Hi,
I've used the benchmark
http://genecensus.org/yeast/cluster/database/mysql/sql-bench/ (ok,
it's without bench on views, sub-select, transaction,..)
The database files are in stripe (RAID 0) on two SATA hd (transfer
rate 50Mb), the filesystem is reiserfs (3.6 format - with checks off),
no optimation on I/O scheduler, DBMS are in default configuration (so
I don't benefit nobody). Total time:
Pgsql: 7h 20'
MySQL: 14' (!!)
This is the configuration where is running Postgres 8.0 and MySql:
Linux version 2.6.9-1.667 ([EMAIL PROTECTED]) (gcc
version 3.4.2
20041017 (Red Hat 3.4.2-6.fc3)) #1 Tue Nov 2 14:50:10 EST 2004
Memory: 1024128k/1047744k available (2389k kernel code, 22900k
reserved, 1276k d
ata, 164k init)
CPU: L1 I Cache: 64K (64 bytes/line), D cache 64K (64 bytes/line)
CPU: L2 Cache: 512K (64 bytes/line)
CPU: AMD Athlon(tm) 64 Processor 3000+ stepping 00
powernow-k8: Found 1 AMD Athlon 64 / Opteron processors (version
1.00.09b)
powernow-k8: BIOS error - no PSB
hda: SAMSUNG SP0411N, ATA DISK drive
hda: max request size: 1024KiB
hda: 78242976 sectors (40060 MB) w/2048KiB Cache, CHS=16383/255/63,
UDMA(133)
ata1: SATA max UDMA/133 cmd 0xE800 ctl 0xE402 bmdma 0xD400 irq 193
ata2: SATA max UDMA/133 cmd 0xE000 ctl 0xD802 bmdma 0xD408 irq 193
cpuinfo
processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 12
model name : AMD Athlon(tm) 64 Processor 3000+
stepping : 0
cpu MHz : 2002.652
cache size : 512 KB
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm
3dnowext 3dnow
bogomips : 3932.16
TLB size : 1088 4K pages
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management: ts fid vid ttp
The analisys:
Test Postgres (sec) Mysql (sec)
alter_table_add 1 34
alter_table_drop 27
connect 36 4
connect+select_1_row 45 5
connect+select_simple 38 4
count 21 5
count_distinct 9 7
count_distinct_2 17 11
count_distinct_big 87 26
count_distinct_group 57 16
count_distinct_group_on_key 19 7
count_distinct_group_on_key_parts 56 16
count_distinct_key_prefix 38 6
count_group_on_key_parts 7 7
count_on_key 226 53
create+drop 145 3
create_MANY_tables 50 3
create_index 1 1
create_key+drop 98 5
create_table 0 0
delete_all 13 2
delete_all_many_keys 11 10
delete_big 0 0
delete_big_many_keys 11 10
delete_key 6 1
drop_index 0 0
drop_table 0
drop_table_when_MANY_tables 23 2
insert 177 24
insert_duplicates 59 6
insert_key 695 21
insert_many_fields 84 2
insert_select_1_key 6 1
insert_select_2_keys 8 1
min_max 9 3
min_max_on_key 1145 27
multiple_value_insert 1
order_by_big 25 19
order_by_big_key 19 14
order_by_big_key2 17 14
order_by_big_key_desc 19 15
order_by_big_key_diff 17 18
order_by_big_key_prefix 17 15
order_by_key2_diff 2 2
order_by_key_prefix 0 1
order_by_range 1 1
outer_join 40 8
outer_join_found 38 8
outer_join_not_found 37 5
outer_join_on_key 38 6
select_1_row 2 0
select_2_rows 1 1
select_big 16 14
select_big_str 12 8
select_column+column 2 0
select_diff_key 120 24
select_distinct 3
select_group 46 20
select_group_when_MANY_tables 9 1
select_join 1
select_key 66 25
select_key2 59 26
select_key2_return_key 57 25
select_key2_return_prim 59 25
select_key_prefix 59 27
select_key_prefix_join 3
select_key_return_key 3 24
select_many_fields 48 5
select_query_cache 104 21
select_query_cache2 104 22
select_range 188 42
select_range_key2 8922 4
select_range_prefix 8905 4
select_simple 1 1
select_simple_join 0
update_big 467 8
update_of_key 58 3
update_of_key_big 27 4
update_of_primary_key_many_keys 2049 5
update_with_key 269 17
update_with_key_prefix 88 5
wisc_benchmark 3 2
TOTALS 25273 847
What do you think about this?
Thank you!
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
