discuz真实环境中postgres与myisam查询性能对比,还有人说mysql快么

mark35 发布于 2012/07/24 21:40
阅读 5K+
收藏 13

上贴在此 【见识下postgreSQL的强悍,对比下mysql的低能】 http://www.oschina.net/question/126398_61956

引发某些mysqler的质疑,因为论坛已经迁移到新系统所以无法以老系统做测试获得对比数据。不过数据都在,我就做个数据库无压力的对比测试吧。

数据来源:www.ifxtx.com

操作系统:CentOS 6.2 X64

mysql版本:mysql-5.1.61-1.el6_2.1.x86_64 (YUM)

pg版本:9.1.4

web服务器与db服务器配置皆为 DELL 2950III,硬盘前者为SAS RAID5,后者为 SSD RAID,其余硬件配置配置相同。

测试环境:web服务器上mysql/pg皆无外部连接,db 服务器上mysql无外部连接pg为上线论坛正式营运库。营运pg库是最新的数据量要比其余3个库要多,其余三个库内容相同。

测试方式:除了db服务器上pg所有测试都不停机、重启,其余3个数据库先关闭服务30分钟,启动服务器开始第一轮测试,第一轮测试间隔3秒重复测试3次,restart数据库之后重复测试一轮。

测试SQL:来自于dz数据调用的一条实际SQL,此语句在论坛老系统中超时频繁,严重阻塞队列。如图

因为mysql垃圾的GROUP BY功能,此条SQL无法直接运行在pg上面,所以我进行修改:

-- for pg

SELECT t.tid,t.fid,t.readperm,t.author,t.authorid,t.subject,t.dateline, 
 t.lastpost,t.lastposter,t.views,t.replies, t.highlight,t.digest,t.typeid,t.sortid, 
 a.remote,a.attachment,a.thumb ,p.message   FROM cdb_threads t 
	INNER JOIN (
	 SELECT t.tid, max(aid) aid FROM cdb_threads t JOIN cdb_attachments a  ON a.tid = t.tid
	 	WHERE t.readperm='0' AND t.fid IN ('87') AND a.readperm = 0  AND a.isimage > 0 
		AND a.price = 0  AND t.displayorder>='0'   AND t.fid>'0'
	  	GROUP BY t.tid  ORDER BY t.dateline DESC LIMIT 4
	) a1 ON a1.tid = t.tid
	LEFT JOIN cdb_posts p ON p.tid=t.tid AND p.first='1' 
INNER JOIN cdb_attachments a ON a.tid=t.tid ORDER BY t.dateline DESC

osc发帖不支持直接绘制表格,于是用wps的。请看测试结果:

1 min 17.48 sec  是啥概念?难道测试错了,于是关闭web服务器mysql服务5分钟,启动后重新测试,结果分别是

4 rows in set (2.37 sec)
4 rows in set (1.60 sec)
4 rows in set (0.00 sec)

这好像看起来才正常嘛。也许因为web服务器上面跑着生产的web server以及附件,附件IO读取对mysql影响很大。

为了排除疑点,我重新开始一轮测试,只不过把 SQL中fid的值由87改成12,结果让人吃惊:

难怪一说到mysql慢就会有人说是没优化好,可my.cnf除了buff还有啥可优化的呢?既不能选择连表方式(hashjoin, merge join, neste join) 也不能对查询计划CPU/DISC因子调整。原来mysql就是靠缓存吃饭的啊!而且这个缓存也忒强悍了点吧,我关闭了服务器5分钟后缓存依旧存在,要不是第一次是关闭了30分钟还不能确定真有这么慢!

原来都说mysql快,大家也知道是快在myisam上面。可现在活生生的实际测试对比,让我怀疑以前那些测试是怎么做的~

以前如果有人说pg快,那么会对曰那是在高并发下快,普通应用没myisam快。如果有人说myisam不支持事务,没行锁,有对曰innodb支持事务也有行锁。

现在,我真没觉得mysql有什么可以和pg进行抗衡之处。除了可以绿色版安装。

可当postgreSQL 9.2版本出来,支持 index-only-scan这个功能后,我不知道mysql在技术上还有什么可值得一用的?

复读机:mysql有myisam,innodb,mem等各种引擎,各有特色。在我看来就实体版的要你命3000 —— 五花八门特色什么功能都有,可没一个称手可堪重任的。

 

有不服气的mysqler不要来虚的,红口白牙空口无凭,要来就直接上真实环境的测试数据。

以下是话题补充:

@zgz88:这个用pg9.1.4对比mysql 5.1有点不妥,升级到5.5对比一下 根据我的经验,超算杂的场景,pg可以一比mysql (2012/07/26 10:06)
加载中
1
l
laosong

引用来自“laosong”的答案

记得n年前俺还是个菜鸟的时候,那会也曾迷信过pg,比较过pg和ms sql server,造了个1千万记录的表,在普通查询上pg的速度和ms sql server有数量级的差距
pg慢,06年,pg是8.2(不确定),ms sql server是2000,因为那时我在的公司是搞数据服务的,数据量都在千万级别,所以试了下千万级别的比较。在索引的使用上,pg明显不如ms sql server聪明,为此专门看过pg的代码(pg的代码的确很棒,清晰明了,注释超多),反编译过ms sql server的代码,具体就为弄清楚它们的差距究竟在哪,其实最后也没看懂,但学了很多查询优化器中概念
0
mark35
mark35
db服务器上pg是跑着正式论坛,数据量要比其余3个多,并且有真实负载。而其余3个毫无负载,就控制台登录上去测试的一个连接
0
mark35
mark35

我晕,红薯,你这编辑器怎么粘贴了表格上去看着正常,提交之后就不显示了呢?

帮我编辑使用下方图片。另外请 帮我把标题的 “dz实际” 改成 “discuz真实”,谢谢

 

0
mark35
mark35

结论:

mysql是靠缓存忽悠人的,所谓的极速都在缓存上,如果是冷启动或冷查询,那么其性能基于不同磁盘性能将可能会低得令人发指。如果写入极少,那么这种问题还不够明显,如果写入频繁无缓存可用,那将会是致命的

 

Raynor1
Raynor1
@mark35 不说了.睡觉去了.风大雨大.祖国强大..就这样..拜拜..
mark35
mark35
回复 @俞慧涛 : 我这儿又不是来咨询如何解决mysql的问题,而是在同样的硬件环境、数据环境下的对比测试。你咋不说买个小机加上存储那mysql就没问题了? 你的逻辑思路有问题。
Raynor1
Raynor1
@mark35 楼主脑残无下限还说别人.我说你这一个问题可以使用读写分离来实现.你就是说别的数据库也可以实现.P话
mark35
mark35
回复 @俞慧涛 : mysqler就是那么的脑残无极限。读写分离是mysql独有的么?你mysql可以靠读写分离提升性能,我oracle、mssql、pgsql就不能用么?
Raynor1
Raynor1
楼主不知道有种技术叫做读写分离..你竟然还认同.
下一页
0
mark35
mark35

再劳烦小编修正下文字:

我关闭了服务器5分钟后都还有
应为
我关闭了mysql服务5分钟后缓存依旧存在
语句不严谨,容易产生误会~

 

0
鉴客
鉴客
MySQL 对应的 SQL 语句是什么?
mark35
mark35
第一张黑底的截图……
0
mark35
mark35

转个文章

http://obmem.info/?p=317

以后谁再跟我说MySQL性能好我跟谁急……

TNND,今天浪费了一天的时间在Mysql上面,先是改代码,然后是转换sqlite3数据库到mysql,然后发现原来好好的网站跑不起来了。 @。@ 然后就这么折腾了半天,基本上确定了,在select语句上,mysql的性能平均落后sqlite十倍左右,内存消耗超过sqlite则是三倍左右。
-
实际上mysql更灵活点,我的意思是:给mysql三倍的内存,那么他的表现只比sqlite慢十倍而已,如果你给他很抠门的内存?那么超时是唯一的结果。就像我一开始网站挂掉那样。
-

不过今天这么折腾的好处是:
1.对数据库的命令行操作有了全面的认识,现在我已经完全不需要phpmyadmin这种土货了
2.对数据库的优化有了全面的认识,我今天起码看了100篇以上的mysql索引优化方法,官方文档翻了个底朝天,看得吐血
-
结论是:
1.mysql太过傻X,order by怎么样都用不上index,而这应该是用膝盖想都要用index的(如果我错了,请指出,我实在是找遍网上资料,mysql官网文档都快犁了一遍了, 还是没找到如何让order by用index的方法(我是指没有where语句,只有order by,至少sqlite是会去用index的,这从explain语句可以看出))


2.即使用fulltext全文索引,用where加limit搜索同样的内容,如果分页很大(例如limit 10000,1)的话,mysql的执行速度还是比sqlite慢十倍左右。

3.where加order by加limit,其中where和order by的关键字组成索引对,明显可以看出sqlite的性能提升很大,而mysql的性能则毫无多大变化。
-
有人说sqlite不开事务很悲剧,insert时间是别人的十几倍,我靠谁吃饱了撑的有事没事去insert?数据库到底是insert时间多还是 select的时间多,哪怕insert时间比mysql慢一百倍,就冲它select时间比mysql快一百倍以上我就不会用mysql了。(真的有一 百倍,如果用order by关键字的话,因为mysql怎么都教不会它用索引,用force index语句都不行,它就是顽固地要进行filesort,太无语了)
-
有人不信的话我可以提供数据库和测试语句,自己去折腾去
==========================
一些比较:
mysql设置:默认的large配置文件,修改sort相关的buff到16M以上
sqlite设置:无设置
数据库:verycd,表项有id,title,content,updatetime,category等,id是primary integer,数据表项有16万
索引都是三个,updatetime,(updatetime,title),(category,updatetime,title)
语句一:
select title from verycd limit 150000,1;
mysql=1 row in set (4.64 sec)
sqlite3=CPU Time: user 0.116007 sys 0.144009


语句二:
select title from verycd order by updtime desc limit 150000,1;
mysql=1 row in set (3.97 sec)
sqlite3=CPU Time: user 0.032002 sys 0.020001


语句三:
#mysql额外做了fulltext
select title from verycd where title like "%4%" limit 10000,1;
mysql=1 row in set (1.59 sec)
sqlite3=CPU Time: user 0.268016 sys 0.180011


语句四:
select title from verycd where title like "%5%" order by updtime desc limit 10000,1;
mysql=1 row in set (1.79 sec)
sqlite3=CPU Time: user 0.184011 sys 0.020002


语句五:为mysql开个特例,用primary key做索引,它倒是用了,但是……
select title from verycd where title like "%2%" order by verycdid desc limit 10000,1;
mysql=1 row in set (0.40 sec)
你好意思和sqlite3的0.02比么?
=================================
如果是我太土不会用mysql的话请千万告诉我,不然我以后就坚定地抱着mysql性能就是渣的想法了,以后除非高并发,不需要order by,也不需要limit分页的应用,否则很难相信我会去选择mysql了。

 

 

 

 

 

 

mark35
mark35
回复 @喜之郎 : 可能作者贴上来的SQL没对吧
喜之郎
喜之郎
语句三里面作全文索引有什么用,like无法用到啊。不用要写成这样才能用到全文索引么?match(name) against('"key"' in boolean mode)
mark35
mark35
回复 @mahone : 即便有WHERE,但大OFFSET分页下所有数据库都比较慢。
mahone
mahone
回复 @mark35 : 嗯,对。好像是要加where,这样就效率会高点好像。。。我也忘了。。。
mark35
mark35
回复 @mahone : 大OFFSET取数据任何数据库都慢
下一页
0
dy810810
dy810810

如果dz支持pgsql,那至少在大陆没mysql什么事了。那些努力说服老板用pgsql的人就没那么苦B了。

别说bbs的那个战国时代,vbb phpbb。。。mysql靠简单胜出,而终会因此而失败。

0
solookin
solookin

Mysql就是比pgsql查询速度快,pgsql的查询速度就是渣。

怎么样?

0
IdleMan
IdleMan
If you query has no where clause then Oracle has to retrieve all the
rows in the table. In general, a full table scan is quicker than a full
index scan plus a full table's worth of fetch by rowid.
返回顶部
顶部