mysql和postgresql性能对比测试

今天突然想知道mysql和postgresql的性能哪个好些

搜索看了一些文章，然后看到一篇专业的测试文章

网址是http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html#agg_subtotal_report()

对比测试的环境如下

电脑配置

Hardware Resources
CPU: Intel(R) Pentium(R) D CPU 3.00GHz Dual Core
RAM: 3G Ram
HDD: WDC WD3200AAJS-0

测试安装的软件

Software Resources
Debian Lenny 5.0 64 bit arch
Linux painkiller 2.6.26-2-amd64 #1 SMP Wed May 13 15:37:46 UTC 2009 x86_64 GNU/Linux
MySQL 5.0.51a-24+lenny1
MySQL 5.1.30 Sun compiled
InnoDB 1.0.3 Plugin compiled by ORACLE for MySQL 5.1.30
PostgreSQL 8.3.7
osdb 2.1 – Opensource Database benchmark.

 

使用osdb生成了1024m的数据来测试

The test was made on a 1024MB of data and the multiple users test was made with 100 users simultaneously on the same database.

截取一部分测试结果大家看

SQL Operation	MySQL 5.0.51	MySQL 5.1.30 with InnoDB 1.0.3	PostgreSQL
	seconds	seconds	seconds
sel_1_cl()	0	0.02	0.04
join_3_cl()	0.06	0.05	0.02
sel_100_ncl()	0.01	0.01	0.03
table_scan()	10.54	4.13	0.64
oin_2()	1.78	1.62	0.89
sel_variable_select_low()	0.03	0.02	0
sel_variable_select_high()	12.52	7.82	49.51
join_4_cl()	0	0	0
proj_100()	7.13	7.35	149.97
join_4_ncl()	0.03	0	0
proj_10pct()	9.13	2.68	20.06

 

table_scan()

select * from uniques where col_int = 1

sel_1_cl()	select col_key, col_int, col_signed, col_code, col_double, col_name from updates where col_key = 1000
sel_1_ncl()	select col_key, col_int, col_signed, col_code, col_double, col_name from updates where col_code = ‘BENCHMARKS’
sel_100_cl()	select col_key, col_int, col_signed, col_code, col_double, col_name from updates where col_key <= 100
sel_100_ncl()	select col_key, col_int, col_signed, col_code, col_double, col_name from updates where col_int <= 100

sel_variable_select_high()	select col_key, col_int, col_signed, col_code, col_double, col_name from tenpct where col_signed < :prog_var;
sel_variable_select_low()	select col_key, col_int, col_signed, col_code, col_double, col_name from tenpct where col_signed < :prog_var;

join_4_cl()	select uniques.col_date, hundred.col_date, tenpct.col_date, updates.col_date from uniques, hundred, tenpct, updates where uniques.col_key = hundred.col_key and uniques.col_key = tenpct.col_key and uniques.col_key = updates.col_key and uniques.col_key = 1000
join_4_ncl()	select uniques.col_date, hundred.col_date, tenpct.col_date, updates.col_date from uniques, hundred, tenpct, updates where uniques.col_code = hundred.col_code and uniques.col_code = tenpct.col_code and uniques.col_code = updates.col_code and uniques.col_code = ‘BENCHMARKS’

sel_variable_select_high()	select col_key, col_int, col_signed, col_code, col_double, col_name from tenpct where col_signed < :prog_var;
sel_variable_select_low()	select col_key, col_int, col_signed, col_code, col_double, col_name from tenpct where col_signed < :prog_var;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------

 在一些常规的根据表的键查询MySQL 5.1.30 with InnoDB 1.0.3居然还快过postgresql

sel_1_cl()

select col_key, col_int, col_signed, col_code, col_double, col_name from updates where col_key = 1000

 

但是表扫描就要慢了

table_scan()

select * from uniques where col_int = 1

 

可见mysql也不是被postgresql比的一无是处

大家可以再研究下对比结果