Re: Postgres performance - Mailing list pgsql-sql
| From | bertolima@yahoo.it (mauro) |
|---|---|
| Subject | Re: Postgres performance |
| Date | |
| Msg-id | d64dda1e.0503010252.67c2312d@posting.google.com Whole thread Raw |
| In response to | Postgres performance (bertolima@yahoo.it (mauro)) |
| Responses |
Re: Postgres performance
(Michael Fuhr <mike@fuhr.org>)
Re: Postgres performance (Richard Huxton <dev@archonet.com>) Re: Postgres performance (Scott Marlowe <smarlowe@g2switchworks.com>) |
| List | pgsql-sql |
> 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 (bhcompile@dolly.build.redhat.com) (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!