Discuz真实环境中PostgreSQL与myisam查询性能对比，还有人说mysql快么

上篇传送门：
【Discuz能有多快？ 见识下postgreSQL的强悍，对比下mysql的低能】
https://discuz.dismall.com/thread-2990167-1-1.html

引发某些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/pgsql皆无外部连接，db 服务器上mysql无外部连接，pg为上线论坛正式营运库。营运pg库是最新的数据量要比其余3个库要多，其余三个库内容相同

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

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




因为mysql垃圾的GROUP BY功能，此条SQL无法直接运行在pg上面，所以修改如下：

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

复制代码

---

测试结果：



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就是靠缓存吃饭的啊！而且这个缓存也忒强悍了点吧，我关闭了mysql服务5分钟后缓存依旧存在，要不是第一次是关闭了30分钟还不能确定真有这么慢！
原来都说mysql快，大家也知道是快在myisam上面。可现在活生生的实际测试对比，让我怀疑以前那些测试是怎么做的～
以前如果有人说pg快，那么会对曰那是在高并发下快，普通应用没myisam快。如果有人说myisam不支持事务，没行锁，有对曰innodb支持事务也有行锁


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

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


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

sheaven 发表于 2012-7-25 21:38
如果楼主真心想学习mysql优化的话，推荐一本书 《mysql性能调优与架构设计》先入入门

说实话我现在对于一个事务不支持DDL，分区表功能不完善，不支持一个关系型数据库理应支持的差集、交集运算，不支持部分索引，不支持函数索引，只支持nested loop表连接而不支持merge join/hash join连接，不支持聚集索引，不支持窗口函数，无法调整查询规划器细节，视图不能INSERT/UPDATE，视图不支持触发器，触发器只能绑定一个，尤其对于数据完整性无法保证一致，更无法保证数据库数据安全的mysql毫无兴趣。不想把有限的精力去折腾这家伙～

因为跑在mysql上面的数据99%是不值钱的……

明镜 发表于 2012-10-5 19:53
用remi的源

前段时间打算做测试，用remi源装上了mysql5.5，在冷数据时比YUM的mysql5.0大概快一倍，但距离pgsql还是有差距。不过后来有事耽搁没全面测试完。

基本上对于一个成熟的程序，如果不是因为对性能有提升的新构架型功能加入，否则普通升级能有很大性能提升，那我可以认为以前的版本非常的烂。所以即便mysql5.5比5.0快了许多，但我依然认为mysql很烂，这是原因之一。
比如pgsql 9.2引入了index-onsy scan技术，这个技术在mysql早就具有（其名称是coverage index scan 覆盖索引扫描），结构是索引中包含该索引数据的值，功能是当扫描索引时就可以直接返回该行字段的值，可以避免对数据堆的（随机）访问。 pg9.2有此技术后性能提升明显，尤其是SELECT (*)时相当明显。而在pg9.2之前的多个版本升级速度并未有这么大幅度提升（除了9.1加入的UNLOGGED表），可见pgsql的构架相当稳定，没啥垃圾代码可通过一次升级来让系统得到大幅度提升！

另外需要注意的是 myisam做select count(*)速度快不是因为这覆盖索引功能，而是内部一个缓存计数器实现的。但悲摧的是innodb具有覆盖索引功能，但做select count(*)却无法借此覆盖索引功能加速。为何？ 因为mysql很烂～

以下是my.cnf 配置， 与innodb相关的已删除，因为dz只支持myisam。
有说mysql没优化的，请先指出哪儿设置不对。否则我会无视其结论的。

1. 
   
2. [client]
   
3. #password       = your_password
   
4. port            = 3306
   
5. socket          = /var/lib/mysql/mysql.sock
   
6. 
   
7. [mysqld]
   
8. port            = 3306
   
9. socket          = /var/lib/mysql/mysql.sock
   
10. 
    
11. skip-external-locking
    
12. skip-innodb
    
13. #skip-name-resolve
    
14. #skip-networking
    
15. #skip-locking
    
16. 
    
17. max_allowed_packet = 32M
    
18. max_connections    = 7000
    
19. #max_seeks_for_key  = 3000
    
20. 
    
21. key_buffer_size = 512M
    
22. table_open_cache = 512
    
23. sort_buffer_size = 16M
    
24. read_buffer_size = 16M
    
25. join_buffer_size = 32M
    
26. read_rnd_buffer_size = 32M
    
27. 
    
28. myisam_sort_buffer_size = 96M
    
29. 
    
30. thread_cache_size = 32
    
31. query_cache_size = 32M
    
32. 
    
33. tmp_table_size   = 128M
    
34. 
    
35. wait_timeout = 25
    
36. interactive_timeout = 25
    
37. 
    
38. # Try number of CPU's*2 for thread_concurrency
    
39. thread_concurrency = 8
    
40. 
    
41. slow_query_log  = 1
    
42. long_query_time = 10
    
43. slow_query_log_file = /var/log/slowlog.log #skip-networking
    
44. 
    
45. #log-bin = mysql-bin
    
46. #expire_logs_days = 20
    
47. server-id       = 1
    
48. [mysqldump]
    
49. quick
    
50. max_allowed_packet = 16M
    
51. 
    
52. [mysql]
    
53. no-auto-rehash
    
54. # Remove the next comment character if you are not familiar with SQL
    
55. #safe-updates
    
56. 
    
57. [myisamchk]
    
58. key_buffer_size = 256M
    
59. sort_buffer_size = 256M
    
60. read_buffer = 2M
    
61. write_buffer = 2M
    
62. 
    
63. [mysqlhotcopy]
    
64. interactive-timeout
    
65. 
    

复制代码

技术贴。貌似很懂看不懂。
不过大概还是看出点什么。就是mysql一直原来的快最后还是被打败了。
那么数据库在硬件资源使用率方面呢？比如cpu使用率，内存甚至带宽占用什么的都行。可以来一个对比吗？

枯心树 发表于 2012-7-25 19:34
技术贴。貌似很懂看不懂。
不过大概还是看出点什么。就是mysql一直原来的快最后还是被打败了。
那么数据库 ...

其他的我不确定，但在我这儿测试结果mysql的确被pg打败了，而且是惨败。

就CPU使用率对比有个奇怪但细想就明了的现象：
mysql（就1个）进程CPU占用率并不高，平时在3%左右，高点在20%左右。而pg因为是多进程，所以不好综合评估，但postgres的活动进程CPU占用率从1%到20%的都有，且分布平均。 这说明了啥？

个人理解：mysql瓶颈在磁盘性能，而其弱智的查询优化器很容易产生filesort状况，于是查询开销大部分要么在磁盘上面，要么处于磁盘等待队列中，这使得mysql无法有效地合理地利用硬件资源（主要是CPU）。但这种情况所表现出的低CPU占用率往往会迷惑人，让人以为mysql对硬件资源占用低。
而pg因为程序结构设计优秀，没有明显的硬件瓶颈，所以可以尽量的发挥硬件性能，可以跑出高CPU占用。于是就可以尽快地处理完查询而接收下一个请求。

这种现象其实用OS来比较就容易明白了：
windows在vista之前对于内存管理方式一直是尽量少用物理内存，只要程序不活动或者最小化就鸡婆地做换页把程序占用的内存页交换到虚拟内存中（也就是交换文件）。让你再内存管理器中看到系统还有那么多空余内存，啊，可以睡个好窖了～～～
而*nix对于内存管理设计思想是尽量利用物理内存，如果物理内存足够就拿来做缓存。所以在top中看到的内存往往都是占用满了的。于是乎初次接触linux的win用户会对此惊讶并发出求救：我的内存怎么用光了啊！ 于是乎当他们用上vista,win7后也会有同样的遭遇。

mark35 发表于 2012-7-25 19:59
其他的我不确定，但在我这儿测试结果mysql的确被pg打败了，而且是惨败。

就CPU使用率对比有个奇怪但细 ...

这点倒认同。最近换vps了。用了linux系统，发现内存使用率基本都很高。仔细看，实际内存使用率却不高。
想来应该是这个"物尽其用“原因。
我还有一点不是很明白。pg是否和linux一样？也就是说，相同的硬件环境，pg的效率是不是比my的高？
linux对硬件要求不高，甚至很低。甚至用赛扬4都可以跑站。那么pg如果在这样低级的硬件环境下会不会卡死？我的意思是，如果硬件不是很好，是不是就不能跑pg了？？比如是vps入门的一般都是很低硬件配置之类的

枯心树 发表于 2012-7-25 20:55
这点倒认同。最近换vps了。用了linux系统，发现内存使用率基本都很高。仔细看，实际内存使用率却不高。
...

linux的top看的内存可以理解为有效内存，要看物理内存使用用这个命令：
free -m
然后看 -/+ buffers/cache: 这一行的数据

楼主版主复读机了耶 ～～

枯心树 发表于 2012-7-25 20:55
这点倒认同。最近换vps了。用了linux系统，发现内存使用率基本都很高。仔细看，实际内存使用率却不高。
...

关于pg对于低硬件配置的表现这个没仔细研究。不过按照*nix系统极其程序的一贯表现，我认为pg在低硬件配置下表现不会差。
当然有个情况必须考虑到：pg是跑进程的，每个请求都会新开一个postgres进程。如果不限制webserver连接数那么极有可能会因为并发请求多而导致pg进程把内存吃完。 这个问题对于 php-fpm 就很好解决了，把 pm.max_children 设定一个合理值即可。对于apache也有对应的配置参数。
这一点也是在低配置服务器上mysql表现得比pg的原因——mysql是跑线程，只有一个主进程，不会因为连接数轻易把内存吃光。

但反过来，如果硬件配置高，那pg就可以发挥潜能了。好比在市区内QQ还能比法拉利跑得欢，一旦上高速公路那又是另外一分高下。

如果楼主真心想学习mysql优化的话，推荐一本书 《mysql性能调优与架构设计》先入入门

楼上高级黑